How to filter a report based on properties of two related records?

Question

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:

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.

Answer 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...

Answer 2

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