Reporting on more than 3 objects without using formula fields

You get a request from a user to create a custom report type and you associate your 3 objects only to find that a field you want to report on isn't on one of these 3 objects.

So you create a formula field to pull in the data you want down to one of the 3 objects. New administrators and developers do it all of the time.  It works and they move on with their work day.

Did you know there is another way to accomplish this without clogging your objects up with redundant fields?

Custom report types allow you to select up to 3 objects down stream from a primary object but did you know that you can go 4 levels back up from those objects?  This gives you a total of about 20 objects you can pull fields from.

For this example, I am going to use the objects and data from the Survey Force app.  This is free from Salesforce labs and can be found here.

Open the menu under your name and click .  Once on the setup screen, expand the menu and select the option.


In this example, we want to create a report that will show data from the survey, the question and the contact that completed the survey.

So select Surveys as your primary object, Survey's Taken as your secondary, and Survey Question Responses as your tertiary object.  Your object relationships should now look like this:



Since we want to pull data from the contact object but it wasn't selected during this process, a lot of people get confused and assume that the data can't be pulled in without a formula field.

However, this is wrong.  Save your report type and then click the button.

Unless you have created a new custom field and are coming back in to add that new custom field to a preexisting custom report type, you have probably discounted this screen.

On the right side of the screen under the Legend you will see a box like this:


Select "Survey's Taken Fields" as seen in the screen shot and then click the "Add fields related via lookup" link below the text.

A box like this will now appear:


This is where you can select objects that were not originally selected but that have a relationship to those objects.  For this example, select "Survey Taken By Contact" and you will see a window like this:



Select Account name, click ok and now your selected fields will include the Account Name:


Now you can add this field to reports that use this custom report type.