Skip to content

The Ultimate Guide to Report Types: 7 Query Scenarios

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

At the heart of every report is one of the following seven query scenarios. For these examples, up to two objects will be mentioned, though more object relationships can be chained together in practice. A “parent record” is one that can be specified within a relationship field that is defined on a “child record”. There is a one-to-many relationship between parent records and child records. For each scenario, it will be noted if a deluxe report type can facilitate that type of request (spoiler alert: most of these can). Recognizing the query scenario when you first hear the report request will allow you to identify the best report type configuration for the job.

1. Records from one object

This is the simplest scenario. To report on records from a single object and only reference fields from that object, use a report type that has the primary object set to the desired object. There should be no object relationships enforced within the report type.

The foundation of a Deluxe Report Type – only the primary object is specified in the object relationships

2. Parent Records with Child Records

This scenario can be approached in three different ways:

Request A: Show a list of parent records that are filtered based on data from child records.

Solution A: Use a report type set up to show records from the parent object and add a cross filter to filter based on child record data. A cross filter will ensure that child records exist or do not exist for each of the records in the report results. Check out Two Additional Ways to Filter Your Reports for more on cross filters.


Request B: Show a list of child records that are related to parent objects. The “Show Me” filter may use the owner of the child record or be set to show records owned by any user. Fields from both objects should be accessible for column display and within filters.

Solution B: Use a deluxe report type set up to show records from the child object. Edit the layout to include any desired fields from the parent object. To ensure that each child record has a parent record specified in the report results, apply a field filter to ensure that the lookup field is not equal to a blank value.

Opportunities (Deluxe) report type with field filter ensuring an account is specified

Request C: Show a list of child records that are related to parent objects. The “Show Me” filter may use the owner of the parent record or be set to show records owned by any user. Fields from both objects should be accessible for column display and within filters. A cross filter may need to be applied starting from the parent object.

Solution C: Use a report type set up with the parent object as the primary object and then relate the child object within the object relationship settings. This will allow for filtering child records based on the owner of the parent record within the “Show Me” filter. Cross filters can be applied starting from any object that is enforced within the object relationships.

A report type with a child object enforced using the “must have” option

3. Parent Records with or without Child Records

This scenario occurs when the desire is to show all child records that are related to a parent and also include a row for each parent record that has no child records. Note that any child record with a blank value in the relationship field referencing the parent object will be excluded from these report results. A custom report type must be used for this scenario, and it must utilize the relationship option: Each “A” record may or may not have related “B” records. This style of report is excellent for summarizing information from a set of child records grouped by their parent record and wanting to display the parent record even if all the child records are filtered out. This is possible due to the “decoupled” nature of field filters on these types of reports. Field filters from the child object will not filter out a record from the parent object. In order to remove a parent record from the results, apply a field filter using a field from the parent object.

A report type with a child object enforced using the “may or may not have” option

4. Parent Records without Child Records

To generate a list of parent records that do not have any child records, use any report type that displays a row for each parent record. Then apply a cross filter using the “without” operator to ensure each record does not have a child record. Optionally, additional criteria can be added to the cross filter if the desire is to show parent records that don’t have a child record with certain field values. This is great for finding certain data scenarios even if there are existing child records for a given parent record.

Report showing accounts without certain kinds of opportunities

5. Child Records with Parent Records

This scenario is very similar to “2. Parent Records with Child Records, request B” – the desire is to see a list of records that have a relationship with each other, fields from both objects need to be utilized for display in columns and within field filters, and the “Show Me” filter may use the owner of the child record or be set to show records owned by any user. Use a deluxe report type set up to show records from the child object. Edit the layout to include any desired fields from the parent object. Then when creating a report, add a field filter to ensure the relationship between the child and parent object is populated. The results will show a row for every child record that has a parent specified.

This is the same image from scenario 2 request B

6. Child Records with or without Parent Records

A deluxe report type really comes in handy here for showing a list of child records regardless of their relationship with a parent object. Fields from both objects can be shown as columns and used for filters. For columns displaying a field from the parent object, if a child record does not have a parent specified, no value will be displayed for that column on that row. The row is still included in the results though, since each row is representing a record from the child object.

Opportunities (Deluxe) report type showing account fields as columns and opportunity records that have no account specified

7. Child Records without Parent Records

Finally, it is simple to show child records that do not have a parent specified when using a deluxe report type or any standard report type that only specifies the desired object as the primary object. Add a field filter to ensure the relationship field is blank and the results will show only the child records that do not specify a parent.

Only opportunities with a blank value in the account lookup field are shown

Conclusion

Listen closely as a report request is being defined in order to decipher which scenario is at play. This guide can be an excellent resource to help match up a solution to the request. And with most scenarios being possible to address using deluxe report types, you may be wondering how to transition your organization to this deluxe report type model so you can take advantage of all the flexibility it offers. Check out the next post in this series for some tips to get started. Then the series wraps up with some advanced considerations.

Share your success story

If you implement deluxe report types in your organization, we’d love to hear your success story. Use #DeluxeReportType or #DeluxeReportTypes and mention @ReportForce on Twitter. Here’s a tweet template.

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: 7 Query Scenarios 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: