Sending email from SSRS repor 2008
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.
email reporting-services ssrs-2008 send
add a comment |
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.
email reporting-services ssrs-2008 send
add a comment |
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.
email reporting-services ssrs-2008 send
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.
email reporting-services ssrs-2008 send
email reporting-services ssrs-2008 send
asked Nov 14 '18 at 14:30
sparcsparc
164
164
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
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.
Thanks a lot Hannover Fist for your response. I am using 2008 standard edition. I don't see the same things in properties as shown above. I tried giving the query in step3 that has email ids in parameter, but it is giving error the operation you are attempting on item is not allowed for its type. Sorry, I am using this feature for the first time.
– sparc
Nov 15 '18 at 15:36
Still getting the same error. Can you please help. Thanks.
– sparc
Nov 15 '18 at 20:50
The Standard Edition won't have the Data Driven Subscription option so you won't have the ability to set them using a query. The one I showed was 2016 so it would be different from your 2008 Report Manager. Are you trying to assign the TO value to use your e-mail address?SET @EXTENSION_SETTINGS.modify('replace value of (/ParameterValues/ParameterValue[Name="TO"]/Value/text())[1] with sql:variable("@emailaddress")');
– Hannover Fist
Nov 15 '18 at 21:23
Where should I give the above code? and will it work in SSRS 2008 standard edition if I give the above code? Sorry for asking questions as I am a novice and haven't used this feature.before..Thanks for your time.
– sparc
Nov 16 '18 at 9:19
Sorry, just realised that I am using enterprise edition. Can you please tell me where do I use the above code in enterprise edition> Thanks
– sparc
Nov 16 '18 at 13:23
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53302565%2fsending-email-from-ssrs-repor-2008%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
Thanks a lot Hannover Fist for your response. I am using 2008 standard edition. I don't see the same things in properties as shown above. I tried giving the query in step3 that has email ids in parameter, but it is giving error the operation you are attempting on item is not allowed for its type. Sorry, I am using this feature for the first time.
– sparc
Nov 15 '18 at 15:36
Still getting the same error. Can you please help. Thanks.
– sparc
Nov 15 '18 at 20:50
The Standard Edition won't have the Data Driven Subscription option so you won't have the ability to set them using a query. The one I showed was 2016 so it would be different from your 2008 Report Manager. Are you trying to assign the TO value to use your e-mail address?SET @EXTENSION_SETTINGS.modify('replace value of (/ParameterValues/ParameterValue[Name="TO"]/Value/text())[1] with sql:variable("@emailaddress")');
– Hannover Fist
Nov 15 '18 at 21:23
Where should I give the above code? and will it work in SSRS 2008 standard edition if I give the above code? Sorry for asking questions as I am a novice and haven't used this feature.before..Thanks for your time.
– sparc
Nov 16 '18 at 9:19
Sorry, just realised that I am using enterprise edition. Can you please tell me where do I use the above code in enterprise edition> Thanks
– sparc
Nov 16 '18 at 13:23
add a comment |
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.
Thanks a lot Hannover Fist for your response. I am using 2008 standard edition. I don't see the same things in properties as shown above. I tried giving the query in step3 that has email ids in parameter, but it is giving error the operation you are attempting on item is not allowed for its type. Sorry, I am using this feature for the first time.
– sparc
Nov 15 '18 at 15:36
Still getting the same error. Can you please help. Thanks.
– sparc
Nov 15 '18 at 20:50
The Standard Edition won't have the Data Driven Subscription option so you won't have the ability to set them using a query. The one I showed was 2016 so it would be different from your 2008 Report Manager. Are you trying to assign the TO value to use your e-mail address?SET @EXTENSION_SETTINGS.modify('replace value of (/ParameterValues/ParameterValue[Name="TO"]/Value/text())[1] with sql:variable("@emailaddress")');
– Hannover Fist
Nov 15 '18 at 21:23
Where should I give the above code? and will it work in SSRS 2008 standard edition if I give the above code? Sorry for asking questions as I am a novice and haven't used this feature.before..Thanks for your time.
– sparc
Nov 16 '18 at 9:19
Sorry, just realised that I am using enterprise edition. Can you please tell me where do I use the above code in enterprise edition> Thanks
– sparc
Nov 16 '18 at 13:23
add a comment |
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.
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.
answered Nov 14 '18 at 21:29
Hannover FistHannover Fist
5,7901825
5,7901825
Thanks a lot Hannover Fist for your response. I am using 2008 standard edition. I don't see the same things in properties as shown above. I tried giving the query in step3 that has email ids in parameter, but it is giving error the operation you are attempting on item is not allowed for its type. Sorry, I am using this feature for the first time.
– sparc
Nov 15 '18 at 15:36
Still getting the same error. Can you please help. Thanks.
– sparc
Nov 15 '18 at 20:50
The Standard Edition won't have the Data Driven Subscription option so you won't have the ability to set them using a query. The one I showed was 2016 so it would be different from your 2008 Report Manager. Are you trying to assign the TO value to use your e-mail address?SET @EXTENSION_SETTINGS.modify('replace value of (/ParameterValues/ParameterValue[Name="TO"]/Value/text())[1] with sql:variable("@emailaddress")');
– Hannover Fist
Nov 15 '18 at 21:23
Where should I give the above code? and will it work in SSRS 2008 standard edition if I give the above code? Sorry for asking questions as I am a novice and haven't used this feature.before..Thanks for your time.
– sparc
Nov 16 '18 at 9:19
Sorry, just realised that I am using enterprise edition. Can you please tell me where do I use the above code in enterprise edition> Thanks
– sparc
Nov 16 '18 at 13:23
add a comment |
Thanks a lot Hannover Fist for your response. I am using 2008 standard edition. I don't see the same things in properties as shown above. I tried giving the query in step3 that has email ids in parameter, but it is giving error the operation you are attempting on item is not allowed for its type. Sorry, I am using this feature for the first time.
– sparc
Nov 15 '18 at 15:36
Still getting the same error. Can you please help. Thanks.
– sparc
Nov 15 '18 at 20:50
The Standard Edition won't have the Data Driven Subscription option so you won't have the ability to set them using a query. The one I showed was 2016 so it would be different from your 2008 Report Manager. Are you trying to assign the TO value to use your e-mail address?SET @EXTENSION_SETTINGS.modify('replace value of (/ParameterValues/ParameterValue[Name="TO"]/Value/text())[1] with sql:variable("@emailaddress")');
– Hannover Fist
Nov 15 '18 at 21:23
Where should I give the above code? and will it work in SSRS 2008 standard edition if I give the above code? Sorry for asking questions as I am a novice and haven't used this feature.before..Thanks for your time.
– sparc
Nov 16 '18 at 9:19
Sorry, just realised that I am using enterprise edition. Can you please tell me where do I use the above code in enterprise edition> Thanks
– sparc
Nov 16 '18 at 13:23
Thanks a lot Hannover Fist for your response. I am using 2008 standard edition. I don't see the same things in properties as shown above. I tried giving the query in step3 that has email ids in parameter, but it is giving error the operation you are attempting on item is not allowed for its type. Sorry, I am using this feature for the first time.
– sparc
Nov 15 '18 at 15:36
Thanks a lot Hannover Fist for your response. I am using 2008 standard edition. I don't see the same things in properties as shown above. I tried giving the query in step3 that has email ids in parameter, but it is giving error the operation you are attempting on item is not allowed for its type. Sorry, I am using this feature for the first time.
– sparc
Nov 15 '18 at 15:36
Still getting the same error. Can you please help. Thanks.
– sparc
Nov 15 '18 at 20:50
Still getting the same error. Can you please help. Thanks.
– sparc
Nov 15 '18 at 20:50
The Standard Edition won't have the Data Driven Subscription option so you won't have the ability to set them using a query. The one I showed was 2016 so it would be different from your 2008 Report Manager. Are you trying to assign the TO value to use your e-mail address?
SET @EXTENSION_SETTINGS.modify('replace value of (/ParameterValues/ParameterValue[Name="TO"]/Value/text())[1] with sql:variable("@emailaddress")');
– Hannover Fist
Nov 15 '18 at 21:23
The Standard Edition won't have the Data Driven Subscription option so you won't have the ability to set them using a query. The one I showed was 2016 so it would be different from your 2008 Report Manager. Are you trying to assign the TO value to use your e-mail address?
SET @EXTENSION_SETTINGS.modify('replace value of (/ParameterValues/ParameterValue[Name="TO"]/Value/text())[1] with sql:variable("@emailaddress")');
– Hannover Fist
Nov 15 '18 at 21:23
Where should I give the above code? and will it work in SSRS 2008 standard edition if I give the above code? Sorry for asking questions as I am a novice and haven't used this feature.before..Thanks for your time.
– sparc
Nov 16 '18 at 9:19
Where should I give the above code? and will it work in SSRS 2008 standard edition if I give the above code? Sorry for asking questions as I am a novice and haven't used this feature.before..Thanks for your time.
– sparc
Nov 16 '18 at 9:19
Sorry, just realised that I am using enterprise edition. Can you please tell me where do I use the above code in enterprise edition> Thanks
– sparc
Nov 16 '18 at 13:23
Sorry, just realised that I am using enterprise edition. Can you please tell me where do I use the above code in enterprise edition> Thanks
– sparc
Nov 16 '18 at 13:23
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53302565%2fsending-email-from-ssrs-repor-2008%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown