none
How to get List of SSRS Reports called via an Action

    Question

  • How to get a list of SSRS Reports called via an Action and their corresponding calling report.
    Tuesday, May 21, 2019 12:40 PM

Answers

  • Below query will return the list of reports that have driithrough report  as action. Note that this is written for SSRS 2008 R2

    select

           name,

           substring(convert(varchar(max),convert(varbinary(max), content)),charindex('<ReportName>',convert(varchar(max),convert(varbinary(max), content)) )+12,   charindex('</ReportName>',convert(varchar(max),convert(varbinary(max), content)) )  - charindex('<ReportName>',convert(varchar(max),convert(varbinary(max), content)) )-12)

    from catalog with (nolock)

    where convert(varchar(max),convert(varbinary(max), content)) like '%<ActionInfo>%<Drillthrough>%<ReportName>%'

    Also a report may have more than one action defined.. in that case you might want to put this in a loop and change the substring parameters accordingly.


    Suresh
    My Scribbling

    • Marked as answer by zl34 Wednesday, May 22, 2019 12:46 AM
    Tuesday, May 21, 2019 1:30 PM

All replies

  • Below query will return the list of reports that have driithrough report  as action. Note that this is written for SSRS 2008 R2

    select

           name,

           substring(convert(varchar(max),convert(varbinary(max), content)),charindex('<ReportName>',convert(varchar(max),convert(varbinary(max), content)) )+12,   charindex('</ReportName>',convert(varchar(max),convert(varbinary(max), content)) )  - charindex('<ReportName>',convert(varchar(max),convert(varbinary(max), content)) )-12)

    from catalog with (nolock)

    where convert(varchar(max),convert(varbinary(max), content)) like '%<ActionInfo>%<Drillthrough>%<ReportName>%'

    Also a report may have more than one action defined.. in that case you might want to put this in a loop and change the substring parameters accordingly.


    Suresh
    My Scribbling

    • Marked as answer by zl34 Wednesday, May 22, 2019 12:46 AM
    Tuesday, May 21, 2019 1:30 PM
  • Thank you so much.  This will do the trick.
    Wednesday, May 22, 2019 12:46 AM
  • This code should also work for current version, since the category table remain the same.

    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, May 22, 2019 1:54 AM