I have a cross tab report:
comparison| Target | May09|June09|July09|...|May10
service | < or > | value | | | |...|
What this does is print the actual stat of each service (the cross tab corner is actual) Here is an example of 2 types of rows:
phone answering | < | 60 |79|45|55|...|43
answering percent| > | 99 |99|76|89|...|100
I want cells that meet goal to turn green and those that don't to turn red. Because the comparisons can be different I can't use a simple conditional styling.
TRIAL 1
When I tried to do an "advanced styling" I used the following algorithm:
SET TO GREEN:
(([Query1].[Comparison] = '<') and ([Query1].[Actual] > [Query1].[Metric Target])) or (([Query1].[Comparison] = '>') and ([Query1].[Actual] < [Query1].[Metric Target]))
SET TO RED:
(([Query1].[Comparison] = '<') and ([Query1].[Actual] <= [Query1].[Metric Target]))or (([Query1].[Comparison] = '>') and ([Query1].[Actual] >= [Query1].[Metric Target]))
this has no effect on the crosstab table whatsoever.
TRIAL 2
So to check my logic I made a new data item RG:
if (([Query1].[Comparison] = '<') and ([Query1].[Actual] > [Query1].[Metric Target])) or (([Query1].[Comparison] = '>') and ([Query1].[Actual] < [Query1].[Metric Target])) then (1)
else (0)
and put this as the corner value, and it printed out a 1 where we met target and 0 where it did not.
TRIAL 3
After this I made a boolean variable RG:
(([Query1].[Comparison] = '<') and ([Query1].[Actual] > [Query1].[Metric Target])) or (([Query1].[Comparison] = '>') and ([Query1].[Actual] < [Query1].[Metric Target]))
and added it to style variable and set it to turn green when yes and red when no and turns them all red as though its not treating cells individually but doing a rows possibly as an average or sum?
TRIAL 4
I took the RG item i made in trial for and added it to the properties of the month column then made an advanced conditional format so that when rg=1 green and when rg=0 red
this had no effect on the cross tab at all
I don't know what else to do as seen below I would like to format based on the RG while having the data of the actual crosstab.
Actual | comparison| Target | May09|June09|July09|...|May10
phone answering | < | 60 |79 |45 |55 |...|43
answering percent| > | 99 |99 |76 |89 |...|100
RG | comparison| Target | May09|June09|July09|...|May10
phone answering | < | 60 |0 |1 |1 |...| 1
answering percent| > | 99 |1 |0 |0 |...| 1
I hope I was elaborate enough and thank you for any help. I can email screen shots if anyone wants to see the reports
Cheers!
comparison| Target | May09|June09|July09|...|May10
service | < or > | value | | | |...|
What this does is print the actual stat of each service (the cross tab corner is actual) Here is an example of 2 types of rows:
phone answering | < | 60 |79|45|55|...|43
answering percent| > | 99 |99|76|89|...|100
I want cells that meet goal to turn green and those that don't to turn red. Because the comparisons can be different I can't use a simple conditional styling.
TRIAL 1
When I tried to do an "advanced styling" I used the following algorithm:
SET TO GREEN:
(([Query1].[Comparison] = '<') and ([Query1].[Actual] > [Query1].[Metric Target])) or (([Query1].[Comparison] = '>') and ([Query1].[Actual] < [Query1].[Metric Target]))
SET TO RED:
(([Query1].[Comparison] = '<') and ([Query1].[Actual] <= [Query1].[Metric Target]))or (([Query1].[Comparison] = '>') and ([Query1].[Actual] >= [Query1].[Metric Target]))
this has no effect on the crosstab table whatsoever.
TRIAL 2
So to check my logic I made a new data item RG:
if (([Query1].[Comparison] = '<') and ([Query1].[Actual] > [Query1].[Metric Target])) or (([Query1].[Comparison] = '>') and ([Query1].[Actual] < [Query1].[Metric Target])) then (1)
else (0)
and put this as the corner value, and it printed out a 1 where we met target and 0 where it did not.
TRIAL 3
After this I made a boolean variable RG:
(([Query1].[Comparison] = '<') and ([Query1].[Actual] > [Query1].[Metric Target])) or (([Query1].[Comparison] = '>') and ([Query1].[Actual] < [Query1].[Metric Target]))
and added it to style variable and set it to turn green when yes and red when no and turns them all red as though its not treating cells individually but doing a rows possibly as an average or sum?
TRIAL 4
I took the RG item i made in trial for and added it to the properties of the month column then made an advanced conditional format so that when rg=1 green and when rg=0 red
this had no effect on the cross tab at all
I don't know what else to do as seen below I would like to format based on the RG while having the data of the actual crosstab.
Actual | comparison| Target | May09|June09|July09|...|May10
phone answering | < | 60 |79 |45 |55 |...|43
answering percent| > | 99 |99 |76 |89 |...|100
RG | comparison| Target | May09|June09|July09|...|May10
phone answering | < | 60 |0 |1 |1 |...| 1
answering percent| > | 99 |1 |0 |0 |...| 1
I hope I was elaborate enough and thank you for any help. I can email screen shots if anyone wants to see the reports
Cheers!