Saturday, February 23, 2008

V-shaped (Junction Object) Reports

Custom Report Types are incredibly powerful, but one feature that you might not know about, is the ability to "go back up" the object hierarchy.

Let's imagine that your business has a call center and that every once in a while one of your products breaks. When this happens, the affected customer will call your 1-800 number and ask for a replacement product. As a company, you're happy to oblige, but you want to run a report on Cases, Replacement Orders and Products to try and detect breakage patterns. With traditional salesforce reports, you'd be stuck, because the object relationship is V-shaped:

Using standard salesforce reports, you could easily run a report on Cases and Replacement Orders, but you wouldn't be able to "go back up" to get any Product fields beyond the name of the Product being replaced.

Allow me to introduce you to the little-known hero of our story, the "Add fields related via lookup" hyperlink for Custom Reports:

The "Add fields related via lookup" hyperlink will allow you to add fields from the Product table to your report on Cases and Replacement Orders.

And now you'll be able to save the company untold millions by detecting product breakage patterns - all thanks to one little hyperlink.

If you want to build a similar report in your salesforce org, you'll need to define a custom report type with one or more related objects:

Once you've defined your custom report, the next two steps are just a matter of clicking on the "Edit Layout" button and the "Add fields related via lookup" link. Happy Reporting!