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

Free Support Forum - aspose.com

Pivot table with report filter

Hello,


We are using Aspose Cells 7.7.1.0.
I would like to create a pivot table with report filter, like the example in Excel (see attachment).

To achieve this, I’ve used the code below:
Worksheet pivotSheet = workbook.Worksheets[pivotSheet1];
//Getting the pivottables collection in the sheet
Aspose.Cells.Pivot.PivotTableCollection pivotTables = pivotSheet.PivotTables;
//Adding a PivotTable to the worksheet
int pivotIdx = pivotTables.Add(string.Format("={0}!A1:AY100", dataSheet), “A3”, “PivotTable1”);
//Accessing the instance of the newly added PivotTable
Aspose.Cells.Pivot.PivotTable pivotTable = pivotTables[pivotIdx];
//Showing the grand totals
pivotTable.RowGrand = true;
pivotTable.ColumnGrand = true;
//Setting the PivotTable report is automatically formatted
pivotTable.IsAutoFormat = true;
//Setting the PivotTable autoformat type.
pivotTable.AutoFormatType = Aspose.Cells.Pivot.PivotTableAutoFormatType.Report9;
//Columns
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Column, 4); //TYPE ONGEVAL
pivotTable.ColumnFields[0].IsAutoSort = pivotTable.ColumnFields[0].IsAscendSort = true;
//Page / Report filter
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Page, 6); //INTERN/EXTERN
//Data
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 2); //NAAM/VOORNAAM
//Rows
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 12); //BU
pivotTable.RowFields[0].IsAutoSort = pivotTable.RowFields[0].IsAscendSort = true;
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 14); //TEAM
pivotTable.RowFields[1].IsAutoSort = pivotTable.RowFields[1].IsAscendSort = true;

The line with ‘PivotFieldType.Page’ has no effect. How can I add one or more report filters?

I’ve read about PivotFilters, but I can’t find an example, with an explanation of what it actually does.

Please provide some assistance.

Regards,
Wim.

Hi,


Please try our latest version/fix: Aspose.Cells for .NET v8.2.0.2.
I used the following sample code using it, it works fine, it adds filters to Page pivot field fine. I have also attached the output file for your reference.
e.g
Sample code:

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


sheet.Cells[“F1”].PutValue(“Store”);
sheet.Cells[“F2”].PutValue(“bStore”);
sheet.Cells[“F3”].PutValue(“AStore”);
sheet.Cells[“F4”].PutValue(“bStore”);
sheet.Cells[“F5”].PutValue(“AStore”);
sheet.Cells[“F6”].PutValue(“AStore”);


sheet.Cells[“G1”].PutValue(“GAme”);
sheet.Cells[“G2”].PutValue(“Golf”);
sheet.Cells[“G3”].PutValue(“Tennis”);
sheet.Cells[“G4”].PutValue(“Golf”);
sheet.Cells[“G5”].PutValue(“Tennis”);
sheet.Cells[“G6”].PutValue(“Football”);

sheet.Cells[“H1”].PutValue(“Day”);
sheet.Cells[“H2”].PutValue(“Day1”);
sheet.Cells[“H3”].PutValue(“DAy2”);
sheet.Cells[“H4”].PutValue(“day3”);
sheet.Cells[“H5”].PutValue(“day4”);
sheet.Cells[“H6”].PutValue(“day4”);

sheet.Cells[“I1”].PutValue(“Sales”);
sheet.Cells[“I2”].PutValue(5);
sheet.Cells[“I3”].PutValue(20);
sheet.Cells[“I4”].PutValue(10);
sheet.Cells[“I5”].PutValue(5);
sheet.Cells[“I6”].PutValue(15);

PivotTableCollection pcs = sheet.PivotTables;

int idx = pcs.Add("=F1:I6", “J10”, “Ptable1”);

PivotTable pt = pcs[idx];

pt.AddFieldToArea(PivotFieldType.Row, 2);
pt.AddFieldToArea(PivotFieldType.Column, 1);
pt.AddFieldToArea(PivotFieldType.Data, 3);
pt.AddFieldToArea(PivotFieldType.Page, 0);

pt.PageFields[0].IsMultipleItemSelectionAllowed = true;
pt.RowGrand = true;

workbook.Save(“e:\test2\outpivottablewithreportfilter1.xlsx”);


If you still have any issue, kindly create a sample console application (runnable), zip it and post us here to reproduce the issue on our end. Also attach the template and output Excel files here. Moreover, attach an Excel file that you may create in MS Excel manually which should contain your desired pivot table with filters etc., we will check it soon.

Thank you.