Hey guys!
In this article, I would like to share with you how to use views and catalog tables from SQL Server Reporting Services (SSRS) to identify which reports are most accessed and which are not being used. This need ended up arising for me while serving a client, who is in the process of migrating RDL reports from Reporting Services to 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 already mentioned in the post  SQL Server Reporting Services (SSRS) – How to log in to view reports and identify which user is accessing, we can identify report runs using SSRS catalog views ExecutionLog%, which return various information about it, such as date/time of execution, user, parameters used and much more.

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

To view the current range of execution history data, you can use this query:

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 configuration is configured as 60 days. This happens because I wasn't consulting the reports in the last 60 days on my VM and I only consulted them in the last 2 days to make this post, so be aware of this when identifying unused reports in your environment.

Therefore, 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 server:

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 that will be deleted:

;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 = Paginated Report (RDL)
3 = File
4 = Linked Report
5 = Datasource
6 = Model
7 = ReportPart
8 = Shared Dataset
11 = KPI
12 = Mobile Report report (formerly Datazen)
13 = Power BI Report

Have you checked the results of the query above, made a backup of your ReportServer database and are you ready to delete these unused reports? Now you can use the script below to delete these reports:

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 consult our SQL Server Reporting Services (SSRS) or Power BI Report Server (PBIRS) server and check if the reports have been deleted:

Ready! Unused reports deleted.
Once again: remember to TEST and BACKUP the Report Server database before using these scripts.

Don't you know Power BI Report Server yet? Would you like to know more about this tool? Don't forget to check out my article Video – Power BI vs Reporting Services: Who is better?.

I hope you enjoyed this post and see you later!
Hug!