Learn Salesforce Reports with Examples – Matrix Reports with Custom Summary Formula

Learn Salesforce Reports with Examples – Matrix Reports with Custom Summary Formula is the part of the series “Learn Salesforce Reports with Examples” where I will be sharing some key features for the Salesforce Reports which is an inspiration from a similar series on formulas – “Learn Salesforce Formulas with Examples ” and the numerous related queries on Answers Community . Hereby, I bring you the first post in the series – Learn Salesforce Reports with Examples – Matrix Reports with Custom Summary Formula

If you are hearing the word “Custom Summary Formula ” or “Matrix Reports” for the first time,I would suggest you to check the official documentation for Custom Summary Formula and How to create Matrix Report first and then come back again.Also would suggest you to go through Reporting Workbook as well.Now Let’s deep dive into how to use custom summary formula with an example:

Scenario

An Administrator wants to create an Opportunity matrix report which should be such that it calculates the % of Opportunity Amount that is closed per Account Name .Basically ,They should have two groupings -Group by Account Name at row level and the opportunity Closed at column level,Something like the below:

Solution

We need to Create a Report of Matrix type on Opportunity such that it has two groupings-Account name at row level and Closed Status at column level and then add two custom summary formulas to that report.

Create Matrix Report

• Go to Report Type->New Report->Select Opportunity Report Type and select Opportunities.

• Then click create and then create matrix report as explained with steps below:

1. Select the Matrix Format Report Type
2. Drag and Drop Account Name Field to create a row grouping.
3. Drag and Drop the Closed Field to create a column Grouping
4. This is where you need to Create new custom summary formulas,that is explained later separately.
5. Custom Formula Fields will appear once we create them later.
6. Same as 5.
7. Same as 5.

Add a Simple Custom Summary Formula

Now let us first create the simple custom summary formula which is referred as in step 4 above,which will give us the sum of all opportunities grouped by Account Name.

1. As stated in step 4 in previous snippet,Just add  a new custom summary formula.Enter the Column Name
2. Keep the format as number as we just want to show number.
3. Select the level where you want to display the formula calculation in the report
4. We want to display it at Account Name level so We have selected the specific row level – Account Name
5. Select the summary field you want to display from drop-down list.
6. Select Amount :SUM which will be is used in our formula.
7. Please use Insert for any function you use,Since We have not used this function anytime so you can ignore this step.
8. If you want to know any information you can click on the Help on this Function Link .

Add an Advanced Custom Summary Formula

Now let us create the second Formula which will be used to get the %Amount of Opportunities per Account and closed status,For this We will be using PARENTGROUPVAL function.(Want to know more about PARENTGROUPVAL and PREVGROUPVAL?Check here ) :

1. Add  a new custom summary formula.Enter the Column Name similar to step 1 in previous snippet.
2. Change the Format to Percent here.
3. Keep the decimal places to 2.
4. Select the level where you want to display the formula calculation in the report
5. We want to display it at Account Name level so We have selected the specific row level – Account Name at A
6. We want to show the % on both open and closed separately so add Closed at B
7. Select the summary field you want to display from drop-down list.
8. Change the formula as mentioned.To check the syntax,Check here.
9. Parent Grouping Level selected here is Row Grand Summary and Column Grand Summary
10. Don’t forget to use Insert for  to select PARENTVAL.
11. If you want to know any information you can click on the Help on this Function Link .

Add on Tips for Custom Summary Formula in Salesforce Reports:

• You cannot add a Filter criteria by custom summary formula
• You cannot group or Bucket a Summary or Matrix report by a Custom Summary Formula.
• You can use a Bucket in a Custom Summary Formula, but not the other way around.
• It works for summary and matrix reports only.
• Check this awesome tutorial – Creating a Matrix Report with Custom Summary Formulas
• Want to learn more about Report Summary Functions?Check here
• Check Salesforce Documentation for the step to step guide to Build a Custom Summary Formula.
• Want to try Summary Formula in a Joined Report?Check here
• Do not miss this help doc – Get the Most Out of Custom Summary Formulas