Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rexyrexin
Starting Member
19 Posts |
 Posted - 2011-07-17 : 12:41:22
|
| I want to use subscription for the reports. Eg- I want a report to run on certain date of the month and it should be sent to the client in a pdf format automatically. Please anyone tell me |
|
|
ronsh123
Starting Member
1 Post |
Posted - 2011-07-19 : 13:38:26
|
| The expectation with this reply is that you are some-what familiar with the SSRS subscription creation, specifically the Data Driven subscription. Data Driven subscription creation steps: a) If this is not to execute on a standard date interval, such as first of the month, every Friday, etc, then first create a calendar table that contains a date column that you can enter the specific dates. i.e. Within SS Mgmt Studio. Insure the database used below is accessable to the credentials assigned to the published SSRS report: USE <your database>; CREATE TABLE dbo.SSRSsubscriptionCalendar (RunOnThisDate DATETIME NOT NULL); INSERT INTO dbo.SSRSsubscriptionCalendar VALUES ('07/19/2011'); -- add your list of distinct dates Otherwise, a standard date function can be utilized within the subscription SQL. b) Create a "Data Driven" subscription based on the report in question. c) In step 2 of the subscription wizard, select the database where the calendar table was created. If not using the calendar method, then select any database that permissions have been granted credentials assigned to the published SSRS report. d) In step 3 of the subscription wizard, write a simple SQL statement that will query the calendar table or use a datetime function as described above. i.e.: -- Usage for Calendar table SELECT 1 FROM dbo.SSRSsubscriptionCalendar AS C WHERE DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) = C.RunOnThisDate -- OR -- Usage with fixed date interval SELECT 1 WHERE DATENAME(dw, GETDATE() ) = 'Tuesday' -- your desired date interval e) In step 4, fill out the delivery info - To, Cc, Bcc (I include myself), etc... f) In step 6, specific when to process the subscription. I use "On a schedule created for this subscription" g) In step 7, have this run daily. Set the time according to when you want this to deliver the report. h) Click "Finish"Essentially, the subsciption will be triggered to execute on the days & time chosen in step 7. If the logic within the SQL is satisfied based on the calendar dates or fixed date interval, then it will process the subscription and send out email with the report (or link to the report as you setup in step 4). If the logic within the SQL is not satisfied, it will not send out the email. Hope this helps.Ron H |
 |
|
|
rexyrexin
Starting Member
19 Posts |
Posted - 2011-07-20 : 02:00:40
|
| Thanks |
|
|
|
|
|
|
|
|
Report Subscription