UMT360 Blog

Blog Posts from the Leader in Enterprise Portfolio Management

Building reports and dashboards with Project for the web

Written by admin on October 28th, 2019 at 1:07 pm

Strategy Execution

Project for the web marks the next chapter for Microsoft Project, providing a distinctive modern web interface and user experience for managing work of any size.

Project for the web is built on the Microsoft Power Platform, which means that, unlike its predecessor, all data is stored in the Common Data Service (CDS). Why is this important? Well, for starters, it will require a learning curve when extending its functionality through configuration, as well as building reports and dashboards when pulling data from the CDS.

We’ll leave the topic of extending the functionality of the new Project through configuration for another time and focus today on how to build reports and dashboards instead.
NOTE: Before we get started, let’s briefly touch on licensing implications. In order to build or customize Power BI reports on Project for the web data you will need to have a Project Plan 3 (formerly known as Project Online Professional) or Project Plan 5 (formerly known as Project Online Premium) license. However, these licenses are not required in order to read Power BI reports built on Project for the web data.

Connect to the data you need
Ok, let’s get to it. The data from the new Project for the web is stored in the Common Data Service and can be accessed through OData. Here are 5 easy steps to connect to the data using Power BI Desktop:

  • Start Power Bi Desktop and from the Home tab, click Get Data
  • In the Get Data list, select Online Services on the right-hand side, select the Dynamics 365 (online) option and click Connect
  • In the dialog box that opens, enter the OData endpoint URL in the Web API URL field and click OK.
    Here is how the URL should look like: https://[baseURL]/api/data/v9.1
    NOTE: You will replace the [baseURL] with the URL of the Dynamics environment where you access the new Project app. It will look something like this [tenant name].crm.dynamics.com.
  • If you are prompted to sign-in, click Organizational account, and then click Sign In. Enter your credentials (the same one you use to connect to Project for the web), then click Connect.
  • The entity tables will now appear in the Navigator window, where you can select both default and custom entities.

Select the right entities
Here’s where it gets a bit tricky. Connecting to the CDS database exposes a long list of Entities (default and custom), most of which are not relevant to Project for the web.

So which ones should you choose? Here’s the list of the entities I found useful when building reports that contain data from Project (yes, these are their actual names):

  • accounts
  • bookableresources
  • contacts
  • msdyn_projectbuckets
  • msdyn_projects
  • msdyn_projecttaskdependencies
  • msdyn_projecttasks
  • msdyn_projectteams
  • msdyn_resourceassignments

Take the time to rename fields
Once you add the required Entities into your Power BI dataset, the first thing you’ll notice is that the fields don’t have the most user-friendly names. Take for example the msdyn_projects table:

I recommend you rename the fields to something more usable and export as a Power BI Template, so the next time you go about doing a report, you already have a set of fields that are easy to identify. For example, renaming the msdyn_subject field to Project Name in the msdyn_projects table will make it easier to find next time, since that’s what it actually represents.

Also, consider removing system fields which you’re not likely to ever use in your reports, the field msdyn_replaylogheader_value being a good example.

The key to unlocking all that data
There you have it. Ready-to-build project-level and portfolio-level reports and dashboards based on the new Project for the web. If you require assistance, don’t hesitate to reach out to UMT360’s team of experienced professionals to help you bring your Project for the web data to life in powerful reports and dashboards like these:

Leave a Reply