Formatting Parameters in SSRS Report

Question

I have an SSRS report that takes 2 optional text parameters as below:

StartDate, Text, allow Null, format YYYYMMDD
EndDate, Text, allow Null. format YYYYMMDD

I am trying to format a header text box that will show the below:

If the parameters are Null, then I would get by default the Month Name and the Year (as the report will get data defaulted to the previous Month)

If the parameters are set then I want to format them as dd MMMM YYYY (so 20141103 will be formatted as 03 November 2014

Here is the code I am trying to use for the Expression in the text field:

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

so as you can see, if the parameter is Null (allowing for the report to default to run the last months data when scheduled) then the function should just show something like December 2014 but if they are entered as YYYMMDD then I need to format them.

I am getting the below error (in SSRS in Visual Studio 2013):

[rsRuntimeErrorInExpression] The Value expression 
for the textrun ‘Textbox15.Paragraphs[0].TextRuns[0]’ 
contains an error: Conversion from string "--" to type 'Date' is not valid.

I have also tried using CDate around

LEFT(Parameters!EndDate.Value,4) + "-" +
MID(Parameters!EndDate.Value,5,2) + "-" +
RIGHT(Parameters!EndDate.Value,2)

(these should come out as 2014-11-01 for example) but again, no luck

So where am I going wrong here and how can I format those dates if they are entered?

BI Dude BI Dude · Accepted Answer · 2015-01-06T13:38:22

I would use expression something like this:

=Format(CDate(Left(20150131, 4) + "-" + Mid(20150131, 5, 2) + "-" + Right(20150131, 2)), "dd MMMM yyyy")

I believe it is better to use integer or date parameters rather than text parameters. So you could convert date text parameter to date before it appears in the report by doing something like:

  select cast('20150131' as date)

Formatting Parameters in SSRS Report

1 Answers