Salesforce: Adding Fields to Custom Report Types

Salesforce’s Report Builder puts a ton of power in the hands of nonprofit organization admins, but the learning curve can be steep! In this series of tips, we’re covering how to tackle several common reporting challenges. (Check out last month’s tips about choosing the correct report type for your needs.)

Just. One. More. Field.
In today’s scenario, you’ve built the perfect report that has all the right filters and formatting, but then you discover you need to add just one more field… that doesn’t seem to be available. How frustrating!

Sometimes, this happens when you (or someone else in your organization) have created a new custom field, perhaps specifically so you can report on it, and you’re so excited to explore this new data… but it’s just not there — let’s call this “Example A.” Or maybe you’ve used filters to create a list of records meeting particular criteria, but you realize you also want to include some information from a related record (“Example B”). In each of these cases, what can you do to get the results you need?

Here’s how to determine your next step:

1. What object is your missing field from, and how does it relate to the other objects in your report?
   We wrote last month about the importance of paying attention to which object a field is on; to solve today’s problem, we’ll need to take it a step further and investigate the relationships between the objects themselves. Objects can be related to each other in multiple ways — for example, in NPSP, a Contact is generally a “child record” to the Account that represents their Household, but they might also be connected to the Account that represents their job via a Lookup Relationship to “junction object” called Affiliations.
   
   It’s important to think through these object relationships, especially when it comes to address fields or summary totals.
   
   Say you’re creating a report of Contact Giving Totals (so you’re primarily focusing on Contacts and their Accounts/Households).
   
   Example A: You’ve successfully included fields summarizing the last couple years of donation totals, using standard NPSP fields; you also want the total gifts from three years ago, a custom field your organization uses, but you can’t find it your report.
   
   Example B: You want summarize the report by the Zip Code of each Contact’s Primary Affiliation Account. Yes, that Zip Code field lives on the Account object, but it’s a different Account record than the one you’re already using in the report — and as we’ll see, that distinction might point us in the right direction for solving our reporting problem.

2. What Report Type is your report built with, and what objects does it include?
   The likely culprit here is, once again, Report Types. (Remember those? Among other things, the Report Type is what determines which fields are available to add to your report.) The images below show how to check which Report Type you’re using for a particular report. In the top left corner, we can see the report name (“Contacts with Giving Totals”) and the report type it’s based on (“Contacts with Accounts – CUSTOM” or “Contacts & Accounts”).
   
   Example A:
   
   Example B:
   
   
   In both cases, the report type name gives us a pretty good hint that it will include fields from both the Contact object and the Account object… at least, the ones that have a direct “master-detail” relationship to each other. But will Example A have ALL the fields from those objects, even custom fields? Will Example B let us add that Affiliation Zip Code that’s related “via lookup” to the records showing in the report? On to the next step!

3. Is it a standard Report Type, or a custom one?
   
   Standard Report Types are automatically system-generated for all objects and object relationships (yes, even for custom objects!); they always include every field on their primary objects, but they can’t be modified at all. Custom Report Types require a bit more maintenance and attention, but they give you the flexibility to add fields from objects that are connected via Lookup Relationships to their primary objects.
   
   It isn’t always so easy to tell what kind of Report Type you’ve got by looking at that screen above! If you’re lucky, the Report Type might have the word “Custom” in its name, like in Example A… but the best way to tell for sure is to navigate to Setup and explore the Report Types list:
   

Standard ones aren’t listed here (because you can’t edit them!), but all your Custom Report Types are.

Our Example A report is clearly using a custom report type, which we’ll need to edit and add our custom Contact field, “Total Gifts 3 Years Ago.” Until very recently — i.e. a week or two after this blog is posted! — it was necessary to manually add new custom fields to all your CUSTOM report types where you’d want to use them. (Thankfully, Salesforce’s “Summer 21” release, which will be live for all orgs by mid-June, takes a huge step forward in preventing this problem! From now on, new custom fields you create will AUTOMATICALLY be added to all relevant custom report types, so you’ll be able to skip this step entirely.)

Example B is a little trickier — “Contacts & Accounts” isn’t showing up in this list, so it must be a standard report type! In order to add in fields from the Primary Affiliation, you’ll need to use a custom report type, add in some fields via that Lookup Relationship, and then use it to rebuild your report.

Our video below walks you through how to edit a custom Report Type and add fields, including fields related via Lookup.

Understanding when and how to create a custom report type is a huge step forward for any Salesforce admin, and critical to your reporting success. This video from Salesforce explains how to create them from scratch. Watch it and then try it yourself to create a super useful report to identify duplicate records.

Learning these rules and mastering reports can take some time and effort, so we’re always here to help! If you ever need help figuring out why your report is missing fields or doesn’t seem to give you the results you require, please submit a support request.