HelpBot Assistant

How can I help you?

Drill through in ASP.NET Core Pivot Table component

26 Feb 202613 minutes to read

The drill-through feature in the Pivot Table component allows users to view the raw, unaggregated data behind any aggregated cell in the Pivot Table. To enable this feature, set the allowDrillThrough property to true. By double-clicking an aggregated cell, users can view its detailed raw data in a data grid displayed in a new window. The new window shows the row header, column header, and measure name of the selected cell at the top. Additionally, users can include or exclude fields available in the data grid using the column chooser option.

To use the drill-through feature, inject the DrillThrough module in the Pivot Table.

Below is an example of enabling drill-through in a Pivot Table:

<ejs-pivotview id="PivotView" height="300" allowDrillThrough="true" showTooltip="false">
    <e-datasourcesettings dataSource="@ViewBag.DataSource" expandAll="false" enableSorting="true">
        <e-formatsettings>
            <e-field name="Amount" format="C0" maximumSignificantDigits="10" minimumSignificantDigits="1" useGrouping="true"></e-field>
        </e-formatsettings>
        <e-rows>
            <e-field name="Country"></e-field>
            <e-field name="Products"></e-field>
        </e-rows>
        <e-columns>
            <e-field name="Year" caption="Year"></e-field>
            <e-field name="Quarter"></e-field>
        </e-columns>
        <e-values>
            <e-field name="Sold" caption="Units Sold"></e-field>
            <e-field name="Amount" caption="Sold Amount"></e-field>
        </e-values>
    </e-datasourcesettings>
</ejs-pivotview>
public ActionResult Index()
{
    var data = GetPivotData();
    ViewBag.DataSource = data;
    return View();
}

Pivot Table drill-through before interaction




Pivot Table drill-through after interaction

Users can also access drill-through data through the pivot chart. By clicking on any data point in the pivot chart, they can view the raw data in a data grid displayed in a new window.

Below is an example of enabling drill-through with a pivot chart:

<ejs-pivotview id="PivotView" height="300" allowDrillThrough="true" showTooltip="false">
    <e-datasourcesettings dataSource="@ViewBag.DataSource" expandAll="false" enableSorting="true">
        <e-formatsettings>
            <e-field name="Amount" format="C0" maximumSignificantDigits="10" minimumSignificantDigits="1" useGrouping="true"></e-field>
        </e-formatsettings>
        <e-rows>
            <e-field name="Country"></e-field>
            <e-field name="Products"></e-field>
        </e-rows>
        <e-columns>
            <e-field name="Year" caption="Year"></e-field>
            <e-field name="Quarter"></e-field>
        </e-columns>
        <e-values>
            <e-field name="Sold" caption="Units Sold"></e-field>
            <e-field name="Amount" caption="Sold Amount"></e-field>
        </e-values>
    </e-datasourcesettings>
    <e-chartSettings>
        <e-chartSeries type="Column"></e-chartSeries>
    </e-chartSettings>
    <e-displayOption view="Chart"></e-displayOption>
</ejs-pivotview>
public ActionResult Index()
{
    var data = GetPivotData();
    ViewBag.DataSource = data;
    return View();
}

Pivot chart drill-through before interaction




Pivot chart drill-through after interaction

Maximum rows to retrieve

This property is applicable only for the OLAP data source.

The maxRowsInDrillThrough property specifies the maximum number of rows to be returned during a drill-through operation. By default, this property is set to “10000”, meaning that if it is not explicitly defined, up to 10,000 rows will be returned.

<ejs-pivotview id="pivotview" allowCalculatedField="true" showFieldList="true" showGroupingBar="true" width="100%" height="600" allowDrillThrough="true" maxRowsInDrillThrough="10">
    <e-datasourcesettings catalog="Adventure Works DW 2008 SE" cube="Adventure Works" url="https://bi.syncfusion.com/olap/msmdpump.dll" providerType="SSAS" enableSorting="true">
        <e-rows>
            <e-field name="[Customer].[Customer Geography]" caption="Customer Geography"></e-field>
        </e-rows>
        <e-columns>
            <e-field name="[Product].[Product Categories]" caption="Product Categories"></e-field>
            <e-field name="[Measures]" caption="Measures"></e-field>
        </e-columns>
        <e-values>
            <e-field name="[Measures].[Customer Count]" caption="Customer Count"></e-field>
            <e-field name="[Measures].[Internet Sales Amount]" caption="Internet Sales Amount"></e-field>
            <e-field name="Order on Discount" isCalculatedField="true"></e-field>
        </e-values>
        <e-filters>
            <e-field name="[Date].[Fiscal]" caption="Date Fiscal"></e-field>
        </e-filters>
        <e-filtersettings>
            <e-field name="[Date].[Fiscal]" items="@ViewBag.filterMembers" levelCount=3></e-field>
        </e-filtersettings>
        <e-calculatedfieldsettings>
            <e-field name="BikeAndComponents" formula="([Product].[Product Categories].[Category].[Bikes] + [Product].[Product Categories].[Category].[Components])" hierarchyUniqueName="[Product].[Product Categories]" formatString="Standard"></e-field>
            <e-field name="Order on Discount" formula="[Measures].[Order Quantity] + ([Measures].[Order Quantity] * 0.10)" formatString="Currency"></e-field>
        </e-calculatedfieldsettings>
    </e-datasourcesettings>
