Cfrgtkky

I have the below supplier data in SSRS 2008 report.

ID    Name    Details    Email

1     abc      d1       rob.b@gmail.com

2     def      d2       pat@gmail.com

3     ghi      d3       golf@gmail.com

4     dft      d4       rob.b@gmail.com 

I need to send the relevant details to each email id. For example, in the data above, I need to send the entire record of ID 1 and 4 to rob.b@gmail.com.
Is there any way to send just the relevant details to the emails given in the report. Could someone please advise.

Yes - you can use a Data Driven Subscription to do this in SSRS - if you have the Enterprise version of SQL Server.

If you do not have the Enterprise version, you can still do this with a little bit more effort using a scheduled Stored Procedure to emulate the data driven subscription.

Either way, you would have a report that works with a parameter to identify a single record. Then the separate data driven part would have a different query that gets a list of IDs that you need for the report along with the e-mail address to send it to. The data driven part will then generate a separate report for each of the ID parameter that you created in the report and then send them to the address.

Data Driven Subscription properties:

Unfortunately, both methods are too big of a topic to be covered in an answer.

MSDN - Data Driven Subscriptions

MSSQLTips.com - simulate-reporting-services-data-driven-subscriptions

Here's a SP that I made to generate multiple e-mail:

ALTER PROCEDURE [dbo].[RECORD_REVIEW_GENERATION]



AS

DECLARE @SUBSCRIPTION_ID AS VARCHAR(100) = (SELECT SETTING_VALUE FROM WORKING_STORAGE.dbo.SETTINGS WHERE SETTING_NAME = 'REVIEW_SUBSCRIPTION')

DECLARE @SUBJECT VARCHAR(200) 

DECLARE @COMMENT VARCHAR(500) 

DECLARE @REVIEW_ID VARCHAR(50) 

DECLARE @EXTENSION_SETTINGS XML

DECLARE @PARAMETERS XML

DECLARE @RECORD_ID AS INT 

DECLARE @MAX_RECORD_ID AS INT 

DECLARE @END_DATE AS DATE = GETDATE() - DATEPART(WEEKDAY, GETDATE())    --PREVIOUS SUNDAY

DECLARE @START_DATE AS DATE = DATEADD(DAY, -6, @END_DATE)   --PREVIOUS MONDAY

DECLARE @DATE VARCHAR(500) = CONVERT(VARCHAR(8), @END_DATE, 112)







SELECT @EXTENSION_SETTINGS = ExtensionSettings, @PARAMETERS = [Parameters]

FROM ReportServer.dbo.Subscriptions

WHERE SubscriptionID = @SUBSCRIPTION_ID;







IF OBJECT_ID('tempdb..#REVIEWS') IS NOT NULL DROP TABLE #REVIEWS 



CREATE TABLE #REVIEWS(ReviewID VARCHAR(50), ProviderName VARCHAR(250), SiteIDNo VARCHAR(20), 

                        ReviewDate VARCHAR(12), UserName VARCHAR(250), VisitPurpose VARCHAR(250), 

                        NoOfPhysicians INT, NoOfRecords INT, RECORD_ID INT IDENTITY(1,1)

                    )

INSERT INTO #REVIEWS(ReviewID, ProviderName, SiteIDNo, ReviewDate, UserName, VisitPurpose, NoOfPhysicians, NoOfRecords)

SELECT ReviewID, ProviderName, SiteIDNo, ReviewDate, UserFirstName + ' ' + UserLastName AS UserName, VisitPurpose, NoOfPhysicians, NoOfRecords 

FROM KHSSQLODSPRD.QI_SITE_REVIEW.dbo.RecordHeader 

WHERE ExportDate BETWEEN @START_DATE AND @END_DATE

ORDER BY ReviewDate





SET @RECORD_ID = 1

SET @MAX_RECORD_ID = (SELECT ISNULL(MAX(RECORD_ID), 0) FROM #REVIEWS)





    WHILE @RECORD_ID <= @MAX_RECORD_ID 

    BEGIN



        SELECT @REVIEW_ID = ReviewID, 

                @SUBJECT = 'Review - ' + ProviderName, 

                @COMMENT = CAST(@RECORD_ID AS VARCHAR(10)) + ' of ' + CAST(@MAX_RECORD_ID AS VARCHAR(10)) 

                            + ' Review Surveys for the week ending ' + RTRIM(CONVERT(VARCHAR(12), @END_DATE, 110)) + '.' 

                            + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + ProviderName + ' [' + SiteIDNo + '] - Reviewed ' + RTRIM(CONVERT(VARCHAR(12), ReviewDate, 110)) 

                            + '. Review ID ' + ReviewID  

                            + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + VisitPurpose + ' visit for ' + CAST(CAST(NoOfPhysicians AS INT) AS VARCHAR(20)) + ' physicians with ' 

                            +  CAST(CAST(NoOfRecords AS INT) AS VARCHAR(20)) + ' records by ' + UserName + '.'

        FROM #REVIEWS 

        WHERE RECORD_ID = @RECORD_ID





        SET @REVIEW_ID = LOWER(@REVIEW_ID)  



        --SET EXTENSION OPTIONS

        SET @EXTENSION_SETTINGS.modify('replace value of (/ParameterValues/ParameterValue[Name="Subject"]/Value/text())[1] with sql:variable("@SUBJECT")');

        SET @EXTENSION_SETTINGS.modify('replace value of (/ParameterValues/ParameterValue[Name="Comment"]/Value/text())[1] with sql:variable("@COMMENT")');



        --SET REPORT PARAMETERS

        SET @PARAMETERS.modify('replace value of (/ParameterValues/ParameterValue[Name="SITE_ID"]/Value/text())[1] with sql:variable("@REVIEW_ID")');





        --UPDATE SUBSCRIPTION PARAMETERS 

        UPDATE dbo.Subscriptions

        SET ExtensionSettings = CAST(@EXTENSION_SETTINGS AS VARCHAR(8000)), 

            [Parameters]      = CAST(@PARAMETERS AS VARCHAR(8000))

        WHERE SubscriptionID = @SUBSCRIPTION_ID





        EXEC dbo.AddEvent @EventType = 'TimedSubscription', @EventData = @SUBSCRIPTION_ID;





        --WAIT WHILE REPORT GENERATES 

        WAITFOR DELAY '00:00:03.000';



        --IF REPORT IS STILL EXECUTING, WAIT SOME MORE

        WHILE EXISTS (SELECT TOP 1 'X' FROM dbo.Event WHERE EventData = @SUBSCRIPTION_ID ) OR EXISTS (SELECT TOP 1 'X' FROM dbo.Notifications WHERE SubscriptionID = @SUBSCRIPTION_ID)

                        BEGIN

                            WAITFOR DELAY '00:00:01.000';

                            PRINT 'Waiting for subscription to finish'

                        END





        SET @RECORD_ID = @RECORD_ID + 1



    END  --END OF LOOP 



--      SET @FLAG = 1

You'd add the TO address the same way as the Comment is updated. The @Review_ID is passed to the report as a parameter.