Passing Date Parameter To SubReport SSRS SharePoint Integrated Mode

When SSRS is rendered in SharePoint Integrated Mode if there is a drop down parameter of type DateTime and it is passed to a parameter of DateTime in the SubReport, the subreport fails to load when rendering in browser though it loads correctly from reportbuilder.

Again much pain, I decided to convert the date parameters into Text as the parameters were drop down displaying in dd/MM/yyyy format. The SQL query to populate the parameter

SELECT CONVERT(nvarchar(10),Min(TimeByDay),103) DisplayDate
,Convert(date,Min(TimeByDay)) StartTime
,CONVERT(VARCHAR(10), Min(TimeByDay), 112) FormatStartTime
FROM [dbo].[MSP_TimeByDay]
where Year(TimeByDay) = 2015
GROUP BY MONTH(TimeByDay), Year(TimeByDay)
order by StartTime Asc

Results of query
DisplayDate StartTime FormatStartTime
01/01/2015 2015-01-01 20150101
01/02/2015 2015-02-01 20150201
01/03/2015 2015-03-01 20150301
01/04/2015 2015-04-01 20150401
01/05/2015 2015-05-01 20150501
01/06/2015 2015-06-01 20150601
01/07/2015 2015-07-01 20150701
01/08/2015 2015-08-01 20150801
01/09/2015 2015-09-01 20150901
01/10/2015 2015-10-01 20151001
01/11/2015 2015-11-01 20151101
01/12/2015 2015-12-01 20151201

The SQL function CONVERT(VARCHAR(10), Min(TimeByDay), 112) returns date in yyyyMMdd format which can be passed to a date parameter of dataset without converting to a date object.

The date parameter of the main report and sub report is set in a similar fashion


And date parameters are just passed to the subreport without any conversion.


The main report is rendered in SharePoint Integrated mode and report builder without any issues.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s