Pivot Table Column Grouping

Aspose.Cells Product Family
1

Hi, I have found older posts regarding the ability to have a Pivot Table have grouping applied to columns (specifically a date column in my case). All of the older posts reference CELLSNET-42655.

(example post: Pivot Table grouping by year and month)

I can’t figure out how to find a status update on CELLSNET-42655, is there a particular place I can search for this item to see if it was included in a recent release or if it’s still open?

I’m so close to using Aspose Cells Pivot Table feature but can’t roll it into production because the pivot table doesn’t group on the date field which i’m using for columns in the pivot table.

Thank you-

2

@sdiamond,

Thanks for your query.

I am afraid, your requested feature (Grouping of PivotFields) is still not available. Actually your requested feature is complex and demands lots of work and effort to implement it, we might have to re-model the core to incorporate the necessary changes. I have asked our product team to share an update on it or provide an ETA if possible. Moreover, the ticket CELLSNET-42655 is also attached to this thread for automated notifications. As soon we receive any updates in this regard, we will post here for your kind reference.

We are sorry for any inconvenience caused.

3

Hi Amjad,
Ok, good to have the update on this topic.
Thank you,
Sean

4

@sdiamond

Thanks for using Aspose APIs.

Please download and try the following fix and let us know your feedback.

About CELLSNET-42655, please try the following sample code with the provided fix.

C#

string filePath = Constants.PIVOT_PATH2 + @"NET42655\";

Workbook wb = new Workbook(filePath + "pastry2_desired.xlsx");

Worksheet pivotSheet = wb.Worksheets.Add("GroupPivotTable");

int pivotIndex = pivotSheet.PivotTables.Add("=PastrySalesData!A1:D16", "A3", "groupPivot");

PivotTable pivot = pivotSheet.PivotTables[pivotIndex];
pivot.AddFieldToArea(PivotFieldType.Page, "Testing");
pivot.AddFieldToArea(PivotFieldType.Row, "Name");
pivot.AddFieldToArea(PivotFieldType.Column, "Date");
pivot.AddFieldToArea(PivotFieldType.Data, "NumberOfOrders");

pivot.DataFields[0].Function = ConsolidationFunction.Sum;

PivotField dateBaseField = pivot.BaseFields["Date"];

DateTime start = new DateTime(2013,1,1);
DateTime end = new DateTime(2013,12,31);

ArrayList groupTypeList = new ArrayList();
groupTypeList.Add(PivotGroupByType.Months);
groupTypeList.Add(PivotGroupByType.Years);

pivot.SetManualGroupField(dateBaseField, start, end, groupTypeList, 1);

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

wb.Save(filePath + "out.xlsx");
5

Hi Shakeel,
I downloaded 18.2.8 and was able to confirm your code snippet above works great!
Thank you for following up with a fix, we appreciate it!
Sean

6

@sdiamond

Thanks for your feedback and sharing good news with us.

It is good to know that your issue is now sorted out. Let us know if you encounter any other issue, we will be glad to look into it and help you further.

7

The issues you have found earlier (filed as CELLSNET-42655) have been fixed in this update. This message was posted using BugNotificationTool from Downloads module by Amjad_Sahi

8

The issues you have found earlier (filed as CELLSNET-42655) have been fixed in this update. Please also check the following article:* <a href="Install Aspose Cells through NuGet|Documentation