Report templates can be as simple as just custom columns and highlighting, but they can also be extensively customized and include data analysis charts and special formatting. When building out report templates you will want to review some of our best practices to ensure that you have an efficient template that will display exactly what you need.

To create an Excel template, you can start by running an ALMS Report with all the fields available, and then you can filter and build your template off of that.  If you build your template this way, be sure to remove all data except for the top/header row and remove the filters worksheet. 

Best Practices

• Place the “Data” sheet on the first worksheet tab (1)

Open

• Do not put any other elements on the Data worksheet. For example, if you need to use a pivot table, chart, or lookup tables, put them on a separate worksheet.

Open

• When adding data, Tables, and Pivot Tables, start them on the first row and column (A1)

• When setting up a Pivot Table, highlight all the column letters where you may have data.

• To ensure your Pivot Table accurately reflects the data that got populated, make sure your Pivot Table is not set to “Retain items deleted from the data source” or “Save source data with file.” In addition, configure your Pivot table to “Refresh data when opening file.”
  
  

• When you save the template, Excel will remember the worksheet and the cell(s) that were selected when you saved. Make sure you save the file the way you want your end users to open the file. For example, if you want you users to open the worksheet to a Pivot Table, save the workbook with that worksheet showing.

• Do not reference other files, for example if using a lookup to another sheet

• Whenever you are removing data such as formulas/functions, make sure to Delete the cells and check for warnings in cells

• Delete extra rows and columns that are not being used

• Slicers can be used on Tables and Pivot tables to filter. Do not place your data slicer under the Table.

• When using CONCATENATE and ‘+’ within a table, change the column/row data type formatting to General

• When using Conditional Formatting on Ranges, set the rules to apply by column(s).

• If you have a static value that you want to be propagated down a column, you will need to turn it into a “formula” as simple as ‘=n’

• If you want to avoid “Blanks” on a pivot table, you can turn your Range into a Table and have the Pivot Table reference the Table

Troubleshooting your Report Template

Issues with Uploading a File

If you encounter any issues uploading your excel file as a report template into Cognition, ensure that you are not using any of the following:

• Data Model

• Grouped Pivot Tables (System unexpected error)

Issues with Generating a File

If you encounter any issues generating your excel file using a custom report template, ensure that you template does not have any of the following:

• Formula use within a Table

• Added columns to a Table, referenced by a Pivot Table that has not been refreshed

• Grouped Pivot Tables ( Sequence Contains no Elements)

Issues with Opening a File

If you encounter any issues opening your generated excel file using a custom report template, ensure that you template does not have any of the following:

• Extra rows in a Table

• Formulas outside of a Range or Table

Things we know are “Unsupported”

Excel has an extensive library of features and functions that can be used to help you analyze data. Cognition can support a variety of those features and functions, but there are some known features that may not be supported. Refer to the following:

• Spill functions should only be used with tables and not ranges

• If your function/formula has curly brackets {} around it, then this function/formula is likely not supported

• If your function/formula has the prefix of “xlfn,” then this is an indicator that your current Excel version does not support the function

• Sorting and filtering on Tables

• Conditional formatting on Table[Column]

• Filtering on Pivot Tables

• Data validation

• Grouped Pivot Tables

FAQ

• How do I get rid of blanks?

You can get rid of blanks by using a Table instead of a Range.

• How do I get rid of #DIV/0! Errors?

If you want to avoid “#DIV?0!” on a pivot table, you can customize the Pivot Table to read a specific error message or to leave it blank. In Pivot Table Options, you can checkmark the 'Error values as: ' setting to leave it bank or you can type in a custom message.

• Can I prevent the “Enable Editing” prompt from showing?

No, you cannot prevent the “Enable Editing” prompt from showing to your end users when they open a report they ran using your custom template. You will need to instruct your end users that this will be part of the process.