1

I've created a report which shows all active Subscriptions on our Report Server and have currently got a table showing all the reports by name and I've also included 3 columns, one which shows you the code to Enable the subscription, one which shows you code to Disable the subscription and the last one shows code to Run the subscription immediately.

I've had a look at making these columns buttons/clickable text so a user can just hit the button/text and are able to Enable/Disable/Run subscriptions so it's not a single point of failure with myself. I've not been able to find anything yet which allows me to do this.

Does anyone know if this is possible and could provide some guidance on how this is/could be done?

Here's the SQL for the report:

    SELECT
    cat.Name,
    cat.Path,
    sub.Description,
    sch.ScheduleID AS AgentJobID,
    sch.LastRunTime,
       CASE sch.RecurrenceType
       WHEN 1 THEN 'Once'
       WHEN 2 THEN 'Hourly'
       WHEN 4 THEN 'Daily/Weekly'
       WHEN 5 THEN 'Monthly'
       END AS ScheduleFrequency,
    'EXEC msdb.dbo.sp_start_job N''' + CAST(sch.ScheduleID as nvarchar(36)) + ''';' AS StartJob,
    'EXEC msdb.dbo.sp_update_job @job_name = N''' + CAST(sch.ScheduleID as nvarchar(36)) + ''', @enabled = 1 ;' AS EnableJob,
    'EXEC msdb.dbo.sp_update_job @job_name = N''' + CAST(sch.ScheduleID as nvarchar(36)) + ''', @enabled = 0 ;' AS DisableJob
FROM
    ReportServer.dbo.Schedule sch
INNER JOIN
    ReportServer.dbo.ReportSchedule rsch
ON sch.ScheduleID = rsch.ScheduleID
INNER JOIN
    ReportServer.dbo.Catalog cat
ON rsch.ReportID = cat.ItemID
INNER JOIN
    ReportServer.dbo.Subscriptions sub
ON rsch.SubscriptionID = sub.SubscriptionID

Example report in it's current form Example report in current form

Alex
  • 21
  • 4
  • What is the business case for wanting these elements on a report, and why would you want someone to enable/disable the reports in this way? – A.Steer Jun 01 '22 at 13:12
  • If our data warehouse processes fail before the subscriptions are ran, we need the ability to disable the subscriptions before they're ran otherwise data is not up to date. I'm currently the only person in the department who can do this so I'm the single point of failure at the moment. – Alex Jun 01 '22 at 14:24
  • 1
    Would it be possible to run the subscriptions as data driven, so they don't run if data isn't available? I'm just wondering if there is another way of going about this? – A.Steer Jun 01 '22 at 17:34

1 Answers1

1

I am currently attempting to do something similar myself, I hope this may help you in some way.

Currently the only workaround I have managed to achieve something along these lines is to have an image or other object with an "onclick" event that actions Go To Report. I then have a dataset in this SubReport that executes the Stored Procedure using paramaters passed into it from the parent report as required. Some images below may help clarify how I did it:

Main Report Sub Report that Fires Stored Procedure

Unfortunately, it can be a bit unwieldy since it opens up a sub report that the user has to navigate back out of if they need to action multiple results.

Iyla
  • 41
  • 5
  • 1
    Thank you for this Lyla! Fortunately, our users are pretty good with being able to navigate reports so probably wouldn't be too much of an issue in this case. When I have some capacity I'll definitely look into doing this - great workaround! – Alex Jan 11 '23 at 11:37