The Calculated Column Editor allows you to Add, Modify and Delete calculated columns to your available data sets. Calculated columns can then be used to define the data for dashboard objects and drill down objects.

The Calculated Column Editor consists of three panels, the Calculated Columns Treeview, the Expression Text Box and the Expression Elements Treeview. The expression is calculated using the .NET DataColumn Expression property. For details on the syntax of the expression, see DataColumn.Expression Property

The Calculated Column Treeview displays the available queries, data sets and calculated columns. Each icon in the treeview are described below:

The Query icon indicates a query. To add a Calculated Column to the root dataset of the query, select the query then press the add calculated column button.

The data set icon indicates a child data set of a multi level query where split results has been selected to indicate you want the query to produce multiple data sets. To add a calculated column to the dataset select the dataset then press the add calculated column button

The Calculated column buttons indicates a calculated column. To modify the name or data type of the calculated column, select the calculated column then press the edit calculated column button. To change the calculated columns expression, select the calculated column, then edit the expression using the expression text box. To delete the calculated column, select the calculated column then press the delete calculated column button.

The Add Calculated Column button opens the Calculated Column Details window to allow you to specify the name and data type of a new calculated column. The new calculated column will be added to the query or dataset selected in the Calculated Column Treeview. To define the calculated columns expression, select the calculated column, then edit the expression using the expression text box.

The Edit Calculated Column button opens the Calculated Column Details window to allow you to modify the name and data type of the selected calculated column.

The Delete Calculated Column button deletes the selected calculated column.

The expression text box is a free text editor that allows you to enter the expression for the calculated column. You can also use the Expression Elements Treeview to add pre-defined text to your expression.

The expression is calculated using the .NET DataColumn Expression property. For details on the syntax of the expression, see DataColumn.Expression Property.

Each time you make a change to the expression, the syntax of the expression is checked, and the results displayed in the syntax message below the expression text box.

If the syntax of the expression you have entered is incorrect, the Calculated Columns Treeview and OK button will be disabled and the details of the syntax error will be displayed in the syntax message below the expression text box.

You cannot exit the Calculated Column Editor until you have entered a valid expression for the selected calculated column. You can do this by either changing the text to a correct expression, or press the undo button () to revert to the last correct expression.

When you add a calculated column as a summary column in a dashboard object, you can specify that the calculation is evaluated on the summary data rather than the source data. In this scenario, each of the dependant columns (Credit Limit and Balance in this example) are summarised for the group, and then the calculation is evaluated on the summary values. By default, the dependant columns will be summarised using the summary function specified for the calculated column in the data screen.

However, you may wish to specify a specific summary function for one or all of the dependant columns. To do this, press the manage dependant column summary type button () which opens the Dependant Column Summary Definition window to allow you to specify the summary type for each dependant column.

The Expression Elements Treeview provides access to a wide range of expression elements, described below, that you can add to your expression. To add an element from the expression elements treeview to your expression, press the + symbol on the element category to expand the category, then click on the element you want to add to your expression. The current selected text in the expression text box will be replaced with the text of the expression element you have selected.

The Available Columns expression elements allows you to add existing columns returned by the query to your expression.

The Calculated Columns expression elements allows you to add other calculated columns that you have already defined for this query or dataset to the expression.

The Operators expression elements allows you to add operators to your expression. For details on the behaviour of each operator, see DataColumn.Expression Property.

The Operators expression elements allows you to add aggregates to your expression. For details on the behaviour of each aggregate, see DataColumn.Expression Property.

Beware of defining calculated columns that use aggregates on large datasets as they can cause your dashboard to be slow to render.

The Functions expression elements allows you to add functions to your expression. For details on the behaviour of each function, see DataColumn.Expression Property.

Note: For "Convert" Type is a .Net Framework construct.

All conversions are valid with the following exceptions: Boolean can be coerced to and from Byte, SByte, Int16, Int32, Int64, UInt16, UInt32, UInt64, String and itself only. Char can be coerced to and from Int32, UInt32, String, and itself only. DateTime can be coerced to and from String and itself only. TimeSpan can be coerced to and from String and itself only.

An Example would be:-

Convert([total], 'System.Int32')