Thursday, April 12, 2012 - Using PARENTGROUPVAL in a Reporting Formula

A customer of mine wanted to create a 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 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: