Format Date Time Exception in SSRS

0
votes

I have built a SSRS report which combines two int columns of data (i.e.: 6 & 2017) to form a valid month and year result in a column of the SSRS report (i.e.: June 2017) using the following statement: Format(CDate(Fields!MonthEarnedId.Value & "/" & Fields!YearEarned.Value)

That works as desired, however some of the int values for the month have a value of 13 which is supposed to represent all months of the year so that the required format for these values in the report is such as "All 2017". I thought an IIF statement such as the following would specify the necessary formating for the exclusion, however the rows with a month value of 13 simply display #error. Any suggestions would be greatly appreciated!

=iif(Fields!MonthEarnedId.Value < 13, Format(CDate(Fields!MonthEarnedId.Value & "/" & Fields!YearEarned.Value), "MMM yyyy"), "All " & Fields!YearEarned.Value)
2
sql-serverdatetimereporting-servicesssrs-2012

2 Answers

1
votes

The issue in expression is CDate() when the 13 occurs. As per the design in SSRS, IIF evaluates TRUE and FALSE both and in your case when the 13 is selected as MonthEarnedId then CDate() tries to convert 13/2017 in MMM yyyy format which is invalid and throws #Error.

I would suggest you to convert the int into month names in your SQL query and bring the data to just concatenate in SSRS but if you strictly have to do it in SSRS then below is the expression you have to put in.

=SWITCH(Fields!MonthEarnedId.Value=1,"Jan"
,Fields!MonthEarnedId.Value=2,"Feb"
,Fields!MonthEarnedId.Value=3,"Mar"
,Fields!MonthEarnedId.Value=4,"Apr"
,Fields!MonthEarnedId.Value=5,"May"
,Fields!MonthEarnedId.Value=6,"Jun"
,Fields!MonthEarnedId.Value=7,"Jul"
,Fields!MonthEarnedId.Value=8,"Aug"
,Fields!MonthEarnedId.Value=9,"Sep"
,Fields!MonthEarnedId.Value=10,"Oct"
,Fields!MonthEarnedId.Value=11,"Nov"
,Fields!MonthEarnedId.Value=12,"Dec"
,Fields!MonthEarnedId.Value=13,"All"
) & " " & CStr(Fields!YearEarned.Value.Value)
0
votes

You are casting that field type to a date variant to apply formatting in doing so, you are also changing the data type of the column from string to datetime. The error occurs when the renderer attempts to cast the string value "All 2017" into a corresponding date time. To get around this you need to cast it back to a string subtype using CStr() or some other way. 

=iif(Fields!MonthEarnedId.Value < 13, CStr(Format(CDate(Fields!MonthEarnedId.Value & "/" & Fields!YearEarned.Value), "MMM yyyy")), "All " & Fields!YearEarned.Value)