Search results

Filtering

Member filtering

Allows to view the pivot table with selective records based on included and excluded members in each field. The filter icons are enabled by default, besides each field arranged in both field list and grouping bar UI. This UI option helps end user to filter members of specific fields at runtime.

output
output
output
output

Meanwhile filtering can also be configured at code behind using the PivotViewFilterSettings class while initial rendering of the component. The basic settings required to add filter criteria are:

  • Name: It allows to set the appropriate field name.
  • Type: It allows to set the filter type as FilterType.Include or FilterType.Exclude to include or exclude field members respectively.
  • Items: It allows to set the members which needs to be either included or excluded from display.
    @using Syncfusion.EJ2.Blazor.PivotView

    <EjsPivotView TValue="ProductDetails" ShowGroupingBar="true">
         <PivotViewDataSourceSettings DataSource="@data">
            <PivotViewColumns>
                <PivotViewColumn Name="Year"></PivotViewColumn>
                <PivotViewColumn Name="Quarter"></PivotViewColumn>
            </PivotViewColumns>
            <PivotViewRows>
                <PivotViewRow Name="Country"></PivotViewRow>
                <PivotViewRow Name="Products"></PivotViewRow>
            </PivotViewRows>
            <PivotViewValues>
                <PivotViewValue Name="Sold" Caption="Unit Sold"></PivotViewValue>
                <PivotViewValue Name="Amount" Caption="Sold Amount"></PivotViewValue>
            </PivotViewValues>
            <PivotViewFormatSettings>
                <PivotViewFormatSetting Name="Amount" Format="C0" UseGrouping="true"></PivotViewFormatSetting>
            </PivotViewFormatSettings>
            <PivotViewFilterSettings>
                <PivotViewFilterSetting Name="Year" Type=FilterType.Exclude Items="@(new string[] { "FY 2017" })">
            </PivotViewFilterSetting>
            </PivotViewFilterSettings>
        </PivotViewDataSourceSettings>
    </EjsPivotView>

    @code{
        public List<ProductDetails> data { get; set; }
        protected override void OnInitialized()
        {
            this.data = ProductDetails.GetProductData().ToList();
           //Bind the data source collection here. Refer "Assigning sample data to the pivot table" section in getting started for more details.
        }
    }

output

Option to select and unselect all members

The member filter dialog comes with an option “All”, which on checked selects all members and on unchecked deselects all members. The option “All” would appear in intermediate state mentioning that both selected and unselected child members are available.

output

When all members are deselected, the “Ok” button in member filter dialog would be disabled, meaning, at least one member should be selected and bound to the pivot table component.

output

Provision to search specific member(s)

By default, search option is available to quickly navigate to the desired members. It can be done by entering the starting character(s) of the actual members.

output

Performance Tips

In member filter dialog, end user can set the limit to display members while loading large data. Based on this limit, initial loading will get completed quickly without any performance constraint. Also, a message with remaining member count, which are not part of the UI, will be displayed in the member editor.

The data limit can be set using the MaxNodeLimitInMemberEditor property in EjsPivotView class. By default, the property holds the numeric value 1000.

    @using Syncfusion.EJ2.Blazor.PivotView

<EjsPivotView TValue="ProductDetails" ShowFieldList="true" EnableVirtualization="true" MaxNodeLimitInMemberEditor="500">
    <PivotViewDataSourceSettings DataSource="@data">
        <PivotViewColumns>
            <PivotViewColumn Name="DeliveryDate"></PivotViewColumn>
        </PivotViewColumns>
        <PivotViewRows>
            <PivotViewRow Name="ProductID"></PivotViewRow>
        </PivotViewRows>
        <PivotViewValues>
            <PivotViewValue Name="Sold" Caption="Units Sold"></PivotViewValue>
        </PivotViewValues>
    </PivotViewDataSourceSettings>
