Change SSRS expression used based on parameter value

Question

I have an SSRS expression that i need to change the values in based on a passed parameter.

="Double Coat " & 
IIF((First(Fields!rr_paintpayout.Value, "Market") = "Task-Based") OR (First(Fields!rr_paintpayout.Value, "Market") = "Hourly"),
    IIF(Fields!rr_taskpayoutaddonoption.Value = "Floorplan Configuration",
        Fields!rr_1x1.Value & " - "& Fields!rr_1x4.Value**,
        IIF(Fields!rr_taskpayoutaddonoption.Value = "Fixed Quantity",
            Fields!rr_fixedamount.Value & " each",
            IIF(Fields!rr_taskpayoutaddonoption.Value = "Fixed Hourly",
                Fields!rr_fixedamount.Value & " hourly",
                Fields!rr_fixedamount.Value))),
    IIF(First(Fields!rr_paintpayout.Value, "Market") = "Variable Percent",
        IIF(Fields!rr_variablepercentaddonselection.Value = "Sq Ft Variable Pricing",
            Fields!rr_fixedamount.Value & " sq. ft.",
            IIF(Fields!rr_variablepercentaddonselection.Value = "Fixed Quantity",
                Fields!rr_fixedamount.Value & " each",
                IIF(Fields!rr_variablepercentaddonselection.Value = "Fixed Hourly",
                    Fields!rr_fixedamount.Value & " hourly",
                    Fields!rr_fixedamount.Value))), 0.00))

My parameter is an int and based on either 1,2,3 or 4. I need to change the third line of the expression. I have tried to wrap the entire expression in an IIF and I was not able to run the report. What im thinking is

  ="Double Coat " & 
    IIF((First(Fields!rr_paintpayout.Value, "Market") = "Task-Based") OR (First(Fields!rr_paintpayout.Value, "Market") = "Hourly"),
        IIF(Fields!rr_taskpayoutaddonoption.Value = "Floorplan Configuration",
      IIF((Parameters.Bedrooms.Value =1 )
            Fields!rr_1x1.Value & " - "& Fields!rr_1x4.Value)),
 IIF((Parameters.Bedrooms.Value =2 )
            Fields!rr_2x1.Value & " - "& Fields!rr_3x4.Value)),
            IIF(Fields!rr_taskpayoutaddonoption.Value = "Fixed Quantity",
                Fields!rr_fixedamount.Value & " each",
                IIF(Fields!rr_taskpayoutaddonoption.Value = "Fixed Hourly",
                    Fields!rr_fixedamount.Value & " hourly",
                    Fields!rr_fixedamount.Value))),
        IIF(First(Fields!rr_paintpayout.Value, "Market") = "Variable Percent",
            IIF(Fields!rr_variablepercentaddonselection.Value = "Sq Ft Variable Pricing",
                Fields!rr_fixedamount.Value & " sq. ft.",
                IIF(Fields!rr_variablepercentaddonselection.Value = "Fixed Quantity",
                    Fields!rr_fixedamount.Value & " each",
                    IIF(Fields!rr_variablepercentaddonselection.Value = "Fixed Hourly",
                        Fields!rr_fixedamount.Value & " hourly",
                        Fields!rr_fixedamount.Value))), 0.00))

which is basically adding the Parameters.Value right at the top. Has anyone had any success or a better way to change what expression is being used?

AnkUser AnkUser · Accepted Answer · 2019-08-13T06:29:02

When I took your Expression in notepad and checked bracktes, it did not matched. I reworked your expression and I could find 1 false expression been missing from your expression. You might want to check that. Below is the expression I created from your example.

="Double Coat " & IIF(First(Fields!rr_paintpayout.Value, "Market") = "Task-Based" OR First(Fields!rr_paintpayout.Value, "Market") = "Hourly",
IIF(Fields!rr_taskpayoutaddonoption.Value = "Floorplan Configuration",
IIF(Parameters.Bedrooms.Value =1,
Fields!rr_1x1.Value & " - "& Fields!rr_1x4.Value,
IIF(Parameters.Bedrooms.Value =2 ,
Fields!rr_2x1.Value & " - "& Fields!rr_3x4.Value,
IIF(Fields!rr_taskpayoutaddonoption.Value = "Fixed Quantity",
Fields!rr_fixedamount.Value & " each",
IIF(Fields!rr_taskpayoutaddonoption.Value = "Fixed Hourly",
Fields!rr_fixedamount.Value & " hourly",Fields!rr_fixedamount.Value)))),
IIF(First(Fields!rr_paintpayout.Value, "Market") = "Variable Percent",
IIF(Fields!rr_variablepercentaddonselection.Value = "Sq Ft Variable Pricing",
Fields!rr_fixedamount.Value & " sq. ft.",
IIF(Fields!rr_variablepercentaddonselection.Value = "Fixed Quantity",
Fields!rr_fixedamount.Value & " each",
IIF(Fields!rr_variablepercentaddonselection.Value = "Fixed Hourly",
Fields!rr_fixedamount.Value & " hourly",Fields!rr_fixedamount.Value))),
false)),
0.00)

Change SSRS expression used based on parameter value

1 Answers