
 June 24, 2014 08:41 by 
 Peter
 Peter
Using the Query to Get the List of All Reports - Now, I will explain you about a good admin tool to monitor all reports deployed on Reporting Service (SSRS) 2012 instance and it can be useful especially if the list of your reports is getting really big.  There is a simple query on "ReportServer" database which can be sometimes named differently like mine, "ReportServer$PETER" (named instances), but for SSRS 2012 installed on default instance it should be this one from below:

USE [ReportServer]
 GO
 SELECT 
   Name,
   [Path]
   --,[Description]
 FROM [dbo].[Catalog]
 WHERE [Type] = 2
 ORDER BY [Path]
 And here is the result of the code : 
 
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)
  
 Then, you can type a small extension below for the query which I prefer to use this one with parent folder structure 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 some of the most important tables in ReportServer database:
 USE [ReportServer]
 GOSELECT * FROM [Catalog]
 SELECT * from [dbo].[DataSets]
 SELECT * FROM [dbo].[DataSource]
 SELECT * FROM [dbo].[Users]