SpreadJS 14
Features / Pivot Table / Filter
In This Topic
    Filter
    In This Topic

    You can apply certain filters to a pivot table in order to view only the required information while hiding the rest of the data.

    Row and Column Fields Filter

    You can apply Row and Column Fields filter when the data of rows or columns needs to be filtered. The following image shows the filter applied on a pivot table to show the quantity for only East region.

    Filter the row and column fields

    The following example code shows how to filter the Region column to show only East region's data.

    Javascript
    Copy Code
    // add filter field
    myPivotTable.add("OrderDate", "OrderDate", GC.Spread.Pivot.PivotTableFieldType.filterField);
    let itemList = ["East"];
    myPivotTable.labelFilter("Region", { textItem: { list: itemList, isAll: false } });
    

    Value Filter

    The value filter can be used to filter the value based fields in a pivot table based on summary values (like sum, count, max, min, average etc.). The following image shows the value filter applied on a pivot table to show the 'Sum of quantity' of Category column with values greater than 2000.

    Apply value filter to data

    The following example code shows how to apply value filter to display 'sum of quantity' greater than 2000.

    Javascript
    Copy Code
    // add filter field
    myPivotTable.add("OrderDate", "OrderDate", GC.Spread.Pivot.PivotTableFieldType.filterField);
    // apply Value Filter for "Sum of quantity" greater than 2000 using FilterInfo
    let valueFilter = { condition: { conType: GC.Pivot.PivotConditionType.value, val: [2000], operator: 2 }, conditionByName: "Sum of quantity" };
    myPivotTable.valueFilter("Category", valueFilter);
    

    Label Filter

    The label filter can be used to filter row label items. It provides different options based on the data type of the field to be filtered. For example, for string or number data type, the options provided are equals, does not equals, begin with, contains, greater than etc. whereas for date data type, the options provided are before, after, tomorrow, next week etc. The following example demonstrates how to apply a label filter to "Region" column field to display city which starts with “S”.

    Apply label filter to data

    The following sample code shows how to apply a label filter for city which starts with "S".

    Javascript
    Copy Code
    // add filter field
    myPivotTable.add("OrderDate", "OrderDate", GC.Spread.Pivot.PivotTableFieldType.filterField);
    // apply Label Filter for 'City' names starting with 'S'using FilterInfo
    let labelFilter = { condition: { conType: GC.Pivot.PivotConditionType.caption, val: 'S', operator: 2 } };
    myPivotTable.labelFilter("City", labelFilter);
    

    You can also use the manual filter in place of defining conditions for label filter. The following image shows manual filter applied to display the data for 'Jersey' and 'Seattle' cities.

    Apply manual filter

    The following code example shows how to apply manual filter.

    Javascript
    Copy Code
    // add manual filter
    let parent = { textItem: { list: ["Jersey", "Seattle"], isAll: false } };
    myPivotTable.labelFilter("City", parent);
    

    You can also choose to apply manual and condition based label filters together by setting the allowMultipleFiltersPerField option to true. The following example demonstrates how to apply multiple filters in a pivot table.

    Apply multiple filter

    The following example shows how to apply multiple filters in a pivot table.

    Javascript
    Copy Code
    myPivotTable.options.allowMultipleFiltersPerField = true;
    // When allowMultipleFiltersPerField is set, the two filters i.e. both take effect
    var labelFilter = {
        textItem: { list: ["Jersey", "San Francisco"], isAll: false },
        condition: { conType: GC.Pivot.PivotConditionType.caption, operator: GC.Pivot.PivotCaptionFilterOperator.beginsWith, val: 'S' }
    };
    myPivotTable.labelFilter("City", labelFilter);
    

    Date Type Filter

    A date type filter can be used to specify a criteria that is applied to date type items of the pivot table. The following image shows date type filter applied on a pivot table to show quarterly data.

    Apply date type filter to data

    The following example code shows how to apply a date type filter.

    Javascript
    Copy Code
    let groupInfo = {
         originFieldName: "OrderDate",
         dateGroups: [
             { by: GC.Pivot.DateGroupType.quarters },
             { by: GC.Pivot.DateGroupType.years }]
     };
     myPivotTable.group(groupInfo);
     myPivotTable.add("OrderDate", "Qtr", GC.Spread.Pivot.PivotTableFieldType.columnField);
    // apply label Filter using FilterInfo
     let labelFilter = { condition: { conType: GC.Pivot.PivotConditionType.date, val: [], operator: GC.Pivot.PivotDateFilterOperator.Q1 } };
     myPivotTable.labelFilter("Qtr", labelFilter);       
    

    Create FilterInfo

    The above filters are applied by creating the filter info. The following table describes the conditions which can be used to create filter info:

    Condtions conType operator val conditionByname type isWholeDay
    GC.Pivot.IPivotCaptionConditionFilterInfo GC.Pivot.PivotConditionType.caption GC.Pivot.PivotCaptionFilterOperator Array.<string>

    /

    /

    /

    GC.Pivot.IPivotDateConditionFilterInfo GC.Pivot.PivotConditionType.date GC.Pivot.PivotDateFilterOperator Array.<Date>

    /

    /

    boolean
    GC.Pivot.IPivotTop10ConditionFilterInfo GC.Pivot.PivotConditionType.top10 GC.Pivot.PivotTop10FilterOperator number

    /

    GC.Pivot.PivotTop10FilterType

    /

    GC.Pivot.IPivotValueConditionInfo GC.Pivot.PivotConditionType.value GC.Pivot.PivotValueFilterOperator Array.<number> string

    /

    /

    Hide Filter Button

    You can hide the filter buttons from the pivot table using showFilter option.

    Apply date type filter to data

    The following example code shows how to hide filter buttons.

    Javascript
    Copy Code
    // Set showFilter to false
    myPivotTable.options.showFilter = false;