String Variable in Crosstab throwing Failed to Validate String1

[teresa.danna@gmail.com]

I use a relational datasource using SQL Server

I have the following crosstab with hours worked:

                                 Week 1   Week 2   Week 2
Employee1 | Project1
                   Project2
             Total Emp
Employee2.....

I'd like to show the following conditional formatting for the total employee hours row:
<10.1  Yellow
<32.1 Pink
>48.1 Green

I created a string variable with the below, I've created values, YELLOW, PINK, GREEN and I've applied the values using the conditional explorer. IE: pink - then highlighted the value pink and made the subtotal pink.

CASE

total ( [Join_Final].[Hours] for [Join_Final].[Employee ID], [Join_Final].[First Day of Week]) < 10.1 then 'YELLOW'

total ( [Join_Final].[Hours] for [Join_Final].[Employee ID], [Join_Final].[First Day of Week]) < 32.1 then 'PINK'

total ( [Join_Final].[Hours] for [Join_Final].[Employee ID], [Join_Final].[First Day of Week]) > 48.1 then 'GREEN'

END


The error:
Failed to validate the variable String1 CRX-YXX-4010 A parsing error was found at or near the position 108 in the expression "Case....

[MFGF]

I use a relational datasource using SQL Server

I have the following crosstab with hours worked:

                                 Week 1   Week 2   Week 2
Employee1 | Project1
                   Project2
             Total Emp
Employee2.....

I'd like to show the following conditional formatting for the total employee hours row:
<10.1  Yellow
<32.1 Pink
>48.1 Green

I created a string variable with the below, I've created values, YELLOW, PINK, GREEN and I've applied the values using the conditional explorer. IE: pink - then highlighted the value pink and made the subtotal pink.

CASE

total ( [Join_Final].[Hours] for [Join_Final].[Employee ID], [Join_Final].[First Day of Week]) < 10.1 then 'YELLOW'

total ( [Join_Final].[Hours] for [Join_Final].[Employee ID], [Join_Final].[First Day of Week]) < 32.1 then 'PINK'

total ( [Join_Final].[Hours] for [Join_Final].[Employee ID], [Join_Final].[First Day of Week]) > 48.1 then 'GREEN'

END


The error:
Failed to validate the variable String1 CRX-YXX-4010 A parsing error was found at or near the position 108 in the expression "Case....

Hi,

There should already be a query item for the Total Employee values in your query? It will probably have a name of [Total(Employee)] or something similar.

If so, use this. The report won't like you trying to calculate a total as part of the variable expression.

Your expression should look something like:

case
when [Query1].[Total(Employee)] < 10.1 then 'Yellow'
when [Query1].[Total(Employee)] >= 10.1 and [Query1].[Total(Employee)] <32.1 then 'Pink'
when [Query1].[Total(Employee)] > 48.1 then 'Green'
else 'Other'
end

Cheers!

MF.

[teresa.danna@gmail.com]

Of course - the WHEN! Thanks!