Sorting and members

Part 1: Sorting

You can control how values in a query item are sorted. This also allows you to generate relational members for the query item, which can be used in your reports.

This paper deals with sorting including a description of custom sorting, which was introduced in Cognos Analytics (CA) 12.0.1. In time, a second paper dealing with members will be published.

Sorting

Sorting is a set of properties which allow you to define the order in which the values of a query item appear in a report or dashboard.

The sorting rules are defined in your data module. You can control whether values of a query item are sorted, the sort order, what rule defines a sort order, and where nulls are placed.

In a dashboard you can also set sorting. Those would override the data module defined sorting settings. This sorting will apply to that dashboard only.

You can define whether sorting will take place. You can define the basis of the sort. Starting with CA 12.0.1, this includes custom sorting. You can define what is used as the sort values, which could be the query item itself or any other query item in the query subject.

Sorting properties

Sorting is performed on query items. Sorting is controlled by properties of a query item. The properties exist in the general tab of the properties slide out for a query item. They are grouped in the Members display section of the properties slide out.

If the usage of a query item is either attribute or identifier, then the sorting properties are available. If the usage of the query item is set to measure, then the sorting properties are not displayed.

After metadata import, you need to confirm that the Usage property of each query item is set correctly. This is part of the general review of metadata after importing, which is also part of the Cognos best practices. It is particularly important to ensure that usage is set correctly, not just because it allows you to have control over sorting but more importantly usage determines the treatment of the query item in the generation of SQL.

There are five properties controlling sorting. They are Display options, Sort members by, Members order, Null values, and custom sort order.

 

Display options

The primary property which governs sorting is Display options. As well as sorting, display options controls the display of members in the metadata tree.

Display options has four settings.  They are automatic, show members, hide members, and custom.

The default value for Display options is automatic except when the source column of the query item has been determined, when the source file or schema was loaded, to support custom sorting.

When the value is set to automatic, Cognos will try to sort values, based on the subsidiary sorting property rules, but will not guarantee that sorting will take place.

When the setting value is show members, Cognos does the same thing but explicitly undertakes to sort the values.

Hide members will tell Cognos not to try to sort the values.  It will also disable the display of members in the metadata tree of the data module and of dashboards.

Custom will sort the query item by the order of members defined in the custom sort order property.

When the value for Display options is set to either automatic or hide members then the subsidiary sorting properties are disabled. They will only become enabled if the option is show members. The custom sort order property will only be enabled if the display options setting is custom.

For both automatic and show members, the sorting will use the subsidiary sorting property settings. If you do not want to explicitly set sorting on for a query item but nevertheless wish to define sorting rules then you can do this by setting the display options setting to show members, set the sorting rules you want, and then set the display options setting back to automatic.

If you have an older release of CA, the properties you will see will be slightly different. The functionality is largely the same.  Later in the paper, in the section called upgrade, a discussion of this older UI will take place.

Sort members by

The Sort members by property governs which query item will be used to do the sorting.

Sort members by contains a drop-down list of the query items in the query subject.

By default, the Sort members by column will be the query item itself.

This means that the default sort rule will be by the values of the query item. If the query item has a data type of text, then the order will be alphabetical. If the query has a numeric data type, then the sort order will be numerical order. If the query item has a data type of Date or Time, then the sort order will be based on the rules of Dates.

Here is an example of the differences between sorting of text and numeric data types. The picture illustrates two query items which have the days between the product introduction date and either the discontinued date or the current date calculated as the life duration of the products.  At the top of the picture, you will see the sorting of members of a query item where the duration value has been cast to a text data type. At the bottom of the picture, you will see the sorting of members of a query item whose expression has not been cast to text but remains as a numeric data type.  In both cases the sorting is set to automatic and the members order value is ascending.

You can set the Sort members by value to any other query item in the query subject as well, except to measures. You could use this to apply a sorting rule which is more appropriate to the nature of the data. The query item used in the Sort members by value could be a simple reference to a column or could be an expression.

A very simple example of that would be sorting a column with month names by another query item which contains the month numbers, as the month names are not alphabetically in the same order as their position in the year.

