1

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.

Dale K
  • 25,246
  • 15
  • 42
  • 71
MDP
  • 13
  • 2
  • wait a minute you can use CURSORs and where ever your reading the data from; you use that proc and its param to create a temp table. Since you have manager(s) email you can create and DB-email and send html formated text with desired rows about their specific employees. It wont be an SSRS report with headers etc but close. – junketsu Dec 12 '18 at 20:30

2 Answers2

1

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

Vic Street
  • 168
  • 1
  • 11
  • safer to use table variables because sql-server has that limitation where nested SP using temp tables is not allowed. Esp if op gets a cursor involved. – junketsu Dec 12 '18 at 20:49
  • we never did step 2 at my last place. Seems do-able. Leveraged db-mail with html tags to create a report like feel and send out to managers/employees with their requested data. – junketsu Dec 12 '18 at 20:52
  • Thank you guys for your help, this pointed me to the right approach/solution. – MDP Dec 13 '18 at 03:19
1

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.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • Thank you Tab for your suggestion, it helped me to achieve a great deal of knowledge how to approach the situation. – MDP Dec 13 '18 at 03:21