Edit

Analysing Report Data with Excel and XML

  • Article

APPLIES TO: Business Central 2021 release wave 2 and later

As a developer or advanced user, it helps to inspect the data that is generated for a given report dataset while you create new reports or modify existing ones. To support this capability, you can export a report dataset as raw data to an Excel workbook or XML file—directly. In Excel, for example, you can then do ad-hoc analysis of the data and diagnose issues.

Get started

To export a report dataset to an Excel workbook or XML file, open the report in the client, then in the request page, select Send to > Microsoft Excel Document (data only) or XML Document. The file will be downloaded to your device.

More about Excel (data only)

Microsoft Excel Document (data only) option exports the report results and the criteria that was used to generate them—but it doesn't include the report layout. The Excel file will include the full dataset, as raw data, arranged in rows and columns. All data columns of the report's dataset are included, regardless of whether they're used in the report layout.

Once you have the Excel file, you can start analysing the data. For example, you could filter the data and use Power Pivot to display it.

Each time you export results, a new worksheet is created. Using the Microsoft Excel Document (data only) option, you can run the same report and reuse formatting changes. For example, for Power Pivot, you can run the report again for another time period, copy the results to the worksheet, and then refresh the worksheet. You can also find a reporting app on AppSource.

Note

You can't export a report that has more than 1,048,576 rows or 16,384 columns. With Business Central on-premises, the maximum number of exported rows might be even less. Business Central Server includes a configuration setting, called Max Data Rows Allowed to Send to Excel, for decreasing the limit from the maximum value. For more information, see Configuring Business Central Server or contact your administrator.

For administrators

  • Microsoft Excel Document (data only) was introduced as an optional feature in the 2021 release wave 1, update 18.3. To give users access to this feature when running 2021 release wave 1, enable the Save report dataset to Microsoft Excel Document feature update in Feature Management. For more information, see Enabling Upcoming Features Ahead of Time. In 2021 release wave 2, this feature became permanent, so you won't have to enable it.

  • To use Microsoft Excel Document (data only), user accounts need the Allow Action Export Report Dataset To Excel permission. You can give users this permission by assigning either the Troubleshooting Tools or Export Report Excel permission set. For more information, see Assign Permissions to Users and Groups

For developers and advanced users

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.

For more information about report design, see Report Overview.

See Also

Working with Reports
Managing Report and Document Layouts
Assign Permissions to Users and Groups
Working with Business Central

Find free e-learning modules for Business Central here