</ejs-pivotview>
public ActionResult Index()
{
    return View();
}

Maximum rows drill-through

Events

DrillThrough

The drillThrough event is triggered immediately after a user double-clicks a value cell in the Pivot Table. This event allows users to customize the columns displayed in the drill-through popup’s data grid. It is specifically designed to help users view and process the raw data behind an aggregated value in a value cell. The event includes the following parameters:

  • columnHeaders: Contains the column header of the clicked cell.
  • currentCell: Contains details about the clicked cell.
  • currentTarget: Contains the HTML element of the clicked cell.
  • gridColumns: Specifies the data grid columns to be displayed in the drill-through popup.
  • rawData: Contains the raw, unaggregated data for the clicked cell.
  • rowHeaders: Contains the row header of the clicked cell.
  • value: Contains the value of the clicked cell.
  • cancel: It is a boolean property and by setting this to true, dialog won’t be created.

Below is an example of using the drillThrough event in a Pivot Table:

<ejs-pivotview id="PivotView" height="300" allowDrillThrough="true" drillThrough="drillThrough" showTooltip="false">
    <e-datasourcesettings dataSource="@ViewBag.DataSource" expandAll="false" enableSorting="true">
        <e-formatsettings>
            <e-field name="Amount" format="C0" maximumSignificantDigits="10" minimumSignificantDigits="1" useGrouping="true"></e-field>
        </e-formatsettings>
        <e-rows>
            <e-field name="Country"></e-field>
            <e-field name="Products"></e-field>
        </e-rows>
        <e-columns>
            <e-field name="Year" caption="Year"></e-field>
            <e-field name="Quarter"></e-field>
        </e-columns>
        <e-values>
            <e-field name="Sold" caption="Units Sold"></e-field>
            <e-field name="Amount" caption="Sold Amount"></e-field>
        </e-values>
    </e-datasourcesettings>
</ejs-pivotview>

<script>
    function drillThrough(args) {
        //triggers on value cell double click
    }
</script>
public ActionResult Index()
{
    var data = GetPivotData();
    ViewBag.DataSource = data;
    return View();
}

BeginDrillThrough

The event beginDrillThrough triggers after a double-click on a value cell in the Pivot Table and fires right after the data grid is initialized in the drill-through popup. This event allows users to interact with the data grid, which displays the raw data behind the aggregated value cell. Users can perform operations such as sorting, grouping, and filtering within the data grid according to their specific needs. The event includes the following parameters:

  • gridObj - It holds the data grid instance to be rendered inside the drill-through popup.
  • cellInfo - Gives details about the clicked cell, including rawData (unaggregated data), rowHeaders, columnHeaders, and value.

The following example demonstrates how to enable sorting, filtering, and grouping in the data grid displayed within the drill-through popup. This is achieved by configuring the gridObj in the beginDrillThrough event.

Grid features are segregated into individual feature-wise modules. For example, to use sorting feature, you should inject Sort using the Grid.Inject(Sort) section.

<ejs-pivotview id="PivotView" height="300" beginDrillThrough="beginDrillThrough" showTooltip="false">
    <e-editSettings allowAdding="true" allowDeleting="true" allowEditing="true" mode="Normal"></e-editSettings>
    <e-datasourcesettings dataSource="@ViewBag.DataSource" expandAll="false" enableSorting="true">
        <e-formatsettings>
            <e-field name="Amount" format="C0" maximumSignificantDigits="10" minimumSignificantDigits="1" useGrouping="true"></e-field>
        </e-formatsettings>
        <e-rows>
            <e-field name="Country"></e-field>
            <e-field name="Products"></e-field>
        </e-rows>
        <e-columns>
            <e-field name="Year" caption="Year"></e-field>
            <e-field name="Quarter"></e-field>
        </e-columns>
        <e-values>
            <e-field name="Sold" caption="Units Sold"></e-field>
            <e-field name="Amount" caption="Sold Amount"></e-field>
        </e-values>
    </e-datasourcesettings>
</ejs-pivotview>

<script>
    function beginDrillThrough(args) {
        if (args.gridObj) {
            var gridObj = args.gridObj;
            gridObj.allowGrouping = true;
            gridObj.allowSorting = true;
            gridObj.allowFiltering = true;
            gridObj.filterSettings = { type: 'CheckBox' };
        }
    }
</script>
public ActionResult Index()
{
    var data = GetPivotData();
    ViewBag.DataSource = data;
    return View();
}