Problem
The SQL Server Reporting Services (SSRS) ReportServer database created during a native SSRS installation is undocumented by Microsoft, but I want to query it.
Solution
This tip will explain common tables and joins as found in a default ReportServer database and some example scenarios where such queries could be helpful.
Querying the SSRS Catalog
The first useful table within ReportServer is dbo.Catalog. This table contains 1 row for every object found on the SSRS site. This includes a row for each folder, report, data source, image, and linked report. The primary key and clustered index for the table is ItemID and it is a GUID. The user columns are also a GUID and can be converted to a name by joining to the dbo.Users table.
Depending on the version of SSRS not all type values may be available.
SELECT
ItemID -- Unique Identifier
, [Path] --Path including object name
, [Name] --Just the objectd name
, ParentID --The ItemID of the folder in which it resides
, CASE [Type] --Type, an int which can be converted using this case statement.
WHEN 1 THEN 'Folder'
WHEN 2 THEN 'Report'
WHEN 3 THEN 'File'
WHEN 4 THEN 'Linked Report'
WHEN 5 THEN 'Data Source'
WHEN 6 THEN 'Report Model - Rare'
WHEN 7 THEN 'Report Part - Rare'
WHEN 8 THEN 'Shared Data Set - Rare'
WHEN 9 THEN 'Image'
ELSE CAST(Type as varchar(100))
END AS TypeName
--, content
, LinkSourceID --If a linked report then this is the ItemID of the actual report.
, [Description] --This is the same information as can be found in the GUI
, [Hidden] --Is the object hidden on the screen or not
, CreatedBy.UserName CreatedBy
, CreationDate
, ModifiedBy.UserName ModifiedBy
, ModifiedDate
FROM
ReportServer.dbo.[Catalog] CTG
INNER JOIN
ReportServer.dbo.Users CreatedBy ON CTG.CreatedByID = CreatedBy.UserID
INNER JOIN
ReportServer.dbo.Users ModifiedBy ON CTG.ModifiedByID = ModifiedBy.UserID;
Consider this instance of SSRS with one folder, one report, one linked report, and an image at the root.
Within the folder is a single data source.
The output of the above query would contain a row for each item in the screenshots. The row at the top with no ParentID is the root folder. Notice that the root ItemID is the ParentID for each item stored at the root. The one data source that lives in a folder outside the root has a ParentID value belonging to the source older. Finally, the linked report is the only object with a LinkSourceID value and that that value is the ItemID of the report.
There is one additional column to cover within the catalog. The column is called content. This column is a binary value and contains the actual XML definition of the object where appropriate. To view the actual XML rather than the binary value use this query.
This previous tip covers the content column in much more detail.
SELECT
[Path]
, CASE [Type]
WHEN 2 THEN 'Report'
WHEN 5 THEN 'Data Source'
END AS TypeName
, CAST(CAST(content AS varbinary(max)) AS xml)
, [Description]
FROM ReportServer.dbo.[Catalog] CTG
WHERE
[Type] IN (2, 5);
Continuing with the example from above, this is the expected output. The blue text can be clicked and will open the XML in a new SSMS window.
The Data Source object looks like this when expanded into its own window.
<DataSourceDefinition > <Extension>SQL</Extension> <ConnectString>Server=.;Initial Catalog=ReportServer</ConnectString> <CredentialRetrieval>Integrated</CredentialRetrieval> <Enabled>True</Enabled> </DataSourceDefinition>
Can one change these values, perhaps to move a report to a different folder or to hide/unhide it? Technically, yes. But that is not advised and would not be a supported operation.
Querying SSRS Report Execution Statistics
Every time an SSRS report executes a row is entered into the table dbo.ExecutionLog. In modern versions of ReportServer the table is called dbo.ExecutionLogStorage and dbo.ExecutionLog is a view.
SELECT * FROM dbo.ExecutionLog
The data returned by dbo.ExecutionLog can be difficult to understand so Microsoft did everyone a favor and started adding more views to make it even simpler to query. They started with dbo.ExecutionLog2 and later dbo.ExecutionLog3. Querying dbo.ExecutionLog3 shows many of the columns have been converted to human readable values.
SELECT
[ItemPath] --Path of the report
, [UserName] --Username that executed the report
, [RequestType] --Usually Interactive (user on the scree) or Subscription
, [Format] --RPL is the screen, could also indicate Excel, PDF, etc
, [TimeStart]--Start time of report request
, [TimeEnd] --Completion time of report request
, [TimeDataRetrieval] --Time spent running queries to obtain results
, [TimeProcessing] --Time spent preparing data in SSRS. Usually sorting and grouping.
, [TimeRendering] --Time rendering to screen
, [Source] --Live = query, Session = refreshed without rerunning the query, Cache = report snapshot
, [Status] --Self explanatory
, [RowCount] --Row count returned by a query
, [Parameters]
FROM ReportServer.dbo.ExecutionLog3
Here is some sample output from dbo.ExecutionLog3 after each report from the prior demo was executed once on the screen and then exported to Excel and PDF respectively.
One of the most useful parts of this table is the parameters column. This column is part of the execution log and records the parameters used when a report was executed. When a user says that they ran a report and received unexpected results it can be hard to replicate without knowing exactly what they typed in. With this column the process becomes a matter of fact rather than a matter of guessing. Did the user type in something unexpected? Perhaps an obvious typo like the wrong year on their date range? Bingo. There it is in black and white.
The sample catalog report has been modified to accept 2 parameters. They aren’t used by the report, but they will still be captured by the execution log. Consider this execution of the catalog report.
The execution log captures these values like so.
Querying SSRS Subscriptions
Anyone that manages an SSRS box has noticed that each subscription creates a SQL Server Agent Job with a GUID as its name. These might seem like they don’t have meaning, but they actually do. There are 2 new tables that will help understand how subscriptions are stored in the ReportServer database.
The table dbo.ReportSchedule is a list of schedules with which one might place a subscription. Another table, dbo.Subscriptions marries a report from dbo.Catalog to a schedule in dbo.ReportSchedule creating a report subscription. It is the unique identifier of dbo.ReportSchedule that becomes the name of the SQL Server Agent Job. Once joined to msdb.dbo.sysjobs the subscription can be followed for history like any other job.
Continuing with the prior demo database a single subscription was created against My Catalog Report.
As expected, a single new SQL Server Agent Job appeared on the instance.
This query will join from the catalog through to the agent job in MSDB.
SELECT
ctg.[Path]
, s.[Description] SubScriptionDesc
, sj.[description] AgentJobDesc
, s.LastStatus
, s.DeliveryExtension
, s.[Parameters]
FROM
ReportServer.dbo.[Catalog] ctg
INNER JOIN
ReportServer.dbo.Subscriptions s on s.Report_OID = ctg.ItemID
INNER JOIN
ReportServer.dbo.ReportSchedule rs on rs.SubscriptionID = s.SubscriptionID
INNER JOIN
msdb.dbo.sysjobs sj ON CAST(rs.ScheduleID AS sysname) = sj.name
ORDER BY
rs.ScheduleID;
For this example, the results of this query show that the inner join has matched the subscription to the agent job.
Using this Data in Real Life Scenarios
This information may be quite interesting to someone that enjoys SQL Server trivia, but its usefulness may seem limited. This section of the tip includes a few real-life scenarios where this data was used to solve a business problem and may serve as an example of how this information can be applied to the day-to-day workings of a SQL Server professional.
This author remembers a time when a report was running slowly for the end users. He was asked to help clean up the query to make the report run faster. Upon reviewing the execution log, it was determined that the query (TimeDataRetrieval) was finishing in a sub-second time frame. The rendering time (TimeRendering), however, was many seconds long. This pushed the onus back to the report developer and away from the DBA who would have otherwise wasted time trying to tune a well-tuned query.
Another real-world scenario is a time that many users were complaining about application performance. The SQL Server was running with higher than normal resource usage. The SSRS application was consistently showing up with a busy, active SPID. Reviewing dbo.ExecutionLog3 showed a query running repeatedly with very long TimeDataRetieval. Reviewing history for days prior showed that execution was considerably longer today than yesterday or prior days. Reviewing dbo.Catalog showed the report had been modified that very morning along with the user that modified it. It was clear which report writer needed to be consulted at that time to rollback a change and review it before resubmitting.
A customer was getting ready to migrate to a new SQL Server and wanted to clean up older reports that weren’t being used any longer. Each business unit manager was presented with a report showing each report in their respective libraries along with the execution count and most recent execution time. They were able to remove many more reports from the library than would have been had they not started with such concrete evidence that many reports were idle.
Next Steps
- How to mine the content XML columns of dbo.Catalog
- Installing SSRS 2017
- Administering SSRS in SSMS
- Check out all of the SQL Server Reporting Services Resources on MSSQLTips.com
