Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Manage SQL Server Reporting Services in Management Studio


By:   |   Read Comments (4)   |   Related Tips: > Reporting Services Administration

Attend these FREE MSSQLTips webcasts >> click to register


Problem

I hear I can manage SQL Server Reporting Services in Management Studio? Is that true?

Solution

Most SSRS report developers reactions to our problem would be two fold. First, they would ask, "You can maintain SSRS in Management Studio?" Next, if they knew you could connect to SSRS in Management studio, the next question would be, "Why would you do that?".

These questions are both valid, especially the second one. Microsoft does not advertise the SSRS Management Studio "connection".  Most maintenance for SSRS is performed either in Report Manager online or within the Report Services Configuration Manager from the desktop. The SSRS Management Studio connection is certainly no replacement for either of these tools, especially the Reporting Services Configuration Manager. However, several benefits can be obtained using SSMS. First, you are able to connect to the SSRS even if you are having browser issues; second, it is often quicker to just switch from using Management Studio for the Database Engine.

The Management Studio SSRS Object Explorer allows for the following tasks:

  1.  Maintenance and deletion of jobs
  2.  Creation, deletion, and maintenance of job schedules
  3.  Setup, deletion, and maintenance of regular roles
  4.  Setup, deletion, and maintenance of system roles

One big caveat with the SSMS interface stems from the fact that the SSRS Management Studio interface does not allow a report developer to add, delete, or maintain the actual reports on the report server. For instance, in order to delete a report, the online Report Manager must be used. Similarly, folder name changes must also be performed in report manager online.

Connecting to SQL Server Reporting Services (SSRS) using Management Studio

We will use sample reports from the AdventureWorks data warehouse database; this database along with many other samples databases and data warehouses can be downloaded from CodePlex at: http://msftdbprodsamples.codeplex.com/releases/view/55330. In our examples we use some of the sample data and reports that coincide with the data in the AdventureWorks database. Connecting to the Report Server in SSMS is just like connecting to the Database Engine or Analysis Services.  As displayed below, first Click Connect and then select "Reporting Services".  Fill in the server name and instance (if using a named instance) and the authentication method (Windows Authentication is most common). Last click Connect. 

Connection

The object explorer, as illustrated below, is displayed with three main Folders:

  1. Jobs
  2. Security-with Roles and System Roles sub folders
  3. Shared Schedules.
Object Explorer

SQL Server Reporting Services Job Maintenance Options

The Jobs folder allows for the review and canceling of current running jobs.  A job in SSRS is any of the following:

  1. On-demand report run by a user
  2. Manual creation of a report snapshot
  3. Manual creation of a report history snapshot
  4. Currently running standard subscription
  5. System jobs started by the report server

As shown below, double clicking on a particular job displays the job's properties including: the status, the type of job, how the job is being displayed, the report name, the server, the user name, and the start time.

Running Job

If you want to cancel a job, just right mouse click on the job name in the objective explorer and select "Cancel Job(s)".

cancel job click

The cancel job window appears as shown below. Simply clicking the Cancel button aborts the running job without warning.

cancel job

If a report is being run by a user interactively, the user will get the following message on the Report Server website. The message is a bit misleading as the user did not cancel the job, but an administrator did.  

aborted job

This functionality is most often used when a SSRS job becomes unyielding and needs to be canceled.  

SQL Server Reporting Services Security Roles and System Role Maintenance

The security Role Maintenance area serves two similar purposes: the creation, maintenance, and deletion of regular roles and the creation, maintenance and deletion of system roles. Upon installation of SSRS, 5 regular roles (Browser, Content Manager, My Reports, Publisher, and Report Builder) and 2 system roles (System Administrator and System User) are created as noted below. Each of these roles are assigned a predefined set of permissions or tasks.  

object explorer

In order to create either a New Role or New System Role, right click on the respective folder and then select New Role... or New System Role....

New Role

New System Role

Next for either role, fill in a name, a description (optional but helpful), and then select the appropriate task(s), and finally click OK. For details on each task, see:  http://technet.microsoft.com/en-us/library/ms160344.aspx for regular tasks or http://technet.microsoft.com/en-us/library/ms157353.aspx for system tasks.

New role

New System Role

In a similar fashion either role type can be maintained by right clicking on the role to be changed and then selecting properties or by double clicking on a role, as displayed below.

role properties

On the role properties screen, illustrated below, tasks can be added or removed via checking or unchecking individual tasks and then clicking ok.

role properties detail


Last, roles can easily be deleted by first right clicking on the role to be deleted and then select Delete as shown below. The confirmation Delete Catalog Items screen appears; once you click OK, the role is deleted without further warning. Please exercise care when deleting as you could lock yourself out of a folder or report server.

delete row

delete role confirm


SQL Server Reporting Services Subscription Shared Schedules

The last major item that can be updated in Management Studio are shared schedules; these schedules are generally used by subscriptions to run reports at specified times on a set schedules. To create a new schedule, right click on Shared Schedules and then click New Schedule.

New Schedule

For a new schedule, as outlined below, fill in: a name, a start date, an end date (optional), and a recurrence pattern. Based on the recurrence pattern type selected, different scenario and selection box will appear in the square area.  For instance, if you select hourly, the hours and minutes interval will show (as noted below). However if you select week, the days of the week and the repeat after this number of weeks selection will appear. When the setup is complete, click OK.

New Shared Schedule

Maintaining a schedule can be done by right clicking the desired schedule and selecting properties (or double clicking on it). Similar to the new schedule screen, customized schedules are defined by naming the scheduling and selecting a recurrence pattern.

schedule properties

schedule properties detail

Clicking on the Reports option in the upper left corner of the properties page provides a handy way to see all the reports which are assigned to the selected schedule.

Reports belonging to schedule

Deleting a schedule is again very easy, as shown below. And again, take care in using the delete option.

schedule delete

Once a shared schedule is deleted, we are fortunate that SSRS leaves the same schedule "intact" within the report itself by using the same schedule with the exact same time and day pattern. You can see in the screen print below how the schedule was converted from a shared schedule to a schedule embedded in the report.

embedded schedule

Conclusion

Unknown to many SSRS administrators, Management Studio can be used to maintain several specific functions in SSRS. These functions includes: 1)  role and system role creation, adjustment, and removal, 2) shared schedule creation, maintenance, deletion, and 3) job review and cancellation. Using Management Studio provides an easy method to perform these task without having to go to the Report Manager website; however, certain tasks such as content maintenance and additions can only performed in Report Manager on the web.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Sunday, May 01, 2016 - 8:59:33 AM - scott Back To Top

Unfortunately as mentioned in the tip you cannot add / delete reports within SSMS.


Friday, April 29, 2016 - 7:00:48 PM - Anil Back To Top

Hello Scott,
Is there any way I can delete the contents in Report Manager using T-Sql ? All I want is to avoid opening up the report manager URL and then delete the folders/reports etc. manually. If there is way to delete folders and theirs contents in report manager uisng script, that would be awesome.

Thanks a lot

 


Friday, August 08, 2014 - 11:19:48 AM - William Clardy Back To Top

One minor correction: Report Builder serves as a decent desktop alternative to the Report Manager web UI for managing reports on an SSRS instance.


Friday, September 13, 2013 - 11:56:14 AM - Jeremy Kadlec Back To Top

Scott,

Congrats on your 25th tip!  On behalf of the global SQL Server community, thank you for all of your contributions.

Thank you,
Jeremy Kadlec
MSSQLTIps.com Community Co-Leader


Learn more about SQL Server tools