Free SQL Help for Devs, DBAs, and the Curious

SQL Server Reporting Services (SSRS) is a powerful tool that allows developers to design and manage reports. However, when working with a large number of reports, it can become difficult to manage the metadata associated with each report. This blog post will guide you through extracting metadata from SSRS reports for cataloging purposes, using technologies such as SQL Server 2012, SQL Server 2014, MySQL 5.6, DB2 10.5, and Azure SQL.

The SSRS ReportServer database is a warehouse of report metadata, and accessing this metadata can be accomplished by querying the Catalog and ExecutionLogStorage tables. The Catalog table holds information about every item in the report server database, including folders, reports, and data sources, while the ExecutionLogStorage table contains information about report execution.

-- T-SQL example to get report details from the Catalog table
SELECT 
    ItemID,
    Path,
    Name,
    Description
FROM 
    ReportServer.dbo.Catalog
WHERE
    Type = 2;

The above T-SQL code extracts ItemID, Path, Name, and Description from the Catalog table in the ReportServer database where the type is 2, which represents reports. This will return a list of all reports stored in your SSRS instance, along with their respective metadata.

To extract execution log data, which includes information on when and how often a report was run, by whom, and how long it took, we query the ExecutionLogStorage table. The following T-SQL code extracts the report path, username, and execution start and end times from the ExecutionLogStorage table.

-- T-SQL example to get execution log data from the ExecutionLogStorage table
SELECT 
    Catalog.Path,
    ExecutionLogStorage.UserName,
    ExecutionLogStorage.TimeStart,
    ExecutionLogStorage.TimeEnd
FROM 
    ReportServer.dbo.Catalog
INNER JOIN 
    ReportServer.dbo.ExecutionLogStorage 
ON 
    Catalog.ItemID = ExecutionLogStorage.ReportID;

The above T-SQL code joins the Catalog and ExecutionLogStorage tables on the ItemID (or ReportID) and extracts the report path, username, and execution start and end times. This will provide you with a detailed log of all report executions.

While this post focuses on SQL Server, similar practices can be applied to other databases such as MySQL and DB2 with their respective syntax. The principles remain the same: understand your report metadata storage structures and query them for the necessary information. In the case of Azure SQL, it's even easier because you can use the Azure portal's SQL Server Management Studio (SSMS) to run your queries.

In conclusion, cataloging metadata from SSRS reports can greatly aid in the management and optimization of your reporting services. By properly utilizing the ReportServer database, you can gain insights into your reports that go beyond their immediate content, enabling you to improve the efficiency and effectiveness of your reports.