Obsoleting Unused SSRS Reports

Question

Obsoleting Unused SSRS Reports

amarreddy23

Ten Centuries

Points: 1361
More actions
April 26, 2017 at 12:00 am

#323138

Comments posted to this topic are about the item Obsoleting Unused SSRS Reports
Thanks,
AMAR
amarreddy23@gmail.com

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Piquet SSCommitted Points: 1528 More actions · Answer 1

Warning: You will need to be careful with any sub-reports that are defined and deployed - sub-reports do NOT appear in the execution log when the "parent" report is executed - only the parent report executed, hence sub-reports can easily be mistaken for an obsolete/unused report...

spampirate SSC Journeyman Points: 85 More actions · Answer 2

I would add a check to see if the reports have any subscriptions and check if there are any reports that are a linked report to the ones being removed and if the linked report has any subscriptions. I also just like to move the rdls to a folder hidden to most everyone so I can easily restore a report if needed.

Sue_H SSC Guru Points: 90860 More actions · Answer 3

amarreddy23 - Tuesday, April 25, 2017 11:46 PM
Comments posted to this topic are about the item Obsoleting Unused SSRS Reports

There has been a couple or more of these recently with queries of the ExecutionLog views. One of the problems I keep seeing is an assumption of how long the data is being retained in the table and subsequent views. There are those who may not even know where to change it so something like a quarterly report would be seen as obsolete due to the default retention settings. Or what if someone has changed this value and the retention is set to 7 days.
It seems that It may be good to check the ExecutionLogDaysKept in the ConfigurationInfo table and base some logic off of that. I suppose you could go further and as alluded to in the previous post, also check some of the schedules for subscriptions since there could be a once in the future or every XX number of months, or on the XX month...things along those lines. I'm not sure that just querying the execution log can tell you a report is obsolete or not being used.

Sue

Matthew Cushing SSCrazy Points: 2898 More actions · Answer 4

Having subscription and linked report look ups would definitely tie this article up nicely 🙂 Thanks for posting this, in the process of going through our reports to do an audit and clean things up.

*edit 1:14pm* added a sql script to look at subscriptions

flbotts 29605 SSC Enthusiast Points: 125 More actions · Answer 5

Where do you find the *.rss file in step 3??? Everything I can find points to C:\Program Files\Microsoft SQL Server\110\Tools\Binn
But I do not see it or the RS.exe either.

amarreddy23 Ten Centuries Points: 1361 More actions · Answer 6

Please download the attached code , you will find a ObsoleteReportsFromFile.rss file.

regarding Rs.exe Can you look into 32 bit folder.
C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn..

Thanks,
AMAR
amarreddy23@gmail.com

jhill-959840 SSC Veteran Points: 245 More actions · Answer 7

Thanks! How can I edit the DummyReport after running the ObsoleteReportsFromFile.rss? I updated the rdl and re-ran the ObsoleteReportsFromFile.rss code but my DummyReport on the reportserver did not update. Any ideas?

amarreddy23 Ten Centuries Points: 1361 More actions · Answer 8

Sorry , Not sure if i get your Question correctly.
DummyReport.Rdl is the Report that we are going to overwrite the content of all the reports that we are going to Obsolete.
Once you run the Rss file it will overwrite the target Report that are listed on reports.txt file.

Thanks,
AMAR
amarreddy23@gmail.com

jhill-959840 SSC Veteran Points: 245 More actions · Answer 9

jhill-959840

SSC Veteran

Points: 245

April 27, 2017 at 6:37 am

#1940188

Thanks, I understand the process now.

jhill-959840 SSC Veteran Points: 245 More actions · Answer 10

Piquet - Wednesday, April 26, 2017 1:59 AM
Warning: You will need to be careful with any sub-reports that are defined and deployed - sub-reports do NOT appear in the execution log when the "parent" report is executed - only the parent report executed, hence sub-reports can easily be mistaken for an obsolete/unused report...

I would further add to this that if you have linked reports that you remove them from the list. They also do not appear in the execution log when the linked report is executed.

chudman SSCrazy Points: 2453 More actions · Answer 11

I get errors from the command prompt when referencing the .rss file basically stating that the last line is incorrect : Unused local variable: 'report'. referencing the line "Dim report AS String". While that appears to just be a warning, I also get this error:

Error BC30289: Statement cannot appear within a method body. End of method assumed.

This is all from VB compiler.

Any thoughts?

Thanks,
Jeff Bennett

sstanek 27058 SSC Enthusiast Points: 142 More actions · Answer 12

Moving on from what you showed (thanks much btw, had no grounding in the Reports db) I took what you laid out and made of it a listing of report uses with the username exposed. This way, not only can I tell which reports have not been used much, I can filter out my own uses --mostly for validation after making a change.

As others long ago noted, subreports show no uses. I would also add that any reports used as chart objects in dashboards also show no activity, so a naming convention that clearly marks them as such is helpful.

Scott Stanek