How to add a Pivot Filter to PivotTable and do operations on that like Equals- like- greater than etc

Aspose.Cells Product Family
1

Hi,
Shakeel…

I saw some posts on Pivot Filter but didn’t get much info. I have done
lot of work with this Aspose.Celss.dll…

But ont thing I didn’t get is:

  1. How to add a Pivot Filter to PivotTable? - Can you provide some
    sample code - I am trying to create but no luck.
  2. After adding PivotFilter to Pivot Table…how can we do operations on
    that like Equals, like, greater than…

I have attached the excel file what I have created through this
wonderful dll and also in the same excel i highlghted how the Filter
should be…

Can you please reply back to me when you hae some time, need to show
this demo in office by next wee…

Thanks for your time…

Regards,
Asrar Khan

This message was posted using Email2Forum by Shakeel Faiz.

2

Hi,

Thanks for your posting and using Aspose.Cells.

Please see the following sample code and the sample excel file used in this code as well as the output excel file generated by the code for your reference. The code creates the pivot table as you have shown in your sample excel file. I have run this code on the latest version:
Aspose.Cells for .NET (Latest Version) .

C#

Workbook wb = new Workbook(“sample.xlsx”);

Worksheet ws = wb.Worksheets[0];

int idx = ws.PivotTables.Add(“Incidents!A1:D22”, “G13”, “TestPivot”);
PivotTable p = ws.PivotTables[idx];

PivotField pf = p.BaseFields[“Assignee”];
p.PageFields.Add(pf);

pf = p.BaseFields[“priority”];
p.RowFields.Add(pf);

pf = p.BaseFields[“groupName”];
p.RowFields.Add(pf);

pf = p.BaseFields[“Incident”];
p.DataFields.Add(pf);
p.DataFields[0].Function = ConsolidationFunction.Count;

p.ShowInCompactForm();

p.RefreshData();
p.CalculateData();

p.PivotTableStyleType = PivotTableStyleType.PivotTableStyleLight7;

wb.Save(“output.xlsx”);
3

Hi Asrar Khan,


Also, please see the code segment (in bold) on how to apply/set Pivot filters to PivotTable for your reference, I have added some lines of code to the code segment shared by Shakeel Faiz using his template file to accomplish the task:
e.g
Sample code:

Workbook wb = new Workbook(“e:\test2\sample.xlsx”);

Worksheet ws = wb.Worksheets[0];

int idx = ws.PivotTables.Add(“Incidents!A1:D22”, “G13”, “TestPivot”);
PivotTable p = ws.PivotTables[idx];

PivotField pf = p.BaseFields[“Assignee”];
p.PageFields.Add(pf);

pf = p.BaseFields[“priority”];
p.RowFields.Add(pf);

pf = p.BaseFields[“groupName”];
p.RowFields.Add(pf);

pf = p.BaseFields[“Incident”];
p.DataFields.Add(pf);
p.DataFields[0].Function = ConsolidationFunction.Count;

p.ShowInCompactForm();

PivotField pageField = p.PageFields[0];
int pageItemCount = pageField.PivotItems.Count;
for (int i = 0; i < pageItemCount; i++)
{
if (“Asrar Khan”.Equals(pageField.PivotItems[i].Value))
{
pageField.CurrentPageItem = (short)i;
}
}

p.RefreshData();
p.CalculateData();

p.PivotTableStyleType = PivotTableStyleType.PivotTableStyleLight7;

wb.Save(“e:\test2\output1.xlsx”);

Hope, this helps a bit.

Thank you.
4

Hi Shakeel/Amjad,

Thanks for replying back to me… :slight_smile:

I have implemented the code what you provided and it was successfully executed, it reduces so much lines of code when compared to the code what I wrote… For the code what i wrote previously i was able to get the Count (DataField on “Incident” column in descending order).

But now for the code what you hae provided i tried to sort the DataFiled “Incident” in descending order, but unable to get that.

Tried below piece of code but no luck, looks like i didn’t understood the elements of PivotTable correctly and applying it wrongly.

//Setting the field auto show.
pivotTable.DataFields[0].IsAutoShow = true;
//Setting the field auto show ascend.
pivotTable.DataFields[0].IsAscendShow = false;
//Setting the auto show using field(data field).
pivotTable.DataFields[0].AutoShowField = -1;

Tried AutoSort,IsAscendSort but no luck…? not sure where I am missing.

Can you help me how to Sort the DataField “Incident” which we applied Count function and also is there any way how can we with filter multiple names? In the above code filter was applied on one name “Asrar Khan”, i want to filter with two or more names… can you also share me that - do we need to add any array for that logic…

Thanks for your time…

By the way i have attached the excel file again(asrar.xslx), in case if we missed out the file.

Regards,
Asrar Khan

5

Hi,

Thanks for your posting and using Aspose.Cells.

For descending and sorting issue, we are looking into it and update you asap.

For filtering multiple names issue, please see the following sample code and its comment. I have also attached the sample excel file used in this code and output excel file generated by the code for your reference.

C#

Workbook wb = new Workbook(“sample.xlsx”);

Worksheet ws = wb.Worksheets[0];

int idx = ws.PivotTables.Add(“Incidents!A1:D22”, “G13”, “TestPivot”);
PivotTable p = ws.PivotTables[idx];

PivotField pf = p.BaseFields[“Assignee”];
p.PageFields.Add(pf);

pf = p.BaseFields[“priority”];
p.RowFields.Add(pf);

pf = p.BaseFields[“groupName”];
p.RowFields.Add(pf);

pf = p.BaseFields[“Incident”];
p.DataFields.Add(pf);
p.DataFields[0].Function = ConsolidationFunction.Count;

p.ShowInCompactForm();

PivotField pageField = p.PageFields[0];
pageField.IsMultipleItemSelectionAllowed = true;

int pageItemCount = pageField.PivotItems.Count;

//Select all except Vinod and Gopal
for (int i = 0; i < pageItemCount; i++)
{
PivotItem item = pageField.PivotItems[i];

if(item.Name.Contains(“Vinod”)==true)
{
item.IsHidden = true;
}

if (item.Name.Contains(“Gopal”) == true)
{
item.IsHidden = true;
}

}

p.RefreshData();
p.CalculateData();

p.PivotTableStyleType = PivotTableStyleType.PivotTableStyleLight7;

wb.Save(“output.xlsx”);

6

Ok, Thanks. Mean while i will check on the properties of excel file and debug to see properties of Aspose cells to resolve the sorting issue.

7

Hi,

Thanks for your posting and using Aspose.Cells.

You can sort column and row fields. But you cannot sort data fields currently.

We have already logged this issue in our database for implementation. Once there is some fix available for you, we will let you know asap.

This issue has been logged as

  • CELLSNET-43257 - Data Sorting for Data field with respect to Row field in PivotTable

Please check this thread for a reference.

( Pivot Table Sort Exception )

8

Thanks for the help and support.

9

@Asrar409025,
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.