CODEWITHSUNDEEP
reporting-servicesssrs-2008-r2
codingIsCool
codingIsCool

Reputation: 72

How to specify different from addresses for difference subscription recipients?

I have an SSRS report that is sent out to different user groups based on the business function. I need the subscription to change the from address depending on who the report is being sent to. For example when the report is for the Finance group it should be sent from [email protected] but when it is for the Procurement group it should come from [email protected].

Presently the report is just sent from the address configured in the from field of the subscription settings.

I read in a post somewhere once that changing the owner ID in the Subscriptions table would do it - but that did not change anything for me.

Is there a way to make the from address dynamic in an SSRS subscription?

I'm using SQL Server and SSRS 2008 R2.

Upvotes: 0

Views: 3314

Answers (1)

Mike D.
Mike D.

Reputation: 4104

The simplest way to do this is to create multiple subscriptions with different from addresses configured. But if you have the Enterprise or Business Intelligence edition of SQL Server there is a better way.

You can accomplish this using a data driven subscription. This allows you to set all of the subscription options with SQL queries. You will need Enterprise or Business Intelligence edition of SQL Server in order to use data driven subscriptions.

I'm assuming your report takes a parameter to determine the type of report (e.g. finance, procurement, etc.)

First go to the Subscriptions section for your report and create a new data-driven subscription: Create new subscription.

You then configure your data source for the SQL query that will return your subscription information. Note: this doesn't actually have to rely on tables in the database you can synthesize all of the values in a SELECT statement if that works for you.

You could use a query similar to this to get all of your settings:

SELECT 'finance' AS type,
    '[email protected]' AS rcptAddr,
    '[email protected]' AS fromAddr

UNION ALL

SELECT 'procurement',
    '[email protected]',
    '[email protected]'

Then the next couple of screens allow you to use the values determined by this query to set various options for the subscription. In this case you would set your parameter value to the type field, your to value would use the rcptAddr field, and you would set your from field with fromAddr.

In this way you can configure dynamic subscriptions that have different recipients, reply to addresses, from addresses, subjects, etc. all based on the parameter value you're passing to the report.

Upvotes: 2

Related Questions