How to query list of reports available on the report server and the data sources used for the report?

Problem: How to query list of reports available on the report server and the data sources used for the report?

Solution: SSRS Report Server Database Tables

After we deploy reports to Report Server, the information gets saved inside of the report server database to keep a track of the details.

Below is the list of some useful information is saved inside this database.

      ·         Users that have access to the report server.
·         List of reports that are on the report server.
·         Path of where the reports are located.
·         Datasets that are present and used inside the report.
·         Parameter required reports
·         List of Subscriptions, subscription history
·         History of reports execution

Examples:

In this article, I am going to show we can query few different sets of queries that are useful to show the information present inside the report server database.

Example 1: In this example, I am going to show list of reports and the author of the report.

SELECT Name,CreatedBy = U.UserName

                  ,CreationDate = C.CreationDate

                  ,ModifiedBy = UM.UserName

                  ,ModifiedDate

  FROM dbo.Catalog C

  JOIN dbo.Users U

    ON C.CreatedByID = U.UserID

  JOIN dbo.Users UM

    ON c.ModifiedByID = UM.UserID

Results:

Name	CreatedBy	CreationDate	ModifiedBy	ModifiedDate
	Demouser	2016-07-21 11:47:42.183	Demouser	2017-06-11 17:07:29.780
Data Sources	Demouser	2017-06-11 17:07:29.773	Demouser	2017-06-11 17:07:29.817
DS_Local	Demouser	2017-06-11 17:07:29.817	Demouser	2017-06-14 21:05:58.400
Datasets	Demouser	2017-06-11 17:07:29.780	Demouser	2017-06-11 17:07:29.980
DS_DemoReport	Demouser	2017-06-11 17:07:29.980	Demouser	2017-06-13 21:21:05.393
Report Project	Demouser	2017-06-11 17:07:29.760	Demouser	2017-06-11 17:07:35.090
Cascading Parameters Report	Demouser	2017-06-11 17:07:31.220	Demouser	2017-06-13 21:21:06.597
Charts	Demouser	2017-06-11 17:07:33.100	Demouser	2017-06-13 21:21:08.290
Demo report 1	Demouser	2017-06-11 17:07:33.730	Demouser	2017-06-13 21:21:08.950
Drill Down	Demouser	2017-06-11 17:07:34.070	Demouser	2017-06-13 21:21:09.313
DrillThrough	Demouser	2017-06-11 17:07:34.333	Demouser	2017-06-13 21:21:09.613
Groupings	Demouser	2017-06-11 17:07:34.567	Demouser	2017-06-13 21:21:09.897
InvoiceDetails	Demouser	2017-06-11 17:07:34.857	Demouser	2017-06-13 21:21:10.373
Sparklines	Demouser	2017-06-11 17:07:35.090	Demouser	2017-06-13 21:21:10.730
Reports	Demouser	2017-06-11 16:54:16.803	Demouser	2017-06-14 20:59:50.627
Report Drill down	Demouser	2017-06-14 20:59:50.627	Demouser	2017-06-14 20:59:50.627

Example 2: In this example, I am going to show how we can see the execution details of a report.

SELECT C.NAME,

                CASE E.Requesttype

                                WHEN 1

                                                THEN 'Subscription'

                                WHEN 0

                                                THEN 'Report Launch'

                                ELSE ''

                                END AS TypeofExecution,

                E.TimeStart,

                E.TimeProcessing,

                E.TimeRendering,

                E.TimeEnd,

                E.STATUS,

                E.InstanceName,

                E.UserName

FROM dbo.ExecutionLog E

INNER JOIN dbo.CATALOG C

                ON E.ReportID = C.ItemID

WHERE C.NAME = 'Charts'

ORDER BY E.TimeStart DESC

Results:

Name	TypeofExecution	TimeStart	TimeProcessing	TimeRendering	TimeEnd	Status
Charts	Report Launch	2017-06-15 21:33:38.113	15	24	2017-06-15 21:33:38.160	rsSuccess
Charts	Report Launch	2017-06-15 21:31:04.677	627	2011	2017-06-15 21:31:07.570	rsSuccess