We’ve run into multiple occasions where we’ve seen environments with top-notch resources, up to date servers and not an overly abundant amount of data yet they still seem to experience performance issues while generating reports.
The report itself would take 30+ seconds to generate but then the opening of the MR Report Viewer application would drag on and one and sometimes take upwards of a minute to open and display the generate report. When the Report Viewer application opens, it calls the reports in the report library (to an extent) and typically isn’t a big deal. However, when you are working in an environment where Management Reporter reports are heavily relied on, the number of reports in the repository builds quickly. You’ll also find that there is no option within Management Reporter or the Report Viewer (as of CU14) to clear the repository reports or set any amount of time to store the historical reports before clearing. This is where a bit of SQL magic comes in handy.
The following query defines an integer that is used to define how many days worth of reports that you’d like to keep in your repository. In our example, we decided that only 10 days worth were necessary and if any reports past 10 days were ever needed that we’d simply re-generate the report.
Please note that this query may take an extended period of time to run, especially when being used as a first time. We also highly recommend running this in a Development environment to ensure the desired outcome is what’s expected before moving into production.
DECLARE @DaysBack integer Set @DaysBack = 10 delete reporting.reportlinetransaction where ReportID in (select id from reporting.report where RepositoryID in (select id from reporting.repository where StatusType = 0 and [Type] = 10 and CreateDate <= DATEADD(day,@DaysBack,getdate()))) delete reporting.ReportLineFinancial where reportid in (select id from reporting.report where RepositoryID in (select id from reporting.Repository where StatusType = 0 and [Type] = 10 and CreateDate <= dateadd(day,@DaysBack,getdate()))) delete reporting.ReportLineAccount where reportid in (select id from reporting.report where RepositoryID in (select id from reporting.Repository where StatusType = 0 and [Type] = 10 and CreateDate <= dateadd(day,@DaysBack,getdate()))) delete reporting.report where RepositoryID in (select id from reporting.Repository where StatusType = 0 and [Type] = 10 and CreateDate <= DATEADD(day,@DaysBack,getdate())) delete reporting.repository where statustype = 0 and [Type] = 10 and CreateDate <=DATEADD(day,@DaysBack,getdate())
After running this query, you should find that when generating your next report that Report Viewer will open up in a fraction of a time as before.
That’s all for today everyone. Please feel free to leave any comments or questions below and check out ALaCarteGP.com.