</EjsPivotView>

    @code{
        public List<ProductDetails> data { get; set; }
        protected override void OnInitialized()
        {
            this.data = ProductDetails.GetProductData().ToList();
        }

        public class ProductDetails
        {
            public string ProductID { get; set; }
            public int Sold { get; set; }
            public DateTime DeliveryDate { get; set; }
            public static List<ProductDetails> GetProductData()
            {
                List<ProductDetails> productData = new List<ProductDetails>();
                for (int i = 0; i < 5000; i++)
                {
                    int RandomNumber = new Random().Next(1, 10);
                    productData.Add(new ProductDetails { Sold = RandomNumber, ProductID = "PRO-" + (i+1001), DeliveryDate = new DateTime(2019, 1, 1).AddDays(RandomNumber) });
                }
                return productData;
            }
        }
    }

output

Meanwhile, end user can utilize the search option to refine the members from the exceeded limit. For example, consider that there are 5000 members in the name “Node 1”, “Node 2”, “Node 3”, and so on… and user has set the property MaxNodeLimitInMemberEditor to 500. In this case, only the initial 500 members will be displayed by default leaving a message “4500 more items. Search to refine further.”. To get the member(s) between 501 to 5000, enter the starting character(s) in search option to bring the desired member(s) from the exceeded limit to the UI. Now, end user can either check or uncheck to continue with the filtering process.

output

Label filtering

The label filtering helps to view the pivot table with selective header text in fields across row and column axes based on the applied filter criteria. The following are the three different types of label filtering available:

  • Filtering string data type
  • Filtering number data type
  • Filtering date data type

The label filtering dialog can be enabled by setting the AllowLabelFilter property in PivotViewDataSourceSettings class to true. After enabling this API, click the filter icon besides any field in row or column axis available in field list or grouping bar UI. Now a filtering dialog will appear and navigate to “Label” tab to perform label filtering operations.

    @using Syncfusion.EJ2.Blazor.PivotView

    <EjsPivotView TValue="ProductDetails" ShowGroupingBar="true" ShowFieldList="true">
         <PivotViewDataSourceSettings DataSource="@data" AllowLabelFilter="true">
            <PivotViewColumns>
                <PivotViewColumn Name="Year"></PivotViewColumn>
                <PivotViewColumn Name="Quarter"></PivotViewColumn>
            </PivotViewColumns>
            <PivotViewRows>
                <PivotViewRow Name="Country"></PivotViewRow>
                <PivotViewRow Name="Products"></PivotViewRow>
            </PivotViewRows>
            <PivotViewValues>
                <PivotViewValue Name="Sold" Caption="Unit Sold"></PivotViewValue>
                <PivotViewValue Name="Amount" Caption="Sold Amount"></PivotViewValue>
            </PivotViewValues>
            <PivotViewFormatSettings>
                <PivotViewFormatSetting Name="Amount" Format="C"></PivotViewFormatSetting>
            </PivotViewFormatSettings>
        </PivotViewDataSourceSettings>
    </EjsPivotView>

    @code{
        public List<ProductDetails> data { get; set; }
        protected override void OnInitialized()
        {
            this.data = ProductDetails.GetProductData().ToList();
           //Bind the data source collection here. Refer "Assigning sample data to the pivot table" section in getting started for more details.
        }
    }

output
output

output

output

In label filtering UI, based on the field chosen, it’s member data type is automatically recognized and filtering operation will be carried out. Where as in code behind, user need to define the data type through a property and it has been explained in the immediate section below.

Filtering string data type through code

This type of filtering is exclusively applicable for fields with members in string data type. The filtering can be configured using the PivotViewFilterSettings class through code-behind. The properties required for label filter are:

For example, in a “Country” field, to show countries names that contains “United” text alone, set Value1 to “United” and Condition to Operators.Contains for desired output in pivot table. Operators that can be used in label filtering are:

