SQL Server Reporting Services (SSRS) - Which reports are most accessed? And which are not being used?

Views: 824
Reading Time: 5 minutes

Hello everybody!
In this article, I'd like to share with you how to use SQL Server Reporting Services (SSRS) catalog views and tables to identify which reports are most accessed and which are not being used. This need eventually arose for me while serving a customer, who is in the process of migrating Reporting Services RDL reports to Power BI Report Server, and he would like to take advantage of this move to identify and remove reports that are no longer being used. .

As I had commented in the post SQL Server Reporting Services (SSRS) - How to log the report view and identify which user is accessing, we can identify report runs using SSRS catalog views ExecutionLog%, which returns various information about it, such as execution date / time, user, parameters used, and more.

This information is automatically saved by default by SSRS, which stores by default the report execution data of the last 60 days, information which we can check by Management Studio (SSMS) on the SSRS properties screen:

To see what the current range of run history data is, you can use this query:

Result:

Note that in the example above, the query brought only 2 days of history, even though the storage setting is set to 60 days. This is because I was not consulting the reports for the last 60 days on my VM and only consulted for the last 2 days to make this post, so be aware of this when identifying unused reports in your environment.

So in this quick, simple and objective post, I'd like to share with you a query that lets you identify which reports are most commonly accessed and which are no longer being used on your SQL Server Reporting Services (SSRS) or Power BI Report Server server:

Execution Result:

I want to delete unused reports

What if you want to delete unused reports? Imagine that there are several unused reports and you want to remove them without having to open report by report. I will share a T-SQL code to do this. REMEMBER TO TEST BEFORE!

Query to validate the files to be deleted:

Query Result:

List of reports from my server:

Remembering that the available types are:
1 = Directory
2 = Paged Report (RDL)
3 = File
4 = Linked Report
5 = Data Source (Datasource)
6 = Model
7 = ReportPart
8 = Shared Dataset
11 = KPI
12 = Mobile Report Report (formerly Datazen)
13 = Power BI Report

Have you checked the result of the above query, backed up your ReportServer database, and are you ready to delete these unused reports? You can now use the script below to delete these reports:

Now let's look at our SQL Server Reporting Services (SSRS) or Power BI Report Server (PBIRS) server and see if the reports have been deleted:

Ready! Unused reports deleted.
Again: Remember to TEST and BACK UP your Report Server database before using these scripts.

Don't know Power BI Report Server yet? Would you like to know more about this tool? Be sure to check out my article Video - Power BI vs. Reporting Services: Who's Better?.

I hope you enjoyed this post and even more!
Hug!