Filtering in Angular Pivotview component
12 Sep 202524 minutes to read
Filtering helps you focus on specific data by showing only the records you need in the Pivot Table. This allows you to analyze relevant information more effectively by including or excluding specific members through the user interface or programmatically.
The Pivot Table offers three types of filtering options:
- Member filtering
- Label filtering
- Value filtering
When all filtering options are disabled programmatically, the filter icon will not appear in the field list or grouping bar interface.
Member filtering
This filtering option displays the Pivot Table with selective records based on the members you choose to include or exclude in each field. By default, member filtering is enabled through the allowMemberFilter
property in the dataSourceSettings
.
Users can apply member filters at runtime by clicking the filter icon next to any field in the row, column, and filter axes, available in both the field list and grouping bar interfaces.
You can also configure filtering programmatically using the filterSettings
property during the initial rendering of the component. The essential settings required to add filter criteria are:
-
name
: Sets the appropriate field name for filtering. -
type
: Specifies the filter type as Include or Exclude to include or exclude field members respectively. -
items
: Defines the members that need to be either included or excluded from the display. -
levelCount
: Sets the level count of the field to fetch data from the cube. Note: This property is applicable only for OLAP data sources.
When you specify unavailable or inappropriate members in the include or exclude filter items collection, they will be ignored.
import { NgModule } from '@angular/core'
import { BrowserModule } from '@angular/platform-browser'
import { PivotViewAllModule, PivotFieldListAllModule } from '@syncfusion/ej2-angular-pivotview'
import { Component, OnInit } from '@angular/core';
import { IDataSet } from '@syncfusion/ej2-angular-pivotview';
import { Pivot_Data } from './datasource';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
@Component({
imports: [
PivotViewAllModule,
PivotFieldListAllModule
],
standalone: true,
selector: 'app-container',
// specifies the template string for the pivot table component
template: `<ejs-pivotview #pivotview id='PivotView' height='350' [dataSourceSettings]=dataSourceSettings [width]=width></ejs-pivotview>`
})
export class AppComponent implements OnInit {
public width?: string;
public dataSourceSettings?: DataSourceSettingsModel;
ngOnInit(): void {
this.width = '100%';
this.dataSourceSettings = {
dataSource: Pivot_Data as IDataSet[],
expandAll: false,
allowMemberFilter:true,
drilledMembers: [{ name: 'Country', items: ['France'] }],
filterSettings: [{ name: 'Country', type: 'Exclude', items: ['United States'] }],
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
formatSettings: [{ name: 'Amount', format: 'C0' }],
filters: []
};
}
}
import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));
Option to select and unselect all members
This option lets you quickly manage all members at once, saving time when working with large datasets. The member filter dialog includes an All option that provides a convenient way to select or deselect all available members with a single click.
When you check the All option, it selects all members in the list. When you uncheck it, all members become deselected. If you manually select some members while others remain unselected, the All option displays an intermediate state (partially checked) to show that the list contains both selected and unselected members.
Note: When all members are deselected, the OK button becomes disabled. You must select at least one member to apply the filter and display data in the Pivot Table.
Provision to search specific member(s)
This option helps you quickly locate specific members without scrolling through long lists. The member filter dialog includes a built-in search box that allows you to find members by typing part of their name.
Simply enter the starting characters of the member name you want to find, and the list will automatically filter to show only matching members. This makes it easy to locate and select specific members, especially when dealing with large datasets.
Option to sort members
This option allows you to organize members in a logical order for easier selection and review. The member filter dialog provides built-in sort icons that let you arrange members in ascending or descending order.
You can click the ascending sort icon to arrange members from A to Z (or lowest to highest for numerical values), or click the descending sort icon to arrange them from Z to A (or highest to lowest). When neither sorting option is selected, members appear in their original order as retrieved from the data source.
Performance tips
The member filter dialog improves loading performance when working with large datasets by limiting the number of members displayed initially. This helps you work with extensive data without experiencing delays while the member list loads.
You can control how many members are displayed in the member filter dialog using the maxNodeLimitInMemberEditor
property. By default, this property is set to 1000. When your data contains more members than this limit, only the specified number will be shown initially, and a message will indicate how many additional members are available.
import { NgModule } from '@angular/core'
import { BrowserModule } from '@angular/platform-browser'
import { PivotViewAllModule, PivotFieldListAllModule } from '@syncfusion/ej2-angular-pivotview'
import { Component } from '@angular/core';
import { IDataSet, PivotView, GroupingBarService, FieldListService } from '@syncfusion/ej2-angular-pivotview';
import { Pivot_Data } from './datasource';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
@Component({
imports: [
PivotViewAllModule,
PivotFieldListAllModule
],
standalone: true,
selector: 'app-container',
providers: [GroupingBarService, FieldListService],
// specifies the template string for the pivot table component
template: `<div><ejs-pivotview #pivotview id='PivotView' height='350' [dataSourceSettings]=dataSourceSettings showGroupingBar='true' [width]=width maxNodeLimitInMemberEditor='100' showFieldList='true'></ejs-pivotview></div>`
})
export class AppComponent {
public width?: string;
public dataSourceSettings?: DataSourceSettingsModel;
public maxNodeLimitInMemberEditor?: number;
public date1?: number;
public date2?: number;
data(count: number) {
let result: Object[] = [];
let dt: number = 0;
for (let i: number = 1; i < count + 1; i++) {
dt++;
let round: string;
let toString: string = i.toString();
if (toString.length === 1) {
round = "0000" + i;
} else if (toString.length === 2) {
round = "000" + i;
} else if (toString.length === 3) {
round = "00" + i;
} else if (toString.length === 4) {
round = "0" + i;
} else {
round = toString;
}
result.push({
ProductID: "PRO-" + round,
Year: "FY " + (dt + 2013),
Price: Math.round(Math.random() * 5000) + 5000,
Sold: Math.round(Math.random() * 80) + 10
});
if (dt / 4 == 1) {
dt = 0;
}
}
return result;
}
ngOnInit(): void {
this.width = "100%";
this.maxNodeLimitInMemberEditor= 100;
this.dataSourceSettings = {
dataSource: this.data(1000) as IDataSet[],
enableSorting: false,
expandAll: true,
allowMemberFilter:true,
formatSettings: [{ name: 'Price', format: 'C0' }],
rows: [{ name: 'ProductID' }],
columns: [{ name: 'Year' }],
values: [{ name: 'Price', caption: 'Unit Price' }, { name: 'Sold', caption: 'Unit Sold' }]
};
}
}
import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));
When the member count exceeds your set limit, you can use the search option to find specific members beyond the displayed range. For example, if your data contains 5000 members named “Node 1”, “Node 2”, “Node 3”, and so on, and you set the maxNodeLimitInMemberEditor
property to 500, only the first 500 members will appear by default. The dialog will show a message like “4500 more items. Search to refine further.” To access members 501 to 5000, type the starting characters in the search box to locate the desired members. Once the members appear in the list, you can select or deselect them to apply your filtering preferences.
Loading members on-demand
This option is applicable only for OLAP data sources.
This option improves the performance of the member editor by loading members only when needed, rather than loading all members at once. You can enable this by setting the loadOnDemandInMemberEditor
property to true. When enabled, only the first level members are loaded initially from the OLAP cube, allowing the member editor to open quickly without performance delays.
By default, this property is set to true and search operations will only apply to the currently loaded level members. You can load additional level members using either of the following methods:
- Expand individual members: Click the expander button next to any member to load only its child members.
- Load by level selection: Choose a specific level from the dropdown list to load all members up to that selected level from the cube.
This approach prevents performance issues when working with hierarchies that contain large numbers of members. Once level members are loaded, they remain available for all subsequent operations (such as reopening the dialog or drag-and-drop actions) and persist until you refresh the web page.
import { NgModule } from '@angular/core'
import { BrowserModule } from '@angular/platform-browser'
import { PivotViewAllModule, PivotFieldListAllModule } from '@syncfusion/ej2-angular-pivotview'
import { Component } from '@angular/core';
import { IDataSet, PivotView, FieldListService, CalculatedFieldService } from '@syncfusion/ej2-angular-pivotview';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
@Component({
imports: [
PivotViewAllModule,
PivotFieldListAllModule
],
standalone: true,
selector: 'app-container',
providers: [FieldListService, CalculatedFieldService],
// specifies the template string for the pivot table component
template: `<ejs-pivotview #pivotview id='PivotView' height='350' [dataSourceSettings]=dataSourceSettings [width]=width allowCalculatedField='true' loadOnDemandInMemberEditor='true' showFieldList='true'></ejs-pivotview>`
})
export class AppComponent {
public dataSourceSettings?: DataSourceSettingsModel;
public width?: string;
ngOnInit(): void {
this.dataSourceSettings = {
catalog: 'Adventure Works DW 2008 SE',
cube: 'Adventure Works',
providerType: 'SSAS',
enableSorting: true,
url: 'https://bi.syncfusion.com/olap/msmdpump.dll',
localeIdentifier: 1033,
rows: [
{ name: '[Customer].[Customer Geography]', caption: 'Customer Geography' },
],
columns: [
{ name: '[Product].[Product Categories]', caption: 'Product Categories' },
{ name: '[Measures]', caption: 'Measures' },
],
values: [
{ name: '[Measures].[Customer Count]', caption: 'Customer Count' },
{ name: '[Measures].[Internet Sales Amount]', caption: 'Internet Sales Amount' }
],
filters: [
{ name: '[Date].[Fiscal]', caption: 'Date Fiscal' },
],
calculatedFieldSettings: [
{
name: 'BikeAndComponents',
formula: '([Product].[Product Categories].[Category].[Bikes] + [Product].[Product Categories].[Category].[Components] )',
hierarchyUniqueName: '[Product].[Product Categories]',
formatString: 'Standard'
},
{
name: 'Order on Discount',
formula: '[Measures].[Order Quantity] + ([Measures].[Order Quantity] * 0.10)',
formatString: 'Currency'
}
]
};
this.width = "100%";
}
}
import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));
In the example above, the “Customer Geography” dimension loads with only the first level (Country) initially. Search operations will apply only to the “Country” level members. You can then load the next level members (State-Province) on-demand in two ways:
- Expand specific countries: When you expand “Australia”, the “State-Province” members load only for Australia.
- Load all states by level: When you select “State-Province” from the dropdown list, all “State-Province” members load across all countries (Australia, Canada, France, etc.).
Once loaded, these members are stored internally and remain available until you refresh the page.
When the loadOnDemandInMemberEditor
property is set to false, all members from all levels are loaded during the initial setup. This approach executes a single query to retrieve all members at once. While this may cause slower performance when opening the member editor due to the large number of members being fetched, expand and search operations will be faster since all members are already available.
Loading members based on level number
This property is applicable only for OLAP data sources.
This option enables you to control the depth of member loading by specifying how many levels should be loaded initially. By setting the levelCount
property in the filterSettings
, you can improve performance and focus filtering operations on specific hierarchy levels.
The levelCount
property is set to 1 by default, which means only the first level members are loaded initially. When you apply filters or search operations, they will only affect the members within the loaded levels.
import { NgModule } from '@angular/core'
import { BrowserModule } from '@angular/platform-browser'
import { PivotViewAllModule, PivotFieldListAllModule } from '@syncfusion/ej2-angular-pivotview'
import { Component } from '@angular/core';
import { IDataSet, PivotView, FieldListService, CalculatedFieldService } from '@syncfusion/ej2-angular-pivotview';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
@Component({
imports: [
PivotViewAllModule,
PivotFieldListAllModule
],
standalone: true,
selector: 'app-container',
providers: [FieldListService, CalculatedFieldService],
// specifies the template string for the pivot table component
template: `<ejs-pivotview #pivotview id='PivotView' height='350' [dataSourceSettings]=dataSourceSettings [width]=width allowCalculatedField='true' showFieldList='true'></ejs-pivotview>`
})
export class AppComponent {
public dataSourceSettings?: DataSourceSettingsModel;
public width?: string;
ngOnInit(): void {
this.dataSourceSettings = {
catalog: 'Adventure Works DW 2008 SE',
cube: 'Adventure Works',
providerType: 'SSAS',
enableSorting: true,
url: 'https://bi.syncfusion.com/olap/msmdpump.dll',
localeIdentifier: 1033,
rows: [
{ name: '[Customer].[Customer Geography]', caption: 'Customer Geography' },
],
columns: [
{ name: '[Product].[Product Categories]', caption: 'Product Categories' },
{ name: '[Measures]', caption: 'Measures' },
],
values: [
{ name: '[Measures].[Customer Count]', caption: 'Customer Count' },
{ name: '[Measures].[Internet Sales Amount]', caption: 'Internet Sales Amount' }
],
filters: [
{ name: '[Date].[Fiscal]', caption: 'Date Fiscal' },
],
calculatedFieldSettings: [
{
name: 'BikeAndComponents',
formula: '([Product].[Product Categories].[Category].[Bikes] + [Product].[Product Categories].[Category].[Components] )',
hierarchyUniqueName: '[Product].[Product Categories]',
formatString: 'Standard'
},
{
name: 'Order on Discount',
formula: '[Measures].[Order Quantity] + ([Measures].[Order Quantity] * 0.10)',
formatString: 'Currency'
}
],
filterSettings: [
{
name: '[Customer].[Customer Geography]', items: ['[Customer].[Customer Geography].[State-Province].&[NSW]&[AU]'], type: 'Exclude',
levelCount: 2
}
]
};
this.width = "100%";
}
}
import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));
In the above example, the levelCount
is set to 2 for the “Customer Geography” dimension in filterSettings
. This loads both the “Country” and “State-Province” levels during the initial loading process. Any search or filter operations will be applied only to the members within these two levels. To access members from deeper levels like “City”, you can either expand the respective “State-Province” node or select the “City” level from the dropdown list.
Label filtering
Label filtering allows you to display only the data with specific header text across row and column fields, making it easier to focus on relevant information in your Pivot Table. This filtering works with three types of data:
- String data type
- Number data type
- Date data type
To enable label filtering, set the allowLabelFilter
property to true in the dataSourceSettings
. Once enabled, you can access the filtering options by clicking the filter icon next to any field in the row or column axis of the field list or grouping bar. This opens the filtering dialog where you can navigate to the “Label” tab to apply your label filtering criteria.
Filtering string data type through code
String-based label filtering enables you to programmatically show only data that matches specific text values in your row and column fields, making it easier to focus on the exact information you need.
This filtering approach is specifically designed for fields containing string data type members. You can configure the filtering through the filterSettings
property in your code. The following properties are required for label filtering:
-
name
: Specifies the field name to apply the filter. -
type
: Sets the filter type as Label for the specified field. -
condition
: Defines the operator type such as Equals, GreaterThan, LessThan, and others. -
value1
: Sets the primary value for comparison. -
value2
: Sets the secondary value for comparison. This property is only applicable for operators like Between and NotBetween. -
selectedField
: Specifies the level name of a dimension where the filter should be applied. NOTE: This property is applicable only for OLAP data sources.
For example, to display only countries containing “United” in their name from a “Country” field, set the value1
property to “United” and the condition
property to Contains.
The following table shows all available operators for label filtering:
Operator | Description |
---|---|
Equals | Shows records that exactly match the specified text. |
DoesNotEquals | Shows records that do not match the specified text. |
BeginWith | Shows records that start with the specified text. |
DoesNotBeginWith | Shows records that do not start with the specified text. |
EndsWith | Shows records that end with the specified text. |
DoesNotEndsWith | Shows records that do not end with the specified text. |
Contains | Shows records that contain the specified text anywhere. |
DoesNotContains | Shows records that do not contain the specified text. |
GreaterThan | Shows records where the text value is alphabetically greater. |
GreaterThanOrEqualTo | Shows records where the text value is alphabetically greater than or equal. |
LessThan | Shows records where the text value is alphabetically less. |
LessThanOrEqualTo | Shows records where the text value is alphabetically less than or equal. |
Between | Shows records with text values that fall between two specified values. |
NotBetween | Shows records with text values that do not fall between two specified values. |
import { NgModule } from '@angular/core'
import { BrowserModule } from '@angular/platform-browser'
import { PivotViewAllModule, PivotFieldListAllModule } from '@syncfusion/ej2-angular-pivotview'
import { Component, OnInit } from '@angular/core';
import { IDataSet } from '@syncfusion/ej2-angular-pivotview';
import { Pivot_Data } from './datasource';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
@Component({
imports: [
PivotViewAllModule,
PivotFieldListAllModule
],
standalone: true,
selector: 'app-container',
// specifies the template string for the pivot table component
template: `<ejs-pivotview #pivotview id='PivotView' [dataSourceSettings]=dataSourceSettings [width]=width height='350'></ejs-pivotview>`
})
export class AppComponent implements OnInit {
public width?: string;
public dataSourceSettings?: DataSourceSettingsModel;
ngOnInit(): void {
this.width = '100%';
this.dataSourceSettings = {
dataSource: Pivot_Data as IDataSet[],
expandAll: false,
allowLabelFilter: true,
filterSettings: [{ name: 'Country', type: 'Label', condition: 'Contains', value1: 'United' }],
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
filters: []
};
}
}
import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));
Filtering number data type through code
Filter numeric data programmatically to display only values that meet specific numeric conditions, helping you analyze data patterns and ranges more effectively. This filtering approach is specifically designed for fields containing numeric data types and follows the same configuration method as string data filtering, with one key difference: set the type
property to Number enumeration instead of Label.
To filter numeric values, specify the filtering criteria using the following properties:
-
value1
: The primary value for comparison -
condition
: The comparison operator -
value2
: The secondary value (required for Between and NotBetween conditions)
For example, to display only sales data where the “Sold” field values are less than 40000, set value1
to “40000” and condition
to LessThan.
The following operators are supported for number data type: Equals, DoesNotEquals, GreaterThan, GreaterThanOrEqualTo, LessThan, LessThanOrEqualTo, Between, and NotBetween.
Number filtering is available only when the field contains numeric data format.
import { NgModule } from '@angular/core'
import { BrowserModule } from '@angular/platform-browser'
import { PivotViewAllModule, PivotFieldListAllModule } from '@syncfusion/ej2-angular-pivotview'
import { Component, OnInit } from '@angular/core';
import { IDataSet } from '@syncfusion/ej2-angular-pivotview';
import { Pivot_Data } from './datasource';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
@Component({
imports: [
PivotViewAllModule,
PivotFieldListAllModule
],
standalone: true,
selector: 'app-container',
// specifies the template string for the pivot table component
template: `<ejs-pivotview #pivotview id='PivotView' [dataSourceSettings]=dataSourceSettings [width]=width height='350'></ejs-pivotview>`
})
export class AppComponent implements OnInit {
public width?: string;
public height?: number;
public dataSourceSettings?: DataSourceSettingsModel;
ngOnInit(): void {
this.width = '100%';
this.dataSourceSettings = {
dataSource: Pivot_Data as IDataSet[],
expandAll: false,
allowLabelFilter: true,
filterSettings: [{ name: 'Amount', type: 'Number', condition: 'LessThan', value1: '40000' }],
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
values: [{ name: 'Sold', caption: 'Units Sold' }],
rows: [{ name: 'Amount', caption: 'Sold Amount' }],
filters: [{ name: 'Country' }, { name: 'Products' }]
};
}
}
import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));
Filtering date data type through code
This filtering option makes it simple to filter data based on date values in your fields, helping you quickly focus on records from specific time periods. This type of filtering is only available for fields that contain date data types and can be configured programmatically using the same approach as explained in the previous section “Filtering string data type through code”, with one key difference: set the type
property to Date.
To apply date filtering, specify your filtering criteria using the value1
and condition
properties. For example, if you have a “Delivery Date” field and want to show delivery records from before a specific date like “2019-01-07”, set the value1
property to “2019-01-07” and the condition
property to Before. This will display only the records with delivery dates before January 7, 2019 in your Pivot Table.
You can use the following operators with date data type filtering: Equals, DoesNotEquals, Before, BeforeOrEqualTo, After, AfterOrEqualTo, Between, and NotBetween.
Date filtering is available only when the field has date type
formatSettings
configured.
import { NgModule } from '@angular/core'
import { BrowserModule } from '@angular/platform-browser'
import { PivotViewAllModule, PivotFieldListAllModule } from '@syncfusion/ej2-angular-pivotview'
import { Component, OnInit } from '@angular/core';
import { IDataSet } from '@syncfusion/ej2-angular-pivotview';
import { Pivot_Data } from './datasource';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
@Component({
imports: [
PivotViewAllModule,
PivotFieldListAllModule
],
standalone: true,
selector: 'app-container',
// specifies the template string for the pivot table component
template: `<ejs-pivotview #pivotview id='PivotView' height='350' [dataSourceSettings]=dataSourceSettings [width]=width></ejs-pivotview>`
})
export class AppComponent implements OnInit {
public width?: string;
public dataSourceSettings?: DataSourceSettingsModel;
ngOnInit(): void {
this.width = '100%';
this.dataSourceSettings = {
dataSource: Pivot_Data as IDataSet[],
expandAll: false,
allowLabelFilter: true,
formatSettings: [{ name: 'Year', format: 'dd/MM/yyyy-hh:mm', type: 'date' }],
filterSettings: [{ name: 'Year', type: 'Date', condition: 'Before', value1: new Date('2016') }],
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
filters: []
};
}
}
import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));
Clearing the existing label filter
Users can clear the applied label filter by clicking the Clear option at the bottom of the filter dialog. This option is located under the Label tab for string and number type filtering, and under the Date tab for date type filtering.
Value filtering
Value filtering allows you to filter data based on aggregated values from measure fields, helping you focus on specific data ranges that meet your criteria.
You can enable value filtering by setting the allowValueFilter
property to true in the dataSourceSettings
. Once enabled, click the filter icon next to any field in the row or column axis within the field list or grouping bar. A filtering dialog will appear where you can navigate to the “Value” tab to perform value filtering operations.
You can also configure value filtering programmatically using the filterSettings
property. The following properties are required for value filtering:
-
name
: Specifies the field name to which the filter applies. -
type
: Sets the filter type as Value. -
measure
: Specifies the value field name used for filtering. -
condition
: Defines the comparison operator such as Equals, GreaterThan, or LessThan. -
value1
: Sets the comparison value or the start value for range operations. -
value2
: Sets the end value, applicable only for Between and NotBetween operators. -
selectedField
: Specifies the dimension level name where filter settings apply. Note: This property is only applicable for OLAP data sources.
For example, to display data where the total sum of units sold for each country exceeds 1500, set the value1
to “1500” and condition
to GreaterThan for the “Country” field.
The following table shows the available operators for value filtering:
Operator | Description |
---|---|
Equals | Shows records that match the specified value. |
DoesNotEquals | Shows records that do not match the specified value. |
GreaterThan | Shows records where the value is greater than the specified value. |
GreaterThanOrEqualTo | Shows records where the value is greater than or equal to the specified value. |
LessThan | Shows records where the value is less than the specified value. |
LessThanOrEqualTo | Shows records where the value is less than or equal to the specified value. |
Between | Shows records with values between the specified start and end values. |
NotBetween | Shows records with values outside the specified start and end values. |
import { NgModule } from '@angular/core'
import { BrowserModule } from '@angular/platform-browser'
import { PivotViewAllModule, PivotFieldListAllModule } from '@syncfusion/ej2-angular-pivotview'
import { Component, OnInit } from '@angular/core';
import { IDataSet } from '@syncfusion/ej2-angular-pivotview';
import { Pivot_Data } from './datasource';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
@Component({
imports: [
PivotViewAllModule,
PivotFieldListAllModule
],
standalone: true,
selector: 'app-container',
// specifies the template string for the pivot table component
template: `<ejs-pivotview #pivotview id='PivotView' height='350' [dataSourceSettings]=dataSourceSettings [width]=width></ejs-pivotview>`
})
export class AppComponent implements OnInit {
public width?: string;
public dataSourceSettings?: DataSourceSettingsModel;
ngOnInit(): void {
this.width = '100%';
this.dataSourceSettings = {
dataSource: Pivot_Data as IDataSet[],
expandAll: false,
allowValueFilter: true,
drilledMembers: [{ name: 'Country', items: ['France'] }],
filterSettings: [{ name: 'Country', measure: 'Sold', type: 'Value', condition: 'GreaterThan', value1: '2000' }],
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
filters: []
};
}
}
import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));
Clearing the existing value filter
You can clear the applied value filter by clicking the “Clear” option at the bottom of the filter dialog under the “Value” tab.
Event
MemberFiltering
The memberFiltering
event gives you complete control over filter operations by triggering before any filter is applied through the filter dialog. This event activates specifically when you click the “OK” button in the filter dialog, allowing you to review, modify, or cancel the filtering process based on your requirements.
This event provides access to the current filter settings, enabling you to customize filter behavior programmatically. You can examine the filter items, modify filter types and conditions, or prevent the filter from being applied entirely.
The event includes the following parameters:
-
cancel
- A boolean property that stops the filter from being applied when set to true. -
filterSettings
- Contains the current filter settings including filter items, types, and conditions. -
dataSourceSettings
- Holds the updated dataSourceSettings after the filter is applied.
For example, you can use the memberFiltering
event to block the filter action by setting args.cancel
parameter to true. This is shown below:
import { NgModule } from '@angular/core'
import { BrowserModule } from '@angular/platform-browser'
import { PivotViewAllModule, PivotFieldListAllModule } from '@syncfusion/ej2-angular-pivotview'
import { Component, OnInit, ViewChild } from '@angular/core';
import { PivotView, MemberFilteringEventArgs, IDataSet } from '@syncfusion/ej2-angular-pivotview';
import { GridSettings } from '@syncfusion/ej2-pivotview/src/pivotview/model/gridsettings';
import { Pivot_Data } from './datasource';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
@Component({
imports: [
PivotViewAllModule,
PivotFieldListAllModule
],
standalone: true,
selector: 'app-container',
// specifies the template string for the pivot table component
template: `<ejs-pivotview #pivotview id='PivotView' height='350' [dataSourceSettings]=dataSourceSettings
[gridSettings]='gridSettings' (memberFiltering)='memberFiltering($event)' [width]=width></ejs-pivotview>`
})
export class AppComponent implements OnInit {
public width?: string;
public dataSourceSettings?: DataSourceSettingsModel;
public gridSettings?: GridSettings;
public columnGrandTotalIndex?: number;
public rowGrandTotalIndex?: number;
@ViewChild('pivotview', { static: false })
public pivotGridObj?: PivotView;
memberFiltering(args: MemberFilteringEventArgs): void {
args.cancel = true;
}
ngOnInit(): void {
this.width = '100%';
this.dataSourceSettings = {
dataSource: Pivot_Data as IDataSet[],
expandAll: false,
drilledMembers: [{ name: 'Country', items: ['France'] }],
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
formatSettings: [{ name: 'Amount', format: 'C0' }],
filters: []
};
this.gridSettings = {
columnWidth: 140,
} as GridSettings;
}
}
import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));
MemberEditorOpen
When you open the Member Editor dialog, the memberEditorOpen
event is triggered. With this event, you can decide which field members are shown, making it easier to include or exclude specific items. The event provides the following options:
-
fieldName
: The name of the field for which the Member Editor dialog opens. -
fieldMembers
: The list of all members in the selected field. -
cancel
: If you set this property totrue
, the Member Editor dialog will not open. -
filterSettings
- Contains the currentfilterSettings
including filter items, types, and conditions.
Here’s an example. In the Pivot Table below, only the selected member for the “Country” field appears in the Member Editor dialog:
import { NgModule } from '@angular/core'
import { BrowserModule } from '@angular/platform-browser'
import { PivotViewAllModule, PivotFieldListAllModule } from '@syncfusion/ej2-angular-pivotview'
import { Component, OnInit, ViewChild } from '@angular/core';
import { MemberEditorOpenEventArgs, IDataSet, PivotView } from '@syncfusion/ej2-angular-pivotview';
import { Pivot_Data } from './datasource';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
@Component({
imports: [
PivotViewAllModule,
PivotFieldListAllModule
],
standalone: true,
selector: 'app-container',
// specifies the template string for the pivot table component
template: `<ejs-pivotview #pivotview id='PivotView' height='350' [dataSourceSettings]=dataSourceSettings (memberEditorOpen)='memberEditorOpen($event)' showGroupingBar='true' [width]=width></ejs-pivotview>`
})
export class AppComponent implements OnInit {
public width?: string;
public dataSourceSettings?: DataSourceSettingsModel;
@ViewChild('pivotview', { static: false })
public pivotGridObj?: PivotView;
memberEditorOpen(args: MemberEditorOpenEventArgs | any): void {
if (args.fieldName == 'Country') {
args.fieldMembers = args.fieldMembers.filter((key: any) => {
return (key.actualText == 'France' || key.actualText == 'Germany')
});
}
}
ngOnInit(): void {
this.width = '100%';
this.dataSourceSettings = {
dataSource: Pivot_Data as IDataSet[],
expandAll: false,
drilledMembers: [{ name: 'Country', items: ['France'] }],
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
formatSettings: [{ name: 'Amount', format: 'C0' }],
filters: []
};
}
}
import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));
ActionBegin
The actionBegin
event is triggered when a user clicks the filter icon on a field button in either the grouping bar or the field list, allowing users to monitor and control actions in the Pivot Table. The event argument includes the following properties:
-
dataSourceSettings
: Contains the current data source configuration, including input data, rows, columns, values, filters, format settings, and other report settings. -
actionName
: Indicates the name of the action being initiated, such as Filter field for filtering. -
fieldInfo
: Provides information about the selected field for the action.
Note: The
fieldInfo
property is available only when the action involves a specific field, such as filtering, sorting, removing a field from the grouping bar, editing, or changing the aggregation type.
-
cancel
: A boolean property that allows you to prevent the current action from completing. Set this to true to stop the action.
In the example below, you can prevent a filter action by setting args.cancel to true in the actionBegin
event:
import { NgModule } from '@angular/core'
import { BrowserModule } from '@angular/platform-browser'
import { PivotViewAllModule, PivotFieldListAllModule } from '@syncfusion/ej2-angular-pivotview'
import { Component, OnInit } from '@angular/core';
import { GroupingBarService, FieldListService, PivotActionBeginEventArgs, IDataSet } from '@syncfusion/ej2-angular-pivotview';
import { Pivot_Data } from './datasource';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
@Component({
imports: [
PivotViewAllModule,
PivotFieldListAllModule
],
standalone: true,
selector: 'app-container',
providers: [GroupingBarService, FieldListService],
// specifies the template string for the pivot table component
template: `<ejs-pivotview #pivotview id='PivotView' height='350' [dataSourceSettings]=dataSourceSettings showGroupingBar='true' showFieldList='true' (actionBegin)='actionBegin($event)' [width]=width></ejs-pivotview>`
})
export class AppComponent implements OnInit {
public width?: string;
public dataSourceSettings?: DataSourceSettingsModel;
actionBegin(args: PivotActionBeginEventArgs): void {
if (args.actionName == 'Filter field') {
args.cancel = true;
}
}
ngOnInit(): void {
this.width = "100%";
this.dataSourceSettings = {
dataSource: Pivot_Data as IDataSet[],
expandAll: false,
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }],
rows: [{ name: 'Country' }, { name: 'Products' , showFilterIcon: false }],
formatSettings: [{ name: 'Amount', format: 'C0' }],
filters: []
};
}
}
import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));
ActionComplete
The actionComplete
event triggers when filtering actions are completed through the field button in both the grouping bar and field list UI. You can use this event to monitor current UI actions and implement custom logic based on the completed operations.
The event provides the following parameters:
-
dataSourceSettings
: Contains the current data source configuration, including input data source, rows, columns, values, filters, format settings, and other report settings. -
actionName
: Specifies the name of the completed action. For filtering operations, the action name appears as Field filtered. -
fieldInfo
: Contains information about the selected field that was involved in the action.
Note: This parameter is available only when the action involves a specific field, such as filtering, sorting, removing a field from the grouping bar, editing, or changing the aggregation type.
-
actionInfo
: Provides detailed information about the current UI action. For filtering operations, this includes filter members, field name, and other relevant details.
import { NgModule } from '@angular/core'
import { BrowserModule } from '@angular/platform-browser'
import { PivotViewAllModule, PivotFieldListAllModule } from '@syncfusion/ej2-angular-pivotview'
import { Component, OnInit } from '@angular/core';
import { IDataSet, GroupingBarService, FieldListService, PivotActionCompleteEventArgs } from '@syncfusion/ej2-angular-pivotview';
import { Pivot_Data } from './datasource';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
@Component({
imports: [
PivotViewAllModule,
PivotFieldListAllModule
],
standalone: true,
selector: 'app-container',
providers: [GroupingBarService, FieldListService],
// specifies the template string for the pivot table component
template: `<ejs-pivotview #pivotview id='PivotView' height='350' [dataSourceSettings]=dataSourceSettings showGroupingBar='true' showFieldList='true' (actionComplete)='actionComplete($event)' [width]=width></ejs-pivotview>`
})
export class AppComponent implements OnInit {
public width?: string;
public dataSourceSettings?: DataSourceSettingsModel;
actionComplete(args: PivotActionCompleteEventArgs): void {
if (args.actionName == 'Field filtered') {
// Triggers when the filter action is completed.
}
}
ngOnInit(): void {
this.width = "100%";
this.dataSourceSettings = {
dataSource: Pivot_Data as IDataSet[],
expandAll: false,
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }],
rows: [{ name: 'Country' }, { name: 'Products' , showFilterIcon: false }],
formatSettings: [{ name: 'Amount', format: 'C0' }],
filters: []
};
}
}
import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));
ActionFailure
The actionFailure
event is triggered when a UI action fails to produce the expected result. This event provides detailed information about the failure through the following parameters:
-
actionName
: It holds the name of the current action failed. For example, if the action fails while filtering, the action name will be shown as Filter field. -
errorInfo
: It holds the error information of the current UI action.
import { NgModule } from '@angular/core'
import { BrowserModule } from '@angular/platform-browser'
import { PivotViewAllModule, PivotFieldListAllModule } from '@syncfusion/ej2-angular-pivotview'
import { Component, OnInit } from '@angular/core';
import { IDataSet, GroupingBarService, FieldListService, PivotActionFailureEventArgs } from '@syncfusion/ej2-angular-pivotview';
import { Pivot_Data } from './datasource';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
@Component({
imports: [
PivotViewAllModule,
PivotFieldListAllModule
],
standalone: true,
selector: 'app-container',
providers: [GroupingBarService, FieldListService],
// specifies the template string for the pivot table component
template: `<ejs-pivotview #pivotview id='PivotView' height='350' [dataSourceSettings]=dataSourceSettings showGroupingBar='true' showFieldList='true' (actionFailure)='actionFailure($event)' [width]=width></ejs-pivotview>`
})
export class AppComponent implements OnInit {
public width?: string;
public dataSourceSettings?: DataSourceSettingsModel;
actionFailure(args: PivotActionFailureEventArgs): void {
if (args.actionName == 'Filter field') {
// Triggers when the current UI action fails to achieve the desired result.
}
}
ngOnInit(): void {
this.width = "100%";
this.dataSourceSettings = {
dataSource: Pivot_Data as IDataSet[],
expandAll: false,
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }],
rows: [{ name: 'Country' }, { name: 'Products' , showFilterIcon: false }],
formatSettings: [{ name: 'Amount', format: 'C0' }],
filters: []
};
}
}
import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));