0

Hi I am using visual studio 2015 to make an SSRS 2014 report. The place I am stuck at is my email recipient is dependent on what company is selected from the SSRS report.

Not that it should matter but my report parameters are Company (really companyID),begdat,enddate.

select email from users where companyid= @companyid<< which would be my selection.

Most examples I see have a defined list of emails or just 1 person to email. I want to send a report per company only to the users of that company aka dynamic recipient list.

T Dang
  • 157
  • 3
  • 17
  • Possible duplicate of [Dynamically Set the to Address in SSRS reports Subscription](https://stackoverflow.com/questions/30318484/dynamically-set-the-to-address-in-ssrs-reports-subscription) – ViKiNG Jul 25 '17 at 01:43

1 Answers1

0

  1. Modify your query as: select email, companyid from users

  2. At the next step in select an option Get the value from the database for the TO field and set it to email.

  3. At the next step change your company option to Get the value from the database and set it to companyid.

Then users from company CompanyA will receive only a CompanyA report, and CompanyB - only a CompanyB report.

You could debug the subscription using temporary query with only your email:

select email='your@email', companyid = 'CompanyAid' UNION ALL select email='your@email', companyid = 'CompanyBid'

Dzmitry Paliakou
  • 1,587
  • 19
  • 27
  • Thank you for the reply, would you recommend having the email and companyid in 1 table or view? As of right now companyid is on a seperate company table,email is on users table and the user/company association table is a seperate 3rd table. – T Dang Jul 25 '17 at 14:06
  • it's not necessary to have all the data in one table, you can write the query using JOIN similar to: `select email, companyid from users join userscompanies ON users.id = userscompanies.userid` – Dzmitry Paliakou Jul 25 '17 at 14:56
  • After many hours I finally got a query that suits my needs. Thank you! – T Dang Jul 25 '17 at 19:57