Setting unused Reporting Services (SSRS) Reports invisible by script
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 */
As you can see there is a "Hidden" Column. We get the same view when browsing the folder in the ReportManager:
You can set this property in the settings in the ReportManager:
Checking this box will update the Dbo.Catalog-Table:
And the Report is not shown in the Overview-Page any more:
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%'
Or Connect the SQL Server Management Studio (SSMS) to the ReportServer Instance, right click on it and select properties. In the dialog select "Logging": 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.
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) Experienced readers will now tell me, that this only works in List-View but all Reports are still there in Details-View:
That's true:
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)
- #ui_btnSwitchView{ visibility: hidden;}
This option is gone: Good luck tiding up your Report Server!
Neuen Kommentar schreiben