How to include custom fields to the reports
In this article, we will review how to include custom fields to the main PPM Express Report Pack and to PPM Express Executives Report Pack.
When you configure external Power BI Reports (step 5) and PPM Express Executives 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 on this step: ODataFeedsContainAllFields. 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, then the report Data Model will include a default set of fields only (fields that are used in the report visualizations and key fields that are 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 will minimize the delays in response while working with it as well as the report memory.
ODataFeedsContainAllFields parameter is used mostly for cases when you need to create your own reports using your PPM Express custom fields. In this case, you can use the existing report pack Data Model instead of creating your own one from scratch. The best way to create your reports, in this case, 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 exactly are added to the report and the report is not overloaded with the unnecessary fields.
However, if you do not know how to edit Power Query queries, you can use ODataFeedsContainAllFields 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. The list is limited to the following names: Portfolios, Projects, Risks, Issues, PortfolioKeyDates, and ProjectKeyDates.
Please note: If you add the feeds to the ODataFeedsContainAllFields parameter, then all custom fields included in these feeds will be added to the report Data Model. This can make the refresh process and the report response longer.
Please note: We recommend to leave this parameter empty while configuring your reports in Power BI service in any case. 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 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).
Please note: Report Data Model supports the following Data types:
The fields where the 'Multiple choice' option is set to 'YES' cannot be added to the Report Data Model. To add a custom field to the Report Data Model, the 'Multiple choice' 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 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 ODataFeedsContainAllFields parameter.
4. Click the Apply Changes button.
5. In the Credentials window that appears 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 external reports in Power BI service or create a new one.
The 'User name' field may be left empty. Click Connect.
To create a token for external Power BI reports perform the following actions:
1. Click Create access token under the OData feed URL.
3. Click Generate Token button;
4. 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.
Please note: Once the token is revoked, it cannot be restored.
6. Allow some time for the changes to take place.
7. Now you can navigate to the Fields pane on the right and 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 ODataFeedsContainAllFields parameter in our example).
When you are ready with the report, click the Publish button to publish the report to Power BI service.
Please note: After you publish the report to Power BI service the report Data Model in Power BI service will be changed and will include all custom fields from the feeds you added to the report Data Model. You can build your reports in Power BI service using these fields as well.
Please note: If you add a new custom field to PPM Express pages and need to include it to the report Data Model, it is required to download the report pack from 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 ODataFeedsContainAllFields parameter as described in this article. After this publish it to Power BI service.
Please note: 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 Power BI service to change the Data Model there as well.