SSRS Report dependencies

Question

Is there an automatic way to track reports and their dependencies for SSRS? What I want is a map/tree where I can see whether a report depends on a other report or subreport.

So far we did this by hand using Visual Studio 2008 checking all the 'actions' in text field properties, image field properties etc. for generating such a report tree

Someone advised me to query the catalog table in the reportdatabase. However I can't see a possibility there to see which report depends on an other report. Maybe I'm doing it wrong?

What I want is a map like:

Report A refers to/is used by Report B and C

Report B refers to/is used by D and F

Report F refers to/is used by Report G

...

Any advice?

Answer 1

I don't have 2008 to look at but do you have access to the RDL files that were published to the server? I can't recall if those are plain old XML files, but I think if you have those, it's probably a convenient way - you could parse them with Linq to XML, or XSLT.

The schema is documented at:

http://msdn.microsoft.com/library/dd297486%28SQL.100%29.aspx

Answer 2

You should indeed be able to do this using the Catalog table in the ReportServer database. It contains a column Content with the report definition in binary, which you can convert to XML with a query.

The resulting XML can then further be queried. I personally don't have much experience with this, but with just glancing at the query method and basic XQuery expressions I could easily come up with this:

DECLARE @rdl XML

SELECT @rdl = CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content))
FROM   Reportserver.dbo.Catalog C 
WHERE  C.ItemID = 'AAAAAAAA-1111-BBBB-2222-CCCCCCCCCCCC' -- Use your ID ;-)

SELECT @rdl.query('//*:Subreport')

Which would give a result similar to this:

<p1:Subreport xmlns:p1="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition" Name="SubReport">
  <p1:ReportName>SomeAwesomeSubreport</p1:ReportName>
  <p1:Parameters>
    <p1:Parameter Name="Param1">
      <p1:Value>=Fields!MyGreatField.Value</p1:Value>
    </p1:Parameter>
  </p1:Parameters>
  <p1:KeepTogether>true</p1:KeepTogether>
  <p1:Height>2.25cm</p1:Height>
  <p1:Width>21cm</p1:Width>
  <p1:Style />
</p1:Subreport>

From there it's still some work to get to a query that creates the "map/tree structure" you're after, but it should get you startedl. Unfortunately my SQL-fu is not yet awesome enough to just easily spit out that code, but I welcome you or anyone who's able to do so to add it to this answer.

Hope this helps.