In this article, we will review how to include custom fields to the PPM Express Power BI Reports.
When you configure Advanced Power BI Report Pack (step 5), Core Report Pack (step 4), Innovation Report Pack (step 4), and PPM Express Executive Report Pack (step 4), RAID+Registry Report Pack (step 4), Project Management Report Pack (step 4), Time Report Pack (step 4) there is a step when on the Settings for published dataset page, the Parameters section is opened and the OData URL is provided to the PathToPPMX parameter.
There is one more parameter in this step: FeedsWithAllFields (ODataFeedsContainAllFields in Advanced & Core report packs). It is optional and is used only for cases when it is required to include the custom fields from PPM Express pages to the report Data Model.
This parameter defines the report Data Model structure. If this parameter is empty, the report Data Model will include a default set of fields only (fields used in the report visualizations and key fields used in the Data Model relationships only).
If the parameter is empty and the default set of fields is used, it will make the report dataset refresh much faster and minimize the delays in response while working with it and the report memory.
FeedsWithAllFields (ODataFeedsContainAllFields in Advanced & Core report packs) parameter is used mostly for cases when you need to create your reports using your PPM Express custom fields. In this case, you can use the existing report pack Data Model instead of creating your one from scratch. In this case, the best way to create your reports is to select only the custom fields you need by editing the existing Power Query queries (providing the needed custom fields there). This is the best scenario because you can control what fields are added to the report and the report is not overloaded with unnecessary fields.
However, if you do not know how to edit Power Query queries, you can use FeedsWithAllFields (ODataFeedsContainAllFields in Advanced & Core report packs) parameter and provide the feeds that include the needed custom fields in this parameter (separating them with commas).
All the fields of the listed feeds will be added to the report Data Model:
- Advanced PPM Express Report Pack: The list is limited to the following names: Portfolios, Projects, Risks, Issues, PortfolioKeyDates, and ProjectKeyDates, Resources.
- Core PPM Express Report Pack: The list is limited to the following names: Portfolios, Projects, Risks, Issues, PortfolioKeyDates, ProjectKeyDates, Tasks.
- PPM Express Executive Report Pack: The list is limited to the following names: Portfolios, PortfolioKeyDates, PortfolioRisks, Projects, ProjectKeyDates, ProjectRisks, Issues, Programs, ProgramKeyDates, ProgramRisks, Resources, Tasks, Challenges, Ideas.
- Innovation PPM Express Report Pack: The list is limited to the following names: Resources, Challenges, Ideas.
- RAID+Registry Report Pack: Projects, Portfolios, Programs, Resources, ProjectRisks, ProgramRisks, PortfolioRisks, Issues, ChangeRequests, ProjectActionItems, ProgramActionItems, PortfolioActionItems, ProjectKeyDecisions, ProgramKeyDecisions, PortfolioKeyDecisions, LessonsLearned, ProjectDeliverables.
- Project Management Report Pack: Resources, Projects, Project Key Dates, Project Risks, Issues, Project Action Items, Change Requests, Tasks.
- Time Report Pack: Resources, TimeTrackingEntries, Portfolios, Programs, Projects.
- Financial Report Pack: Resources, Portfolios, Programs, Projects, Invoices, PurchaseOrders.
We recommend leaving this parameter empty while configuring your reports in the Power BI service. Changes to the report Data Model can only be done in Power BI Desktop. That is why changes in the parameter will only take effect if the needed feeds are provided in the FeedsWithAllFields (ODataFeedsContainAllFields) parameter in Power BI Desktop and the refresh operation is performed in Power BI Desktop.
When the parameter is provided in Power BI Desktop and published to Power BI Service the custom fields appear in the report Data Model (both in Power BI Desktop and Power BI Service).
The Report Data Model supports the following Data types:
The fields where the 'Multiline' option is set to 'YES' cannot be added to the Report Data Model. To add a custom field to the Report Data Model, the 'Multiline' option should be set to 'NO' during field creation.
To add the custom fields from the needed feeds to the report Data Model, perform the following:
1. Open the needed report in Power BI Desktop.
2. Click Transform data => Edit parameters.
3. In the Edit Parameters window, paste the OData URL to the PathToPPMX parameter and provide the needed feeds in the FeedsWithAllFields (ODataFeedsContainAllFields) parameter, separating them with commas.
For example, if you add two new custom fields to the 'Details' project section in PPM Express and one field to the 'Risks' section, to add these fields to the report Data Model, you need to type “Projects, Risks” value in the FeedsWithAllFields (ODataFeedsContainAllFields) parameter.
FirstDayOfWeek parameter is optional, and the default value is 'Sunday'.
TaskField_DailyTaskProgressIsBasedOn parameter defines the name of a task field based on which a calculation of daily task progress is based. Supported fields are the following: Effort, Story Points. The default value is Effort.
Click OK.
4. Click the Apply Changes button.
5. In the Credentials window, select Basic authentication type and paste the token copied from PPM Express => Reporting settings to the Password field. You can use the token you copied while configuring reports in the Power BI service or create a new one.
The 'User name' field may be left empty. Click Connect.
To create a token for PPM Express Power BI reports, perform the following actions:
Click Create access token under the OData feed URL.
Click Generate Token button;
Copy the token.
Make sure you copy the above token now. We don't store it and you will not be able to see it again.
Next, we need to establish a connection to the Web. By establishing a connection to the Web, we ensure that the latest version of the report pack will be available for download.
Select an Anonymous authentication method in the next window. Click Connect.
Next, select the Organizational privacy level. Click Save.
6. Allow some time for the changes to take place.
7. Now, you can navigate to the Data pane on the right, select Projects (or Risks as in our example), and build the report using your custom fields.
Report Data Model will now include all fields from Projects and Risks feeds (these feeds were provided in the FeedsWithAllFields (ODataFeedsContainAllFields) parameter in our example).
When ready with the report, click the Publish button to publish the report to the Power BI service.
After you publish the report to the Power BI service, the report Data Model in the Power BI service will be changed and will include all custom fields from the feeds you added to the report Data Model. You can also build your reports in the Power BI service using these fields.
If you add a new custom field to PPM Express pages and need to include it in the report Data Model, it is required to download the report pack from the Power BI service (use its latest version), open this report in Power BI Desktop and refresh it there or add the required feeds to the FeedsWithAllFields (ODataFeedsContainAllFields) parameter as described in this article. After this, publish it to the Power BI service.
If the custom fields that you added to the report Data Model are deleted from PPM Express, it is required to refresh the report Data Model via Power BI Desktop again so that the changes take place. When the report Data Model is refreshed using Power BI Desktop, it should be published to the Power BI service to change the Data Model there as well.