Free Support Forum - aspose.com

Move Pivot Field

I’m trying to move a RowField from RowFields to Filters (Page). Have invested already some hours debugging it but seems to just not work . Segment of the code for this functionality as follows:
indPivTab = pivotTables.Add(sourceDataPT, “A1”, “PivotTbl”);
PivotTable pivotTable = pivotTables[indPivTab];
pivotTable.AddFieldToArea(PivotFieldType.Data, 2);
pivotTable.AddFieldToArea(PivotFieldType.Row, 7);
pivotTable.AddFieldToArea(PivotFieldType.Row, 14);
pivotTable.AddFieldToArea(PivotFieldType.Column, 12);
pivotTable.AddFieldToArea(PivotFieldType.Row, 1);

 // Create group field from baseFieldIndex 1

PivotField dateBaseField = pivotTable.BaseFields[“AccDate”];
DateTime start = new DateTime(2020, 1, 1);
DateTime end = new DateTime(2020, 12, 31);
System.Collections.ArrayList groupTypeList = new System.Collections.ArrayList();
groupTypeList.Add(PivotGroupByType.Months);
groupTypeList.Add(PivotGroupByType.Years);
pivotTable.SetManualGroupField(dateBaseField, start, end, groupTypeList, 1);

   // WORK OUT HERE to MOVE YEARS to THE FILTERS (PAGE) . I've tried this 3 approaches:
  1.      //pivotTable.AddFieldToArea(PivotFieldType.Page, pivotTable.RowFields[0]);
   2.     ////PivotField yearsField = pivotTable.RowFields[0];
        ////pivotTable.AddFieldToArea(PivotFieldType.Page, yearsField);
   3.    //pivotTable.AddFieldToArea(PivotFieldType.Page, "Years");

 // Refresh and claculate data

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

If further details (a sample console and file) are needed please let me know.

@Remus87,

Please create a standalone console demo application (without compile time errors) and post us which we could use to evaluate and execute to reproduce the issue on our end. We will check your issue soon. Also, provide your input Excel file (if any), current output Excel file by Aspose.Cells APIs and your expected output file (containing your desired pivot table in tact). This will help us really to evaluate your issue precisely to consequently figure it out soon.

PS. please zip the Excel files and application prior attaching.

I attached a console app below together with the required files included. The input file is a: tabCF.csv and the output Excel file by Aspose Cells API is: CFPiv.xlsx
Also i attached a screenshot FinalResult.png of what is expected to achieve (moved Years on Filters) and also want to hide the Pivot Field AccDate after moving Years.SampleProject.zip (157.2 KB)

@Remus87,

I logged a ticket with an id “CELLSNET-47987” for your issue/requirements. We will check on how to move certain pivot field to Page section or pivot filters for your needs, we might update your provided code segment.

Once we have an update on it, we will let you know.

ok. Thanks for the update Amjad.

@Remus87,

You are welcome.

Hi !
Any updates on the ticket: CELLSNET-47987 ?
Thanks in advance.

@Remus87,
We are gathering information about this issue and will share our feedback soon.

@Remus87,

Please try our latest fix v21.4.2:

Aspose.Cells21.4.2 For .Net2_AuthenticodeSigned.Zip (5.5 MB)
Aspose.Cells21.4.2 For .Net4.0.Zip (5.5 MB)
Aspose.Cells21.4.2 For .NetStandard20.Zip (5.5 MB)

Please add the following code to move “Years” to the FILTERS(page field area) and remove “AccDate” from ROWS(row field area).

pivotTable.AddFieldToArea(PivotFieldType.Page, pivotTable.RowFields[0]);
pivotTable.RemoveField(PivotFieldType.Row, “AccDate”);

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

Thanks for the fix. It’s working great.
Keep up the great work!

@Remus87,

Good to know that your issue is sorted out by the fix and suggested lines of code. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

Hi!
Referring to the same sample console app the Years on filter (PageField) display fine and the AccDate is removed wihich is exactly what i needed (the new asposeCells version and the 2 lines of code supplied by John He)
However now i just wanted to add a Slicer from the same field “Years” and it doesn’t generate it. It adds slicer for all the other 4 PivotFields (CF, CfGroup, SexTrans, SevTrans) but not for “Years” . So m guess is that is a matter of refreshing data / maybe add a property somewhere near those 2 lines of code provided.
Again is he exact same console app -> just added the latest dll, the code provided and those below lines just before saving the workbook:
int indSlicer = wsSheet1.Slicers.Add(pivotTable, “H2”, pivotTable.BaseFields[“Years”]);
Slicer slicer = wsSheet1.Slicers[indSlicer];

@Remus87,

Do you get any error using the above line?
Could you try to refresh the Pivot Table using the following lines of code just before you add slicers:
e.g.
Sample code:

.....
pivotTable.RefreshData();
pivotTable.CalculateData();
......

If you still find the issue kindly provide your complete (updated) sample code, current output file and your expected output file, we will check it soon.

No errors displayed using the lines mentioned. I tried RefreshData() and CalculateData() just before adding slicer and still doesn’t create it.
Below i attach the updated sample code with current output file (CFPiv.xlsx) and a picture of the expected output file (FinalResult.png just near the solution file -> i just Inserted manually in Excel the slicer from BaseField “Years”)SampleProject.zip (147.2 KB)

@Remus87,

Please notice, I am able to reproduce the issue as you mentioned by using your template file(s) and updated sample code. I found slicer control is not added based on Page Filter field of Pivot Table. I have logged a separate ticket with an id “CELLSNET-48036” for your issue. We will look into it soon.

Once we have an update on it, we will let you know.

Just wondering if there is a workaround to achieve this task (Create Slicer from “Years” field) on the present Aspose dll (v21.4.2.0) as ideally is not to update the dll (because of the time consuming CI/CD on the pipeline integrating the new dll) that often (just updated 1 week ago)

@Remus87,

We are working over your issue (logged earlier as “CELLSNET-48036”) and hopefully we could update you soon on it. I am afraid, there is no workaround to cope with it at the moment. However, we will still check if there is any, so we could share it with you.

ok Amjad. Thanks for the update and effort.

@Remus87,

You are welcome.

@Remus87,

Please try our latest version/fix: Aspose.Cells for .NET v21.4.6 (attached)

Aspose.Cells21.4.6 For .Net2_AuthenticodeSigned.Zip (5.5 MB)
Aspose.Cells21.4.6 For .Net4.0.Zip (5.5 MB)
Aspose.Cells21.4.6 For .NetStandard20.Zip (5.5 MB)

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