SSRS report - export to excel date time filteration

3
votes

Populating an SSRS report from dyanmic dataset i.e. number column and order of columns may vary based on condition, and this report on trying to export to excel. But in exporting date column filtration not working. Finds the reason is I bind the column in string format.

Sample data format to understand. Here the columns & order of columns may vary as per condition.

enter image description here

And I am converting this to below format and passing to SSRS as dataset.

enter image description here

And populating report by grouping with ROW_NO & COL

Then I tried to convert DOB column to date time.

For converting DOB to date format changed return type of DOB from sql query as datetime, converted column value to date time using CDate() in SSRS and formatted in own format (dd-mmm-yyyy).

Below expression used for converting to date time:

IIF((Fields!COL.Value="DOB") ,CDate(Fields!VALUE.Value),(Fields!VALUE.Value))

But showing #Error for columns data type other than date time:

enter image description here

1
sql-serverreporting-servicesssrs-2008rdl
You have an expression that can return two different datatypes. Each field (whether from the datasaource or from a calculation) has one datatype and your expression can return either a Date or anything but a date. – Frank Ball
If you run the report in Visual Studio, it will give you the details of the error message. This will help troubleshoot it. – StevenWhite

1 Answers

1
votes

Go to your detail textbox in the DOB column. And use the following expression:

=Format(CDate(Fields!DOBValue.Value), "dd-MM-yyyy")

This way the date in your DOB column will show 23-01-2018

Note: The expression just works if in the Fields!DOBValue.Value field are just dates. If there is a N/A it will throw an error. Then you have to clear the N/A first.

And if you want to export it to Excel you need to set the language in your report properties (use your region) and go to your textbox where you wrote in the above expression under Textbox properties > Number chose Date format.

UPDATE

Based on your updated question I think I found the error. The problem is your writing of the month names combined with the report language. My report language is set to DE. For me the following works:

=CDate("15-Mai-94") 'Result 05.05.1994

But this dosent work:

=CDate("15-May-94") 'Result =Error

The second one works, when I set the report language to EN but then the first one throws an error