Excel templates (.XLS or .XLSX) can be configured to source their data from multiple User Reports. This allows for several blocks of autonomous data to be populated within the same document, with each block of data sourced from its own User Report.
Click here for a sample Excel file.
For example, the output on the left-hand side of the screenshot uses 2 User Reports. In the top right corner, the Excel fields are named to include the user report's alias value more details are provided later in the document. In the bottom right corner, the two reports: "Merge CE - Header Section" and "Merge CE - Repeating Rows" are given aliases of Report1 and Report2 respectively on the Merge Document screen to perform the Excel Merge.
After an Excel document is uploaded and saved on the Merge Document screen, the User Reports section is displayed when the User Report radio button is selected as the Merge Data Source. For each User Report used to populate the excel merge document, add the details by clicking the
button. Use the User Report type ahead drop-down list to specify the user report name and populate the Alias for Merge field for each report. The Alias for Merge field cannot contain non-alphanumeric characters except for underscores.
Note:
1. When a single user report is used as a merge source, even though an alias must be specified in the User Report section in order to save the merge document record, the alias is not required when naming Excel cells. See how to configure an excel document using a single merge data source.
2. When multiple merge sources are used, all fields named using the Name Manager must include an alias as part of the naming of a cell or range of cells. Not doing so will result in the error message "An unexpected error occurred rendering the report." and prevent the merge document from being created.
The below screenshot shows the Data Fields and Filter used for the user report with the alias value of Report1. The data fields in this report will be used to populated the top part of the merge document which consists of a single non repeating lines.
Fields are named in Excel using the Name Manager feature by specifying the name and the cell or range of cells to be named. The cells in the top part of the merge document use the format: EQ_alias.fieldColumnCaption e.g. EQ_Report1.FirstName. The field name must be prefixed with the report alias followed by a period ".".
The below screenshot shows the Data Fields and Filter used for the user report with the alias value of Report2. The fields in this report will be used to populate the repeating lines section of the report. In addition to repeating lines, headers, subtotals, and grand totals are used and are based on a key field column caption in the user report - Section.
Each part of the repeating lines section will be explained in terms of how cells and range of cells are to be named when more than one user report is used.
Header
Name the range of cells C11:E11 as "EQ_HEADER_ROW_" followed by the report alias, a period (.) and the key field column caption - EQ_HEADER_ROW_Report2.Section
For the details field defined in EQ_HEADER_ROW_Report2.Section, name the individual field using the format "EQ_HEADER_FIELD_" followed by the report alias, a period (.) and the key field column caption. This will result in the value of the key field for the group to be written to the header.
EQ_HEADER_FIELD_Report2.Section for cell C11.
Repeating Details Rows
Name the range of cells C12:E12 as "EQ_DETAIL_ROW_" followed by the report alias, a period (.) and the key field column caption - EQ_DETAIL_ROW_Report2.Section. This causes the output document to write a header, details section, and sub-total in the output for each key value in the data source.
For the two detail fields defined in EQ_DETAIL_ROW_Report2.Section, name the individual field(s) using the format "EQ_" followed by the report alias, a period (.) and the column caption returned by the merge data source.
EQ_Report2.LineItemName for cell C12
EQ_Report2.AmountTotal for cell E12
Sub Total
Name the range of cells C13:E14 as "EQ_SUBTOTAL_ROW_" followed by the report alias, a period (.) and the key field column caption - EQ_SUBTOTAL_ROW_Report2.Section. This sub-total will be written for each value of the key field, Section in this case. To include a blank row between the Section Sub Total and the following section, include the blank cells beneath the Section Sub Total when naming the range.
Details for the sub-total row can be defined using fields that are summed. For the details field defined in EQ_SUBTOTAL_ROW_Report2.Section, name the individual field(s) using the format
"EQ_DETAIL_SUB_TOTAL_" followed by the report alias, a period (.) and the column caption returned by the merge data source - EQ_DETAIL_SUB_TOTAL_Report2.AmountTotal
If you wish to have different multi-line Sub Total names, the Sub Total field can be name as EQ_SUBTOTAL_FOOTER_" followed by the report alias, a period (.) and the column caption that contains the value (see screenshot of user report with the alias value of Report2 for the logic used for the Custom Calc field).
EQ_SUBTOTAL_FOOTER_Report2.SectionName for cell C13
Grand Total
Name the range of cells C15:E15 as "EQ_GRAND_TOTAL_ROW" followed by the report alias, a period (.) and the key field column caption - EQ_DETAIL_ROW_Report2.Section.
For the details field defined in EQ_GRAND_TOTAL_ROW_Report2.Section, name the individual field(s) using the format "EQ_" followed by the report alias, a period (.) and the column caption returned by the merge data source:
EQ_DETAIL_GRAND_TOTAL_Report2.AmountTotal for cell E15