Operator Description
Equals Displays the pivot table that matches with the text.
DoesNotEquals Displays the pivot table that does not match with the given text.
BeginWith Displays the pivot table that begins with text.
DoesNotBeginWith Displays the pivot table that does not begins with text.
EndsWith Displays the pivot table that ends with text.
DoesNotEndsWith Displays the pivot table that does not ends with text.
Contains Displays the pivot table that contains text.
DoesNotContains Displays the pivot table that does not contain text.
GreaterThan Displays the pivot table when the text is greater.
GreaterThanOrEqualTo Displays the pivot table when the text is greater than or equal.
LessThan Displays the pivot table when the text is lesser.
LessThanOrEqualTo Displays the pivot table when the text is lesser than or equal.
Between Displays the pivot table that records between the start and end text.
NotBetween Displays the pivot table that does not record between the start and end text.
    @using Syncfusion.EJ2.Blazor.PivotView

    <EjsPivotView TValue="ProductDetails">
         <PivotViewDataSourceSettings DataSource="@data" AllowLabelFilter="true">
            <PivotViewColumns>
                <PivotViewColumn Name="Year"></PivotViewColumn>
                <PivotViewColumn Name="Quarter"></PivotViewColumn>
            </PivotViewColumns>
            <PivotViewRows>
                <PivotViewRow Name="Country"></PivotViewRow>
                <PivotViewRow Name="Products"></PivotViewRow>
            </PivotViewRows>
            <PivotViewValues>
                <PivotViewValue Name="Sold" Caption="Unit Sold"></PivotViewValue>
                <PivotViewValue Name="Amount" Caption="Sold Amount"></PivotViewValue>
            </PivotViewValues>
            <PivotViewFormatSettings>
                <PivotViewFormatSetting Name="Amount" Format="C"></PivotViewFormatSetting>
            </PivotViewFormatSettings>
            <PivotViewFilterSettings>
                <PivotViewFilterSetting Name="Country" Type=FilterType.Label Condition=Operators.Contains Value1="United">
            </PivotViewFilterSetting>
            </PivotViewFilterSettings>
        </PivotViewDataSourceSettings>
    </EjsPivotView>

    @code{
        public List<ProductDetails> data { get; set; }
        protected override void OnInitialized()
        {
            this.data = ProductDetails.GetProductData().ToList();
           //Bind the data source collection here. Refer "Assigning sample data to the pivot table" section in getting started for more details.
        }
    }

output

Filtering number data type through code

This type of filtering is exclusively applicable for fields with members in number data type. The filtering can be configured in a similar way explained in the previous section - “Filtering string data type through code”, except the Type property setting. For number data type, set the Type property to FilterType.Number enumeration.

For example, in a “Sold” field, to show the values between “90” to “100”, set Value1 to “90”, Value1 to “100” and Condition to Operators.Between for desired output in pivot table.

Operators like Operators.Equals, Operators.DoesNotEquals, Operators.GreaterThan, Operators.GreaterThanOrEqualTo, Operators.LessThan, Operators.LessThanOrEqualTo, Operators.Between, and Operators.NotBetween are alone applicable for number data type.

    @using Syncfusion.EJ2.Blazor.PivotView

    <EjsPivotView TValue="ProductDetails">
        <PivotViewDataSourceSettings DataSource="@data" AllowLabelFilter="true">
            <PivotViewColumns>
                <PivotViewColumn Name="Year"></PivotViewColumn>
                <PivotViewColumn Name="Quarter"></PivotViewColumn>
            </PivotViewColumns>
            <PivotViewRows>
                <PivotViewRow Name="Sold" Caption="Unit Sold"></PivotViewRow>
                <PivotViewRow Name="Country"></PivotViewRow>
                <PivotViewRow Name="Products"></PivotViewRow>
            </PivotViewRows>
            <PivotViewValues>
                <PivotViewValue Name="Amount" Caption="Sold Amount"></PivotViewValue>
            </PivotViewValues>
            <PivotViewFormatSettings>
                <PivotViewFormatSetting Name="Amount" Format="C"></PivotViewFormatSetting>
            </PivotViewFormatSettings>
            <PivotViewFilterSettings>
                <PivotViewFilterSetting Name="Sold" Type=FilterType.Number Condition=Operators.Between Value1=90 Value2=100></PivotViewFilterSetting>
            </PivotViewFilterSettings>
        </PivotViewDataSourceSettings>
    </EjsPivotView>

    @code{
        public List<ProductDetails> data { get; set; }
        protected override void OnInitialized()
        {
            this.data = ProductDetails.GetProductData().ToList();
           //Bind the data source collection here. Refer "Assigning sample data to the pivot table" section in getting started for more details.
        }
    }

