Thoughts on cleaning up old custom reports in prep for v16

Nathaniel Baker said:

I'm curious what DBA's are doing in preparation for the upgrade with old custom reports. I know when I check, some of our custom reports haven't been used in a while. Like, years.

Since 2016, we've inactivated any report not used in 25 months via a SA job, which is archiving of a sort. I turned that off for a while during 2020-21, but it's back on now. It's interesting to see what isn't getting used. Prior to that I would actually get complaints from time to time that report menus were too long(!).

Occasionally someone will ask for something to be re-activated, but not very often.

Does anyone have a query handy to quickly look at the last time a report was run?  It would be good just to spot check this in SSMS.

There's a canned Custom Report Run Frequency report - can you take the code from that and edit?

There's a report in the Version Upgrade folder within Tessitura that will tell you that! It's called Custom REport Run Frequency

Something like this, see what you get, and, if desired, set matching rows in gooesoft_report to inactive = 'Y'.

select gr.id
	,gr.name
    ,count(gr.id) as run_count
    ,min(gq.request_date_time) as first_run_dt
    ,max(gq.request_date_time) as last_run_dt
		into #idle_reports
from gooesoft_request gq
join gooesoft_report gr on gr.id = gq.report_id
where gr.inactive = 'N'
group by gr.id
	,gr.name
having max(gq.request_date_time) < getdate() - 750 -- 25 mos ago
order by last_run_dt desc

Something like this, see what you get, and, if desired, set matching rows in gooesoft_report to inactive = 'Y'.

select gr.id
	,gr.name
    ,count(gr.id) as run_count
    ,min(gq.request_date_time) as first_run_dt
    ,max(gq.request_date_time) as last_run_dt
		into #idle_reports
from gooesoft_request gq
join gooesoft_report gr on gr.id = gq.report_id
where gr.inactive = 'N'
group by gr.id
	,gr.name
having max(gq.request_date_time) < getdate() - 750 -- 25 mos ago
order by last_run_dt desc

Thanks Chris, will give it a try!