none
Performance - Setting PivotField PivotItems Visibility RRS feed

  • Question

  • Hi,

    This is regarding setting text filters (multi select) in PivotTable's (non OLAP) PivotField from code (VSTO/INTEROP c#).  

    Performance issue - Enumerating though the list of PivotItems (has more than 1000 text fields) and setting Visibility property to false for non-selected 

    We set the Excel Application's ScreenUpdating, Interactive properties to false as well... 

    Code which we currently use to do this :-

    List<string> _selectedItems; -list of items which user wanted to select and see

    Xls.PivotItems _pivotItems = _pivotField.PivotItems();

    for(int _count = 1; _count <= _pivotItems.Count; _count++)

    {

    Xls.PivotItem _pivotItem = _pivotItems[_count];

    if(!_selectedItems.Contains(_pivotItem.SourceName))

    {

    _pivotItem.Visible = false; 

    }

    }

    Could someone pls let me know if there is a better and performance effective way of doing it? 

    Thursday, February 11, 2016 2:49 PM

All replies

  • VSTO doesn't provide anything for that. I'd suggest asking Excel specific questions on the Excel for Developers forum instead.
    Thursday, February 11, 2016 3:55 PM
  • Hi choksr,

    To narrow down the issue, I suggest that you print the time after every line of code execution. Base on my test, setting visible for the 1000 pivotitems only need 3s. The text match for the List<string> should time costting.

    If I understood correctly, you may consider using SortedList. And if if you still have the performance issue about text match, I suggest that you reopen a new thread in the Visual C# forum.

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, February 12, 2016 5:12 AM
    Moderator
  • Toggling the visibility in the iterative way (when there are more row fields) takes approx. 200ms (& varies) per pivotitem. 

    We used the idea mentioned mentioned in the below link :-

    http://dailydoseofexcel.com/archives/2013/11/14/filtering-pivots-based-on-external-ranges/

    Monday, June 13, 2016 5:53 PM