Hello everybody!
In this article, I would like to share with you how to use SQL Server Reporting Services (SSRS) views and catalog tables to identify which reports are most accessed and which are not being used. This need came to me during the service to a customer, who is in the process of migrating RDL reports from Reporting Services to the Power BI Report Server and he would like to take advantage of this movement 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:
| 1 2 3 4 5 6 7 8 | SELECT MIN(TimeStart) AS Execucao_Mais_Antiga, MAX(TimeStart) AS Execucao_Mais_Nova FROM ReportServer..ExecutionLog3 WHERE RequestType = 'Interactive' AND ItemAction LIKE 'Render%' |
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 would like to share with you a query that allows you to identify which reports are most accessed and which are no longer being used on your SQL Server Reporting Services (SSRS) or Power BI Report Server:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 | SELECT A.[Path], COUNT(DISTINCT B.TimeStart) AS [Quantidade de Views], MIN(B.TimeStart) AS Execucao_Mais_Antiga, MAX(B.TimeStart) AS Execucao_Mais_Nova FROM ReportServer..[Catalog] A LEFT JOIN ReportServer..ExecutionLog3 B ON A.[Path] = B.ItemPath AND B.RequestType = 'Interactive' AND B.ItemAction LIKE 'Render%' WHERE A.[Type] IN (2, 12) GROUP BY A.[Path] ORDER BY 2 DESC |
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:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 | ;WITH relatorios_nao_utilizados AS ( SELECT * FROM ReportServer..[Catalog] A LEFT JOIN ReportServer..ExecutionLog3 B ON A.[Path] = B.ItemPath AND B.RequestType = 'Interactive' AND B.ItemAction LIKE 'Render%' WHERE A.[Type] IN (2, 12, 13) -- Relatório paginado (RDL) / Relatório móvel / Relatório Power BI AND B.TimeStart IS NULL ) SELECT A.ItemID, A.[Path], A.[Description], A.[Hidden], (CASE A.[Type] WHEN 1 THEN 'Diretório' WHEN 2 THEN 'Relatório Paginado (RDL)' WHEN 3 THEN 'Arquivo' WHEN 4 THEN 'Linked Report' WHEN 5 THEN 'Fonte de dados (Datasource)' WHEN 6 THEN 'Model' WHEN 7 THEN 'ReportPart' WHEN 8 THEN 'Conjunto de dados compartilhado (Shared Dataset)' WHEN 11 THEN 'KPI' WHEN 12 THEN 'Relatório do Mobile Report (antigo Datazen)' WHEN 13 THEN 'Relatório do Power BI' END) AS [Type], A.CreationDate, C.UserName AS CreatedBy, A.ModifiedDate, D.UserName AS ModifiedBy, A.ParentID FROM ReportServer..[Catalog] A JOIN relatorios_nao_utilizados B ON A.ParentID = B.ItemID OR A.ItemID = B.ItemID JOIN ReportServer.dbo.Users C ON C.UserID = A.CreatedByID JOIN ReportServer.dbo.Users D ON D.UserID = A.ModifiedByID UNION SELECT A.ItemID, A.[Path], A.[Description], A.[Hidden], (CASE A.[Type] WHEN 1 THEN 'Diretório' WHEN 2 THEN 'Relatório Paginado (RDL)' WHEN 3 THEN 'Arquivo' WHEN 4 THEN 'Linked Report' WHEN 5 THEN 'Fonte de dados (Datasource)' WHEN 6 THEN 'Model' WHEN 7 THEN 'ReportPart' WHEN 8 THEN 'Conjunto de dados compartilhado (Shared Dataset)' WHEN 11 THEN 'KPI' WHEN 12 THEN 'Relatório do Mobile Report (antigo Datazen)' WHEN 13 THEN 'Relatório do Power BI' END) AS [Type], A.CreationDate, D.UserName AS CreatedBy, A.ModifiedDate, E.UserName AS ModifiedBy, B.ParentID FROM ReportServer..[Catalog] A JOIN ReportServer..[Catalog] B ON A.ParentID = B.ItemID JOIN relatorios_nao_utilizados C ON B.ParentID = C.ItemID OR B.ItemID = C.ItemID JOIN ReportServer.dbo.Users D ON D.UserID = A.CreatedByID JOIN ReportServer.dbo.Users E ON E.UserID = A.ModifiedByID ORDER BY A.ParentID, A.ItemID |
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:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 | DECLARE @Items TABLE ( ItemID UNIQUEIDENTIFIER, [Path] NVARCHAR(255) ) ;WITH relatorios_nao_utilizados AS ( SELECT * FROM ReportServer..[Catalog] A LEFT JOIN ReportServer..ExecutionLog3 B ON A.[Path] = B.ItemPath AND B.RequestType = 'Interactive' AND B.ItemAction LIKE 'Render%' WHERE A.[Type] IN (2, 12, 13) -- Relatório paginado (RDL) / Relatório móvel / Relatório Power BI AND B.TimeStart IS NULL ) INSERT INTO @Items SELECT A.ItemID, A.[Path] FROM ReportServer..[Catalog] A JOIN relatorios_nao_utilizados B ON A.ParentID = B.ItemID OR A.ItemID = B.ItemID JOIN ReportServer.dbo.Users C ON C.UserID = A.CreatedByID JOIN ReportServer.dbo.Users D ON D.UserID = A.ModifiedByID UNION SELECT A.ItemID, A.[Path] FROM ReportServer..[Catalog] A JOIN ReportServer..[Catalog] B ON A.ParentID = B.ItemID JOIN relatorios_nao_utilizados C ON B.ParentID = C.ItemID OR B.ItemID = C.ItemID JOIN ReportServer.dbo.Users D ON D.UserID = A.CreatedByID JOIN ReportServer.dbo.Users E ON E.UserID = A.ModifiedByID ----------------------------------------------------------------------- -- CUIDADO! AQUI ESTAMOS APAGANDO OS SEUS RELATÓRIOS NÃO UTILIZADOS ----------------------------------------------------------------------- -- Apaga o relacionamento do relatório com o conjunto de dados DELETE A FROM ReportServer.dbo.DataSets A JOIN @Items B ON B.ItemID = A.ItemID -- Apaga os relatórios e arquivos relacionados DELETE A FROM ReportServer.dbo.[Catalog] A JOIN @Items B ON B.ItemID = A.ItemID |
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!
Excellent article. Very well explained. Thank you!
Excellent article. Only something went wrong here… Msg 547, Level 16, State 0, Line 51
The DELETE statement conflicted with the REFERENCE constraint “FK_DataSourceItemID”. The conflict occurred in database “ReportServer”, table “dbo.DataSource”, column 'ItemID'.
If anyone knows the cause it can be helpful and save me time because I will have to exclude the least used here from the base.
Solution used: I deleted the FK, deleted the reports, restored the FK. Done.
USE [ReportServer]
GO
ALTER TABLE [dbo]. [DataSource] DROP CONSTRAINT [FK_DataSourceItemID]
GO
–AQUI RUN DELETE SCRIPT
ALTER TABLE [dbo]. [DataSource] WITH NOCHECK ADD CONSTRAINT [FK_DataSourceItemID] FOREIGN KEY ([ItemID])
REFERENCES [dbo]. [Catalog] ([ItemID])
GO
ALTER TABLE [dbo]. [DataSource] CHECK CONSTRAINT [FK_DataSourceItemID]
GO