Custom Report Types: Why do I love thee? Let me count the ways…

Have you ever wanted to report on something only to realize you’ll likely have to write three processes and a few formulas to get there? Have you already written those processes and formulas? Is your org in danger of maxing out all its cross-object relationship reference limits? Are you about to write some more right now? STOP! Custom Report Types can solve many of your cross-object reporting problems with just a few clicks!

I LOVE Custom Report Types and you should too! There is so much great functionality they offer. If it were physically possible, I would buy Custom Report Types roses and take it out to a nice candlelit dinner by the seashore and we would hold hands and dance and stare deeply into each other’s underlying architecture, but since that’s, in the SFDC world, probably a slightly odd fantasy to have, I’ll settle for writing this blog. Read on because there’s a chance Custom Report Types will solve all your problems and then maybe you’ll want to buy me dinner. (I like Sushi)

If you’re going to fall in love, first you need to meet…

Report Types: What are they?

Report Types are the handy dandy way to specify which objects and fields you are going to report on. Imagine a world without report types in which all objects and fields are available in the report builder. Simply trying to sort through which created date field to use would be a nightmare and I don’t even want to think about scrolling through options for summary formula field values. (Yes, having all that data available in something like Wave or Tableau is great but I’m talking about the Salesforce Report Builder here.) Report types let you narrow down what you are going to report on to a few related objects. However, sometimes the out-of-the-box options are too narrow or too broad, this is where Custom Report Types come into play.

Custom Report Types let you specify exactly which objects and fields you can report on

Custom Report Types let you specify up to three related objects to report on. Unlike Cross-Filters, which are also very useful in certain cases, Custom Report Types let you not only specify if an object has related records but also report on the fields of related records. For example, you may want to report on Accounts, with Contacts, with Campaign Members. You can also use report types to limit the number of fields available. For example, you may want to just report on Account fields and not Accounts and related Contacts.

Step 1. Define the Custom Report Type

In order to create a Custom Report Type, first go to Setup and then Search for “Report Types” and click “New Custom Report Type.” Second, specify a Primary Object. If you intend to report on additional child objects this object should be the highest level parent. Third, Label your Custom Report Type. Usually you should label your Custom Report Type after the objects you plan to include in the Custom Report Type. For Example: “Accounts” or “Accounts with Opportunities” or “Accounts with or without Opportunities” or “Accounts with Opportunities with or without Products”, etc. (Confused about when to use “with” or “with or without”? Don’t worry, I’ll cover that shortly.) For this blog I named my Custom Report Type “Accounts With Other Things Too!” This is a great example of what you should never name a Custom Report Type in real life. It’s far too enthusiastic for how vague it is.

Fourth, you must enter a description. Here again, I have failed. Yours should be specific and easy for end users to understand. The Description will display under the preview when you select your Custom Report Type when creating a new report. Fifth, Select a category to store your Report Type in. This maps to those fun little folders that store all the report types available for the entire Org. (Fun Fact: You can hide report types from appearing if they are irrelevant to your business but that’s for another blog.) Sixth, select either In Development or Deployed. Only Select Deployed when you are ready for end users to access your Custom Report Type. Finally, Click Next.

Step 2. Define Report Records Set

Here is where all that “with or without” stuff comes into play. In the Define Report Records Set section you can add related child objects to the report type. Choose from a list of available child objects in the drop down. You can define if a child object must exist for the report to display records of the parent or if the parent can exist with or without a related child record.

Here are a few examples:

You can only choose up to three levels of child records. Feeling limited? Don’t! Once you Save the glory of layout editing comes into play. This is where the good stuff is.

Once you save you’ll see an overview like below:

In the bottom section next to “Fields Available for Reports” Click the “Edit Layout” button. This will take you to the layout editor:

 

Custom Report Types let you customize and reorganize field folders in the report builder

the Sections fields are grouped in correspond to folders in the report builder. Once I created the custom Report Type with Accounts, Opportunities, and Activities those three sections and all corresponding fields were created by default in the layout. Thus, when I use the Custom Report Type the report builder looks like this:

However this does not need to remain the case. You can delete sections, change the names of sections, add new sections, and drag and drop to rearrange sections. In this example I’ll change the name of the Opportunities Section to “Deals”, add a Section called “Financial Info”, drag some currency fields into the “Financial Info” Section, and drag the Financial Info Section above the Activities Section. This example isn’t a best practice, it just shows you what is possible. Just to drive that message home I will also create a section called “Dragon” and add random fields from all three objects.  After I make these changes and save the layout, the report builder will look like this:

You may notice I put three random fields into the Dragon Section. It may be difficult for end users to tell which objects those fields are from. Never fear, Custom Report Types to the rescue again!

Custom Report Types let you change the field labels and rearrange fields for reporting

If a field label is too long or confusing or if it’s unclear by the label which object the field is referencing, just double click on any field in the layout to change the label.

For this example I’ll change “Industry” to “Opportunity Industry”, “Description” to “Account Description”, and “Task” to “Dragon Treasure” I’ll also drag Dragon Treasure field to the end so it displays at the bottom in the list.

Now the Report Builder will look like this:

Custom Report Types let you specify which Columns automatically display in a new report

You may have noticed that only the Account Name, Opportunity Name, and Subject are columns are displayed automatically when I create a new report. With most reports it’s likely there is a subset of important fields you won’t want to spend time adding to every report. Luckily you can double click on any field in the layout and check the “Checked by Default” checkbox and that column will automatically display in any new report.

You can see which fields have Checked by Default Set because they will have a checkbox on the field in the layout editor.

Now when I create a new report the Dragon Treasure column automatically displays.

Now at this point you’re probably thinking one of three things: ‘This is so great! Is there more?!?!” or “Yeah yeah yeah I already knew all this, is that it hot shot?” or “Oh jeepers oh gee willickers! (giggles and hops up and down in giddy anticipation) she hasn’t even mentioned the best part yet!”

Custom Report Types let you add fields from related objects via lookup.

In the layout editor, you probably noticed a section on the right where the fields available for each object can be dragged and dropped into and out of the layout. You may have even noticed that you can select from each of the three objects included in the Custom Report Type and see available fields below.

But did you notice the little white link below the view drop-down called “Add fields related via lookup >>”? That link is a gold mine of opportunities. Click it, you know you want to.

Let’s try it with opportunity Fields as the reference:

From here you can click to view fields for any parent object.

You can select fields here and click okay or click on the “view related fields…” links next to fields to view and select fields on those objects.

And so on and so forth. (My Dev org isn’t particularly complex so I run out of options pretty quickly.)

Once you click OK those fields will appear in the layout and you can drag and drop them to the appropriate section or re-label them. I suggest you do both, the labels on objects added via lookup can get pretty wonky.

Now think back to those cross object formulas you may or may not have been writing. Every object has a limit of 15 spanning relationships (other object references). If you try to create a formula that references more than 15 objects, you’ll receive an error message. But one single Custom Report Type can contain up to 60 object references. However, there are some limits. 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. Still…

1. With Custom Report Types you do not need to spend time creating formula fields that reference related objects to use in your reports.
2. The formula fields you didn’t have to create will not count against field per object limits.
3. You will not need to write any automation to capture field values on child objects for reporting purposes because you maxed out your object relationship reference limits on formula fields.

Thanks for reading. Hopefully you learned something new about Custom Report Types and are now as in love with them as I am. Remember, as with everything, there are limits, but hopefully you feel a lot less limited or intimidated after reading this. If nothing else, maybe you enjoyed the dragon references so much you’ve decided to buy me a Dragon Roll.