Call Us Today! 1-800-895-3254
[email protected]
Microsoft Training
Integent offers a comprehensive portfolio of training services, with an ideal fit for every organization. Ranging from general courses to custom solutions, we have you covered.
Learn About TrainingSee Full list of Courses
Microsoft Consulting
Integent provides consulting services for a variety of Microsoft applications including Microsoft Project, Project Online, Project for the Web, Dynamics 365, and Microsoft SharePoint.
Learn About Consulting
Microsoft Support
Microsoft technologies set the standard for project portfolio management, business applications, collaboration, and more. Integent has flexible solutions to support every customer.
Learn About Support
Microsoft Project
Integent has the knowledge and experience to help design and deploy a solution to help your organization manage projects and resources more effectively
Learn More About Microsoft Project
Microsoft Dynamics 365
Integent can help you nsure business continuity with a custom cloud solution that connects sales, service, finance, and operations teams to deliver results.
Learn More About Dynamics 365
Microsoft SharePoint
SharePoint is Microsoft’s collaboration platform that is a place where team members can communicate, exchange data, share files and much more.
Learn More About Microsoft SharePoint
Microsoft Power Platform
Consisting of Power BI, Power Apps, Power Automate and Power Virtual Agents, the Microsoft Power Platform helps streamline and improve business functions.
Learn More About The Power Platform
About Integent
Integent was founded in 2009 on the principles of building long-lasting relationships with customers, exceeding expectations, and providing outstanding customer service. 

We’re dedicated to providing a customized solution backed by our knowledgeable team of Microsoft experts.
Discover What Makes Us Different
GSA Schedule
Integent is GSA Schedule Contract holder. A GSA schedule helps simplify selling our services to government customers.
Read More Here
News
Stay informed on Integent’s latest company updates and other important industry information. Our passion is customer success.
Read The Latest News
Videos
Learning new software doesn’t have to be difficult with our library of dozens of free training videos. Our video training always has something new.
Browse Our Training Video Library
Blog
Our industry experts share best practices and common pitfalls to avoid, in order to get the most out of your technology solutions.
Discover More

November 3, 2014

Fixing Powerpivot Table Relationships for Odata

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.

  1. Install the Powerpivot add-in if you haven’t already.
  2. In PWA, navigate to your Reports.  Select English.
  3. Click on New and select Excel workbook.
  4. Click on Open in Excel.
  5. You will get a message your browser is trying to close the page. Just say no.
  6. Excel will open.
  7. Click on Data/From Other Sources
  8. Select Odata
  9. Enter the URL for your Odata (this will be <your pwa address>/_api/ProjectData/).
  10. Click Next.
  11. Select the tables you need and click Next
  12. 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.
  13. Select Table from the Import Data popup and click OK.
  14. Click on the Powerpivot tab.
  15. In the Powerpivot window, click on Manage/Design/Create Relationships.
  16. Select the relationships between each of your tables.  In this screen, the first field is the “many” field, and the second is the “one”.
  17. In each tab, remove the fields you don’t need (leave the key fields you require for the relationships).
  18. 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.
  19. Click on Insert Function.
  20. Choose the RELATE function. This will provide a wizard to help you add the fields you need.
  21. 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).
  22. Add a closing parenthisis to the function before clicking on OK.
  23. Repeat for each field you need in your report.
  24. When all the fields you need are in this table, click on Home/Create Pivot Table.
  25. 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.

© Copyright 2023 | Integent.com | All Rights Reserved
linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram