[Closed] Problems applying Conditional Formatting to zero vals in a CRN crosstab

[JGirl]

I'm trying to apply conditional formatting to a crosstab in reportnet.

My crosstab is employees by days, with the daily load rate (ie 0 - 1 formatted as a percentage) as the measure.Ã,  I have a conditional variable (string) called allocation, defined as follows:
case
when load > 1 then 'Overallocated'
when load > 0 and load < 1 then 'Partially allocated'
when load > 0 then 'Unallocated'
when load is null then 'Unallocated'
when load is missing then 'Unallocated'
else 'Unallocated'

I have applied the variable as a conditional style to the crosstab measure cell, and applied conditional cell background colour formatting for each of the allocation variable outcomes (ie. Dark red for Overallocated, yellow for partially allocated, red for unallocated).

The problem is that all my 0 values are not being formatted with a red background - they are just appearing with the white default but all the other formatting is being applied.Ã,  Ie...this is what is happening:

If the employee record for the day has a loading of > 1, then formatting is correct (ie dark red)
If the employee record for the day has a loading of 1, then formatting is correct (ie green)
If the employee record for the day has a loading of >0 and <1, then formatting is correct (ie yellow)
If the employee has no record for the day, then formatting is correct (ie red)
If the employee record for the day has a loading of 0, then formatting is not being applied (and the cell stays white).


In an attempt to try and fool reportnet around this problem, I also formatted the default 'Other' value for the variable to display red, and it still isnt colouring the cell.

Anyone have ideas on what the hell is going on here?

[cognosfreelancer]

Hello JGirl

I have not tested this solution but what struck me was that you were not testing for the value zero but only for values greater than or less than zero.

Change this statement:

when load > 0 then 'Unallocated'

to :
when load >= 0 then 'Unallocated'

HTH
NKT

[JGirl]

Thanks for such a speedy reply, but.....

My apologies - I just realised that I very stupidly placed the wrong logic in my posting, so let me clarify: (this happens when you change the logic a million times in order to try and get this working and then and type it into a forum - I should have just used good old cut and pasteÃ,  :)Ã,  )

The actual case statement is

case
when load > 1 then 'Overallocated'
when load = 1 then 'Fully Allocated'
when load > 0 and load < 1 then 'Partially allocated'
when load = 0 then 'Unallocated'
when load is null then 'Unallocated'
when load is missing then 'Unallocated'
else 'Unallocated'

There are 4 possible outcomes, and 4 different colours that i'm attempting to reply, but the report that I'm running has 5 colours (the 4 I am applying, and the standard white for when the logic doesnt apply).Ã,  The thing that has me confused is that I'm setting the format color to red when the value is 0 (and it is 0 in the database not just in my report), I've set all the other values through the 'ELSE' clause to red, and I've set the reportnet defualt 'Other' variable to be red, so I can't see how the hell the zeros are slipping through unformatted.

I want the zeros to appear as red, the same as the nulls, missings and everything that doesnt apply to the logic, but for some reason it isnt processing the load=0 properly.

Any ideas on why this is slipping through the loop?

J

[Merri]

What's the datatype on the item you're testing? You say it's formatted as a percentage, but is it a float or an integer?
Sometimes you can have issues when testing for equality with 0 for a float. If so, try testing for equality with 0.0 instead.

Let us know how it goes...

[CoginAustin]

Or try

when SUM(field)=0

[JGirl]

I've tried testing as = 0, =0.0, =0.00 etc with the same result (no formatting applied).

The database is MSSQL server and the datatype is float.

The thing that has me baffled is that I've added logic in the case statement to handle everything else not already handled in the when clauses (for when the =0 doesnt work!), and that I've also applied the formatting to the reportnet 'Other' value for the variable, and these zero values are totally slipping through the net!

[JGirl]

Also - in addition to my other (hundred) postings for this topic:....

If I copy the variable logic and paste it into a calculated column in the query, then view the tabular data, it is seeing that the zero values (appearing in the tabular data as 0.00) are actually a 'NA' value - its only the formatting that doesnt seem to pick up on the correct value.

J

[Darek]

Could you try this logic instead

case
when load > 1 then 'Overallocated'
when load = 1 then 'Fully Allocated'
when load > 0 and load < 1 then 'Partially allocated'
when load < 0 then 'Other'
else 'Unallocated'

I am not sure if you need the less-than-logic so feel free to remove it.

[JGirl]

Darek, Thanks for your reply...

I tried a similar approach (ie. removing the logic to check for = 0 so the value will fit into the 'else' condition) but still no luck....

This is what I can't get my head around....every value (null, missing, less than zero etc) is being handled correctly, except the EXACT zeros, regardless of whether I explicitly define code for equality to zero, or if i try to force it into the else condition.

I've checked the logic in the tabular data for the query, and the logic in the case statement is OK (ie. the query knows that the zeros should fall into the 'Unallocated' bucket), but somehow reportnet can't apply the formatting rule i created for 'Unallocated' (but only for the zeros)

[Darek]

This rings as a bug ... Maybe something like 0 ain't 0.0 ? I'm puzzled. Could you try casting the source value * 10 to int and see what you get? Base your conditions also on *10 ranges, please.

[mallika]

by any chance when you formatted the cell/column to percentage, did you set the 'no. of decimal places' to zero. If yes, then there may be a possibility that the data may  be a fraction 0.002 etc which is still not zero and therefore does not fall into  the '=0' category. The report output will still show 0 because fractions will not be displayed.

Just thinking out aloud..

[JGirl]

The value is definitely 0.....both in the DB and in the report....definitely something buggy going on

[Darek]

I've just read the first post in this thread. It is a CROSSTAB. Conditional formatting of crosstabs is not supported up to CRN 1.1MR2. Only lists and alike.

[bdybldr]

J, Just a suggestion...Have you tried placing WHEN load <=0 THEN "Unallocated" in the first position of the case statement?

Also, have you tested the values of your case statement?  What value is displayed for load <=0?

Please keep us posted.  I'm intrigued by your dillemna.

[JGirl]

I was intrigued for a little while, but I'm long over it now...

I have tried when load  <= 0 = 'Unallocated'
I have also tried when load <= 0.00 (with and without percent formatting)

What I have now is:

CASE
WHEN [load_day] > 1 THEN 'Overallocated'
WHEN [load_day] >= 0.875 and [load_day] <= 1 THEN 'Fully allocated'
WHEN [load_day] > 0.00 and [load_day] < 0.875 THEN 'Partially allocated'
WHEN [load_day] <= 0.00 THEN 'Unallocated'
WHEN [load_day] is missing THEN 'Unallocated'
WHEN [load_day] is null THEN 'Unallocated'
ELSE 'NA'
END

and it still doesnt work.  Turns out zero doesnt equal zero. 

Also turns out since it is a crosstab it isnt supported (thanks for that bit of infor darek) :(

Thanks everyone for your contributions

[Blue]

May I suggest you make the standard formatting of the cell as the way you want it for "unallocated" and then use the conditional formatting to override the standard/default.  This way you assume the value will be zero or null and the conditiional style will only apply if it is something else.

Bluey