Use new dedicated APIs for financial reporting

  • 4 minutes

Since Business Central 2022 Wave 2, a new set of APIs are available or financial reporting.

The new APIs and datasets consist of data for:

  • General Ledger data, including G/L budgets and Dimensions

  • Accounting Periods and Business Units

  • Customers, Vendors, and Ledger entries, including detailed Ledger entries

  • Webhook subscriptions and entity definitions

The financial reporting APIs are:

  • accountingPeriods

  • businessUnits

  • customerLedgerEntries

  • customers

  • detailedCustomerLedgerEntries

  • detailedVendorLedgerEntries

  • dimensionSetEntries

  • dimensionValues

  • generalBudgetEntrys

  • generalLedgerAccounts

  • generalLedgerBudgets

  • generalLedgerEntries

  • vendorLedgerEntries

  • vendors

With these APIs, it's easy to get data from Business Central for financial reporting in Excel, Power BI, or other reporting tools.

The APIs can be found under Advanced APIs and are called reportsFinance/beta:

Screenshot of Navigator with Display Options expanded to reveal the advanced APIs.

With dedicated APIs for finance reporting, it's easier to create your own Excel-based financial reports. You can also consume the APIs in Power BI through the Business Central Power BI Connector.

The extension API Reports - Finance is available by default in Business Central. You can find it in the Extension Management page:

Screenshot of the Extension Management page with API Reports Finance selected.

To work with the new finance APIs in Power BI, follow these steps:

  1. In Power BI Desktop, select Get data, More.

  2. Select Online Services, Dynamics 365 Business Central.

  3. If you're asked to authenticate, then sign in with your Business Central credentials (email/password).

  4. The Navigator window opens.

  5. Select your Environment, then Company.

  6. Expand the Advanced APIs, and look for the microsoft/reportsFinance/beta folder.

    Screenshot of the advanced APIs expanded to show the beta folder.

  7. Now select the APIs you want to use in Power BI, then Load or Transform Data.

To work with the new finance APIs in Power BI dataflows, follow these steps:

  1. Open your Power BI workspace.

  2. Select New, Dataflow.

    Screenshot of Power BI New menu expanded to show Dataflow selected.

  3. Select Add new tables.

    Screenshot of Define new tables showing the Add new tables button.

  4. In the Choose data source window, select Dynamics 365 Business Central.

    Screenshot of the Choose data source window.

  5. In the Connection settings window, select Next.

    Screenshot of the Connection settings window.

  6. In the Choose data window, expand your Environment, Company, Advanced APIs, microsoft/reportsFinance/beta:

    Screenshot of the Choose data window with the APIs expanded.

  7. Now select the APIs you want to import and select Transform data.

Microsoft Excel currently doesn't have a connector to connect to the Business Central APIs. However, Excel can connect to Power BI Datasets and/or Power BI Dataflows.

So if you have created Power BI Dataflows that harvest the Business Central APIs, you can connect to them in Excel.

To work with the new finance APIs in Excel, follow these steps:

  1. Open Microsoft Excel.

  2. In the Data tab, select Get Data, From Power Platform, From Dataflows:

    Screenshot of Excel showing the Get Data menu option expanded to show From Power Platform and From Dataflows.

  3. The Navigator window opens.

  4. Expand Workspaces and then expand the workspace you created earlier.

  5. The workspace now shows you the dataflow(s) you created earlier, consuming the new Finance APIs:

    Screenshot of the workspace showing the dataflows consuming the finance APIs.

  6. Select Load or Transform Data to import the data in Excel.