Creating custom calculations

To create a custom calculation, you must define your own expression using the expression editor.

The expression editor provides functions and operators to create your expressions. It also includes function examples and documentation, and validates the expressions. For more information about using the expression editor, see this blog in the product community.

About this task

Custom calculations can be based on one column or multiple columns from different tables.

Procedure

  1. In the data module tree, right-click the data module name, a table name, or a folder name, and click Calculation.
    Note: You can also create calculations from the Relationships or Custom tables view.
  2. In the expression editor that is displayed, specify the calculation name by overwriting the automatically generated name.
  3. In the Expression box, define the expression for your calculation by using the expression editor capabilities.
    • To enter a function for your expression, type the first character of the function name, and select the function from the drop-down list of suggested functions. For example, the following expression can be used to concatenate a person's first and last name and create a calculated column called Name:
      concat ( [Sales target (query)].[Sales staff].[First name], 
[Sales target (query)].[Sales staff].[Last name] )
    • To add table columns to your expression, drag-and-drop one or more columns from the data module tree to the Expression box. The column name is added where you place the cursor in the expression editor.
      Tip: You can also double-click the column in the data module tree, and the column name appears in the expression editor.
  4. Click Validate to check if the expression is valid. The calculation will be created even if the expression is not valid, but it won't be usable.
  5. Leave clear or select the Calculate after aggregation checkbox.

    When you leave this checkbox clear, the calculation is performed on the column values before they are aggregated. If you select this checkbox, the calculation is performed after the values are aggregated. The calculation results might be different in each case.

    For more information, see Aggregation and the order of operations.

  6. Click OK.

Results

A calculation based on one column is created as an embedded calculation. The calculation is added at the top of the list of columns in the table. You can move it to a folder inside the same table.

A calculation based on two or more columns can be created as an embedded or stand-alone calculation. If the columns are from the same table, an embedded calculation is created. It is added at the top of the list of columns in the table. If the columns are from different tables, a stand-alone calculation is created. It is added at the top of the data module tree, outside of any table. You can move this calculation to a module-level folder.

What to do next

The calculated column can be used in the same way as other columns in the table. From its context menu Vertical actions menu icon, you can access the different actions to edit the calculation, format its data, or remove it.

The calculation can be used in reports, dashboards, explorations, and other Cognos® Analytics content.