none
Extract Cost Rate Table using Project Server Interface (PSI) - PS 2007 RRS feed

  • Question

  • We need to extract the cost rate table for rate A for all resources. Including all records with an effective date.

    I have seen that this is not available in the Reporting Database

    Are there any example of how to do that in PSI? Preferably with PowerShell.

    Mike


    Mike

    Sunday, November 3, 2013 3:53 PM

All replies

  • Mike,

    This can be easily done with VBA--which could be your path of least resistance.

    Not the VBA code that you need but the following will give you some idea of the correct syntax for accessing the rate tables with VBA:

    Public Sub ZeroRates()

    Dim R As Resource

    On Error Resume Next

    For Each R In ActiveProject.Resources

        R.CostRateTables("A").PayRates(1).StandardRate = 0

    Next

    End Sub




    • Edited by Edward Hanna Sunday, November 3, 2013 6:57 PM Remove signature
    Sunday, November 3, 2013 6:57 PM
  • We needed the code to run as regular unattended job. So we went for Powershell feeding a SQL BULK INSERT.

    Very rough code below. 2 main issues on the way:

    1. Getting error 401 on attaching to web service from the dev user. Don;t know where to configure the permission
    2. Powershell export-csv is really unhelpful. Its format is not compatible with BULK INSERT. Every field has quotes around, even non-strings. Brute force method of stripping out quotes is not ideal but think I can guarantee no pipe symbols (my delimiter) in the strings

    Only interested in Rate A and the code seems to default to that. Good practice would be to be more explicit

    Load rate table
    5/11
    
    $pwaServiceAssembly = New-WebServiceProxy -uri http://projectserver-dev/pwa/_vti_bin/PSI/Resource.asmx?wsdl -UseDefaultCredential
    
    # 1 is enum that evaluates to active
    $list_resources = $pwaServiceAssembly.ReadUserList(1).Resources
    $results = @()
    $csv = 'd:\SQL Code\rates.csv'
    $ratetable= "dbo.Custom_ts_rates"
    $delimiter = '|'
      
     foreach ($res in $list_resources)
     {
        #Write-Output ($res.RES_NAME,$res.RES_UID)
        $resource = $pwaServiceAssembly.ReadResource($res.RES_UID)
        $RateCount = $resource.ResourceRates.Count
        if ($RateCount -gt 0) 
        {
            foreach ($rate in $resource.ResourceRates)
            {
    
                if ($rate.RES_RATE_EFFECTIVE_DATE.gettype().name -eq "DBNull")
                {$eff = "2000/1/1"}
                else
                {$eff = $rate.RES_RATE_EFFECTIVE_DATE.tostring("yyyy/MM/dd")}
                  
                $record = @{
                        effective = $eff
    		ot1=$rate.RES_OVT_RATE
    		std = $rate.RES_STD_RATE 
    		resid = $res.RES_UID
    		resname=$res.RES_NAME
                }
                $results+= New-Object psobject -Property $record
            }
        }
       
       
     }
     
     # send to csv
     $results | select resname, resid, std, ot1, effective | export-csv -Delimiter $Delimiter -Path $csv -NoTypeInformation
     # strip out header row and the double quotes
     (Get-Content $csv) | where {$_.readcount -gt 1} |  foreach {$_ -replace '"'} |Set-Content $csv
     $lines = get-content $csv | measure-object -line
    
     if ($lines.lines -gt 0)
     {
     
        $con = new-object "System.data.sqlclient.SQLconnection"
    
        #Set Connection String
        $con.ConnectionString =(“Data Source=server;Initial Catalog=projectserver_reporting_17Sept;Integrated Security=SSPI”)
        $con.open()
    
        $sqlcmd = new-object "System.data.sqlclient.sqlcommand"
        $sqlcmd.connection = $con
        $sqlcmd.CommandTimeout = 600000
        $sqlcmd.CommandText = "DELETE  from " + $ratetable
        $rowsAffected = $sqlcmd.ExecuteNonQuery()
        $sqlcmd.CommandText = "BULK INSERT " + $ratetable + " FROM '" + $csv + "' WITH (FIELDTERMINATOR = '" + $Delimiter + "', ROWTERMINATOR = '\n')"
        $rowsAffected = $sqlcmd.ExecuteNonQuery()
     
     }
     Return $LASTEXITCODE
    
    


    Mike

    Wednesday, November 6, 2013 9:28 AM
  • If anyone does use this, it doesn't just fetch cost rates in table A. Had to exclude B,C,D etc with an if

    Mike

    Wednesday, November 20, 2013 2:37 PM