CODEWITHSUNDEEP
sql-serverreporting-servicesssrs-2012data-driven
MDP
MDP

Reputation: 13

SSRS Data-Driven Triggered Email

I am fairly new to SSRS and have a question on how to send an email to individuals that are in the report.

I was tasked to create a report that list all employees with their email address and some information about them, the report also contains the email address of their managers.

I already have created the report that list all of the employees and the necessary information, now their managers should received an email that will only show the information regarding the employees that are directly under them and the email should only be sent to the managers if they have employees under them that appears in the list from the report.

Upvotes: 1

Views: 1162

Answers (2)

Tab Alleman
Tab Alleman

Reputation: 31785

SSRS lets you create subscriptions that use data, however, it doesn't let you use data to create subscriptions.

What you CAN do is this: Add "Manager Name" as a parameter to your report, and have the report only show the employees that are directly under the Manager passed to the parameter. Then create a subscription for each manager that passes their name to the "Manager Name" parameter.

What you CANNOT do is use the data that gets generated by the report to create subscriptions dynamically for the managers whose employees will be in the report that day. To do something like that, you'd need to write your own reporting application in .net or something similar.

Upvotes: 1

Vic Street
Vic Street

Reputation: 168

Create a stored procedure which gathers the report data. Here's an example of how you could construct it

1) Create the stored procedure

a) It collects managers and employees into a table variable

b) It uses a cursor to loop through each manager in the table variable and executes a second stored procedure which uses DB-email to send them a message

c) It SELECTs the report data

2) Create the SSRS report

a) Create a dataset which calls your stored procedure from step 1) (Note: this will send email to the managers every time it runs!)

b) Publish the SSRS report to the reportserver

c) Create subscriptions for the report so it will run when you want it to. When SSRS calls the stored procedure to produce the report data, that stored procedure will call your second stored procedure which emails each manager

Upvotes: 1

Related Questions