0

I have a custom object that represents purchases in an online shop. Each instance of this object has an optional relationship to an account and at least one relationship to another custom object, which represents items in the order. For the visually minded, here's an entity diagram:

Entity diagram

I want to build a report based around these Order objects, but filtered based on details from the parent Account and the child Items.

How can I do this?

I've tried using a Joined report, but the filters appear to operate independently for each report type. I.e. if I have one filter based on the account and another based on the items, I get a row for each order that gets through either of the filters. I need to only return rows that match both filters.

Any help / advice appreciated.

1

A custom report type should be able to do this. You can create a report type of orders with items, and then use the add related fields via lookup option to include the account fields. It's an incredibly hideous UI, but powerful.

See this doc for details on the process if it's unfamiliar.

Or, alternatively, you could use formula fields on the order object to pull in account fields and forego creating a custom report type. Pros are one less report type, cons are one more formula field...

| improve this answer | |
  • It looks like a custom report type will do the trick. Thanks @gorav! – Tom Wright Mar 1 '17 at 8:57
1

Edit: To prevent confusion, I assumed you already had a custom report type to report on the data structure you want. The below answer was meant to answer the question on filtering.

Probably you can with Cross Filters. They're quite powerful.

They allow you to filter e.g. only those Accounts with Contacts that have Contact.something=somethingelse. You can even use multiple Cross Filters in one report.

See https://help.salesforce.com/articleView?id=reports_cross_filters.htm&language=en&type=0

| improve this answer | |
  • As far as I can tell, cross filters allow me to go one level down (e.g. from account to order, or order to items). In my scenario, I'd either need to be able to go down two levels (from account to items), or up one as well as down (i.e. from order up to account and down to items). – Tom Wright Feb 27 '17 at 13:58
  • Ah, you can't do that unfortunately. If you want to filter on data from records from up or down, you need to use formula fields (up) or roll-up-summary fields (down)... There's no way around it. – Willem Mulder Feb 27 '17 at 16:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.