Member filtering in React Pivotview component
30 Jan 202323 minutes to read
Member filtering allows you to view pivot table with particular records based on filter criteria. You can disable the member filter by setting the allowMemberFilter
property to false. By default, this property is set as true.
Filtering through UI
Filtering can also be performed through UI option available in grouping bar
and field list
at run time.
Filtering through code
It can be configured using the filterSettings
option through code-behind. The settings required to filter at initial rendering are:
-
name
: It allows to set field name. -
type
: It allows to set filter type as either “Include” or “Exclude” to the field. -
items
: It allows to set the filter members of the field.
import { PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { pivotData } from './datasource';
function App() {
let dataSourceSettings = {
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
dataSource: pivotData,
expandAll: false,
filterSettings: [{ name: 'Country', type: 'Exclude', items: ['United States'] }],
filters: [],
drilledMembers: [{ name: 'Country', items: ['France'] }],
formatSettings: [{ name: 'Amount', format: 'C0' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }]
};
let pivotObj;
return (<PivotViewComponent ref={d => pivotObj = d} id='PivotView' height={350} dataSourceSettings={dataSourceSettings}></PivotViewComponent>);
}
;
export default App;
ReactDOM.render(<App />, document.getElementById('sample'));
import { IDataOptions, IDataSet, PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { pivotData } from './datasource';
function App() {
let dataSourceSettings: IDataOptions = {
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
dataSource: pivotData as IDataSet[],
expandAll: false,
filterSettings: [{ name: 'Country', type: 'Exclude', items: ['United States'] }],
filters: [],
drilledMembers: [{ name: 'Country', items: ['France'] }],
formatSettings: [{ name: 'Amount', format: 'C0' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }]
}
let pivotObj: PivotViewComponent;
return (<PivotViewComponent ref={d => pivotObj = d} id='PivotView' height={350} dataSourceSettings={dataSourceSettings}></PivotViewComponent>);
};
export default App;
ReactDOM.render(<App />, document.getElementById('sample'));
On demand member loading
This feature is applicable only for the OLAP data source.
Users can load members on demand by setting the loadOnDemandInMemberEditor
property to true. By default, one level is loaded in the member editor. So, the initial loading will be completed quickly, without any performance constraints. After that, based on the user requirement, members can be loaded on demand. By default this property is set to true. Search will be applied to only the level elements that are loaded.
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { CalculatedField, FieldList, Inject, PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
function App() {
let 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' },
{ name: 'Order on Discount', isCalculatedField: true }
],
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'
}
]
};
let pivotObj;
return (<PivotViewComponent ref={d => pivotObj = d} id='PivotView' height={350} dataSourceSettings={dataSourceSettings} allowCalculatedField={true} showFieldList={true} loadOnDemandInMemberEditor={true}><Inject services={[CalculatedField, FieldList]}/>
</PivotViewComponent>);
}
;
export default App;
ReactDOM.render(<App />, document.getElementById('sample'));
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { CalculatedField, FieldList, IDataOptions, IDataSet, Inject, PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
function App() {
let dataSourceSettings: IDataOptions = {
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' },
{ name: 'Order on Discount', isCalculatedField: true }
],
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'
}
]
};
let pivotObj: PivotViewComponent;
return (<PivotViewComponent ref={d => pivotObj = d} id='PivotView' height={350} dataSourceSettings={dataSourceSettings} allowCalculatedField={true} showFieldList={true} loadOnDemandInMemberEditor={true}><Inject services={[CalculatedField, FieldList]}/>
</PivotViewComponent>);
};
export default App;
ReactDOM.render(<App />, document.getElementById('sample'));
If the property is set to false, all members will be loaded for all levels at initial loading. So, it will take time to fetch the members at initial loading alone. After that, all operations (like drill, search) are performed using existing members without any performance constraints.
Loading members based on level count
This feature is applicable only for the OLAP data source.
User can load the members based on the level count by using the property levelCount
in the filterSettings
. By default this property is set to 1. Search will be applied to only the level elements that are loaded.
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { CalculatedField, FieldList, Inject, PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
function App() {
let 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' },
{ name: 'Order on Discount', isCalculatedField: true }
],
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: '[Date].[Fiscal]', items: ['[Date].[Fiscal].[Fiscal Quarter].&[2002]&[4]',
'[Date].[Fiscal].[Fiscal Year].&[2005]'],
levelCount: 3
}
]
};
let pivotObj;
return (<PivotViewComponent ref={d => pivotObj = d} id='PivotView' height={350} dataSourceSettings={dataSourceSettings} allowCalculatedField={true} showFieldList={true}><Inject services={[CalculatedField, FieldList]}/>
</PivotViewComponent>);
}
;
export default App;
ReactDOM.render(<App />, document.getElementById('sample'));
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { CalculatedField, FieldList, IDataOptions, IDataSet, Inject, PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
function App() {
let dataSourceSettings: IDataOptions = {
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' },
{ name: 'Order on Discount', isCalculatedField: true }
],
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: '[Date].[Fiscal]', items: ['[Date].[Fiscal].[Fiscal Quarter].&[2002]&[4]',
'[Date].[Fiscal].[Fiscal Year].&[2005]'],
levelCount: 3
}
]
};
let pivotObj: PivotViewComponent;
return (<PivotViewComponent ref={d => pivotObj = d} id='PivotView' height={350} dataSourceSettings={dataSourceSettings} allowCalculatedField={true} showFieldList={true}><Inject services={[CalculatedField, FieldList]}/>
</PivotViewComponent>);
};
export default App;
ReactDOM.render(<App />, document.getElementById('sample'));