Creating calculations

You can create calculations to provide your users with calculated values that they regularly use. Calculations can use query items, parameters, variables, calculated members, expressions, and expression components, such as functions.

Punctuation characters, such as the question mark (?), must be in 7-bit ASCII character code. If you type a punctuation character from a multi-byte enabled keyboard, ensure that you type the 7-bit ASCII representation of the character. For example, type Alt+063 for the question mark.

Avoid using characters that are used for expression operators in the name of the calculation. Syntax errors may occur when the expression is evaluated. For example, a calculation named Margin * 10 causes errors when used in an expression such as [Margin * 10]< 20.

In expressions, an operator or function may require operands to be of a particular dimensional type. When an operand is not of the required type, one or more coercion rules may be applied to coerce the operand to the appropriate type. Because coercion rules are not applied to expressions in model query subjects, ensure that those expressions are valid without relying on coercion rules. For more information about coercion rules, see the IBM Cognos Analytics - Reporting User Guide.

If you insert an imported user-defined function in the calculation, ensure that the function name does not repeat vendor-specific names. For example, if the user-defined function name is CHAR you will receive and error when testing the function in the Calculation Definition dialog box because this name is considered identical as char in Microsoft SQL Server. For information about function names used in your database, see the database product documentation.

At query time, IBM® Cognos® Framework Manager returns a null value for any calculation that contains a divisor whose value is zero. Framework Manager cannot detect zero-division errors in functions such as average and mod, because the division operator is not explicit.

Framework Manager supports stand-alone calculations and embedded calculations.

  • Use a stand-alone calculation when you want to reuse the expression or control the order of operations using the Calculated setting in the Regular Aggregate property. For more information, see Order of operations for model calculations.

    You can apply a stand-alone calculation to one or more dimensions or query subjects to provide calculated data to a report, or include it in a package to make it available to your users. By moving a stand-alone calculation or a shortcut to it into a folder, you can better organize the model objects.

    You cannot use stand-alone calculations in Analysis Studio. Instead, use an embedded calculation in the measure dimension.

  • Use an embedded calculation when you want to use a calculation with only one dimension or query subject.

    You can create an embedded calculation when modifying a relational data source query subject, model query subject, or dimension.

    If you start with an embedded calculation, you can later convert it into a stand-alone expression that you can apply to other dimensions or query subjects. Tip: Right-click the calculation expression in the Calculations tab and click Convert to Stand-Alone Calculation.

    When you embed a calculation, the data source query subject must have a relationship to any query subject referenced by the expression. This relationship is necessary even if the expression references a model query subject based on the same table as the data source query subject in which you are embedding the expression.

Use the following steps to create a calculation:

  1. Decide what type of calculating to create:
    • To create a stand-alone calculation, click the namespace or folder and click Actions, Create, Calculation.
    • To create an embedded calculation for a measure dimension, double-click the dimension. On the Measure Dimension tab, click Add.
    • To create an embedded calculation for a regular dimension, double-click the dimension. On the Dimension tab, select a hierarchy and click Add.
    • To create an embedded calculation for a data source query subject, double-click the data source query subject. On the Calculations tab, click Add.
    • To create an embedded calculation for a model query subject, double-click the model query subject. On the Query Subject Definition tab, click Add.
  2. In the Calculation Definition dialog box, type the calculation name, and define its expression:
    Add items
    On the Model tab, click a query item, filter, or calculation and click the arrow.
    Add functions
    On the Functions tab, choose a component and click the arrow.
    Add parameters
    On the Parameters tab, click a parameter and click the arrow.
    Retrieve all data and show a specified number of rows
    Click the options button, select the Restrict the maximum number of rows to be returned check box, and type the required number of rows to be returned.
    The options setting options icon does not improve performance for retrieving data when testing dimensions, query subjects, and query sets.
    Override session parameters
    Click the options button, click Set, enter a value in the Override Value field, and click OK.
    Override prompt values
    Click the options button, and then click Prompts.
    The Model Prompts Manager dialog box displays all prompts, and their values, in the model.
  3. To test the calculation, click the test icon test icon.

    You can test only calculations that contain query items. If a calculation contains a function, for example _add_days, the Test Sample button is not available.

    Note: If the expression is invalid, check the Tips box in the expression editor.
  4. Modify the Data Type property on the calculation, if needed.