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 (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 can be owned by a user or a queue
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:
- 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…
- 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.
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.
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.
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.
Do you know which standard Salesforce objects (other than Event and Task) have Polymorphic relationships?
I don’t see an exhaustive list, but I found a few by searching for “polymorphic” in the developer documentation
You can determine what kind a field is by calling describeSObjects() on the object and examine the properties for the field.
– If relationshipName is not null, the field is a relationship field.
– If, in addition, namePointing is true, then the field is polymorphic.
Contacts and Accounts have a lookup relationship. I have not been successful in creating a Deluxe Account Report Type that would pull the contacts and other objects related to the Contact Object.
There is a standard relationship on the contact object that lets you specify an account – this allows you to create a “Contacts (Deluxe)” report type and include any field from the account object.
If you wanted to create a report type that displays account records as rows and include fields from the contact object, you will need to create a custom lookup field on the account object that lets you specify a contact, then make sure that field is populated with the appropriate value on every account record.
Can you tell me more about what you are trying to accomplish in a report? Maybe I can help recommend which object should be the primary object in a “Deluxe” report type.
I am trying to replicate the model of the Deluxe Report Type for the Account object and I realize it’s not very useful, because I don’t have the look-ups to all the other objects. So I don’t have the object model and relationships that would allow me to create an Account Deluxe Report Type.
What questions are you trying to answer in a report? There might be a different object you can use as the basis of your “Deluxe” report type so that you can utilize your existing architecture.