How can I order a PivotTable's ranges by the value in a particular cell?

I need to order the ranges (each range covers four rows) by a value in one of that range's cells. This is how the first bunch of data looks (screenshot at bottom).

I need to order these by the value in the cell at the juxtoposition of "Sum of TotalPrice" and "Grand Total". So in the data shown, the order should be:

ASPARAGUS, STANDARD 11/1#
LETTUCE, SPRING MIX 3#
ASPARAGUS, LARGE 11/1#
LETTUCE, ROMAINE HEARTS 48 CT
CUCUMBERS, ENGLISH (SDLS) 12-14 CT
TOMATOES, 5x6 LOOSE X-LG 25#
CARROTS, BABY PEELED W/TOPS 5# (IMPORT)
TOMATOES, CHERRI 12/1 PT

How can I do that, with Aspose Cells?

In Excel Interop (which is untenable for some of these reports, as it will not even generate the larger ones, even after hours, whereas Aspose Cells can generate them in several minutes), the code is this:

// These two lines don't seem that they would do so, but they do result in the items
// being sorted by (grand) total purchases descending
var fld = ((PivotField)pvt.PivotFields("Description"));
fld.AutoSort(2, "Total Purchases");

As the comments indicate, *why* this works is a mystery to me, and surely the way to do this in Aspose Cells, and probably more grokkable, but just how is the question.

Hi,


Thanks for providing us some details and screenshot.

Could you provide us template Excel file (if any), output Excel file (containing Pivot Table) by Aspose.Cells APIs and an Excel file that you may manually create in Ms Excel that should contain the PivotTable with your desired order set for the underlying ranges. Please also provide steps details on how to accomplish the task (e.g., specify or set sort order for your desired ranges in PivotTable report) in MS Excel manually. Also, provide your current sample code (runnable) using Aspose.Cells APIs. All this will help us to evaluate your issue/requirements properly to consequently figure it out soon.

Thank you.
Thanks,

I am not using a template Excel file; I am attaching a copy of the report as it should be ordered (by Total Purchases > Grand Total). This file ("ABUELOS - Produce Usage - from Oct 2015_PROCESSED_1603.xlsx") was generated using Excel Interop.

I am also attaching the file as it is currently being created using Aspose Cells (AsposeTest32.xlsx). As you can see, this file is not being ordered at all.

Hi,


Well, you may try to use PivotTable.ShowInCompactForm method to accomplish your desired task for PivotTable’s layout. See the sample code segment below for your reference:
e.g
Sample code:

var book = new Workbook(“e:\test2\AsposeTest32.xlsx”);
var sheet = book.Worksheets[“PivotTableSheet”];
var pivot = sheet.PivotTables[0];

pivot.ShowInCompactForm();


Hope, this helps a bit.

Thank you.

Thanks, I’ll try it, but I don’t see how it’s going to know what to order by with that code.

I added that line of code:


pivotTable.ShowInCompactForm()

…and it completely jumbled up the report:

Hi,


Well, the suggested method would only set the Pivot Table’s layout in compact form (this is somehow what your output (by Interop. APIs) exhibits a bit). Regarding your custom needs (for sorting specific data ranges), we appreciate if you could accomplish your desired task manually in MS Excel (via PivotTable options) upon the Aspose.Cells generated PivotTable in the file. Re-save the file and provide us here with all the details and steps involved (you performed) using PivotTable’s options in MS Excel, you may attach some screenshots to demonstrate the steps and options you have used/chosen in MS Excel. All this will help us to evaluate your requirements precisely to figure it out soon.

Thank you.

I did not do it in Excel manually, nor do I know how that would be done; as I stated in the original question, it is accomplished in Excel Interop as follows, but I don’t know what the equivalent in Aspose.Cells would be:


In Excel Interop (which is untenable for some of these reports, as it will not even generate the larger ones, even after hours, whereas Aspose Cells can generate them in several minutes), the code is this:

// These two lines don’t seem that they would do so, but they do result in the items
// being sorted by (grand) total purchases descending
var fld = ((PivotField)pvt.PivotFields(“Description”));
fld.AutoSort(2, “Total Purchases”);

As the comments indicate, why this works is a mystery to me, and surely the way to do this in Aspose Cells, and probably more grokkable, but just how is the question.

That (Excel Interop code) works, as can be seen below.

Hi Clay,


As per my understanding of this scenario, you probably wish to sort the PivotField as per another PivotField in the PivotTable.DataFields. Please try the following piece of code which adds a new Pivot Table to your existing sample with required sorting. Please confirm if this is what you wish to achieve.

C#

