Connecting Excel to Business Central is getting even easier in the 2024 wave 2 release (BC25). The biggest change from my original blog post – no more hardcoding Tenant Ids, and we even have some help with named formulas to make development even faster!

Excel Reports are extremely useful when you need a detailed dataset. They not only provide a denormalized dataset, but also facilitate the creation of meaningful data connections, pivot charts, visualizations and more. A notable drawback has been the cumbersome process of generating an Excel Report from Business Central. This involves running a report request page, downloading the file, then opening and enabling the workbook before you can access any data. Moreover, once the report is generated, you are left with a static dataset that lacks a refresh option. This means the data remains unchanged from the moment the report was run.

In Business Central 23.3 we were given the ability to create refreshable datasets by calling Business Central APIs from within Excel. Here is a deep dive into how you can create a refreshable Excel Report Layout — all you need is a Business Central API and a Report.

[NOTE]: This blog post was written before the release of BC25 (2024 release wave 2). Extensive improvements have been made to make connecting to APIs through Excel Report Layouts easier. So if you are working with BC25, please check out this blog post