The Ultimate Guide to Report Types: Advanced Considerations

In the final post of this series, we’ll discuss some advanced considerations that I’ve been compiling over the years. These are suggestions you may want to implement right away or tuck away in your toolbox if you haven’t encountered these scenarios. Here are the other posts in the series to get you up to speed:

• Part 1: How Do Report Types Work?
• Part 2: Deluxe Report Types
• Part 3: 7 Query Scenarios
• Part 4: How to Position and Transition

Deluxe Report Types for Detail Objects in a Master-Detail Relationship

In the quest for arming yourself with the most flexible and reusable report types to tackle any reporting request, you may want to consider the following when creating a “Deluxe” report type for the detail object of a master-detail relationship: You will actually gain more flexibility for using cross filters in this report type if you enforce the master object in the object relationships.

Gasp! Won’t this go against the “only enforce the primary object” mantra? Yes – but for good reason. The driver behind only enforcing the primary object in the custom report type is to ensure you have access to every record you are hoping to report on. You are eliminating any filters that are baked into the base report type. But with master-detail relationships, this concern can still be satisfied since the rules of master-detail relationships are so strict: every detail record must specify a master record. So a custom report type built to show “all master records with detail records” a.k.a. “a list of detail records that are related to master records” is guaranteed to show you every detail record in system (if you remove all filters in the report and you have sharing visibility to all records). The same results you could achieve with a custom report type built specifying the detail object as the primary object can still be achieved when specifying the master object as primary and enforcing the detail object using the “Each A record must have at least one related B record” option.

I would strongly suggest to continue utilizing the naming convention and call this custom report type “Details (Deluxe)”. All fields from the enforced objects will be included in the layout by default. It might make sense to reorder those field sections so the detail object fields are displayed first (mimicking all other “Deluxe” report types”). You can still use the “Add related fields via lookup” hyperlink to traverse relationships and include reference fields from objects further up the data architecture hierarchy. Within reports built from this custom report type, you’ll be able to utilize ownership criteria (the Show Me “My Records” or “My Team’s Records” option) based on the record that actually controls visibility for each detail record – the master record. Furthermore, you’ll be able to add a cross filter that starts from the master object and cross filters against any of its child objects.

So while the subtlety here seems inconsequential, this approach might save you from having to build a separate custom report type to satisfy a particular report down the road. Go ahead and enforce the master object as the primary object in your single custom report type built to show records from a detail object. If you have a three-level master-detail hierarchy, you can safely enforce all three objects.

Objects with Polymorphic Relationships

“Poly-what?” you might be asking? There are certain standard objects in Salesforce that have very special relationships to other objects. They use polymorphic relationships to allow you to select which object you want to relate the record to. Trying to traverse these polymorphic relationships when building a “Deluxe” report type will leave you frustrated, as only certain standard fields can be accessed (if Salesforce even allows you to traverse that relationship).

Activities

Activities (Tasks and Events) are notorious for having polymorphic relationships:

• Related To (WhatId) – this field can specify a record from any object that allows activities
• Name (WhoId) – this field can specify a contact record or a lead record
• Assigned To (OwnerId) – this field can specify one or more users (creating multiple tasks), a group, a queue, or even a calendar (for events)

Cases

Cases can be owned by a user or a queue

Campaign Members

When you click the “Add contacts” or “Add leads” button on a campaign to associate records, you are filling in a value for the polymorphic LeadOrContactId field.

How to handle these situations

There are other objects with polymorphic fields, but the ones listed above are probably the ones you will encounter first. A few solutions can be used to remedy the situation depending on the circumstances:

1. Create a custom lookup field on the object that allows you to specify the single object you are interested in retrieving fields from and populate it using automation. The new “before save flow” feature can be used here to quickly update the value in your new custom field. For truly polymorphic fields, you can use the first 3 letters of the ID as decision criteria to distinguish what object it is. For objects like Campaign Member, you can simply keep your new custom lookup field in sync with either the ContactId or LeadId field. Showcasing this solution would be a great topic for a future post…
2. Create a custom report type that enforces the parent object as well as the child object containing the polymorphic relationship. This is a quick way to generate a report but will likely lead to maintaining lots of extra custom report types if you only use this method. If you have time, the first solution will get you to a place where you can truly create a “Deluxe” report type for that object.

Limitations

The limitations of custom report types have been mentioned throughout this series, but I’ll compile the highlights here for good measure. If you want to see the official Salesforce documentation on this subject, check out this article.

• A custom report type can contain up to 60 object references
• Users will receive an error message if they run a report from a custom report type and the report contains columns from more than 20 different objects
• You can add up to 1000 fields to each custom report type
• You can traverse up to 4 relationships in the “Add fields related via lookup” window
  • This allows you to access fields from 5 objects in a hierarchy
  • If you have multiple objects enforced in the custom report type object relationships, you can start from the primary object and traverse 4 relationships from there. In an extreme scenario, enforcing 4 objects in the custom report type and then traversing 4 relationships starting with the primary object would allow you to access fields from 8 objects in a hierarchy

I imagine there are performance concerns if you are working with large data volumes and approaching some of these limitations. If you come across these, please let me know in the comments.

Conclusion

Even with some of the headaches that may arise in implementing “Deluxe” report types for all your reporting needs, the benefits far outweigh the struggles. Stay tuned for more posts that highlight reporting tips and tricks that can be accomplished utilizing “Deluxe” report types as the base.

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.

Happy Reporting!