HelpBot Assistant

How can I help you?

OLAP in ASP.NET CORE Pivot Table component

26 Feb 202624 minutes to read

Getting Started with ASP.NET Core

NOTE

Starting with v16.2.0.x, if you reference Syncfusion^® assemblies from trial setup or from the NuGet feed, you also have to include a license key in your projects. Refer to this link to know about registering Syncfusion^® license key in your ASP.NET MVC application to use our components.

Prerequisites

The official prerequisites to create and run an ASP.NET Core 2.x application on Windows environment are described in the .NET Core documentation website.

Create ASP.NET Core web application

Step 1: Choose File > New > Project… in the Visual Studio menu bar.

new project in aspnetcore2.x

Step 2: Select Installed > Visual C# > .NET Core and choose the required .NET Framework in the drop-down.

Step 3: Select ASP.NET Core Web Application and change the application name, and then click OK.

aspnetcore2.x project template

Step 4: Choose .NET Core with ASP.NET Core 2.0 and select Web Application(Model-View-Controller), and then click OK. The web application project is now created with default ASP.NET Core template.

aspnetcore2.x web application template

Configure Essential^® JS 2 in the application

Step 1: Add the Syncfusion.EJ2.AspNet.Core NuGet package to the new application by using the Nuget Package Manager. Right-click the project and select Manage NuGet Packages….

NOTE

Refer to this article to learn more details about installing Essential^® JS 2 NuGet packages in various OS environment.

aspnetcore2.x manage nuget packages

Step 2: Search the Syncfusion.EJ2.AspNet keyword in the Browse tab and install Syncfusion.EJ2.AspNet.Core NuGet package in the application.

aspnetcore2.x install nuget packages

The Essential^® JS 2 package will be included in the project, after the installation process is completed.

NOTE

The Syncfusion.EJ2.AspNet.Core NuGet package has dependencies, Newtonsoft.Json for JSON serialization and Syncfusion.Licensing for validating Syncfusion^® license key.

Step 3: Open ~/Views/_ViewImports.cshtml file and import the Syncfusion.EJ2 package.

@addTagHelper *, Syncfusion.EJ2

Step 4: Add the client-side resources through CDN or local npm package in the <head> element of the ~/Views/Shared/_Layout.cshtml layout page.

<head>
...

    <!-- Syncfusion Essential JS 2 Styles -->
    <link rel="stylesheet" href="https://cdn.syncfusion.com/ej2/tailwind3.css" />

    <!-- Syncfusion Essential JS 2 Scripts -->
    <script src="https://cdn.syncfusion.com/ej2/dist/ej2.min.js"></script>
</head>

Step 5: Add the Essential^® JS 2 Script Manager at the end of <body> element in the ~/Views/Shared/_Layout.cshtml layout page.

<body>
...
    <!-- Syncfusion Essential JS 2 ScriptManager -->
    <ejs-scripts></ejs-scripts>
</body>

Adding component to the application

Add the below code to your Index.cshtml view page which is present under Views/Home folder, to initialize the pivot table component with sample OLAP data source.

CSHTML
Pivottable.cs
<ejs-pivotview id="pivotview" width="100%" height="600">
    <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-values>
        <e-filters>
            <e-field name="[Date].[Fiscal]" caption="Date Fiscal"></e-field>
        </e-filters>
    </e-datasourcesettings>
</ejs-pivotview>
public ActionResult Index()
{
    return View();
}

Adding OLAP Cube Elements to Row, Column, Value, and Filter Axes

After initializing the Pivot Table and assigning a sample OLAP data source, you can organize the OLAP cube elements to define how your data is displayed using the rows, columns, values, and filters properties in the DataSourceSettings option.

You can use these four main axes to arrange OLAP cube elements from your data source and control how the Pivot Table displays the information.

rows: Add OLAP cube elements such as hierarchies, named sets, or calculated members to show them as rows in the Pivot Table.
columns: Add OLAP cube elements like hierarchies, named sets, or calculated members to show them as columns in the Pivot Table.
values: Add OLAP cube elements such as measures or calculated measures to display summarized numeric data in the Pivot Table.
filters: Add OLAP cube elements like hierarchies or calculated members here to filter the data shown in the row, column, and value axes.

To specify each OLAP cube element in the required axis, set the following options:

