SSRS Reports - Formatting Date Column using expression

1
votes

I am using SSAS cube to display data in my report. There is a date column in the cube which may be blank. If it is blank I need to display blank otherwise I need to format it as "MM-yyyy". I am using the below mentioned expression in the column.

=IIf(Trim(Fields!Chargeoff.Value) = "", "", 
     Format(CDate(Fields!Chargeoff.Value), "MM-yyyy"))

The rows which have date values are perfect with the correctly formatted date being displayed. However for blank rows, it displays error saying

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

I tried with IsNothing, Null and everything else that came to my mind but could not get to display blank.

Does anyone have any suggestions on how to do this?

Update

I actually formatted my date in SSAS instead of SSRS.. That did the trick for me..

2
reporting-servicesdate-formatting

2 Answers

2
votes
=IIF(CDATE(IIF(TRIM(Fields!RequiredStart.Value).ToString().Length = 0,
"1/1/0001",
Fields!RequiredStart.Value)).ToString() = CDATE("01/01/0001"),
"",
Format(CDATE(IIF(TRIM(Fields!RequiredStart.Value).ToString().Length = 0,
"1/1/0001",
Fields!RequiredStart.Value)), "dd-MMM-yyyy"))

:)

0
votes

try putting and Empty Date in for the output instead of an empty string. 00-0000 it is looking for a Date and you give it an empty String. I would imagine that you either have to give it an empty date or a Default Date