Spire.Doc is a professional Word .NET library specifically designed for developers to create, read, write, convert and print Word document files. Get free and professional technical support for Spire.Doc for .NET, Java, Android, C++, Python.

Mon Jun 02, 2014 6:47 pm

Please let me kwow how to add report filter.
How to use "pivotTable.ReportFilters"?

Thanks

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

Tue Jun 03, 2014 8:28 am

Hello,

Thanks for your inquiry.
Sorry that at present our Product doesn't support the feature. And We have added it to our schedule. Once it is added, we will let you know ASAP.
If there are any questions, welcome to get it back to us.
Thanks,
Gary
E-iceblue support team
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Tue Jun 03, 2014 2:52 pm

Hello,

I found following post.
e-iceblue.com/forum/how-do-i-add-report-filter-in-pivot-t2432.html

I think you have already added this function.
Please let me know hot to use.

Thanks.

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

Wed Jun 04, 2014 2:10 am

Hello,

Thanks for your response, although the function has been added, after testing again there are some problems exist in the function such as the add method, so at present it still doesn't work. Our Dev team will fix it further. Sorry for inconvenience.
If there are any questions, welcome to get it back to us.
Sincerely,
Gary
E-iceblue support team
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Mon Jun 09, 2014 8:59 am

Hello,

The issue has been resolved, and the newest hotfix has been released, you can download the Spire.XLS Pack Hotfix Version:7.4.13 and refer the following method.
Code: Select all
Workbook workbook = new Workbook();
workbook.LoadFromFile("sample.xlsx");
Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotTable pivotTable = workbook.Worksheets["Pivot Table"].PivotTables[0] as Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotTable;
PivotReportFilter filter = new PivotReportFilter("Name", true);
pivotTable.ReportFilters.Add(filter);
workbook.SaveToFile("result.xlsx", ExcelVersion.Version2007);

If there are any questions, welcome to get it back to us.
Sincerely,
Gary
E-iceblue support team
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Wed Mar 25, 2020 3:10 pm

Hello.

How can i select a default value in report filter? This is my code:

PivotReportFilter filter = new PivotReportFilter("GRUPO", true);
pt.ReportFilters.Add(filter);
sheet2.Range["B1"].Text = "[VALUE_I_WANT]";

Thanks,

David

dmaciasaviles
 
Posts: 6
Joined: Wed Oct 09, 2019 11:56 am

Thu Mar 26, 2020 6:12 am

Hi David,

Thanks for your inquiry.
Please refer to the following code to select the default value in report filter, and attached are my input file and output file. If there is any question, please provide your input file as well as your desired output for further investigation.
Code: Select all
        Workbook workbook = new Workbook();
        workbook.LoadFromFile("CreatePivotTable.xlsx");
        Worksheet sheet = workbook.Worksheets[0];
        Worksheet sheet2 = workbook.CreateEmptySheet();
        sheet2.Name = "Pivot";

        CellRange dataRange = sheet.Range["A1:C17"];
        PivotCache cache = workbook.PivotCaches.Add(dataRange);
        PivotTable pt = sheet2.PivotTables.Add("Pivot Table", sheet.Range["A1"], cache);
        pt.EnableDrilldown = true;

        var r1 = pt.PivotFields["Product"];
        r1.Axis = AxisTypes.Row;

        var r2 = pt.PivotFields["Month"];
        r2.Axis = AxisTypes.Row;

        pt.DataFields.Add(pt.PivotFields["Count"], "Sum of Count", SubtotalTypes.Sum);

        //**********************************************
        //select the default value in report filter
        PivotReportFilter filter = new PivotReportFilter("Product", true);
        filter.IsMultipleSelect = true;
        List<string> list = new List<string>();
        list.Add("SpireDoc");
        filter.FilterItemStrings = list;
        pt.ReportFilters.Add(filter);
        //**********************************************

        pt.CalculateData();
        pt.Cache.IsRefreshOnLoad = true;
        workbook.SaveToFile("result.xlsx",FileFormat.Version2013);


Sincerely,
Rachel
E-iceblue support team
User avatar

rachel.lei
 
Posts: 1571
Joined: Tue Jul 09, 2019 2:22 am

Tue Jul 13, 2021 3:16 am

Hello Everyone,

How do you do this in Spire.XLS for Java?

I am currently using the codes below, but nothing happens after the Excel file has been generated.

Code: Select all
PivotReportFilter locationFilter = new PivotReportFilter("LOCATION", true);
locationFilter.isMultipleSelect(true);
pivotTable.getReportFilters().addItem(locationFilter);


Any help will be much appreciated.
Thanks.

Kheiko.G
 
Posts: 4
Joined: Tue Jul 13, 2021 3:00 am

Tue Jul 13, 2021 10:39 am

Hello,

Thanks for your inquiry.
Regarding adding the Report Filter in pivot, please refer to the following code.
Code: Select all
        Workbook workbook = new Workbook();
        workbook.loadFromFile("data/pivotTable.xlsx");
        Worksheet sheet = workbook.getWorksheets().get(0);
        Worksheet sheet2 = workbook.createEmptySheet();
        sheet2.setName("Pivot");

        CellRange dataRange = sheet.getCellRange("A1:C7");
        PivotCache cache = workbook.getPivotCaches().add(dataRange);
        PivotTable pivotTable = sheet2.getPivotTables().add("Pivot Table", sheet.getCellRange("A1"), cache);
        PivotReportFilter locationFilter = new PivotReportFilter("LOCATION", true);
        locationFilter.isMultipleSelect(true);
        pivotTable.getReportFilters().add(locationFilter);

If there are any issues, please provide your input file as well as your desired output to help us investigate further. Thanks in advance.

Sincerely,
Brian
E-iceblue support team
User avatar

Brian.Li
 
Posts: 1271
Joined: Mon Oct 19, 2020 3:04 am

Tue Jul 13, 2021 1:35 pm

Hello Brian,

Thank you for the quick response.

I tried making the changes using your provided reference code for adding report filter in pivot.
But the method add() is not available and I am getting the message below :
The method add(PivotReportFilter) is undefined for the type CollectionExtended


Here's the part of the code that creates the pivot table (still using the method addItem since the add is not available) :
Code: Select all
Worksheet pivotSheet = workbook.getWorksheets().get(1);
pivotSheet.setName("PIVOT");
           
CellRange dataRange = dataSheet.getCellRange("A1:G" + rsUtilDataColumnCount);
PivotCache cache = workbook.getPivotCaches().add(dataRange);
           
PivotTable pivotTable = pivotSheet.getPivotTables().add("Pivot Table", pivotSheet.getCellRange("A1"), cache);
           
PivotReportFilter prLocationFilter = new PivotReportFilter("LOC", true);
prLocationFilter.isMultipleSelect(true);
pivotTable.getReportFilters().addItem(prLocationFilter);
           
PivotField pfPlatformColumn = (PivotField) pivotTable.getPivotFields().get("PLATFORM");
pfPlatformColumn.setAxis(AxisTypes.Column);
           
PivotField pfStatusColumn = (PivotField) pivotTable.getPivotFields().get("STATUS");
pfStatusColumn.setAxis(AxisTypes.Column);
           
PivotTableFields ptFields = pivotTable.getPivotFields();
PivotDataFields ptDataFields = pivotTable.getDataFields();
IPivotField iField = null;
String iFieldName = "";
           
for(int i = 0; i < ptFields.getCount(); i++)
{
        iField = ptFields.get(i);
        iFieldName = iField.getName();
               
        if(iFieldName.contains("WW"))
        {
               ptDataFields.add(iField, iFieldName, SubtotalTypes.Average);
        }
}



I do not use an input/source file, but rather creates a new workbook that contains 3 worksheets.
Attached are the generated excel file and the desired output file.

Kheiko.G
 
Posts: 4
Joined: Tue Jul 13, 2021 3:00 am

Wed Jul 14, 2021 10:37 am

Hello,

Thanks for your sharing.
Regarding the problem that the method add() is undefined, you should be using an older version. Please download the latest Spire.XLS for Java Version:4.6.5 and test again.

Besides, I noticed that in your expect file, the "Values" field is in the row labels. I am sorry that our Spire.XLS does not yet support moving this field to row labels. If there is any other question, just feel free to contact us.

Sincerely,
Brian
E-iceblue support team
User avatar

Brian.Li
 
Posts: 1271
Joined: Mon Oct 19, 2020 3:04 am

Thu Jul 15, 2021 9:10 am

Hello Brian,

Thanks for the information.

I've upgraded the version from 3.9.2 to the latest version 4.6.5, and was able to use the add() method.
But I am getting an error, below is the error message.
Index is less than 0 or more than or equal to the list count.
Parameter name: index
Actual value was 1.


