Building Power BI reports to display Dynamics 365 Business Central data

You can make your Dynamics 365 Business Central data available as a data source in Power BI Desktop and build powerful reports about the state of your business.

This article describes how to start using Power BI Desktop to create reports that display Dynamics 365 Business Central data. After you create reports, you can publish them to your Power BI service, or share them with all users in your organization. When the reports are in the Power BI service, users that are set up for it can view the reports in Dynamics 365 Business Central.

Get ready

• Sign up for the Power BI service.

  If you aren't signed up, go to https://powerbi.microsoft.com. When you sign up, use your work email address and password.

• Download Power BI Desktop.

  Power BI Desktop is a free application you install on your local computer. For more information, see Quickstart: Connect to data in Power BI Desktop.

• Make sure the data you want in the report is available as an API page or published as a web service. For more information, see Expose data through API pages or OData web services.

• Download the Business Central report theme (optional).

  For more information, see Use the Business Central report theme in this article.

Note

Note:
You could get the data from various companies in a single report with OData web services. However, starting with Business Central 2021 release wave 2, only ODataV4 is supported. ODataV4 doesn't export data from multiple companies. The $expand function in Power BI that you might think would be an alternative way to create a multi-company report can't be used either. It creates a column with the company name but doesn't populate it with the company data after a refresh.

Add Business Central as a data source in Power BI Desktop

The first task in creating reports is to add Business Central as a data source in Power BI Desktop. Once connected, you can start to build the report.

1. Start Power BI Desktop.

2. Select Get Data.

   If you don't see Get Data, select the File menu, then Get Data.

3. On the Get Data page, select Online Services.

4. In the Online Services pane, do one of the following steps:

   • To connect to Business Central online, select Dynamics 365 Business Central, then Connect.

5. Sign-in to Business Central (one-time only).

   If you aren't signed in to Business Central from Power BI desktop, you're prompted to sign in.

   • For Business Central online, select Sign in, and then choose the relevant account. Use the same account that you use to sign into Business Central. When done, select Connect.

   Note

   Note:
   After you connect to Business Central, you won't be prompted again to sign in. How do I change or clear the account I'm currently using to connect to Business Central from Power BI Desktop?

6. When connected, Power BI contacts the Business Central service. The Navigator window displays the data sources that are available for building reports. Select a folder to expand it and show the available data sources.

   These data sources represent all web services and API pages that are published for Business Central, grouped by environments and companies. With Business Central online, Navigator has the following structure:

   • Environment name
     • Company name

       • Advanced APIs

         This folder lists advanced API pages published by Microsoft, like the Business Central automation APIs and custom API pages for Business Central. Custom API pages are further grouped in folders according to the APIPublisher/APIGroup properties of the API page source code.

       • Standard APIs v2.0

         This folder lists the API pages exposed by the Business Central API V2.0.

       • Web services (legacy)

         This folder lists pages, codeunits, and queries that are published as web services in Business Central.

7. Select the data source or sources that you want to add to your data model, and then select the Load button.

8. If later you want to add more Business Central data, you can repeat the previous steps.

Once the data is loaded, you can see it in the right navigation on the page. At this point, you're connected to your Business Central data, and you can begin building your Power BI report.

Tip

Tip:
For more information about using Power BI Desktop, see Get started with Power BI Desktop.

Creating accessible reports

It's important to make your reports usable for as many people as possible. Try to design reports so that they don't require any special adaption to meet specific needs of different users. Make sure the design lets users take advantage of standard assistive technologies, like screen readers. Power BI includes various accessibility features, tools, and guidelines to help you achieve this goal. For more information, Design Power BI reports for accessibility in the Power BI documentation.

Creating reports to display data associated with a list

You can create reports that display in a FactBox of a Business Central list page. The reports can contain data about the record selected in the list. Creating these reports is similar to other reports, except there are a few things to do to make sure the reports display as expected. For more information, see Creating Power BI Reports for Displaying List Data in Business Central.

Using the Business Central report theme (optional)

