Are you a vlookup veteran? Were you thrilled when table relationships and Powerpivot became available in Excel? Oh yes.
So this week I’m using Powerpivot to create a really cool report in Project Online, where I associate projects in the red with their high level risks. I love this! Perfect way to use Project Online’s capability to view across the portfolio.
Except…..when I pull the Projects and the Risks tables and pivot them, every project has every risk there is. Yes, my many-to-one relationship (many risks to one project) has turned into a many-to-many relationship. Not good.
After much searching and testing I found this solution. If you find yourself in the same situation, give it a try.
- Install the Powerpivot add-in if you haven’t already.
- In PWA, navigate to your Reports. Select English.
- Click on New and select Excel workbook.
- Click on Open in Excel.
- You will get a message your browser is trying to close the page. Just say no.
- Excel will open.
- Click on Data/From Other Sources
- Select Odata
- Enter the URL for your Odata (this will be <your pwa address>/_api/ProjectData/).
- Click Next.
- Select the tables you need and click Next
- You will be asked to name your connection file AND give it a friendly name. I suggest using the names of the tables you selected in each to help identify the data.
- Select Table from the Import Data popup and click OK.
- Click on the Powerpivot tab.
- In the Powerpivot window, click on Manage/Design/Create Relationships.
- Select the relationships between each of your tables. In this screen, the first field is the “many” field, and the second is the “one”.
- In each tab, remove the fields you don’t need (leave the key fields you require for the relationships).
- Open the lowest-level tab. This would be the most of the many-to-one relationships. For instance, if I have Projects, Tasks and Assignments, this would be Assignments.
- Click on Insert Function.
- Choose the RELATE function. This will provide a wizard to help you add the fields you need.
- For the first function, add a column you need from another table (hint: start typing the table name, the fields will appear once you choose the table).
- Add a closing parenthisis to the function before clicking on OK.
- Repeat for each field you need in your report.
- When all the fields you need are in this table, click on Home/Create Pivot Table.
- If you choose this table within the pivot table, all your fields will be available, properly related!
Thanks to Mike of Mike’s Notebook for this great blog post: http://mikesnotebook.wordpress.com/2014/06/03/reporting-with-project-online-attempt-2/
At MS Project Now we help clients solve reporting and data challenges on a regular basis. If you’d like to hear more, contact us.