Does Sql Server Managmenet Studio Come With Sql Server Reporting Services

By: | Updated: 2013-09-thirteen | Comments (four) | Related: > Reporting Services Administration

Trouble

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

Solution

Nigh SSRS written report developers reactions to our problem would be two fold. First, they would enquire, "You can maintain SSRS in Management Studio?" Next, if they knew you could connect to SSRS in Management studio, the side by side question would be, "Why would you practise that?".

These questions are both valid, especially the second one. Microsoft does not advertise the SSRS Management Studio "connection". Almost maintenance for SSRS is performed either in Report Manager online or inside 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. Withal, several benefits can be obtained using SSMS. First, you are able to connect to the SSRS even if you are having browser issues; second, information technology is often quicker to just switch from using Direction Studio for the Database Engine.

The Direction Studio SSRS Object Explorer allows for the post-obit tasks:

Maintenance and deletion of jobs
Creation, deletion, and maintenance of chore 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 let a study programmer to add together, delete, or maintain the bodily reports on the report server. For instance, in order to delete a report, the online Study Manager must be used. Similarly, folder proper name changes must also be performed in study manager online.

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

Nosotros will use sample reports from the AdventureWorks information warehouse database; this database along with many other samples databases and information 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 beneath, first Click Connect and then select "Reporting Services". Fill in the server proper noun and example (if using a named instance) and the hallmark method (Windows Authentication is nearly common). Concluding click Connect.

Connection

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

Jobs
Security-with Roles and Arrangement Roles sub folders
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 whatever of the post-obit:

On-demand report run by a user
Manual creation of a report snapshot
Transmission creation of a report history snapshot
Currently running standard subscription
Organisation jobs started by the report server

As shown below, double clicking on a particular task displays the job'southward properties including: the status, the type of job, how the job is existence displayed, the report name, the server, the user name, and the first fourth dimension.

Running Job

If you want to abolish a job, just right mouse click on the job proper name in the objective explorer and select "Cancel Chore(south)".

cancel job click

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

cancel job

If a report is being run by a user interactively, the user will become the post-obit bulletin on the Report Server website. The bulletin is a scrap misleading every bit the user did not cancel the task, simply an administrator did.

aborted job

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

SQL Server Reporting Services Security Roles and System Function Maintenance

The security Role Maintenance area serves ii like 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 ii system roles (System Administrator and Arrangement User) are created as noted below. Each of these roles are assigned a predefined ready of permissions or tasks.

object explorer

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

New Role

New System Role

Adjacent for either function, make full in a proper name, a clarification (optional but helpful), and and then select the appropriate chore(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 like fashion either part blazon can be maintained past correct clicking on the role to be inverse and so selecting properties or by double clicking on a role, as displayed below.

role properties

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

role properties detail

Last, roles can easily exist deleted by kickoff right clicking on the role to be deleted and and so select Delete as shown below. The confirmation Delete Catalog Items screen appears; once you click OK, the role is deleted without further alert. Delight practice care when deleting as you could lock yourself out of a folder or study server.

delete row

delete role confirm

SQL Server Reporting Services Subscription Shared Schedules

The last major detail that can exist updated in Direction Studio are shared schedules; these schedules are more often than not used past subscriptions to run reports at specified times on a set schedules. To create a new schedule, right click on Shared Schedules so click New Schedule.

New Schedule

For a new schedule, every bit outlined beneath, fill up in: a name, a start date, an end date (optional), and a recurrence blueprint. Based on the recurrence pattern blazon selected, different scenario and selection box will appear in the square area. For case, if you select hourly, the hours and minutes interval volition evidence (as noted below). However if you select week, the days of the week and the repeat afterward this number of weeks pick will announced. When the setup is complete, click OK.

New Shared Schedule

Maintaining a schedule can be done by right clicking the desired schedule and selecting backdrop (or double clicking on it). Similar to the new schedule screen, customized schedules are defined past 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 backdrop folio provides a handy mode to see all the reports which are assigned to the selected schedule.

Reports belonging to schedule

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

schedule delete

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

embedded schedule

Conclusion

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

Adjacent Steps

TechNet, Connecting to a Report Server in Direction Studio: http://technet.microsoft.com/en-us/library/ms159797.aspx
More than Reporting Services Tips

Pop Articles

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

Article Final Updated: 2013-09-13