Reports for two lookup objects that relate to a parent

Question

I got a request this week from our customer service group that stumped me.

We use person-account, case, and a custom object called order (that person bought something). Both case and order have lookup to account.

They wanted to see accounts with orders that had cases of a certain type. It looked like report types offered vertical relationships (contact to account) but not a V-shaped 3-Object option.

Thoughts? I'll admit I haven't dived into joined reports, but would if someone told me this would work.

Answer 1

Joined Reports was built specifically for this type of issue.

With a Joined Report, you can report on both Cases and your Custom Object in a single report.

You need to have Report Types for both the Account with Cases and Account with Custom Object. Then inside your report you can include a joined report.

Here is a video on how to do joined reports that should help: http://www.youtube.com/watch?v=BGOkixtzv7s

Note that a current limitation of Joined Reports is that you can only return 2000 records, and you can't export to excel. So if you need more than 2K recs or need to export to excel it won't meet your need.

Answer 2

This is very similar to a question I asked, and the current answer is you cannot do that within Salesforce. You are correct that Custom Report Types (CRT) only do vertical reports, and not vee-shaped and Joined Reports cannot be cross-filtered.

Our solution will probably end up being exporting the data on a regular basis into local database servers and then providing reports. The drawback is that the reports will not be real-time, but we will address that later.

Answer 3

Depending on what it is about the Cases that causes the Account to be included on the report, this might be accomplished by an Apex Trigger on the Case object that updates the Account.

For example: the report is based on Accounts with Cases that have Status="Closed": Create a custom text field on Account called Case Statuses, and use a Case Trigger that collects all the Statuses from Cases on the Account, then populates them into the new Case Statuses field separated by semicolons. The resulting text value might look like "Closed;Open;In Progress". Your report can be your normal "Accounts with Orders" report type, and just filter on that custom field containing the Status values you're looking for.

If you go this direction, you will need to update all the existing accounts in some bulk way, like a data load or a simple Batch class against all Accounts with just an update statement in it.