Cognos Analytics

 View Only

Module filter types

By IAN HENDERSON posted Tue December 22, 2020 03:04 PM

  

Module filter types

Contents

Summary

Types of filters

Stand-alone filters

Query subject filters

Query item filters

Embedded filter expressions

Filters and members

Filter timing

Filter administration

 

 

Summary

This is intended to document the different types of filters which are available to a modeler in Cognos Analytics.

Types of filters
There are two categories of filters. They are:

  • Selectable filters
  • Embedded filters

Selectable filters

Selectable filters are pre-defined, optional filters. They exist as distinct objects. They only apply to a query if they have been added to the query.

There are two types of selectable filters. They are:

  • Stand-alone filters
  • Query subject filters

Stand-alone filters exist as independent filter objects and are children of the module. They are defined in the expression editor.

Query subject filters are also independent filter objects but are children of a specific query subject. They are also defined in the expression editor.

Embedded filters

Embedded filters are defined in a query subject and always apply to the query subject.

There are two types of embedded filters.

  • Query item filters
  • Embedded filter expressions

A query item filter is a filter defined by filtering on a query item. The data type of the query item determines how you can define the filter.

An embedded filter expression is a freeform filter, defined by a filter expression created in the expression editor.

The variety of filter types allows you flexibility in defining filters in your module.

Stand-alone filters

Stand-alone filters are similar to stand-alone filters defined in Cognos Framework Manager (FM). They are created with an expression using the expression editor. They can be tested in the expression editor to validate whether the Boolean result of the expression for each object in the expression’s source object is true or false.

The stand-alone filter, like a stand-alone calculation, exists without affecting results until it is added to the query. When it is part of a query, then it will filter the query by the filter expression defined.

The stand-alone filter exists in the module as a stand-alone object. It can be put anywhere in the module tree, just as a query subject, stand-alone calculation, or folder can exist anywhere in the tree.

The illustration below shows several stand-alone filters in the module tree, together with stand-alone calculations, several query subjects, folders, and the Gregorian calendar sample module, which is used for defining relative time measures.




Query subject filters

 

 

The query subject filter exists as a child object in a query subject. This allows a filter to be more closely associated with a relevant query subject.

Query subject filters are similar to stand-alone filters except they belong as children of query subjects rather than to the module. They are created with an expression using the expression editor. They can be tested in the expression editor to validate whether the Boolean result of the expression for each object in the expression’s source object is true or false.

The illustration below shows two query subject filters in the Retailers query subject.

The query subject filter exists without affecting results until it is added to the query. When it is part of a query then it will filter the query by the filter expression defined.

Although the query subject filter exists as a child object in a query subject, this does not mean that it will only be valid if a query item from the parent query subject is used in a report. It can be used to filter a report without the need to include a query item from the parent query subject. The query subject filter will be valid, provided that the query subject has a relationship to some other object in the query. This is because the addition of the query subject filter will modify the generated SQL in the query by including a join to the parent query subject and by adding the filter expression to the where clause of the query.

In the illustration below, a simple query using Year from the time dimension of the sample data base GOSLDW and sales quantity from the SLS_SALES_FACT fact table of the same data base is shown along with part of the Cognos SQL which has been generated for that report.

Because I have defined column dependency on the time dimension in my module, the Cognos SQL has been automatically annotated with tags which will tell the query engine how to handle the query. They are the itemNormalGenerated tags which you see in the generated Cognos SQL.

In the metadata tree, you will see the Retailers query subject. In it, there is a query subject filter named RETAILER_TYPE_EN does not contain (‘Store’). Its expression is shown in the next illustration. As you can see, the retailer types which contain store return 0 and the ones which do not contain store return 1.



In the next picture, the query subject filter from the retailers dimension has been added to the report.


The addition of the query subject filter has altered the generated SQL and has altered the report accordingly.

One of the changes is that from clause has been expanded to include a join from the fact table to retailers. There is also now a where clause between the from clause and the group by clause. In the where clause is an expression, analogue to the expression in the query subject filter but re-written by the Cognos query engine, which filters the query to only include retailer types which do not contain store.

The results of the query have been filtered by the addition of the query subject filter.

Query item filters

Query item filters have existed since the start of Cognos Analytics. They are filters on a query item.

Query item filters will be defined in a dialog, which will appear when a query item is selected, and the filter context menu chosen or when the query item is chosen in the drop-down list of the query subject’s manage filters functionality.

