Fields and Measures

Adding a Custom Field

To add a custom field to a table, follow the steps below.

1. Right click a table and click Add Custom Field. The Add Custom Field window appears.
   
2. In the Name box, type a name for the field.
3. In the Description box, type a description of the custom field to use in front-end tools.
   Note: This setting applies to SSAS Tabular endpoints only.
4. In the Data type list, click on the data type you want to use for the custom field.
   Note: This setting applies to SSAS Tabular and Tableau endpoints only.
5. In the Data format list, click on the data format you want the field to have. For some data types, just one data format is available which is set and cannot be changed. Click Customize to customize the data format, e.g. number of decimal places for decimal numbers.
   Note: This setting applies to SSAS Tabular endpoints only.
6. In the Category list, click on the category you want the field to have.
   Note: This setting applies to SSAS Tabular endpoints only.
7. In the Summarize by list, click on the type of aggregation you want to use for the field in e.g. Power BI.
   Note: This setting applies to SSAS Tabular endpoints only.
8. In the Sort by list, click on the field that contains the values you want to sort by.
   Note: This setting applies to SSAS Tabular endpoints only.
9. In the Script box, enter the script that generates the value of the custom field. Since syntax differs between endpoint types, you can add a script for each endpoint type. Click on an endpoint type in the Endpoints list to switch between endpoint types. Any endpoint type that does not have a specific script will use the Default script. You can drag in fields from the Available parameters list to use them as parameters in the script. Click Show translation to show the script TimeXtender will deploy to the endpoint.

Adding a Custom Measure

Note: This setting applies to SSAS Tabular and Tableau endpoints only.

Custom measures use a script to calculate the value of the measure.

To add a custom measure, follow the steps below.

1. Right click a table and click Add Custom Measure. The Add Custom Measure window appears.
   
2. In the Name box, type a name for the field.
3. In the Description box, type a description of the custom measure to use in front-end tools.
   Note: This setting applies to SSAS Tabular endpoints only.
4. In the Data type list, click on the data type you want to use for the custom field.
5. In the Data format list, click on the data format you want the field to have. For some data types, just one data format is available which is set and cannot be changed. Click Customize to customize the data format, e.g. number of decimal places for decimal numbers.
   Note: This setting applies to SSAS Tabular endpoints only.
6. In the Script box, enter the script that generates the value of the custom measure. Since syntax differs between endpoint types, you can add a script for each endpoint type. Click on an endpoint type in the Endpoints list to switch between endpoint types. Any endpoint type that does not have a specific script will use the Default script. You can drag in fields from the Available parameters list to use them as parameters in the script.

Adding a Measure based on a Table

You can add measures to a table based on both the table itself and fields on the table.

To add a measure based on a table, follow the steps below.

1. Right click a table and click Add Measure The Add Measure window appears.
   
2. In the Name box, type a name for the measure.
3. In the Description box, type a description of the measure to use in front-end tools.
   Note: This setting applies to SSAS Tabular endpoints only.
4. In the Type list, click on the type of measure you want to create. You have the following options:
   • Row Count: The value will be the number of rows in the table.
5. In the Data type list, click on the data type you want to use for the custom field.
   Note: This setting applies to SSAS Tabular and Tableau endpoints only.
6. In the Data format list, click on the data format you want the field to have. For some data types, just one data format is available which is set and cannot be changed. Click Customize to customize the data format, e.g. number of decimal places for decimal numbers.
   Note: This setting applies to SSAS Tabular endpoints only.

Adding a Measure based on a Field

To add a measure based on a field, follow the steps below.

1. Right click a field and click Add Measure. The Add Measure window appears.
   
2. In the Name box, type a name for the measure.
3. In the Description box, type a description of the measure to use in front-end tools.
   Note: This setting applies to SSAS Tabular endpoints only.
4. In the Type list, click on the type of measure you want to create. You have the following options:
   • Average: An average on the field values.
   • Count: The number of field values.
   • Distinct count: The number of unique field values.
   • Maximum: The highest field value.
   • Minimum: The lowest field value.
   • Sum: The sum of the field values.
5. In the Data type list, click on the data type you want to use for the custom field.
   Note: This setting applies to SSAS Tabular and Tableau endpoints only.
6. In the Data format list, click on the data format you want the field to have. For some data types, just one data format is available which is set and cannot be changed. Click Customize to customize the data format, e.g. number of decimal places for decimal numbers.
   Note: This setting applies to SSAS Tabular endpoints only.

Setting Data Format and Category

Note: This setting applies to SSAS Tabular endpoints only.

By setting data format and category, you can control how a fields data will be displayed in client applications. For instance, text fields categorized as "Web URL" will be displayed as links in PowerBI.

To set data format and category for a field, follow the steps below.

1. Right click the field you want to sort and click Edit Field. The Edit Field window appears.
   
2. In the Data format list, click on the data format you want the field to have. The options depend on the data type which can be changed on the source field in the data warehouse or staging database. For some data types, just one data format is available which is set and cannot be changed.
3. Click Customize to customize the data format, e.g. number of decimal places for decimal numbers.
4. In the Category list, click on the category you want the field to have.

Choosing How a Field is Summarized

Note: This setting applies to SSAS Tabular endpoints only.

In analysis and reporting applications, a field can be aggregated in a number of different ways such as 'sum', 'average' or 'minimum'. The Summarize by option on fields in the semantic layer controls how Power BI aggregates the field.

To change the summarize by option

• Right click the field you want to set the option for, click Edit Field and, in the Summarize by list, click on setting you want to use.

Sorting a Field by another Field

Note: This setting applies to SSAS Tabular endpoints only.

Some fields have a certain conventional sort order. For instance, month names are usually ordered January - December, not alphabetically April - September. In this case, it would make sense to order the months according to a month number instead of the month name.

In TimeXtender you can set a field to be sorted by another field.

To sort one field by another field

• Right click the field you want to sort, click Edit Field and, in the Sort by list, click on the field that contains the values you want to sort by.

Organizing fields and Measures in Display Folders

Note: This setting applies to SSAS Tabular endpoints only.

With display folders, you can organize fields and measures in folders in client applications such as PowerBI.

To add a display folder and add fields to it, follow the steps below.

1. Right click the table that contains the fields and click Display Folders. The Display Folders window appears.
   
2. Click Add and type a name for the folder in the Name box in the window that appears. The syntax for display folders has two special characters:
   • Backslash: Use backslash to create a hierarchy of display folders. For example, "A\B" will create a display folder "A" that contains a display folder "B".
   • Semicolon: Behind the scenes, TimeXtender creates one display folder string for each field where each folder is separated by a semicolon. It is possible, but not recommended, to create display folders with semicolon in the name. For example, mapping a display folder called "A;B" to a field is the same as mapping the field to a display folder "A" and a display folder "B".
   Display folders are shared across the model.
3. Map the fields to display folders by clicking the check box where a field and a display folder intersect in the grid.

Hiding a Field or a Measure

Field and measures can be hidden to let the front-end application know that this object should no be displayed. This is very useful if you have intermediate fields or measures.

To hide a field or measure

• Right click the field or measure and click Hide.

Note: Hiding measures only applies to SSAS Tabular and Tableau endpoints.