question

GPO avatar image
GPO asked

SSRS E-mail report content based on user

SSRS 2008R2 I have a report for which I want to set up a data driven subscription. It needs to return different rows for different users and be automatically e-mailed to those different users. Say, for example, the report was based on one dataset and a column in that dataset was called [user]. I want the report e-mailed where UserA just gets the rows with UserA in the [user] column, UserB just gets the rows with UserB in the [user] column and so on. I'm not sure how to configure a data-driven subscription to do this (or even whether it's possible). I'm sure this is not an unusual requirement. Are there any good step-by-step guides that can be recommended?
ssrssubscriptionsdata-driven
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
This is exactly the case for which Data Driven Subscription Exists. You should prepare a select which will return e-mail address of the person to which the email should be delivered + additional column for each report parameter, so single row contains email address + all necessary parameters for particular user. Additionally you can have another columsn for nother parameters like preferred delivery format, email subject, email message body etc. The Steps to configure are as folow: 1. Select a Data Source which will be used to satisfy the Data Driven Subscription query (the query returning email addresses and all necessary parameters). 2. Enter the DDS query (it can be even Exec of stored proc. 3. Map the columns to appropriate parameter of Data Driven Subscription. You have options to specify a fixed value where you do not need dynamic behavior, or select a value from Database (Column from the query mentioned above). 4. Specify parameter of the report itself. Agai can be static or you can select a field from above query to support such parameter. 5. Finally select a schedule when report should be executed. Don't forget, that in case of Subscriptions the DataSource supporting the report and Data Driven Subscription itself Has to have stored credentials inside the Data Source definition. (or No credential specified and SSRS can use the default credentials specified in SSRS Configuration). **Edit:** Another note. There is an issue in SSRS 2008 and SSRS 2005 if you want to deliver multi-line email body (a message sent together with report) in the Data Driven Subscription. Even your query returns a mlutiline text, the text is delivered as a single line in the email. There is a workaround of returning an HTML for the email body. I have details about this in my post: [SSRS 2008 & Multi-Line email body in Data-Driven Subscription delivered by email][1]. The issue is fixed in SSRS 2008R2. [1]: http://www.pawlowski.cz/2010/12/ssrs-2008-multi-line-email-body-in-data-driven-subscription-delivered-by-email/
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

GPO avatar image GPO commented ·
Fantasic stuff Pavel (yet again). With a bit of trial and error, I think I've got it to do what I want.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.