Probs applying Conditional Formatting to 0 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?

 

[KingCrab]

Case statement:
1.1 or greater = Over
.1 - .9 = Partial
else 0.0 or less = Unallocated

You say you applied 3 colors for the condition. However, I see 4 maybe even 5 colors actual?

Your case statement is only passing 3 different values

If your logic is correct then 0 should be red just as all the other unallocated values are. Matter of fact everything that passes into that CASE statement should be red unless it is a value .01+

 

[JGirl]

KingCrab, thanks for your quick reply - I just realised that I very stupidly placed the wrong logic in my posting, so let me clarify:

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

 

[almeids]

JGirl,
I think I read in another forum that there is a known bug related to conditional formatting in a crosstab when the "first" (whatever that means) value is a zero or null...so you might try displaying one value but basing the conditional formatting on another, perhaps substituting -1 for zero or something like that.
Steve