Report cross object filters: pulling only parents with multiple types of cross object children

Question

Sorry if the wording is confusing, let me explain:

Our Account object is (indirectly?) a parent of the Asset objects, so it is possible to run a cross object filter on Accounts with Assets, and to further filter which asset records I'm interested in returning account info for, AKA

"show me all accounts with assets whose status equals 'Shipped'"

All well and good, but my question is about pulling Accounts with two different types of assets, each with its own filter criteria, AKA

"Show me all accounts with at least one asset whose status equals "Shipped" and at least one other asset whose status equals "Shipping".

The key part of the question is about returning only accounts that meet both criteria. After all, I could create a single cross object filter, and set the Asset to filter on 'where status = "Shipped", "Shipping"'. Doing so would return all accounts that have both types of assets, sure, but it would also return all the accounts that just have "shipped" assets, as well as all the accounts that just have "Shipping" assets.

I also tried creating two asset Cross Object filters on the same report, one with asset status equal to "Shipped", and the other with asset status equal to "Shipping". Unfortunately, that didn't return accounts that had both asset types (in fact it behaves quite oddly, not sure how it works).

Answer 1

You would definitely need to use two cross object filters to achieve the results you are looking for. I am adding an example using Installed and Shipped but it will only return accounts that have at least one asset with status = shipped and at least one asset with status = installed (in your case would be shipping).

Answer 2

Scratch that! Turns out my last attempt was the correct one: Just add a different cross object filter to your same report for each child record type you want to filter for.

I don't know why my report returned no results when I tried this the first time, but when I recreated the report from scratch it worked out fine.