Cognos ReportStudio 8.2 - Conditional Formatting

[CognosAdmn]

Hello everyone,

I am trying to conditionally format a report.
this report has the following columns. ActualValue, Value1, Midpoint, Value2 (where Value1<Midpoint<Value2 or Value1>Midpoint>Value2
)
I need to change the background color for ActualValue field.

this is the expression i am using:
CASE
WHEN ( ([Query1].[Value1] > [Query1].[Value2]) and ([Query1].[ActualValue] > [Query1].[Midpoint]) )
THEN ('Red')

WHEN ( ([Query1].[Value1] > [Query1].[Value2]) and ([Query1].[ActualValue] < [Query1].[Midpoint]) and ([Query1].[ActualValue] > [Query1].[Value2]) )
THEN ('Yellow')

WHEN ( ([Query1].[Value1] > [Query1].[Value2]) and ([Query1].[ActualValue] < [Query1].[Value2]) )
THEN ('Green')

WHEN ( ([Query1].[Value2] > [Query1].[Value1]) and ([Query1].[ActualValue] < [Query1].[Midpoint]) )
THEN ('Brown')

WHEN ( ([Query1].[Value2] > [Query1].[Value1]) and ([Query1].[ActualValue] > [Query1].[Midpoint]) and ([Query1].[ActualValue] < [Query1].[Value2]) )
THEN ('Orange')

WHEN ( ([Query1].[Value2] > [Query1].[Value1]) and ([Query1].[ActualValue]> [Query1].[Value2]) )
THEN ('Blue')

END


But, I dont see any of the colors in the report when its run. If i hard-code the numbers
such as [Query1].[ActualValue] > 50 and etc I am able to see the colors. But my conditions are against dynamic values and therefore need to be compared with other columns.

Any help will be greatly appreciated.

Thank you in advance.

[MFGF]

Hi,

Sounds like your expression is never validating to True.  What if you simplify it to a single test with two outcomes - can you get a colour result at all?  If so, gradually build up your expression to see where the problem starts.

MF.

[CognosAdmn]

You are correct MFGF. The expression is never set to True for any of my conditions. It defaults always to the "(Other)" condition.

It doesnt seem to like it if i am comparing the ActualValue field against Value1, Midpoint or Value2 fields,
where btw all 4 fields are measures in a Crosstab.

I simplified the expression to the following:

CASE
WHEN ([Query1].[ActualValue] > [Query1].[Midpoint])
THEN ('Red')

WHEN ([Query1].[ActualValue] < [Query1].[Midpoint])
THEN ('Green')
END

Result: I can not see any Red or Green cells.

Thank you for helping me troubleshoot this problem MFGF.

[CognosAdmn]

Sorry, forgot to mention this: I can see the colors if i am comparing the ActualValue field against actual numbers such as:

CASE
WHEN ([Query1].[ActualValue] > 50)
THEN ('Red')

WHEN ([Query1].[ActualValue] < 50)
THEN ('Green')
END

[MFGF]

Is the Midpoint item actually displayed in the report or is it just in the query?  If it's the latter, you could try enabling it in the 'Properties' property.

MF.

[CognosAdmn]

Good Question.  :)

Midpoint was initially a Query Calculation only in the query, then i've added it in the database and and fm and currently its displayed in the report.

[MFGF]

Hi,

What results do you get if you try the following:

Add a Query Calculation to the report - call it Expression Test.  Code the syntax as:

CASE
WHEN ([ActualValue] > [Midpoint])
THEN ('Red')
WHEN ([ActualValue] < [Midpoint])
THEN ('Green')
ELSE ('Equal')
END

Does this display appropriate Red/Green values, or does it always return the 'Equal' value?


MF.

[CognosAdmn]

Hello MFGF,

It always displays EQUAL.

I really appreciate your support. :)

[blom0344]

We had similar issues when comparing numerical values (in 8.1) and finally settled on using a numerical outcome that was then used in a string variable to set the proper conditional formatting:

like:

CASE
WHEN ([ActualValue] > [Midpoint])
THEN (0)
WHEN ([ActualValue] < [Midpoint])
THEN (1)
ELSE (2)
END

I doubt this has much to do with your issue, but perhaps you could give it a try..

[CognosAdmn]

Hi Blom0344,

I tried your approach and the outcome seems to be always 2. It does not pick up 0 and 1.

I did create a another query calc in my report that returned a integer value for each condition.
Then I used this field in my Conditional Formatting expression and checked if it equalled the integer. But, nope, that didnt work either.

I have opened a ticket with ibm Cognos. I'll post back if they find a solution.

Thank you so much for time and effort.

[blom0344]

I very much doubt this is a bug in Cognos. The probable reason for always defaulting to the ELSE part is that Cognos cannot evaluate the equation. Logically , the SQL underneath will need (= only able to) to evaluate values within the same fetched record. I am wondering whether this is true in your case..

[MFGF]

Hello MFGF,

It always displays EQUAL.

I really appreciate your support. :)

That is very odd!

Perhaps you could try the following.

Drag a list object from the toolbox onto your report page somewhere, and in the properties of the list, set the Query property to point to your existing report query.

Now drag both Actualvalue and Midpoint items into the list.  What results do you get?  Do the results appear to be equal?  Are Actualvalue and Midpoint both of the same data type?

MF.

[CognosAdmn]

Hello MFGF,

I tried your approach. ActualValue and Midpoint and 90% of the time not the same. and if I added another Query calculation with (if Actual > Midpoint then 'Yes' else 'No'), i am getting the correct 'yes's and 'no's.
They are both of the same data type.

I temporarily solved it by compating ActualValue against the actual Midpoint numbers. Also opened a ticket and havent heard from the rep back. I'll post back if I find a solution.

Thanks again for your time and effort. :)

[MFGF]

Hello MFGF,

I tried your approach. ActualValue and Midpoint and 90% of the time not the same. and if I added another Query calculation with (if Actual > Midpoint then 'Yes' else 'No'), i am getting the correct 'yes's and 'no's.
They are both of the same data type.

I temporarily solved it by compating ActualValue against the actual Midpoint numbers. Also opened a ticket and havent heard from the rep back. I'll post back if I find a solution.

Thanks again for your time and effort. :)

Hmmm - most intriguing.

If you get chance, could you try changing the previous calculation from

CASE
WHEN ([ActualValue] > [Midpoint])
THEN ('Red')
WHEN ([ActualValue] < [Midpoint])
THEN ('Green')
ELSE ('Equal')
END


to

if ([ActualValue] > [Midpoint]) then ('Red') else if ([ActualValue] < [Midpoint]) then ('Green') else ('Equal')

Does this return expected results?

MF.

[CognosAdmn]

Thank you MFGF,

I will try to code per your suggestion and report back.

Thank you again.

[CognosAdmn]

hello MFGF,

I've tried coding the condition using if-then-else. Still no luck.

Its been three weeks since i opened a ticket with IBM Cognos, but I havent heard from them till now. 

[MFGF]

Hmmm - most intriguing.

If you get chance, could you try changing the previous calculation from

CASE
WHEN ([ActualValue] > [Midpoint])
THEN ('Red')
WHEN ([ActualValue] < [Midpoint])
THEN ('Green')
ELSE ('Equal')
END


to

if ([ActualValue] > [Midpoint]) then ('Red') else if ([ActualValue] < [Midpoint]) then ('Green') else ('Equal')

Does this return expected results?

MF.

Did you have any success with the above?