Obsoleting Unused SSRS Reports

  • Comments posted to this topic are about the item Obsoleting Unused SSRS Reports

    Thanks,
    AMAR
    amarreddy23@gmail.com

  • 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 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.

  • 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

  • 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

  • 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.

  • 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

  • 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?

  • 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

  • Thanks, I understand the process now.

  • 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.

  • 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

  • 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

     

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

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