Search code examples
salesforceapexvisualforcemaster-detail

Creating a Salesforce report on Master Detail relationship

I am trying to create a report on one to many Master-Detail relationships. I need to get results on Opportunity(Master) related to the many financiers(Detail). The financiers have a picklist field that can approve or deny or approve conditionally. Some opportunities can have one financier approved and others denied. some have denied by all the financiers. I need to get results for the opportunities that were denied by all the financiers they have applied for. When I tried to use the filter to show denied financiers I am getting results for opportunities that were denied but some of the results were approved by another financer. How do I run a logic to discard the opportunities that were approved by at least one financier and denied by all the financiers? I know that this can be achieved by creating a new field on opportunity or by creating a custom report using a visualforce page. Which is the more subtle and feasible solution?

Solution

  • This is a SF admin question rather than coding. You might have better luck at https://salesforce.stackexchange.com.

    As a rule of thumb - si this report going to be the only place you need this kind of data. For "clean" solution I'd be tempted to make up to 3 rollup summary fields on Opportunity, something like "count all financiers", "count approved", "count rejected". And then your report gets significantly simpler.

    But if it's one-off requirement or other reason you can't do rollups (for example reaching limit of rollup fields / not wanting to waste them on something considered trivial) you might still be able to pull it off with report.

    I need to get results for the opportunities that were denied by all the financiers they have applied for

    I'd try with something called "cross filter". Try help articles or this might be a good start: https://salesforce.stackexchange.com/a/23697/799

    Maybe "Opportunities with Financiers" report, filtered on Financiers.Status = Declined. Plus cross filter saying "Opportunities without Financiers where status != Declined".

    Or maybe 2 cross filters. You'll have to experiment a bit.