The long awaited Report Inspector is Save report dataset to Excel in BC 18.3!

Yes! It’s here! The long awaited Report Inspector in Business Central.

Don’t know what I’m talking about, well let me bring you up-to-date:

Microsoft Idea · Report Inspector (dynamics.com)

And now, with the release of Update 18.3 for Microsoft Dynamics 365 Business Central online 2021 release wave 1, we have these 2 amazing features:

Get report (raw data) into Excel from request page (targeting end users)
Save report dataset to excel from the request page (targeting developers)

The new features are controlled by a feature flag, so opt-in for upgrading customers and opt-out for new customers, as well as permission/permission sets.

So how can you try it out?

Well, first you need a Business Central 18.3 environment, and for that purpose I created this new sandbox today:

After spinning up the new sandbox, I then went to the Feature Management page:

and yes, the new feature was there, and enabled: Export report dataset to Microsoft Excel Document.

I then went to the Posted Sales Invoices, opened the first one in the list and selected the Print button. In the popup then selected Send To and there it is:

An Excel file downloads:

and in the Excel file I can see the dataset, including empty and null values!

A cell can be empty because there’s no data in the column (field), and can be NULL because there’s an empty row, like in a left-outer-join.

Each time you export results, a new worksheet is created.
User accounts will need the Allow Action Export Report Dataset To Excel permission, which you can apply by using the Troubleshooting Tools permission set or Export Report Excel permission set.
You can’t export a report that has more than 1,048,576 rows or 16,384 columns.

The Microsoft Excel Document (data only) option exports all columns, including columns that hold filters and formatting instructions for other values. Here are a few points of interest:

Binary data in a field, like an image, isn’t exported.
In columns that hold binary data, fields will include the text Binary data ({0} bytes), where {0} indicates the number of bytes.
Starting in Business Central 2021 release wave 2, the Excel file also includes the Report Metadata worksheet.
- This worksheet shows the filters applied to the report and general report properties, like the name, ID, and extension details.
- The filters are shown in the Filter (DataItem::Table::FilterGroupNo::FieldName) column.
- The filters in this column include filters set on the report’s request page.
- It also includes filters defined in AL code, for example, by the DataItemLink property and DataItemTableView property.

I also tested the feature before, on a docker ctp, and it also works with report extensions!