Cognos Analytics

Cognos Analytics Modelling expression editor

The Cognos Analytics Modelling expression editor has been revised in 11.1.x.  You can use the expression editor in Modelling, Dashboards, and Exploration.

The intention of this article is to describe functionality available in the expression editor.

Work area

The new editor has common elements for calculations, filters, and SQL expressions. 

They are preview, validation, commenting, prettifying, high contrast, and font size presentation.  In addition, any keyword or function has an associated information description.  There is also type-ahead functionality.

Calculations and filters have several tree views.

Stand-alone calculations and filters have a tree of the module and a functions tree.

Here is an example of the module tree.

Here is the functions tree.  

In addition to these two trees, embedded calculations and filters have a query subject tree, which is a tree of the objects in the query subject which contains them.  The query subject tree allows you to not need to hunt through the module tree for the objects in your query subject and helps prevent you from accidentally using an object from another query subject.

Here is an illustration of that tree.  

The module tree and query subject tree allow you to see and use the objects contained in those objects in your expression. 

The functions tree contains functions, including macro functions, which can be used in expressions by operating on an input.

Preview       

One particularly important improvement has been the implementation of test preview of the results of an expression, in addition to the existing validation of the expression.   Included in the results of the expression are the source columns used in the expression.  This allows you to walk through the expression result and confirm its validity.

The illustration below is of an expression which takes PRODUCT_NAME (from the sample GOSLDW data base) and concatenates it with PRODUT_NUMBER, which has been cast to a character data type.

As you can see, the preview has columns for both of the source query items and for the result of the expression.  

You can preview the results of a filter expression.   The filter result will show you the Boolean result of the expression, which you can use to verify that the filter is producing the results which you want.

In the illustration below, the expression tests to see if RECORD_END_DATE is null.   One record is null and the result for the result column is 1, which indicates that the expression is true for this record.

Sub-expression preview

You can select a sub-expression in your expression and preview the results.   

Previewing sub-expressions is useful for troubleshooting expressions.   You can isolate the parts of an expression, view their results, and identify where problems originate.

Here is a fairly simple expression which takes the RECORD_END_DATE column from the Cognos sample data base table EMP_EMPLOYEE_DIM, which is a timestamp data type column with null values, and then converts it to a numeric value.   The intention was to use it as a key in a relationship.  

The expression casts RECORD_END_DATE to a character data type, extracts substrings from it and concatenates them into the key.  It handles for null values by substituting the current date.   You could have something like 29991231 instead, if it is your preference.   The expression has no pretentions to elegance; it exists to illustrate the functionality of sub-expression preview.

In the illustration below, you will see two columns.   The first is the result of the expression.  It has the header Record_end_date_key.   The second column has the values of the source column, RECORD_END_DATE.  The preview results are those of the entire expression.

One of the values shown in RECORD_END_DATE is null.  Since the expression handles for nulls in the source values, the current date has been substituted and then processed into the key.

In the next illustration below, I have selected part of the expression and pressed the preview button.  The sub-expression which I have selected is SUBSTRING(CAST (RECORD_END_DATE AS VARCHAR(10) ). 1, 4 ). The sub-expression casts RECORD_END_DATE to a character and then gets a substring of the first 4 characters of the generated string.   This represents the year portion of the timestamp, which is returned in the first column of the results field.

You will notice that the result in the expression for any value which is null is null.  This is because the sub-expression, unlike the full expression above, does not have any handling for the null.

Here is an example of testing a sub-expression in a SQL table.   This is the result of the entire SQL statement.

This is the result of a sub-expression in the SQL statement.

Formatting of expressions preserved

The formatting of your expression is preserved in the module and you can layout the expression in a manner that you find suitable.  

Function information

The function help text will stay visible as you work in the expression until either another function is selected in the function tree or in the expression itself or if the preview or validate buttons are pressed.   This helps when you are building up your expression.  The function’s syntax help will be available to you to refer to until you no longer need it.   If you had much experience with the Framework Manager expression editor, you will appreciate how helpful this will be.

Comments

You can add comments to the expressions, which will aid you or other people in understanding your expressions when they are reviewed.

As you can see in the illustrations, you can add two types of comments using the // and /* */ operators.

Collapsing of expression elements

You can move elements of expressions out of the way by collapsing them.

In the illustration below, our expression has been collapsed.

The expression editor looks for opening brackets and puts a widget into the expression editor at the start of the line which contains it.  

Here is our expression, slightly modified, which will allow us to see how the collapsing functionality works.  I have added a bracket around the year element generation expression.

This is the element as collapsed.

The collapsed elements of an expression will not remain collapsed in the next session of the expression.

Validation

Like the old expression editor, you can validate the expression.  More information about the validation errors will be displayed than was displayed in the previous Modelling expression editor.

High contrast

You can view the expression with a high contrast view without setting your computer to use its high contrast accessibility functionality.

Font

You can increase the font size of the expression.

Function auto-completion

The expression editor will attempt to suggest auto-completion of what you are typing.   You can press escape to not accept any of the suggestions.   You can use the arrow keys to navigate up and down the list of suggestions and press enter to choose an item. 

The suggestions will be from the functions list and of query items which exist in the module. 

In the illustration below, auto-completion is suggesting the query item Discontinued Date and some functions and operators.

Aggregation timing control

You can control the timing of aggregation.  You do this using the calculate after aggregation control.   If it is set on, then the calculations in the expression will be executed after the values of the query items are aggregated.  If not, the calculations in the expression will execute first and the results will be then aggregated.

Pretty printing

You can have the expression editor attempt to automatically format your expression.   There have been some cases where the expression had been altered by the pretty print operation prior to 11.1.4.

Object names

If your expression is built in a query subject, then the object names will be one-part names.  Our expression is an example of that. Relative time measures and filters will have a two-part name if used in a query subject calculation or filter.  The parts are the identifier of parent date query item or measure and the identifier of the relative time filter or measure.

If you have an object from somewhere else, for example if you are creating a stand-alone calculation or filter, then the object name will have two parts.  One is the source query subject identifier.   The other part is the identifier of the query item.  A relative time filter or measure used in a stand-alone calculation or filter will have a three-part name.   The parts are the query subject identifier, the parent date query item or measure and the identifier of the relative time filter or measure.

Here is an example of a two-part name.