name: Specifies the unique name of the hierarchy, named set, measure, or calculated member from the OLAP data source. The name must be entered exactly as it appears in the data source. If the name is not matched, the Pivot Table will be empty.
caption: Specifies a caption or display name for the item in the Pivot Table. If a caption is not set, the unique name appears by default.

For example, in the sample below, the element “Product Categories” is assigned to the columns axis, “Customer Geography” is assigned to the rows axis, and both “Customer Count” and “Internet Sales Amount” are set in the values axis.

CSHTML
Fields.cs
<ejs-pivotview id="pivotview" width="100%" height="600">
    <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-values>
        <e-filters>
            <e-field name="[Date].[Fiscal]" caption="Date Fiscal"></e-field>
        </e-filters>
    </e-datasourcesettings>
</ejs-pivotview>
public ActionResult Index()
{
    return View();
}

Applying Formatting to a Value Field

You can change how values in the Pivot Table are displayed by applying formatting. For example, you can display values as currency by using the C format string. To apply formatting, use the e-formatsettings property within DataSourceSettings, and define both the name (the value field to format) and the format (the format to apply).

In the following example, the e-formatsettings property is used to apply the C0 format to the [Measures].[Internet Sales Amount] field. This causes its values to be displayed as currency, showing the currency symbol without any decimal places. You can add formatting for other value fields in a similar way by including them in the e-formatsettings array.

Only fields from the value section containing numeric data can be formatted.

CSHTML
Formatting.cs
<ejs-pivotview id="pivotview" width="100%" height="600">
    <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-values>
        <e-filters>
            <e-field name="[Date].[Fiscal]" caption="Date Fiscal"></e-field>
        </e-filters>
        <e-formatsettings>
            <e-field name="[Measures].[Internet Sales Amount]" format="C0"></e-field>
        </e-formatsettings>
    </e-datasourcesettings>
</ejs-pivotview>
public ActionResult Index()
{
    return View();
}

Enable Grouping Bar

The grouping bar lets users easily organize OLAP cube elements from the connected data source. Users can drag these cube elements between different axes, such as rows, columns, values, and filters, to quickly change how data is shown in the Pivot Table. It also allows sorting, filtering, and removing of elements directly from the grouping bar, making it simple to customize the Pivot Table layout at runtime.

To display the grouping bar, set the showGroupingBar property to true in the Pivot Table component, and make sure to inject the GroupingBar module as shown below.

Note: If the GroupingBar module is not injected, the grouping bar will not appear in the Pivot Table component.

CSHTML
Grouping-bar.cs
<ejs-pivotview id="pivotview" showGroupingBar="true" width="100%" height="600">
    <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-values>
        <e-filters>
            <e-field name="[Date].[Fiscal]" caption="Date Fiscal"></e-field>
        </e-filters>
    </e-datasourcesettings>
</ejs-pivotview>
public ActionResult Index()
{
    return View();
}

Enable Pivot Field List

The Pivot Table control includes a built-in Field List, similar to the one in Microsoft Excel. This Field List allows users to add or remove OLAP cube elements, and to move them between different axes: rows, columns, values, and filters. Users can also filter and sort these elements as needed, all during runtime.

To display the Field List, set the showFieldList property to true on the Pivot Table. It is also necessary to inject the FieldList module.

Note: If the FieldList module is not injected, the Field List will not appear in the Pivot Table.

CSHTML
Field-list.cs
<ejs-pivotview id="pivotview" showFieldList="true" width="100%" height="600">
    <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-values>
        <e-filters>
            <e-field name="[Date].[Fiscal]" caption="Date Fiscal"></e-field>
        </e-filters>
    </e-datasourcesettings>
</ejs-pivotview>
public ActionResult Index()
{
    return View();
}

Exploring Filter Axis

The filter axis in the Pivot Table allows users to control which data is displayed in the rows, columns, and values axes. It includes various OLAP cube elements, such as hierarchies and calculated members. When elements are placed in the filter axis, they act as master filters that refine the data shown in the Pivot Table.

Users can add OLAP cube elements and filter members to the filter axis either by updating the report in code behind or by dragging items from other axes to the filter axis using the grouping bar or field list at runtime. This makes it easy to filter data according to specific requirements directly within the Pivot Table interface.

CSHTML
Explore-filter.cs
<ejs-pivotview id="pivotview" allowCalculatedField="true" showFieldList="true" showGroupingBar="true" width="100%" height="600">
    <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-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-datasourcesettings>
</ejs-pivotview>
public ActionResult Index()
{
    ViewBag.filterMembers = new string[] { "[Date].[Fiscal].[Fiscal Quarter].&[2002]&[4]", "[Date].[Fiscal].[Fiscal Year].&[2005]" };
    return View();
}

Calculated Field

The calculated field option lets users create a new field in the Pivot Table by using expressions based on existing OLAP cube elements from the connected data source. These calculated fields are new custom dimensions or measures built from an expression defined by the user.

There are two types of calculated fields:

Calculated Measure – Creates a new measure by using a custom expression.
Calculated Dimension – Creates a new dimension by using a custom expression.

You can define calculated fields in your code by using the e-calculatedFieldSettings property in the e-datasourcesettings configuration. The available options for calculated fields are:

name: Sets a unique name for the new calculated field.
formula: Allows you to set the expression for the calculated field.
hierarchyUniqueName: Specifies the dimension’s unique name, so that only hierarchies within that dimension are used in the expression. This option applies only to calculated dimensions.
formatString: Sets the format for the calculated field result.

When adding calculated fields to an axis in your code, set the isCalculatedField property to true.

You can also add calculated fields at runtime through the built-in dialog. To enable this dialog, set the allowCalculatedField property to true and add the CalculatedField module. This will display a button in the Field List UI, letting users open the calculated field dialog and create or edit calculated fields as needed.

If the CalculatedField module is not added, the calculated field dialog will not be shown with the Pivot Table component. Also, calculated measures can be added only to the value axis.

CSHTML
Calculated-field.cs
<ejs-pivotview id="pivotview" allowCalculatedField="true" showFieldList="true" showGroupingBar="true" width="100%" height="600">
    <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()
{
    ViewBag.filterMembers = new string[] { "[Date].[Fiscal].[Fiscal Quarter].&[2002]&[4]", "[Date].[Fiscal].[Fiscal Year].&[2005]" };
    return View();
}

Users can add a calculated field at runtime using the built-in dialog by following these steps:

Step 1: Click the CALCULATED FIELD button in the field list dialog, located at the top right corner. The calculated field dialog appears. Enter the name for the new calculated field in the dialog.

Calculated field button

Enter calculated field name

Step 2: Create the expression for your calculated field. To do this, drag and drop fields from the tree view on the left side of the dialog and use simple arithmetic operators.

For example: IIF([Measures].[Internet Sales Amount]^0.5 > 100, [Measures].[Internet Sales Amount]*100, [Measures].[Internet Sales Amount]/100)
For more information about supported operators and functions, see the Microsoft documentation.

Create calculated field expression

Step 3: Select the type for the new field, either calculated measure or calculated dimension.

Select calculated field type

Step 4: If you are creating a calculated dimension, select its parent hierarchy from the drop-down list. This step is only required when adding a calculated dimension.

Choose parent hierarchy

Step 5: Select a format string from the drop-down list and then click OK to finalize the calculated field.

Choose format string

Calculated field added in Pivot Table

Format String

When creating a calculated field in the Pivot Table, you can choose the format for displaying values by selecting a format string. The available options are:

Standard – Displays values as standard numbers.
Currency – Displays values in currency format.
Percent – Displays values as a percentage.
Custom – Allows you to define your own format string. For example, entering “###0.##0#” will show the value “9584.3” as “9584.300”.

By default, the Standard option is selected in the drop-down list.

This option helps users present calculated field results in the most suitable format for their needs.

Format string in calculated field

Renaming the Existing Calculated Field

You can rename any existing calculated field directly through the user interface at runtime. This option allows you to update calculated field names to keep them clear and meaningful as your analysis needs change.

To rename a calculated field:

Open the calculated field dialog in the Pivot Table.
Click the name of the field you want to rename. The current name will be shown in the text box at the top of the dialog.
Enter the new name in the text box.
Click OK to save the new name.

Renaming the calculated field before editing

Renaming the calculated field after editing

Editing an Existing Calculated Field Formula

You can edit an existing calculated field formula directly through the user interface at runtime. To do this:

Open the calculated field dialog in the Pivot Table.
From the list, select the calculated field you want to edit.
The current formula for the selected field will appear in the Expression section.
Modify the formula as needed based on your requirements.
Click OK to apply and save your changes.

The Pivot Table will automatically update to show the changes in the calculated values.

