Skip to content

The Ultimate Guide to Report Types: How Do Report Types Work?

This post is part 1 of The Ultimate Guide to Report Types. Check out the other posts in this series:

When working with reports in Salesforce, you will find that report types have a major effect on what you are able to manipulate. Report types control what report results have the potential to show, what objects you have access to, what fields are available, and how certain report features will behave. This is the first filter being applied to the data. It’s important to understand how this works since it’s not (yet) possible to change the report type once you start creating a report. Let’s take a closer look at the inner workings of report types by dissecting the options encountered when setting up a custom report type.

What is the primary object?

The first option you will stumble upon in this journey is the “primary object” setting. This is the object that every record in the report results must have a relationship with. There must be a record from the primary object in order for a row of data to display. While subsequent settings will have a huge impact on the report type, the primary object is the first stake in the ground to define which object’s records you want to report on. This is also the “top” object if you plan to enforce an object hierarchy in your report type (more on that later). Have you ever used a “My Records” or “My Team’s Records” filter in the “Show Me” section of the report builder to dynamically control which records are shown based on the logged-in user? The primary object’s owner field is used for this feature. One word of caution – the primary object cannot be changed once you create a custom report type, so choose wisely.

What happens when you relate an object on the object relationships screen?

On the second screen of the new custom report type wizard, you have the option to relate another object. The choices for object B are all of the child objects related to object A. This allows you to move downward in your data architecture hierarchy – from parent objects to child objects. The “A to B Relationship” setting has two options you can choose from. The first option reads: Each “A” record must have at least one related “B” record. While this is true, it might lead you to think that each row in the report results is representing an “A” record. Note the Venn diagram image on the right. Report types using this option are actually displaying a row for each “join” between an “A” record and a “B” record, or in other words, a row is displayed for each “B” record that specifies an “A” record as its parent. This is an “inner-join”, which shows records that have a relationship with each other. This setting does not show all of the “B” records (unless there is a master-detail relationship in place). Any “B” record with a blank value in the lookup field pointing to the “A” object will be excluded from report results no matter what filter settings are used within a report. If you keep adding additional objects on this page and continue to use this first relationship setting, the report results for this report type will show a row for every “D” record that specifies a “C” record that specifies a “B” record that specifies an “A” record. Remember, each record in the results must be related to a record from the primary object (the “A” object).

What does the “A records may or may not have related B records” option do?

The other relationship setting reads: “A” records may or may not have related “B” records. Again, note the Venn diagram on the right. This setup will continue to show all the “B” records that specify an “A” record as its parent, just like before. In addition, a row will be displayed for each “A” record that does not have any child “B” records. This is a “left-outer join”. This setting does not show all of the “B” records (unless there is a master-detail relationship in place). Any “B” record with a blank value in the lookup field pointing to the “A” object will be excluded from report results no matter what filter settings are used within a report. All of the “A” records will be accessible, though information from those records will be repeated on rows where multiple “B” records are specifying the same “A” record as its parent. 

An interesting effect to be aware of when using this style of report type is how field filters behave in the report builder. Records from each object, along with their accompanying field filters, are queried independently. Those results are joined together in a secondary step in order to be displayed on the screen. When applying a field filter using a field from the “A” object, you can completely remove all rows from the final results that don’t meet the filter criteria. Nothing groundbreaking here. But when using a field from the “B” object in a field filter, it will only affect the rows showing “B” records. If you filter out all the “B” records for a given “A” record, that “A” record will still display in the final report results since it was included in the individual results for the query against that object. This decoupled filter effect allows you to separately apply filters to the “A” records and “B” records within a single report. This comes in handy when you want to show a list of “A” records, summarize information from the child “B” records that meet certain criteria, and continue to show the “A” record even if all its “B” records get filtered out.

It’s also worth mentioning that not all reporting features are available when using this style of report type. Filter logic with OR operators between fields that are from each object cannot be configured. Field-to-field filtering and row-level formula features cannot be used.

How do all these settings impact a report?

