none
Apply Multiselect filter on a PageFIeld using VSTO RRS feed

  • Question

  • Hello Team,

    we are using vsto with excel 2007 connecting to a SSAS and populating a pivot table.

    we have  Action pane  to select a value from a list and on click of search we want to pass the values( Select Multiple items) as a  a filter in the pivot report( on a Filter Attribute which is of type page field).

    We are able to set the visible fields by using the property PivotField.VisibleItemsList=<stringArray>

    The PageField Filter Attribute has the data in a hierarchical format and is working only  if we are giving the complete traversing path ( where as we have only the last item levee value) 

    example:

    If we consider that thepagefield attribute has the following data

    (Application-->pages-->controls-->events-->"click")

    From the action pane selection we get the value as "click" and this should filter the pivot table only with records having the text as "Click"

    In order to make tis work currently we are giving 

    string[] strarrr={"[Application].[pages].[controls].[events].[click]"};

    PivotField.VisibleItemsList=strarrr;

    Since the  value can be there in any part of the  traversing path ( the heirechy path) .We are not able to make it dynamic.

    We would like to know is there any other standard way present to have a multiple filters on the OLAP Cube pivot for a specific page field .

    Thanks,

    Pr

    Monday, February 6, 2017 6:31 PM

All replies

  • Hi aspnetdeveloper17,

    I try to find an example for filter in pivot table.

    you can refer below.

     AdvancedFilter(
        xlPT,
        (xlPIA.PivotField)xlPT.PivotFields("[Product].[Product Categories].[Product Name]"),
        xlPIA.XlPivotFilterType.xlCaptionContains,
        null,
        "Silver",
        null,
        -1,
        "PivotLabelFilter",
        "Label filter contains silver on member property field",
        (xlPIA.PivotField)xlPT.PivotFields("[Product].[Product Categories].[Product Name].[Color]"),
        (xlPIA.Worksheet)xlWB.Worksheets[4]);
    
        xlWB.Close(false, miss, miss);
        }
    
            public static void AdvancedFilter(xlPIA.PivotTable xlPT, xlPIA.PivotField xlPF, xlPIA.XlPivotFilterType xlPivotFilterType, object dataField, object value1, object value2, int order, string name, string description, xlPIA.PivotField memberPropertyField, xlPIA.Worksheet outputSheet)
        {
        xlPIA.PivotFilter xlPivotFilter;
      
        int numFilters;
    

    multiple fields enabled for filter

    xlPivotFilter = xlPF.PivotFilters.Add(
        xlPivotFilterType,
        dataField,
        value1,
        value2,
        order,
        name,
        description,
        memberPropertyField);
    
        if (xlPT.AllowMultipleFilters == true) 
        {
        outputSheet.get_Range("A1", Type.Missing).Value2 = "Allow multiple filters is set to true";
        }
        else
        {
        outputSheet.get_Range("A1", Type.Missing).Value2 = "Allow multiple filters is set to false";
        }
    

    you can see that they are also try to fetch the field hierarchically .

    Reference:

    Filtering Data Programmatically in Excel 2007 by Using PivotTables

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, February 7, 2017 5:37 AM
    Moderator
  • Hello Deepak,

    Thanks for your Reply.

    The problem for me here is the color  "Silver" can be part of 

    [Product].[Product Categories].[Product Name].[Color].[Primary_Color] or

    [Product].[Product Categories].[Product Name].[Color].[Secondary_color] or

    [Product].[Product Categories].[Product Name].[Color].[Complimentary_color] etc

    and we have only the value "Silver" available which needs to be filtered against 

    [Product].[Product Categories].[Product Name]

    Now i am trying to filter the color silver in the above hierarchy. This we are trying to achieve in a OLAP PivotCube.

    Can you please help with some more insight into this.

    Thanks,

    Pr

    Tuesday, February 7, 2017 5:00 PM
  • Hi aspnetdeveloper17,

    can you show me a full code so that I can make a test with it on my side and try to solve the issue if possible.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, February 8, 2017 7:57 AM
    Moderator