Dynamically set address in SSRS Subscription reports
I am showing a list of items with an Assigned to field, which are email addresses. I have now set up a subscription with a separate list of assigned email addresses and sent the list of all items to all users. I want to send only to these items assigned to a specific user by that user; Instead of sending all items to all users. Can this be done in SSRS? I would have to dynamically set the To address and filter the result dataset.
source to share
It looks like you need to set up a data subscription available in the enterprise version of SSRS 2012.
Create a report using a custom parameter that will create a list of items attached to email addresses.
When you create a data-driven subscription, you have the ability to create another SQL statement that can be used for recipients and get values to pass as parameters to your report.
If your proc is instantiated something like (yours will probably be a little more complicated ...)
CREATE PROC GetAssigned
@EmailAddress nvarchar(255)
As
IF @EmailAddress is Not null
SELECT EmailAddress,Item1,Item2
FROM MyTable
WHERE EmailAddress = @EmailAddress
ELSE
SELECT DISTINCT EmailAddress FROM myTable
You can run this process both in a report and as a data subscription to get a list of email addresses to send and as a parameter in the report. (Although you can just add the request SELECT DISTINCT
in the box below and not have it in the proc?)
To set up a subscription (enterprise version, right?)
Although, you can just type your script in the box above.
SELECT DISTINCT EmailAddress from MyTable
- Then on the next screen when entering recipients use the value returned by proc
source to share