SSRS expression giving error with iif condition

Question

I have this expression for a text box in a SSRS report

=IIF((Fields!Spot.Value = True), "SPOT", 
     MonthName(Fields!Codes_MonthFromIDfk.Value,true).ToUpper().ToString() & "-"
         & MonthName(Fields!Codes_MonthToIDfk.Value,true).ToUpper().ToString())

The column "Spot" in the database is a column of datatype bit. It has either a 0 or 1. When I have 1/True it should print SPOT or the months like JAN-FEB. I am getting this error when the value of Spot column is 1.

"The Value expression for the textrun ‘Textbox32.Paragraphs[0].TextRuns[0]’ contains an error: Argument 'Month' is not a valid value."

When I remove the false part and type in "ABCD", I get correct output for the textbox. Either SPOT(for true) or ABCD(for false). Currently when it is NOT 1 then it shows JAN-FEB(which is the desired output). If 1 it shows up as #Error. What is wrong with that expression? If you need more info, please ask. Thanks.

EDIT:

Public Function FormatMonths(ByVal spot As Boolean, ByVal from As Integer, ByVal to As        Integer) As String
    If spot Then
    Return "SPOT"
Else
    Return  MonthName(from,true).ToUpper().ToString() & "-" & MonthName(to,true).ToUpper().ToString()
End If
End Function

Expression :

=Code.FormatMonths(Fields!Spot.Value, 1,2)

rla4 · Accepted Answer · 2014-02-11T21:09:35.913

1

The IIF operator will always evaluate both expressions before deciding which one to use. One possible solution for your problem is to create a custom function that does what you need, and use it in your textbox:

=Code.FormatMonths(Fields!Spot.Value, Fields!Codes_MonthFromIDfk.Value, Fields!Codes_MonthToIDfk.Value)

For creating your custom function, go to Reports > Report Properties > Code and enter the definition below:

Public Function FormatMonths(ByVal spot As Boolean, ByVal fromMonth As Integer, ByVal toMonth As Integer) As String
    If spot Then
        Return "SPOT"
    Else
        Return  MonthName(fromMonth,true).ToUpper().ToString() & "-" & MonthName(toMonth,true).ToUpper().ToString()
    End If
End Function

edited Feb 11 '14 at 21:09

answered Feb 11 '14 at 20:11

rla4

1,228
13
25

Thanks. I am getting the errors below:[BC30183] Keyword is not valid as an identifier. [rsCompilerErrorInExpression] The Value expression for the textrun ‘Textbox32.Paragraphs[0].TextRuns[0]’ contains an error: [BC30057] Too many arguments to 'Public Function FormatMonths(spot As Boolean, from As Integer) As String'. – RookieAppler Feb 11 '14 at 20:24
The FormatMonths function you just wrote has only 2 parameters, is that correct? Shouldn't you pass all of your 3 fields to this function? Verify whether you are passing 3 arguments (as in my example), and let me know if that still doesn't work – rla4 Feb 11 '14 at 20:30
No, I took the same code you gave here and pasted it to my code. Changed the datatypes. Please check the EDITED question I posted. I was testing by giving values 1 and 2 (jan and feb) for the time being – RookieAppler Feb 11 '14 at 20:59
It's an issue with the name of the parameters. You can't use reserved words (to, in our case) as variable names. I edited my answer, see if it helps. – rla4 Feb 11 '14 at 21:09

SSRS expression giving error with iif condition

1 Answers1

Linked