The comprehensive guide to using Business Central Excel Report Metadata with refreshable APIs

Dynamics 365 Business Central

Excel Reports are extremely useful for detailed datasets, denormalized datasets, data connections, pivot charts, and visualizations.
Generating an Excel Report from Business Central was previously cumbersome, involving multiple steps before accessing data.
In Business Central 23.3, the ability to create refreshable datasets by calling Business Central APIs from within Excel was introduced.
The process involves running a report, sending it to Excel, and utilizing the Aggregated Metadata worksheet for crucial information.
Advanced Power Query Editor is used to construct dynamic URLs for connecting Excel to an API, requiring company and environment information.
Privacy levels are set to Organizational, and refreshing data from Business Central in Excel is faster and more efficient.
Custom APIs can also be used by constructing URLs with variables from the ReportRequestValues table and the published API.
Resources like Copilot and online tutorials are helpful for creating and utilizing refreshable APIs for Excel reports connected to Dynamics 365 Business Central.

Registered users can view the full text for FREE!