Within a query I often create custom groups of data. Take this example from Go Sales:
I want to group these months into Seasons. To do so I can create a calculation:
case
when [C].[C_Great_outdoors_data_module].[page_6].[Month_] in ('Mar', 'Apr', 'May') then 'Spring'
when [C].[C_Great_outdoors_data_module].[page_6].[Month_] in ('Jun', 'Jul', 'Aug') then 'Summer'
when [C].[C_Great_outdoors_data_module].[page_6].[Month_] in ('Sep', 'Oct', 'Nov') then Autumn'
else 'Winter'
end
This results in the data looking like:
This data is still sorted by Month. If I want to sort by Season then I can’t uses Season itself as the alphabetical order is not the correct order. Here I have sorted the data by Season and you can see the order is not correct.
In this situation most report authors fall back to creating an additional data item to sort by. In this example I have created a Season Order calculation:
case
when [C].[C_Great_outdoors_data_module].[page_6].[Month_] in ('Mar', 'Apr', 'May') then 1
when [C].[C_Great_outdoors_data_module].[page_6].[Month_] in ('Jun', 'Jul', 'Aug') then 2
when [C].[C_Great_outdoors_data_module].[page_6].[Month_] in ('Sep', 'Oct', 'Nov') then 3
else 4
end
I can then sort by Season Order to get the data in the correct order:
Whilst this resolves the issue, it does create a number of problems.
- I now have two calculations to maintain and keep synchronised.
- I have added complexity to the SQL query.
If we want reports to run as quick as possible, this additional calculation will add to run times.
Is there a better way?
Within Cognos the data in the query can be manipulated on the page using a report expression. Basically, the data in the query, isn’t the data we have to display on the page. With this in our minds we can alter our original season calculation to include something that will make the data order correctly.
In this case I have prefixed the Season with a number:
case
when [C].[C_Great_outdoors_data_module].[page_6].[Month_] in ('Mar', 'Apr', 'May') then '1Spring'
when [C].[C_Great_outdoors_data_module].[page_6].[Month_] in ('Jun', 'Jul', 'Aug') then '2Summer'
when [C].[C_Great_outdoors_data_module].[page_6].[Month_] in ('Sep', 'Oct', 'Nov') then '3Autumn'
else '4Winter'
end
If I run this query, and sort by Season the data now looks like:
This has simplified the query – I now only have one calculation that groups and sorts the data correctly. But it has a prefix that I don't want in the report output.
Using the Data Item in a List Report
We can use Season on a list report and order by Season. (Note that within these list example I have left Month in the list as a data item, in later examples I have removed this).
The list now looks like:
But we don’t want to display the Season’s numeric prefix within our report output.
Within a List we can add a report expression to remove the prefix.
By default, the Season column is based on the Season data item.
I can change the Source type to report expression:
I can use the following report expression to remove the numeric prefix:
The list now looks like:
And the list output now looks like:
I have removed the numeric prefix using a report expression.
Other Data Containers
I can use the same techniques on other data containers. That is to sort by Season and use a report expression to remove the numeric prefix.
For example, I can also use of a report expression within a crosstab:
To give:
And even on charts:
#IBMChampion