Editing the calculated field

Editing the calculated field after change

Reusing an Existing Formula in a New Calculated Field

This option allows you to easily create a new calculated field in the Pivot Table by reusing a formula from an existing calculated field. This saves time and helps keep your calculations consistent.

To reuse an existing formula when working with the OLAP data source:

Open the calculated field dialog in the Pivot Table.
Find the existing calculated field that contains the formula you want to use again.
Drag the existing calculated field from the field list treeview.
Drop it into the Expression section. The formula from the selected field is then added automatically.
If needed, you can adjust the formula further or use it without changes.
Click OK to add your new calculated field.

Reusing the calculated field in Pivot Table

Dragging the existing calculated field

Calculated field formula reused

Modifying the Existing Format String

You can modify the format string of an existing calculated field at runtime through the user interface. To do this:

Open the calculated field dialog in the Pivot Table.
Click the name of the calculated field you want to edit.
The dialog will display the current format string in a drop-down list.
Select or enter a new format string based on your requirements.
Click OK to apply and save your changes.

Modifying the calculated fields format string

Modified calculated field output

Clearing the Changes While Editing the Calculated Field

If you make edits while creating or modifying a calculated field, you can easily remove all the current changes by clicking the Clear button. This option is available in the bottom left corner of the calculated field dialog. Using the Clear button helps you start over without manually undoing each change, ensuring a smooth editing experience.

Clearing the changes while editing the calculated field

Virtual Scrolling

Virtual scrolling helps you view large amounts of data smoothly in the Pivot Table. It loads and displays only the rows and columns currently visible in the viewport. As you scroll vertically or horizontally, new data is brought into view automatically, ensuring good performance even with a large data source.

To enable virtual scrolling, set the enableVirtualization option to true. Also, be sure to inject the VirtualScroll module into the Pivot Table.

CSHTML
Virtual.cs
<ejs-pivotview id="pivotview" width="100%" height="600" enableVirtualization="true">
    <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]" caption="Customer"></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-values>
        <e-filters>
            <e-field name="[Date].[Fiscal]" caption="Date Fiscal"></e-field>
        </e-filters>
        <e-formatsettings>
            <e-field name="[Measures].[Internet Sales Amount]" format="C0"></e-field>
        </e-formatsettings>
    </e-datasourcesettings>
</ejs-pivotview>
public ActionResult Index()
{
    return View();
}

Virtual Scrolling applied

Limitations for Virtual Scrolling

When using virtual scrolling, the columnWidth property under e-gridSettings must be set in pixels; percentage values are not supported.
Resizing columns or setting width to individual columns affects the calculation used to pick the correct page on scrolling.
With OLAP data, subtotals and grand totals are shown only when measures are placed at the end of the rows or columns axes within DataSourceSettings. If measures appear elsewhere, data will display without summary totals.
If the width and height of the Pivot Table are set to large values, the amount of data loaded in the current, previous, and next pages increases. This may impact loading performance during scrolling.

Data Binding

To connect an OLAP data source to the Pivot Table, use the DataSourceSettings property. Several options within dataSourceSettings must be specified to bind data correctly:

Property	Description
`cube`	Specifies the name of the OLAP cube to use from the database.
`providerType`	Indicates the type of provider, helping the Pivot Table determine how to connect to the data source.
`url`	The URL of the OLAP service. Use this to establish an online connection to the cube.
`catalog`	The database or catalog name containing the cube data.

Below are sample code files showing how to bind an OLAP data source in ASP.NET Core:

CSHTML
Data-binding.cs
<ejs-pivotview id="pivotview" width="100%" height="600">
    <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-values>
        <e-filters>
            <e-field name="[Date].[Fiscal]" caption="Date Fiscal"></e-field>
        </e-filters>
    </e-datasourcesettings>
</ejs-pivotview>
public ActionResult Index()
{
    return View();
}

Fields

Measures in the Row Axis

By default, measures are shown on the columns axis in the Pivot Table. If you would like to display measures on the rows axis instead, you can do this using the grouping bar or the field list UI. Simply drag the “Measures” button and drop it onto the rows axis.

Alternatively, you can set up the measure directly in your code by configuring the DataSourceSettings option, as shown in the code below:

CSHTML
Measures-in-row.cs
<ejs-pivotview id="pivotview" width="100%" height="600">
    <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-field name="[Measures]" caption="Measures"></e-field>
        </e-rows>
        <e-columns>
            <e-field name="[Product].[Product Categories]" caption="Product Categories"></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-values>
        <e-filters>
            <e-field name="[Date].[Fiscal]" caption="Date Fiscal"></e-field>
        </e-filters>
    </e-datasourcesettings>
</ejs-pivotview>
public ActionResult Index()
{
    ViewBag.filterMembers = new string[] { "[Date].[Fiscal].[Fiscal Quarter].&[2002]&[4]", "[Date].[Fiscal].[Fiscal Year].&[2005]" };
    return View();
}

Measures in Different Positions

You can choose where to place measures on either the row or column axis through code behind or the user interface. In this example, the measures are set before the dimension field on the column axis. To achieve this, specify the order of the fields within the DataSourceSettings property.

CSHTML
Measures-position.cs
<ejs-pivotview id="pivotview" width="100%" height="600">
    <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="[Measures]" caption="Measures"></e-field>
            <e-field name="[Product].[Product Categories]" caption="Product Categories"></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-values>
        <e-filters>
            <e-field name="[Date].[Fiscal]" caption="Date Fiscal"></e-field>
        </e-filters>
    </e-datasourcesettings>
</ejs-pivotview>
public ActionResult Index()
{
    ViewBag.filterMembers = new string[] { "[Date].[Fiscal].[Fiscal Quarter].&[2002]&[4]", "[Date].[Fiscal].[Fiscal Year].&[2005]" };
    return View();
}

Named Set

A named set is a multidimensional expression (MDX) that provides a predefined group of members from a dimension. It is created by combining cube data with arithmetic operators, numbers, or functions.

To display a named set in the Pivot Table, set its unique name using the name property within either the row or column axis in DataSourceSettings. Additionally, set the isNamedSet property to true. In the example below, the “Core Product Group” named set is added to the column axis.

CSHTML
Named-set.cs
<ejs-pivotview id="pivotview" showFieldList="true" showGroupingBar="true" width="100%" height="600">
    <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="[Core Product Group]" caption="Core Product Group" isNamedSet="true"></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-values>
        <e-filters>
            <e-field name="[Date].[Fiscal]" caption="Date Fiscal"></e-field>
        </e-filters>
    </e-datasourcesettings>
</ejs-pivotview>
public ActionResult Index()
{
    ViewBag.filterMembers = new string[] { "[Date].[Fiscal].[Fiscal Quarter].&[2002]&[4]", "[Date].[Fiscal].[Fiscal Year].&[2005]" };
    return View();
}

Configuring Authentication

To connect to an OLAP data source that requires authentication, users can provide basic authentication details through the e-authentication property within the DataSourceSettings option of the Pivot Table. The authentication options include:

userName: Enter the username required for access to the OLAP server.
password: Enter the password associated with the username.

If authentication details are not provided, the browser will display a default pop-up window prompting users to enter the required information.

Below is an example of how to configure authentication settings in the Pivot Table:

CSHTML
Authentication.cs
<ejs-pivotview id="pivotview" width="100%" height="600">
    <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-values>
        <e-filters>
            <e-field name="[Date].[Fiscal]" caption="Date Fiscal"></e-field>
        </e-filters>
		<e-authentication userName="userName" password="password"></e-authentication>
    </e-datasourcesettings>
</ejs-pivotview>
public ActionResult Index()
{
    return View();
}

Roles

SQL Server Analysis Services (SSAS) uses roles to control user access to the data inside an OLAP cube. Each role is defined with a set of permissions that can be assigned to individual users or groups. By assigning roles, you can restrict access to sensitive data and also determine who can view or modify information in the cube.

In the Syncfusion ASP.NET Core Pivot Table, you can specify roles using the roles property within the e-datasourcesettings object. This allows you to provide one or more role names for connecting to an OLAP cube. If you want to use multiple roles, list them as a comma-separated string.

CSHTML
Roles.cs
<ejs-pivotview id="pivotview" width="100%" height="600">
    <e-datasourcesettings catalog="Adventure Works DW 2008 SE" cube="Adventure Works" url="https://bi.syncfusion.com/olap/msmdpump.dll" providerType="SSAS" roles="Role1">
        <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-values>
    </e-datasourcesettings>
</ejs-pivotview>
public ActionResult Index()
{
    return View();
}

OLAP Cube: Elements

