across-how-use-financials-data-source-powerbi.md

title	description	author	ms.topic	ms.devlang	ms.search.keywords	ms.date	ms.author	ms.service	ms.reviewer
Building Reports in Power BI Desktop to Display Business Central Data	Learn how to make your data a Power BI source and build insightful business reports.	jswymer	conceptual	al	business intelligence, KPI, Odata, Power App, SOAP, analysis	10/23/2024	jswymer	dynamics-365-business-central	jswymer

Building [!INCLUDE powerbi-name] reports for displaying [!INCLUDE prod_long] data

You can make your [!INCLUDEprod_long] data available as a data source in [!INCLUDE powerbi-desktop-name] and build powerful reports about the state of your business.

This article describes how to start using [!INCLUDE powerbi-desktop-name] to create reports that display [!INCLUDEprod_long] data. After you create reports, you can publish them to your [!INCLUDE powerbi-service-name] or share them with all users in your organization. When the reports are in the [!INCLUDE powerbi-service-name], users with access can view the reports in [!INCLUDEprod_long].

Get ready

• Sign up for the [!INCLUDE powerbi-service-name].

  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.

  [!INCLUDE powerbi-desktop-name] is a free application you install on your local computer. Learn more at Quickstart: Connect to data in Power BI Desktop.

• Make sure the data you want in the report is available as an API (recommended) or published as a web service. Learn more in Expose data through API pages or OData web services.

