We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Grand Total sort order: Descending

Aspose.Cells Product Family
#1
Hi,

I have created a pivot table and I would like to change the sorting order
of the grand total.(Please see the attached picture)

How can I do this using Aspose Cells smartmaker dotnet?

Thanks & Regards,
Periasamy T
#2

Hi Periasamy,


Thank you for contacting Aspose support.

You need to enable the AutoSort for the required PivotField and then specify the index on which you wish to sort the Grand Total. We have devised a generic solution for better elaboration of above concepts. Please check the following code snippet that creates a PivotTable from scratch and applies the descending sort on the Grand Total.

C#

var workbook = new Workbook();
var sheet = workbook.Worksheets[0];

var cells = sheet.Cells;
var cell = cells[“A1”];
cell.PutValue(“Sport”);
cell = cells[“B1”];
cell.PutValue(“Quarter”);
cell = cells[“C1”];
cell.PutValue(“Sales”);
cell = cells[“A2”];
cell.PutValue(“Golf”);
cell = cells[“A3”];
cell.PutValue(“Golf”);
cell = cells[“A4”];
cell.PutValue(“Tennis”);
cell = cells[“A5”];
cell.PutValue(“Polo”);
cell = cells[“A6”];
cell.PutValue(“Golf”);
cell = cells[“A7”];
cell.PutValue(“Tennis”);
cell = cells[“A8”];
cell.PutValue(“Golf”);
cell = cells[“A9”];
cell.PutValue(“Polo”);
cell = cells[“A10”];
cell.PutValue(“Polo”);
cell = cells[“B2”];
cell.PutValue(“Qtr3”);
cell = cells[“B3”];
cell.PutValue(“Qtr4”);
cell = cells[“B4”];
cell.PutValue(“Qtr3”);
cell = cells[“B5”];
cell.PutValue(“Qtr3”);
cell = cells[“B6”];
cell.PutValue(“Qtr3”);
cell = cells[“B7”];
cell.PutValue(“Qtr4”);
cell = cells[“B8”];
cell.PutValue(“Qtr3”);
cell = cells[“B9”];
cell.PutValue(“Qtr4”);
cell = cells[“B10”];
cell.PutValue(“Qtr3”);
cell = cells[“C2”];
cell.PutValue(1500);
cell = cells[“C3”];
cell.PutValue(2000);
cell = cells[“C4”];
cell.PutValue(600);
cell = cells[“C5”];
cell.PutValue(1500);
cell = cells[“C6”];
cell.PutValue(4070);
cell = cells[“C7”];
cell.PutValue(5000);
cell = cells[“C8”];
cell.PutValue(6430);
cell = cells[“C9”];
cell.PutValue(12999);
cell = cells[“C10”];
cell.PutValue(8050);

sheet = workbook.Worksheets.Add(“pivot”);
var pivotTables = sheet.PivotTables;
//Adding a PivotTable to the worksheet
int index = pivotTables.Add("=Sheet1!A1:C10", “A1”, “PivotTable2”);
//Accessing the instance of the newly added PivotTable
var pivotTable = pivotTables[index];
//Showing grand totals for rows & columns
pivotTable.RowGrand = true;
pivotTable.ColumnGrand = true;

int rowFieldIndex = pivotTable.AddFieldToArea(PivotFieldType.Row, 0);
int colFieldIndex = pivotTable.AddFieldToArea(PivotFieldType.Column, 1);

PivotField cpf = pivotTable.ColumnFields[colFieldIndex];
int dataFieldIndex = pivotTable.AddFieldToArea(PivotFieldType.Data, 2);

PivotField pf = pivotTable.DataFields[dataFieldIndex];
pf.Function = ConsolidationFunction.Count;

PivotField rpf = pivotTable.RowFields[rowFieldIndex];

//Enable AutoSort
rpf.IsAutoSort = true;
//Sort Decending
rpf.IsAscendSort = false;
//Sort by Grand Total (Sum of Sales)
rpf.AutoSortField = 0;

pivotTable.IsAutoFormat = true;
workbook.Save(“D:/temp/output.xlsx”, SaveFormat.Xlsx);
In case you face any difficulty, please provide us the sample spreadsheet after processing the smart markers and refreshing the PivotTable with new data. We will further look into the matter to provide the exact code snippet according to PivotFields in the spreadsheet.
#3

HI Raza,


I have tried above mentioned code but its not working .please find the smart maker formula excel and generated excel(Based on formula) for your reference.


thanks in advance.
Ship
#4

Hi Ship,


Sorry for the confusion earlier. The current implementation of Aspose.Cells APIs do not support the sorting of row or column fields according to the data field. As your requirement is to sort the row field according to the data field (sum of jobs) so this cannot be achieved with current implementation. I have now logged a feature request in our database to provide the request feature with future releases of the Aspose.Cells for .NET API. The ticket Id for your future reference is CELLSNET-43148. Please spare us little time to properly analyze the request on our end then we can share the estimated release schedule for this feature.
#5

Hi raza,


Any updates CELLSNET-43148?

Thanks and Regards,
Ship
#6

Hi Ship,


Thank you for writing back.

I am afraid, we haven’t yet received any news regarding the feature request logged earlier as CELLSNET-43148 in our database. We have now recorded a note for the concerned development team member to share the estimated release schedule for this feature. As soon as we receive any news, we will post here for your kind reference.
#7

Hi Ship,


This is to update you regarding the ticket logged earlier as CELLSNET-43148 in order to provide the support for the sorting of Grand Total according to the DataFields. Unfortunately we cannot provide this feature in short period of time due to its complexity and dependencies. We will keep the aforesaid ticket open in our database so that your could be notified once the required feature is available for your testing. Moreover, as soon as we schedule this feature for publication, we will inform you here with estimated time of arrival.

Thank you for your understanding.
#8

@ShipNet,
Please try our latest version/fix: Aspose.Cells for .NET v21.6.3 (attached)
Aspose.Cells21.6.3 For .Net2_AuthenticodeSigned.Zip (5.5 MB)
Aspose.Cells21.6.3 For .Net4.0.Zip (5.5 MB)
Aspose.Cells21.6.3 For .NetStandard20.Zip (5.5 MB)

Your issue should be fixed in it.
Let us know your feedback.