Proj SVR 2010 - Remaining availability RRS feed

  • Question

  • Hi

    I wonder if anyone can tell me the easiest way to obtain a list of enterprise resources with remaining availability programmatically. Probably timephased. VBA?

    I guess these would be the same figures that can be displayed in the resource usage view - remaining availability.

    I've seen the code for standalone project vba - tsvs As TimeScaleValues etc. Would this same approach work in EPM or would code need to be adapted to deal with enterprise focus rather than individual project focus.


    Brian Hyam

    Sunday, June 21, 2015 5:46 AM

All replies

  • Get it from the Reporting db. The free Project SDK kit you can download from provides the schema for the database.

    You can use Excel VBA to read the data and display in Excel. Use a Pivot table on the returned data for a great report.

    Rod Gill
    Author of the one and only Project VBA Book

    Monday, June 22, 2015 4:26 AM
  • Thanks Rod

    Would it still be code similar to the below bearing in mind I will be looking at remaining availability across the enterprise rather than single standalone project or would you approach it in a different way.

    I wish to retrieve the remaining availability metrics in such a way that the results can be programmatically assigned to a task in a specific project for each resource with the aim of making/showing staff as 100% deployed.

    • Sub test()
    • Dim Res As Resource
    • Dim tsvs As TimeScaleValues
    • Dim tsv As TimeScaleValue
    •     Set Res = ActiveProject.Resources("Bob")
    •                              Set tsvs = Res.TimeScaleData(ActiveProject.ProjectStart, ActiveProject.ProjectFinish, _
      pjResourceTimescaledRemainingAvailability, pjTimescaleWeeks)
    •     For Each tsv In tsvs
    •         Debug.Print tsv.StartDate; tsv.Value
    •     Next tsv
    • End Sub



    Brian Hyam

    • Edited by Brian Hyam Monday, June 22, 2015 8:39 AM
    Monday, June 22, 2015 8:38 AM
  • No, totally different. You are reading data from a database with ADODB, not interfacing to an object such as the timescaledata object.

    You need to set a reference to Active Data objects 2.8 in Excel then the following should work if you have rights to the reporting db.

    Sub ReadTest()
    Dim Conn As New ADODB.Connection
    Dim Rs As New Recordset
    'Edit names
    Const ConnectionString = "Connection String=Provider=SQLOLEDB;Server=SQLServername;Database=DatabaseName;Integrated Security=SSPI;"
        Conn.Open ConnectionString
        Rs.Open "SELECT * FROM dbo.MSP_EpmTask_UserView", Conn, adOpenForwardOnly, adLockReadOnly
        Range("A1").CopyFromRecordset Rs
    End Sub
    As I said you need the schema and easiest is to get your SQL db admin people to create a view with the data you want then you only have to read that View. But your admin people will need that Schema as well.

    Rod Gill
    Author of the one and only Project VBA Book

    Monday, June 22, 2015 8:52 PM