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

DateParameter

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

SubreportDt

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

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s