Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Documenting SSRS RDL files Expand / Collapse
Author
Message
Posted Monday, March 25, 2013 10:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, November 8, 2014 2:59 AM
Points: 21, Visits: 86
I'm working in SSRS2008 R2 and I have several projects, which in turn have several report definitions held within them.

Each rdl may have many datasets, tablix, matrix definitions detailed within it.

My problem is documentation! I need to document all the datasets, tablix/matrix names, stored procedures etc

Is there an easy way of extracting this information and creating either an excel workbook using VBA, or detailing them back into a SQL2008 table that I can then use SSRS to produce the report?

I have read a similar post that suggests using the reportserver database to get this information from, but if the rdl hasn't been deployed yet it won't be in the database.

Any help would be much appreciated as it will save me going through over 150 rdl's manually and writing down the information.

Thanks
Post #1435048
Posted Wednesday, March 27, 2013 11:32 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, October 24, 2014 9:55 AM
Points: 1,485, Visits: 1,036
RDL's are just xml files.

For example this will get you the data sets information used in your report. You can use this to insert into a table, or copy paste results wherever you want.
Using your local sql server.

IF NOT EXISTS (SELECT * FROM tempdb.INFORMATION_SCHEMA.Tables WHERE TABLE_NAME LIKE '#t%')
CREATE TABLE #T (IntCol int, XmlCol xml);
ELSE TRUNCATE TABLE #t
GO

INSERT INTO #T(XmlCol)
SELECT * FROM OPENROWSET(
BULK 'C:\Users\<YourUserName>\Documents\Visual Studio 2008\Projects\Test\Test\MyTestReport.rdl',
SINGLE_BLOB) AS x;


DECLARE @idoc int
DECLARE @doc XML
SELECT TOP 1 @Doc = xmlCol
FROM #T t
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc, '<root xmlns:n="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"/>'

SELECT
DataSourceName
,Name AS DataSetName
,ISNULL(CommandType,'Text') AS CommandType
,CommandText
,DataField AS DataSetFieldName
,TypeName AS FieldDataType
FROM OPENXML (@idoc, '/n:Report/n:DataSets/n:DataSet/n:Fields/n:Field',2)
WITH (Name varchar(50) '../../@Name'
,DataSourceName varchar(100) '../../n:Query/n:DataSourceName'
,CommandType varchar(100)'../../n:Query/n:CommandType'
,CommandText varchar(1000)'../../n:Query/n:CommandText'
,DataField VARCHAR(50) 'n:DataField'
,TypeName VARCHAR(50) 'rd:TypeName'
)

EXEC sp_xml_removedocument @idoc

This is a sample how to pull info from a tablix. However the structure changes dramatically when you add row and column groups, so its not a one size fits all type query.

SELECT 
Name
,AttribName
,ToolTip
,DataField
,ROW_NUMBER() OVER(ORDER BY CASE WHEN AttribName LIKE 'hdr%' THEN AttribName ELSE 'z' + AttribName END) AS Ord
FROM --OPENXML (@idoc, '/n:Report/n:ReportSections/n:ReportSection/n:Body/n:ReportItems/n:Tablix/n:TablixColumnHierarchy/n:TablixMembers/n:TablixMember/n:Group',2)
OPENXML (@idoc, '/n:Report/n:Body/n:ReportItems/n:Tablix/n:TablixBody/n:TablixRows/n:TablixRow/n:TablixCells/n:TablixCell/n:CellContents/n:Textbox',2)
WITH (Name varchar(10) '../../../../../../../@Name'
,AttribName varchar(100) '@Name'
,ToolTip VARCHAR(500) 'n:ToolTip'
,DataField VARCHAR(1000) 'n:Paragraphs/n:Paragraph/n:TextRuns/n:TextRun/n:Value'

Post #1436066
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse