Publish an SSRS Subscription From an Application
Here’s Your Situation:
- You have an SSRS report to publish dailiy, when ready
- And a list of subscribers
- When the key user clicks a button, the subscribers should be emailed their report for the day
Here’s Your Problem:
SSRS doesn’t have an subscription option that works that way. It likes to publish on a schedule, or, if you specify “When the report data is updated on the report server“, it tries to work with a cached version of your report. That wouldn’t work because we couldn’t figure-out how to cache the report when user clicked a button in the application. There is no built-in way to push the subscription from within your application 😦 Naturally, your users can’t figure-out why you can’t do something that ought to be so simple!
Here’s Your Solution:
- Create a data-driven subscription for your report
- Key: use a stored procedure as the data source
- Write your stored procedure to not just return data, but also update the database so that, the next time, it won’t return any data. Effectively, it only returns data once per day. (Unless the users want to republish.)
- Key: make sure you “Grant Execute” on your stored procedure to the account running the report. Apparently granting execute privileges to public doesn’t work.
- Key: use the correct syntax to invoke your stored procedure from SSRS (see below)
The Solution Nitty-Gritty
Nitty-Gritty
Write your stored procedure to return the same columns regardless of whether the you want the subscription to be published. However, make sure no rows are returned unless you are ready to publish.
The following stored procedure excerpt assumes you already have a variable named @Midnight containing todays’ date with no time component:
--This 'if-clause' returns data for today if the SubscriptionSent flag hasn't been set yet
If Exists (Select *
From PublishEvent
Where SubscriptionSent = 0
And
PublishDate Between @Midnight And DateAdd(dd, 1, @Midnight)
)
Begin
--Set the flag so it won't happen again
Update PublishEvent
Set SubscriptionSent = 1
Where PublishDate Between @Midnight And DateAdd(dd, 1, @Midnight)
--Return the list of subscribers
Select EmailAddress
From EmailNotification
Where Active = 1
End
Else
Begin
--Select an empty list with the same columns as above
Select EmailAddress
From EmailNotification
Where (1 = 0)
End
Note the ‘where-clause’ for my second select statement – I wrote it so that it never returns any rows. This is necessary to ensure we get the same columns regardless of whether the flag has been set.
Explicitly Grant Permission!
Here’s what I did for my sproc:
Grant Execute on selSubscriptionEmailList to GenDispatchRpt_SSRS_RD
Make sure you revise the permissions if you change the account for the report!
Set-up Your Subscription in SSRS
Examine the screen shots below, which are from SSRS. They skip the easy choices and only illustrate the tricky steps.
Bottom line: when user clicks the application button, it updates the database, creating an entry for today in our table ‘PublishEvent’. Key point: the flag ‘SubscriptionSent’ is set to false; when SSRS runs, the stored procedure will return a list of email addresses and, as a side-effect, set that field to true. This ensures the subscription only gets published once.
Summary
By using a stored procedure that not only fetches data, but also updates a table as a side effect when it runs, you can make SharePoint check whether it should run every ten minutes, but only publish once per day. The upshot is that your users can trigger the publish event when they are ready, and it won’t publish again until the next day. If you didn’t use the stored procedure this way, you would have to settle for publishing on a fixed schedule regardless of whether the users are ready. With this trick, the users can have the convenience of SharePoint publishing, but under their control. Maybe they’ll finally give you some respect!~