+ Start a Discussion

Estimate the ordered products from customer in report

I am trying to define a report to find the number of customers buying m-number of products. I thought that I found solution, but it still doesn’t work  correctly, so I need your help and ideas
I defined a summary report with Opportunities and Products; I defined rollup summary fields on the account and opportunity to get the number of ordered products from all opportunities from this account. My rollup summary field on the account calls “Ordered products” . I defined groups for number of ordered Product, Account name and Product Name

1 Ordered product 
 Account A
  Product E
2 Ordered products 
 Account B
  Product C
  Product B
 Account C
  Product A
  Product D


The Problem is, when an account has more opportunities with the same product
Opportunity 1: Product A and Product B
Opportunity 2: Product A and Product C
the customer will be visible in the group with 4 products, and not in the group with 3 products, where he belongs,
Any ideas for this problem? 

Jeff MayJeff May
This will work as as long as you have < 10 products:

1) Create an Opp custom field that is a filtered Rollup count of Opportunity Product, and filter on Product Description or somthing

2) Create an Account custom field that is a Rollup sum of Opp.rollup

3) Create an Account formula field called 'Number of Products ordered" as follows:

IF(Acct.RollupA > 1, 1, 0) + IF(Acct.RollupB > 1, 1, 0) + IF(Acct.RollupC > 1, 1, 0)

4) Use the Account formula on your report.