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?

Entomostracan

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:

Raoul

You don't even need to set up a dataset for this. You can provide the default as a formula in the parameter. – Soelch

@JamieF - thats true but with a dataset it is more reusable and flexible. With a dataset you can reuse this code across multiple reports. – Raoul

I don't see how using a dataset is more flexible. It would require access to the same database, while a formula could be cut and paste across databases and servers. – Heterosexual

@EricNess - When you need parameters such as dates, the post I linked to is more flexible then hard coding some function or formula. It handles any sort of date such as (start of month, end of month, start of week, end of week, last day of year, first day of year, and many more). – Raoul

@EricNess Formula for default looks great to me. Can you please post an answer with the formula to be used in the parameter? – Disintegrate

Answer 2

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')

Mustache

This is quite an ingenious solution! And, unlike the data-driven subscription answer, it does not require the Enterprise Edition of SSRS. – Claiborne

Answer 3

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

Manipulator

how do you call it from subscription – Runofthemill

As far as I know it is not possible to call a stored proc from a subscription. Unless the subscription is data-driven? But the answer doesn't say that. – Claiborne