SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSRS How to trigger subscription when data is ready


SSRS How to trigger subscription when data is ready

Author
Message
christian_t
christian_t
SSC Veteran
SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)

Group: General Forum Members
Points: 265 Visits: 530
hi guys,

I have a few Reports that are subscripte bei a normal subscription meaning having a fixed time where it will be executed.
However it happens, that the data for those reports is not ready yet.

I thought this problem will be solved by a data driven subscription but there you just can get the "parameters" (e.g. TO, CC, Subject, Report-Parameters) set. There seems no way to say, that there should be an check up on if the data is ready (e.g. select table for success) and then delay 5 minutes to check again.

How did you fix this issue?

Mitch
Daniel Bowlin
Daniel Bowlin
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4226 Visits: 2629
You could take a couple approaches.

You could disable the agent job that runs the subscription. Then put some code at the end of your data load that executes the agent job using sp_start_job.

Or, you could build some sort of log table that your data load writes to when it is complete, then use the data driven subscription to test for certain conditions in the log before executing the report.
tnk
tnk
SSC Veteran
SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)

Group: General Forum Members
Points: 264 Visits: 443
2008 R2 has an option to execute the data-drive subscription, "When the report data is updated on the report server".
christian_t
christian_t
SSC Veteran
SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)

Group: General Forum Members
Points: 265 Visits: 530
Thx guys.
As I am looking for a more native way to handle this, so non so technical people can configure those Subscription I will wait for the R2, which I will get in the next few weeks and will look in to the new data-driven execution.
tech.groupchat
tech.groupchat
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 9
Did you get any answer how to execute/trigger report when data is ready? I am looking for same thing. I am using R2 but do not know exact steps how to achive this.
tech.groupchat
tech.groupchat
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 9
I used data driven subscription where report executes and distirbutes single/multple times and delivered to by email.

Could you please tell me steps where I can set something where subscription will check if data is ready every 5 mins and if yes then execute report for once and stop running second time. We have early morning data load where I like to trigger report execution as soon as data ready.
vinoth08-1062266
vinoth08-1062266
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 46
tech.groupchat - Tuesday, June 18, 2013 10:06 AM
I used data driven subscription where report executes and distirbutes single/multple times and delivered to by email.Could you please tell me steps where I can set something where subscription will check if data is ready every 5 mins and if yes then execute report for once and stop running second time. We have early morning data load where I like to trigger report execution as soon as data ready.

Hi,
did you find anything for every 5 mins check?
if so can you pl share the details

christian_t
christian_t
SSC Veteran
SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)

Group: General Forum Members
Points: 265 Visits: 530
Hi Vinoth,

what I did is, to create a dedicated schedule that ended in the past e.g. 01.01.2017. For every subscription that I wanted to trigger when the data is ready I used that schedule.
Once the ETL that delivered the data for the report is ready I execute the procedure to fire the subscription.

Procedure in ReportServer-DB

USE [ReportServer]
GO

CREATE PROCEDURE [dbo].[usp_report_event_trigger]
@report_path nvarchar(425) = N'',
@report_action int = -1
/* ReportAction EventType
3 CacheInvalidateSchedule
4 TimedSubscription
5 RefreshCache
*/
AS
BEGIN
SET NOCOUNT ON;
/* Schedule for Dummy_Data_Driven_Abo */
DECLARE @schedule_id uniqueidentifier = '308D512B-1E97-4EF6-BC9C-493AF0520267';
DECLARE @exec_command nvarchar(MAX) = N'';

SELECT
@exec_command =
@exec_command + N'EXECUTE ReportServer.dbo.AddEvent @EventType=''' +
CASE
WHEN rs.ReportAction = 4 THEN 'TimedSubscription'
ELSE sc.EventType
END +
''', @EventData='''+CONVERT(nvarchar(36), UPPER(rs.SubscriptionID))+N'''; '
FROM dbo.Catalog AS c
INNER JOIN dbo.ReportSchedule AS rs ON (c.ItemID = rs.ReportID AND rs.SubscriptionID IS NOT NULL)
INNER JOIN dbo.Schedule AS sc ON (rs.ScheduleID = sc.ScheduleID)
WHERE c.Path = @report_path
AND (rs.ReportAction = @report_action OR @report_action = -1)
/* if chachinvalidateSchedule or RefreshCache OR TimedSubscription with my dedicated Schedule */
AND (@report_action != 4 OR rs.ScheduleID = @schedule_id)

EXECUTE sp_executesql @exec_command;

END



Fire the subscription

EXECUTE ReportServer.dbo.usp_report_event_trigger
@report_path = N'/Report_Folder/Reportname',
@report_action = 4;


vinoth08-1062266
vinoth08-1062266
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 46
christian_t - Tuesday, April 4, 2017 1:52 AM
Hi Vinoth,

what I did is, to create a dedicated schedule that ended in the past e.g. 01.01.2017. For every subscription that I wanted to trigger when the data is ready I used that schedule.
Once the ETL that delivered the data for the report is ready I execute the procedure to fire the subscription.

Procedure in ReportServer-DB

USE [ReportServer]
GO

CREATE PROCEDURE [dbo].[usp_report_event_trigger]
@report_path nvarchar(425) = N'',
@report_action int = -1
/* ReportAction EventType
3 CacheInvalidateSchedule
4 TimedSubscription
5 RefreshCache
*/
AS
BEGIN
SET NOCOUNT ON;
/* Schedule for Dummy_Data_Driven_Abo */
DECLARE @schedule_id uniqueidentifier = '308D512B-1E97-4EF6-BC9C-493AF0520267';
DECLARE @exec_command nvarchar(MAX) = N'';

SELECT
@exec_command =
@exec_command + N'EXECUTE ReportServer.dbo.AddEvent @EventType=''' +
CASE
WHEN rs.ReportAction = 4 THEN 'TimedSubscription'
ELSE sc.EventType
END +
''', @EventData='''+CONVERT(nvarchar(36), UPPER(rs.SubscriptionID))+N'''; '
FROM dbo.Catalog AS c
INNER JOIN dbo.ReportSchedule AS rs ON (c.ItemID = rs.ReportID AND rs.SubscriptionID IS NOT NULL)
INNER JOIN dbo.Schedule AS sc ON (rs.ScheduleID = sc.ScheduleID)
WHERE c.Path = @report_path
AND (rs.ReportAction = @report_action OR @report_action = -1)
/* if chachinvalidateSchedule or RefreshCache OR TimedSubscription with my dedicated Schedule */
AND (@report_action != 4 OR rs.ScheduleID = @schedule_id)

EXECUTE sp_executesql @exec_command;

END



Fire the subscription

EXECUTE ReportServer.dbo.usp_report_event_trigger
@report_path = N'/Report_Folder/Reportname',
@report_action = 4;


Thank you so much christian.
I want to discuss more on this, can you send an email to vinoth08.rvi@gmail.com.
Thanks!

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search