Setting up an SSRS Data Driven Subscription to email recipients using SharePoint

[dxhansen]

thread1462-1621585
thread1462-1458983

I have been looking all over the web, trying to find a good example/tutorial about setting up Data Driven subscriptions for email recipients using SharePoint. Everything I find is about setting up subscriptions for delivery to shared folders.

I have successfully set up a DDS for a report that went to specified recipients. However, each of them got the same report, instead of receiving their own report specific to them. How is this set up? Do you have to use parameters to specify the recipient specific info? If so, how is this specified in the DDS?

If anyone knows of a good example/tutorial page, it would be greatly appreciated.
Microsoft's pages only go over the Shared Folder option. (

https://msdn.microsoft.com/en-us/library/ms169972.aspx)

 

[dxhansen]

Well, after looking all over for something and not finding what I needed. I just started doing some experimenting and figured it out.

Here is what I came up with.


The report must be set up with a Parameter which is used to identify the Grouping which the report will be limited to. In this case, the report will be limited to the specified sales person.

Put together a query which pulls a distinct list of recipients and email addresses. In this case it is from one data source and only includes three recipients. (It is not required to limit the list of recipients. In this case the WHERE clause can be eliminated to accommodate all sales persons in the data source)


SELECT DISTINCT

SalesPerson

, EmailAddress

FROM DataSource

WHERE SalesPerson IN ('mmouse', 'dduck', 'ppan')



Once the data connection information has been entered in the Data Definition screen, this query will need to be put into the Query box.

Select “Validate” when finished.

On the Parameters screen, Select the “Override report default value from database” radio button, and from the drop down, select the field which will give you the desired parameter. In this case, it would be SalesPerson.

On the Delivery Options screen, select “E-Mail as the Delivery Type”, and in the To field, Select the “Select a value from the database” radio button. Then from the drop down menu, select database filed which will provide the email addresses for the recipients of the reports. In this case it is EmailAddress.

Continue setting up the scheduled report with the desired formatting, delivery, and schedule options.