Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Report Server Diagnostic Reports

By Michael Davis,

As the individual responsible for managing the usage, content, permissions and overall health of my company's enterprise reporting (using SSRS), I found it difficult to proactively stay on top of every facet of my job. Often I would get questions regarding a report that produced an error, how often an item was being used (and by whom), what subscriptions were running and who had permissions to various objects. Each of these questions had an answer, but it would usually take anywhere between a couple minutes to a few hours to solidly respond. After one such incident I decided to delve into the ReportServer database and create my own diagnostic reports to assist me in the future. I've found that the time spend was well worth the investment. This article is aimed at providing you the reports I created as well as giving a demonstration of their features. Enjoy!!!

This SSRS Diagnostics Report Pack was developed in SSRS 2005 (I am not sure of the schema of the 2008 ReportServer is different). It is 10 reports (.rdl files) in total and only requires an SSRS data source called "ds_ReportServer". The "master" report is simple report with a handful of sub-reports and a document map for easy navigation. The available items from the document map are as follows:

Report Inventory: This provides a list of every report on the Report Server. The item's path, data source, creation and modified date as well as the last access date are shown. In addition, a column notes whether each particular report has a subscription (this field can be clicked to view the "Subscriptions" report for details). The background color of the "Last Used" column will be highlighted should the report have produced an error on its last execution.

Subscriptions: This details every subscription on the Report Server and provides key information such as the status, last execution time, render format, write mode. Depending upon the type of subscription the file share path or the mail-to information will be shown. As with the inventory, the background of a cell (status) will change if the most recent run of the subscription produced an error.

Users: This gives a definitive list of all the users or active directory groups who have explicit access to a report somewhere on the server. The list of users are in alphabetical order and can be selected to show a narrowed down view of the Permissions report (see below) for just that individual.

Permissions: This provides a list of every folder and report on the Report Server represented in a similar navigational flow as one would find within Report Manager. Rows are color coded (gray for folder and white for report) and child items are stepped-in from their parent for easy identification. Each row can be expanded to view the user permissions to provide a quick and easy understanding of the security level for any object.

Usage Statistics: This details every report and its important metrics such as average run time, average rows returned, success percentage, last failure and total views. Each report can also be selected to view an execution summary displaying information on every run of the report (i.e.: who viewed the report and when, what the status was, what parameters (if any) were submitted and what format was rendered). Also available on this report are six month windows tracking the number of users and hits to the overall Report Server. Lastly charts identifying the top 10 reports and users, as well as reports receiving no hits (which could be considered for deprecation) are shown.

Command Text: This displays every report and the queries used within it. If the query is a stored procedure, the report will note which proc is used. Should the command text be a SQL statement, the entire query will be shown. This report can be accessed directly from the document map or by clicking on the "Average Run Time" value from the Statistics report. When accessing via the Statistics report, only the query for the selected item will be displayed. Note - some queries may not be shown if the number of characters is excessively long. 

Server Configuration: The final report is a simple item noting the configuration settings of the Report Server. In environments with multiple servers, this can be used to identify differences and produce a standard configuration.

These reports have saved many hours diagnosing failures, determining data sources and user permission settings. They have also eased maintenance as they provide key insight into the usage of individual reports. Attached is a compressed file containing the necessary rdl files. These can be imported into a Visual Studio (or Business Intelligence Development Studio) report server project and deployed from that environment. They could also be uploaded directly into Report Manager. Either installation route will require the creation of an SSRS data source called ds_ReportServer (pointing to the server's ReportServer database). Depending upon your installation route, you may have to manually configure or refresh the SSRS data source location. When deployed within Report Manager, this would be done by selecting the report properties and then noting the location of the data source.

Cheers...

Resources:

SSRS Diagnostics Report Pack.zip
Total article views: 9284 | Views in the last 30 days: 62
 
Related Articles
FORUM

Report Subscription

Creating subscription using C# code (Using Report server Webservices)

FORUM

Reprting server -- permissions

Report server access permissions

FORUM

Plugin for SQL Server Reporting Service Email subscription

Plugin that used to run subscription on SQL Reporting Services

FORUM

Report server subscription problem

Error on subscription

ARTICLE

Data Driven Subscriptions

Reporting Services is a great subsystem for SQL Server that fulfills quite a few needs. However just...

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones