I have a crosstab, and I would like to highlight the individual cells based on whether they are significantly above the average value. Having looked into conditional styles I can only see ways to highlight things based on hard-coded values (e.g. turn green if the value in the field is more than 10), which isn't helpful because the values in my crosstab could change significantly at different times of year.
I'd like to do something more like this:
[Value] > SUM([Value])/[RowHeader]
To identify where things are above average, or even better something that calculates whether something is in the top quintile and highlights it, but SUM isn't an option in the condition builder.
Is this possible?
Quote from: Charlie_B on 05 Nov 2019 03:34:25 AM
I have a crosstab, and I would like to highlight the individual cells based on whether they are significantly above the average value. Having looked into conditional styles I can only see ways to highlight things based on hard-coded values (e.g. turn green if the value in the field is more than 10), which isn't helpful because the values in my crosstab could change significantly at different times of year.
I'd like to do something more like this:
[Value] > SUM([Value])/[RowHeader]
To identify where things are above average, or even better something that calculates whether something is in the top quintile and highlights it, but SUM isn't an option in the condition builder.
Is this possible?
Hi,
SUM() is a SQL function, but isn't in the list of Cognos summaries you can use in the expression editor. The equivalent summary in Cognos expressions is total()
There is also an average() summary you can use.
You'll need to define any query calculations like this as items in your query, then refer to the items in the expression for your condition. For any items not being displayed in the crosstab, you will also need to add them to the Properties property of either the row edge or the column edge of your crosstab (otherwise the query engine will ignore them).
Cheers!
MF.
Check an attached Report Spec with Screenshots.
It had been built using Cognos Analytics Trial in the Cloud.