Elon's Blog

Stuff that may be of use to someone…

Archive for the ‘SSRS’ Category

SQL Server Reporting Services

Enable or Disable a SSRS Report Subscription based upon another task or event.

Posted by Elon B. on July 31, 2010

Scenario

Whilst at a client site, I was asked whether you could stop (disable) a users Report Subscription based upon an external event, such as another SQL Agent Job or Task outcome, such as failing.

The approach I took to demonstrate how you could achieve this was to create another SQL Agent Job which “enabled” the users Report Subscription Job, based upon the occurrence of a “flag” record in another table. This table could easily be created by the calling control job, or a prior job step.

Steps

Here we can see a Report Subscription that has been created by a user: (click on any image for full-size view)

Subscription Details:

The job is set to run at 19:12.

Using SSMS (SQL Server Management Studio) and connecting to the Database that the “ReportServer” database resides on,  we can use the “Job Activity Monitor” to see the Job that was automatically created for this subcription.

Job Name: A48C0610-4A9F-4847-97E2-BAF7798DEE0C

We can write a SQL Script to view the Job Details:

USE msdb

SELECT *
FROM msdb.dbo.sysjobs
WHERE name = ‘A48C0610-4A9F-4847-97E2-BAF7798DEE0C’

To view the Details of all the Jobs on the Server we can use the following script:

USE msdb

GO

SELECT Getdate() AS ‘ExecutionTime’

GO

SELECT @@SERVERNAME AS ‘SQLServerInstance’

GO

SELECT j.[name] AS ‘JobName’,
enabled = CASE
WHEN j.enabled = 0 THEN ‘No’
ELSE ‘Yes’
END,
l.[name] AS ‘OwnerName’
FROM msdb.dbo.sysjobs j
INNER JOIN MASTER.dbo.syslogins l
ON j.owner_sid = l.sid
ORDER BY j.[name]

GO

Script to Enable/Disable Job

/* script to disable by job */
EXEC msdb.dbo.Sp_update_job
@job_name = ‘A48C0610-4A9F-4847-97E2-BAF7798DEE0C’,
@enabled = 0; — 0 to disable 1 to enable
GO

Therefore, we could setup another SQL Job, which will run this script dependant upon some logical condition. i.e. where a certain value or number of records exists in a table, etc.

To enable/disable the job based upon say records, in a table, adapt the following script accordingly to your requirements. I have used a temporary table to demonstrate the principle.

 /* script to enable(1) or disable(0) by job based upon records in table @flagtable */ 
USE msdb 
 
DECLARE @flagtable TABLE ( 
 flag INT) 
DECLARE @DoIt INT 
 
SET @DoIt = 0 
 
-- let?s add a record to this table for test purposed. To Disable the Job we would comment out the following two rows 
INSERT INTO @flagtable 
 (flag) 
VALUES (1) 
 
-- so lets say we will enable the job if a record exists in the @flagtable 
SELECT @DoIt = COUNT(*) 
FROM @flagtable 
 
/* script to enable(1) or disable(0) by job based upon records in set @flagtable */ 
EXEC msdb.dbo.Sp_update_job 
 @job_name = 'A48C0610-4A9F-4847-97E2-BAF7798DEE0C', 
 @enabled = @DoIt; 
 
GO 

Script to DISABLE the SQL Agent Job as @DoIt = 0

Script to ENABLE the SQL Agent Job as @DoIt = 1

So, to automate this task we can set up a new SQL Agent Job to run this script as shown;

Create the following Step, and add the T-SQL to enable or disable the Job as required.

Hence, if  more than 1 record exists in our “flag” table, it will ENABLE the Report subscription Job.

If no records exist in the “Flag” table, the Report Subscription Job will be set to Enabled = No (i.e. Disabled).

Remember, you will use your own logic to set the @DoIt parameter based upon your own requirements.

You can run this Job Manually or on a Scheduled as required:

Conclusion

Though I applied this to a Report Subscription Job, it could easily be adapted for other types of Jobs.

The beauty of it is that the original Job and its associated schedule timings remain unaltered. All we are doing is disabling/enabling the Job as necessary.

Posted in SQL Server, SSRS, T-SQL | 2 Comments »

Adding a Back Button to a SSRS Drill through Report.

Posted by Elon B. on July 31, 2010

Scenario

You have a Reporting Services report viewed via a browser that has a drill-through to another report. When the drill through report is opened, you would like the user to be able to “jump back” to the previous parent report page. Users can use the browsers back button to do this, but you would like to place a more obvious link on the Report.

Solution

The following report was created using BIDS for SQL Server 2008. The same method can be employed for the 2005 version, though the dialog boxes may appear slightly different.

Open your drill- through report (i.e. the report you will be drilling to, from another ssrs report) and add a text box, which you can format to look like a button, if you wish.

After Adding the Textbox, View its Properties (right-click on textbox).

In the Textbox Properties Dialog box, Select Action, and check “Go to URL

Enter   javascript:history.back(1) in the entry box, then press OK.

When you deploy the report, and view it in your browser, the user only has to click on the “button” (Go Back)  to be returned to the previous browser page, which would be the parent report.

Posted in SQL Server, SSRS | 13 Comments »

 
%d bloggers like this: