Publish an SSRS Subscription From an Application

Posted on December 28, 2013

Here’s Your Situation:

1. You have an SSRS report to publish dailiy, when ready
2. And a list of subscribers
3. 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:

1. Create a data-driven subscription for your report
2. Key: use a stored procedure as the data source
3. 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.)
4. 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.
5. 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!~