Posted by (JavaScript must be enabled to view this email address) on Wed 18 Nov 2009

Today we had an issue with a report and its related subreport in CRM 4.0, specifically that the report wouldn’t run and we were receiving the following message…

 
"The sub report could not be shown" 
 

Looking at the reporting services log on the server, we could see the following error, leading us to believe that the problem was a result of a date field being passed between the main report and its related sub-report, ...

 
w3wp!processing!7!11/17/2009-08:53:56:: 
e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot read the next data row for the data set _MSCRM., ;
Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot read the next data row for the data set _MSCRM. 
---> System.Data.SqlClient.SqlException: Arithmetic overflow error converting expression to data type datetime.
 

This problem however was only being experienced on certain machines. Looking at the specifics of these machines we could see that the machines affected were those who had their CRM Current Format set as English (United Kingdom), those machines who had their current format as the default English (United States) could run the report without any issue.

The only date related functionality we had in the report was a string parameter in the main report which held a date value, this was being passed from the main report to the sub-report and was used to compare against a datetime field in the sub-report. The source dataset of the date string parameter was as follows…

 
DECLARE @DateRange AS Table
(
	Period VARCHAR(14),
	EndDate DATETIME
)
 
INSERT INTO @DateRange (Period, EndDate)
VALUES('Current Month', DATEADD(WEEK, 3, GETDATE()))
 
SELECT EndDate FROM @DateRange
WHERE Period = 'Current Month'
 

Looking at the above sql I could see that the datetime value was being pulled directly from the sql (therefore it would be in american format, potentially causing issues on british format CRM browsers), so I therefore decided to change this to ensure the date was in the more generic ISO format of yyyymmdd.
I therefore changed the SQL to the below, replacing one line with a CONVERT statement, shown below…

 
DECLARE @DateRange AS Table
(
            Period VARCHAR(14),
            EndDate DATETIME
)
 
INSERT INTO @DateRange (Period, EndDate)
VALUES('Current Month', DATEADD(WEEK, 3, GETDATE()))
 
SELECT CONVERT(varchar,EndDate,112) AS EndDate
FROM @DateRange
WHERE Period = 'Current Month'
 

After modifying the dataset as per the above and re-uploading the report it worked without issue, as I had removed the dependancy on an American date format which didn’t agree with any English (United Kingdom) format CRM browsers.