No changes in the previously posted code, except the add() part.
Code: Select all
//Code using the older version
pivotTable.getReportFilters().addItem(prLocationFilter);

//Changes on code after upgrading to the latest version
pivotTable.getReportFilters().add(prLocationFilter);



Besides, I noticed that in your expect file, the "Values" field is in the row labels. I am sorry that our Spire.XLS does not yet support moving this field to row labels.

Will this feature be supported in the near future?


Regards,
Kheiko

Kheiko.G
 
Posts: 4
Joined: Tue Jul 13, 2021 3:00 am

Thu Jul 15, 2021 10:26 am

Hello Kheiko,

Thanks for your feedback.
Please refer to the following code.
Code: Select all
        Workbook workbook = new Workbook();
        workbook.loadFromFile("data/Generated_File.xlsx");
        Worksheet pivotSheet = workbook.getWorksheets().get(1);
        pivotSheet.setName("PIVOT");

        CellRange dataRange = workbook.getWorksheets().get(0).getCellRange("A1:G523");
        PivotCache cache = workbook.getPivotCaches().add(dataRange);
        pivotSheet.getPivotTables().removeAt(0);
        PivotTable pivotTable = pivotSheet.getPivotTables().add("Pivot Table", pivotSheet.getCellRange("A1"), cache);

        PivotField pfPlatformColumn = (PivotField) pivotTable.getPivotFields().get("PLATFORM");
        pfPlatformColumn.setAxis(AxisTypes.Column);

        PivotField pfStatusColumn = (PivotField) pivotTable.getPivotFields().get("STATUS");
        pfStatusColumn.setAxis(AxisTypes.Column);

        PivotReportFilter prLocationFilter = new PivotReportFilter("LOC", true);
        prLocationFilter.isMultipleSelect(true);
        pivotTable.getReportFilters().add(prLocationFilter);

        PivotTableFields ptFields = pivotTable.getPivotFields();
        PivotDataFields ptDataFields = pivotTable.getDataFields();
        IPivotField iField = null;
        String iFieldName = "";

        for(int i = 0; i < ptFields.getCount(); i++)
        {
            iField = ptFields.get(i);
            iFieldName = iField.getName();

            if(iFieldName.contains("WW"))
            {
                ptDataFields.add(iField, iFieldName, SubtotalTypes.Average);
            }
        }

        workbook.saveToFile("result.xlsx", ExcelVersion.Version2016);

Besides, regarding moving the "Values" field to row labels, we have added it as a new feature into our upgrade list with the ticket SPIREXLS-3348. If it can be implemented in the future, we will inform you immediately.

Sincerely,
Brian
E-iceblue support team
User avatar

Brian.Li
 
Posts: 1271
Joined: Mon Oct 19, 2020 3:04 am

Fri Jul 16, 2021 12:41 am

Hello Brian,

Besides, regarding moving the "Values" field to row labels, we have added it as a new feature into our upgrade list with the ticket SPIREXLS-3348. If it can be implemented in the future, we will inform you immediately.

Thank you. I’ve been looking forward to this feature.


Regarding the report filter, I am now able to add the "LOC" in the Filters area.
It seems that the error message that has been encountered was not cause by the adding of report filter.
After commenting the code for generating the chart, the pivot table was successfully created.

Here is the code for creating the pivot chart :
Code: Select all
Worksheet chartSheet = workbook.getWorksheets().get(2);
chartSheet.setName("CHART");

IPivotTable iPivotTable = pivotSheet.getPivotTables().get(0);           

chartSheet.setGridLinesVisible(false);
chartSheet.setRowColumnHeadersVisible(false);
           
Chart chart = chartSheet.getCharts().add(ExcelChartType.Line, iPivotTable);
           
 chart.setTopRow(2);
 chart.setBottomRow(35);
 chart.setLeftColumn(6);
 chart.setRightColumn(21);
           
chart.setChartTitle("Platform utilization vs Time");
           
workbook.saveToFile(excelFilePath, ExcelVersion.Version2013);



Thank you very much for your help.


Regards,
Kheiko

Kheiko.G
 
Posts: 4
Joined: Tue Jul 13, 2021 3:00 am

Fri Jul 16, 2021 8:19 am

Hello Kheiko,

Thanks for your feedback. And we will keep you informed if there is any update.

Sincerely,
Brian
E-iceblue support team
User avatar

Brian.Li
 
Posts: 1271
Joined: Mon Oct 19, 2020 3:04 am

Return to Spire.Doc