Filtering Reports in JavaScript Pivot Table Using Advanced Excel-like Filters

In the JavaScript Pivot Table, filtering reports helps to simplify, organize, and view only a necessary portion of data, along with its summary, for in-depth analysis. With our control, you can perform:

• Member filtering (basic)
• Label filtering (advanced)
• Value filtering (advanced)

Member filtering

The member-based filter is simple and commonly used for refining and viewing a small set of data. In this filter, distinct members will be shown in the filter dialog beside the selected field. End-users can either select or deselect members to display specific data only.

The member-based filter is available in the filter dialog under the Member tab and can be enabled using the following code sample.

var pivotTableObj = new ej.pivotview.PivotView({
    dataSourceSettings: {
        allowMemberFilter: true
    }
});
pivotObj.appendTo('#PivotTable');

Note: The filtering option is always enabled by default for data filtering.

Other important functionalities include:

• Search option to navigate quickly to the desired members.
• When all members are deselected, the OK button in the member filter dialog will be disabled. Therefore, at least one member should be chosen and bound to the pivot table.

Performance tip:

You can set a limit for showing members in the filter dialog when loading large data. Based on this limit, initial loading should take place quickly without any performance constraints. Additionally, a message with the remaining member count, which is not part of the UI, will be shown at the bottom of the member editor dialog.

In the meantime, you can use the search option to refine members that are not part of the UI (members exceeding the threshold limit). Enter the starting character(s) in the search option to get the desired member(s), and you can either select or deselect the members to continue the filtering process. To learn more about this, refer to this documentation.

Label filtering

Similar to member-based filtering, label filtering is also used to refine members in rows and columns. However, it filters the members on the basis of the condition set exclusively by the member name. For example, a condition can be set for viewing countries that begins with “United”.

The label-based filter is available in the filter dialog under the Label tab and can be enabled using the following code sample.

var pivotTableObj = new ej.pivotview.PivotView({
    dataSourceSettings: {
        allowLabelFilter: true
    }
});
pivotObj.appendTo('#PivotTable');

Filtering conditions tend to vary among date, string, and number data types. We’ll discuss this in the following sections.

Date type

Date type applies to a field with members set to date data type. For example, end users can set a condition for viewing the sales and stock of certain products after “01 January 2000”. The conditions applicable for date data type are: equals, does not equal, before, before or equal to, after, after or equal to, between, and not between.

String type

String type applies to a field with members set to string data type. For example, end users can set a condition for viewing countries that begin with “United”. The conditions applicable for string data type are: equals, does not equal, begins with, does not begin with, ends with, does not end with, contains, does not contain, greater than, greater than or equal to, less than, less than or equal to, between, and not between.

Number type

Number type applies to a field with members set to number data type. For example, end users can set a condition for viewing products whose ID is between “1012” and “1017”. The conditions applicable for number data type are: equals, does not equal, greater than, greater than or equal to, less than, less than or equal to, between, and not between.

Value filtering

The value-based filter refines the data based on the condition applied to the measure (value field). For example, end users can set a condition for viewing countries that have sold less than 100,000 units of goods in certain years, where “Units” act as a measure. The condition can be set to equal, does not equal, greater than, less than, between, not between, etc.

The value-based filter is available in the filter dialog under the Value tab and can be enabled using the following code sample.

var pivotTableObj = new ej.pivotview.PivotView({
    dataSourceSettings: {
        allowValueFilter: true
    }
});
pivotObj.appendTo('#PivotTable');

References

Online sample

https://ej2.syncfusion.com/demos/#/material/pivot-table/field-list.html

UG document

Member filtering: https://ej2.syncfusion.com/javascript/documentation/pivotview/member-filtering/

Label filtering: https://ej2.syncfusion.com/javascript/documentation/pivotview/label-filtering/

Value filtering: https://ej2.syncfusion.com/javascript/documentation/pivotview/value-filtering/

Conclusion

In this blog post, we have seen the basic and advanced features, such as label filtering and value filtering, of the JavaScript Pivot Table. You can try this control in your application development.

If you are an existing customer, you can download the new version of Essential Studio^® from the Downloads page and try these filtering options. If you are a new customer, try our 30-day free trial to check out this control.

Please post your questions in the comments section below. You can also contact us through our support forum or Direct-Trac.