Adding an Export Configuration

In Reporting and Analytics, click the side navigation menu, click Reports, click Exports, click Configurations, and then click Add Export Configuration.

Enter a configuration name and then click a creation method.

To add a new configuration, click New.
To copy an existing configuration and then modify it, click Copy, and then select the source configuration.
To copy a core export configuration and then modify it, click Core Exports, and then select a template.

When you copy a configuration or use a core export configuration as a template, you can preview the subject areas and steps (the code for querying the subject areas in the database) for the source and you can also preview the final result.

Click Add.

Define the subject areas for your configuration.

For a new configuration, to add a subject area, click Add in the Subject Areas section. Select the category, subject area, and version. To work with actual data from your Reporting and Analytics database, click the Actual data type. If you do not have permissions to work with actual data, only the Sample data type is available.

If you copied an existing configuration or used a core export configuration template, you can click a subject area menu to edit or remove the subject area. Depending on your export requirements, you can also add new subject areas to a copied configuration.

Define the steps for your configuration.

A step contains the Apache Drill SQL code for selecting subject area data in the Reporting and Analytics database.

For a new configuration, Reporting and Analytics automatically adds the first step. Enter the code in the code text area. Keep the following points in mind when entering code:

The subject area being queried needs opening and closing parentheses after the alias name. The alias name is shown on the left side of the screen after the subject area has been added and expanded. The value after Table: is the alias.
In the SELECT statement, it is a best practice to specifically select each required column. Avoid using the following in a drill step: SELECT * FROM <subject area alias>. Otherwise integrations may break when new columns are added to an existing subject area.

The following example shows code for selecting JSON fields from a subject area:

SELECT
  'DSC' as `Record Type`,
  COALESCE(DD.revenueCenterNum, 0) AS `Revenue Center Number`,
  COALESCE(DD.discountNum, 0) AS `Discount Number`,
  COALESCE(DD.discountName, ' ') AS `Discount Name`,
  COALESCE(DD.discountTotal, 0) AS `Total`,
  COALESCE(DD.discountCount, 0) AS `Count`,
  COALESCE(DD.discountMasterName, ' ') AS `Discount Master Name`,
  COALESCE(DD.discountMasterNum, 0) AS `Discount Master Number`, 
  COALESCE(DD.revenueCenterName, ' ') AS `Revenue Center Name`,
  COALESCE(DD.revenueCenterMasterName, ' ') AS `Revenue Center Master Name`,
  COALESCE(DD.revenueCenterMasterNum, 0) AS `Revenue Center Master Number`,
  COALESCE(DD.discountGrossVat, 0) AS `Discount Gross VAT`
FROM discountDailyTotals3() DD

Note:

The alias for a step can't be changed. The output of a step can be referenced in subsequent steps. For example, to reference Step 1, use the alias step1. Because of this, only the last step in the sequence can be deleted.

To add a step, click Add Step, enter a step name, and then add the query code.

To test the query, click Test All Steps or Test Step to execute an individual step's code. If one step references another step, then click Test All Steps.

To exclude a step when testing the query, deselect Enabled. Select Enabled again to include the step in the final output.

To exclude step query results from the output, deselect Include in export.

To add custom JavaScript code to format the output, click Add JavaScript Step, and then enter the JavaScript code in the code text area.

Save your changes and then click Results to specify the format of the exported data.

To export to a JSON file without formatting, click JSON.

To export to a file with fields separated by a delimiter, click Delimited and then enter the delimiter in the Delimiter Value field.

Optionally, specify a single-character text qualifier that encloses each value.
To include column header names in the output, select Include headers.
To prevent parsing issues while importing export data in third-party systems, select Remove delimiter character from all strings.
Optionally, specify file or location trailer or header by selecting Optional Settings. This adds the defined string at the beginning or end of each export file, or at the beginning or end of each location’s exported data. Supported replacement tokens are displayed when selecting each field. This option is intended to be used in conjunction with export schedule parameter Combine locations into single output file.

To export to a tab-delimited file, click Tab Delimited. Other available options are the same as delimited export format.

To export to an XML file, click XML.

Click the Mapping drop-down and select the XML structure for the output file:
- Xml Node Mapping: Makes the output of each step a parent node. The row alias name of the step output is the name of the child node as defined in final output JSON.
- Xml Node Attribute Mapping: Makes the output of each step a parent node and records child nodes. The row alias name of the step output is the name of the child node as defined in final output JSON.
- Xml Table Mapping: Makes the output of each step a table.
To include column header names in the output XML, select Include Headers and enter the headers in the code text area.
To include location name, location reference, and location ID information in the output, select Include location separator. This option is intended to be used in conjunction with export schedule parameter Combine locations into single output file.
Space characters are not allowed in the configuration name when choosing XML formatting.

To preview the results, click Run and See Results.

Note:

The result preview only displays the first 10 records. To see the full export results, generate an export by clicking Export Results.

To generate a one-time export of the final results, click Export Results. On the next screen define a file name, choose to compress or encrypt the results, select a delivery profile, and then click Export.

Save your changes and then add an export schedule.

The export schedule defines when the system runs the export, which locations are included, and how the system delivers the exported data file to the end point.