Crosstab - Advanced Conditional formatting ( drill behaviour enabled)

[cognosjon]

Apologies for the complicated wording of this post :o
I've found it very difficult to put into words the issue that I am experiencing. I have provided two small screenshots which are referenced below so if anyone can shed any light it would be appreciated.

I've a crosstab created using DMR within report Studio ( Cognos 8.4). The report has drill behaviour enabled and simply the report has a two level hierarchy.
In the Rows of the crosstab is an item called Owner which upon drill down has a child of Engine ( this details all engines that belong to a specific owner).

Each engine has a score and detailed for each engine is a threshold. An advanced conditional style has been applied to the mesures area of the crosstab to provide Red, Amber Green formatting with the calculation at Engine score > Threshold.

Each engine within the sample report I have created has a threshold of 5. Therefore if the engine score is > 5 the style will apply a red background to the cell.
As you can see from screen 1 that I have provided no engines meet this condition and are therefore green.

However when I drill back up the hierarchy to the owner level ( screenshot 2) you will see that the aggregated scores of the engines are greater than the threshold score held at the engine level so it applies the conditional styling of Red.

Whilst I understand  why this is happening it is not what I'm trying to achieve through the report. Within this example I still want to show the owner ARA with a green cell background as no engine exceeds the threshold.
If one of the engines did exceed the threshold then I would like the owner level value cell to change to Red to reflect this.

Any help/guidance greatly appreciated

Thanks

[MFGF]

Hi Jon,

I can't view the attachments on my iPhone, but I reckon the trick will be to change the aggregation property of the measure from Sum to Maximum. I can't test it right now either - not until IBM develop a BI server that runs on iOS :)

Best regards,

MF.

[cognosjon]

Cheers for the reply,
I will be honest and admit that I haven't tried your suggestion yet ( I will at some point)
I had some success using the following calculation which when rolled up provides the functionality the users are seeking.

average(aggregate([engine score] for [engine ID],[Date]))

Jon