Using the Expression Editor

Using the Expression Editor includes

• understanding dimensional coercion rules
• browsing or searching the values of data items
• understanding how to use quotation marks
• using Member Unique Names (MUNs)
• understanding the limitations when creating certain expressions
• understanding the limitations of creating expressions with specific data sources
• understanding expression components

An expression is any combination of operators, constants, functions, and other components that evaluates to a single value. You build expressions to create calculation and filter definitions. A calculation is an expression that you use to create a new value from existing values contained within a data item. A filter is an expression that you use to retrieve a specific subset of records.

The expression editor shows the expression components that are supported by the data source in which the metadata is stored. For example, if you import metadata from an Oracle data source, the expression editor shows only the elements that are supported in Oracle.

If you are using a DB2 data source, note that the subtract operator is invalid if you combine the datatypes timestamp2 and packed decimal.

When creating an expression that will be used in a double-byte environment, such as Japanese, the only special characters that will work are ASCII-7 and ~ -- || - $ ¢ £ ¬.

Quality of Service Indicators

Not all data sources support functions the same way. The data modeler can set a quality of service indicator on functions to give a visual clue about the behavior of the functions. Report authors can use the quality of service indicators to determine which functions to use in a report. The quality of service indicators are:

• not available (X)

  This function is not available for any data source in the package.

• limited availability (!!)

  The function is not available for some data sources in the package.

• limited support (!)

  The function is available for all data sources in the package but is not naturally supported for that data source. IBM Cognos 8 uses a local approximation for that function. Because an approximation is used, performance can be poor and the results may not be what you expect.

• unconstrained (no symbol)

  The function is available for all data sources.

SAP BW Support

SAP BW does not support all operators or summaries. This can be confusing if you have imported SAP BW metadata and non-SAP BW metadata into the same model.

SAP BW does not support the following operators:

• like

• lookup

SAP BW does not support the following member summaries:

• date-time

• interval

• interval month

• interval day

• interval day to hour

• interval day to minute

• interval day to second

• interval hour

• interval hour to minute

• interval hour to second

• interval minute

• interval minute to second

• interval second

• interval year

• interval year to month

• moving

• running

• time with time zone

• timestamp with time zone

Cell values are date, number, or time. Attribute values are strings.

Length of Expressions

When creating layout calculations that use complex expressions, limit the length of the expression to less than 1,000 tokens. A token is a keyword, identifier, constant, or special character symbol that the server identifies when it parses the SQL. For example, the expression 1+1+1+1+1+1+1+1+1+1 contains approximately 28 tokens, whereas the expression 1+1+1...+1 (400 times) contains more than 1000 tokens.

Tip: The Sales Commissions for Central Europe sample report in the GO Data Warehouse (analysis) package includes expressions. For more information about The Great Outdoors Company samples, see Sample Reports and Packages.