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.
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.
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:
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:
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 cust_data.id 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: