6

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?

enter image description here

aron
  • 2,856
  • 11
  • 49
  • 79

3 Answers3

3

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:

enter image description here

Community
  • 1
  • 1
JonH
  • 32,732
  • 12
  • 87
  • 145
  • 3
    You don't even need to set up a dataset for this. You can provide the default as a formula in the parameter. – Jamie F Oct 26 '11 at 18:40
  • @JamieF - thats true but with a dataset it is more reusable and flexible. With a dataset you can reuse this code across multiple reports. – JonH Oct 26 '11 at 19:28
  • 1
    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. – Eric Ness Mar 13 '13 at 18:45
  • @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). – JonH Mar 14 '13 at 14:10
  • @EricNess Formula for default looks great to me. Can you please post an answer with the formula to be used in the parameter? – LCJ Feb 16 '18 at 18:00
1

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

khr055
  • 28,690
  • 16
  • 36
  • 48
Anil
  • 11
  • 1
0

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')
CW1255
  • 113
  • 1
  • 1
  • 6