Workbook wb = new Workbook(dir + “ABUELOS±+Produce+Usage±+from+Oct+2015_PROCESSED_1603.xlsx”);
int sheetIndex = wb.Worksheets.Add();
PivotTableCollection pts = wb.Worksheets[sheetIndex].PivotTables;
int ptIndex = pts.Add("=PivotData!$A$1:$J$415", “A5”, “testPt”);
PivotTable pt = pts[ptIndex];
pt.AddFieldToArea(PivotFieldType.Row, “Description”);
pt.AddFieldToArea(PivotFieldType.Column, “MonthYr”);
pt.AddFieldToArea(PivotFieldType.Data, “TotalQty”);
pt.DataFields[0].DisplayName = “Total Packages”;
pt.AddFieldToArea(PivotFieldType.Data, “TotalPrice”);
pt.DataFields[1].DisplayName = “Total Purchases”;
pt.AddCalculatedField(“Average Price”, “=TotalPrice/TotalQty”, true);
pt.AddCalculatedField(“PercentOfTotal”, “=TotalPrice”, true);
pt.DataFields[3].DisplayName = “Percentage of Total”;

pt.RowFields[0].ShowInOutlineForm = true;
pt.RowFields[0].ShowCompact = true;

PivotField field = pt.RowFields[0];
field.IsAutoSort = true;
field.IsAscendSort = false;
field.AutoSortField = 1; // 0 based indexed position in the PivotTable.DataFields


pt.PivotTableStyleType = PivotTableStyleType.PivotTableStyleLight16;

pt.RefreshDataFlag = true;
pt.RefreshData();
pt.CalculateData();
pt.RefreshDataFlag = false;
wb.Save(dir + “output.xlsx”);
I tried the following code:

pivotTable.DataFields[0].DisplayName = "Total Packages";
pivotTable.DataFields[1].DisplayName = "Total Purchases";
pivotTable.DataFields[2].DisplayName = "Avg Purchase";
pivotTable.DataFields[3].DisplayName = "% of Total";

pivotTable.RowFields[0].ShowInOutlineForm = true;
pivotTable.RowFields[0].ShowCompact = true;

PivotField field = pivotTable.RowFields[1];
field.IsAutoSort = true;
field.IsAscendSort = false;
field.AutoSortField = 1; // 0 based indexed position in the PivotTable.DataFields

pivotTable.PivotTableStyleType = PivotTableStyleType.PivotTableStyleLight16;

pivotTable.RefreshDataFlag = true;
pivotTable.RefreshData();
pivotTable.CalculateData();
pivotTable.RefreshDataFlag = false;

...but it did not work for me; it failed with, "You can't operate the field" on this line:

field.IsAscendSort = false;

Hi Clay,

Thank you for writing back.

Please note, I have been using latest version of Aspose.Cells for .NET 16.11.3 (attached) therefore please give it a try on your side as well. In case the problem persists, please provide an executable sample console application along with its all dependencies (assemblies, input spreadsheets etc.). Please note, I have tried the code on your both shared samples and I am not able to observe the said problem.

Okay; I’m using 16.11.0.0; maybe something changed between my version and yours…

Hi Clay,


It is advised to always use the latest revision of the API. Please note, only major releases are posted to the Aspose download section whereas the minor releases such as 16.11.3 are shared in the forums only.

Please confirm if you are able to bypass the aforementioned issue by upgrading to 16.11.3. Otherwise, we require an executable sample application as well as its dependencies to isolate the problem cause.

I am using the latest version available via the NuGet Package Manager in Visual Studio.

Hi Clay,

Let me provide you some more information about how we publish releases of Aspose.Cells APIs. Please note, we publish the major releases once every month during the 2nd week. These major releases are available through NuGet as well as Aspose download section. On top of it, we publish 1 minor release once every week whereas the download link/release archive is shared in the forums on concerned threads only. Such minor releases can be used in production environment without any problems because they contain all the fixes and enhancements from their predecessors.

That said, as you have acquired the release from the NuGet, it should be 16.11.0. You can keep using this release in case you are not facing any problem. In case of any issue, we suggest you to use the most recent revision of the API. If you are not certain which revision is most recent, you can always post your concerns in support forums, we will test the case against the most recent release and share it with you in case the problem has already been resolved. If the problem persists, we will log it in our bug tracking system and share a tracking code for future reference as well as automated notifications regarding the particular problem.

Thanks; this particular issue has been solved (by you, I think), so versioning is not an issue with me at present. Thanks for the info!

Hi,


Thanks for your feedback and using Aspose.Cells.

We have now closed this thread based on your feedback. You may open it whenever you like.