Report SSRS report definition from TSQL

Within this holiday season, it is really difficult to bring on a topic relates to techs. So I decided to make it simple and short: let’s talk about how to manage reports by T-SQL.

If you had read my previous posts, I had demonstrated how to read and modify report definitions by C# codes. But there could be situations that we want to read report definitions for specific purpose, e.g. report documentation.

To document the report definition, try below codes (assume there is a report named "test report"):  

 

USE ReportServer
go

;WITH XMLNAMESPACES   
 (   
 DEFAULT   
 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition',   
 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'  
 AS rd   
 )  
 SELECT cte.name as ReportName  
   , ds.value('(@Name)[1]', 'VARCHAR(250)') as DatasetName  
      , ds.value('(Query/DataSourceName)[1]','VARCHAR(250)') as DataSourceName  
      , ds.value('(Query/CommandText)[1]','VARCHAR(250)') as Query  
      , fd.value('(@Name)[1]','VARCHAR(250)') as FieldName  
      , fd.value('(DataField)[1]','VARCHAR(250)') as DataField  
      , fd.value('(rd:TypeName)[1]','VARCHAR(250)') as DataType  
  FROM   
  (   
      SELECT C.Name  
           ,CAST(CAST(C.Content as VARBINARY(MAX)) as xml) AS reportXML  
      FROM ReportServer.dbo.Catalog C  
      WHERE C.Content is not null  
      AND C.Type = 2 and c.Path like '%test report%'  
 ) cte  
      CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet') r(ds)  
      CROSS APPLY ds.nodes('Fields/Field') f(fd)   
 ORDER BY name   

It them will give you result like below screenshot:

The only thing needs to be noticed is about the XML namespace declaration. As shown above, the "2010/01" is for SSRS 2012. Depends on the edition of the SSRS you are using, you might need to change the declaration a bit. For instance, if the report was created from SSRS 2008, you need to change the definition to "2008/01".

Simple and easy, isn't it?

So that is it. The last day of 2015, and last post of 2015.  Happy New Year!