A computer monitor showcasing reports and text that says, "The Ultimate Guide to Report Types Part 2"

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

By

Editor’s note: This post was updated on May 30, 2023, with the latest information and resources. 

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 in part 2:

  • 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!

Editing the layout for the report type.

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.

Filed layout properties.

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.

Report with the data set.

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

Opportunities as 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:

Remove the filter that ensures that the “Account” lookup field on the opportunity is populated.

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.

Creating a data set that shows child records without parent records.
Using our sample data, the resulting data set just shows the single opportunity record that does not have an account specified.
Data set that just shows the single opportunity record.

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. The tactics used for scenarios 5, 6, and 7 allow you to create one custom report type for an object that you want to report on and utilize field filters within the report builder to accomplish various scenarios – talk about flexibility! Learn more here.

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) via the object relationships settings of a custom report type.
  2. You can join five objects in a series when ascending an object hierarchy (child to parent) via the “Add fields related via lookup” hyperlink on the layout of a custom report type. Multiple object hierarchies can be referenced using this method, allowing fields from up to 60 unique objects to be included in a single custom report type layout.
  3. A maximum of 1000 fields can be added to a custom report type page layout.
  4. Native dynamic ownership “Show me” filters such as “My records” or “My team’s records” only work for the primary object specified in a report.
  5. 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. Check out this solution for a workaround.
  6. You can’t use the OR condition between field filters of objects defined by the “With or Without” relationship in the report type.
  7. A maximum of 3 cross filters can be defined within a report. Each cross filter is always layered on top of all other report filters (including other cross filters) using “AND” logic. 

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!

Additional Resources

A computer monitor that displays a productivity tracker.

How to Build a Productivity Tracker to Show Your ROI

Right now, everyone is looking for ways to drive efficiency and get more done with less. That’s good news for us admins because we know how to build solutions in Salesforce to help our users get their work done more efficiently. One of the most important tools we have in our Salesforce toolbox is analytics. […]

READ MORE
The admin Learn Moar Trailhead Community badge next to text that says, "#4 Analytics Enhancements."

Analytics Enhancements | Learn MOAR Summer ’23

The Summer ’23 release has a lot of great analytics enhancements specifically designed for admins, from the new Analytics tab to posting dashboards to Slack to sharing Tableau visualizations in Salesforce. Read this blog post for the highlights. Then complete the Learn MOAR Summer ’23 for Admins trailmix to earn a special community badge. Visit […]

READ MORE
Greenery and a blue sky below a headline that says, How to Optimize License Utilization."

How to Optimize License Utilization

We know that one of your main responsibilities as a Salesforce Admin is to manage users and drive adoption. One important adoption metric is license utilization, which generally indicates whether users are using the available licenses. You can calculate license utilization as follows: License Utilization = # Used Licenses / # Available Licenses Here, we […]

READ MORE