February 17, 2011 at 7:46 am
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
February 18, 2011 at 7:32 am
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.
March 2, 2011 at 1:13 am
2008 R2 has an option to execute the data-drive subscription, "When the report data is updated on the report server".
March 2, 2011 at 2:57 am
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.
June 18, 2013 at 10:02 am
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.
June 18, 2013 at 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.
April 3, 2017 at 3:24 pm
tech.groupchat - Tuesday, June 18, 2013 10:06 AMI 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
April 4, 2017 at 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;
April 4, 2017 at 5:03 pm
christian_t - Tuesday, April 4, 2017 1:52 AMHi 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]
GOCREATE 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!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply