PivotGrid Data Binding
This article describes the PivotGrid data binding mechanism and the supported data source options.
Data Provider Type
The PivotGrid supports different data sources via its DataProviderType parameter. The parameter type is PivotGridDataProviderType enum and its members are:
Local
When bound to local data, the Pivot Grid requires its Data parameter to provide all the data at once as IEnumerable<TItem>. The component will perform all aggregate calculations in-memory and there is no load on demand.
Large amounts of local data may impact the performance, especially in WebAssembly applications.
PivotGrid bound to Local data provider
<TelerikPivotGrid Data="@PivotData">
<PivotGridColumns>
<PivotGridColumn Name="@nameof(PivotModel.City)" />
</PivotGridColumns>
<PivotGridRows>
<PivotGridRow Name="@nameof(PivotModel.Category)" />
<PivotGridRow Name="@nameof(PivotModel.Product)" />
</PivotGridRows>
<PivotGridMeasures>
<PivotGridMeasure Name="@nameof(PivotModel.ContractValue)" />
</PivotGridMeasures>
</TelerikPivotGrid>
@code {
private List<PivotModel> PivotData { get; set; } = new List<PivotModel>();
protected override void OnInitialized()
{
var dataItemCount = 100;
var categoryCount = 3;
var productCount = 5 + 1; // effectively 5, as rnd.Next() will never return 6
var cityCount = 3 + 1; // effectively 3
var rnd = new Random();
for (int i = 1; i <= dataItemCount; i++)
{
var productNumber = rnd.Next(1, productCount);
PivotData.Add(new PivotModel()
{
Category = $"Category {productNumber % categoryCount + 1}",
Product = $"Product {productNumber}",
City = $"City {rnd.Next(1, cityCount)}",
ContractDate = DateTime.Now.AddDays(-rnd.Next(1, 31)).AddMonths(-rnd.Next(1, 12)).AddYears(-rnd.Next(0, 5)),
ContractValue = rnd.Next(123, 987)
});
}
base.OnInitialized();
}
public class PivotModel
{
public string Category { get; set; } = null!;
public string Product { get; set; } = null!;
public string City { get; set; } = null!;
public DateTime ContractDate { get; set; }
public decimal ContractValue { get; set; }
}
}
XMLA
The PivotGrid supports binding to XML for Analysis data, for example an OLAP cube. For more information about OLAP cubes, check Just What Are Cubes Anyway? A Painless Introduction to OLAP Technology by Microsoft.
The PivotGrid provides nested Razor components to setup the XMLA connection. Use the <PivotGridSettings> tag and place one or both of the following inside it:
-
<PivotGridXmlaDataProviderSettings>- configures the data server URL and database name -
<PivotGridXmlaDataProviderCredentials>- configures optional access credentials
XMLA Data Provider Settings
The PivotGridXmlaDataProviderSettings component exposes the following parameters.
| Parameter | Type | Description |
|---|---|---|
Catalog |
string |
The initial dataset (batabase) name to connect to. |
Cube |
string |
The OLAP cube name. |
ServerUrl |
string |
The endpoint URL that provides HTTP access to the XMLA Analysis Services. If the endpoint is on a different domain, make sure to configure CORS. |
Setting up an OLAP cube is outside the scope of this documentation. You can start from SQL Server Analysis Services.
XMLA Data Provider Credentials
The <PivotGridXmlaDataProviderCredentials> component creates an object, which is similar to System.Net.NetworkCredential and works in a similar way. It has the following parameters.
| Parameter | Type | Description |
|---|---|---|
Domain |
string |
(optional) The domain, which verifies the credentials. |
Password |
string |
The password for the provided Username. |
Username |
string |
The user that requests access to the XMLA data. |
XMLA Data Binding Example
PivotGrid bound to XMLA data provider without credentials
<TelerikPivotGridContainer>
<TelerikPivotGridConfigurator />
<TelerikPivotGridConfiguratorButton />
<TelerikPivotGrid DataProviderType="@PivotGridDataProviderType.Xmla"
TItem="object">
<PivotGridSettings>
<PivotGridXmlaDataProviderSettings ServerUrl="https://demos.telerik.com/olap/msmdpump.dll"
Catalog="Adventure Works DW 2008R2"
Cube="Adventure Works" />
</PivotGridSettings>
<PivotGridRows>
<PivotGridRow Name="[Product].[Category]"></PivotGridRow>
<PivotGridRow Name="[Product].[Model Name]"></PivotGridRow>
</PivotGridRows>
<PivotGridColumns>
<PivotGridColumn Name="[Date].[Calendar Quarter of Year]"></PivotGridColumn>
<PivotGridColumn Name="[Date].[Calendar Year]"></PivotGridColumn>
</PivotGridColumns>
<PivotGridMeasures>
<PivotGridMeasure Name="[Measures].[Reseller Order Count]"></PivotGridMeasure>
</PivotGridMeasures>
</TelerikPivotGrid>
</TelerikPivotGridContainer>