Scenario: I have a report which works fine in SSRS. I need to add a parameter to allow the dataset to be filtered by a Time-range, eg. last 7, 14, 28 days or 'Calendar' (Time/Date Selector) so I create a Parameter with four available values (last 7 days, last 14 days, last 28 days and Calendar-which returns (Null). It's a drop-down value which I will refer to as a Switch.
I added logic to the underlying stored procedure to look for these values which then adds to the WHERE clause in the query. It looks for dates within the date-range (or all dates if the parameter is null). There is other logic too which looks ats the Calendar values if my new Switch is Null, but that's not important here.
This report now runs exactly as I need it to in VS2008, BUT, when I deploy the report to SSRS 2008 R2 the report returns no data at all regardless of which value in the Switch I select. Also, it errors with:
An error has occurred during report processing. (rsProcessingAborted)
Cannot read the next data row for the dataset ds_xxxxx. (rsErrorReadingNextDataRow)
Arithmetic overflow error converting expression to data type datetime.
If I download the RDL file back out of SSRS and run it in VS2008, it works fine!!! What am I missing, it must be a configuration item maybe within the Config file for SSRS, but what?