Report Magic: Last Run of a Dashboard
I’m willing to bet that many organizations that have been running Salesforce for a long time have many dashboards and reports they no longer need or use. Ideally, these should get cleaned up and removed from your system. If you’re like me, you don’t want to spend anymore time on cleaning than needed.
You may already know about cleaning up old reports. You can run a report on Reports and find the “last run” date of each report. Unfortunately, there’s not a “last run” for Dashboards. Worse, you can’t base the use of a dashboard based on the “Last Refreshed” date you see on the dashboard. That may be the date YOU last refreshed, not someone in the company.
What’s an admin to do?
Why Clean Up Dashboards Anyways?
Why bother to clean them up if it’s such a bother anyways? The big reason is improved user experience. When you have lots of out-dated dashboards it simply clogs up the dashboard list for users. This makes finding the GOOD dashboards more difficult. Yes, you can hide dashboard folders to help with this – but you still need to know which Dashboards to hide!
Another good reason is to improve adoption. A user may not go to the correct dashboard to get the metrics and information they need as part of their job. Users may simply give up and stop looking at dashboards all together. This makes driving behavior and supporting these users more difficult. You also run the risk of “Attitude Spillage.” Why should I trust the rest of Salesforce, if I can’t trust the part that is supposedly measuring me?
Let’s Get Cleaning
Just like cleaning your house, you will need some tools. So let’s take a look at our metaphorical cleaning devices.
- A Dashboard “Archive” folder – This folder is hidden for everyone but the admin
- A Dashboard report “Archive” Folder – This folder is hidden for everyone but the admin
- Custom Report Type: Dashboards with Reports – More on this in a moment
- List for “No longer needed folders” – it’s a list of… well folders you no longer need
I’m assuming you have your folders ready and something to start making a list of the folders you will soon be able to remove.
Gather the Data
We’re going to use a Custom report type for “Dashboards with Reports. This is something you will likely need to create since it isn’t a standard report. You can find your custom report types under Setup -> Create -> Report Types.
When you create your new report type you have to specify a primary object. This will be “Reports.” Then you’ll want to select a related object which will be “Dashboard Components.” This is technically a report of Reports with Dashboard Components – but that doesn’t quite roll off the tongue.
Now that we’ve acquired our cleaning tool, let’s go create a report with our new report type. How you clean will depend on:
- How Stale do you want your dashboards to get. A good rule of thumb for me is anything that’s hasn’t been run for 1 year is fodder. Everything else is a concern to discuss with the stake holders of those dashboards.
- How often do you want to clean up? I’m a fan of the annual clean up but some people like their spring cleaning a bit less seasonal.
- I don’t actually have a third thing to consider, but number lists of only 2 items look silly
For my reports, I’m looking for any Report Last Run older than a year. I run report I’m want to include the dashboard title, report Name, Report ID, report folder, and the Last Run Date. Depending on who creates the reports in your organization you may want to include the created by or last modified by field as well.
I also like to group by the Dashboard Title since you will get multiple rows for each dashboard. Remember, we’re technically looking at Dashboard components and not just the dashboard itself.
Now I have my report of all the reports tied to dashboards and when the report was last run. When a dashboard is refreshed, it runs the reports that make up the dashboard components.
I have my list – now what
My personal preference is to move the Dashboard and reports into our archive folders. I like to keep TWO sets of Archive folders. I put all the reports and Dashboards I have identified in one set. The second set is for NEXT Years cleanup. I wait 1 year to give people the chances to scream and yell before I start deleting old dashboards.
In the meantime, everything is in an archive folder which only you have visibility to. For all intensive purposes it looks like things are deleted!
This plan isn’t without some gotchas. Things to consider.
- You will see different Last Run Dates of the reports. While all the reports will have the same last run date when the dashboard refreshes, there’s nothing that prevents a user from looking at the reports outside of the dashboard.
- You may be able to get rid of Dashboards but not the report. Sometimes users will look at a report but not the dashboard. You may have to keep those reports available.
- Not every report used in a Dashboard may show up – especially if those reports are being used. So pay close attention to the names of the reports as well as the dashboard when you’re moving them to the archive folder!
- You cannot delete Reports that are used in a Dashboard. When it is time to delete, you have to delete the dashboard first THEN the reports