CODEWITHSUNDEEP
reporting-servicesssrs-2008ssrs-2008-r2
Jin Kim
Jin Kim

Reputation: 17752

Run SSRS subscription with different parameters

Is there a way to programmatically execute an existing subscription with different report parameters?

So far I execute subscriptions via SQL as follows:

EXEC ReportServer.dbo.AddEvent 'TimedSubscription', '<Subscriptions.SubscriptionID>'

But this seems to only allow me to execute the subscription as-is.

Edit: I wanted to do this for our existing subscriptions (50+ total). Manually re-creating each subscription is not feasible.

Upvotes: 2

Views: 5742

Answers (3)

PeterX
PeterX

Reputation: 2911

The first commenter in this article updates the Parameters value in the Subscriptions table via a Stored Procedure before running the AddEvent command:

update Subscriptions 
set Parameters=    
'<ParameterValues><ParameterValue><Name>Value</Name><Value>' + @value + '</Value></ParameterValue></ParameterValues>'
where SubscriptionID='XXX-XXX-XXX-XXX'

Upvotes: 1

SQLUser44
SQLUser44

Reputation: 51

If I'm understanding your question correctly, you could take 2 of the following approaches:

Approach 1

Step 1: Navigate to the report you would like run with different parameters.
Step 2: Select to manage the report
Step 3: Select to Create Linked Report
Step 4: Choose the location where the report can live.
Step 5: For the new linked report, click to manage the report
Step 6: Change the default parameters you'd like passed
Step 7: Create subscription for new linked report.

Approach 2

Step 1: Navigate to the report you would like run with different parameters.
Step 2: Select to manage the report.
Step 3: Create a subscription for the report
Step 3a: While creating subscription, specify what values you want passed.

Hope this helps!

Upvotes: 0

iamdave
iamdave

Reputation: 12243

The way I worked through this problem was to set up a data driven subscription on the report that populated the parameters based on the values held within a specifically designed SQL table. I then set up a stored procedure that populated that table with the values that were required and then fired the subscription using AddEvent.

You may run into issues with this approach if you need to run fire the subscriptions more frequently than it takes to produce the report and thus cleaning out the lookup table. If the report isn't that frequent though, you should be fine.

Upvotes: 0

Related Questions