SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Dynamic SSRS report documentation via a ReportServer.dbo.Catalog query

By Stan Kulp,

Creating adequate documentation for any IT project is a challenge, particularly with SSRS where an organization can have literally hundreds or thousands of reports in production. Writing the documentation alone can be a formidable task, not to mention keeping it up-to-date when changes are made. Fortunately, the SQL Server ReportServer.dbo.Catalog table contains data that can be used to create a documentation system that is automatically updated as report changes are made.

Microsoft does not document or support querying the ReportServer.dbo.Catalog table...

...but it contans a lot of useful information. The Content field, for instance, contains the entire RDL file that defines each report. The following query...

SELECT 
	Name as ReportName
	,CONVERT(Varchar(Max),CONVERT(VARBINARY(MAX),Content)) AS ReportContent 
FROM  
ReportServer.dbo.Catalog Where Content is NOT NULL

...extracts the Content field to reveal the RDL XML code.

This query...

WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition', 
    'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd )
SELECT DISTINCT ReportName		= name
	  ,CommandText		= x.value('(Query/CommandText)[1]','VARCHAR(250)')
 FROM ( SELECT C.Name,CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML
      FROM ReportServer.dbo.Catalog C
     WHERE C.Content is not null
      AND C.Type = 2
	  ) a
 CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet') r ( x )
 CROSS APPLY x.nodes('Fields/Field') f(df) 
ORDER BY name  

...extracts the SQL commands embedded in the RDL code.

If you cannot get the above two queries to work, you probably need to change the schema in the queries to the schema being used by SQL Server on your SQL Server installation.

Open an SSRS report in Visual Studio and select View-Code from the main menu.

Change the schema in the queries to the one in the SSRS code.

The following query is used in the zipped  "SSRS Dynamic Documentation" SSRS report RDL resource file attached to this article.

BEGIN TRY
DROP TABLE #ReportList
END TRY
BEGIN CATCH
END CATCH

BEGIN TRY
DROP TABLE #ReportParameters
END TRY
BEGIN CATCH
END CATCH

BEGIN TRY
DROP TABLE #ReportFields
END TRY
BEGIN CATCH
END CATCH

SELECT 
Name
,Path
INTO #ReportList
FROM ReportServer.dbo.Catalog 
WHERE Content IS NOT NULL
ORDER BY Name;

 SELECT DISTINCT Name as ReportName
,ParameterName = Paravalue.value('Name[1]', 'VARCHAR(250)') 
  ,ParameterType = Paravalue.value('Type[1]', 'VARCHAR(250)') 
  ,ISNullable = Paravalue.value('Nullable[1]', 'VARCHAR(250)') 
  ,ISAllowBlank = Paravalue.value('AllowBlank[1]', 'VARCHAR(250)') 
  ,ISMultiValue = Paravalue.value('MultiValue[1]', 'VARCHAR(250)') 
  ,ISUsedInQuery = Paravalue.value('UsedInQuery[1]', 'VARCHAR(250)') 
  ,ParameterPrompt = Paravalue.value('Prompt[1]', 'VARCHAR(250)') 
  ,DynamicPrompt = Paravalue.value('DynamicPrompt[1]', 'VARCHAR(250)') 
  ,PromptUser = Paravalue.value('PromptUser[1]', 'VARCHAR(250)') 
  ,State = Paravalue.value('State[1]', 'VARCHAR(250)') 
INTO #ReportParameters
 FROM (  
SELECT top 1000 C.Name,CONVERT(XML,C.Parameter) AS ParameterXML
FROM  ReportServer.dbo.Catalog C
WHERE  C.Content is not null
AND  C.Type  = 2
 ) a
CROSS APPLY ParameterXML.nodes('//Parameters/Parameter') p ( Paravalue )
ORDER BY ReportName,ParameterName;

WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition', 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd )
SELECT DISTINCT ReportName = name
    ,DataSetName = x.value('(@Name)[1]', 'VARCHAR(250)') 
 ,DataSourceName = x.value('(Query/DataSourceName)[1]','VARCHAR(250)')
 ,CommandText = x.value('(Query/CommandText)[1]','VARCHAR(250)')
 ,Fields = df.value('(@Name)[1]','VARCHAR(250)')
 ,DataField = df.value('(DataField)[1]','VARCHAR(250)')
 ,DataType = df.value('(rd:TypeName)[1]','VARCHAR(250)')
 ,ConnectionString = x.value('(ConnectionProperties/ConnectString)[1]','VARCHAR(250)')
INTO #ReportFields
 FROM ( SELECT C.Name,CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML
      FROM ReportServer.dbo.Catalog C
     WHERE C.Content is not null
      AND C.Type = 2
 ) a
 CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet') r ( x )
 CROSS APPLY x.nodes('Fields/Field') f(df) 
ORDER BY name 

SELECT 
a.Name AS ReportName
,a.Path
,SUBSTRING(a.Path,1,LEN(a.Path)-LEN(a.Name)) AS ReportFolder
,'http://msl-db12/Reports/Pages/Report.aspx?ItemPath='+REPLACE(SUBSTRING(a.Path,1,LEN(a.Path)-LEN(a.Name)),'/','%2f')+REPLACE(a.Name,' ','+') AS ReportLink
,'User Input' AS FieldType
,b.ParameterPrompt AS DataSetOrPromptName
,b.ParameterName AS FieldOrParameterName
FROM #ReportList a
LEFT OUTER JOIN #ReportParameters b ON a.Name = b.ReportName
WHERE b.ParameterName IS NOT NULL
UNION
SELECT 
a.Name AS ReportName
,a.Path
,SUBSTRING(a.Path,1,LEN(a.Path)-LEN(a.Name)) AS ReportFolder
,'http://msl-db12/Reports/Pages/Report.aspx?ItemPath='+REPLACE(SUBSTRING(a.Path,1,LEN(a.Path)-LEN(a.Name)),'/','%2f')+REPLACE(a.Name,' ','+') AS ReportLink
,'Data Point' AS FieldType
,b.DataSetName AS DataSetOrPromptName
,b.Fields AS FieldOrParameterName
FROM #ReportList a
LEFT OUTER JOIN #ReportFields b ON a.Name = b.ReportName
WHERE b.Fields IS NOT NULL
ORDER BY Name,Path,FieldType,ParameterPrompt,ParameterName

Deploy this SSRS report to your report server with a user that has read privileges on the ReportServer databse after changing the schema referenced by the DataSet1 query, if necessary.

When you run the report, it will display the name of each report, the report server folder in which it resides, a link to the report and a listing of all the datasets,fields and parameters used by the report.

When you click the report link, you will be taken to that report.

--The SQL code used in this article was adapted from Sorna Kumar Muthuraj's code in Extract metadata from report server database on the Microsoft Developer Network.

 

Resources:

SSRS Dynamic Documentation.rdl
Total article views: 3639 | Views in the last 30 days: 21
 
Related Articles
FORUM

Select report query

Select report query

FORUM

Select query

Select query

FORUM

select query

select query

FORUM

Select Smart query

Select Query

FORUM

Recursive select Query

I want to write Recursive select Query

 
Contribute