Create a New Report Template Using User-Defined Fields

Updated 2 years ago by Dean Sallinen

This procedure is written for Microsoft Excel 2010. The steps may be different in other versions of Excel.

To create a new Excel report template using a custom column setup you must start with one of the existing templates from the template library and configure it to use a custom column setup.

Download the template

In Maximizer, navigate to Administration > Template Library .

Download an existing Excel Report Template and open it in Excel.

Set the data source

Open the relevant Maximizer module for your report (e.g. Address Book) and click View > Column Setup. 

Choose the column setup you would like to use for the report template. You can use an existing column setup or create a new one specifically for this report. Once you've selected your column setup, click Use Now.

Click the Export to Excel icon to export the current list of entries from Maximizer using the selected column setup. Open the file in Excel.

Copy the exported data, including the column names, to the Data Source sheet of your previously downloaded template file.

Update the Report Template sheet

In the Template file, right-click on the sheet tabs at the bottom of the Excel window and select Unhide. Choose the Rpt Template sheet and click OK to unhide the sheet.

Copy the headers from Row 1 of the Data Source sheet to Row 6 of the Rpt Template sheet. These are the same headers you would have selected as the fields in your Column Setup.

Using a reference formula (e.g. ='Data Source'!A2), fill in the values for Row 7 of the Rpt Template sheet.

The values of the cells must be copied from their corresponding columns in Row 2 of the Data Source sheet. For example, in Cell A7 we would use the formula ='Data Source'!A2 to copy the value from A2 of the Data Source sheet. This formula can then be copied to each cell in the row and Excel should update the cell references automatically.

If done correctly, Row 6 and 7 of the Rpt Template sheet should now look like Row 1 and 2 of your Data Source sheet.

Modify the Configuration sheet

In the Template file, right click on the sheet tabs at the bottom of the Excel window and select Unhide. Choose the Configuration sheet and click OK to unhide the sheet.

Copy the headers from Row 1 of the Data Source sheet to Row 1 of the Configuration sheet. 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.

If done correctly, Row 1 of the Configuration sheet should now match Row 1 of your Data Source sheet and Row 6 of your Rpt Template sheet.

In the Configuration sheet, in the Parameters section, specify a new value for Template Last Column. This is the label of the rightmost column in the Rpt Template sheet. For example, if you have 31 columns in your Column Setup, and your Rpt Template sheet goes to column AE, your Template Last Column value will be AE.

In the Sheets To Hide field, specify the sheets you would like hidden in the report template. The defaults include: Rpt Template, Configuration, and Help

Test the report

Save the file and on the Configuration sheet click the Test Report Generation button.

The test is run using sample data from the Data Source sheet.

Once finished, you can click Add Template in the Template Library in Maximizer and upload the modified file.

How did we do?