Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.