reporting-servicesexpressionssrs-expression

SSRS multiple IF expressions (SSRS Version Of a SQL CASE)

I'm really struggling to write an SSRS Expression, due to various permissions issues on DB's I can't do it in SQL which would take a few seconds... I need a text box expression to show either 20000, 30000, 40000, 60000 depending on the value in OtherModelsInfo in the Agreement dataset. I've attempted to write the expression but I am no expression writer so I apologise. I believe from what a co-worker told me the First needs to stay in there to make sure the right OtherModelsInfo value is shown. Any questions please let me know, thanks :)

=iif(First(Fields!OtherModelsInfo.Value,"Agreement") = "V12 All V12 Models 4 Year 60,000 Miles Extended Warranty", then 60000, (First(Fields!OtherModelsInfo.Value,"Agreement") = "V12 All V12 Models 3 Year 30,000 Miles ", then 30000, (First(Fields!OtherModelsInfo.Value,"Agreement") = "V12 All V12 Models 4 Year 40,000 Miles ", then 40000, (First(Fields!OtherModelsInfo.Value,"Agreement") = "V12 All V12 Models 2 Year 20,000 Miles ", then 20000,

Solution

  • You don't need to use "then". IIF() syntax is simply

    =IIF(Expression to evaluate, True result, False Result)

    for example

    =IIF(Fields!Score.Value > 75, "Pass", "Fail")

    If you want to branch more than one test then you can nest IIF()s like this.

    =IIF(Fields!Score.Value > 75, "Pass", IIF(Fields!Score.Value < 20, "BadFail",  "Fail"))

    However, in your case a switch statement might be easier to read and debug rather than nesting lots of IIFs

    =SWITCH(
   First(Fields!OtherModelsInfo.Value,"Agreement") = "V12 All V12 Models 4 Year 60,000 Miles Extended Warranty", 60000, 
   First(Fields!OtherModelsInfo.Value,"Agreement") = "V12 All V12 Models 3 Year 30,000 Miles", 30000,
   First(Fields!OtherModelsInfo.Value,"Agreement") = "V12 All V12 Models 4 Year 40,000 Miles", 40000,
   First(Fields!OtherModelsInfo.Value,"Agreement") = "V12 All V12 Models 2 Year 20,000 Miles", 20000,
   True, 0)

    SWITCH uses pairs of expressions, the first the expression to test if it evaluates to true, the second is the result that should be returned. It stops at the first expression that evaluates to True. The final True, 0 pair just acts like an else as "True" always evaluates to True. So in this case if none of the above match, you will get 0 returned.

    With regards to the FIRST() function. You will only need this if the textbox containing the expression is within a group that aggregates rows of data with the same OtherModelsInfo field. If the textbox is within the details group then you don't need this.