Skip to main content

Roll up Resource report by Project first, then Resource



  • Wes Kliewer

    The issue with the out-of-the-box report is exactly as described. The first column contains different data points and no clear indicator for a spreadsheet to separate the data into three separate columns. Spreadsheets are rather particular about this! This limitation should be overcome using a brand new API which has not yet been exemplified in a way that non-programmers can easily use for spreadsheet access. We're working on an example to make it easy. We should have that example in the next few days.

    There are two different ways to use the API to get access to data from a spreadsheet, and the one referenced here is the easiest which is simply using the API as a login. It doesn't do anything else. What you see in the PI report is what you get in Excel and Excel uses the API token to login and get the report.

    The second method takes more time to assemble, but uses the API to access raw data and provides far more control than the first method. This is the example that is needed for this report because we can take all three data points from the first column and fully expand all of that data into three separate columns.

    As soon as we have a better example for non-programmers for this other method, we will update this post with a reference to the article. If it is not in the next few business days due to challenges we don't currently expect, we will update this post with a new estimate.

  • Margaret Campbell

    Would any of these articles help with the discussion?

    Extract Saved Report to Excel without API
    Get Project Insight Data into Power BI using REST
    Get your Project Data Directly to Excel 

    Are any of these methods a possibility?

  • Allison Thayer

    Hi Margaret, I did look at those articles before posting my question. But it looks like Wes updated the "Extract Saved Report to Excel without API" article since I last spoke with him. I'm having trouble on step 3: when using the Advanced setting to place the token in the API request header parameter parameters, I'm inserting the saved report URL in the "URL parts" box and then entering my api token in the "HTTP request header parameters" box, but it's not connecting to the report data. I think I'm probably missing something simple - any ideas?

  • PI Marketing

    Hey Allison,

    So I'm a "visual" guy 🤣 ... and I will attempt to answer your questions with as many pictures to make this straight-forward. When you do it one time, it's really easy to repeat. Here we go...


    1. MAKE SURE YOUR TOKEN IS "WEB APP ENABLED". When you create your token in PI, be sure that you check this "Yes" so that this procedure will work:





    4. VERIFY CONNECTION. You will see a status screen followed by a Access Content confirmation. Go ahead and continue with "Anonymous" and the default settings.


    5. SELECT THE APPROPRIATE TABLE YOU WANT TO SYNC WITH IN THE EXCEL NAVIGATOR. In a typical PI Report page, it's one of the tables around "Table 4". Select your table, and when ready, click the Load button.


    7. PI DATA LOADS IN EXCEL. Once this connection has been made, the file has a permanent sync to the dyanmic data being provided by PI. You will only have to do this process ONCE. Every time the file opens, Excel will fetch the PI data in real-time. (If you want to do this manually, explore the Excel settings to modify this Excel behavior.)


    HOPE THAT HELPS! If you have any further questions, please feel free to post here in the community or contact us about more complex integrations that can make your life easier. My email is:

    Happy Exceling!


Please sign in to leave a comment.

Powered by Zendesk