4

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.

1
  • Do they just want to see the list of Accounts? A cross-object filter may work here. You could start with the report Type "Accounts" and then use a Cross-Object filter to display Accounts With Orders and a second of Accounts with Cases where (Case Fileters) Joined reports are for displaying information from multiple objects and really only works as a summary (Counts). Anything else can get ugly. Cross-Object Filters lets you get a list of Accounts based on related records without displaying those records. Aug 21, 2012 at 13:22

3 Answers 3

3

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.

3
  • Joined Reports - That's one puzzle I have yet to solve!
    – Saariko
    Aug 21, 2012 at 14:28
  • Cory wins! Thanks for this. Gonna screw with it on Friday. Aug 22, 2012 at 0:13
  • The only gotcha is there is no "export" option - just "printable view" - which is kind of odd-looking and not really the same as "export to excel" - even though it is an excel format file.
    – user2223
    Aug 4, 2015 at 21:10
0

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.

0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .