If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Report Studio 8.4: Conditional Formatting always formats as "other"

Started by cpcognos, 08 Jun 2009 01:26:31 PM

Previous topic - Next topic

cpcognos

In Cognos 8.4 Report Studio:  I have a crosstab with budget pools on the vertical axis and budgets, actuals within year on the horizontal axis:

Pool | Budget | Actual | Var |
Pool 1 | 20 | 15 | .25 |
Pool 2 | 10 | 12 | -.2 |

Budget and Actuals are members from the data source.
Var is a calculated member:  ((Budget - Actual) / Budget)
The measure is dollars

I am trying to format the Var column background color using conditional formatting.  The colors will be Red, Yellow, Green, Blue.  For now I am just using two colors (Red or Green) to try to make this work, then I will build out the rest.

I have defined a variable "Status Color" with the following syntax:
if ([Query1].[Var] < 0) then ('Red') else ('Green')

I created two "Values" on the Variables screen for Status Color:  Red, Green

On the crosstab, I clicked the member fact cell for the Var column, and changed the Style Variable property to "Status Color".  Then, with the member cell still highlighted, I hovered over the Condition explorer and clicked Red, then set background color to Red.  I clicked Green and set BG color to Green.  I set the (other) value BG color to Yellow.

When I run the report, all of the cells in the Var column come out with BG color Yellow which is the (Other) format.  The cell values in the column are sometimes positive, sometimes negative, sometimes zero.  I expect the negatives to be Red and the positives Green.

What am I doing wrong?

blom0344

I have no experience with version 8.4, but with 8.1 we found out that you NEED to positively identify each possible value.
In your case:

CASE
WHEN
([Query1].[Var] < 0) THEN 'Red'
WHEN
([Query1].[Var] > 0) THEN 'Green'
ELSE 'Nocolor'
END

In other words, with 2 possible 'real' colors and use the third possibility in the ELSE.

Do not ask me why , but I managed to get the conditional formatting to work with some reports.

vij

Hi  cpcognos ,
Just check your data types of the data item on which you are giving the condition ,
many time it happens that report runs properly but because of not matched data types ,conditional settings won't give o/p correctly.


regards,
vij  :)

cpcognos

The solution was to use cellvalue() instead of [Query1].[Var].  My problem now is that I would like to test conditions on multiple columns at once...

i.e. [Var %] => 0 AND [Var $] < 10000  (of course this syntax doesn't work... just psuedo code)

Any ideas for this? 

blom0344

The concept 'column' is very much based on a list. Crosstab data is about intersections.
CellValue() was introduced with 8.3. We still develop on 8.1 and the regular expressions that mention in the original first posting do work, though one still needs to tweak them to get it working.
Did you try to get it working by using the earlier suggestions?