Extracting Resources' data RRS feed

  • Question

  • I need to extract some information from resources in PWA to a Excel Spreadsheet where the data will be used to create high-level budgets. Since this must be a template for all projects, I'm trying to decide what's my best option to do it so the users do not have to worry about copy-paste, connections and so on.

    My first option was to simply add a connection to the Project Server Reporting database and retrieve the resources' data to the workbook but since I need to consider Cost Tables I cannot do it that way. I can only get the Standard Rate using that approach.

    Second option is to create a VBA code in Excel to retrieve data by using the references to the Project Model.

    Third option is to create a VBA code in Project to push data to Excel once the resources are already been selected in the project.

    Someone has any ideas?

    Thanks in advance

    Rene Alvarez

    Thursday, November 1, 2012 3:07 PM

All replies

  • All depends on the data you need. I would try and read from the Reporting db into Excel. That's the fastest and simplest solution. VBA from Project Controlling Excel or vice versa is decided purely by where you data is and what needs moving where.

    If this doesn't help then we need more information on what you want to do and why just reading from the reporting db isn't good enough.

    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    • Edited by Rod Gill Sunday, November 4, 2012 6:30 AM
    Sunday, November 4, 2012 6:30 AM
  • Thanks Rod,

    The thing is that I need to extract Cost Table's data and that's not available in the RDB.

    I'm actually following some examples you have on your book for VBA programming and I'm basically there. Do you think it's easier to pull Cost Table data in a better way?

    Rene alvarez

    Rene Alvarez

    Sunday, November 4, 2012 8:29 PM
  • What's not in the Cost table? All cost data should be in the Task table in the reporting db. View dbo.MSP_EpmTask_UserView seems to have everything and it also has any custom fields appended to it as well.

    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Monday, November 5, 2012 12:12 AM
  • I'm not accessin Task info, but Resource's. If I enter different costs in the Cost Rate Tables, in the MSP_EpmResource_UserView I will always have only Standard Rate. I need to be able to report the cost of a given resource (regardless of assignments) and that info is stored as a binary field in the databases so the only way to read it is by using PSI, an Add-On or VBA. Am I missing something?

    Rene Alvarez

    Rene Alvarez

    Monday, November 5, 2012 12:25 AM
  • Hi René,

    you are right. You can get that information only using PSI or indirectly from the assignment data in the assignment_by_day table (devide cost/work) if you have one for that cost rate table.

    We have created a tool that creates a resource_costrate_by_day table for a given timeframe.

    Contact us if you are interested.


    Saturday, November 17, 2012 8:04 PM