Think of the primary object as being the epicenter of record relationships for the report type. The “Show Me” setting uses the owner of the primary object’s records to dynamically display data based on the user viewing the report. What records are actually being represented on each row in the results depends on a few things. A report type with only a primary object defined will display a row for every record from the primary object. As object relationships are added and the relationship settings are toggled, the “rows” can become less obvious. Check out this table:

Object “B”
Relationship
Object “C”
Relationship
Object “D”
Relationship
Report Result
Rows Represent:
Object “A” records
Each “A” record must have
at least one related “B” record.
Object “B” records that specify
object “A” records
Each “A” record must have
at least one related “B” record.
Each “B” record must have
at least one related “C” record.
Object “C” records that specify
object “B” records that specify
object “A” records
Each “A” record must have
at least one related “B” record.
Each “B” record must have
at least one related “C” record.
Each “C” record must have
at least one related “D” record.
Object “D” records that specify
object “C” records that specify
object “B” records that specify
object “A” records
“A” records may or may not
have related “B” records.
Object “B” records that specify
object “A” records

plus object “A” records that don’t
have child object “B” records
“A” records may or may not
have related “B” records.
“B” records may or may not
have related “C” records.
Object “C” records that specify
object “B” records that specify
object “A” records

plus object “B” records that specify
object “A” records and don’t have
child object “C” records

plus object “A” records that don’t have
child object “B” records
“A” records may or may not
have related “B” records.
“B” records may or may not
have related “C” records.
“C” records may or may not
have related “D” records.
Object “D” records that specify
object “C” records that specify
object “B” records that specify
object “A” records

plus object “C” records that specify
object “B” records that specify
object “A” records and don’t have
child object “D” records

plus object “B” records that specify
object “A” records and don’t have
child object “C” records

plus object “A” records that don’t have
child object “B” records
Each “A” record must have
at least one related “B” record.
“B” records may or may not
have related “C” records.
Object “C” records that specify
object “B” records that specify
object “A” records

plus object “B” records that specify
object “A” records and don’t have
child object “C” records
Each “A” record must have
at least one related “B” record.
“B” records may or may not
have related “C” records.
“C” records may or may not
have related “D” records.
Object “D” records that specify
object “C” records that specify
object “B” records that specify
object “A” records

plus object “C” records that specify
object “B” records that specify
object “A” records and don’t have
child object “D” records

plus object “B” records that specify
object “A” records and don’t have
child object “C” records
Each “A” record must have
at least one related “B” record.
Each “B” record must have
at least one related “C” record.
“C” records may or may not
have related “D” records.
Object “D” records that specify
object “C” records that specify
object “B” records that specify
object “A” records

plus object “C” records that specify
object “B” records that specify
object “A” records and don’t have
child object “D” records

The only feature that seems to be impacted by the “A” records may or may not have related “B” records option is row-level formulas. They are not available when this setting is used. Bucket columns can be used with any style report type. Any object that is enforced in the report type’s object relationships can be the starting point for a cross filter. This allows for some interesting and complex filter scenarios (which will be covered in a later blog post).

Now that we’ve uncovered how report types work, stay tuned for part 2 where we showcase a strategy that gives you the most flexibility with report types. Then part 3 dives into various reporting scenarios at the core of any request. Then part 4 will cover some tips for transitioning an existing Salesforce instance to take full advantage of everything covered in this series. Finally, part 5 details a few advanced considerations.

Evan Ponter View All

Evan Ponter is a Salesforce Admin Hero from Baltimore, MD who has been focusing on declarative development since 2012. His desire to keep an org simple, streamlined, and maintainable by future admins has led him to being an expert on the declarative features of the platform. A deep understanding of reports, the importance of proper data modeling, and the utilization of declarative automation tools have propelled Evan along a blossoming Salesforce journey where he solves complex problems using clever solutions that provide the ultimate flexibility. When he's not logged into Salesforce, Evan enjoys playing bass guitar in a local rock band.

4 thoughts on “The Ultimate Guide to Report Types: How Do Report Types Work? Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: