bigator

Find all SSRS Reports, Report Locations & Data Sources in ReportServer

Hi All,
sometimes opening ReportServer or Reports (Report Manager) takes a while. On the other hand querying database for information is faster than waiting for websites to respond.

Here is the quick way to check the number of SSRS reports deployed on report server, their locations (paths) and available data sources.

Use <Report Server Name>
Go

List of available Reports & Their Locations
Select
[Name] as [Report Name], [Path] as [Report Location]
From dbo.Catalog
Where [Name] <> ” and [Type] = 2
Order By [Name]
Go

List of available Data Sources
Select
[Name] as [Report Name], [Path] as [Report Location]
From dbo.Catalog
Where [Name] <> ” and [Type] = 5
Order By [Name]
Go

Interesting fact about ReportServer database:
“We do not document or support querying any Report Catalog tables.  If you need to find out what items are in the report catalog, use the ListChildren SOAP API to list them.  We do support the ExecutionLog (Table/View; version dependent) or the ExecutionLog2 View.    Otherwise, writing queries against the SSRS DB is not supported and the schema, content, interpretation of the content is subject to change without notice.”

You can check this thread here:
http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/60dd3392-42d8-4dc4-b8e6-15e9aeaad29e

Type column values in dbo.Catalog table
(Note: Information mentioned in below table is not validated by Microsoft.)

Type Description
1 Folder
2 Report
3 Resource
4 Linked Report
5 Data Source
6 Report Model
7 Report Part (SQL 2008 R2)
8 Shared Dataset (SQL 2008 R2)

I will post more on SSRS internals in my later posts.
Thanks, Khilit

bigatorFebruary 6, 2012 · bigatorKhilitchandra Prajapati · bigator4 Comments
bigatorTags: , ,  · Posted in: Business Intelligence, SQL Server, SSRS, Tutorial

4 Responses

  1. clarke - February 27, 2012

    Thanks for interesting article.
    Clarke

  2. Kortney Dooney - March 7, 2012

    I really enjoy looking at on this web site , it holds superb blog posts. “Literature is the orchestration of platitudes.” by Thornton.

  3. Vijay - March 16, 2012

    Very interesting sir. Thank you.

  4. sanjay - June 6, 2013

    http://bretstateham.com/extracting-ssrs-report-rdl-xml-from-the-reportserver-database/

Leave a Reply


%d bloggers like this:
bigator