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
use the string variable for this
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
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
Thank you Sathya...
Even I was trying with string variable only but missed this logic.. :)
Will try this and update you...
Regards,
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
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