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.

Posted by (JavaScript must be enabled to view this email address) on Tue 28 Jul 2009

The first thing to do is get the current month:

SET @CurrentDate = GetDate()SET @CurrentMonth = Month(@CurrentDate)

Then its just a case of setting the start and end of the fiscal year according to whether the month falls in the last quarter (Jan, Feb, Mar) or the first three quarters (April thru to Dec):

IF @CurrentMonth >= 4
BEGIN	
SET @FiscalYearStart = DATEADD(mm, 3, DATEADD(yyyy, DATEDIFF(yyyy, 0, @CurrentDate), 0))	
SET @FiscalYearFinish = DATEADD(mm, -1, DATEADD(yyyy, 1, @FiscalYearStart))
END
ELSE
BEGIN	
SET @FiscalYearStart = DATEADD(mm, -9, DATEADD(yyyy, DATEDIFF(yyyy, 0, @CurrentDate), 0))	
SET @FiscalYearFinish = DATEADD(mm, -1, DATEADD(yyyy, 1, @FiscalYearStart))
END

Hope this helps you in your report building duties full code below:

Declare @CurrentDate AS DateTime
Declare @CurrentMonth AS int
Declare @FiscalYearStart AS DateTime
Declare @FiscalYearFinish AS DateTime
SET @CurrentDate = GetDate()
SET @CurrentMonth = Month(@CurrentDate)
IF @CurrentMonth >= 4
BEGIN	
SET @FiscalYearStart = DATEADD(mm, 3, DATEADD(yyyy, DATEDIFF(yyyy, 0, @CurrentDate), 0))	
SET @FiscalYearFinish = DATEADD(mm, -1, DATEADD(yyyy, 1, @FiscalYearStart))
END
ELSE
BEGIN	
SET @FiscalYearStart = DATEADD(mm, -9, DATEADD(yyyy, DATEDIFF(yyyy, 0, @CurrentDate), 0))	
SET @FiscalYearFinish = DATEADD(mm, -1, DATEADD(yyyy, 1, @FiscalYearStart))
END
SELECT @FiscalYearStart, @FiscalYearFinish

Posted by (JavaScript must be enabled to view this email address) on Fri 27 Mar 2009

To put a total at the end of the SSRS report, add a textbox to the DataSet Region and set the visibility property of the text-box to an expression - set the the expression to:

=iif(RowNumber("List1") = Count(Fields!.Value),true,false) 

Then a just set the expression on the text box to:

="Total between " & Parameters!.Value & " and " &_
Parameters!.Value + ": " & RowNumber("List1") 

and this is what it looks like rendered:

Enjoy!