Since the alphabetical order of the English names of the months of the year (and the months of the year for most if not all other languages) does not match the order of the months, this can be annoying, and more importantly, muck up the presentation of reports and charts and interfere with forecasting. By having the Sort members by property use another query item, which has month numbers which will allow for the imposition of a sorting rule which actually matches the order of the months, the members would be sorted in the order you would want.

In the picture below, I defined sorting on a query item called Month En. The values of Month En are the English names of the months of the year. I have a query item called month number. It consists of a number for each month. For example, January has the value of 1 and December has the value of 12. My data base has this built in, but it is not difficult to create a calculation in your query subject where you can specify values for sorting. I will show an example of such an expression later.

Here is Month as seen in a report. The sort order of the previous picture is the same. You will notice that the Display options property has an effect on the sorting.

 


Sort order index

Generally, the preferred approach to defining a Sort members by column is to create the sort column in the table in the data base. If that is not possible you would need to create a sort in a calculation in the data module. If it is built into the data base table, the processing time will be less. This is because a calculation needs to be performed and evaluated and the results delivered whereas a column just needs to be scanned through and operated on.

You need to evaluate the tradeoffs entailed in relying on model-based sorting expressions, including custom sorting, rather than data base sorting columns such as the performance hits which could be encountered and the costs of implementing sorting columns in your data base.

Here is a calculation expression which looks up the values of a Month column and returns a number value. You could use that as a template for a similar sort (no pun intended) of sorting calculation expression.

case

When MONTH_EN ='January' then 1

When MONTH_EN ='February' then 2

When MONTH_EN ='March' then 3

When MONTH_EN ='April' then 4

When MONTH_EN ='May' then 5

When MONTH_EN ='June' then 6

When MONTH_EN ='July' then 7

When MONTH_EN ='August' then 8

When MONTH_EN ='September' then 9

When MONTH_EN ='October' then 10

When MONTH_EN ='November' then 11

When MONTH_EN ='December' then 12

else 0

end

It is probably good form in general to have provision for else in all cases, but in this one the Month En column has, in addition to the names of the Months, a record called Opening balance, so it is especially so.

Here is a similar expression which generates sort values which include the year, which will uniquely identify months for sorting purposes. 

cast (

cast (CURRENT_YEAR, varchar(4)) ||

case

When MONTH_EN ='January' then '01'

When MONTH_EN ='February' then '02'

When MONTH_EN ='March' then '03'

When MONTH_EN ='April' then '04'

When MONTH_EN ='May' then '05'

When MONTH_EN ='June' then '06'

When MONTH_EN ='July' then '07'

When MONTH_EN ='August' then '08'

When MONTH_EN ='September' then '09'

When MONTH_EN ='October' then '10'

When MONTH_EN ='November' then '11'

When MONTH_EN ='December' then '12'

else '00'

end

, integer)

Here is an example of an expression to generate a sort index for the days of the week.

case

When DAY_OF_WEEK  ='Sunday' then 1

When DAY_OF_WEEK = 'Monday' then 2

When DAY_OF_WEEK  ='Tuesday' then 3

When DAY_OF_WEEK  ='Wednesday' then 4

When DAY_OF_WEEK  ='Thursday' then 5

When DAY_OF_WEEK  ='Friday' then 6

When DAY_OF_WEEK  ='Saturday' then 7

else 0

end

I have tested sorting with values which have unique numbers for each month in every year (i.e., January 2022 has a sort number which is different from that of January 2023) as well as values which have only numbers for a month. The behaviour does not seem to differ much. Sort is not a key so only one January is generated. If there is forecasting set on in a dashboard sometimes the order in which you put the query items into the widget can affect whether forecasting will generate a message about the order of categories. See the sorting and forecasting section for more information.

