Setting "Select Multiple Items" in a pivot table page field

Aspose.Cells Product Family
1

Is there a way that I can set this checkbox? I can select and unselect the values on the PivotItem just fine, but if I only have one value selected, the spreadsheet still shows “Multiple Items” are selected. I would think there would be a method on the PivotField or PivotPageFields object. Can you help?

2

Hi,


Thanks for your interest in Aspose Products.
Can you please further elaborate your question with snapshots? You may share your template Excel file by creating it manually in MS Excel. If Excel support this functionality then Aspose.Cells API must have corresponding methods as well.
As soon as you share your requirements, we will look into it to provide you further assistance in this regard.
Thank you for your cooperation and understanding.
3

You can also go to the ‘help’ menu in the software application. MS Excel, right?,

4

Hi again,


I followed your advice to check the “Help” in MS Excel. I put string “Select Multiple Items” to search. Nothing in my results matched this string 100% nor I was able to find any Checkbox in Pivot Table Options with such label. It can be difference in Excel versions. Mine is Excel 2010 64 bit [check snapshot attached].
Then I Googled it and found two related articles. Links to them are below. Can you identify what you require?
http://office.microsoft.com/en-us/access-help/allow-multiple-selection-in-a-filter-field-in-pivottable-or-pivotchart-view-HP003083504.aspx
http://office.microsoft.com/en-us/excel-help/select-data-in-a-pivottable-report-HP010167767.aspx#BMselect_multiple_items

5

OK. I’ll elaborate. I create a pivot table with the java API.I add a page field to the pivot area. This page field, or “report filter” in excel, allows me to filter my pivot table based on certain values contained. For example, if I have a page field for “Stock”, the pivot table would provide a user with a way to select only the data that matches the status that I select. I have included an example excel 2010 file that I created. Pay specific attention to the page fields, or report filters, at the top. They are identified as “Status”, “Stock”, “Qty On Hand”, and “Qty On PO”. Click on the filter. You will see selections for “All”, “Y”, and “N” with a checkbox at the bottom that says “Select Multiple Items” which allows multiple items to be selected from the filter. Then the checkbox for “Select Multiple Items” is checked, this displays “(Multiple Items)” next to the page field header as the value. With the API, I do the following…
// setup page field for Stock
pivotTable.addFieldToArea(PivotFieldType.PAGE, HEADINGS[9]);
pf = pivotTable.getFields(PivotFieldType.PAGE).get(1);
pis = pf.getPivotItems();
for(int i=0; i<pis.size(); i++)
{
PivotItem pi = pis.get(i);
if(!pi.getStringValue().equals(“Y”))
pi.hide(true);
}
This causes the “Select Multiple Items” checkbox to be selected even if I only programatically selected one value, which means excel shows the value as "(Multiple Items)"instread of just “Y”. If only one value is selected, I want to uncheck the “Select Multiple Items” checkbox so the pre-selected value will show next to the page field in the pivot table.

6

Hi,


Thanks a lot for the elaboration and sample file. That was really helpful in understanding your needs. Right now, we are looking into it to provide you a possible solution or workaround. Shortly, we will reply you back.
7

Greetings,


I am afraid, right now Aspose.Cells JAVA API does not support such feature. So we have logged it as a “Required Feature” in our Tracking System under Ticket ID CELLSJAVA-28372. We will provide this functionality in our next release.

Thank you for your patience.
8

Hi,

We have fixed this issue. Please download Aspose.Cells for Java v2.5.4.6.

Please use isMultipleItemSelectionAllowed()/setMultipleItemSelectionAllowed(boolean) methods of pivotfield to set “select multi items”.

9

Yes, its fixed but its backwards. If I want to disallow multiple items to be selected, I would naturally choose false. However, the method does not perform as expected in this manner. I had to select true, to allow multiple items, so that the “multiple items” test does not display. Also, if I click on the filter, the checkbox is still selected regardless of the boolean value I pass.

10

Hi,


Thank you for bringing this to our knowledge. I have noticed both issues as mentioned in your last post. So I have re-opened the ticket associated with this thread and also I have attached your comments to it. Soon we will figure this out and let you know here.
11

Hi,


We have fixed the issue as mentioned in our last post. Please test your requirement with our latest fix version of Aspose.Cells JAVA v2.5.4.7 and let us know of your feedback.

Please use field.setCurrentPageItem(index) method as given in below source code sample,

JAVA
PivotField field = pivot.getPageFields().get(1);
field.setMultipleItemSelectionAllowed(false);
field.setCurrentPageItem((short)0);

12

Ok. Looks like it works now. So instead of showing or hiding specific pivotitems as i was doing before with pivotitem.hide(true), I just test the pivot item for the value and if the value happens to be the one I want to preselect, the index of the pivot item is used for the pivotfield.setCurrentPageItem((short)index). Thanks guys.

13

The issues you have found earlier (filed as 28372) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.