All Collections
View reports in Excel
View reports in Excel

This article describes the format of the reports that you download from Click Travel and open in Excel

Hayley Marsden avatar
Written by Hayley Marsden
Updated over a week ago

Click Travel outputs each report as an Excel (.xlsx) spreadsheet.

The exact format depends on whether the report is a Travel Analysis report or a Travel Billing report. In addition, the report template from which the report was produced might have been customised by someone in your team.

Pivot tables

When you open a report produced from the Travel Analysis report template, Excel generates several pivot tables. For example, one of these pivot tables is labelled Travel Policy Analysis:

Reports produced from the Travel Billing report template don't include pivot tables.

Raw data

When you open any Click Travel report in Excel, Excel generates a sheet of raw data. For example, the Travel Analysis report template produces one row of raw data for each travel booking: 

Row 1 contains column headers for the RawDataRange. To find out more about the data under a particular column header, please refer to the Travel Analysis and Travel Billing data fields.

Sometimes, row 1 is blank. This means that a formula is applied to data in this column by using data from other columns. For example, Lead Time is calculated by subtracting the booking_date from the service_date:

Custom data

If your team collects custom data for bookings, the spreadsheet includes custom data at the end of the raw data.

Normally, there is one column for each item of custom data. We construct the column header as:
where id is the custom field's ID in Team Admin, e.g. cust_data.reason-for-travel:

When custom data is collected from a custom field set up as type Select, the report provides an additional column for the labels.

Custom fields with Type set to 'Select'

Custom fields with Type set to Select are a special case. To set this up, someone has to import a 2-column list that contains a label and a code for each item. For example: 

The report provides 2 columns for Select custom data so that both the label and the code can be output.

The codes go into the column as normal. 

The labels go into an extra column, for which we construct the column header as:
where id is the custom field's ID in Team Admin:

Did this answer your question?