June 24, 2014 08:41 by
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]