The Data Transformer enables you to perform actions on a data source linked to a widget in order to:

• Define its structure. Indicate the Dimension column and the Measure columns.  See Standard data source structure.

• Define its scope. By default, the widget takes into account all of the measures. However, you can use the Data Transformer to exclude certain columns if you do not want to process their values.

• Convert data by applying statistical aggregate functions, such as the minimum or average, to all values in a measure.

• Modify the display order of the Measure columns in widgets.

Example

You want to display the number of incidents per employee for three departments (Sales, Marketing and Administration) using a vertical bar chart (Advanced Bar Chart widget).

CSV file data

• Dimension = First column of the file = Department
• Measures = The other columns = Number of incidents per employee and Number of service requests per employee
  Note: You select only the measure you want, i.e. Number of incidents per employee using the Data Transformer.
• Elements displayed by the widget: Number of incidents per employee and by department
             Download Example (CSV file)
   

Chart displaying the number of incidents by department

Data source	Advanced Bar Chart widget

Notes

• By default:
  • All data source columns will be displayed in the widget pointing to the data source.
  • The first column in the data source is the Dimension column and the following columns are the Measure columns.
  • Measures are displayed in the order of the columns in the data source.

• Statistical aggregate functions:
  • They are defined by . You cannot add to them.
  • They are called by running SQL queries for MS SQL Server data sources.

Caution

• You must ensure that the Dimension column is always defined as the first column in the widget.

Screen description

Access: In the Properties Inspector of a widget, click Setup / Preview Data below the Data Transformer property.

Choose columns to display: List of measures available in the data source (columns).

• By default, all data source columns will be displayed in the widget pointing to the data source. To restrict the display to certain columns, select them from the list and click .
   

Optional aggregate function: List of statistical aggregate functions available.

• maximum: Returns the maximum value of the selected measure.
• minimum: Returns the minimum value of the selected measure.
• average: Returns the average value of the selected measure.
• sum: Returns the sum of values.
• count: Returns the number of values.
   

     Only for DateTime fields:

• date: Returns the date.
• time: Returns the time.

(2) List of elements displayed in the widget pointing to the data source, i.e. measures selected in the data source and statistical aggregate functions.

•  and : Used to modify the display order of elements.
• : Used to delete the display of the element.
   

(3) Preview pane: Used to preview values displayed in the widget pointing to the data source.

• You can click and drag the column headers to change the order of the columns.

Procedure: How to use the Data Transformer

Step 1: Specify the Dimension column of the data source.

1. Click the widget you want to configure.
The Properties Inspector will be refreshed.

2. Check the First Row Contains Dimension Elements option.
The first column of the data source will be processed as the Dimension column.
           First column = 3 departments  See the example.
 

Step 2: Open the Data Transformer.

1. Click Setup / Preview Data below the Data Transformer property.
The Data Transformer will appear with data from the data source linked to the widget.
 

Step 3: Select the measures to be displayed by the widget.

1. Select the Dimension column.

• Select the column from the Choose columns to display list.
• Click  to add it.
  The column will automatically be added to the list of elements to be displayed by the widget and will appear in the Preview pane.

2. Select the measures to be displayed by the widget.

• Select the column from the Choose columns to display list.
• (optional) Apply a statistical aggregate function to the measure using the Optional aggregate function list.
• Click  to add the column to the selection.
  For each new measure, one column will automatically be added to the list of elements to be displayed by the widget and will appear in the Preview pane.
    

Modify the order of the measures

• Click and drag the column headers to the Preview table.
  or 

• You can also use the  arrows in the list of selected measures.
   

Delete the display of a measure

• Click  next to the measure in the list of selected measures.
   

Step 3: Save your modifications.

1. Click OK.

Reinitialize the scope of the data source

• Click Showing . columns below the Data Transformer property of the widget.

• Click Remove.
  • The button name will switch back to Setup / Preview Data.
  • All measures in the data source will be displayed by the widget again.