I had a consulting engagement with a well-known Fortune 100 company. The executive who engaged us was furious because he presented a report to the Board of Directors that was inconsistent with an existing report they already had. He was confronted with the inconsistency and embarrassed. He was still furious during our initial meeting and said this:

“We have 700 reports. Our IT department says that it takes them too long to make sure each report is consistent with one another. Why do we have 700 reports? I’m confident I can run this entire company on no more than 15 reports. I’ve identified the reports I think are useful. It’s your job to determine if we can delete the rest. That should make the consistency issue a thing of the past.”

Initially, I thought his reaction was very extreme. How can one person know what the rest of the company needs in order to run their respective businesses? I told him there was no way we were going to delete 685 reports at the end of this project. He told us he would do everything in his power to make it happen anyway.

It still amazes me how close he was to being right about the state of those reports. Here is what we found in our analysis:

  • Multiple reports that had 80% the same columns, just a few columns were different
  • Reports that were the same, except one had a different sub total
  • Reports that had slightly different parameters or sorting options
  • Reports that were never used
  • Reports that were completely wrong or empty
  • Reports pulling data from retired systems

In the end, we delete almost 650 reports, leaving about 50 reports that were absolutely necessary. This ended up having a huge impact on the organization. It had the following benefits:

  • It became much easier to keep 50 reports consistent with one another
  • Writing new reports was easier, because report developers were not constantly trying to troubleshoot old or out of date reports.
  • Less effort for users to find and use existing reports
  • More manageable to pay off old technical debt and implement better solutions like Azure Analysis Services, Power BI, and Azure Data Factory

Are you convinced? Is it time to start trimming these reports and make your reporting environment more manageable? If so, here are three tips to get you started:

Remove Access To It and See Who Complains

If no one complains, great. Wait 90 days and delete it. Reports should be in source control so you can always get it back later.

Brand Your Reports

Never use the business logo on any reports. Businesses get acquired, change logos, change names, or coloring. If you put that theme on all of your reports then they’ll all have to change and redeploy when the logo changes. Instead, come up with a brand for just your department. Give it a catchy acronym. Put that brand on your report. In the footer put “Certified by“ and your team name.

This will allow you to create uncertified reports. Let’s say a particular stakeholder requests a report but you don’t want to make it consistent with all the other reports. Or let’s say that stakeholder has a different way of calculating something that the rest of the business does not agree with. You can create the report for them but leave the certified logo off of it. Now you are not accountable for that report getting in the wrong hands. Tell your executives that only certified reports should be shared with the board, the media, or customers.

Check to See Who is Using Your Reports

In SSRS, the following query will tell you who is still using your reports:

SELECT COUNT(Name) AS ExecutionCount,

       Name,

       SUM(TimeDataRetrieval) AS TimeDataRetrievalSum,

       SUM(TimeProcessing) AS TimeProcessingSum,

       SUM(TimeRendering) AS TimeRenderingSum,

       SUM(ByteCount) AS ByteCountSum,

       SUM([RowCount]) AS RowCountSum

  FROM (SELECT TimeStart,

               Catalog.Type,

               Catalog.Name,

               TimeDataRetrieval,

               TimeProcessing,

               TimeRendering,

               ByteCount,

               [RowCount]

          FROM Catalog

               INNER JOIN

               ExecutionLog

                 ON Catalog.ItemID = ExecutionLog.ReportID

         WHERE Type = 2

       ) AS RE

GROUP BY Name

ORDER BY COUNT(Name) DESC,

         Name; 

This will not cover report subscriptions that are getting emailed, this query will tell you who is receiving which report through email:

SELECT
c.Name AS ReportName,
'Next Run Date' = CASE next_run_date
WHEN 0 THEN null
ELSE
substring(convert(varchar(15),next_run_date),1,4) + '/' +
substring(convert(varchar(15),next_run_date),5,2) + '/' +
substring(convert(varchar(15),next_run_date),7,2)
END,
'Next Run Time' = isnull(CASE len(next_run_time)
WHEN 3 THEN cast('00:0'
+ Left(right(next_run_time,3),1)
+':' + right(next_run_time,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(next_run_time,4),2)
+':' + right(next_run_time,2) as char (8))
WHEN 5 THEN cast('0' + Left(right(next_run_time,5),1)
+':' + Left(right(next_run_time,4),2)
+':' + right(next_run_time,2) as char (8))
WHEN 6 THEN cast(Left(right(next_run_time,6),2)
+':' + Left(right(next_run_time,4),2)
+':' + right(next_run_time,2) as char (8))
END,'NA'),
Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="TO"])[1]','nvarchar(50)') as [To]
,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="CC"])[1]','nvarchar(50)') as [CC]
,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="RenderFormat"])[1]','nvarchar(50)') as [Render Format]
,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="Subject"])[1]','nvarchar(50)') as [Subject]
---Example report parameters: StartDateMacro, EndDateMacro & Currency.
,Convert(XML,[Parameters]).value('(//ParameterValue/Value[../Name="StartDateMacro"])[1]','nvarchar(50)') as [Start Date]
,Convert(XML,[Parameters]).value('(//ParameterValue/Value[../Name="EndDateMacro"])[1]','nvarchar(50)') as [End Date]
,Convert(XML,[Parameters]).value('(//ParameterValue/Value[../Name="Currency"])[1]','nvarchar(50)') as [Currency]
,[LastStatus]
,[EventType]
,[LastRunTime]
,[DeliveryExtension]
,[Version]
FROM 
 dbo.[Catalog] c


;


Use these two queries to formulate a strategy for report retirement. You may still need to contact people, but it will get you started.

For Power BI, use the following MSDN article to audit report usage: https://docs.microsoft.com/en-us/power-bi/service-admin-auditing

Summary

Report ownership is like boat ownership. The second happiest day in a boat owner’s life is the day they sell their boat. Start deleting reports and watch your productivity soar!