PivotGrid Supports OLAP & Relational Data Sources

The PivotGrid control now supports both OLAP data binding from Microsoft SQL Server Analysis Services and relational data binding from a data table or data collection.

Users can either bind OLAP or relational data to the control by setting the mode as shown in the below code snippets along with their corresponding service methods. By default, the OLAP data source will be bound to the PivotGrid control.

JavaScript

[~.html] $(“#PivotGrid”).ejPivotGrid({             url: “/wcf/RelationalService.svc”,             analysisMode: ej.PivotGrid.AnalysisMode.Pivot });

ASP.NET MVC

[~.cshtml] @Html.EJ().Pivot().PivotGrid(“PivotGrid1”).Url(Url.Content(“~/wcf/RelationalService.svc”))

ASP.NET

[~.aspx]   <cc1:PivotGrid ID=”PivotGrid1″ url=”../wcf/RelationalService.svc”>

The PivotTable Field List, also known as the Pivot Schema Designer, has been added for the ease of performing changes in the fields. It allows us to add, remove, or rearrange fields in the PivotTable using checking, unchecking, and drag-and-drop operations. The corresponding changes would be reflected in the PivotTable Field List.

Below is the code snippet for creating PivotTable Field List. The pivotControl property needs to be initialized with the PivotGrid object.

JavaScript

[~.html]   $(“#PivotSchemaDesigner”).ejPivotSchemaDesigner({ layout: ej.PivotSchemaDesigner.Layouts.Excel });

 

ASP.NET MVC

[~.cshtml]   @Html.EJ().Pivot().PivotSchemaDesigner(“PivotSchemaDesigner”).Layout(PivotSchemaDesignerLayout.Excel)

ASP.NET

[~.aspx]   <cc1:PivotSchemaDesigner ID=”PivotSchemaDesigner” Layout=”Excel”> </cc1:PivotSchemaDesigner>

The PivotTable Field List has two sections:

1. Field section: For adding and removing fields from the PivotTable.
2. Layout section: For rearranging and repositioning fields in the PivotTable.

 

The layout section has four report areas:

1. Values (for containing measures and KPIs).
2. Report Filter (for filtering the report based on the selected item in this area).
3. Column Label.
4. Row Label.

The schematic view of the PivotTable field list is shown in Figure 1.

Figure 1: PivotGrid

We can filter one or more fields in the Pivot Table by selecting or clearing check boxes corresponding to the member editor field item in the PivotTable Field List.

The filtering window can be launched by clicking the drop-down list next to the field name in the field list, as shown in Figure 2.

Figure 2: Filtering in PivotGrid

Content Contributor: Narendhran Muthuvel