• Download the [!INCLUDE prod_short] report theme (optional).

  Learn more in [Use the [!INCLUDE prod_short] report theme](#theme) in this article.

[!INCLUDEnote-multicompany-reports]

Add [!INCLUDEprod_short] as a data source in Power BI Desktop

The first task in creating reports is to add [!INCLUDEprod_short] as a data source in [!INCLUDE powerbi-desktop-name]. Once connected, you can start to build the report.

1. Start [!INCLUDE powerbi-desktop-name].

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 [!INCLUDE prod_short] online, select Dynamics 365 Business Central, then Connect.

5. Sign in to [!INCLUDE prod_short] (one-time only).

   If you aren't signed in to [!INCLUDE prod_short] from [!INCLUDE powerbi-desktop-name], you're prompted to sign in.

   • For [!INCLUDE prod_short] online, select Sign in, and then choose the relevant account. Use the same account that you use to sign into [!INCLUDE prod_short]. When done, select Connect.

   [!NOTE]
   After you connect to [!INCLUDEprod_short], 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, [!INCLUDE powerbi-name] contacts the [!INCLUDE prod_short] 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 APIs and web services that are published for [!INCLUDE prod_short], grouped by environments and companies. With [!INCLUDE prod_short] online, Navigator has the following structure:

   • Environment name
     • Company name

       • Advanced APIs

         This folder lists advanced APIs published by Microsoft, like the Business Central automation APIs and APIs published by other organizations (these APIs are called Custom APIs). Custom APIs are further grouped in folders according to the APIPublisher/APIGroup properties of the API source code.

       • Standard APIs v2.0

         This folder lists the data APIs exposed in the Business Central API V2.0. You use these APIs to read data from the [!INCLUDE prod_short] database, such as master data (customer, vendor, item, and so on), transactions (general ledger entries, customer entries, and so on), or setup data (dimensions, payment methods, unit of measure, and so on).

       • Web services (legacy)

         This folder lists pages, codeunits, and queries that are published as web services in [!INCLUDE prod_short]. Using this legacy method to read data from [!INCLUDE prod_short] is discouraged. We suggest using APIs instead.

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 [!INCLUDEprod_short] data, and you can begin building your [!INCLUDE powerbi-name] report.

Tip

For more information about using [!INCLUDE powerbi-desktop-name], go to 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 adaptation to meet the specific needs of different users. Make sure the design lets users take advantage of assistive technologies, like screen readers. [!INCLUDE powerbi-name] includes various accessibility features, tools, and guidelines to help. To learn more, go to Design Power BI reports for accessibility in the [!INCLUDE powerbi-name] documentation.

Creating reports to display data associated with a list in [!INCLUDE prod_short]

You can create reports that display in a FactBox on a [!INCLUDE prod_short] list page, and change when different rows are 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. To learn more, go to [Creating Power BI Reports for Displaying List Data in [!INCLUDEprod_short]](across-how-use-powerbi-reports-factbox.md).

Using the [!INCLUDE prod_short] report theme (optional)

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

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 [!INCLUDE prod_short] report theme, you can import it to your reports. To import the theme, Select the View > Themes > Browse for themes. Learn more in Power BI Desktop - Import custom report themes.

Publish reports

After you create or modify a report, you can publish the report to your [!INCLUDE powerbi-service-name] and also share it with others in your organization. After you publish a report, it's available in [!INCLUDE prod_short]. The report also becomes available for selection in [!INCLUDEprod_short].

To publish a report, select Publish on the Home tab of the ribbon or from the File menu. If you're signed into the [!INCLUDE powerbi-service-name], the report is published to this service. Otherwise, you're prompted to sign in.

Distribute or share a report

There are a couple of ways to share reports:

• Share a report from your [!INCLUDE powerbi-service-name] (recommended)

  If you have a [!INCLUDE powerbi-pro-license-name] license or use [!INCLUDE powerbi-premium-capacity-name], you can share the report directly from your [!INCLUDE powerbi-service-name]. To learn more, go to Power BI - Share a dashboard or report.

• Distribute reports as .pbix files.

  Reports are stored on your computer as .pbix files. You can distribute the .pbix file to users, like any other file. Then, the people you shared the file with can upload it to their [!INCLUDE powerbi-service-name]. To learn more, go to Upload reports from files.

  [!NOTE] Distributing reports in this way means that refreshing data for reports is done individually by each user. This situation might impact [!INCLUDEprod_short] performance.

How to develop cross-company or cross-environment [!INCLUDE powerbi-name] reports

The [!INCLUDEprod_short] 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.

Advanced: Customize language, timeout, database replica, or page size for your Business Central data source

The [!INCLUDE powerbi-connector-name] supports several advanced properties for connecting to a Business Central data source that you can set in your Power Query queries. The following table describes the parameters.

Parameter	Description	Default	Learn more at
AcceptLanguage	This parameter allows you to specify preferred languages for responses, ensuring users receive messages and translatable strings in their desired language. It sets the language in which the Business Central API session runs. It influences the language of error messages, formatted values in AL, and other values that depend on language or culture. Setting this parameter improves user satisfaction and makes the data more accessible and relevant.	not specified	Use locale values in multiple-language Power BI reports.
ODataMaxPageSize	This parameter limits the number of entities per results page, which allows for more flexibility when connecting to large datasets or using complex queries. It sets the maximum number of records to return for each page when calling an API. For example, if your table Customers has 13,000 records and ODataMaxPageSize is set to 5000, Power BI makes 3 API calls to get your customers. The first call gets 5,000 records, the next one gets 5000 more, and the last call gets the remaining 3000. This option can't be higher than the maximum page size enforced by Business Central, which is 20000. Setting this parameter ensures efficient and responsive data retrieval, leading to faster insights and decision-making. You can't exceed the maximum page size defined on the service.	5000	ODataPreferenceHeader.MaxPageSize Property
Timeout	This parameter defines the maximum duration for a request before cancellation. It sets the timeout for each single API call to Business Central. Its value can't exceed the timeout enforced on the Business Central service, which is currentæy 10 minutes (00:10:00). Setting this parameter helps manage system resources effectively and prevents long-running queries from impacting overall system performance. Users experience minimal delays and interruptions, ensuring a smoother workflow.	00:08:00	OData.Feed
UseReadOnlyReplica	This parameter determines whether requests target the primary database or a read-only replica. Offloading read operations from the primary database can significantly boost performance. Setting this property leads to faster data retrieval and improved system stability, especially during peak usage times.	true

Configure the advanced parameters

1. Start [!INCLUDE powerbi-desktop-name].

2. Complete the step that suits your scenario:

   Edit existing report

   1. Select File > Open.
   2. Browse for and select the report (.pbix).
   3. In the ribbon, select Transform Data to open the Power Query Editor.

   Create new report

   1. In the ribbon, select Get Data. If you don't see Get Data, select the File menu, then Get Data.
   2. On the Get Data page, select Online Services > Dynamics 365 Business Central > Connect.
   3. In the Navigator window, select the API endpoint that you want to load data from.
   4. Select Transform Data instead of Load as you might normally do. This step opens Power Query Editor.

   ---

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

4. In Advanced Editor, locate the line that starts with Source =:

   Source = Dynamics365BusinessCentral.ApiContentsWithOptions(null, null, null, null),
   

5. In the line, replace the fourth parameter of Dynamics365BusinessCentral.ApiContentsWithOptions with a comma separated list of properties and values you want to set, for example:

   Source = Dynamics365BusinessCentral.ApiContentsWithOptions(null, null, null, Dynamics365BusinessCentral.ApiContentsWithOptions(null, null, null, [UseReadOnlyReplica = true, Timeout = Duration.From("00:07:00"), ODataMaxPageSize = 10000, AcceptLanguage = "it-it"])
   

6. Select Done to close Advanced Editor.

7. Select Close & Apply to save the changes and close Power Query Editor.

Fixing problems

"Expression.Error: The environment 'Production' does not exist." error when specifying a Business Central environment

APPLIES TO: [!INCLUDE prod_short] online

When you connect to [!INCLUDE prod_short] online from [!INCLUDE powerbi-name], or when you install a [!INCLUDE powerbi-desktop-name] app from Microsoft AppSource that uses [!INCLUDE prod_short] data, you might be prompted to input the [!INCLUDE prod_short] environment you want to connect to.

If you get an error similar to "Expression.Error: The environment 'Production' does not exist.", follow these steps to troubleshoot:

1. Make sure you're using the right credentials to access [!INCLUDE prod_short]. These credentials might not be the same credentials you use to access [!INCLUDE powerbi-name]. How do I change or clear the account I'm currently using to connect to Business Central from Power BI Desktop?
2. If your environment is an embedded ISV environment, you need to specify the embedded ISV name in parenthesis as part of the environment name. For example, if you want to connect to an environment named Production from the embedded ISV named Fabrikam, you must specify "PRODUCTION (fabrikam)" as environment name.

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

APPLIES TO: Business Central online

By default, reports that use [!INCLUDE prod_short] data connect to a read-only replica of the [!INCLUDE prod_short] database. In rare cases, depending on the API design, you might get an error when you try to connect to and get data from the API. The error looks like this:

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

If you're using a custom API, we recommend you rework the AL code to make sure it doesn't make database modifications when it's just reading data. But in case your scenario requires it, you can configure the connector to use a read-write connection instead.

Related information

Enabling Your Business Data for Power BI
Business Intelligence
Getting Ready for Doing Business
Importing Business Data from Other Finance Systems
[Setting Up [!INCLUDEprod_short]](setup.md)
Finance
Quickstart: Connect to data in Power BI Desktop

[!INCLUDEfooter-include]