1

I am using GROUP BY ROLLUP in query to get totals & subtotals of aggregate columns.But while the result of total & subtotals are displayed i am getting their labels as Null instead of Total & Sub total. Here is the query i am using to populate below table.

I am getting Total in First row but its labeled as Null and then each subtotal after each Breakdown but again the 'Sub Total' is labeled as again Null.

I am using GROUP BY Rollup on this 2 fields.

SBQQ__Quote__r.SBQQ__Account__r.Geo__c,SBQQ__Quote__r.Owner.Name

How can i get label as TOTAL & SUB-TOTAL? I am looking for TOTAL & Sub total of rows

[SELECT SBQQ__Quote__r.Owner.Name ownerName,SBQQ__Quote__r.SBQQ__Account__r.Geo__c accountGeo ,SUM(SBQQ__Quote__r.New_Recurring_Amount_formula__c) recurringAmount,
                                      SUM(SBQQ__Quote__r.New_One_Time_Amount_rollup__c) oneTimeAmount,COUNT(OrderNumber) ordNo FROM Order where (Opportunity_Closed_Date__c >=:currentFQStartDate) AND (Opportunity_Closed_Date__c <=:currentFQEndDate)
                                      GROUP BY ROLLUP(SBQQ__Quote__r.SBQQ__Account__r.Geo__c,SBQQ__Quote__r.Owner.Name) ORDER BY SBQQ__Quote__r.SBQQ__Account__r.Geo__c ASC,SBQQ__Quote__r.Owner.Name ]

Table data

EDIT - Added the Code where I am building the HTML body.

EDIT2 - Changed the Code as mentioned in the answer given. EDITED After adding the code mentioned in the Answer below

    htmlBody = '<table border="1"  class="slds-table slds-table_cell-buffer slds-table_bordered slds-table_col-bordered" style="border-collapse: collapse"><caption><b>Report for Orders Closed Current Quarter</b></caption><tr><th></th><th>$New Recurring Amount</th><th>$New OneTime Amount</th><th>No.of Deals</th></tr>';
for(AggregateResult clsOrd : [SELECT SBQQ__Quote__r.Owner.Name ownerName,SBQQ__Quote__r.SBQQ__Account__r.Geo__c accountGeo ,GROUPING(SBQQ__Quote__r.Owner.Name) grpOwnerName,                         
                              GROUPING(SBQQ__Quote__r.SBQQ__Account__r.Geo__c) grpAccountGeo,SUM(SBQQ__Quote__r.New_Recurring_Amount_formula__c) recurringAmount,
                              SUM(SBQQ__Quote__r.New_One_Time_Amount_rollup__c) oneTimeAmount,COUNT(OrderNumber) ordNo 
                              FROM Order 
                              where (Opportunity_Closed_Date__c >=:currentFQStartDate) AND 
                              (Opportunity_Closed_Date__c <=:currentFQEndDate) AND SBQQ__Quote__r.SBQQ__Account__r.Geo__c!= null
                              GROUP BY 
                              ROLLUP(SBQQ__Quote__r.SBQQ__Account__r.Geo__c,SBQQ__Quote__r.Owner.Name) 
                              ORDER BY SBQQ__Quote__r.SBQQ__Account__r.Geo__c ASC,SBQQ__Quote__r.Owner.Name ]){
                                  
                                  Boolean isSubTotalByGeo = ((Integer)clsOrd.get('grpOwnerName')) == 1 &&
                                      ((Integer) clsord.get('grpAccountGeo')) == 0;
                                  // Detect  grandtotal (all geos)
                                  Boolean isGrandTotal = ((Integer)clsOrd.get('grpOwnerName')) == 1 &&
                                      ((Integer) clsord.get('grpAccountGeo')) == 1;
                                  String column1Val;
                                  if (isGrandTotal) {
                                      column1Val = 'GRAND TOTAL';
                                  }
                                  else if (isSubTotalByGeo) {
                                      column1Val = 'SUB TOTAL';
                                  }
                                  else {
                                      column1Val = (String) clsOrd.get('ownerName');
                                  }
                                  system.debug('column1Val '+column1Val);
                                  newRecurringAmt = Double.valueOf(clsOrd.get('recurringAmount'));
                                  newOneTimeAmt = Double.valueOf(clsOrd.get('oneTimeAmount'));
                                  if(accountGeo!='' && accountGeo!= clsOrd.get('accountGeo')){
                                      system.debug('newRecurringSubTotal '+newRecurringSubTotal);
                                      newRecurringSubTotal = 0;
                                      newOneTimeSubTotal = 0;
                                      orderSubTotal = 0;
                                  }
                                  
                                  if(accountGeo!= clsOrd.get('accountGeo')){
                                      htmlBody += ' <thead><tr class="slds-line-height_reset"><th class="" scope="col"><div class="slds-truncate" title= '+clsOrd.get('accountGeo')+' Breakdown>'+clsOrd.get('accountGeo')+' Breakdown</div></th><th></th><th></th><th></th></tr></thead>';
                                  }
                                  htmlBody += '<tr class="slds-hint-parent"><td>' +clsOrd.get('ownerName') + '</td>';
                                  htmlBody += '<td>' +newRecurringAmt.format() + '</td><td>'+newOneTimeAmt.format() + '</td>';
                                  htmlBody += '<td>' + clsOrd.get('ordNo') + '</td></tr><br/>';
                                  accountGeo = String.valueOf(clsOrd.get('accountGeo'));
                                  
                              }

