Show/Hide Sub-totals in Pivot Tables

Aspose.Cells Product Family
1

Hi,


Please refer to the attached example workbook.

How do I toggle (i.e., show/hide) the sub-totals (e.g., Beverages Total, Condiments Total, etc.,) in the pivot table?

Can we do it at Pivot Table level rather than Pivot Field level?
2

Hi Hitesh,


Thank you for contacting Aspose support.

You can hide or show the Grand Totals at Pivot Table level whereas the Sub Totals are managed at Pivot Field level so you need to use the following snippet for your requirements.

Java

Workbook book = new Workbook(dir + “Example.xlsx”);
PivotTable table = book.getWorksheets().get(“Pivot Table”).getPivotTables().get(0);
table.setRowGrand(false); //Set true if you wish to show the grand row total
table.setColumnGrand(false); //Set true if you wish to show the grand column total
for(int i = 0; i< table.getRowFields().getCount(); i++)
{
table.getRowFields().get(i).setSubtotals(PivotFieldSubtotalType.NONE,true);
}
book.save(dir + “output.xlsx”);
3

Can I show different type of sub-totals for different measures/data fields, e.g., showing sum() for Quantity and avg() for Discount measures?


I think showing sub-totals at data field level makes more sense than row-field level!
4

It gives the below error if I set sub-totals for the data fields.


com.aspose.cells.CellsException: Subtotals are only valid for nondata fields
5

Moreover, it still shows the sub-totals rows even after using the above code snippet!


Can you please check it again at your end.
6
Hi Hitesh,

hitesh.dholaria:
Moreover, it still shows the sub-totals rows even after using the above code snippet!

Can you please check it again at your end.

I have tested the code before pasting in my response. Moreover, you can check the resultant spreadsheet (attached here) that it does not show the sub-totals for the Pivot Table's row fields. Please note, I have tested the scenario while using the latest version of Aspose.Cells for Java 8.8.0.3 (attached), therefore if you are using any older revision of the API, please give the latest version a try.
7
Hi again,

hitesh.dholaria:
It gives the below error if I set sub-totals for the data fields.

com.aspose.cells.CellsException: Subtotals are only valid for nondata fields

Yes, that is correct. You cannot add a sub-total for the Data Field while using Aspose.Cells APIs. If you can achieve this with Excel application then please modify your source spreadsheet with required representation and provide the spreadsheet here along with details of the manual steps you took to achieve the goal.

That said, Data Fields are actually calculated fields (Sum of Quantity or Average of Discount) therefore if you are looking to change the function of Data Field, that can be done using the PivotField.setFunction method.
8

What is the significance of the second parameter of setSubtotals(int subtotalType, boolean shown) method?


If it has to be set true in both the cases (show/hide sub-totals), what is the additional benefit we get with that parameter?
9

Hi Hitesh,


The second Boolean type parameter to the PivotField.setSubtotals method allows you to control the visibility of the sub totals for the RowFields or ColumnFields. If you pass false, the sub totals will be hidden and vice versa.
10
No, it does not hide sub-totals when passed false.

However, it makes the sub-totals hidden when it is called with the below values.
  • setSubtotals(PivotFieldSubtotalType.NONE, true).

Sounds a strange behavior!
11

Hi Hitesh,


Let me explain why you are not able to hide the sub totals for the row fields in your provided sample spreadsheet. Please note, the sub totals for the row fields are set to Automatic therefore you have to reset the type to AUTOMATIC while passing false as second parameter to setSubTotal method in order to hide them. As some times we are not aware of the sub total type therefore we can use the sub total type as NONE while passing true as second parameter to setSubTotal method to achieve the same result.

Please try following piece of code for verification.

Java

Workbook book = new Workbook(dir + “Example.xlsx”);
PivotTable table = book.getWorksheets().get(“Pivot Table”).getPivotTables().get(0);
for(int i = 0; i< table.getRowFields().getCount(); i++)
{
table.getRowFields().get(i).setSubtotals(PivotFieldSubtotalType.AUTOMATIC, false);
}
book.save(dir + “output.xlsx”);
12

Hi


Please can you provide a .NET equivalent of the solution.


Thanks
13

Hi,


See the equivalent .NET sample code:
e.g
Sample code:

Workbook book = new Workbook(dir + “Example.xlsx”);
PivotTable table = book.Worksheets[“Pivot Table”].PivotTables[0];
for (int i = 0; i < table.RowFields.Count; i++)
{
table.RowFields[i].SetSubtotals(PivotFieldSubtotalType.Automatic, false);
}
book.Save(dir + “output.xlsx”);

Thank you.
14

This doesn’t seem to do anything. Please can you show sample data and results?


Thanks
15

Hi,


Could you provide us your input and output Excel file (after using the code segment), we will check it soon.

Thank you.