Hide Sub-totals/grandTotals In both Rows & Columns in Pivot table

Aspose.Cells Product Family
1

Hi,

I am trying to hide the sub-totals of the Rows & Columns in the pivot table

But the sub totals still seems to be appearing in the final sheet

Source Code example ::

pivotTable.setRowGrand(false);
pivotTable.setColumnGrand(false);

    for(let i = 0; i< rowLabels.length; i++)
    {  
            pivotTable.getRowFields().get(i).setSubtotals(aspose.cells.PivotFieldSubtotalType.NONE,false);
        
    }


    for(let i = 0; i< columnLabels.length; i++)
    {   
            pivotTable.getColumnFields().get(i).setSubtotals(aspose.cells.PivotFieldSubtotalType.NONE,false);
     
    }

But still the sub-totals are showing up in the excel sheet ,
need some help on this

Attaching screenshot for your reference

Screenshot 2023-07-18 at 5.17.32 PM.png (290.7 KB)

as you can see in the above screenshot , grandTotals are NOT coming but still the subtotals are coming

need some help on how to achieve the hiding of sub-totals in pivot table

Thanks

2

@Veerendra1234,

Thanks for the screenshot.

Could you please try PivotField.setAutoSubtotals(false) API for your task.

Let us know with sample Excel file(s) if you still find the issue.

3

Hi
can you please share any sample node js example for this

Thanks

4

@Veerendra1234,

The API is also there in Aspose.Cells for Node.js via Java, see the API reference page.

5

hi ,
it doesn’t seem to work , attaching the sample excel file

empsummayrreport_18-07-2023_72ee067a-f502-489a-9959-4d0f3b696aaf.xls.zip (2.8 MB)

in this sheet you can see one data sheet on which I am creating a pivot sheet

As you can see in the below screenshot , totals & grandTotals are still coming in the sheet

Screenshot 2023-07-18 at 7.34.40 PM.png (333.3 KB)

Code ::

    for(let i = 0; i< rowLabels.length; i++)
    {  
            pivotTable.getRowFields().get(i).setAutoSubtotals(false);  // setting false for subtotals
    }

    for(let i = 0; i< columnLabels.length; i++)
    {         pivotTable.getColumnFields().get(i).setAutoSort(true);
              pivotTable.getColumnFields().get(i).setAscendSort(true);
              pivotTable.getColumnFields().get(i).setAutoSubtotals(false); // setting false for subtotals
        
    }

Can you please let me know , What am i missing here
Thanks

6

@Veerendra1234,

I opened your Excel file into MS Excel and found an error, see the screenshot attached.
sc_shot1.png (4.0 KB)

Could you please give us valid file which could be opened fine into MS Excel. We will check your issue soon.

7

@Veerendra1234,
Please try the following code:

		pivotTable.setRowGrand(false);
		pivotTable.setColumnGrand(false);

	    for(let i = 0; i< rowLabels.length; i++)
	    {  
	            pivotTable.getRowFields().get(i).setSubtotals(aspose.cells.PivotFieldSubtotalType.NONE,false);
	            //add this line to set auto subtotal is false
	            pivotTable.getRowFields().get(i).setAutoSubtotals(false);
	        
	    }


	    for(let i = 0; i< columnLabels.length; i++)
	    {   
	            pivotTable.getColumnFields().get(i).setSubtotals(aspose.cells.PivotFieldSubtotalType.NONE,false);
	            //add this line to set auto subtotal is false
	            pivotTable.getColumnFields().get(i).setAutoSubtotals(false);
	    }

Hope helps a bit.