Ultimate Guide to Report Types Part 2: Flipping Report Types Upside Down

This is the second post of a two-part series. Part 1 is available here.

Welcome back! Up until now, we’ve been setting the primary object as the only object in the report type (scenario 1) or as the parent object in a relationship we want to traverse (scenarios 2, 3, and 4). Let’s add one more tool to our toolkit that will allow us to flip report types upside down and start setting the child object as the primary object: the ability to edit custom report type layouts. Again, if you need a refresher on reporting features, check out the trailhead module on reports. Here are the data sets we will be creating:

• Child records with parent records
• Child records with or without parent records
• Child records without parent records

And, here is the sample data we will be using:

Parent Records (Accounts)

Child Records (Opportunities)

The account table is the parent object and the opportunities table is the child object. This is accomplished by a lookup field on the opportunity object that can optionally specify an account record.

Child records with parent records

Wait a minute, wouldn’t this data set be the same as scenario 2 (in part 1): parent records with child records? Yes – it will be. Salesforce allows for many ways to accomplish similar requirements! The difference here is that you can create this data set while specifying the child object as primary in the report type. When setting up a custom report type to handle this scenario, do not include any other object relationships – just specify the child object as the primary object. Then, edit the layout for this report type. This is where the magic happens!

On this page, you can include fields from related objects by traversing up to four relationships using any lookup field present on the objects you define in the custom report type. Just click the “Add fields related via lookup” button. I recommend creating a new section for every object you are including.

Once you have added the relevant fields from the Account object, we can create our data set. Using this report type, add a filter within a report to ensure that the “Account” lookup field on the opportunity is populated. Then add the desired columns.

The result is the same as scenario 2, but opportunities are the primary object:

Child records with or without parent records

This scenario is very similar to what you created above. In fact, you’ve actually done too much work already! On the report we just created, remove the filter that ensures that the “Account” lookup field on the opportunity is populated. Done. The resulting data set from our sample data would look like this:

All of our opportunity records are represented. This is because opportunities are the primary object and there are no other object relationship filters in the report type. We also have access to account fields if there is an account specified on the opportunity record.

Child records without parent records

Finally, we will create a data set that shows the orphaned children – child records without parent records. This report could also be based on the same report type used in scenarios 5 and 6, or you could use the standard Opportunity report type. Just include a filter that ensures the “Account” lookup field on the opportunity is blank.

Using our sample data, the resulting data set just shows the single opportunity record that does not have an account specified.

Wrap Up and Limitations

These seven scenarios are the basis for all of the report types you may need to create. Any of these scenarios can be expanded to include more object relationships – Parent records with Child records with Grandchild records, Child records with or without Parent records with or without Grandparent records. And, in many cases, you can combine different tactics to accomplish what you need.

Here are some relevant limitations you may encounter:

1. You can join four objects in a series when descending an object hierarchy (parent to child) and five objects in a series when ascending an object hierarchy (child to parent).
2. A maximum of 1000 fields can be added to a custom report type page layout.
3. Native dynamic ownership filters such as “My records” or “My team’s records” only work for the primary object specified in a report.
4. When using activities as your primary object, you cannot traverse the polymorphic fields – Name (WhoId) and Related To (WhatId) – when adding fields to the report type layout.
5. You can’t use the OR condition between field filters of objects defined by the “With or Without” relationship in the report type.

What a journey we’ve been on! Now that your toolbox is full with the essentials, you are ready to tackle some real-world examples in your own org. Happy reporting!