CODEWITHSUNDEEP
reporting-servicesssrs-2008ssrs-2012reportingservices-2005
Alex
Alex

Reputation: 31

Execute Stored Procedure Code using a button in SSRS

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

Upvotes: 2

Views: 1162

Answers (1)

Iyla
Iyla

Reputation: 51

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.

Upvotes: 2

Related Questions