Print this page

Is it possible to filter a Joined Report type, so that each blocks show only master record associated with at least one child records?

Knowledge Article Number 000221790
Description Having created a Joined Report, within the Blocks, you might sometimes see empty lines, which you want to leave out.

Example, Grouped by Account Name:
First Block: Accounts with Opportunities
Second Block: Accounts with CustomObject

You would like to show only Accounts with both Open Opportunities and a Custom Object, but no Accounts which have only Opportunities Open or no Custom Object.

Using a Filter on the Opportunity for Open, might still show Accounts which have none Open, in the other block, since you are not filtering that Block on the same condition.

Currently, the only way to filter both blocks in the same way, would be a field on the Object shared for both reporting blocks, in this example, Accounts.

A Roll-Up-Summary field can be used to get information on the level of the top Object.

Note: Roll-Up-Summary can be used only in Master Detail relationship.

Request to incorporate this into Salesforce, can be found on the Idea Exchange:
Resolution Workaround:

On Accounts (example) create two new fields:
  1. Roll-Up-Summary field, count of Opportunity, filter for only Open Opportunities
  2. Roll-Up-Summary field, count of Custom Object
If you use a Custom Report Type, you might need to add the fields to it, for Standard Report Type, it will be done automatically.

Now in both Blocks at a filter for both new fields, not equal to zero.

With this your report should show only lines, where the Account has a related custom object, and an Open Opportunity.

promote demote