Before building your report, we recommend that you download and import the Business Central theme file. The theme file creates a color palette so you can build reports with the same color styling as the Business Central apps, without requiring you to define custom colors for each visual.

Note

Note:
This task is optional. You can always create your reports, and then download and apply the style template later.

Download the theme

The theme file is available as a json file on Microsoft Power BI Community Themes Gallery. To download the theme file, do the following steps:

1. Go to Microsoft Power BI Community Themes Gallery for Microsoft Dynamics 365 Business Central.
2. Select the download attachment Microsoft Dynamics Business Central.json.

Import the theme on a report

After you download the Business Central report theme, you can import it to your reports. To import the theme, Select the View > Themes > Browse for themes. For more information, see Power BI Desktop - Import custom report themes.

Publish reports

After you create or modify a report, you can publish the report to your Power BI service and also share it with others in your organization. After you publish a report, it's available in Power BI. The report also becomes available for selection in Business Central.

To publish a report, select Publish on the Home tab of the ribbon or from the File menu. If you're signed into Power BI service, the report is published to this service. Otherwise, you're prompted to sign in.

Distribute or share a report

There are a couple ways to get reports to your coworkers and others:

• Distribute reports as .pbix files.

  Reports are stored on your computer as .pbix files. You can distribute the report .pbix file to users, like any other file. Then, users can upload the file to their Power BI Service. See Upload reports from files.

  Note

  Distributing reports in this manner means that refreshing data for reports will be done individually by each user. This situation might impact Business Central performance.

• Share report from your Power BI service

  If you have a Power BI Pro license, you can share the report to others, directly from your Power BI service. For more information, see Power BI - Share a dashboard or report.

How to develop cross-company or cross-environment Power BI reports

The Business Central API endpoints all have the prefix https://api.businesscentral.dynamics.com/v2.0/<environment_name>/api/v2.0 followed by /companies({company_id})/accounts({id}) (here we use the accounts API as an illustration). You can use this structure to create PowerQuery queries that load data for multiple companies or multiple environments if the user who is reading data can access them.

To set up a query to load data for multiple companies, follow these steps:

1. Take the PowerQuery query that loads data for a single company. Convert it to a custom Power Query function that takes the company ID (or maybe the environment name) as parameters. To learn more, go to Using custom Power Query functions.
2. Now use the new custom function in a PowerQuery query, where you map the function over a list of companies and then merge the datasets using the Table.Combine Power Query function.

Fixing problems

"Can't insert a record. Current connection intent is Read-Only." error connecting to custom API page

APPLIES TO: Business Central online

Starting in February 2022, new reports that use Business Central data connect to a read-only replica of the Business Central database by default. In rare cases, depending on the page design, you might get an error when you try to connect to and get data from the page.

1. Start Power BI Desktop.

2. In the ribbon, select Get Data > Online Services.

3. In the Online Services pane, select Dynamics 365 Business Central, then Connect.

4. In the Navigator window, select the API endpoint that you want to load data from.

5. The preview pane shows the following error:

   Dynamics365BusinessCentral: Request failed: The remote server returned an error: (400) Bad Request. (Can't insert a record. Current connection intent is Read-Only. CorrelationId: [...])".

6. Select Transform Data instead of Load as you might normally do.

7. In Power Query Editor, select Advanced Editor from the ribbon.

8. In the line that starts with Source =, replace the following text:

   Dynamics365BusinessCentral.ApiContentsWithOptions(null, null, null, null)
   

   with:

   Dynamics365BusinessCentral.ApiContentsWithOptions(null, null, null, [UseReadOnlyReplica = false])
   

9. Select Done.

10. Select Close & Apply from the ribbon to save the changes and close Power Query Editor.

See also

Enabling Your Business Data for Power BI
Business Intelligence
Getting Ready for Doing Business
Importing Business Data from Other Finance Systems
Setting Up Business Central
Finance
Quickstart: Connect to data in Power BI Desktop

Find free e-learning modules for Business Central here