Conditionally formatting single cell in row CrossTab

[bmccall]

What I'm trying to do is highlight the cell in a particular row that is farthest from the average in that row; my data consists of features (one feature per row) and the values for the rows are the code values for each feature (each feature has the same code names).  I'm using data items to get the largest value in the row, its distance from the average, the smallest value, its distance from the average, and then use those to calculate which cell should be highlighted.  Below is how I've set those data items up, all in a group called QueryGroup:

Largest value (called maxVal): maximum([codeValue])
Distance from largest value to feature average (called maxValDiff): abs([featureAverage] - [maxVal])
Smallest value (called minVal): minimum([codeValue])
Distance from smallest value to feature average (called minValDiff): abs([featureAverage] - [minVal])
Determine which value should be highlighted (called selectedValue): if([maxValDiff] > [minValDiff]) then ([maxVal]) else ([minVal])

Then I set up a boolean variable to be used to format and set it to: [QueryGroup].[codeValue] = [QueryGroup].[selectedValue].  What I thought this should be doing was for each cell in a row, check if the value of the cell is equal to the value that should be highlighted because it is the farthest away from the average.  But nothing gets highlighted.

The attachment shows, on the left, what the report should look like and, on the right, what I added for testing purposes.

When I added the testing columns, I set a conditional format for the 'MaxVal' column using a variable set to [QueryGroup].[maxVal] = [QueryGroup].[selectedValue], so it would highlight the value in the 'MaxVal' column if it equaled the value that should be highlighted; I set the formatting up the same way.  This highlighted as expected.

Add ideas on what I'm missing?  I don't normally work with Cognos (I'm a UI developer), so this is rather frustrating as I would have been able to code a way to do this much easier than Cognos is making it.

[CognosPaul]

There are a few ways to do this, but before we go into it - are you using a relational source (like Oracle, DB2, or SQL Server) or an OLAP source (like SSAS, EssBase, TM1)?

[bmccall]

CognosPaul, we're using Oracle, so relational source.

[CognosPaul]

It's a crosstab with Feature Name is on rows, and Feature Code on columns?

[bmccall]

Yes, feature name on rows and feature code on columns.

[CognosPaul]

Okay, a crosstab is a bit more difficult, and I don't like my solution so much.

Using the the default GO Sales package, I set up a crosstab with product line in rows, and order method in columns, with avg of quantity as the measure.

With that set up, it's possible to identify the cell farthest from average with:

Code Select Expand

if([Quantity] - average([Quantity] for [Product type])
=maximum(average([Quantity]) for [Product type]) - average([Quantity] for [Product type])
) then (1) else (0)

The problem here is that it needs to be in the output to work with the crosstab. We can use various tricks to make it look like a single cell, but exporting to Excel will reveal the deception. Maybe you can merge it into the design?

[bmccall]

Thanks for the help, CognosPaul.  We were finally able to get it working.  Turns out the report that I was given to try and get this to work wasn't set up correctly to begin with, so even though how I set up the logic was correct (used a similar function you have), I wouldn't have been able to get it working anyway.  From how it was explained, the crosstab was set up with having the feature average and code values for the features not being connected.  To get it working, they had to create the crosstab by selecting both data items (feature average and code value) and dragging them onto the designer to make sure Cognos knew there was a relationship between the two.  Also, when I was initially trying to get it to work, I included the data items I needed in the properties for the columns storing the code values instead of including them with the row.

I'm sure someone else could explain it better what needed to be done, but the short story is it's working and I hope to not be asked to help with Cognos reports after this :P