I am using Person Accounts and have 2 custom objects: Ownership and Property.

The Ownership is a junction object and has 2 lookups: Account and Property.

I'd like to run a report with a filter, that gives me unique Accounts where House.Type = 'Maisonette'.

However when I run a report, I may get multiple Accounts because someone owns more than one Property which is a Maisonette.

Any help please?

  • Do you just want counts or to eliminate the duplicates all together? – Mike Chale Jun 19 '13 at 11:09
  • Is it possible to do both? (in seperate reports at least?) – cyorkston Jun 19 '13 at 13:54
  • You can't remove the duplicates in the report; the report builder doesn't do distinct so I'd recommend Excel. For counting, see salesforce.stackexchange.com/questions/8476/… – Mike Chale Jun 19 '13 at 13:58

If I understand correctly, in SOQL you'd express your requirement more or less like that?

FROM Account
WHERE Id IN (SELECT Account__c FROM Ownership__c WHERE Property__r.Type__c = 'Maisonette')

It's perfectly doable with a cross filter.

Let's say I want to see Accounts that have at least 1 Contact with title = Director:

enter image description here

Here's how it looks on the "run report" page (note the icon)

enter image description here

| improve this answer | |

You can't do this in the report builder; it is simply not that powerful. It would be possible query this data using a sub-query in Apex Code (e.g. if you wanted to use Visualforce), by post-processing the data in Excel, or by using a third-party reporting tool that supports this functionality; a list of the latter can be found on the AppExchange.

| improve this answer | |
  • 1
    I respectfully disagree :) – eyescream Jan 7 '14 at 22:16
  • 1
    @eyescream And I learnt something new. That'll teach me not to read the release notes each release. – sfdcfox Jan 7 '14 at 22:34

We have been using Excel and Access to handle this. They don't allow me to do the cool programming stuff here. :'(

| improve this answer | |

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.