This help article describes the structure of the report template and explains which components Click Travel requires to be able to generate reports.
A report template is an Excel workbook with an expected format. The workbook must be in .xlsx format, and is compatible with Excel 2007 onwards. We do not support the older .xls format.
A report template is made up of:
A RawData sheet that generates the data that is output in the report.
A suite of Analysis sheets that contain pivot tables.
Each column has the format shown below.
RawData sheet
Row 1: column headers
Row 1 of the RawData sheet contains the Row1 column headers.
The Row 1 column headers correspond to the Travel Analysis and Travel Billing data fields that generate the raw data in reports.
The Row 1 column headers are case sensitive. The standard Travel Analysis and Travel Billing column headers are all lowercase. However, custom data column headers may contain both uppercase and lowercase, depending on how the ID is set up in Team Admin.
IMPORTANT
Please do not edit or delete any of
the Row 1 column headers.
Row 2 onwards: RawDataRange
Row 2 and the rows that follow are part of a named range called the RawDataRange. The RawDataRange ensures that when you generate a report, we insert the report data in the expected place in the workbook.
Row 2: RawDataRange 'friendly' headers
Row 2 is the top row in the RawDataRange. Row 2 contains the RawDataRange 'friendly' headers:
You can edit the Row 2 'friendly' headers if you wish. Use them to provide your own brief user-friendly description of the data in the column.
You can also use the 2nd row 'friendly' headers to make your own pivot tables.
Row 3 onwards: RawDataRange dummy data
The RawDataRange provides dummy data that enables the pivot tables to work:
Starting with Row 3, there is one row of dummy data for each travel type:
Each cell contains a dummy value that defines EITHER:
The correct cell number format (e.g. 0.00 or Sales), OR
An Excel formula to use for cells that derive their value from other cells in the same row.
IMPORTANT
The pivot tables are populated automatically.
If you want to keep the pivot tables,
make sure you don't remove any of the
dummy data. (For example, if you use
travel policies, don't remove the
Policy Compliant column.)
TIP
To make your pivot tables etc work, you can add
rows of sample data of your own. Please append
your sample data rows to the dummy data we
provide, rather than deleting our data.
When you generate a report from the template,
we will overwrite your sample data.
Custom data
At the end of the RawDataRange, we provide an extra column named cust_data.
The cust_data column ensures that reports contain any custom data that your team collects for bookings. When you generate a report from the template, we replace the cust_data column with one column for each item of custom data collected.
TIP
The order in which custom data columns are output
can vary. You can get round this by putting the
custom data columns in the order that you want.
An added bonus is that this allows you to
create pivot tables based on the data.
Pivot tables
Each major travel type has a pivot table in a separate sheet on the Travel Analysis report template.
We also include pivot tables for some of the columns in the RawDataRange.
For example, the pivot table on the Travel Policy Analysis sheet (see above) uses the Policy Compliant data from the RawDataRange:
You can also use the 2nd row 'friendly' headers to create your own pivot tables.