How to auto document SSRS reports

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 contains 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.