CODEWITHSUNDEEP
sqlsql-serverreporting-servicesssrs-2008
user11703506
user11703506

Reputation:

What am I doing wrong with this SSRS switch expression?

I am following the guide here -> How to Add Excel-like "color scale" red-yellow-green conditional formatting in SSRS report based on min and max values and using the following code from the answer

 =SWITCH 
(Fields!.Value >= Min(Fields!myField.Value, "DataSetName") + (5 *((Max(Fields!myField.Value, "DataSetName")-Min(Fields!myField.Value, "DataSetName"))/6)) ,"#0ca102"
 Fields!.Value >= Min(Fields!myField.Value, "DataSetName") + (4 *((Max(Fields!myField.Value, "DataSetName")-Min(Fields!myField.Value, "DataSetName"))/6)) ,"#7fda24"
 Fields!.Value >= Min(Fields!myField.Value, "DataSetName") + (3 *((Max(Fields!myField.Value, "DataSetName")-Min(Fields!myField.Value, "DataSetName"))/6)),"#bdff30"
 Fields!.Value >= Min(Fields!myField.Value, "DataSetName") + (2 *((Max(Fields!myField.Value, "DataSetName")-Min(Fields!myField.Value, "DataSetName"))/6)),"Gold"
 Fields!.Value >= Min(Fields!myField.Value, "DataSetName") + ((Max(Fields!myField.Value, "DataSetName")-Min(Fields!myField.Value, "DataSetName"))/6),"Yellow"
 1=1,"Coral")

Then I replaced the names of the variable with my actual names, but I think I missed up somewhere along the way because my report doesn't run. Does anyone see what I did wrong?

This is my version

=SWITCH 
 (Min(Fields!NEValue.Value, "CustomersByState") >= Min(Fields!SDValue.Value, "CustomersByState") + (5 * ((Min(Fields!NYValue.Value, "CustomersByState")-Min(Fields!SDValue.Value, "CustomersByState"))/6)) ,"#0ca102"
 Min(Fields!NEValue.Value, "CustomersByState") >= Min(Fields!SDValue.Value, "CustomersByState") + (4  * ((Min(Fields!NYValue.Value, "CustomersByState")-Min(Fields!SDValue.Value, "CustomersByState"))/6)) ,"#7fda24"
 Min(Fields!NEValue.Value, "CustomersByState") >= Min(Fields!SDValue.Value, "CustomersByState") + (3  * ((Min(Fields!NYValue.Value, "CustomersByState")-Min(Fields!SDValue.Value, "CustomersByState"))/6)),"#bdff30"
 Min(Fields!NEValue.Value, "CustomersByState") >= Min(Fields!SDValue.Value, "CustomersByState") + (2  * ((Min(Fields!NYValue.Value, "CustomersByState")-Min(Fields!SDValue.Value, "CustomersByState"))/6)),"Gold"
 Min(Fields!NEValue.Value, "CustomersByState") >= Min(Fields!SDValue.Value, "CustomersByState") + ((Min(Fields!NYValue.Value, "CustomersByState")-Min(Fields!SDValue.Value, "CustomersByState"))/6),"Yellow"
 1=1,"Coral")

EDIT: This is not a duplicate question, the previous question was asking how to get the number to the polygon, this question is only about the case statement

Upvotes: 0

Views: 207

Answers (1)

Strawberryshrub
Strawberryshrub

Reputation: 3399

You missed the comma after your hex colors. It goes like 

=Switch(Condition1, TruePart1, Condition2, TruePart2, ...)

=SWITCH(
        Min(Fields!NEValue.Value, "CustomersByState") >= Min(Fields!SDValue.Value, "CustomersByState") + (5 * ((Min(Fields!NYValue.Value, "CustomersByState")-Min(Fields!SDValue.Value, "CustomersByState"))/6)) ,"#0ca102",
        Min(Fields!NEValue.Value, "CustomersByState") >= Min(Fields!SDValue.Value, "CustomersByState") + (4  * ((Min(Fields!NYValue.Value, "CustomersByState")-Min(Fields!SDValue.Value, "CustomersByState"))/6)) ,"#7fda24",
        Min(Fields!NEValue.Value, "CustomersByState") >= Min(Fields!SDValue.Value, "CustomersByState") + (3  * ((Min(Fields!NYValue.Value, "CustomersByState")-Min(Fields!SDValue.Value, "CustomersByState"))/6)),"#bdff30",
        Min(Fields!NEValue.Value, "CustomersByState") >= Min(Fields!SDValue.Value, "CustomersByState") + (2  * ((Min(Fields!NYValue.Value, "CustomersByState")-Min(Fields!SDValue.Value, "CustomersByState"))/6)),"Gold",
        Min(Fields!NEValue.Value, "CustomersByState") >= Min(Fields!SDValue.Value, "CustomersByState") + ((Min(Fields!NYValue.Value, "CustomersByState")-
        Min(Fields!SDValue.Value, "CustomersByState"))/6),"Yellow",
        1=1,"Coral"
        )

Upvotes: 1

Related Questions