Reporting On Percentage Of Cases By Origin

The original version of this blog post comes courtesy of guest blogger and all-around Salesforce.com guru Jay Thayer (I've since modified it to apply to the new Report Builder).

Let's say you're trying to build a common report, Cases By Origin, but with a twist -- you want to know what percent of your interactions are coming via each channel.  Well, you could build a dashboard with it and eyeball it, but that's not very satisfying -- sometimes you just want cold, hard numbers.  The good news is that there is in fact a way to do it using some little-known capabilities of custom summary fields.

First, start by making yourself a standard Cases By Origin report by creating a custom report.  Search for the report type simply called Cases -- we don't need any related objects for this report.  Next make it a Summary Report by choosing that as the Format:

Now drag the Case Origin field onto the "Drop a field here to select a grouping" header.  This will group your cases by origin, so we're nearly there.

On the top of the left hand side you'll notice an Add Formula icon.  Drag that right on top of your Case Origin grouping (in the blue area).

This type of field will allow us to do math using both a summary of the group items and a summary of the whole report -- that's exactly what we need to calculate our percentages.  First, at the top of the page, give the formula field a name, and set its Format to Percent.  Now set it to display at Grouping 1: Case Origin, because that's the granularity at which we want this calculation to occur.

Now let's go to the formula builder and build it up. In order to calculate the percentage for each group, we need the record count for that group, and then we have to divide that by the total number of records in the report.  To get the record count for the group, we use the handy-dandy Summary Fields > dropdown, where you'll find that the top item is called Record Count.  That inserts an item that says "RowCount." That was easy enough.

Now we have to divide that value by the record count of the entire report.  For that, we can use one of the Custom Summary Functions -- to view them all, click the Functions dropdown.  Here we see a function called PARENTGROUPVAL.  This is exactly what we need, because in this case the parent group is the entire report.  Our only grouping summary is Grand Summary, which is fine because that's what we want. 

Double-clicking PARENTGROUPVAL adds the following to our report: 

PARENTGROUPVAL(summary_field, GRAND_SUMMARY).  

Now we just have to select the summary field.  Select that "summary_field" part of the above, and replace it with RowCount, so it says: 

PARENTGROUPVAL(RowCount, GRAND_SUMMARY) 

So your final formula should read: 

RowCount/PARENTGROUPVAL(RowCount, GRAND_SUMMARY) 

That will divide the rows in the current group by the rows in the entire report.  The completed formula field should look like this:

Save your custom summary formula field by pressing OK, and proceed with the report wizard as usual, selecting and ordering your columns and entering criteria.  When you run the report, you'll see that it's grouped by Case Origin, and each group is tagged with the percentage of total cases that came from that origin: Eureka!