Create an Excel merge document (.XLS or .XLSX) that contains Named cells that will be replaced with data from the merge data source.
In Excel, select Formulas tab and click on the [Name Manager] button.
Click the [New..] button to add a new Name Manager.
Specify which cell it refers to by clicking on the cell in the spreadsheet. Assign a name by entering the merge tag name. The merge tags must begin with "EQ_" followed by the column name returned by the merge data source. In the example below, the merge data source used is a User Report.
Note: The merge tag cannot contain any spaces, hyphens, or other special characters disallowed by Excel Named cells. When creating column captions in User Reports, do not use spaces such as "First Name". Instead use "First_Name" or "FirstName" and the merge tag will be "EQ_First_Name" or "EQ_FirstName" respectively.
Besides using the New Name pop up window to assign names to your cell, you may also name a cell by double clicking on a row. This opens the Edit Name pop up window.
In the screenshot below, cell C1 of Sheet 1 is named EQ_Company. Note, to make any changes to the Name of a named cell, always do this via the Name Manager window.
Repeating for multiple records
Excel merge documents also support repeating sections, or detail lines. These details can be totaled, as well as broken into sections based on a key field and sub-totaled. If using more than one single merge data source, please see How to configure an Excel merge document using multiple user reports where naming of cells are slightly different but the principles of naming repeating detail lines, headers, sub totals and grand totals.
All 3 examples that are covered below are based on a single user report run against a Cost Estimate with the following data fields and filter.
Example 1: Repeating details rows only
Click here for a sample Excel file.
Each repeating details row in the data source that populates merge document consists of 2 details fields: Line Item Name and Amount Total.
To achieve the above sample result of the output when run against a calculated Cost Estimate, enter the following entries need to be setup in the Name Manager
Name the range of cells C5:E5 as EQ_DETAIL_ROW
For the two detail fields defined in EQ_DETAIL_ROW, name the individual fields using the format "EQ_" followed by the column caption returned by the merge data source:
EQ_LineItemName for cell C5
EQ_AmountTotal for cell E5
Example 2: Repeating details rows with a grand total
Click here for a sample Excel file.
.
To achieve the above sample result of the output when run against a calculated Cost Estimate, enter the following entries in the Name Manager in addition to the ones set up in Example 1.
Name the range of cells C7:E7 as EQ_GRAND_TOTAL_ROW
For the details field defined in EQ_GRAND_TOTAL_ROW, name the individual field using the format "EQ_" followed by the column caption returned by the merge data source:
EQ_DETAIL_GRAND_TOTAL_AmountTotal for cell E7
Example 3: Repeating details rows with subtotals, headers and a grand total
Click here for a sample Excel file.
The above sample result of the output when run against a calculated Cost Estimate shows the details from the underlying data sources split into multiple groups base on the value of a field in the data source In this case, the key field is the Cost Estimate Line Item Section field as has a column caption of Section.
To achieve the above sample result of the output, enter the following entries in the Name Manager. There are four parts to cover: Header, Details Section, Sub Total and Grand Total
Header
Name the range of cells C4:E4 as "EQ_HEADER_ROW_" followed by the key field column caption - EQ_HEADER_ROW_Section
For the details field defined in EQ_HEADER_ROW_Section, name the individual field using the format "EQ_HEADER_FIELD_" followed by 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_Section for cell C4
Repeating Details Rows
Name the range of cells C5:E5 as "EQ_DETAIL_ROW_" followed by the key field column caption - EQ_DETAIL_ROW_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_Section, name the individual field(s) using the format "EQ_" followed by the column caption returned by the merge data source.
EQ_LineItemName for cell C5
EQ_AmountTotal for cell E5
Sub Totals
Name the range of cells C6:E7 as "EQ_SUBTOTAL_ROW_" followed by the key field column caption - EQ_SUBTOTAL_ROW_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_Section, name the individual field(s) using the format
"EQ_DETAIL_SUB_TOTAL_" followed by the column caption returned by the merge data source - EQ_DETAIL_SUB_TOTAL_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 column caption that contains the value (see screenshot of user report for the logic used for the Custom Calc field).
EQ_SUBTOTAL_FOOTER_SectionName for cell C6
Grand Total
Name the range of cells C8:E8 as "EQ_GRAND_TOTAL__ROW" followed by the key field column caption - EQ_DETAIL_ROW_Section.
For the details field defined in EQ_GRAND_TOTAL_ROW_Section, name the individual field(s) using the format "EQ_" followed by the column caption returned by the merge data source:
EQ_DETAIL_GRAND_TOTAL_AmountTotal for cell E8
