How to Manage Reports: A Guide for Admins

Know Your Database

You can't create useful reports unless you understand where the data lives in your database and what that data represents. For instance, consider what information you need to create your next fundraising appeal list. Lists like this are often a combination of several pieces of information. You might need to know:

• Who has already donated this year?

• What is their mailing address?

• Does your donor have a preferred name or salutation?

• Who has opted out of solicitation mailings?

• Who are your current board members, regardless of their donor history?

• Who are your recently active volunteers who might be ready to donate for the first time?

Solicitation lists can be complex to compile, especially if key reporting data is spread out among too many records, or too many fields. If you don't know for sure where all the data that represents these various facets is located, you’ll struggle with creating this report.

Missing or Inconsistent Data

Even if you know how to find the relevant data to compose your invitation list, you’ll still have problems if the data itself isn't reliable. Let's look at some examples:

• You want to invite all your board members to an important event but you've been lazy about keeping the board info up-to-date. The new appointments are missing and those who have stepped off the board are still on your list.

• Your membership manager missed putting in the period of membership dates correctly so all the newest members don’t have the right Member Status.

• Your volunteer data entry person got confused and started putting email addresses in the Contact Description field.

Do any of these scenarios sound familiar? These are problems that you need to address before you can reliably report on this data.

When you're building out your report library, expect that the first time you run your report you're going to find things that need fixing. Data quality is like a threshold reminding you that you still have work to do!

A great way to stay on top of data quality is to create a "Wall of Zeros" dashboard. Create a report for each of these items and display the record count of each on a dashboard:

• Closed Donations without a Related Campaign

• New Donors without a Lead Source

• Payments without Payment Methods

• Accounts without Contacts

• Donors with no Address Info

• Contacts with Duplicate Email Addresses

What other "zeros" can you think of to look for?

Note Special thanks to Terry Cole for this awesome idea and screenshot example!

Finding the Truth

Pursuing data quality can be fruitless if you and your staff can't agree on what makes quality data in the first place. You need to have consistent processes for how you handle the data and clear definitions of terms. That can be a lot harder to do than it sounds!

Here's an example from a fictional organization.

Eli is the Development Director at a performing arts organization running a multi-year capital campaign to build a new theatre venue. Whenever he runs a report to check the total-to-date fundraising for the campaign, an argument ensues about the numbers. Eli's report says one thing, Hannah's finance report says something else, and Darby the Executive Director has a third report that she presents to the board. No one can agree on what the real campaign totals are. Why is this so?

Eli decides to find out. He begins by performing a line by line reconciliation of gifts between the three different reports. After a few weeks of digging through the records, Eli discovers that the way pledges have been recorded has changed over time, and several prospective pledges were entered as if they were signed and ready to be fulfilled. The finance office was only counting cash flow, not the informal pledge promises. But Eli didn’t want to discount those prospective gifts, and made sure to include them in his report.

Eli meets with Hannah and Darby to discuss the issue. They decide to remove some of the informal pledges as those pledges were never real commitments and no money had been realized yet. The group also agreed on some additional rules about pledge tracking. It took some time, but guess what happened. The staff began agreeing on the campaign numbers, and actually trusting the database as the single source of truth. The problem was ultimately not that Eli didn't know how to create reports (he certainly does!) but rather that there was never an agreed upon process for how pledges were supposed to be entered.

Additional Data Quality Resources

• Explore Data Quality Fundamentals

• Protect Your Salesforce Data

• Duplicate Management

Naming Convention

Establishing a naming standard for reports is an essential way to improve the user experience for your staff. People have an easier time searching for the reports they need when the names are in a predictable format. Here are some examples of well-formatted report names:

• All Oregon Contacts with Emails

• New Contacts this Year

• Open Cases by User Over 30 Days Old

• Households with Donations Last Year

• Fundraising Event Attendees Last Year

• Open Opportunities Older Than 90 Days

You might notice that there's a pattern to these names. The report names are written in a format like this:

Tip Report names are limited to a maximum of 40 characters. Use your characters wisely!

[Status] [Object] [Timeframe]

Make sense? Let's look at "Open Opportunities Older Than 90 Days".

• Status = Open

• Object = Opportunities

• Timeframe = Older Than 90 Days

A naming format like this helps you understand what's in the report (Opportunities), and what filters the report is using (Open, Older than 90 Days). While you won't be able to make every report fit cleanly into this format, make your best attempt to do so.

Note Status here could mean many things such as "Open", "New", "Closed", or "Approved". In this context, status doesn't refer to a field literally called Status.

Now let's look at the same report, but with less useful names:

• Recently Stale Opportunities

• Donations for Follow Up

• Fundraising Process Report

• Gabbi's Gift Tracker V2 2017

