Have you ever struggled to include all the data you need on a single Salesforce Report? We sure have, and we’ve seen quite a few of our customers face similar challenges. Today, I want to introduce a Salesforce feature that I consider a real game changer when it comes to reporting – the ability to add more fields to a custom report type via lookup.
One of the crucial steps in creating a Conga Composer solution is gathering all the Salesforce data needed to populate a given template. Because it can use Salesforce Reports and SOQL queries as data sources, Composer really shines when it comes to retrieving fields from any standard or custom object in Salesforce. However, certain situations call for more sophisticated techniques to build those reports or queries that navigate exactly the right object hierarchy to gather exactly the right fields.
I’ve always considered the custom report type to be the holy grail of data-gathering. You can do some interesting things with aggregate functions and semi/anti-joins in SOQL, but custom report types are simply awesome for those who prefer to use clicks instead of code. In particular, a handy little feature for custom report types that allows you to ‘Add fields related via lookup
’ opens up some serious doors in terms of data access.
Add Fields Related Via Lookup
In literal terms, this feature allows you to select fields from an object that’s related to an object in a custom report type via lookup. For example, starting with a custom report type of ‘Opportunities with Contact Roles’, I could look up from the Contact Role to its related Account and select any fields from that related object to be included on my report.
Note: The built-in report types for standard objects and the report types that Salesforce automatically creates when you relate custom objects are pretty good about retrieving fields from related objects. I recommend starting there before creating brand new custom report types.
Why This Feature is Awesome
I have a custom object called ‘Project’. The Project looks up to the Opportunity, and also can have related Milestones. The object schema looks like so:
Salesforce was very kind and created a few new report types when I related these objects.
However, let’s say I’m looking to use Composer to create a project status report that includes data from a Project and its related Milestones, but also from its related Opportunity, Account and Account Owner. The stock report types won’t get me what I need, so in order to gather all the required data with a single report it’s going to take a custom report type with some lookup magic.
1) Create the Report Type
My primary object is Projects, but we’ll go ahead and add Milestones:
This gets me the required fields from Projects and Milestones, but now we need to retrieve Opportunity, Account and Account Owner fields.
2) Add Fields Related via Lookup
First, we click ‘Edit Layout’ on our custom report type.
Then, I select the object that I’d like to look up from and click ‘Add fields related via lookup’
This now gives me the ability to add fields from related objects via lookup all at once. In this case, I’m scooping up fields from the Opportunity, the Account and the Account Owner. I like to think of this as navigating “up” the object hierarchy.
Tip: Salesforce allows a custom report type to contain fields available via lookup through four levels of lookup relationships.
Once I’ve selected my desired fields, they’re automatically added to the report layout. I recommend separating the new fields into different sections to better distinguish what object they’re coming from, but that’s optional. No matter the case, each field that’s added via lookup will have a magnifying glass icon next to its name on the layout.
Voila! I now have a custom report type that gathers data from 5 different objects. I’m ready to use this as a data source for the project status report I want to build with Composer.
This feature is incredibly useful for Salesforce reporting in general, but we come across the need pretty frequently when gathering data for Composer solutions. I hope this gives you some food for thought, and if you liked this post,make sure to subscribe so you never miss another one.