These days the request becoming quite common in many companies is to retire unused SSRS reports. This may take quite amount of time if you want to do this manually, especially in some companies dealing with hundreds of reports, organized under different sub folders. This article will provide a step-by-step guide to obsolete un-used reports programmatically by updating with custom message inside the report RDL’s.

Getting Started

In order to follow the examples, you will need to have SQL Server Reporting Services installed and configured. The code folder contains usagemetrics.sql, DummyReport.rdl, ObsoleteReportsFromFile.rss, and reports.txt file.

Identifying Un-used Reports

SSRS Reports Usage metrics can be derived by Querying Report Server DB. Use the below T-SQL to derive the usage counts. If your report server is configured to store 1 year of reports execution history, we can use a specific date to filter the usage metrics. In my query I am checking all the metrics from the beginning of 2016.

with cte
as
(
SELECT
cat.Name,Path,COUNT(ex.TimeStart) as Cou
FROM  (select * from Catalog
where type=2 and Hidden=0)AS cat
left join
ExecutionLog AS ex
on ex.ReportID = cat.ItemID
and ex.TimeStart>'01/01/2016'
Group BY cat.Name,Path)
select * from cte
order by COU asc,path

As per my example shown in the below Figure1, I can see there are two reports, Report2 and Report3, which were not executed at least once since beginning of the year, so I will be considering obsoleting these two reports and updating them with a custom message.

Figure 1

Step 1: Once we have the list of reports to be obsoleted, we can then copy the path of those reports from the sql query output in to a text file called reports.txt, as shown in Figure 2 below.

Figure 2

Step 2: The next step is to prepare the .rss file by updating the correct location for the Dummyreport RDL file and reports.txt file, as shown in below Figure 3.

Figure 3

Step 3: The next step is to open a command prompt and call the rs.exe utility by passing below parameters as shown in Figure 4.

C:\> rs.exe  -S <Report Server URL> - i <Location of .rss file>

Figure 4

Once we call the above Command Script we will be seeing the below messages saying the report was successfully replaced with the stub, as shown in Figure 5. That means all the reports that are supposed to be obsoleted will be replaced with the DummyReport.RDL. As part of this deployment reports will also be hidden on the Tile view. Users won’t be seeing the report in Report Manager in the detail view, but whoever has the report links saved as a bookmark will be redirected to the same report that was updated with the DummyReprt RDL content.

Figure 5

Testing Out the Reports under Tile View

Now we can see that under the Report Manager Folders that these two reports, Report2 and Report3, are hidden, as shown in below Figure 6.

Figure 6

When we try to browse the report, we will see the custom message, as shown in Figure 7

Figure 7

Hopefully this will help you remove old reports and replace them with a message to your users.