Field List

The field list, also called the cube dimension browser, displays the cube elements from the selected OLAP cube in a tree view structure. It organizes elements such as dimensions, hierarchies, and measures into logical groups, making it easier for the user to explore and arrange data for analysis using the Pivot Table.

Types of Nodes in the Field List

Display folder: Contains a set of similar cube elements grouped together.
Measure: Represents the numeric values or quantities that users can analyze and summarize in the Pivot Table.
Dimension: Groups related data and helps users to categorize and filter information in the cube.
Attribute hierarchy: Shows data at different attribute levels within a dimension, allowing users to drill down for more specific analysis.
User-defined hierarchy: Presents a custom arrangement of members within a dimension, structured in multiple levels for easier navigation and deeper data analysis.
Level: Indicates a specific position or stage within a hierarchy for more focused data review.
Named set: A saved collection of tuples or members that can be reused in analysis as part of the cube definition.

Measure

A measure in a cube refers to a numeric value that comes from a column in the cube’s fact table. Measures are the main values analyzed in the Pivot Table. They help users investigate metrics such as sales, costs, expenditures, or production counts. Users can select measures based on their analysis needs. In the field list, all available measures are grouped separately, making it easy to select or remove measures as required. When a user chooses a measure, it is displayed in the desired area of the Pivot Table and participates in calculations and summary values.

Dimension

A dimension is an essential part of the OLAP cube in the Pivot Table. It holds key information, such as its name, hierarchies, levels, and members. To use a dimension, you specify its name, along with the desired hierarchy and the corresponding level. Each dimension contains detailed information about its hierarchies, and each hierarchy is made up of one or more levels. Within each level, there are members, and each member can also have child members. This structure helps users organize and explore data easily in the Pivot Table.

Hierarchy

A hierarchy organizes elements within a dimension into a series of parent-child relationships. Each parent member groups its child members, summarizing their data. These parent members can also be grouped under another parent for further summarization. For example, in a time dimension, the month of May 2005 can be grouped under Second Quarter 2005, which is then summarized under the year 2005.

Level

A level is a child element of a hierarchy in the field list. It contains a group of members that share the same rank within that hierarchy. For example, in a hierarchy representing geographical data, a level might include members like cities or states, all at the same depth.

Attribute Hierarchy

An attribute hierarchy in the Pivot Table organizes data into levels for easier analysis. It includes:

Leaf level: This level contains unique attribute members, known as leaf members. Each leaf member represents a distinct data point.
Intermediate levels: These exist in a parent-child hierarchy, connecting the leaf level to higher levels for structured data exploration.
Optional (all) level: This level shows the combined total of all leaf members’ values. The member at this level is called the (all) member.

User-Defined Hierarchy

A user-defined hierarchy arranges the members of a dimension into a structured, hierarchical format, making it easier to navigate and analyze data in the cube. For example, consider a dimension table with attributes like year, quarter, and month. These attributes can be combined to create a user-defined hierarchy named Calendar within the time dimension. This hierarchy connects all levels—year, quarter, and month—allowing users to explore data across different time periods seamlessly.

Differentiating User-Defined Hierarchy and Attribute Hierarchy

In the field list of the Pivot Table, hierarchies help users organize and analyze data in different ways. There are two main types of hierarchies:

User-defined hierarchy: This type of hierarchy consists of two or more levels. Each level is created by combining related fields, which allows users to drill down through the data step by step—for example, from “Year” to “Quarter” to “Month” within a “Date” dimension. User-defined hierarchies use fields from the same dimension to create a logical path for navigation.
Attribute hierarchy: In this type, there is only a single level. Each field in the dimension automatically forms an attribute hierarchy. For example, if “Country” is a field, it will appear as an attribute hierarchy with just one level, letting the user view data for each country individually.

Named Set

A named set is a group of specific tuples or members that can be defined and stored within the OLAP cube. Named sets are saved inside the sets folder under a dimension element in the field list, making them easy to locate. Users can add these named sets to the rows or columns axes through the grouping bar or the field list when working with the Pivot Table at runtime. Named sets are useful for handling long, complex, or frequently used expressions. The cube supports defining named sets using Multidimensional Expressions (MDX), which helps users manage these expressions more efficiently.

Calculated Field

The calculated field option lets users create a new field in the Pivot Table using their own formula or expression, based on the existing OLAP cube elements in the connected data source. These fields act as custom dimensions or measures, allowing users to add calculations that are not originally available in the cube.