SO I am directly using this htmlBody in EmaiService to send the email.

EmailService service = new EmailService(email);
service.body = 'Dear'+' '+orderOwnerName+','+ '<br/><br/>Here is Report'+' '+reportName+' '+'Containing Closed Orders.<br/><br/>'+emailBody;
service.body += '<br/><br/>Regards,<br/>Salesforce Admin Team.'; 
service.isHtml = true;
service.toAddresses = new List<string>();
  • see developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/… and use Apex to insert the desired labels for subtotals – cropredy Nov 18 '20 at 1:57
  • But how can i add that to field which is already aggregated. SUM(SBQQ__Quote__r.New_Recurring_Amount_formula__c) like this is giving syntax error SUM(GROUPING(SBQQ__Quote__r.New_Recurring_Amount_formula__c)) – Deepak Agarwal Nov 18 '20 at 7:29
  • presumably you are using a wrapper (inner) class to make available the aggregate data to the VF? page. So, when you populate the wrapper object, test for the presence of a subtotal, grand total row and coerce in your own labels – cropredy Nov 24 '20 at 6:23
  • I am not using any wrapper. I wrote a method which returns the html body prepared using that query and then I am using that htmlBody in EmailService to send the table. And i am sending email with table shown not displaying in VF. So here with GROUP BY ROLLUP i think the total & sub total is getting populated but how can i determine it here.? EDITED my question to add more code & details. – Deepak Agarwal Nov 24 '20 at 7:56
2

You need to change your Aggregate query to look like this:

for(AggregateResult clsOrd : 
     [SELECT 
            SBQQ__Quote__r.Owner.Name ownerName,
            SBQQ__Quote__r.SBQQ__Account__r.Geo__c accountGeo,
            GROUPING(SBQQ__Quote__r.Owner.Name) grpOwnerName,                         
            GROUPING(SBQQ__Quote__r.SBQQ__Account__r.Geo__c) grpAccountGeo ,                                    
            SUM(SBQQ__Quote__r.New_Recurring_Amount_formula__c) recurringAmount,                                    
            SUM(SBQQ__Quote__r.New_One_Time_Amount_rollup__c) oneTimeAmount,
            COUNT(OrderNumber) ordNo 
      FROM Order 
      WHERE (Opportunity_Closed_Date__c >=:currentFQStartDate) AND 
            (Opportunity_Closed_Date__c <=:currentFQEndDate)
      GROUP BY 
            ROLLUP(SBQQ__Quote__r.SBQQ__Account__r.Geo__c,SBQQ__Quote__r.Owner.Name) 
      ORDER BY SBQQ__Quote__r.SBQQ__Account__r.Geo__c ASC,SBQQ__Quote__r.Owner.Name ]){

  // Detect subtotal by Geo
  Boolean isSubTotalByGeo = ((Integer)clsOrd.get('grpOwnerName')) == 1 &&
                            ((Integer) clsord.get('grpAcccountGeo')) == 0;
  // Detect  grandtotal (all geos)
  Boolean isGrandTotal = ((Integer)clsOrd.get('grpOwnerName')) == 1 &&
                         ((Integer) clsord.get('grpAcccountGeo')) == 1; 

 // Then use these Booleans to decide how to populate the first column of your HTML table
 String column1Val;
 if (isGrandTotal) {column1Val = 'GRAND TOTAL';}
 else if (isSubTotalByGeo) {column1Val = 'ALL OWNERS';}
 else {column1Val = (String) clsOrd.get('ownerName');}

 // construct HTML here ...
}

Documentation reference: Using GROUPING(fieldName) to Identify Subtotals

