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.
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