Ultimate Salesforce Reports: bars with a cumulative line in one report chart

The need to represent data visually using line chart showing cumulative values on top of a bar chart showing discrete values is almost universal. This can be easily verified by the amount of questions on the success community and the idea on ideaExchange that asks Salesforce to give users this ability. Check out:
https://success.salesforce.com/answers?id=9063A000000p9SrQAI

https://success.salesforce.com/answers?id=90630000000h2JkAAI – with all respect, here Steve Mo suggests it cannot be done (but it can be done in the report itself using formulas – stick with me)

https://success.salesforce.com/ideaView?id=08730000000jySzAAI – idea exchange

Salesforce by default gives an option to chart cumulative values as a line chart but it has the following limitations:
1. Not being able to combine it with bar charts
2. The additional value will also be cumulative
Sample image of what cumulitive chart editor looks like:

---

In this post, I will show you how to create a bar chart and a CUMULATIVE line chart on top of it in Salesforce:

Requirement: To create a chart that gives the total amount of leads created per year and a line showing cumulative number created. The following output is desired:

---

Solution:
1. Create a matrix report type (stick with me on why we chose matrix and not a summary)
2. Group by ‘created date’ and group dates by calendar year
3. Summarize by ‘one’ where one is the power of one formula. That is a formula of type number on Lead object which has a value of 1
The report builder should look like:

4. Add a bar chart (I prefer vertical over horizontal)
You should be able to see something like this:

…So far so good…

5. Now, we create a report formula (aka CSF) called “Cumulative number of leads” with the formula =

RowCount
+
IF(NOT ISBLANK(PREVGROUPVAL(RowCount, CREATED_DATE,1)),
PREVGROUPVAL(RowCount, CREATED_DATE,1), 0)
+
IF(NOT ISBLANK(PREVGROUPVAL(RowCount, CREATED_DATE,2)),
PREVGROUPVAL(RowCount, CREATED_DATE,2), 0)

Select the grouping as in the image below:

Immediately you will see that the formula is added to the grouping and your report builder looks like this:

Perfect?
As a validation that your formula worked, verify that thhe leads nubers are being accumulated as expected: 8+10=18 correct! and 8+10+2 = 30.. perfect!

6. Now to we need to add this to our chart. To do that click on edit chart. Under chart data tab, tick the combination charts checkbox. Display = line and Value = “Cumulative number of leads”. There you go! Our chart shows what we want:

7. Mission accomplished? Well almost there! Remember I asked you to stick around to know why we used matrix report even though we have grouped only by column. So this is where we change the “sum of one” label on the chart to something useful. Lets change it to “Number of leads”. Create a bucket field as follows:

8. Now drag this bucket field to ‘row grouping’ drop area and change you chart as in below image:

There you go!!!
We have achieved it and the final report looks like this:

Some considerations:
We used the formula only upto 3 grouping levels i.e. 3 years. You can add more levels into the formula to cover for more years.

Thank you for reading!
Cheers!
H Mann

Advertisements