SQL Server Reporting Services Subscription with dynamic parameters

Question

I have a custom report in SQL Server Reporting Services. This report has a few parameters, like: Requested date start, Requested data to, donor.

I would like to setup a scheduled email. However I would like to assign the "Requested date start" value to = (current datetime-1 month) and assign the "Requested date end" value to = current datetime

Any tips on how to do this?

Answer 1

This is quite simple, see my answer here:

SQL Server: calculating date ranges

What you have to end up doing is creating these calculations as a dataset and then use the "Use Default" checkbox in the subscription itself:

Answer 2

Write a stored procedure and get the following from the sql in the stored proc:

select @FileName  as FILENAME, @PATH as PATH, 'EXCEL' as RENDER_FORMAT , 'Overwrite' as WRITEMODE, 
'True'  as FILEEXTN , 'null' as USERNAME, 'null' as PASSWORD

Call the proc from the subscription

Anil Madan

Answer 3

I had a similar issue. I created the subscription with an initial set of parameters and set the run time. Then I created a sproc and a job to run the sproc before the schedule run time of the subscription. Your date math will be different from mine depending on your needs. I then copied the parameter settings to notepad, found the parts I needed to change and put them on their own lines. Then put them in the sproc as below.

UPDATE ReportServer.dbo.Subscriptions
SET Parameters = 
(SELECT '<ParameterValues><ParameterValue><Name>Summary</Name><Value>PODETAIL</Value></ParameterValue><ParameterValue><Name>requisitioner_erp_user_id</Name><Value>51</Value></ParameterValue><ParameterValue><Name>requisitioner_erp_user_id</Name><Value>125</Value></ParameterValue><ParameterValue><Name>date_range_type</Name><Value>3</Value></ParameterValue><ParameterValue><Name>po_date_end</Name><Value>'
+
  (SELECT FORMAT(DATEADD(wk, DATEDIFF(wk, 1, GETDATE()), -1), 'MM/dd/yyyy') AS po_date_end)
 +
 '</Value></ParameterValue><ParameterValue><Name>po_date_start</Name><Value>'
 +
  (SELECT FORMAT(DATEADD(wk, DATEDIFF(wk, 0, GETDATE()) - 1, 0), 'MM/dd/yyyy') AS po_date_start)
 +
 '</Value></ParameterValue></ParameterValues>'
)
WHERE ReportServer.dbo.Subscriptions.SubscriptionId IN ('B6645FD3-DE27-4551-8331-C0135305CC79')