Create a New Report Template Using User-Defined Fields

Updated 1 year ago by Celina Kwan

Creating a new Excel report template using a custom column setup involves configuring the columns in the column setup as the data source for the report template. You can then add and modify individual reports within the Excel report template spreadsheet as you would for an existing Excel report template.

To create a new Excel report template using a custom column setup you must use one of the existing report templates from the template library as the basis for your new report template.

  1. Download an Excel report template from the Template Library to use as the basis for your new report template and open the spreadsheet in Excel.
This procedure is written for Microsoft Excel 2010. The steps may be different in other versions of Excel.
  1. Open the Maximizer module you are creating the report template for.
  2. Click View > Column Setup to select a column setup for the report template.

You can use an existing column setup that contains all of the columns to be used in the report template or you can create a new column setup. This column setup must be available to select when generating the report, so you should create a dedicated column setup for the report template.

  1. Select the Column Setup that you want to use for the report template and click Use Now.
  1. Click the Export to Excel icon to export the current view.
  1. In Excel, copy the exported data, including the column names, to the Data Source sheet in the Excel spreadsheet you are using for your template.

Copy:

Paste:

Right click on the sheet tabs at the bottom of the Excel window and select Unhide.

The Unhide dialog box opens.

  1. Select the Rpt Template sheet and click OK.
  1. Copy the names of the exported fields from row 1 of the Data Source sheet to row 6 of the Rpt Template sheet.
  2. Specify the values of the cells in row 7 of the Rpt Template sheet.

The values of the cells should be copied from the corresponding columns in row 2 of the Data Source sheet using a formula. For example, in the cell in row 7 of column A, use the formula ='Data Source'!A2 to copy the value from the cell in row 2 of column A in the Data Source sheet. This formula may be copied to each of the cells in the row and Excel will update the cell references automatically.

  1. Right click on the sheet tabs at the bottom of the Excel window and select Unhide.

The Unhide dialog box opens.

  1. Select the Configuration sheet and click OK.
  1. In the Configuration sheet, specify a value for the Template Last Column field.

The Template Last Column field should contain the Excel column label of the rightmost column in the Rpt Template sheet.

  1. Copy the names of the exported fields from row 1 of the Data Source sheet to row 1 of the Configuration sheet.

Copy:

Paste:

The values in this row are used to verify that the selected column setup contains the same columns as those used in the report template. An error message is displayed if these values do not match the selected column setup when generating the report.

  1. In the Sheets To Hide field, specify all unnecessary sheets to be hidden in the report template.
  1. Save the file and click Test Report Generation.

The report generation is tested using the data in the Data Source sheet.

You can now add and modify the individual reports in the Excel report template spreadsheet as you would for an existing Excel report template and upload the new report template to the Template Library in Maximizer.


How did we do?