question

jpartlow avatar image
jpartlow asked

Listing reports and their dependancies

I would like a way to list for all reports in a SSRS folder and its subfolders the following: Report Name, Report Folder, Report Source(s) E.g. My Report, MyReportSubFolder, upd_My_Stored_Procedure_This_Report_Is_Based_On. It would also be nice to know [presumably within BIDS] whick reports a stored procedure is based on. Thanks in advance.
ssrsstored-proceduresreportdependency
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
Try this for starters. I've been using this on a recent SSRS project where there was no documentation of the existing SSRS instances. It is based in part on queries from Bret Stateham ([ http://bretstateham.com/category/ssrs/][1]) --The first CTE gets the content as a varbinary(max) --as well as the other important columns for all reports, --data sources and shared datasets. WITH ItemContentBinaries AS ( SELECT ItemID,Name,[Type] ,CASE Type WHEN 2 THEN 'Report' WHEN 5 THEN 'Data Source' WHEN 7 THEN 'Report Part' WHEN 8 THEN 'Shared Dataset' ELSE 'Other' END AS TypeDescription ,CONVERT(varbinary(max),Content) AS Content,path FROM ReportServer.dbo.Catalog WHERE Type IN (2,5,7,8) ), --The second CTE strips off the BOM if it exists... ItemContentNoBOM AS ( SELECT ItemID,Name,[Type],TypeDescription ,CASE WHEN LEFT(Content,3) = 0xEFBBBF THEN CONVERT(varbinary(max),SUBSTRING(Content,4,LEN(Content))) ELSE Content END AS Content, path FROM ItemContentBinaries ) , --This CTE strips off the trailing 0x00 if there is one ItemContentNoNullTerm AS ( SELECT ItemID ,Name ,[Type] ,TypeDescription ,path ,CASE WHEN RIGHT(Content,1) = 0x00 THEN CONVERT(varbinary(max),LEFT(Content,LEN(Content)-1)) ELSE Content END AS Content FROM ItemContentNoBOM ) --The old outer query is now a CTE to get the content in its xml form only... ,ItemContentXML AS ( SELECT ItemID,Name,[Type],TypeDescription, path ,CONVERT(xml,Content) AS ContentXML FROM ItemContentNoNullTerm ) --now use the XML data type to extract the queries, and their command types and text.... SELECT path --,ItemID ,Name --,[Type] ,TypeDescription --,ContentXML ,Query.value('(./*:DataSourceName/text())[1]','nvarchar(max)') AS DataSourceName ,ISNULL(Query.value('(./*:CommandType/text())[1]','nvarchar(1024)'),'Query') AS CommandType ,Query.value('(./*:CommandText/text())[1]','nvarchar(max)') AS CommandText --,cast(patindex('%subreport%',convert(varchar(max), ContentXML)) as bit) as HasSubReports FROM ItemContentXML --Get all the Query elements (The "*:" ignores any xml namespaces) outer APPLY ItemContentXML.ContentXML.nodes('//*:Query') Queries(Query) order by path [1]: http://bretstateham.com/category/ssrs/
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jpartlow avatar image
jpartlow answered
This worked beautifully. Thank you.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.