UX Wednesday: Simple Trick to Make Grand Totals More Appealing

The article was originally published on our linkedin.com page.

In this article, I'll share an easy trick to make your custom SAQL table in CRM Analytics look even better. The goal is to follow some simple steps that will result in a well-formatted header showcasing totals, with different colors, bold text, and more.

This trick proves especially useful when you need to create a custom table in SAQL. As we know, this is often essential for blending multiple data streams, implementing custom windowing functions, and more.

Let's take a look at my example:

q = load "opportunities"
q = group q by ('Account.Name', 'Name');
q = foreach q generate 
'Account.Name', 
'Name', 
sum('Amount') as 'Amount',
sum('Amount')*0.84 as 'Net Amount',
sum('Amount')*1.27 as 'Another Amount';

total = group q by all;
total = foreach total generate 
"TOTAL" as 'Account.Name',
null as 'Name',
sum('Amount') as 'Amount',
sum('Net Amount') as 'Net Amount',
sum('Another Amount') as 'Another Amount';

table = union total, q; 

(No sophisticated operations are used to maintain clarity in the example.)

As you can see, I've applied a simple grouping and generated some numbers. To get the total, I take my "q" stream, group by all, and generate totals for both numbers and a text "TOTAL" for the line title. Finally, I combine both streams using a union operation. After this step, you can perform additional grouping or any other operations, but for the purposes of this example, this is sufficient.

The result:

An uninteresting table with the total appearing as if it were just one of the lines 📉.

To improve its appearance, take a look at this code:

q = load "opportunities"
q = group q by ('Account.Name', 'Id');
q = foreach q generate 
'Account.Name', 
first('Name') as 'Name', 
sum('Amount') as 'Amount',
sum('Amount')*0.84 as 'Net Amount',
sum('Amount')*1.27 as 'Another Amount';

total = group q by all;
total = foreach total generate 
sum('Amount') as 'Amount_total',
sum('Net Amount') as 'Net Amount_total',
sum('Another Amount') as 'Another Amount_total';

table = union q, total;

Changes:

• Removed "TOTAL" from the 'total' stream (you can't include any dimension columns there).

• For each label generated, I added "_total" to it. Don't hesitate to use this even if your labels contain spaces.

• During the union operation, ensure your grand total is the last stream.

And this is the visual result:

Looks much better, doesn't it?

With this simple hack, you now have the option to add a nicely formatted grand totals to your SAQL table.