Some asides:

  1. I find that using ALL CAPS for SOQL aliases improves readability
  2. Nicely formatting complex (or lengthy SOQL improves readability
  3. If ever there was a use case for a VF email template that used a VF component+controller, this would be it. You decouple the the formatting of the email to the markup in the VF component and you then use the component controller to query and build a data structure of rows that are made available via a getter to an apex:dataTable VF component. All the styling is done in the VF markup and the controller only needs to worry about data and data structure. In other words, Separation of Concerns
  • Thanks for the response. But by just changing the Aggregate query null values in table are not getting change. – Deepak Agarwal Nov 25 '20 at 13:02
  • I am not understanding how can i replace that null values with Total & subtotal. The thing is i am not understanding from where this null values(ROWS) are getting populated. – Deepak Agarwal Nov 25 '20 at 13:04
  • the example in developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/… was pretty self explanatory. Each row coming back from the Aggregate query is either a detail row, a subtotal row, or a grand total row and the Grouping(xx) fields in the result row tells you this – cropredy Nov 25 '20 at 16:25
  • Yes but even the table in this developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/… is having Null Labels to Grand total rows right? – Deepak Agarwal Nov 27 '20 at 7:37
  • In fact with the given solution above it is still giving me same table with Null column labels. – Deepak Agarwal Nov 27 '20 at 8:12
0

Added comments as required.

    htmlBody = '<table border="1"  class="slds-table slds-table_cell-buffer slds-table_bordered slds-table_col-bordered" style="border-collapse: collapse"><caption><b>Report for Orders Closed Current Quarter</b></caption><tr><th></th><th>$New Recurring Amount</th><th>$New OneTime Amount</th><th>No.of Deals</th></tr>';
    for(AggregateResult clsOrd : [SELECT SBQQ__Quote__r.Owner.Name ownerName,SBQQ__Quote__r.SBQQ__Account__r.Geo__c accountGeo ,GROUPING(SBQQ__Quote__r.Owner.Name) grpOwnerName,                         
                                  GROUPING(SBQQ__Quote__r.SBQQ__Account__r.Geo__c) grpAccountGeo,SUM(SBQQ__Quote__r.New_Recurring_Amount_formula__c) recurringAmount,
                                  SUM(SBQQ__Quote__r.New_One_Time_Amount_rollup__c) oneTimeAmount,COUNT(OrderNumber) ordNo 
                                  FROM Order 
                                  where (Opportunity_Closed_Date__c >=:currentFQStartDate) AND 
                                  (Opportunity_Closed_Date__c <=:currentFQEndDate) AND SBQQ__Quote__r.SBQQ__Account__r.Geo__c!= null
                                  GROUP BY 
                                  ROLLUP(SBQQ__Quote__r.SBQQ__Account__r.Geo__c,SBQQ__Quote__r.Owner.Name) 
                                  ORDER BY SBQQ__Quote__r.SBQQ__Account__r.Geo__c ASC,SBQQ__Quote__r.Owner.Name ]){
                                      
                                      Boolean isSubTotalByGeo = ((Integer)clsOrd.get('grpOwnerName')) == 1 &&
                                          ((Integer) clsord.get('grpAccountGeo')) == 0;
                                      // Detect  grandtotal (all geos)
                                      Boolean isGrandTotal = ((Integer)clsOrd.get('grpOwnerName')) == 1 &&
                                          ((Integer) clsord.get('grpAccountGeo')) == 1;
                                      String column1Val;
                                      if (isGrandTotal) {
                                          column1Val = 'GRAND TOTAL';
                                      }
                                      else if (isSubTotalByGeo) {
                                          column1Val = 'SUB TOTAL';
                                      }
                                      else {
                                          column1Val = (String) clsOrd.get('ownerName');
                                      }
                                      system.debug('column1Val '+column1Val);
                                      newRecurringAmt = Double.valueOf(clsOrd.get('recurringAmount'));
                                      newOneTimeAmt = Double.valueOf(clsOrd.get('oneTimeAmount'));
                                      if(accountGeo!='' && accountGeo!= clsOrd.get('accountGeo')){
                                          system.debug('newRecurringSubTotal '+newRecurringSubTotal);
                                          newRecurringSubTotal = 0;
                                          newOneTimeSubTotal = 0;
                                          orderSubTotal = 0;
                                      }
                                     

> //Null check condition for First row not to appear.

                                      if(accountGeo!= clsOrd.get('accountGeo') && clsOrd.get('accountGeo')!= null){
                                          htmlBody += ' <thead><tr class="slds-line-height_reset"><th class="" scope="col"><div class="slds-truncate" title= '+clsOrd.get('accountGeo')+' Breakdown>'+clsOrd.get('accountGeo')+' Breakdown</div></th><th></th><th></th><th></th></tr></thead>';
                                      }

> //Here is the Main part where I had to add the column1Val for which I
> was struggling.

                                      htmlBody += '<tr class="slds-hint-parent"><td>' +column1Val + '</td>';
                                      htmlBody += '<td>' +newRecurringAmt.format() + '</td><td>'+newOneTimeAmt.format() + '</td>';
                                      htmlBody += '<td>' + clsOrd.get('ordNo') + '</td></tr><br/>';
                                      accountGeo = String.valueOf(clsOrd.get('accountGeo'));
                                      
                                  }

Reports Using Aggregate result

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.