There are two types of calculated fields:

Calculated measure – This allows users to create a new measure by defining a custom expression. The new measure is then available in the field list along with the other measures.
Calculated dimension – This allows users to create a new dimension using a user-defined expression. The dimension is grouped together with other dimensions in the field list.

Symbolic Representation of the Nodes Inside Field List

In the field list, each node uses a specific icon to help users quickly identify its type and purpose. The following table describes each type of node, its symbol, and whether it can be dragged into the Axis Fields:

Name	Node Type	Is Draggable?
Display folder	Display Folder	No
Measure	Measure	No
Dimension	Dimension	No
User-defined hierarchy	Hierarchy	Yes
Attribute hierarchy	Hierarchy	Yes
Levels (in order)	Level Element	Yes
Named set	Named Set	Yes

Events

BeforeServiceInvoke

The beforeServiceInvoke event is triggered before initiating any service communication with the OLAP server in the Pivot Table and Field List components.

This event allows you to inject custom properties or additional parameters dynamically before a request is made to the OLAP server.
It is particularly useful for passing contextual data such as user tokens, custom filters, or localization information along with the original server request.

When the beforeServiceInvoke event is triggered, the event argument provides access to the request details and includes a customProperties field.

CSHTML
BeforeServiceInvoke.cs
<ejs-pivotview id="pivotview" width="100%" height="600" beforeServiceInvoke="beforeServiceInvoke">
    <e-datasourcesettings catalog="Adventure Works DW 2008 SE" cube="Adventure Works" url="https://bi.syncfusion.com/olap/msmdpump.dll" providerType="SSAS">
        <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-values>
    </e-datasourcesettings>
</ejs-pivotview>

<script>
    function beforeServiceInvoke(args) {
        if (args.actionName == 'Aggregate field') {
            args.cancel = true;
        }
    }
</script>
public ActionResult Index()
{
    return View();
}

AfterServiceInvoke

The afterServiceInvoke event is triggered in the Pivot Table and Field List components during the onSuccess phase of every OLAP service request.

This event is useful for performing post-processing, logging actions, or updating the UI after receiving a successful response from the OLAP server.
You may use it to audit data, trigger notifications, or handle custom response-handling logic.

When the afterServiceInvoke event is triggered, the event argument provides access to the server response details, including properties such as the action performed and the result data returned from the OLAP server.

CSHTML
AfterServiceInvoke.cs
<ejs-pivotview id="pivotview" width="100%" height="600" afterServiceInvoke="afterServiceInvoke">
    <e-datasourcesettings catalog="Adventure Works DW 2008 SE" cube="Adventure Works" url="https://bi.syncfusion.com/olap/msmdpump.dll" providerType="SSAS">
        <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-values>
    </e-datasourcesettings>
</ejs-pivotview>

<script>
    function afterServiceInvoke(args) {
        if (args.actionName == 'Aggregate field') {
            args.cancel = true;
        }
    }
</script>
public ActionResult Index()
{
    return View();
}

How can I help you?

OLAP in ASP.NET CORE Pivot Table component

Getting Started with ASP.NET Core

Prerequisites

Create ASP.NET Core web application

Configure Essential® JS 2 in the application

Adding component to the application

Adding OLAP Cube Elements to Row, Column, Value, and Filter Axes

Applying Formatting to a Value Field

Enable Grouping Bar

Enable Pivot Field List

Exploring Filter Axis

Calculated Field

Format String

Renaming the Existing Calculated Field

Editing an Existing Calculated Field Formula

Reusing an Existing Formula in a New Calculated Field

Modifying the Existing Format String

Clearing the Changes While Editing the Calculated Field

Virtual Scrolling

Limitations for Virtual Scrolling

Data Binding

Fields

Measures in the Row Axis

Measures in Different Positions

Named Set

Configuring Authentication

Roles

OLAP Cube: Elements

Field List

Types of Nodes in the Field List

Measure

Dimension

Hierarchy

Level

Attribute Hierarchy

User-Defined Hierarchy

Differentiating User-Defined Hierarchy and Attribute Hierarchy

Named Set

Calculated Field

Symbolic Representation of the Nodes Inside Field List

Events

BeforeServiceInvoke

AfterServiceInvoke

Configure Essential^® JS 2 in the application