output

Filtering date data type through code

This type of filtering is exclusively applicable for fields with members in date data type. The filtering can be configured in a similar way explained in the prior section - “Filtering string data type through code”, except the Type property setting. For date data type, set the Type property to FilterType.Date enumeration.

For example, in a “Delivery Date” field, to show the delivery records of the first week of the year 2019, then set Value1 to “2019-01-07” and Condition to Operators.Before for desired output in pivot table.

Operators like Operators.Equals, Operators.DoesNotEquals, Operators.Before, Operators.BeforeOrEqualTo, Operators.After, Operators.AfterOrEqualTo, Operators.Between, and Operators.NotBetween are alone applicable for date data type.

    @using Syncfusion.EJ2.Blazor.PivotView

    <EjsPivotView TValue="ProductDetails">
    <PivotViewDataSourceSettings DataSource="@data" AllowLabelFilter="true">
        <PivotViewColumns>
            <PivotViewColumn Name="ProductID"></PivotViewColumn>
        </PivotViewColumns>
        <PivotViewRows>
            <PivotViewRow Name="DeliveryDate"></PivotViewRow>
        </PivotViewRows>
        <PivotViewValues>
            <PivotViewValue Name="Sold" Caption="Units Sold"></PivotViewValue>
        </PivotViewValues>
        <PivotViewFormatSettings>
            <PivotViewFormatSetting Name="DeliveryDate" Type="date" Format="dd/MMM/yyyy"></PivotViewFormatSetting>
        </PivotViewFormatSettings>
        <PivotViewFilterSettings>
            <PivotViewFilterSetting Name="DeliveryDate" Type=FilterType.Date Condition=Operators.Before Value1="2019-01-07"></PivotViewFilterSetting>
        </PivotViewFilterSettings>
    </PivotViewDataSourceSettings>
    </EjsPivotView>

    @code{
        public List<ProductDetails> data { get; set; }
        protected override void OnInitialized()
        {
            this.data = ProductDetails.GetProductData().ToList();
        }

        public class ProductDetails
        {
            public string ProductID { get; set; }
            public int Sold { get; set; }
            public DateTime DeliveryDate { get; set; }
            public static List<ProductDetails> GetProductData()
            {
                List<ProductDetails> productData = new List<ProductDetails>();
                for (int i = 0; i < 100; i++)
                {
                    int RandomNumber = new Random().Next(1, 10);
                    productData.Add(new ProductDetails { Sold = RandomNumber, ProductID = "PRO-" + ((i % 10)+1001), DeliveryDate = new DateTime(2019, 1, 1).AddDays(RandomNumber) });
                }
                return productData;
            }
        }
    }

output

Clearing the existing label filter

End user can clear the applied label filter by simply click the “Clear” option at the bottom of the filter dialog under “Label” tab.

output

Value filtering

The value filtering helps to perform filter operation based only on value fields and its resultant aggregated values over other fields defined in row and column axes.