Notice what's different? It's hard to tell what might be in these reports. For example, what does "Follow Up" mean? Or "Stale" for that matter? The report author might know what those terms mean, but you should never assume everyone will. Also, why is there a gift tracker for 2017? Maybe that's when the author created the report, or maybe it only shows 2017 gifts. It's not clear.

Here's a summary of what makes a good report name format:

• Use Salesforce terminology. For example, Closed Opportunities over $1000.

• Include important report filters in the name. For example, All Iowa Contacts with Addresses. You should expect to find a filter for State=IA and Mailing Address is not blank in this report.

• Avoid putting specific dates into report names. Instead use relative date references in the title. For example, Major Gifts Last Year instead of 2018 Major Gifts.

• If you significantly change the report filters, update the report name.

• Apply your report naming convention consistently across all reports. It's not a convention if only one person is doing it!

Report folder names are important too. Establish a well-curated set of folders with meaningful names early on. Otherwise you may end up with a labyrinth of folders with names that are ambiguous and do little to help people find the reports they need. NPSP comes with several report folders that you can use as a starting point.

Here's an example of a set of report folders including both NPSP folders and a few custom ones.

Note You can create nested subfolders for your reports. Go to the report folder where you want to create a subfolder, and click New Folder. Name the folder, and click Save.

Hide Unused Report Types

Most organizations don't use every standard object in Salesforce. For example, Leads, Products or Pricebooks may not be relevant to you. In NPSP, there are many custom objects included that you also might not use when building reports. For example, GAU Allocations. Since there's a report type for every object and its related records, there can be a really long list of report types to choose from.

Take a look at this slice of report types available for just Opportunities.

There are many, many more report types than the ones shown above. The good news is that you can hide report types to make this list much shorter.

Note Currently, you can't hide or expose report types in Lightning Experience. You must switch to Salesforce Classic to do this.

Here's how to hide unused report types.

1. Click your user profile icon located above the tabs at the top of the screen.

   

2. In the pop-up menu, click Switch to Salesforce Classic.

   

3. You're now in Classic. Next, click the Reports tab.

4. Click the New Report button.

   

5. Click the Select Report Types to Hide checkbox.

   

6. Now expand one of the folders such as Accounts & Contacts by clicking the plus icon. Notice that each report type has a green checkmark next to it. Click the checkmark to hide that report type (the icon changes to an orange X-mark). Here's an example:

   
   

   Note There is no Save button for this process. Salesforce remembers your preferences as soon as you hide or expose a report type.

7. There are a lot of report types to look through, so it may take some time to get through them all. You can uncheck Select Report Types to Hide and come back to it later if you need to.

8. When you're done hiding report types, you can switch back to Lightning by clicking Switch to Lightning Experience at the top of your screen.

   

Understand Report, Dashboard, and Folder Security Settings

There are two fundamental roles when it comes to reports: Those who run reports, and those who create and edit reports. While it's empowering to allow all users to create and edit reports, doing so can cause "report bloat" and create a situation where sharing reports isn't part of the culture of your organization. Obviously, at least some users need to be able to create reports. As in all things, there's a balance to strike. Be intentional about assigning levels of access to reports and folders.

Report permissions aren't managed like object permissions are. You should take some time to understand what system and user permissions control how users interact with reports. For example, consider questions like:

• Should users be able to export report data? Reports can potentially include vast amounts of private information.

• Should users be able to create, edit, and delete report folders?

• Should users be able to edit everyone else's reports?

• How can you ensure that users are able to share reports with each other?

Read these help articles for more about report, dashboard, and folder permissions:

• Access Levels for Report and Dashboard Folders

• User Permissions for Sharing Reports and Dashboards

• Compare Access Levels for Report and Dashboard Folders

Approaches to User Training

As a Salesforce admin, you have a responsibility to ensure that reporting is not a roadblock to using the database. At the beginning of your organization's Salesforce journey you should:

• Deliver report training to all users with an initial focus on finding and running reports.

• Curate the available reports. That means ensuring users follow naming conventions, understand the report folder structure, and that you remove unused reports.

• Ensure users are familiar with Favorites, as this can be an effective way to quickly access commonly used reports from anywhere within the system.

• Identify your "power users" and teach them how to create reports. Those users should have familiarity with where data is and what it represents.

Tip A common error many organizations make is to train users on the report builder early in their adoption of Salesforce. The problem is that you can't make good use of the report builder unless you have a fairly comprehensive understanding of where data is and what it represents. Becoming conversant and knowledgeable about the makeup of the database takes time and is hard to teach quickly.

Of course, most users will eventually want to create or edit reports of their own. It's often the case that a user wants to make small changes to an existing report for their own specific needs. Train them how to save a copy of a report to their own Private folder.

You still should maintain a library of official reports that aren't changed without input from key stakeholders. But, you can allow users to create or modify reports using their Private folders. Keep conversations open about reporting by creating a system for "promoting" useful reports into your official library. That way the whole organization can benefit from those reports and the efforts of the staff.

