What Kind of Reports are Published / Which Use Custom Report Items?
As an administrator of a report server, you may occasionally ask yourself the question of what kind of reports are published in terms of their particular feature usage. For example:
· Usage of Custom Report Items (CRIs)
You plan to upgrade from RS 2005 to RS 2008 and there are CRI controls installed on your current RS 2005 server (look for a <ReportItems> section in rsReportServer.config)? You may want to know whether they are actually referenced in any currently published reports on the server, because you may have read this related blog posting and are wondering if you need to install the 2005-based CRI controls on your RS 2008 server?
· Usage of subreports
Which reports use subreports? Not only is this interesting for dependency tracking, but it might also be relevant for optimizing performance.
· Usage of custom assemblies
What kind of (custom) assemblies are referenced by published reports? This might be interesting information for auditing, or for trouble-shooting the security policy configuration of a report server.
Offline Solutions
Well, as always there are multiple ways to accomplish this.
One option is to download all the reports from the server in an automated way and then analyze the RDL contents. This can be accomplished similar to the RS Explorer sample application, via the Reporting Services SOAP API, and using ListChildren and GetReportDefinition. Alternatively, you could utilize RS Scripter, a great tool written by Jasper Smith, to script/download all reports. For the subsequent offline analysis in the file system use full text search tools (e.g. findstr).
Basic SQL Query Solutions
As an administrator of a report server, you may access the catalog database directly. Keep in mind that any queries you execute directly against the report server’s catalog database may affect the performance of the overall system. Make sure that you never take any locks on objects in that database, as this is not supported and interferes with the function of the report server. That said, if you are familiar with the RDL XSD structure, you can write a simple query like the following to find all reports that reference subreports:
USE ReportServer
SELECT Path, Name, Description, *
FROM Catalog
WHERE Type = 2 AND
CONVERT(VARCHAR(MAX),CONVERT(VARBINARY(MAX),Content)) LIKE '%<Subreport%'
SQL XQuery Solutions
Let's now focus on a slightly more complex scenario: you want to determine which types of CRIs are used by which reports on your server.
Note that the RDLs stored in the report server are exactly the RDL files that were originally uploaded. Since the CRI feature was introduced with the RDL 2005/01 namespace, we only need to query that particular namespace to find reports that reference 2005 CRIs. If however you are looking for subreports or other elements, you may have to query several namespaces: RDL 2003/10, RDL 2005/01, and RDL 2008/01. Btw, my esteemed colleague John Gallardo used to work a lot with XQuery before joining the Reporting Services team and helped crafting the initial query, which I further refined to this:
USE ReportServer
/* declare and populate temp table */
DECLARE @tab TABLE (Path NVARCHAR(MAX), xmlColumn XML)
INSERT @tab
SELECT Path, CONVERT(XML, CONVERT(VARBINARY(MAX), Content)) FROM Catalog WHERE Type=2;
/* for all CustomReportItem elements, display the report path, CRI type and name */
WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition' as rdl)
SELECT Path as ReportPath,
n.x.value('@Name', 'nvarchar(256)') as CRIName,
n.x.value('(//rdl:Type)[1]', 'nvarchar(256)') as CRIType
FROM @tab
CROSS APPLY xmlColumn.nodes('//rdl:CustomReportItem') n(x)