Problem with string variables showing '>100'

[Marvin Falentin]

Hey everyone,

I've been absent for quite some time now, but I got another question so I hope you can help me once more :)

This time I'm using Cognos 10.2 (so far I only worked with Cognos Analytics).

I've got a crosstab displaying 3 different values (current sales, last years sales and this years predicted sales) for 5 different products split into different categories.

         x           y          z
    1 2 3       1 2 3      1 2 3
A
B
C

Please bare with me and my "drawing skills".

My assignment now is to change the values in the crosstab so that everything > 100 will be displayed as ">100" and everything < 0 will be displayed as "< 0" (our controllers like it better that way).

I tried to fulfill this task using string variables with the following expression:

CASE
WHEN [a] IN ( 'abs123' ) AND [YTD_BTR] > 100 THEN '>100'
WHEN [a] IN ( 'abc123' ) AND [YTD_BTR] < 0 THEN '< 0'
ELSE 'STANDARD'
END

'abc123' is the productID and YTD_BTR the "current year sales".

I also added those 3 variables ( '>100', '<0' and 'STANDARD') to it.

When I select the values (e.g. '<0') and create the report it still shows the same result as without the variable so instead of '<0' it still shows '-29' or whatever.

Any idea what I'm doing wrong? I know that normally you would use variables for conditional formatting but to me it still sounds like this solution should work right? I'm open to any idea how to make this work. If there is any more information I can add to help you understand my problem or my solution please just ask.

Kind regards,

Marvin

[Lynn]

Hey everyone,

I've been absent for quite some time now, but I got another question so I hope you can help me once more :)

This time I'm using Cognos 10.2 (so far I only worked with Cognos Analytics).

I've got a crosstab displaying 3 different values (current sales, last years sales and this years predicted sales) for 5 different products split into different categories.

         x           y          z
    1 2 3       1 2 3      1 2 3
A
B
C

Please bare with me and my "drawing skills".

My assignment now is to change the values in the crosstab so that everything > 100 will be displayed as ">100" and everything < 0 will be displayed as "< 0" (our controllers like it better that way).

I tried to fulfill this task using string variables with the following expression:

CASE
WHEN [a] IN ( 'abs123' ) AND [YTD_BTR] > 100 THEN '>100'
WHEN [a] IN ( 'abc123' ) AND [YTD_BTR] < 0 THEN '< 0'
ELSE 'STANDARD'
END

'abc123' is the productID and YTD_BTR the "current year sales".

I also added those 3 variables ( '>100', '<0' and 'STANDARD') to it.

When I select the values (e.g. '<0') and create the report it still shows the same result as without the variable so instead of '<0' it still shows '-29' or whatever.

Any idea what I'm doing wrong? I know that normally you would use variables for conditional formatting but to me it still sounds like this solution should work right? I'm open to any idea how to make this work. If there is any more information I can add to help you understand my problem or my solution please just ask.

Kind regards,

Marvin

A crosstab can't have a measure that is text, only numbers. Try this approach: unlock the report and drag in three separate text items for the three separate conditions. Conditionally style each text item to show only when the condition is met. You may find the CellValue() function handy for defining the conditions.

[Marvin Falentin]

Hey Lynn, thank you again for a very helpful answer :)

Can you tell me if my approach would have worked for a list rather than a crosstab? Just out of curiosity really.

[Robl]

It would have worked I think.
But, I would suggest avoiding variable where possible and instead use conditional styles.

However,
In a list you can display string values.
You can also set a value as a string.
So you could have made a data item as;

CASE
WHEN [a] IN ( 'abs123' ) AND [YTD_BTR] > 100 THEN '>100'
WHEN [a] IN ( 'abc123' ) AND [YTD_BTR] < 0 THEN '< 0'
ELSE cast(YTD_BTR, varchar())
END

and this would have just displayed exactly what you wanted.

[Marvin Falentin]

Hey, thanks for the answer :)

If I get some extra time I'll try it out and see if it works!