Reports and SSRS - Get Report Data Sets

Applicability:

                 SQL Server 2000:        Not Tested        
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    Sorna Kumar Muthuraj
Date:        9 Jul 2011

Description

SP to retreive the data sets (including column and data types)  for one or many reports.  
The data sets are extracted directly from the XML for each report, so this means that the correct namespace must first be referenced. 
This was originally coded for SQL Server 2012 and the namespace was hard-coded, but I have converted the query to dynamic SQL and extracted the namespace from the first report in the set of reports in scope.
Ideally I would have liked this query not to have a loop, but even though a manual changing of namespace reference worked with an unchanged set of attributes between 2012, 2008 and 2005, any reports created with a different namespace than the one declared would actually produce no results, and this could be misleading.

I would think that transferring reports upon upgrade would be quite common (I did one last year) and I'm fairly sure the namespace doesn't get upgraded, so I put in a loop to be safe.
Performance is a little sub-optimal, but the SP uses the NOLOCK hint, so there should be no interference with report operations.

NOTE:  It is assumed that the Report Server Database has its default name of 'ReportServer'

Code

Stored Procedure:


DROP PROC usp_GetReportDataSets
GO

CREATE PROC usp_GetReportDataSets @ReportName NVARCHAR(850) = NULL
AS

/****************************************************
Purpose:   To retreive the data sets for one or many reports.
           Uses the Catalog table in the report server
           database (assumed to be called by its default
           name of 'ReportServer')
          
           The report xml is directly parsed for the information, so a namepsace
           ise required.  In order to make this code more protable, the namespace
           is also parsed from the report code.
          
           If many reports are in scope, then only the namespace from
           the first report (alphabetically) is used

Author:        Sorna Kumar Muthuraj
History:   9 Jul 2011 - Initial Issue
           25 Jul 2012 - transferred to stored procedure and added code to dynamically
           discover the namespace.  Added a loop to ensure that servers with reports
           having mixed version reports will still return data.
           7 Oct 2014 - ChillyDBA - added report path to returned results.
          
*****************************************************/

DECLARE
  
@Namespace  NVARCHAR(500),
  
@SQL        VARCHAR(MAX),
  
@ReportGUID VARCHAR(255)
  
CREATE TABLE #Results
(
  
ItemID          INT IDENTITY(1,1),
   ReportName      VARCHAR(850),
   ReportPath      VARCHAR(850),
   DataSetName     VARCHAR(250),  
  
DataSourceName  VARCHAR(250),  
  
CommandText     VARCHAR(250),  
  
Fields          VARCHAR(250),  
  
DataField       VARCHAR(250),  
  
DataType        VARCHAR(250)
)
  
SELECT  @ReportGUID = MIN(CAST(ItemID AS VARCHAR(255)))
FROM Reportserver.dbo.Catalog (NOLOCK)
WHERE Name LIKE '%' + ISNULL(@ReportName, '') + '%'
AND Name IS NOT NULL
AND
TYPE = 2

WHILE @ReportGUID IS NOT NULL
BEGIN
   SELECT
      
@SQL = '',
      
@Namespace = ''
  
  
SELECT @Namespace= SUBSTRING(x.CatContent, x.CIndex, CHARINDEX('"',x.CatContent,x.CIndex+7) - x.CIndex)
        
FROM
        
(
            
SELECT TOP 1 CatContent = CONVERT(NVARCHAR(MAX),CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)))
                   ,
CIndex    = CHARINDEX('xmlns="',CONVERT(NVARCHAR(MAX),CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content))))
            
FROM Reportserver.dbo.Catalog C  (NOLOCK)
            
WHERE C.Content IS NOT NULL
            AND
C.TYPE  = 2
            
AND C.ItemID = CAST(@ReportGUID AS UNIQUEIDENTIFIER)
            
ORDER BY C.name
        
) X

  
SELECT @Namespace = REPLACE(@Namespace,'xmlns="','') + ''


  
SELECT @SQL = 'WITH XMLNAMESPACES ( DEFAULT ''' + @Namespace + ''', ''http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'' AS rd )
SELECT  ReportName        = name
   ,ReportPath        = path
   ,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)'')
  FROM (  SELECT C.Name,C.path, CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML
   FROM  ReportServer.dbo.Catalog C  (NOLOCK)
  WHERE  C.Content is not null
AND  C.Type = 2
AND C.ItemID = CAST('''
+ @ReportGUID + ''' AS UNIQUEIDENTIFIER)
   ) a
  CROSS APPLY reportXML.nodes(''/Report/DataSets/DataSet'') r ( x )
  CROSS APPLY x.nodes(''Fields/Field'') f(df)  
ORDER BY name '
  
  
INSERT #Results
  
(
       ReportName,
       ReportPath,
       DataSetName,
      
DataSourceName,
      
CommandText,
      
Fields,
      
DataField,
      
DataType
  
)    
  
EXEC(@SQL)      

  
SELECT  @ReportGUID = MIN(CAST(ItemID AS VARCHAR(255)))
  
FROM Reportserver.dbo.Catalog (NOLOCK)
  
WHERE CAST(ItemID AS VARCHAR(255)) > @ReportGUID
  
AND Name LIKE '%' + ISNULL(@ReportName, '') + '%'
  
AND Name IS NOT NULL
   AND
TYPE = 2
END

SELECT
       ReportName,
       ReportPath,
       DataSetName,
      
DataSourceName,
      
CommandText,
      
Fields,
      
DataField,
      
DataType
FROM #Results
ORDER BY ItemID

ċ
usp_GetReportDataSets.sql
(4k)
Andy Hughes,
Oct 7, 2014, 9:22 AM
Comments