How do I add Report Filter in pivot

Technical support for Spire.XLS

Moderators: iceblue support, Flash, Manager

How do I add Report Filter in pivot

Postby ctseng123 » Tue Jul 12, 2011 10:47 pm

Hi,
I want to know in the pivot table, how do I add Report Filter not just column or row?


Thanks!
ctseng123
 
Posts: 12
Joined: Sun May 22, 2011 10:25 pm

Re: How do I add Report Filter in pivot

Postby Justin Weng » Wed Jul 13, 2011 1:56 am

Dear ctseng123,
Thanks for your inquiry.
We'll add this feature at next version.
Please wait patiently.
Once added we'll inform you.
Justin
Technical Support / Developer,
e-iceblue Support Team
User avatar
Justin Weng
 
Posts: 110
Joined: Mon Mar 28, 2011 5:54 am

Re: How do I add Report Filter in pivot

Postby abeverly@precastcorp.com » Mon Apr 08, 2013 8:58 pm

Is this feature available yet? If so, is there documentation on how to use it?
abeverly@precastcorp.com
 
Posts: 1
Joined: Tue Apr 02, 2013 5:09 pm

Re: How do I add Report Filter in pivot

Postby amy.zhao » Tue Apr 09, 2013 9:18 am

Dear Abeverly,

Thanks for your inquiry.
Our dev team are working on the feature. Once the feature has finished, we will release a new version and inform you.
Have any issues, please feel free to contact us.

Regards,
Amy
E-iceblue support team
User avatar
amy.zhao
 
Posts: 2251
Joined: Wed Jun 27, 2012 8:50 am

Re: How do I add Report Filter in pivot

Postby Gary.zhang » Thu Aug 29, 2013 7:58 am

Hello,

Now our Spire.Xls could support the feature to add Report Filter in pivot, and the newest hot_fix has been released, please download the Spire.XLS Pack Hotfix Version:7.1.6 and try the following method.
http://www.e-iceblue.com/Download/download-excel-for-net-now.html
Code: Select all
pivotTable.ReportFilters[1].IsMultipleSelect = false;
pivotTable.ReportFilters[1].FieldString = "FedEx";
pivotTable.ReportFilters[1].FilterItemStrings=new List<string>() { "114", "110" };


If there are any questions, welcome to get it back to us.

Thanks And Regards,
Gary
E-iceblue support team
User avatar
Gary.zhang
 
Posts: 1390
Joined: Thu Apr 04, 2013 1:30 am

Re: How do I add Report Filter in pivot

Postby Gary.zhang » Wed Sep 04, 2013 9:36 am

Hello,

Have you tested the new feature? Does it fulfills your needs? Could you please give us some feedback if convenience?

If there are any questions, welcome to get it back to us.

Thanks,
Gary
E-iceblue support team
User avatar
Gary.zhang
 
Posts: 1390
Joined: Thu Apr 04, 2013 1:30 am

Re: How do I add Report Filter in pivot

Postby jouji_s » Mon Jun 09, 2014 12:10 pm

Thanks Gary,

But it doesn't work.
it was displayed following message when I open the excel.
"Excel found unreadable content in file name."

Please let me know the solution.
Code: Select all
        Dim sheet1 As Worksheet = workbook.Worksheets(0)
        sheet1.Name = "Test"
        Dim sheet2 As Worksheet = workbook.Worksheets(2)
        sheet2.Name = "Data Source"

        Dim dataRange As CellRange = sheet2.Range("A1:G10")
        Dim cache As PivotCache = workbook.PivotCaches.Add(dataRange)
        Dim pt As PivotTable = sheet1.PivotTables.Add("Item Analysis", sheet2.Range("A1"), cache)
        Dim filter As Spire.Xls.Core.Spreadsheet.PivotTables.PivotReportFilter = New Spire.Xls.Core.Spreadsheet.PivotTables.PivotReportFilter("Item#", True)
        Dim ra1 = pt.PivotFields("Category")
        Dim ra2 = pt.PivotFields("Destination")
        ra1.Axis = AxisTypes.Column
        ra2.Axis = AxisTypes.Row
        pt.ReportFilters.Add(filter)
        pt.DataFields.Add(pt.PivotFields("Quantity"), "Quantity", SubtotalTypes.Sum)

        workbook.SaveToFile("test.xlsx", ExcelVersion.Version2007)

jouji_s
 
Posts: 6
Joined: Fri Mar 07, 2014 6:22 pm

Re: How do I add Report Filter in pivot

Postby Gary.zhang » Tue Jun 10, 2014 3:02 am

Hello,

Thanks for your feedback. Please provide us your test excel document for testing.

Thanks,
Gary
E-iceblue support team
User avatar
Gary.zhang
 
Posts: 1390
Joined: Thu Apr 04, 2013 1:30 am

Re: How do I add Report Filter in pivot

Postby vikaash » Thu Jun 26, 2014 9:43 pm

hi, I am also facing the same issue. I have attached my test excel here.

for Report filter i used below code, but it gives me error on line workbook.SaveToFile(PathToFile, ExcelVersion.Version2007);
:
System.Collections.Generic.KeyNotFoundException: The given key was not present in the dictionary


Spire.Xls.Core.Spreadsheet.PivotTables.PivotReportFilter filter = new Spire.Xls.Core.Spreadsheet.PivotTables.PivotReportFilter("PRNT_PROD_CD", true);
pt.ReportFilters.Add(filter);
pt.ReportFilters[0].IsMultipleSelect = true;
pt.ReportFilters[0].FieldString = "DENTAL";
pt.ReportFilters[0].FilterItemStrings = new List<string>() { "DENTAL", "VISION" };

Can you please help me with the sample code for below two issues :

1) Add a Report filter for field "PRNT_PROD_CD"
2) Add a filter on column "FCTR_SYN_TXT".
vikaash
 
Posts: 1
Joined: Thu Jun 26, 2014 7:59 pm

Re: How do I add Report Filter in pivot

Postby Gary.zhang » Fri Jun 27, 2014 7:42 am

Hello,

Thanks for your feedback.

1) Add a Report filter for field "PRNT_PROD_CD"

You only need to use the following codes.

Code: Select all
Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotTable pivotTable = sheet.PivotTables[0] as Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotTable;

Spire.Xls.Core.Spreadsheet.PivotTables.PivotReportFilter filter = new Spire.Xls.Core.Spreadsheet.PivotTables.PivotReportFilter("PRNT_PROD_CD",true);

pivotTable.ReportFilters.Add(filter);

2) Add a filter on column "FCTR_SYN_TXT".

Sorry that currently our product doesn't support the feature to add a filter. Add we have added it to our schedule

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.
Sincerely,
Gary
E-iceblue support team
User avatar
Gary.zhang
 
Posts: 1390
Joined: Thu Apr 04, 2013 1:30 am


Return to Spire.XLS

Who is online

Users browsing this forum: No registered users and 1 guest

cron