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