The value filtering dialog can be enabled by setting the AllowValueFilter property in PivotViewDataSourceSettings class to true. After enabling this API, click the filter icon besides any field in row or column axis available in field list or grouping bar UI. Now a filtering dialog will appear and navigate to “Value” tab to perform value filtering operations.

    @using Syncfusion.EJ2.Blazor.PivotView

    <EjsPivotView TValue="ProductDetails" ShowFieldList="true">
         <PivotViewDataSourceSettings DataSource="@data" AllowValueFilter="true">
            <PivotViewColumns>
                <PivotViewColumn Name="Year"></PivotViewColumn>
                <PivotViewColumn Name="Quarter"></PivotViewColumn>
            </PivotViewColumns>
            <PivotViewRows>
                <PivotViewRow Name="Country"></PivotViewRow>
                <PivotViewRow Name="Products"></PivotViewRow>
            </PivotViewRows>
            <PivotViewValues>
                <PivotViewValue Name="Sold" Caption="Unit Sold"></PivotViewValue>
                <PivotViewValue Name="Amount" Caption="Sold Amount"></PivotViewValue>
            </PivotViewValues>
            <PivotViewFormatSettings>
                <PivotViewFormatSetting Name="Amount" Format="C"></PivotViewFormatSetting>
            </PivotViewFormatSettings>
        </PivotViewDataSourceSettings>
    </EjsPivotView>

    @code{
        public List<ProductDetails> data { get; set; }
        protected override void OnInitialized()
        {
            this.data = ProductDetails.GetProductData().ToList();
           //Bind the data source collection here. Refer "Assigning sample data to the pivot table" section in getting started for more details.
        }
    }

output
output

output

output

The value filtering can also be configured using the PivotViewFilterSettings class through code-behind. The properties required for value filter are:

For example, to show the data where total sum of units sold for each country exceeding 1500, set Value1 to “1500” and Condition to Operators.GreaterThan in the “Country” field.

Operators that can be used in value filtering are:

Operator Description
Equals Displays the pivot table that matches with the value.
DoesNotEquals Displays the pivot table that does not match with the given value.
GreaterThan Displays the pivot table when the value is greater.
GreaterThanOrEqualTo Displays the pivot table when the value is greater than or equal.
LessThan Displays the pivot table when the value is lesser.
LessThanOrEqualTo Displays the pivot table when the value is lesser than or equal.
Between Displays the pivot table that records between start and end values.
NotBetween Displays the pivot table that does not record between start and end values.
    @using Syncfusion.EJ2.Blazor.PivotView

    <EjsPivotView TValue="ProductDetails" ShowGroupingBar="true">
         <PivotViewDataSourceSettings DataSource="@data" AllowValueFilter="true">
            <PivotViewColumns>
                <PivotViewColumn Name="Year"></PivotViewColumn>
                <PivotViewColumn Name="Quarter"></PivotViewColumn>
            </PivotViewColumns>
            <PivotViewRows>
                <PivotViewRow Name="Country"></PivotViewRow>
                <PivotViewRow Name="Products"></PivotViewRow>
            </PivotViewRows>
            <PivotViewValues>
                <PivotViewValue Name="Sold" Caption="Unit Sold"></PivotViewValue>
                <PivotViewValue Name="Amount" Caption="Sold Amount"></PivotViewValue>
            </PivotViewValues>
            <PivotViewFormatSettings>
                <PivotViewFormatSetting Name="Amount" Format="C"></PivotViewFormatSetting>
            </PivotViewFormatSettings>
            <PivotViewFilterSettings>
                <PivotViewFilterSetting Name="Country" Measure="Sold" Type=FilterType.Value Condition=Operators.GreaterThan Value1=1500></PivotViewFilterSetting>
            </PivotViewFilterSettings>
        </PivotViewDataSourceSettings>
    </EjsPivotView>

    @code{
        public List<ProductDetails> data { get; set; }
        protected override void OnInitialized()
        {
            this.data = ProductDetails.GetProductData().ToList();
           //Bind the data source collection here. Refer "Assigning sample data to the pivot table" section in getting started for more details.
        }
    }

output

Clearing the existing value filter

End user can clear the applied value filter by simply click the “Clear” option at the bottom of the filter dialog under “Value” tab.

output