Data driven report subscription send one email to all subscribers in comma separated list

Question

With an existing data driven report solution, a scheduled report is sent to several subscribers by email individually. I;e. if there are 4 users that subscribe to the report in the subscription database then 4 emails will be sent by reporting services. The manager would like to have the report sent once with all the subscribers in the TO section in a comma separated list. that way he can see who else received the email.

Current situation

The subscription database basically looks like this

The Subscription table holds the report parameters needed to subscribe to a report

The UserData table holds general information about the Windows users that can be subscribers.

For the many to many relation between subscriptions and users, the relation has been defined in the table ‘SubscriptionUser’ where users can be assigned to one ore more subscriptions.

There is also a lookup table ‘ReportFormat’ that holds the name of various export formats available in SSRS.

The original statement used by the data driven subscription in reporting services was:

SELECT a.SubscriptionName, d.EmailAddress AS [TO], 'true' AS IncludeReport, b.ReportFormatValue AS RenderFormat

    , a.MessageSubject, a.IncludeLinkToReportserver, a.IncludeFileInMessage, ISNULL(a.MessageBody,'') AS MessageBody

FROM dbo.Subscription AS a

INNER JOIN dbo.ReportFormat AS b ON a.ReportFormatID = b.ReportFormatID

INNER JOIN dbo.SubscriptionUser AS c ON a.SubscriptionId = c.SubscriptionId

INNER JOIN dbo.UserData AS d ON c.UserId = d.UserId

WHERE a.SubscriptionId = 3 AND d.IsActive = 1

This statement will yield a number of lines, in this example 4 lines

jack@abc.com    true    Excel    Weekly sales report    0    1    Dear Receiver, <br/><br/>Find enclosed the weekly sales report. <br/><br/>Regards <br/>
tom@abc.com    true    Excel    Weekly sales report    0    1    Dear Receiver, <br/><br/>Find enclosed the weekly sales report. <br/><br/>Regards <br/>
pete@abc.com    true    Excel    Weekly sales report    0    1    Dear Receiver, <br/><br/>Find enclosed the weekly sales report. <br/><br/>Regards <br/>
mike@abc.com    true    Excel    Weekly sales report    0    1    Dear Receiver, <br/><br/>Find enclosed the weekly sales report. <br/><br/>Regards <br/>

Solution

We need to have only one row returned from our data driven select statement. The Email addresses need to be concatenated in one semicolon (not comma!) separated string that will serve as the TO column.

• Select email addresses for the given report
  
  
  
  

  SELECT EmailAddress 

  FROM dbo.UserData AS dd

  INNER JOIN dbo.SubscriptionUser cc ON cc.UserId = dd.UserId

  WHERE cc.SubscriptionId = 3 AND dd.IsActive = 1

jack@abc.com
tom@abc.com

pete@abc.com

mike@abc.com




• This will yield 4 rows again, now these rows need to be concatenated into a single string. For this the T-SQL FOR XML PATH can be used
  
  SELECT  ';' + EmailAddress
  FROM
  (
      SELECT EmailAddress 
      FROM dbo.UserData AS dd
      INNER JOIN dbo.SubscriptionUser cc ON cc.UserId = dd.UserId
      WHERE cc.SubscriptionId = 3 AND dd.IsActive = 1
  ) AS e FOR XML PATH('')
  
  
  ;jack@abc.com;tom@abc.com;pete@abc.com;mike@abc.com
  




• Now the first semicolon needs to be trimmed from this string, this can be achieved with the STUFF function in T-SQL. The complete select statement result needs to b used as argument to the STUFF function.
  SELECT STUFF(
              (
                  SELECT  ';' + EmailAddress
                  FROM
                  (
                      SELECT EmailAddress 
                      FROM dbo.UserData AS dd
                      INNER JOIN dbo.SubscriptionUser cc ON cc.UserId = dd.UserId
                      WHERE cc.SubscriptionId = 3 AND dd.IsActive = 1
                  ) AS e FOR XML PATH('')
              ) ,1,1,'') AS EmailAddress
  
  
  
  jack@abc.com;tom@abc.com;pete@abc.com;mike@abc.com
  




• Finally the result has to be selected together with the other report subscripion parameters for the report with id 3.
  SELECT STUFF(
              (
                  SELECT  ', ' + EmailAddress
                  FROM
                  (
                      SELECT EmailAddress 
                      FROM dbo.UserData AS dd
                      INNER JOIN dbo.SubscriptionUser cc ON cc.UserId = dd.UserId
                      WHERE cc.SubscriptionId = 3 AND dd.IsActive = 1
                  ) AS e FOR XML PATH('')
              ) ,1,1,'') AS [TO], 'true' AS IncludeReport, b.ReportFormatValue AS RenderFormat
      , a.MessageSubject, a.IncludeLinkToReportserver, a.IncludeFileInMessage, ISNULL(a.MessageBody,'') AS MessageBody
  FROM dbo.Subscription AS a
  INNER JOIN dbo.ReportFormat AS b ON a.ReportFormatID = b.ReportFormatID
  WHERE a.SubscriptionId = 3
  
  



• 
  

  The result is one line and a semicolon separated [TO] column

  jack@abc.com;tom@abc.com;pete@abc.com;mike@abc.com    true    Excel    Weekly FER report    0    1    Dear Receiver, <br/><br/>Find enclosed the weekly FER report. <br/><br/>Regards <br/>