SSRS Catalog Queries

Since it is hard to acquire a complete view of the SSRS landscape from the hierarchical web interface, the following query provides some basic information about what was deployed in SQL Server reporting services (SSRS). The report will provide you with a list of the objects in the reporting service, including folders, and reports, as well as information about who edited the report and when it was created, etc.

SELECT
a.Path AS Item_Path,
a.Name AS Item_Name,
CASE
WHEN a.Type = 1 THEN ‘Folder’
WHEN a.Type = 2 THEN ‘Report’
WHEN a.Type = 3 THEN ‘File’
WHEN a.Type = 4 THEN ‘Linked Report’
WHEN a.Type = 5 THEN ‘Data Source’
WHEN a.Type = 6 THEN ‘Report Model’
WHEN a.Type = 7 THEN ‘Report Part’
WHEN a.Type = 8 THEN ‘Shared Data Set’
WHEN a.Type = 9 THEN ‘Report Part’
WHEN a.Type = 11 THEN ‘KPI’
WHEN a.Type = 12 THEN ‘Mobile Report Folder’
WHEN a.Type = 13 THEN ‘PowerBI Desktop Document’
END AS Item_Type,
b.name AS Parent_Item_Name,
a.Description AS Item_Description,
a.Hidden AS Is_Hidden,
a.CreationDate,
a.ModifiedDate,
a.ContentSize,
c.UserName CreatedBy,
a.CreationDate,
d.UserName ModifiedBy,
a.ModifiedDate
FROM dbo.Catalog a
LEFT JOIN dbo.Catalog b ON b.ItemID = a.ParentID
INNER JOIN dbo.Users c ON a.CreatedByID = c.UserID
INNER JOIN dbo.Users d ON a.ModifiedByID = d.UserID;