Creating an SSRS Report about the reports on the Report Server

Question

I am creating an SSRS Report (for documentation purposes) using SQL-Server 2014 about all the Reports we have on our Report Server. I found code online that would help me to do that.

Please see: http://www.sqlservercentral.com/articles/Reporting+Services+(SSRS)/152814/?utm_source=SSC&utm_medium=pubemail

The code is as follows:

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

This code worked great and I got a nice report about all the reports on my report server.

How do I join the subscriptions table to get columns for subscriptions for each report?

I added the following code but that didn't quite work:

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)'
    -------------------------Added this section here.
    ,S.[OwnerID]
    ,S.[Description]
    ,S.LastStatus)
    -------------------------End of the section here.    
INTO #ReportFields

FROM (  SELECT   C.Name
        ,CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML
    -------------------------Added this section here.
        ,S.[OwnerID]
        ,S.[Description]
        ,S.LastStatus
    -------------------------End of the section here.
        FROM [GEB_ReportServer].[dbo].[Catalog] C
    -------------------------And this section here.     
            INNER JOIN [GEB_ReportServer].[dbo].[Subscriptions] S ON C.ItemID = S.Report_OID
    -------------------------
        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

I tried the code from the below suggested answer but after it ran, I got an error message:

Msg 208, Level 16, State 0, Line 77. Invalid object name '#ReportParameters'.

So, I uncommented the -- INTO #ReportParameters and I got the report to run without errors but it'd didn't give any fields from the Subscriptions table.

Creating an SSRS Report about the reports on the Report Server

Answers (1)

Related Questions