Cleaning Up Temporary SSRS Reports

Question

Our application uses SQL Server Reporting Services and allows users to add custom filters to reports. We do this by modifying the RDL and then uploading the modified RDL to the server to create a new report. The problem is that after the report has run once, it's no longer needed; it's really just a temporary report. Obviously, this would eventually result in a lot of temporary reports laying around. We need a way to clean these up.

We've already thought about external methods like creating a service or job to periodically delete the reports, and that's probably what we'll end up doing if we can't come up with something better. What we're wondering is, does SSRS itself provide a better way to do this? We thought about trying to somehow use a cached instance which would be set to expire, but that seems to only works on an executed instance of a report not the report itself. As far as I can tell there's no way to set a report to expire. Is there some other way to get SSRS to clean up for us?

Immediately deleting the report isn't an option because our execution is asynchronous.

Answer 1

Hi Todd,

Built-in, there's nothing. But writing something yourself is easy enough.

Try having a process which queries your catalog of reports for ones that are older than half an hour (or so). You could even join to ReportServerTempDB to see if they still have an active session (in which case, you ignore them a bit longer).

Once you've found them, it's easy to grab that using the Web Service interface and delete them from the catalog.

But... I'd actually look at a better way of providing the custom filter, using code. Surely you could provide the filter as a parameter, and use the VB code within the report to convert what the user provides into something which could be evaluated for each row.

Rob