You would usually use years to differentiate the months (above the months in the widget or used elsewhere in the chart, such as in a year over year (YOY) chart.

Starting in Cognos Analytics 12.0.1 there is support for custom sorting, similar to the custom sorting which exists in dashboards. It is described in detail later.

Members order

Members order defines the order by which the values are sorted. The options are ascending and descending.

If the Sort members by query item is a numeric data type, then the ascending sort will be from the lowest value to highest. The descending sort will be from highest to lowest. If the Sort members by query item is a text data type, the ascending sort will be from the alphabetically first character to last character.

This provides an additional degree of control over the sort order, in addition to the Sort members by property.

It is possible that for some alphabets the sorting will be slightly incorrect. You need to be aware of the data base collation properties and their behaviour. In addition, the browser’s locale and the user’s preferences could affect sort order.

More information is available here:
https://www.ibm.com/support/pages/how-achieve-different-sort-order-dynamic-query-mode

Null values

The Null values property governs the placement of null values. You can have nulls either at the beginning or the end of the sorted values.

The Null values property will only be displayed if the query item’s Supports Null values property is set. This property is in the advanced section of the query item’s properties.

The Supports Null values property will be set on if the underlying data base column has a property which will indicate that nulls can appear in the column or, if you are using an uploaded file, if the analysis of the uploaded file has determined that a column contained nulls in it. In addition, the Supports Null values property can be set on or off manually. If it is set off the nulls will be sorted at the end of the list, no matter what the setting of Null values is.

This picture shows the Members order when the nulls placement setting value is last.

This picture shows the Members order when the nulls placement setting value is first.

Viewing sorting results

One use I have for members is to confirm the display of sorted members. After you make a change to one of the sorting properties if you want to see the result of the change press the refresh Members menu option in the context menu.

If you have the Display options property set to show members, then you can generate the members tree in the module tree. It will have the sorting you have defined. Another fast way to view the sorting results is to press the try it button and create a list report with the query item which you are sorting.

Sorting and forecasting

Having sorting set on for time columns such as month or day of week helps forecasting in a dashboard. Sometimes it can figure out what order you really want but sometimes it can’t. Having sorting on will allow a more consistent behaviour.

If you are working with time data and you do not have the data sorted, you can get warnings such as this.

Annotation warnings:
• Forecasting requires visualization data to be in chronological order. Therefore, your visualization data was automatically reordered. Disable Forecasting to return the visualization data to its previous order.

Although in such cases forecasting has managed to determine an order it is bought at the price of a warning icon, which you would need to explain, and of an order which might not be what you want. Subsequently, if your users will want to use forecasting then it is a good idea to have your time query items sorted in the order that makes business sense.

In addition, the choice of sort value will affect results.

For example, assume that you have a sort value where the months sort values are 1 to 12.  Assume that you have put your months column into the x axis of a widget which supports forecasting and then turned forecasting on.  Assume you then put the years column into the x axis above the months column. You will probably get a warning similar to the one shown above. If you had put years into the widget first and then put months into the widget you will probably not get the warning.

If the sort value was something which uniquely identifies each month with the year value included, such as, for example, 202301 for January 2023 and 202401 for January 2024, then this warning will probably not be generated.  You can also force the warning to go away by removing the month column from the widget and then dragging it back again.

Automatic versus show members

Sorting is attempted when automatic is chosen for the display options setting but is not guaranteed to take place. If you have display options set to show members Cognos will try to sort but it is bought at the price of having members showing up in the metadata tree. How would you go about deciding what option to choose? I have not observed behaviour differences between sorting with automatic and with show members. This does not mean there would not be differences. I think in general deciding which one to choose would be driven by whether you want the members to be generated for your users and whether the query items you want sorted fall into the situation where they are not sorted unless you explicitly set sorting on through show members.

Custom sorting

In CA 12.0.1, Cognos has introduced an additional sorting order option, custom sorting.

The primary objective of automatically custom sorting things like day and months by their natural order is so you do not need to. Providing smart automatic sorting to situations where the natural sort order is possible to be known frees modelers and users from a small but annoying task. For the latter especially this could be useful as they may not be aware of the existing methods to sort the values and, although those methods are not particularly intellectually challenging, the time invested in seeking assistance and in implementing sorting could be better spent doing almost anything.

Custom sorting allows you to manually sort the values in a query item according to how you want them to be sorted without using a Sort members by column. It is similar to the dashboard custom sorting.

Custom sorting will be autogenerated for columns which have the following taxonomy. Taxonomy is presented in the properties as the represents properties. The taxonomy is determined by examining the column header and sampling the data.

Days of week

Months

Seasons

It will endeavor to do this for columns with short name values (e.g., Jan, Feb) as well as full names.

The autogenerated custom sorting will exist in newly loaded schemas, uploaded files, and data sets. If you have query items in existing schemas and files where you would want to have custom sorting, you will need to manually sort them. There is a known issue in which relinking a data module to a schema which was loaded in 12.0.1 and thus, should have custom sorting defined, or reopening a data module which has had its schemas reloaded will not add the custom sorting.

You can refine the automatic custom sorting. For example, the default custom sorting will have days of the week sorted so that Sunday is the first day of the week. If you want Monday to be the first day of the week you could edit the custom sorting to put Monday at the top of the list and Sunday at the end.

There is a hard coded limit of one hundred members for the custom sorting editor. If you have more than 100 members you would need to use a sort index via the Sort members by property.

Custom sorting options

Any remaining items which have not been put into the list of members in the custom sorting definition will generally, but not always, be placed at the end, unlike the dashboard custom sorting, which allows you to put them at either the beginning or the end.  If the order is not what you want, then try with the whole value search set on. An alternative would be to explicitly define the order you want in the custom sorting definition.

Assume a column with values from 1-12. Assume that a custom sort definition has been created but only the values of 12, 11, 10, 9, 8, and 7 have been put into it, as shown in the illustration below.

 

With the whole value search not set on you will have sorting such as this. You will note that 1 is positioned between 11 and 10.

 

With the whole value search setting set on you will have sorting such as seen in the illustration below. Because the whole value is being used, 1 will be put into a position which would be more likely to be expected. It was being but with 12 and 11 when whole value search was not set on because the 1 of the 12 and 11 values were being used only.

 

The default value for case sensitive search will ignore the case of members in the custom sort.

Assume you had custom sorting defined as this, where April does not have its first letter capitalized.

January

February

March

april

September

Assume that the value for April is April (i.e., the first letter is capitalized.)

If the case sensitive search property is not set on, then the order of members would be this:

January

February

March

April

September

This is because the member April is the same as the custom sorting member april, as the case of the letters is being ignored, and so April is placed in the position which april has been placed.

If the case sensitive search property is set on, then the order of members would be this:

January

February

March

September

April

April is placed after September because its value does not match the value of the custom sorting member value in the custom sorting definition and is subsequently placed at the end of the list of sorted items.

Mixes of names will behave similarly. For example, assume you have a column with month names fully spelled out.  Assume you apply a custom sorting with the definition as below.

Jan

February

March

April

May

June

July

Aug

Sept

October

November

Dec

The resulting sorted members will be this:

February

March

April

May

June

July

October

November

August

December

January

September

August, December, January, and September are sorted alphabetically as the member values did not appear in the custom sorting definition.

Custom sorting and SQL

The dashboard custom sorting rule is stored in the dashboard specification and does not affect the SQL as the sorting for the dashboard custom sorting is performed locally in the dashboard.

Custom sorting for schemas and uploaded files will be included in the generated SQL. Here is an example where the custom sorting has been generated for the months in this order.

January

February

March

April

May

June

July

August

September

October

November

December

Opening balance

This is SQL illustrating how custom sorting is generated in the generated Cognos SQL

SELECT

    VIEW_TIME_MONTH_DIM0.MONTH_EN AS Month_En,

    POSITION(LOWER(';' || VIEW_TIME_MONTH_DIM0.MONTH_EN) IN LOWER(';Opening balance;December;November;October;September;August;July;June;May;April;March;February;January')) AS MONTH_EN_for_CustomSort

FROM

    GOSLDW_DNB_DB2_custom_sorting..GV1022DW.VIEW_TIME_MONTH_DIM VIEW_TIME_MONTH_DIM0

GROUP BY

    VIEW_TIME_MONTH_DIM0.MONTH_EN,

    POSITION(LOWER(';' || VIEW_TIME_MONTH_DIM0.MONTH_EN) IN LOWER(';Opening balance;December;November;October;September;August;July;June;May;April;March;February;January'))

ORDER BY

    MONTH_EN_for_CustomSort DESC NULLS LAST,

    Month_En ASC NULLS LAST

You will notice that the order in the positioning is the reverse of the order of the members in the custom sorting definition. It needs to be in reverse order so that a proper sort order index is generated with the proper weighting, and it has provision for any remaining, unspecified, members. As we saw with the unspecified members being positioned in with specified members in the custom sort earlier, the sorting will, by default use the first character of the member.  To ensure that they will be positioned at the end you would need to use the whole value search setting.

You will also notice that the same expression is used in both the select and group by clauses. This is because it is necessary to have the result in a predictable order.

Storage of custom sorting

The custom sorting is stored as part of the metadata of the uploaded file or schema. As such it is available to all data modules which consume the file or schema or, in the case of an uploaded file, all dashboards which are consuming it directly. I’m going to sneak in an editorial comment that it’s probably a good idea to put your files into a data module and use the data module in dashboard then consume them directly in dashboards.

When you import an uploaded file or schema into a data module any custom sorting definition associated with that object will be stored in the custom sorting section. Because of this, they will be available for you to use for other query items, much like how Excel stores custom sorting.

As you add schemas the custom sorting lists will be added. If there are conflicts then the first one will win and there will not be duplication.

Like other sorting, the custom sorting will be applied first, before any sorting defined in the dashboard.

Multilingual considerations

Custom sorting is created for the locale of the user who has uploaded a file or loaded a schema.

In general, I think that if you are going to need to model for multiple locales and have sorting then you would be best to use a sort index rather than rely on custom sorting. An illustration is shown below.  Each of Month English, Month French, and Month German will have been sorted by Month sort index. Month would pick up the appropriate column.  Because all 3 of the possible source columns are sorted in the same way, a German report consumer would see everything sorted in the same order that his French and English counterparts will see.

Month	Month English	Month French	Month German	Month sort index
Some expression usually using a macro to look up the user’s locale and substitute the appropriate column identifier	January	Janvier	Januar	1
	February	Février	Februar	2
	March	Mars	März	3

In cases where the data for another locale is sufficiently close to the user’s locale, you can get custom sorting generated for those columns too. It is probable that the generated custom sorting will be incorrect.

For example, I loaded a schema while logged in as a user whose locale preferences were German. One table in the schema has columns for things such as months of the year. There are several columns, with each column containing the values of a language, such as English, German, French, Japanese, and Norwegian.

In addition to my expected custom sorting for my German language columns, the Norwegian months column got custom sorting. The list below is what was generated. It was a mix of Norwegian and German.

Januar
Februar
März  //This is German, not Norwegian.
April
Mai
Juni
Juli
August
September
Oktober
November
Dezember  //This is German, not Norwegian.
Desember //This is Norwegian for December.
Mars  //This is Norwegian for March.

I think this reenforces the guidance that you need to review the result of metadata import, which is one of the long-standing Cognos proven practices.

In case you are wondering, if you load the schema (or upload a file) with a locale set to Norwegian you will get two custom sorting generated.  One will have the correct Norwegian months and in their correct order. It will be assigned as the custom sort for the Norwegian months column. I think the fact that the month values are rendered as all lower case is a defect and it has been logged. It does not affect results unless you have case sensitive search setting on.

januar

februar

mars

april

mai

juni

juli

august

september

oktober

november

desember

The second custom sorting will be assigned to the German months column. 

januar

februar

mars

april

mai

juni

juli

august

september

oktober

november

desember

Dezember

März

Upgrade

In older releases of Cognos Analytics, the sorting properties were Sort, Sort by, Order, and NULL values. The differences between these properties and the current properties are purely cosmetic.

Sort was the same as Display options and, like Display options, controlled the enablement of the other properties.

In Cognos Analytics 11.0.x, Sort was a toggle switch. As such, sorting was either enabled or not. The upgrade mapping of the Sort property to the Display options property will set the Display options property value to show members if the Sort property was set on and will set the Display options property to automatic if it had not been set on. Because of how sorting functionality worked, this mapping more accurately reflects the behaviour of the functionality. If you wish to explicitly not sort the query item’s values, then you will want to set the Display options value to hide members.

In early versions of Cognos Analytics 11.1.x the toggle switch was replaced by a drop-down list. The label for the property was sorting. There were three options in the drop-down list. They were automatic, enabled, and disabled. If the sort toggle switch had not been set on, then the value chosen for the drop down would be automatic. If the toggle switch had been set on, then the value chosen for the drop down would be enabled.

In later versions of 11.1.x the label of the property was changed to members. This was done to incorporate the support for member generation. The mapping of property values from prior releases is the same. The enabled property was renamed to show members. The disabled property was renamed to hide members.