Jennifer Lee and Cassie Supilowski in a new "How I Solved It' episode.

How I Solved It: Monitor Unwanted Changes to Reports

By

Welcome to another post in the “How I Solved It” series. In this series, we do a deep dive into a specific business problem and share how one #AwesomeAdmin chose to solve it. Once you learn how they solved their specific problem, you’ll be inspired to try their solution yourself! Let’s take a look at how Cassie Supilowski was able to create a report to track unwanted changes to reports.


Key business problem 

As an admin, I want to find a balance between providing my users with the reports they need and allowing them to create and modify reports for themselves and their teams. But what happens when someone accidentally edits a report used by other teams? I need to be able to monitor unexpected and unwanted changes to reports that might impact other users in the company. 

Background 

As an organization, we have many common benchmarks (BMKs) and key performance indicators (KPIs) for the entire company. In these cases, our Salesforce Admin team creates a suite of reports that we store on a Common Benchmarks Dashboard where regional staff members can go and “shop” for the reports they want to add to their team dashboard. Additionally, they can clone these reports to make slight modifications for themselves and create their own reports for things that fall outside of org-wide BMKs and KPIs. This works out great for everyone, because end users not only have a baseline set of reports but also feel empowered to dig into data and reports on their own to meet the needs of their team. Win-win!

However, we started occasionally seeing cases come in about ”broken” reports. Of course, nothing was really broken, but staff members in one region might be seeing a blank report because a user in another region modified an org-wide report with filters for their own team. This was usually a quick fix—just removing any team-specific report filters. But it was still frustrating for end users, an extra step for our Salesforce Admin team to fix, and always felt urgent. Sometimes, the inadvertent change happened days before or there had been several changes, so we couldn’t always pinpoint when or by whom the change was made.

I knew we needed a way to proactively monitor changes to reports without restricting users’ ability to edit and create reports on their own and without flagging every change to a report. And that’s where things got a little meta. What better way to track this than with a report of reports!

How I solved it

1. Understand which reports should not be edited

Before jumping too far into a solution, I sat down to understand what exactly we would want to proactively flag about report changes. While my first thought was that anything created by our admins shouldn’t be modified, we realized that wasn’t true. There are plenty of times we create specific reports for teams and then say, “Now it’s yours to edit!” so we would expect those to have edits by other staff members. 

What I landed on was that the reports that were created by our Admin team AND that were also on certain dashboards owned and maintained by our team were the reports we didn’t want edited. With this clarification, I was able to turn to a custom report type to take me to the next step.

2. Create a custom report type

I knew that there was a standard report type for Reports, but given the criteria I had outlined above, I also needed to access Dashboard information, so I went with a custom report type of Reports with at least one related record from Dashboard Components. The last piece here means that in order to pull into the report, the report must be used in a component on a dashboard. Which dashboards? We’ll get to that in a minute.

The Object Relationships for the custom report type.

I also knew that fields like Created By and Last Modified By were available in a report of reports which could be useful. While I could add a filter for each name of the members of our Admin team, what I was really hoping I’d find was that I’d be able to use the “Add fields related via lookup” to access additional information about the users in these fields, like Profile or Role. Lo and behold, they were available! I added Profile for both the Created By and Last Modified By user lookup to my report layout, as well as things like Dashboard name from the dashboard component object, and was ready to get my report set up.

Add fields related via lookup link under the Reports Fields dropdown view.

Report filters for Profile and Role on the Add Fields via Lookup page.

Report fields for Profile, Role, Created By, Last Modified, and Profile (via lookup) selected on the Field Layout Properties page.

3. Create report with filters

Now that I had my custom report type with fields about the users who created and modified the report, I was ready to build my report of reports! I added filters to look only at dashboards that met the criteria I had outlined: name contains “common BMK” or “org-wide”. These are the dashboards that serve as go-to sources for the entire organization and should only be modified by our Admin team. 

Next, I wanted to only flag reports that had been edited by a non-Admin team member. In other words, my report of reports should be blank unless there’s something I need to address. For this requirement, I added two filters: 1. Created By: Profile: Name contains “System Admin” (that is, our Salesforce Admins) and 2. Last Modified By: Profile: Name does not contain “System Admin” (that is, everyone else!).

Now that I had a report I could use to see which reports had been edited, I needed to decide how I would monitor and address the changes.

Custom report filters.

4. Monitor the report

Because I wanted to see any flagged reports as soon as possible, and not have to rely on remembering to check a report daily, I opted to subscribe to the report. I set the subscription to email me daily at 7:00 a.m., but only if the report had more than 0 records. The subscription criteria meant I didn’t have to check the report every day, only when there was a report that met my criteria—who doesn’t love efficiency? For good measure, I also added this to a data cleaning dashboard we had which helped other Admin team members monitor the reports as well if I happened to be out of office or they viewed the dashboard before I got the report email.

The Edit Subscription page for the report.

Business results

With our scheduled report of reports, we can now see within 24 hours maximum if an unwanted change has been made to our report. And since the window for reviewing the change is smaller, we can also proactively reach out to users to share a friendly reminder about which reports they should and should not be editing. Before setting up this report, it was common to get a case about “broken” reports sometimes once a week. Now that we can catch any changes sooner and coach users on best practices, we see this kind of case less than once per quarter!

Do try this at home

In this use case, we needed to see reports that shouldn’t be modified, but can you think of another use case for a report of reports? We also use this to view “popularity” of reports; that is, how often they’re used in components on dashboards. Maybe you have reports that need to be modified on a certain cadence or, rather than those created by an admin, those created by other teams. 

Let us know what you thought of this solution, and tell us how you want to use it with #AwesomeAdmins #HowISolvedIt.

Resources

Want to see more good stuff? Subscribe to our channel!

SUBSCRIBE TODAY
Jennifer Lee and Manish Thaduri in a new episode of "How I Solved It."

How I Solved It: Find Newly Created Fields in Salesforce

Welcome to another “How I Solved It.” In this series, we do a deep dive into a specific business problem and share how one #AwesomeAdmin chose to solve it. Once you learn how they solved their specific problem, you’ll be inspired to try their solution yourself! Watch how Manish Thaduri was able to find newly […]

READ MORE

Have an Idea for a Story?

We are all about the community and sharing ideas.
Do you have an interesting idea or useful tip that you want to share?

SHARE YOUR IDEA