SSRS: Searching The Reporting Database - Which Reports Include Subreport XXX?

I've been tasked with splitting several existing reports into two (one for one set of users, one for a different set) and while I was looking at using Linked Reports unfortunately the software program that actually does the pushing out of the reports to the end-users doesn't support Linked Reports.

There also doesn't seem to be a "Dependencies" link which would allow me to see what reports are dependent on the Sub report I've been asked to change.

Digging through various SQL examples that are out there there didn't seem to be anything to do exactly what I was after *without* making it unnecessarily complicated.

Here's the SQL I ended up with;

SELECT *
  FROM (SELECT *,
               CASE
                 WHEN LEFT(CONVERT(varbinary(max),Content),3) = 0xEFBBBF
                   THEN CONVERT(varbinary(max),
                                SUBSTRING(CONVERT(varbinary(max), Content),
                                          4,
                                          LEN(CONVERT(varbinary(max), Content))
                                         )
                                )
               ELSE
                 CONVERT(varbinary(max),Content)
               END AS ContentXML
  FROM Catalog C) AS C
 WHERE C.ContentXML LIKE '%Subreport%'
   AND C.ContentXML LIKE '%SUB_REPORT_NAME%'
   AND C.Path LIKE '/SUB_REPORT_FOLDER/%'

The point of including the sub-report folder is to only pick up items in a single folder (or sub-folder) as we have PROD, DEV, and TEST all on the same server (in different folders).

Hope this saves you the time it took me sorting it out!

Generating a "My Reports" In SQL Server Reporting Services (SharePoint Integrated Mode)

The idea behind this report is that most, if not all, of our users will need to keep running the same reports over and over again every month and so rather than coming up with some fantastic way of categorising the reports why not just write a report that looks at the logs and see which reports the user has run and how often and provide the user with this list to pick the report from?

Here's the screen capture of the final report;

My Reports - Sample Output

As you can see we took the opportunity to provide a method for users to provide suggests to correct the (laughably inaccurate) descriptions we hold of the reports into something a lot more meaningful for what the report does for them.

Now the next thing to say is that Microsoft does not support direct access to tables in either SharePoint or Reporting Services EXCEPT for SSRS access to the ExecutionLog* views. It also helps if, like us, you have consolidated your SQL Servers so that same server hosts both SharePoint's WSS_Content_ database AND the ReportServer database for SSRS.

Here is the SQL;

SELECT A."Report Name",
       AUD.ntext2          "Description",
       AUD.nvarchar12      "Status",
       AUD.tp_Version      "Version",
       A."Execution Count"
  FROM (SELECT C.NAME "Report Name", COUNT(*) "Execution Count"
          FROM ReportServer.dbo.Catalog C
          JOIN ReportServer.dbo.ExecutionLogStorage ELS
            ON C.ItemID = ELS.ReportID
         WHERE 1 = 1
           AND ELS.UserName = @Username
           AND ELS.Format = 'RPL'
           AND ELS.TimeStart > '01-JAN-2012'
           AND C.Type = 2 -- Report
           AND UPPER(C.Path) LIKE UPPER('%Reports LIVE%')
         GROUP BY NAME
        HAVING COUNT(*) >= @Min_Execution_Count
        ) A
  JOIN WSS_Content_Reporting.dbo.AllUserData AUD
    ON A."Report Name" = AUD.tp_LeafName
   AND AUD.tp_DirName = 'Reports LIVE'
   AND AUD.nvarchar3 = 'rdl'
   AND AUD.tp_IsCurrentVersion = 'True'
 ORDER BY A."Execution Count" DESC

As you can see this SQL joins the data in both the Catalog and ExecutionLogStorage tables in SSRS and the AllUserData table in SharePoint. The Execution details are restricted to just reports (ELS.Format  = 'RPL' and C.Type = 2) and, as our live reports are in a single Document Library in SharePoint, we have also restricted it to where the reports path (C.Path) contains the words "Reports LIVE" (the name of the directory).

Additional the details from SharePoint (AUD.ntext2, AUD.nvarchar12, etc) will vary depending on your configuration. If you don't have additional details stored in SharePoint (like descriptions) you can just drop that part of the SQL.

Finally we have two parameters @Username and @Min_Execution_Count. We use @Username as the user whose report execution history we're interested in and @Min_Execution_Count as a way of restricting the report to only display reports that have been executed over a certain number of times.

We order the results by the Execution Count in descending order so the most frequently run report is at the top.

Now that we've got the dataset set-up (I've called it "My reports By Frequency Used.rsd" and it makes use of a dataset in the "Data Sets" document library) the report which accesses it is available here - please note that in order to use it you need to save it as a normal text file. I have made the following replacements;

• xxxxx - This is the servername
• yyyyyy - This is the email address that the people clicking on "[Suggest Update]" will have their messages sent to