Thursday, April 12, 2012

Salesforce.com - Using PARENTGROUPVAL in a Reporting Formula

A customer of mine wanted to create a Salesforce.com Report which grouped together leads by status, owner and owner's manager and then calculate a "conversion rate" for each owner based on the final status of the lead.


Such a requirement calls for the use of the PARENTGROUPVAL reporting function.


Here are the step by step directions on how to build thisvformula using PARTENTGROUPVAL in a Salesforce.com Summary Report.



  1. Create a summary report, add the necessary fields for Manager, Owner, Status, etc., it should look something like this:
  2. Next add grouping by Owner and Status:

  1. Next, add a formula, name it “Conversion Rate”, select “Percent” as the Format and 0 for the number of Decimal Places and select Grouping 2: Status for the location of the formula to be displayed:
  2. Next you start to build the formula. Click Summary Fields >> Record Count and the word RowCount will be added to the formula builder. This represents the number of rows for the grouping level specified in step #3: >>
  3. Click Operators >> Divide and “/” will appear next to the word RowCount
  4. Next, under functions, click Summary, then PARENTGROUPVAL, be sure to select Lead Owner (not Grand Summary) for the grouping level. Then click the insert button: >>
  1. Your formula should now read RowCount/PARENTGROUPVAL(summary_field, OWNER).
  2. The last step is to overwrite the placeholder “summary_field” with RowCount and click OK

That should do it. Your results should look something like this when you run the report:


You can also use this formula in a matrix report, however the syntax is slightly different:



RowCount/PARENTGROUPVAL(RowCount, OWNER, COLUMN_GRAND_SUMMARY)


9 comments:

  1. I spent days messing with this kind of problem and made zero headway until I found this page.

    Much appreciated!

    ReplyDelete
    Replies
    1. Happy to help!

      Delete
    2. Anonymous1:42 PM

      Me too! Thanks man. This is a great, simple breakdown of an awesome feature!

      Delete
  2. Anonymous6:26 AM

    Is there anyone can tell me how to show report result in percentage format?
    For instance, in calendar event Summary report, Scenario Like This

    What I can get now is like:

    FirstName %SaleDescription

    Lead OWner: Krishna(35 Records)
    Disposition :Sale(3 Records) 8.57%
    Disposition :Other(32 Records) 91.43 %

    Lead Owner:Reddy(3 Records)
    Disposition :Other(3 Records) 100.00 %

    Grand Toatl:(38 Records)

    What I Want Is Like:
    FirstName %SaleDescription

    Lead OWner: Krishna(35 Records)
    Disposition :Sale(3 Records) 8.57%
    Disposition :Other(32 Records) 91.43 %

    Lead Owner:Reddy(3 Records)
    Disposition :Sale(0 Records) 0.00 %
    Disposition :Other(3 Records) 100.00 %

    Grand Total:(38 Records)


    And I Am Using This Custom Summary Formulae:

    RowCount / PARENTGROUPVAL(RowCount, OWNER)


    Please advise with any ideas, thank you.


    ReplyDelete
    Replies
    1. See step 3 about. When defining your reporting formula field, set the "format" for the field to be “Percent”.

      Delete
  3. Anonymous5:04 PM

    Hi,

    Thanks for the great post. I have a matrix report of Opportunities and line items. Its grouped exactly the same way you have. I am not able to drag the formula onto the report. I tried different combinations of grouping by grand summaries and the grouped fields, etc, but nothing works. Can you tell what I could be missing?

    Thanks,
    kp

    ReplyDelete
    Replies
    1. Try building it as a Summary report 1st, then converting to a matrix

      Delete
  4. Anonymous3:22 AM

    Hi,

    I have to compare amount of opportunities that were created this year and the previous year till the same date.
    Example:
    today is 20.07.2013
    So I have to compare the amount of opportunities that were created this year starting from 01.01.2013 till today 20.07.2013 to the amount of opportunities that were created previous year in the same period of time, from 01.01.2012 till 20.07.2013.

    Moreover dates 20.07.2012 and 20.07.2013 have to be dynamic, so that tommorow they will be 21.07.2012 and 21.07.2013 accordingly.

    Is it possible to build such kind of reports.

    Thank you for your help!

    Best Regards,
    Sergey

    ReplyDelete
  5. Anonymous2:02 AM

    Hi,

    Thanks for this article that helped me a lot, i just have an issue with the total. i want to know if it's possible to have the conversion rate total in the grand totals area

    thanks

    ReplyDelete

Step-by-Step on How to Update Git on Mac

Good article on updating Git on your Mac, leveraging Brew. https://www.michaelcrump.net/step-by-step-how-to- update-git/ Check current ...