Create customized data analysis report

If the Data Analysis module is enabled for your company and you have the right Admin - Data Analysis, you will be able to create customized report templates for downloading and analyzing transaction data.

To create a customized data analysis report:

1. From the main menu, select Administration > Data Extract Management > Data Analysis.

   The Data Analysis screen appears, defaulting to the General tab.

2. Type a name for your template in the Template Name field.
3. Type a name for your report template in the File Name field.

   Note: When the report is run, Shared Travel Services automatically adds a date stamp to the end of the file name.

4. Select an output file type for your report from the File Type drop-down box.

   Note: The file types .xls and .xlsx (default) can support a maximum of 65,536 and 999,999 rows, respectively. Any additional rows will be truncated, and a warning message will appear to confirm the action.

   If you select .txt or .csv, the Format Amounts to 2 Decimal Places check box appears. If you are creating a new extract, the check box is selected by default.

5. If you select .txt as the file type, select what delimiter will be used to separate downloaded information.
6. Select how you want dates to appear in your report from the Date Format drop-down list.
7. Select how you want the report to communicate if a value is in credit or debit from the Debit/Credit Indicator drop-down field.

   Note: If you plan to add the Transaction - Debit/Credit Indicator field to your report template, select either D/C or DR/CR. Otherwise, select +/-.

8. If you want to repeat values, where possible, across multiple rows of transactions in the extracted report, select the Repeat Fields check box.
9. If a certain option has been enabled for your company and you have the right Admin - Data Analysis File Delivery, you will be able to select if you want your report placed in the Vault (the default location) or sent to a secure, pre-configured external location.
10. To define the data you are reporting on, select the relevant search parameters from the Standard Filters fields.

    Note: As you select values across each of the three tabs (General, Fields and Advanced Filters), those values display in the vertical Active Filters panel on the right side of the screen.

11. Click the Fields tab.
12. Select a field category from the Field Category drop-down list. The fields belonging to that category automatically display for your selection, and the selection is not limited to one category; this is merely a grouping of items in the Available fields list.
13. Click anywhere on a field line from the Available fields list to add it to your list of Selected fields for inclusion in your custom extract. It is not necessary to click directly on icon.

    Note: The Select all link selects all the fields in the category. If a field is added to the selection, it is highlighted in grey to indicate inclusion. A field line can be added more than once to the selected fields; this may be useful for concatenated lists.

14. Select as many fields as you need from as many field categories as you need. Note: You can include a maximum of 256 fields in your custom report.
15. To change the positioning of a field (column) in the report, click beside the field and drag it to its new location.
16. If required, click beside a selected field to remove it from your report. To remove all fields and start over, click Remove All. Note: remove fields cannot be undone.
17. To edit the properties of a field you've selected to appear in your report:
    1. Click beside the field line item. The Edit Field Properties dialogue box appears.
    2. If required, change the name of the column header that will appear in the report (note: this does not change the name of field as it displays within the Data Extract Management module) and (optionally) define the maximum number of characters that will display as data output within the column.
    3. Click Save.
18. To insert a blank field into your report:
    1. Select User-Defined from the Field Category drop-down box.
    2. Click beside Blank Field, then click and define a name for the column header that will appear in the report. Note: Do not define a maximum field length.
19. To insert a customized field into your report:
    1. Select User-Defined from the Field Category drop-down box.
    2. Click beside Custom Field, then click and define a name for the column header that will appear in the report and (optionally) define the maximum number of characters that will display as data output within the column.
20. To join (concatenate) two or more fields of data into a single column of a data analysis report:
    1. Click Concatenate.

       The Concatenate Fields dialogue box appears.

    2. Enter the name of the column header for the concatenated fields that will appear in the extract.
    3. Select the Fields to be joined within the named column. Only fields available in the selected fields window can be added and are chosen by checking the check box beside each field in the list. These fields cannot be reordered here; however, they can be reordered in the selected fields panel after the concatenated group is defined.
    4. The separator for the selected fields is defined by selection from the Field Separator drop-down list. Note: If a field has no data, e.g. first name, middle name and last name are in the group but there is no middle name, it will be substituted with a space unless No separator is selected.
    5. (Optionally) define the maximum number of characters that will display as data output within the grouped column. Note: If the joined fields exceed this character length, some may be omitted from the extract.
    6. Click Save.
    7. The concatenated group will appear in the selected fields panel and can be rearranged, edited and removed as with normal fields.

       Note: The concatenated fields are indented to indicate that they will be joined into a single column in the extract.

       If the group header is removed, all the selected fields will be removed too.

       A concatenated group cannot be added to another group.

21. Click the Advanced Filters tab and, if required, select any additional search parameters.

    Note: The advanced filter options that display depend on the selected report mode (account or employee).

22. To save your report template, click Save.
23. To run the report, click Run Data Analysis.

    By default, your file is placed in the Vault. (Note: If personal vaults are enabled for your instance, the extract is placed in your personal vault.)