Scenario: "SMART Generic Excel Report"
Extension – SMART Generic Excel Report, is designed to be used together with the "SMART Data Template" extension.
The SMART Generic Excel Report add-on is an extension for Microsoft Dynamics 365 Business Central that enables the generation of Excel reports based on created data templates. It uses mappings between variables in the data template and Excel report cells — without writing any code or involving a developer.
To generate Generic Excel reports, follow these configuration steps.
1. Creating a Generic Excel Report
Page: Generic Excel Reports
You need to create a new generic Excel report by filling in the following fields:
| Field | Description |
|---|---|
| Code | Unique identifier of the report. |
| Description | Description of the report. |
| Excel Template Code | Select the Excel Template to be used for generating the report. The template must be pre-created with all relevant cells and named ranges identified for data mapping from the data templates. |
| Main DataItem Table Id | ID of the root table. |
| Main DataItem Table Name | Name of the root table (auto-filled). |
| Data Template Code | Select the data template on which the report will be based. The data template must include all variables that will be exported into the Excel report. For a detailed guide on creating and configuring a data template, refer to the SMART Data Template scenario. |
2. Creating Report Sections
For the selected Excel template, you need to add sections in the Sections table part.
| Field | Description |
|---|---|
| Sheet Name | Name of the sheet in the Excel file selected in the Excel Template Code field — must be chosen from the dropdown list. |
| Section Name | Name of the corresponding range in the Excel template — must be selected from the dropdown list. All ranges must be pre-created in the Excel template defined in the Excel Template Code field. Examples include HEADER, LINE, FOOTER. |
| Repeating | Set to Yes for sections in the Excel report that should repeat. |
3. Configuring Cell Mappings for Excel
For each cell in the Excel report that should receive data from the system, you must define a mapping with the variables from the data template specified in the Data Template Code field. To configure this mapping, on the Generic Excel Report card, for each section, go to the Gen. Excel Report Mapping page by clicking the Mapping button on the row for the section.
On the Gen. Excel Report Mapping page, you can use the Suggest data items function to select cells from the Excel template that need mappings (cells from the Excel template will be created automatically when user import Excel template to the system).
| Field | Description |
|---|---|
| Data Item Name | Name of the cell in the Excel template. |
| Data Type | Define the format in which data will be exported to Excel, e.g.: Text (text format); Date (date format); Decimal (numeric format); Boolean (Yes/No). |
| Number Format | Define the numeric format to be applied to the exported values. |
| Mapping Data Code | Specifies the variable from the data template used in this generic report that should be exported into the defined Excel cell. |
4. Running the Report
To run the report, go to the Generic Excel Report page and trigger the Run Report action.
5. Report Selection for HRP Report Selection
The created Generic Excel Report can be selected on the HRP Report Selection page for a specific document type. This allows the report to be generated from the corresponding source. For example, a Generic Excel Report can be created for a Vacation order, along with an Excel template, a data template, and the configured generic report. After that, the report 72835670 can be specified on the HRP Report Selection page for the Vacation Order, and the required layout can be selected. When printing from the vacation order, it will then be possible to generate this report in Excel according to the configured Generic Excel Report template.