HOW TO GET THE LIST OF SSRS REPORTS (T-SQL, SSRS)

SSRS Web Access

SQL Server Reporting Services in its native mode offers a decent report management features like organizing reports into folders, managing security, various types of subscriptions, snapshots, cache options and other. Also, navigation through folders and between reports is easy. 

Access to SSRS content and administration tool is available at the following locations:

1. Content area: http://<servername>/reportserver (http://<localhost>/reportserver)
2. Manager: http://<servername>/reports (http://<localhost>/reports)

These are default installation locations locations, but they might differ depending on SSRS configuration or installation settings. If one of these are not available from those reasons, either you can "ask your administrator" or check SQL Server Reporting Services Configuration manager which is available through start menu on the server (and hopefully you have access to the server).

However, database admins or report developers face situations where Reporting Services instance might host tons of client reports spread across the folders. Navigating and monitoring reports might be difficult in that case, but even when there is a small subset of reports we are interested in getting more information about our reports like usage by users, execution times, data sources, data sets.

Get the List of Reports using T-SQL

SSRS metadata are stored into ReportServer database and the easiest way to access it is through SQL Server Management Studio.

To get the list of databases let's execute a simple query on "ReportServer" database and it can be an excellent admin tool to monitor all of the reports on SSRS instance :

USE [ReportServer]

GO

SELECT

       Name,

       [Path]

       --,[Description]

FROM [dbo].[Catalog]

WHERE [Type] = 2

ORDER BY [Path]

Result:

Name Path
----------------------------------------------------------------------------------------------------------------------
AdventureWorks_Base /AdventureWorks/AdventureWorks_Base
Customers_Near_Stores /AdventureWorks/Customers_Near_Stores
Employee_Sales_Summary /AdventureWorks/Employee_Sales_Summary
Sales_by_Region /AdventureWorks/Sales_by_Region
Sales_Order_Detail /AdventureWorks/Sales_Order_Detail
Store_Contacts /AdventureWorks/Store_Contacts
(6 row(s) affected)
 

Also, here is a small addition to previous query with parent folder and full path included:

USE [ReportServer]

GO

SELECT

       Name,

       FullPath = [Path]

       ,ReportParentPath = REVERSE(SUBSTRING(REVERSE(Path), CHARINDEX('/', REVERSE(Path)), LEN(REVERSE(Path))))

       --,[Description]

FROM [dbo].[Catalog]

WHERE [Type] = 2

ORDER BY [Path]

For broader monitoring considerations I recommend to dig down in some of the most important tables in ReportServer database:

USE [ReportServer]

GO

SELECT * FROM dbo.[Catalog]

SELECT * from dbo.[DataSets]

SELECT * FROM dbo.[DataSource]

SELECT * FROM dbo.[Users]

SELECT * FROM dbo.[ExecutionLog]

Mike C.