SQL Server Reporting Service : Multiple reports deletion from Report Manager at One Go

Hello Folks!!

This time I comes up with one of the very interesting topic of the SQL Server Reporting Services, this will allow user to get rid of the cumbersome task of deleting reports manually and in fact save lot of time that can be utilised in other more important productive work.

Now a days, every organization are indulging into migration of their deliverables from one location to another, sometime for better technology and sometime for removing all the obsolete objects from their environment, let's suppose your organization is also following the same path and on one very good morning of your work day, you will receive request from the client to remove all unwanted or obsolete reports that they have in our very own Report Manager, sounds good and affirmative but when you explored the details of the request then you get to know that the number of reports are quite high in number however your client provided you the list of all the reports along with the path or URL of all the reports those are resides on Report Manager.

Such kind of request are actually very time consuming and if one has to go with traditional way of deleting report one by one after navigating reports over the Report Manager, it will take ages to complete the process, its not only about time, it also quite frustrating, tedious and cumbersome task to deal with.

Let's explore the possibility of optimised solution for such kind of client request, let's discuss in detail.

Let's start the road roller with some illustration.

Requirement :

Let's say you are working in Sales organization and there are numerous reports residing on Report Manager of SQL Server Reporting Services, you get a JIRA request stating to delete OR remove all the reports from the Report Manager those are obsolete or Not in use any more (they have also provided list of report along with path of all the reports in Report Manager) reason being, client really do not want to waste its time, money and resources for those reports which are useless for them and they will not use it any time in future.

Resolution : 

Though, request is no doubt is very cumbersome and no one wants to work on such activity, but some one has to perform it as it is the legitimate request and client is paying for the same.

One could be solution is to delete all the reports one by one manually but for sure it will require more time and more human efforts, in addition, doing activity manually in itself is very cumbersome and tedious task to perform, so in ideal scenario, this must not be in the list of the solution, even, if it is, it must be last thing in the world to progress with.

So, lets move towards the actual solution of the request, but I would like to explain few things before elaborating the solution as these will be key part of the solution.

What is Report Manager ?

Report Manager is nothing but a repository which is available over the internet (or cloud) on which all reports will be hosted, user who having required access can only access the reports.

What is RSS Extension File?

RSS Extension file is what we call it is rich Summary file which is saved on XML kind of format, one can write code on the same and execute it via Command Prompt (CMD).

What is VB Script ?

VB Script is kind of scripting language where we can write a code to perform certain activity, it is same as C# Script with only difference is use of different programming language.

What is Command Prompt (CMD) ?

CMD is default command-line interpreter and also known as CMD.exe

Definition provided above for the objects are at glance, if you want to have more detail on the subject you can refer them in detail over the internet.

As of now, you understood well all the basic terminology that are going to use in this very subject, lets start with the solution.

One of the handful and most useful method to delete multiple reports is to use RSS file for the deletion of reports, herein, I am detailing out all the steps that needs to perform this activity.

Delete one Report at a time

Step 1 : Open Notepad

Step 2 : Click on Save As  OR HIT < CTRL + SHIFT + S >  

Step 3 : Under Save As Type , select All Files

Step 4 : Provide File Name as TestDelete.rss

Step 5 : Write below VB - Script  inside the file  

    Public Sub Main()
        rs.DeleteItem("/Report_test/TestReport/")
    End Sub

* Below is the snapshot of the .RSS file that was created in above steps.

                                

Step 6 : Place TestDelete.rss at any location where ever your organization has or you want.

Note - /Report_Test/TestReport : Root Folder in Report Manager, i.e. in this case, we have folder named Report_test at root on Report Manager and TestReport.rdl  is RDL file inside that folder.

Step 7 : Herein, the idea is to call TestDelete.rss from CMD (Command Prompt), Now, with the help of CMD command, we can delete above aforementioned report, please find snapshot below.

                                

Hurray !!! Here we go, we got the desired result.

Required report got deleted from the Report Manager as soon as we have executed above CMD.

Delete multiple Reports at a time

One might thinks, aforementioned procedure can only delete one report at a time, then what is the difference between deleting everything manually one by one and using above procedure and providing report name one by one and deleting report, so Yes, you are absolutely correct, literally, they are more or less same but later will take bit less time than former however level of frustration and monotonous work will remain the same.

Just to enhance and make this procedure helpful to remove multiple report at a matter of time, we need to do little change in the VB Script and then we will be able to delete multiple reports at one go.

Below are the complete steps to perform the activity.

Step 1 : Open Notepad

Step 2 : Click on Save As  OR HIT < CTRL + SHIFT + S >  

Step 3 : Under Save As Type , select All Files

Step 4 : Provide File Name as TestDelete.rss

Step 5 : Write below VB - Script inside the file  

    Public Sub Main()
        rs.DeleteItem("/Report_test/TestReport/")
        rs.DeleteItem("/Report_test/TestReport1/")
    End Sub

Note - Just to include one more row to the VB-Script, highlighting in RED above, if you want to delete n number of rows, you need to include all the report path one by one as shown in the Step-5, as we have all list of path of complete report, so it won't be time consuming task to accumulate all the report path , may be MS- Excel will help you in gather list of path of report in quick time.

* Below is the snapshot of the .RSS file that was created in above steps       

                                      

Step 6 : Place TestDelete.rss at any location where ever your organization has or you want.

Step 7 : Herein, the idea is to call TestDelete.rss from CMD (Command Prompt), Now, with the help of CMD command, we can delete above aforementioned report, please find snapshot below.

                                

Hurray !!! Here we go, we got the desired result.

Pros and Cons

Pros 

1. This will help you to delete multiple reports at one and consume less time.

Cons 

1. VB Script and CMD knowledge is required to perform aforementioned activity.

Author - Feel free to ask query if you have any query