IIF in SSRS Report not working

Question

In my SSRS Report the report can run with 2 Null parameters, or the users can enter start amd end dates in the format YYYYMMDD.

If the report runs with Null parameters, I have a textbox that should just show the Month and Year if the dataset, if the users put the date parameters in, then the text box should show the start and end dates formatted as DD MMMM YYYY as below

From 02 October 2014 to 03 November 2014

But despite trying for several hours, I have no success getting the IIF function in the expression for the textbox to work.

Here is the basic code:

= IIF(IsNothing(Parameters!StartDate.Value), 
" for " & MonthName(Month(DateAdd("m",-1,Today))) & " " & Year(DateAdd("m",-1,Today)), 
"From " & 
    RIGHT(Parameters!StartDate.Value,2) + " " + 
    MonthName(CInt(Mid(Parameters!StartDate.Value,5,2))) & " " &
    LEFT(Parameters!StartDate.Value,4) & 
" to " &
    Right(Parameters!EndDate.Value, 2) + " " &
    MonthName(CInt(Mid(Parameters!EndDate.Value,5,2))) & " " &
    Left(Parameters!EndDate.Value, 4)
)

the thing is the below works on it's own:

"From " & Right(Parameters!StartDate.Value, 2) + "-" + 
    MonthName(CInt(Mid(Parameters!StartDate.Value,5,2))) & "-" &
    Left(Parameters!StartDate.Value, 4)
&
" to " &
    Right(Parameters!EndDate.Value, 2) + "-" &
    MonthName(CInt(Mid(Parameters!EndDate.Value,5,2))) & "-" &
    Left(Parameters!EndDate.Value, 4)

and this works on it's own (for the case when the parameters are missing or Null):

=" for " & MonthName(Month(DateAdd("m",-1,Today))) & " " & Year(DateAdd("m",-1,Today))

So I know there is no problem with the logic and code for the two IIF options.

When the report is run with Null parameters it should show for October 2014 and when it's run with parameters (such as 20141002 and 20141103) it should show from 02 October 2014 to 03 November 2014

Update: I seem to remember from VB6 that the IIF function evaluates all expressions before returning the evaluated result. What this means is that if the Parameters!EndDate.Value is Null then an error is thrown by the false part even if the True part will be used...

How should I write that IIF function to work properly?

How could it be written using Switch

Dbloch Dbloch · Accepted Answer · 2015-01-06T20:27:11

Yes IIF does eval both sides before it is rendered and since a parameter can be null the eval fails. I think it is stupid, but....

Here is a workaround from writing about 100 lines of code to get the IIF to work.

SSRS allows custom VB (only VB, not C#) code.

In the Report Properties, in the Custom Code block enter this

Public Shared Function DateString(startDate As String, endDate As String) As String
    Dim s As New DateTime()
    Dim e As New DateTime()
    Dim now As DateTime = DateTime.Now

    If String.IsNullOrEmpty(startDate) OrElse String.IsNullOrEmpty(endDate) Then
        Return " for " & now.ToString("MMMM yyyy")
    End If

    s = DateTime.ParseExact(startDate, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture)

    e = DateTime.ParseExact(endDate, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture)

    Return String.Format("From {0} to {1}", s.ToString("MMMM yyyy"), e.ToString("MMMM yyyy"))
End Function

In the Field expression enter this

= Code.DateString(Parameters!StartDate.Value, Parameters!EndDate.Value)

IIF in SSRS Report not working

2 Answers