Setting unused Reporting Services (SSRS) Reports invisible by script

Technical Value

In this post I'll show you, how an administrator of SQL Server Reporting Services Instance (SSRS) can identify "not used for a while"-reports and set them invisible for the clients. If they are invisible and not missed by any user, you can download the definition, backup it and delete them on the server. Speeding up the browsing the ReportManager.

Giving the situation we have a SQL Server Reporting Services Instance SSRS, the system is running for some years now. And PowerUsers are allowed to develop and deploy reports on the system. The reports are spread over several folders for controlling or sales or marketing and the guys in the logistic centre also need new reports on regular basis. As time goes by there are hundreds of reports deployed, containing names like 'backup' or 'copy', sometimes even 'copy_23' … You know what I mean?

But which of these reports are still in use and which weren't used for long time now. All these reports fill up the folders and users might not know which one is the current one? Now we'd like to tidy up the ReportServerManager and present only the In-Use Reports to the user.

To achieve this we need two information.

which Reports are existing
were they used the last time.

The good thing is we can get this information from the ReportServer-Database. First, let's take a look on the list of all existing reports:

SELECT
[ItemID]
,[Path]
,[Name]
,[ParentID]
,[Type]
,[Hidden]
,[Content]
,[Intermediate]
,[SnapshotDataID]
,[LinkSourceID]
,[Property]
,[Description]
,[CreatedByID]
,[CreationDate]
,[ModifiedByID]
,[ModifiedDate]
,[MimeType]
,[SnapshotLimit]
,[Parameter]
,[PolicyID]
,[PolicyRoot]
FROM
[dbo].[Catalog]
where Type= 2
/*
Type Name
1 Folder
2 Report
3 Resource
4 Linked Report
5 Data Source
6 Report Model
8 Datasets
9 ReportParts */

Table with Query Results As you can see there is a "Hidden" Column. We get the same view when browsing the folder in the ReportManager: Overview Reports in Report Manager You can set this property in the settings in the ReportManager: Report Details Checking this box will update the Dbo.Catalog-Table: Query Results with Hidden Change And the Report is not shown in the Overview-Page any more: Report Manager Overview without hidden Report Second information we need is when was this report used the last time. In the Report-Server-Default-Setting it tracks all report executions and keeps them for 60 days. You can check and set this option here:

SELECT
[ConfigInfoID]
,[Name]
,[Value]
FROM [dbo].[ConfigurationInfo]
where Name like '%Execution%'

Query Results

Or Connect the SQL Server Management Studio (SSMS) to the ReportServer Instance, right click on it and select properties. In the dialog select "Logging": Server Properties If this setting is enabled we can use the already existing ExecutionLog-Views in the SSRS. With every new Version of SSRS a new View was added. Views of ExecutionLog To achieve the most downward compatibility I'll use in this post the "dbo.ExecutionLog"

SELECT
[InstanceName]
,[ReportID]
,[UserName]
,[RequestType]
,[Format]
,[Parameters]
,[TimeStart]
,[TimeEnd]
,[TimeDataRetrieval]
,[TimeProcessing]
,[TimeRendering]
,[Source]
,[Status]
,[ByteCount]
,[RowCount]
FROM [dbo].[ExecutionLog]

As we know from the configuration above here are all reports listed that were executed within the last 60 days. Having said this, we also know all Reports not listed here were not executed within the last 60 days. Now we know about all the existing Reports and do have information about their executions. Let's combine the information:

SELECT
[ItemID]
,[Path]
,[Name]
,[ParentID]
,[Type]
,[Hidden]
,[Content]
,[Intermediate]
,[SnapshotDataID]
,[LinkSourceID]
,[Property]
,[Description]
,[CreatedByID]
,[CreationDate]
,[ModifiedByID]
,[ModifiedDate]
,[MimeType]
,[SnapshotLimit]
,[Parameter]
,[PolicyID]
,[PolicyRoot]
FROM [dbo].[Catalog]
where Type = 2
and ItemId not in (Select ReportId from dbo.ExecutionLog)

This shows the not-used-reports. Using this information in an update-query:

UPDATE [dbo].[Catalog]
SET Hidden = 'true'
WHERE Type = 2 and
ItemId not in (Select ReportId from dbo.ExecutionLog)

And all unused reports are gone (did this for all reports in my example to show the difference) Overview Sample Reports Experienced readers will now tell me, that this only works in List-View but all Reports are still there in Details-View: Part of Details View That's true: Dateils View But when you add this small code to your ReportingServices.css file (Default Location: C:\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\ReportManager\Styles)