Conditional Formatting in CrossTab

[mrcool]

Hello All,

I have following requirement for a crosstab report.


   Jan-09   Feb-09        Mar-09
XYZ   12             20                    15
ABC   10             34                    24
PQR   7             10                    20

For Example if we consider products in rows and months as columns and quantity as measure
I need to apply conditional formatting based on the previous month value of the measure.
i.e If the quantity decreases by 5% or more then red and if the quantity increases by 5% or more then Green.
In the above example if consider for the month of Mar -09 The 1st value should be highlighted with red and second value should be highlighted with green and so on..
Please let me know if you require any further information.
Any help would be highly appreciated.

Regards,
Mrcool

[jahnavi]

use the string variable for this

[mrcool]

Thank you jahnavi..
But in crosstab how can I compare columns??If it is list I would have handled easily. The problem here is with crosstab.
Plz explain in detail how string variable works here.


Regards,
Mrcool

[sridharansathya]

Hi,

String variable would do mate , only thing to concentrate is the logic ....

Let me explain it ....


U told the limit is 5% either way


Upper :

qty + ((qty*5)/100)

Lower :

qty - ((qty*5)/100)

Condition is u need to compare with the previous month

so ADD_MONTHS(Month,'-1') ------ Previous Month

In the string variable use the below logic

when qty <= total(Lower for Previous Month) then red
when qty >= total(Upper for Previous Month) then green

Let me know whether it resolves your issue or not


Thanks,
Sathya

[mrcool]

Thank you Sathya...
Even I was trying with string variable only but missed this logic.. :)
Will try this and update you...


Regards,
Mrcool

[mrcool]

Hello All,

I am able to fix my problem finally.
I used following calculation to calculate the % increase.

(running_difference ([Quantity] for [Product])) /([Quantity]-running_difference ([Quantity] for [Product]))..

I added this data item as another measure in crosstab and tried applying conditional formatting to my original column based on this dataitem.

The problem here is conditional formatting on quantity based on other measure(% data item). This looks like bug in 8.3 and which is fixed in 8.4
Let me know if any1 has faced similar situation and able to fix in earlier versions.
  Below is context from 8.4 user guide..
Crosstab Measures Can be Formatted Based on Other Crosstab Measures
You can apply conditional styles to crosstab cells based on an adjacent value in the crosstab. For
example, you can conditionally color the bubbles in a bubble chart based on any combination of
the measures, and you can conditionally color an entire row of the crosstab based on any measure
in the columns.

Regards,
Mrcool

[raro]

made a short clickthrou example of how to conditional format some crosstabe cells based on something not in the crosstab with cognos 8.4

http://www.zeitfenster.de/cognos/C84_How_to_format_a_crosstab_in_Cognos_8.pdf