SSRS expression giving error with iif condition

2019-03-04 17:32发布

问题:

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)

回答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