Tables used in SSRS Report
Hi Rudycat, From your description, you want to get the Folder name, report name, data source name, dataset name and table name for each report from the ReportServer database, right? Sorna had wrote a query which get Report name, data source name, dataset name, and query used in the dataset, it looks like below: WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition', 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd ) SELECT 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)') 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 For more information about it, please see: http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/0cbb8997-c54c-4de8-87e8-529dc6e2b017 Hope this helps. Regards, Charlie Liao If you have any feedback on our support, please click here.Charlie Liao TechNet Community Support
November 30th, 2012 4:57am

Thank you so much for this Charlie - I think this will work - so much to learn - so little time
Free Windows Admin Tool Kit Click here and download it now
November 30th, 2012 10:28am

Beginner using SSRS 2008 R2 - I need to build a report that tells be the tables used in all the SSRS reports on our reporting server. I'd like it to look like this: Folder / Report Name / Data Source / dataset name / Tables Used Is this possible? Does anyone have a query that can produce this? Any help is greatly appreciated.
November 30th, 2012 11:08am

Hi Rudycat, From your description, you want to get the Folder name, report name, data source name, dataset name and table name for each report from the ReportServer database, right? Sorna had wrote a query which get Report name, data source name, dataset name, and query used in the dataset, it looks like below: WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition', 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd ) SELECT 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)') 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 For more information about it, please see: http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/0cbb8997-c54c-4de8-87e8-529dc6e2b017 Hope this helps. Regards, Charlie Liao If you have any feedback on our support, please click here.Charlie Liao TechNet Community Support
Free Windows Admin Tool Kit Click here and download it now
November 30th, 2012 8:56pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics