I encountered a problem today which caused havoc with some of my SSRS reports that carry out some date filtering operations when they load or execute on user selection of date filters. Here is a breakdown of the issue:-
The Report Type Description
The reports will read data from a SharePoint list in which some columns are date/time types. Normally there is no problem with SSRS recognising the following type of expression in a filter for a data set:-
Fields!MyStartDate.Value <= Fields!MyEndDate.Value
For some odd reason the SSRS report always reads the date type as “Text” when you re-open data set dialog and select the Filter option. This has never been a problem.
The type of error I was getting referred to the data set and was of the kind where it stated:
“…cannot compare System.DateTime with System.String”
The error message would come up as soon as it had read in the filter parameters and tried to execute the report by using the filter expressions.
Investigation of the Issue
- The report would run fine within Visual Studio SSRS environment.
- There was a patch installed on the report server the night before but that same patch was installed on the development server where the report would function without problems using the production data source SharePoint list.
- If found a workaround by changing the filter expression “Fields!MyStartDate.Value <= Fields!MyEndDate.Value” to
“CDate(Fields!MyStartDate.Value <= Fields!MyEndDate.Value)
- The above change made the report work when we uploaded to the SSRS production server.
- But we also found that if we deployed the report to a different SSRS server (dev/test) environment and it was referring to the same data source – the report would work without any problems. Therefore the workaround did not get to the root cause.
One of my infrastructure colleagues felt that if it was not the patch that caused the issue then maybe a simple server reboot was needed or the restarting of the SSRS Service in Windows Services. After doing this – the problem went away and the filtering would work in reports as they had done for years/months before. I do not understand the root cause of this -but the main symptoms were that SSRS server was reading SharePoint date columns as Text fields using the SharePoint list connector.
I lost a whole day on this issue – so I hope it saves somebody out there some time. Note the SharePoint was version 2010, and SSRS was based on SQL 2008 R2, running on Windows Server 2008.