April 3, 2013 at 8:56 am
Is there a way to get a list of all reports that are using a specific view?
April 3, 2013 at 10:05 am
Yes, but how you get that info depends.
When creating your data sources in these reports did you use Embedded SQL? or Stored Procedures.
If you used only stored procedures simply query Information Schema DMV
SELECT *
FROM INFORMATION_SCHEMA.Routines
WHERE Routine_Definition LIKE '%MyViewName%'
If you used embedded sql you can query the Report Server catalog table to find the view in the dataset information
CAUTION: This query is kind of intense on the sql server, use caution if running on production server.
Check out this link:
http://gallery.technet.microsoft.com/scriptcenter/42440a6b-c5b1-4acc-9632-d608d1c40a5c
WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition', 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd )
SELECT
ReportName
,DataSetName
,DataSourceName
,CommandText
FROM (
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)')
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
--AND C.Name = '' Or Supply Report Name Here
) a
CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet') r ( x )
) T
WHERE CommandText LIKE '%MyViewName%'
April 3, 2013 at 10:22 am
I am using embedded datasets in my report and your code worked perfectly.
Thank you!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply