Power BI Reports in Business Central consists of a FactBox control element on a number of key list pages that provides additional insight into the data in the list.
For Viewing the List Data as you go between the rows in the list, the report is updated and filtered for the selected entry. You can create custom reports to display in this control, but there are a few rules to follow when creating the reports to ensure they provide the desired behavior.
Power BI Reports in Business Central Steps
Report Data Set
When you create the report in Power BI Desktop, specify the data source or web service that contains the data related to the list that you want to associate the report with. For example, if you want to create a report for the Sales List, ensure that the data set contains information related to sales.
To filter the data on the reports based upon the record chosen from the list page, the primary key must be used as a report filter. The primary keys must be a part of your data set in order for the reports to filter correctly. In most cases, the primary key for a list is the No. field.
Defining the Report Filter
The report is required to have a basic report filter (not a page or visual filter and not advanced filter) to filter correctly in the Power BI Fact Box Control. The filter that is sent to the Power BI report from each list page will be based on the primary key as described in the previous section.
To define a filter for the report, select the primary key from the list of available fields, and then drag and drop that field into the Report Filter section.
Report Size and Color
The size of the report should be set to 325 pixels by 310 pixels. This is necessary for proper scaling of the report in the available space allowed by the Power BI Fact Box control. To define the size of the report, choose focus outside of the report layout area and then choose the paint roller icon.
You can change the width and height of the report by selecting Custom in the Type field. Likewise, if you require the background of the report combine into the background color of the Power BI Fact Box control, define a custom report background color of E5E5E5. This is voluntary.
Reports with Multiple Pages
With MS Power BI, you can create a single report with multiple pages. The visuals that you want to see in the Business Central list pages must be on the first page of the report in Power BI.
Saving Your Report
When you save your report, it’s a best practice that the name of the report contains the name of the list page that you want to display the report in. For example, the word Vendor must be stored somewhere in the report name for reports that you want to make available on the Vendor list.
This is not a requirement; but, it will make the process of selecting reports quicker. When the report selection page is opened from a list page, a filter is passed based on the page name to limit the reports that are displayed. You may remove the filter to get a full list of reports available to you in Power BI.
This section provides a workaround for the most typical issues that can occur when creating the Power BI report.
User does not see a report on the Select Report page they want to select – If you cannot select a report, a possible solution is to verify the name of the report to ensure it contains the name of the list page. You can also clear the filter to obtain the full list of Power BI reports that are available.
Report is loaded but blank, not filtered or filtered incorrectly – Verify that the report filter holds the right primary key. In most cases, this is the No. field, but inside the G/L Entry table, for example, you must utilize the Entry No. field .
Report is loaded, but it displays the page you have not expected – Verify that the page you want displayed is the first page in your report.
Report appears with unwanted gray boarders, is too small or too large – Verify that the report size is set to 325 pixels x 310 pixels. Save the report, and then refresh the list page.
We at Locus IT provide Power BI with Dynamics 365 Business Central services including integration, training, implementation and support. For more information on how to View List Data in Power BI Reports in Business Central, please contact us.