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....
Quote from: Tess on 14 Aug 2015 10:54:13 AM
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.
Of course - the WHEN! Thanks!