formula3

Reports in Salesforce are pretty easy to be created. Anybody who hasn’t had much experience with Salesforce can still go ahead and create a report after an hour’s struggle. However, many advanced Salesforce admins find using the formulas in reports to be a bit intimidating. It’s not that formulas are complicated. It’s just that since people are not used to it, they find it to be scary. Honestly, formulas are way better than making your hands dirty with coding and the value they offer to your reports is simply mind-blowing !

So, in this post I will walk through the PARENTGROUPVAL() function which is one of the most powerful functions to be used in Salesforce report formula. Before I jump into this function, let’s go ahead and create a simple summary report and we will eventually see when will this function come handy. Simply follow the below steps to create a summary report.

  • Create a fresh new report from the standard opportunities Report Type.
  • Click on the Drop down beside the Type field and select “Group by this field”
  • Your report has now transformed into a Summary report grouped by the Type field
  • Now Click on the dropdown beside the Amount field > Summarize this field > Sum > Apply.
  • Click on the Shows dropdown at the top and uncheck Details and Dropzones so that your report looks clean.

The report should now look something like this

p1

This report gives you a pretty good picture about the Amount of opportunities associated with each Type. The report also gives you a grand total of the Amount of all the Opportunities.

Now, what if you want to identify the percentage of the “Existing Customer – Upgrade”  amount out of the Grand total ?  This is where PARENTGROUPVAL() function comes into the picture. This function can help you identify the summarized amount of each group and eventually divide it by the grand total. So, below is the syntax for this function.

  • To display the percentage of fields by grouping at a summary level, select the Summary field to consider  in each grouping and use the formula structure PARENTGROUPVAL(summary_field, grouping_level)
  • Complete Formula: summary_field/ PARENTGROUPVAL(summary_field, GRAND_SUMMARY)

We will now see how do we implement this in our scenario.

On the left hand-side pane click “Add formula” and enter the formula as seen in the screenshot below

Formula


AMOUNT:SUM / PARENTGROUPVAL(AMOUNT:SUM , GRAND_SUMMARY)

p2

Now, you will see the percentage share of each Type as seen in the screenshot below.

p3

Do you now see how simple the PARENGROUPVAL() function is and the value that it offers ?

Feel free to comment on the post if you have any doubts.

Happy using Salesforce Reports !