The values which appear in the filter will not be filtered by other filters in the query subject. For example, a Product name table which has multiple records, each for a separate language, will display in the query item filter dialog the product names in all locales which exist in the table even if you have filtered the table to only display the names of the products in one locale. This allows you to have the flexibility of filtering without being encumbered by the order or even existence of filtering on other query items in the query subject. The data grid and the report will display the fully filtered list of results of the query.

The list of values will include all values of the query item, including those which could have been filtered out by other filters in the query subject. This allows the modeler flexibility in his modelling actions. Often, it has been found to be inconvenient to have the values of a query item already filtered out by another filter. It is more convenient and, arguably, better that you have access to values which will have otherwise been suppressed by another filter then for you not to be aware of them. It does not have an effect anywhere else. The values are filtered in the data grid, the module tree members list, in reports, and dashboards.

For example, assume a query subject contains, among other query items, a country query item and a city query item. Assume that the values in the query subject for the country query item are Canada, France, and Germany. Assume that the values for the city query item include such values as Toronto, Montreal, Paris, Toulouse, Stuttgart, Munich, and Berlin. Assume that the query subject has been filtered so that only France and Germany will display in the data grid. The query item filter dialog list will include Toronto and Montreal even though Canada has been excluded.

The type of dialog which appears will depend on the data type of the query item.


Text data type query item filters

If the query item is a text data type, then a list of checkboxes of the individual values of the query item will appear. If the query item has nulls in it, a checkbox for Null is added at the end of the list of values.

If you want to include a value, click the checkbox associated with the value.

When you check on a value, you are including that value in the query.

If you want to exclude certain values, you can do so without needing to check every other value by selecting the values you want to exclude and then clicking the invert button. This will change the selection to unselect the values you want to exclude and select all the values you want to include.

In addition, it is possible to add a filter condition. It allows you to define a condition in which a string value is compared to the values in the query item and will filter depending on whether the condition is true or not. The operators in the filter condition are contains, begins with, ends with, does not equal, does not contain, does not begin with, and does not end with.

You can define two filter conditions. You can define them to be combined with an and operator or with an or operator.

The filter conditions can be used independently or in conjunction with the filters defined in the values list. It will automatically apply the filter conditions to those values in the query item where the conditions are true.



This illustration shows a text query item filter dialog for a query item which contains addresses. It shows the list of values of the query item and their associated checkboxes. The filter condition flyout is also shown. Two conditions have been defined but not yet applied. The conditions are contains ès or ends with 4.



The preceding illustration shows the two filter conditions and their effect on the query item filter list checkbox states.

Once the filter is saved, it will apply to the query subject and filter it. As you can see in the illustration below, the effect of the filter is to only include the addresses which contain the string ès or which end with 4. Only 8 records exist in which these conditions are true and are shown below.




The invert button gives you the power to define combinations of explicit inclusion of values in a query item and conditions and then to change the setting to include only those values which do not match the conditions.

In the illustration below, I have inverted the original filter of contains ès or ends with 4. This results in everything other than those values being included.

If a value is null, an option to define it exists as a checkbox which is at the end of the list of values in the dialog.

If you filter another query item the values of the query item will not be affected by the original filter. As you saw previously, the filter on the retailer address query item filters the values to only 8 records. If you filter another query item, the values will have values which are not viewable in the data grid. In the illustration below, Rtl Address 2 has been filtered by the original filter on Retailer Address. The values in the query item filter for Rtl Address 2 have not just those values which are available because of the filter on Retailer address but all the values for Rtl Address 2.

This can be slightly disconcerting at first but of the two options it is the preferred. This allows you to define more sophisticated and better designed filters

The values will be limited to 32000 records. If you want to work with larger numbers of values it would be better if you defined an embedded filter expression, where you can use the expression editor to define your filter conditions.

If the data changes, it is possible that, depending on the filter taken,  you could expose data which you do not wish to do so. In cases such as that, a filter expression would probably be more suitable.

 

Numeric data type filters

If the data type of the query item is numeric then you are allowed two options to define the query item filter. One will be to pick the items to be filtered out or included from a list of values. The other is to choose from a range of values.

If you choose one you cannot use the other in conjunction with it.

Like the text query item filters, you can invert the filter selection for either the list or the range.

Here is the items view.



Here is the range view.

 

Time data type filters

If the data type of a query item is time, datetime or date then you are allowed two options to define the query item filter. One will be to pick the items to be filtered out or included from a list of values. The other is to choose a range of values from the appropriate time and or date controls.

