Drill Down in ASP.NET Core Pivot Table control
Drill down and drill up
The drill down and drill up action helps to view the bound data in detailed and abstract view respectively. By default, if member(s) has children, then expand and collapse icon will be displayed in the respective row/column header. On clicking the icon, expand or collapse action will be performed automatically through built-in source code. Meanwhile, leaf member(s) does not contain expand and collapse icon.
Drill position
Allows to drill only the current position of the selected member and exclude the drilled data of selected member in other positions. For example, if “FY 2015” and “FY 2016” have “Q1” member as child in next level, and when end user attempts to drill “Q1” under “FY 2016”, only it will be expanded and not “Q1” under “FY 2015”.
This feature is built-in and occurs every time when expand or collapse action is done for better performance.
Expand all
This property is applicable only for the relational data source.
Allows to either expand or collapse all headers that are displayed in row and column axes. To display all headers in expanded state, set the property expandAll to true and to collapse all
headers, set the property expandAll to false. By default, expandAll property is set to false.
<ejs-pivotview id="PivotView" height="300">
<e-datasourcesettings dataSource="@ViewBag.DataSource" expandAll="true">
<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();
}
Expand all except specific member(s)
This option is applicable only for the relational data source.
In addition to the previous topic, there is an enhancement to expand all headers expect specific header(s) and similarly to collapse all headers except specific header(s). To achieve this, e-drilledmember tag is used. The required properties of the e-drilledmembers tag are explained below:
name: It allows to set the field name whose member(s) needs to be specifically drilled.items: It allows to set the exact member(s) which needs to be drilled.
The
e-drilledmembersoption always works in vice-versa with respect to the propertyexpandAllin pivot table. For example, ifexpandAllis set to true, then the member(s) added initemscollection alone will be in collapsed state.
<ejs-pivotview id="PivotView" height="300">
<e-datasourcesettings dataSource="@ViewBag.DataSource" expandAll="false">
<e-drilledmembers>
<e-field name="Country" items="@ViewBag.drilledMembers"></e-field>
</e-drilledmembers>
<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;
ViewBag.drilledMembers = new string[] { "France", "Germany" };
return View();
}
Expand specific member(s)
End user can also manually expand or collapse specific member(s) in each fields under row and column axes using the e-drilledmembers tag from code behind. The required properties of the e-drilledmembers tag are explained below:
name: It allows to set the field name whose member(s) needs to be specifically drilled.items: It allows to set the exact member(s) which needs to be drilled.delimiter: It allows to separate next level of member from its parent member.
<ejs-pivotview id="PivotView" height="300">
<e-datasourcesettings dataSource="@ViewBag.DataSource" expandAll="false">
<e-drilledmembers>
<e-field name="Quarter" items="@ViewBag.drilledMem"></e-field>
<e-field name="Year" delimiter="~~" items="@ViewBag.drilledMembers"></e-field>
</e-drilledmembers>
<e-rows>
<e-field name="Year" caption="Year"></e-field>
<e-field name="Quarter"></e-field>
<e-field name="Products"></e-field>
</e-rows>
<e-columns>
<e-field name="Country"></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;
ViewBag.drilledMembers = new string[] { "FY 2015","FY 2016" };
ViewBag.drilledMem = new string[] { "FY 2015~~Q1" };
return View();
}
Event
Drill
The event drill triggers every time when a field is expanded or collapsed. For instance using this event user can alter delimiter and drill action for the respective item. It has the following parameters:
drillInfo- It holds the current drilled item information.pivotview- It holds pivot table instance.
<ejs-pivotview id="PivotView" height="300" drill="onDrill">
<e-datasourcesettings dataSource="@ViewBag.DataSource" expandAll="false">
<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 onDrill(args) {
// triggers whenever a cell is expanded or collapsed
}
</script>