How to select record on report filter in pivot table

Hi,

Please see attached excel file, in “BillingPivot” tab, a report filter on B3, the values are from G cell of “BillingData” tab.

I want to make the report filter select the max value(in this case the max value is 2006/4/21) after some new records add to BillingData tab by using aspose, and the pivot table should be really filtered, please give me some clue, thanks in advance.

Hi,


Apologies for late reply.
What I understand from your post, you have two queries.

1) You want to update the Pivot Table data source. Please see the below reference article. You need create a name range and you may assign it to your Pivot Table as data source.
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/pivot-table.html

2) You want to programmatically select the MAX value from a Report Filter of Pivot Table. I am afraid that the current API of Aspose.Cells JAVA does not support this feature. Although, we have logged this as “Requested Feature” in our database under Ticket ID CELLSJAVA-28115. First we will analyse this requirement and will update you on it’s implementation time lines.

Please feel free to write back.

Hi,


Regarding the second point in above reply, you can use the setCurrentPageItem method of PivotField using our latest fix version of Aspose.Cells JAVA v2.5.4.6 [attached].

Sample Code JAVA
workbook.open(“D:\Billing.xls”);
PivotTable table = workbook.getWorksheets().getSheet(0).getPivotTables().get(0);
PivotField field = table.getPageFields().get(1);
field.setCurrentPageItem((short)(field.getItemCount()-1));
table.setRefreshDataOnOpeningFile(true);
//if need
table.calculateData();

Please feed us back with your results. Thank you

Could you provide instructions on how to do this in the .NET version? I don’t seem to have access to most of these methods; starting with the get methods (I can get the fields another way, but I don’t even have a setCurrentPageItem or anything similar on the field.


Thanks in advance,
Mirek

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

Please download and use the latest version: Aspose.Cells
for .NET v7.3.5.1
.

Please use PivotTField.CurrentPageItem the property. Below is the translated code in .NET

C#


Workbook workbook = new Workbook(“D:\Billing.xls”);

PivotTable table = workbook.Worksheets[0].PivotTables[0];

PivotField field = table.PageFields[1];

field.CurrentPageItem = (short)(field.ItemCount - 1);

table.RefreshDataOnOpeningFile = true;

table.CalculateData();


Is there any updates on Ticket Id CELLSJAVA-28115 .
I have a similar requirement.

Hi Nilima,

Thanks for your posting and using Aspose.Cells.

This is actually an old ticket which is non-existent in the current database. Could you please elaborate your requirements in detail in a New Post. We will look into it and create a New Ticket in the current database to support your feature request.

Thanks for your cooperation.