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:
- Maintenance and deletion of jobs
- Creation, deletion, and maintenance of job schedules
- Setup, deletion, and maintenance of regular roles
- 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. 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.
The object explorer, as illustrated below, is displayed with three main Folders:
- Jobs
- Security-with Roles and System Roles sub folders
- Shared Schedules.
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:
- On-demand report run by a user
- Manual creation of a report snapshot
- Manual creation of a report history snapshot
- Currently running standard subscription
- 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.
If you want to cancel a job, just right mouse click on the job name in the objective explorer and select “Cancel Job(s)”.
The cancel job window appears as shown below. Simply clicking the Cancel button aborts the running job without warning.
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.
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.
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….
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.
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.
On the role properties screen, illustrated below, tasks can be added or removed via checking or unchecking individual tasks and then clicking ok.
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.
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.
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.
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.
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.
Deleting a schedule is again very easy, as shown below. And again, take care in using the delete option.
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.
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
- TechNet, Connecting to a Report Server in Management Studio: http://technet.microsoft.com/en-us/library/ms159797.aspx
- More Reporting Services Tips
