The Ultimate Guide to Report Types: 7 Query Scenarios

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.

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.

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.

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.

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.

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.

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.