## Friday, July 5, 2019

### Display Per Line Percent of Total (% of Total) on Saved Searches

A customer saved search with the following results is easily done using a simple CASE SQL formula:

 Status Total Customers Territory  X Territory Y Territory Z CUSTOMER-Closed Won 97 60 22 15 LEAD-Undecided 9 4 2 3 PROSPECT-Proposal 2 1 1 0 PROSPECT-Qualified 12 3 4 5 Total 120 87 23 10

To insert columns for a per-line % of Total that gives the below report, a more advanced SQL formula is needed.

 Status Total Customers Territory  X Territory X Territory Y Territory Y Territory Z Territory Z % of Total % of Total % of Total CUSTOMER-Closed Won 97 60 62% 22 23% 15 15% LEAD-Undecided 9 4 44% 2 22% 3 33% PROSPECT-Proposal 2 1 50% 1 50% 0 0% PROSPECT-Qualified 12 3 25% 4 33% 5 42% Total 120 87 73% 23 19% 10 8%

To compute for the per-line % of Total, use the following formula:

 Field Summary Type Function Formula Formula (Percent) Maximum Round to Hundredths sum(Group Formula)/ nullif(count({internalid}),0)

To create the sample report:

1. Navigate to Lists > Search > Saved Searches > New.
2. Select Customer.
3. Provide a search title.
4. Under Criteria tab, set the desired filters.
5. Under Results tab, add the following:

 Field Summary Type Function Formula Status Group Internal ID Count Formula (Numeric) Sum TO_NUMBER(CASE WHEN ({territory}= 'X') THEN 1 ELSE 0 END) Formula(Percent) Maximum Round to Hundredths SUM(TO_NUMBER(CASE WHEN ({territory}= 'X') THEN 1 ELSE 0 END))/ NULLIF(COUNT({internalid}),0) Formula (Numeric) Sum TO_NUMBER(CASE WHEN ({territory}= 'Y') THEN 1 ELSE 0 END) Formula(Percent) Maximum Round to Hundredths SUM(TO_NUMBER(CASE WHEN ({territory}= 'Y') THEN 1 ELSE 0 END))/ NULLIF(COUNT({internalid}),0) Formula (Numeric) Sum TO_NUMBER(CASE WHEN ({territory}= 'Z') THEN 1 ELSE 0 END) Formula(Percent) Maximum Round to Hundredths SUM(TO_NUMBER(CASE WHEN ({territory}= 'Z') THEN 1 ELSE 0 END))/ NULLIF(COUNT({internalid}),0)

6. Click Save & Run.