SSRS IIF Statement With Nested IIFs

Question

I am trying to convert numeric months to text in SSRS Report Builder. I am having trouble with the expression needed to make this work. Here is the expression I have:

=IIF(Fields!MonthYear.Value = 6, "June" , 
IIF(Fields!MonthYear.Value = 7, "July" ,
IIF(Fields!MonthYear.Value = 8, "August" , 
IIF(Fields!MonthYear.Value = 9, "September" ,
IIF(Fields!MonthYear.Value = 10, "October")))))

This code throws the error: "The Value expression for the textrun ‘MonthYear.Paragraphs[0].TextRuns[0]’ contains an error: [BC30516] Overload resolution failed because no accessible 'IIf' accepts this number of arguments."

If there is a much simpler way to do this, I am all ears!

Answer 1

SWITCH() is normally much easier than nested IIFs, it much easier to read. So you can easily convert to this..

=SWITCH(
    Fields!MonthYear.Value = 6, "June" , 
    Fields!MonthYear.Value = 7, "July" ,
    Fields!MonthYear.Value = 8, "August" , 
    Fields!MonthYear.Value = 9, "September" ,
    Fields!MonthYear.Value = 10, "October",
    True, "unknown"
    )

SWITCH uses pairs of expression/return value. The final True just acts like an else if the previous entries all returned false (e.g. MonthYear = 11)

As a simple example, I created a dataset with the folloing query

DECLARE @t TABLE (MonthYear INT) 
INSERT INTO @t VALUES 
(0),(1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)
SELECT * FROM @t

Then I added a table, set the first column to the the MonthYear field and the second column used the expression above.

This is the result which is as expected.