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",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).
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
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
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