Microsoft ne documente pas ou ne prend pas en charge l’interrogation de la base de données ReportServer, mais elle contient beaucoup d’informations utiles sur les rapports SSRS. Dans cet article, nous explorerons comment interroger la base de données ReportServer pour générer une documentation en direct des rapports SSRS.

Rapports exécutés au cours des 30 derniers jours

Une façon d’analyser l’utilisation des rapports SSRS consiste à interroger la table ReportServer.dbo.ExecutionLog. Par défaut, SQL Server ne stocke que 30 jours de données dans cette table. La requête suivante peut être utilisée pour déterminer quels rapports ont été exécutés au cours du dernier mois :

BEGIN TRY
    DROP TABLE #temp01
END TRY
BEGIN CATCH
END CATCH

SELECT DISTINCT
    SUBSTRING(t2.Path, 1, LEN(t2.Path) - LEN(t2.Name)) AS Dossier,
    t2.Name,
    REPLACE(t1.UserName, 'JACKPOT\', '') AS NomUtilisateur2,
    MAX(t1.TimeStart) AS DernièreExécution
INTO #temp01
FROM ReportServer.dbo.ExecutionLog t1
JOIN ReportServer.dbo.Catalog t2 ON t1.ReportID = t2.ItemID
GROUP BY t2.Path, t2.Name, t1.UserName
ORDER BY Dossier, NomUtilisateur2

SELECT DISTINCT
    Dossier AS DossierRapport,
    Name AS NomRapport,
    COUNT(Name) AS NombreExécutions,
    MAX(DernièreExécution) AS DateDernièreExécution,
    ExécutéPar = STUFF((
        SELECT ', ' + NomUtilisateur2 FROM #temp01
        WHERE Name = x.[Name]
        FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '')
FROM #temp01 x
WHERE Name <> ''
GROUP BY Dossier, Name
ORDER BY NombreExécutions DESC, DossierRapport, Name

Cette requête renverra une liste de rapports qui ont été exécutés au cours des 30 derniers jours, ainsi que le nombre de fois où ils ont été exécutés et les utilisateurs qui les ont exécutés.

Rapports non exécutés au cours des 30 derniers jours

D’autre part, il est également important d’identifier les rapports qui ne sont plus utilisés. La requête suivante peut être utilisée pour trouver les rapports qui n’ont pas été exécutés au cours des 30 derniers jours :

BEGIN TRY
    DROP TABLE #temp01
END TRY
BEGIN CATCH
END CATCH

BEGIN TRY
    DROP TABLE #temp02
END TRY
BEGIN CATCH
END CATCH

WITH RapportsClassés AS (
    SELECT ReportID,
        TimeStart,
        UserName, 
        RANK() OVER (PARTITION BY ReportID ORDER BY TimeStart DESC) AS iRank
    FROM ReportServer.dbo.ExecutionLog t1
    JOIN ReportServer.dbo.Catalog t2 ON t1.ReportID = t2.ItemID AND t2.Type <> 1
)
SELECT DISTINCT
    t1.UserName,
    t2.Name AS NomRapport,
    SUBSTRING(t2.Path, 2, LEN(t2.Path) - LEN(t2.Name) - 1) AS Dossier,
    t2.Type
INTO #temp01
FROM RapportsClassés t1
INNER JOIN ReportServer.dbo.Catalog t2 ON t1.ReportID = t2.ItemID
WHERE t1.iRank = 1
AND t2.Type <> 1
ORDER BY t1.UserName, t2.Name;

SELECT 
    SUBSTRING(Path, 2, LEN(Path) - LEN(Name) - 1) AS DossierRapport,
    Name AS NomRapport,
    CreationDate,
    ModifiedDate,
    Type
INTO #temp02
FROM ReportServer.dbo.Catalog 
WHERE Name NOT IN (SELECT NomRapport FROM #temp01) 
AND Path <> '' 
ORDER BY Path

SELECT * FROM #temp02 WHERE DossierRapport <> '' AND Type = 2 ORDER BY ModifiedDate DESC

Cette requête renverra une liste de rapports qui n’ont pas été exécutés au cours des 30 derniers jours. Elle peut aider à identifier les rapports qui ne sont peut-être plus nécessaires ou qui peuvent être archivés.

Conclusion

En interrogeant la base de données ReportServer, nous pouvons obtenir des informations sur l’utilisation des rapports SSRS. Les requêtes présentées dans cet article nous permettent d’identifier les rapports qui sont largement utilisés et ceux qui ne le sont plus du tout. Ces informations peuvent être précieuses pour optimiser l’utilisation et la maintenance des rapports.