Dimensional coercion rules

IBM® Cognos® Analytics with Watson uses dimensional coercion rules to automatically convert dimensional types to better match other types. These conversions help you build simpler expressions, making them easier to understand. In addition to the implicit rules that IBM Cognos Analytics with Watson provides, you can make explicit data type conversions using various functions, such as children.

IBM Cognos Analytics with Watson supports the following types of coercion:

  • coercion of an object of one dimensional type into another, such as a level into a member set
  • coercion of a dimensional object into a numeric, date, time, or string value, such as a numeric measure into a numeric value

Coercion rules are applied to expressions and data items. 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. Coercion rules may also be applied to data items to coerce the data item to a collection of members or values.

Function Operands

The following describes how coercion rules are applied to function operands:

  • If the operand is of the required type, no coercion is required.
  • If the function operand is supposed to be a numeric value, it is coerced into one.

    A coercion rule exists for each type of dimensional object coercion.

  • If the function operand is supposed to be a dimensional object and a coercion exists to coerce the operand to the required type, the coercion is applied.
  • If no coercion exists, an error message with the code QE-DEF-0478 appears to indicate that an unsupported conversion of a dimensional object from the source to target type occurred.

Comparison and Other Symmetric Operators

Binary operators that accept operands of more than one type, such as equals (=) and in, need both operands to be the same dimensional type.

No coercion is possible between value domains (numeric, date, time, and string) or between members and values. Consequently, if either operand is a value type, both must be in the same value domain and converted explicitly using a function. Otherwise, the query fails.

Members and member set operands are valid only with the operators equals (=), not equals (<>), in, and not in, where the right side of the expression is a member, member set, or prompt. Only the following are valid:

  • [member / member set] = [member]
  • [member / member set] <> [member]
  • [member / member set] = ?p?
  • [member / member set] <> ?p?
  • [member / member set] in ([member], ...)[member / member set] not in ([member], ...)
  • [member / member set] in ([member set])[member / member set] not in ([member set])[member / member set] in ?p?[member / member set] not in ?p?

Exceptions

For the left operand, member sets are supported in detail and summary filter expressions, but not in expressions that use the filter function. Members are not supported in detail and summary filters, but they may be used in expressions that use the filter function.

The in_range operator is not supported for members and member sets.

Normal coercion rules apply to these operands.

NULL operands are considered values, rather than members.

Examples

The following examples illustrate how coercion is applied to levels in expressions with operators.

[Sales].[Products].[].[Product Line] = [Sales].[Products].[].[Product Line]->[Outdoor Equipment]

The left operand is coerced to the following member set:

members ([Sales].[Products].[].[Product Line])

The following expressions are invalid:

  • [Sales].[Products].[].[Product Line] = NULL
  • [Sales].[Products].[].[Product Line] + 1
  • [Sales].[Products].[].[Product Line] = 'Canada'
  • [Sales].[Products].[].[Product Line] > 2000

The following examples illustrate how coercion is applied to members in expressions with operators. In each example, the left operand is coerced to a value of the current cell at this member:

(tuple ([Sales].[Products].[].[Product Line]->[Outdoor Equipment]))

  • [Sales].[Products].[].[Product Line]->[Outdoor Equipment] = NULL
  • [Sales].[Products].[].[Product Line]->[Outdoor Equipment] + 1
  • [Sales].[Products].[].[Product Line]->[Outdoor Equipment] > 2000
  • [Sales].[Products].[].[Product Line]->[Outdoor Equipment] + 'Canada'