If you choose one you cannot use the other in conjunction with it.

Like the text query item filters and numeric data type filters, you can invert the filter selection for either the list or the range.



 


  

 

Embedded filter expressions

 

Embedded filter expressions are filters created in a query subject using the expression editor, rather than filtering a query item. An embedded filter, like a query item filter, always operates on the query subject.

You create the embedded filter on the query subject. You define an embedded filter in the filters tab of the query subject’s properties. You access the tab in one of two ways. One way is to select the query subject, choose properties from the context menu, and click on the filters tab. The other way is to select the query subject and choose the manage filters context menu item.

In the filters tab there is a drop down with a list of the query items in the query subject. It also contains an option called via expression editor. If you choose via expression editor, you will be able to define the embedded filter expression.

The embedded filter expression can be any valid expression which resolves to a Boolean value. As such it gives you flexibility in defining your filter expression.

Embedded expression filters were introduced in 11.1.1.


Filters and members



You can use relational members as part of a filter expression. In the following illustration, the relational members of February and March are part of a filter expression.


As you can see, they produce the same result as an equivalent expression which uses values. 





Where appropriate, a query item can generate members in the module tree. If you have viewed the members of a query item, then you need to select the 'Refresh members' context menu item in the data tree in order to view the effect of the filter on members.


Syntax



A filter expression can contain anything, provided that the expression eventually resolves to a Boolean value, that is a true or false state. This is because the filter expression is going to be added to the where clause of the SQL statement.

For example, this is a valid filter expression.

REGION_EN like '%Europe'


The reason it is valid is that the result of the function is either true or false. In this case, the values of REGION_EN either do or do not contain Europe. In the table below, the condition is true for Central Europe, Northern Europe, and Southern Europe. It is not true for America or Asia Pacific. As a result, the filter result value for the latter is 0 or false and the value for the others is 1 or true.

Region En

Filter result value

Americas

0

Asia Pacific

0

Central Europe

1

Northern Europe

1

Southern Europe

1


This expression is not a valid filter expression.



if ( REGION_EN like '%Europe')

Then ('Europe')

Else ( REGION_EN)


This is because the results of the expression are not Boolean values.

Region En

Expression result

Americas

Americas

Asia Pacific

Asia Pacific

Central Europe

Europe

Northern Europe

Europe

Southern Europe

Europe

 


The expression can, however, be used as part of a valid filter expression. An example of such is shown below. It is an admittedly sad excuse for an expression which is only being used so that, for comprehension purposes, as much of the preceding syntax is preserved.



(if ( REGION_EN like '%Europe')

then ('Europe')

else ( REGION_EN) ) contains ' '


In the table below, which is generated by testing the filter expression, the condition is true for Asia Pacific. It is not for America. It is also false for Central Europe, Northern Europe, and Southern Europe. This is because, as we saw in the preceding table, the if then else function replaces Central Europe, Northern Europe, and Southern Europe with Europe. Europe does not contain a space. Asia Pacific does and, consequently, is the only value where the expression is true.

Region En

Filter result value

Americas

0

Asia Pacific

1

Central Europe

0

Northern Europe

0

Southern Europe

0

It is not a very realistic example, but it should be enough to illustrate the point.


Security filters



Data security filters are another type of filter. It will lookup the users, groups, and roles in which a user is a member and determine the union of the security filter definitions as the basis of what data the user may be allowed to see.

Security filters can be expressions or query item filters.  Many users, groups, and roles may belong to any particular security filter.

In the illustration below, this is a security filter for members of the authors role.

Security filter definitions can be either expressions, such as embedded filter expressions or query item filters.

 

Filter timing

 

You can control the timing of the application of the filter with the post auto-aggregation property.



Filter administration

 

Each query subject has a manage filters tab.  In it is the list of existing filters.

You would administer stand alone filters in the module.  You administer query subject filters much the same way.  You administer embedded filter expressions via the manage filters tab of the query subject. You administer query item filters either in the manage filters tab of the query subject or by selecting the query item and choosing filter from the context menu.

 

If you wish to learn more about using the expression editor in filter expressions, I have written a blog topic about the expression editor and its functionality.

https://community.ibm.com/community/user/businessanalytics/blogs/ian-henderson1/2019/10/17/cognos-analytics-modelling-expression-editor


#CognosAnalyticswithWatson
0 comments
41 views

Permalink