Skip to main content

SOQL as a Summary Report

When learning SOQL, I found it helpful to relate things back to something I was familiar with. I'll be using Summary Reports to help explain how to use GROUP BY and GROUP BY ROLLUP in SOQL.

First, a Salesforce Report

Let's start with a Salesforce Report of Accounts grouped by Billing State/Province:


To get this, I created a Report with the Accounts standard report type. Then I created the grouping, added some fields and ran the report.

Writing SOQL

I'll be executing my SOQL via the SFDX Command Line Interface (CLI). If you aren't familiar with this, check out my other post - SOQL from the Command Line

Feel free to use your preferred method to run your SOQL. If you don't have a preferred method, I talk about my favorites in the last section of another post - Why should I use SOQL?

GROUP BY

Here's our example Salesforce Report done via SOQL:


The key difference is that the Salesforce Report gives us data about each record and SOQL just gives us the totals for each grouping. Depending on what you're trying to do, the additional data given by a Salesforce Report may make it the right tool for the job.

In SOQL, we are
  1. SELECT'ing the BillingState field as the field to group on
  2. COUNT'ing the number of Ids returned for each record in the group
  3. Getting data FROM the Account object
  4. GROUP'ing BY the BillingState field
You'll notice that we get a null BillingState in our result. We could have added a WHERE Clause and filtered out null values, but that's a lesson for another day.

GROUP BY ROLLUP

Salesforce Reports have the ability to create nested groupings. For example, if we took our sample report and added Type as a grouping we would see Accounts by Billing State/Province and then by Type:


We can do the same thing in SOQL by using GROUP BY ROLLUP:


In SOQL, we are
  1. SELECT'ing the BillingState and Type fields as the fields to group on
  2. COUNT'ing the number of Ids returned for each record in each group
  3. Getting data FROM the Account object
  4. GROUP'ing BY ROLL'ed UP values of the BillingState and Type fields
I must say, the results here are not super readable at first. The trick is to focus on any fields you set as sub-groups, reading down them until you reach the last null result for the sub-group. This will be the total number of records for the primary grouping.

Let's take CA BillingState as an example. The last null result in the Type column shows a count of 2. This is what we expect because the sample report above has 2 CA Accounts.

The CA BillingState example is an interesting one because it has 2 null results in the Type column. Looking back at our sample report, we can see that the sForce account has a null Type and it is sub-grouped below the CA BillingState. The result of our GROUP BY ROLLUP is reading the null Type and setting it as a sub-group result in the Type column. The Salesforce Report and SOQL are doing the same thing here, they're just displaying them differently.

Continued Learning

I hope you enjoyed this introduction to GROUP BY and GROUP BY ROLLUP in SOQL. If you'd like to go deeper, I recommend checking out these resources for some continued learning:

Comments

Popular posts from this blog

Salesforce Lighting Migration: Plan for Success

In April 2018, I led the Salesforce Lightning Migration at Postmates. This is how I would do it over again if I could.


Define Goals The goals of a Lightning Migration are: Enable Lightning ExperienceConfigure Lightning ExperienceDocument all Salesforce processesCreate training and support materialsLightning Experience is adopted Easy enough right? Let's go though the plan to see how we accomplish each one. Enable Lightning Experience Just turn it on right? Well, kinda.
Although it may be controversial, I recommend turning it on and hiding the option from all users. This can be done by removing the Lightning Experience User permission from your custom profiles. 
And yes, turn it on in production
Why? You're going to turn it on anyway. Salesforce has declared this as the path forward, so you either turn it on now or wait until they flip the switch for you.
Plus, turning this on ahead of time will make deploying from a sandbox much easier. Trust me.
Goal #1 Enable Lightning Exp…

SOQL from the Command Line

SOQL and the Command Line go together better than peanut butter and jelly. Here's why.
Once upon a time, I got tired of running Salesforce Reports to get miscellaneous data. I found Workbench and started using their interface to run SOQL queries. This was great for a while, but I knew there was a better way.

Pro-tip with Workbench: If you set a browser bookmark after your query runs, you can return to that bookmark and it will re-run your query!

Enter the SFDX Command Line Interface (CLI). Get ready to take your productivity into hyperdrive! 🚀🚀🚀
sfdx force:data:soql:query If there's one command you learn, this is the one. All you have to do is pass a query string into the -q parameter and you're in business. Check it out:
-r | --resultformat This is my 2nd favorite thing. How often do you need to export a csv from Salesforce? And how annoying is it to make throwaway Salesforce reports each time? Yeah, it's the worst.
-r to the rescue! Just add "-r csv" to …

Why should I use SOQL?

You need data, fast!
Whether it's exporting a csv or answering a question, SOQL can get you the result much faster than creating a Salesforce report.

My top 3 reasons to use SOQL are:
Get data fastGet answers fastFlexible access Get Data Fast How often do you find yourself making a throwaway report to check what is happening in the system? For example, you updated your Lead routing and want to see if it's been assigning Leads as you expect. Well you could make a report, run it and review the results. Or you could run a SOQL query.
The benefits of running a SOQL query in this case is that you can retrieve different data sets much quicker. In a Salesforce report, you are stuck with the Report Type you selected when creating the report. With SOQL, you can traverse object relationships in a much more natural way. Get Answers Fast Have you ever been in a meeting and someone asks a data question that no one has the answer to? Well SOQL comes in handy here because you can run a quick …