Pivot grid grandtotal at row level

[Dileep gagan R]

Hi Team,

1. How can we show row level aggregate in pivot grid. for example

     Grouping of row  Country -> State -> City .

     Data is at city level then how can I see aggregate at Country level.

2. How will I get drilldown data for the same.

Thanks,

Dileep gagan R

[Manikandan Murugesan]

Hi Dileep, 

  

    

Please find the response below,   

   

Query	Response
1. How can we show row level aggregate in pivot grid. for example:  Grouping of row  Country -> State -> City. Data is at city level then how can I see aggregate at Country level.	We are unable to understand your exact requirement in your last update. So, could you please provide us more information about the same with code example/images/video (if possible). This would be helpful for us to proceed further.     Meanwhile, we have prepared a sample for your reference where you can find the row level aggregation. Please check the below sample.     Sample: https://stackblitz.com/edit/angular-kk4ag1-pua7kx?file=app.component.html     In the below screenshot, you can see that the Country->Products->Quantity grouping is achieved in pivot table where the subtotals for the quantity 31+51+90+25 = 197 is displayed in the product subtotal.
2. How will I get drilldown data for the same.	We suspect that your requirement is to achieve the drill down action. The drill down action can be achieved by two ways.  ·          Code behind   ·         On-demand    Please find the UG for your reference.    UG: https://ej2.syncfusion.com/angular/documentation/pivotview/drill-down/

     

Please let us know if you have concern. 

  

Regards, 

Saranya Sivan. 

[Dileep gagan R]

Hi, 

Thank you for the response, this resolves one of my requirement. 

We have one more requirement which is as follows, In the mentioned answer screenshot we have Units Sold and Units Sold Amount columns.

Requirement: 

1. We do not want to show grand total of Units Sold Amount column, but Units Sold need to be shown in grand total.

2. We have custom calculated field Total Cost which is Units Sold * Units Sold Amount, this works fine for column level, but when at aggregate level this is calculated as 

Aggregate of Units Sold * Aggregate of Units Sold Amount, but at grand total level we want plain aggregation of calculated field rather applying formula to calculate. How to achieve this.

Thanks,

Dileep gagan R

[Saranya Sivan]

Hi Dileep, 

  

Please find the response below,     

  

Query	Response
We do not want to show grand total of Units Sold Amount column, but Units Sold need to be shown in grand total.	We have modified the sample to hide the grand total of `Units Sold Amount` in the pivot table. Please find the sample for your reference.       Sample: https://stackblitz.com/edit/angular-kk4ag1-wf7nd8?file=app.component.html     Code Snippet:     enginePopulated(args: any) {      let pivotValues: any = this.pivotObj.pivotValues;      for (var j = 0; j < pivotValues[pivotValues.length - 1].length; j++) {        //to hide the grand totals of  Units Sold Amount column        if (          pivotValues[pivotValues.length - 1][j] &&          pivotValues[pivotValues.length - 1][j].isSum &&          pivotValues[pivotValues.length - 1][j].actualText === "Amount"        ) {          pivotValues[pivotValues.length - 1][j].formattedText = "";        }      }    }
We have custom calculated field Total Cost which is Units Sold * Units Sold Amount, this works fine for column level, but when at aggregate level this is calculated as Aggregate of Units Sold * Aggregate of Units Sold Amount, but at grand total level we want plain aggregation of calculated field rather applying formula to calculate. How to achieve this.	We suggest you to do the aggregation manually. It can be achieved by using aggregateCellInfo event where you can get the raw data and type of each aggregated values to customize your own aggregation.      Meanwhile, we have prepared a sample for your reference. Please check the below sample and UG.     Sample: https://stackblitz.com/edit/angular-kk4ag1-utbvdd?file=app.component.html     UG: https://ej2.syncfusion.com/angular/documentation/pivotview/aggregation/#aggregatecellinfo

  

Please let us know if you have concern. 

  

Regards, 

Saranya Sivan. 

 

 

[Dileep gagan R]

Hi Dileep, 

  

Please find the response below,     

  

Query	Response
We do not want to show grand total of Units Sold Amount column, but Units Sold need to be shown in grand total.	We have modified the sample to hide the grand total of `Units Sold Amount` in the pivot table. Please find the sample for your reference.       Sample: https://stackblitz.com/edit/angular-kk4ag1-wf7nd8?file=app.component.html     Code Snippet:     enginePopulated(args: any) {      let pivotValues: any = this.pivotObj.pivotValues;      for (var j = 0; j < pivotValues[pivotValues.length - 1].length; j++) {        //to hide the grand totals of  Units Sold Amount column        if (          pivotValues[pivotValues.length - 1][j] &&          pivotValues[pivotValues.length - 1][j].isSum &&          pivotValues[pivotValues.length - 1][j].actualText === "Amount"        ) {          pivotValues[pivotValues.length - 1][j].formattedText = "";        }      }    }
We have custom calculated field Total Cost which is Units Sold * Units Sold Amount, this works fine for column level, but when at aggregate level this is calculated as Aggregate of Units Sold * Aggregate of Units Sold Amount, but at grand total level we want plain aggregation of calculated field rather applying formula to calculate. How to achieve this.	We suggest you to do the aggregation manually. It can be achieved by using aggregateCellInfo event where you can get the raw data and type of each aggregated values to customize your own aggregation.      Meanwhile, we have prepared a sample for your reference. Please check the below sample and UG.     Sample: https://stackblitz.com/edit/angular-kk4ag1-utbvdd?file=app.component.html     UG: https://ej2.syncfusion.com/angular/documentation/pivotview/aggregation/#aggregatecellinfo

  

Please let us know if you have concern. 

  

Regards, 

Saranya Sivan. 

 

 

Thanks, this achieved our requirement.

We have another query, with respect to same aggregation of the values.

 

In the above mentioned screenshot, Total cost is a calculated field which is QTY*COST​, This works fine at individual level but at grand total , it should be ​SUM(Total Cost) ​ but it is calculating ​

​SUM(QTY)*SUM(COST) . ​How to achieve it for calculated fields

Thanks,

Dileep gagan R

[Saranya Sivan]

Hi Dileep, 

  

We suggest you to do the aggregation manually. It can be achieved by using aggregateCellInfo event where you can get the raw data and type of each aggregated values to customize your own aggregation.  

  

Meanwhile, we have prepared a sample for your reference. Please check the below sample and UG. 

  

Sample: https://stackblitz.com/edit/angular-kk4ag1-utbvdd?file=app.component.html 

  

UG: https://ej2.syncfusion.com/angular/documentation/pivotview/aggregation/#aggregatecellinfo 

  

Please let us know if you need any further assistance on this. 

  

Regards, 

Saranya Sivan.