Sometimes when you’re analyzing data, you want a report. Sometimes it would be easier to have the actual data. Exporting reports from ERP / Accounting systems to Excel has been possible for a long time, but the data you wind up with in Excel is often ugly. Because it is formatted with totals and subtotals, not to mention spacing, lines, titles on every page, etc., it can be harder to use than just rekeying the data.
Microsoft added the capability to export the data behind the report a couple of versions ago (it was optional in 2021 Release Wave 1, it appears for all users in 2021 Release Wave 2). It’s easier to demonstrate than discuss, but here are the steps followed by a video.
- Select the report you want to export to Excel. Complete the filters just as you would if you were going to print the report.
- Select “Send to…” and choose the option “Microsoft Excel Document (data only)”
- Open the document in Excel and remove any data you don’t need.
Note that you’ll also see data here that appears as literal text on the report such as “Aged Customer Balances” in the example below. Business Central also sends text fields to the report formatter along with the data. Just ignore this information and hide or delete it from your worksheet.
Here’s a video demonstrating this feature.