Find Unused Reports

As part of your careful curation of your report library, you should periodically run a "report of reports" to see which reports you may need to retire because you haven’t been using them. There is no report in Salesforce called "report of reports" but you can easily create one yourself. Here's how.

1. Click the Report tab then click New Report.

2. Type Reports in the Choose Report Type quick find box and select Reports.

   

3. Click Continue.

4. By default all reports are included and all available columns are selected for display so there's no customizations needed.

5. Click Save & Run and provide a report name. You can use "Report of Reports" if you like. Choose the appropriate folder where you want this report to live and click Save.

The resulting report should look something like this.

Sort the report by Last Modified date or Last Run date to explore how users have (or haven't) been using your reports.

Find Records with no Related Records

Report types either list the name of a single object (for example, Opportunities) or an object with another object (Opportunities with Payments). In the first case, the report includes Opportunities regardless of what related records there may be. In the second case, the report includes Opportunities only with Payments. But what if you want to report on Opportunities that explicitly have no payments? Enter, the cross filter.

You can use cross filters to include or exclude records from related objects. For example, with an Accounts report, you can apply a cross filter to include Activities, or exclude Contacts. Cross filters access all the related records for a given object, all within a single report type.

Let's build out an example together.

1. Click the Report tab then click New Report.

2. Click Opportunities in the list of object on the left, then select Opportunities.

3. Click Continue.

4. Click the Filters tab. Change the Filter by Close Date range to All Time and click Apply.

5. Click the arrow under the Filters tab and select Add Cross Filter.

   

6. In the Edit Filter dialog, change Show Me to Opportunities without. Then select Payments in the Secondary Object drop down. Click Apply.

The report now displays any Opportunities without Payments.

Note If you're familiar with NPSP you know that the Opportunity object has a Number of Payments rollup field that you could also use to report on Opportunities with no Payments. However, there aren't rollup fields like that on every object for every relationship. Cross filters are an easy way to report that doesn’t require you to create formula or rollup fields.

Power of One: Supercharge your Record Counts

When you run a report, you get a total count of records. But that record count only includes the primary object in the report. For example, while you can display Account data in a report of Opportunities, the record count you see counts the Opportunities, not the Accounts.

Consider the following report. Notice the Total Records—it's a count of Opportunities. That's nice, but how many Accounts own those Opportunities?

There isn't a standard Salesforce feature that provides this secondary record count, but you can achieve it with a simple customization that's become known as the "Power of One."

Let's explore the Power of One using the above example.

The Power of One involves creating a formula field using just the number one on each object that you want to count in reports (hence the name). We know that formulas can be tricky but you'll love this one. It's the simplest formula you'll ever write.

1. In Setup, click Object Manager and select Account from the list of objects.

2. Click Fields & Relationships.

3. Click New to create a custom field.

4. Choose Formula under Data Type and click Next.

5. In the Field Label, enter Account Records. You can choose a different name if you prefer.

6. Select Number under Formula Return Type. For Decimal Places, select 0.

7. Click Next.

8. In the formula editor, enter the number 1. Yep, that's all.

   

9. Click Next.

10. Select Visible under Field-Level Security for Profile for all user profiles that should have access to this field.

11. Click Next.

12. Deselect all the page layouts listed and click Save. The Power of One formula is a "utility" field and doesn't need to be displayed on page layouts.

Repeat the above steps for any additional objects where you want to use the Power of One formula. It's not a bad idea to include it on all your most commonly used objects.

Note While you can add the Power of One formula to any object, you can only get a record count of objects that you can access in the report type you're using. For example, you can't get a count of Products in a Campaigns report.

Now let's build out the report we need.

1. Click the Report tab then click New Report.

2. Click Opportunities in the list of object on the left, then select Opportunities.

3. Click Continue.

4. Click the Filters tab. Change the Filter by Close Date range to All Time and click Apply.

5. To make the report results more compact, remove the following columns:

   1. Owner Role

   2. Opportunity Owner

   3. Fiscal Period

   4. Expected Revenue

   5. Probability

   6. Age

   7. Created Date

   8. Next Step

   9. Lead Source

   10. Type

6. Click Save & Run and provide a name for your report. Something like "Count of Opportunities and Accounts" is fine. In the report, notice the Total Records—it's a count of Opportunities.

7. Let's add the Account total next using our Account Records formula field. Click Edit.

8. Add the Account Records field to the report columns.

9. Click the arrow next to the Total Account Records field in the preview layout. Under Summarize, select Sum.

10. Click Save & Run.

We now have a count of both Opportunities and Accounts.

What other uses can you think of for the Power of One formula field?

Note The idea for the Power of One formula came from the amazing community of Salesforce users. Special thanks to Steve Molis and Tom Tobin.