New to Telerik UI for ASP.NET AJAX? Download free 30-day trial

OLAP and KPIs Support
All Fields
  • Account
  • Customer
  • Date
  • Delivery Date
  • Department
  • Destination Currency
  • Employee
  • Geography
  • Internet Sales Order Details
  • Organization
  • Product
  • Promotion
  • Reseller
  • Reseller Sales Order Details
  • Sales Channel
  • Sales Reason
  • Sales Summary Order Details
  • Sales Territory
  • Scenario
  • Ship Date
  • Source Currency
  • KPIs
  • Internet Sales
  • Internet Orders
  • Internet Customers
  • Reseller Sales
  • Reseller Orders
  • Sales Summary
  • Sales Orders
  • Sales Targets
  • Financial Reporting
  • Exchange Rates
Filter Fields
Row Fields
CategoryValues
Column Fields
Year
Aggregate Fields
Internet Sales AmountRevenue GoalRevenue StatusRevenue Trend
empty
Drop Filter Fields Here
Internet Sales AmountRevenue GoalRevenue StatusRevenue TrendAggregate fieldsYear
Category 
FY 2005FY 2006FY 2007FY 2008FY 2009FY 2010FY 2011FY 2012FY 2013FY 2014Grand Total
       $294,907.67$405,852.29 $700,759.96
       294907.67324398.437446437.519700759.96
     $3,222,952.62$6,586,209.31$9,371,244.74$9,137,737.98 $28,318,144.65
     3222952.62023545247.882227244830.2436410308369.2119110051511.77828318144.6507
       $138,857.34$200,915.27 $339,772.61
       138857.34152743.074221006.797339772.61
           
           
     $3,222,952.62$6,586,209.31$9,805,009.75$9,744,505.54 $29,358,677.22
     3222952.62023545247.882227244830.2436410785510.7229110718956.09429358677.2207
AccessoriesInternet Sales Amount
Goal (Internet Revenue)
Status (Internet Revenue)
Trend (Internet Revenue)
BikesInternet Sales Amount
Goal (Internet Revenue)
Status (Internet Revenue)
Trend (Internet Revenue)
ClothingInternet Sales Amount
Goal (Internet Revenue)
Status (Internet Revenue)
Trend (Internet Revenue)
ComponentsInternet Sales Amount
Goal (Internet Revenue)
Status (Internet Revenue)
Trend (Internet Revenue)
Total Internet Sales Amount
Total Goal (Internet Revenue)
Total Status (Internet Revenue)
Total Trend (Internet Revenue)
1
Page size:
  • Refresh
  • Hide
  • Show Fields Window
  • Stacked
  • Side-By-Side
  • Two-By-Two
  • One-By-Four
  • Move Up
  • Move Down
  • Move to Beginning
  • Move to End
  • Move to Filter Fields
  • Move to Row Fields
  • Move to Column Fields
  • Move to Aggregate Fields
  • Hide Field
  • Demo Configurator

Set the olap data provider

RadPivotGrid supports displaying data from OLAP data sources through the XMLA or the ADOMD.NET data provider.

In order to configure the grid for binding to an OLAP data source you need the following settings:

  • OlapSettings.ProviderType – tells the pivot grid control what type of provider should be used for binding to the OLAP data source. In this case, it should be set to Xmla.
  • OlapSettings.XmlaConnectionSettings - this property is of type XmlaConnectionSettings. It is used to define all needed connection settings through the XmlaConnectionSettings properties:
    • Cube - string property defining the exact name of the Cube.
    • Database - string property defining the exact name of the Database.
    • ServerAddress - string property defining the exact address(with protocol used) of the server.
    • Credentials - this property is of type XmlaNetworkCredential and it is used to define the authentication details for the server (if it has any kind of authentication).
By analogy, should the pivot grid be bound through the ADOMD.NET provider, the olap settings should be configured as follows:
  • OlapSettings.ProviderType = “Adomd”.
  • OlapSettings.AdomdConnectionSettings - this property is of type AdomdConnectionSettings and it exposes several properties that are mandatory when connecting to the OLAP Cube:
    • Cube - string property defining the exact name of the Cube.
    • Database - string property defining the exact name of the Database.
    • ConnectionString - string property used to open a database. It is in OLE DB connection string format.
    • Credentials - this property is of type XmlaNetworkCredential and it is used to define the authentication.

The PivotGrid Hierarchical Fields List (positioned on the left of the Telerik's PivotGrid control) which reorders, sorts, hides and shows fields could be enabled by setting the RadPivotGrid.EnableConfigurationPanel property to true.

  • DefaultCS.aspx
  • DefaultCS.aspx.cs
  • styles.css
<%@ Page Language="c#" AutoEventWireup="false" CodeFile="DefaultCS.aspx.cs" Async="true"Inherits="Telerik.PivotGrid.Examples.Olap.DefaultCS"  %>

<%@ Register TagPrefix="telerik" Namespace="Telerik.QuickStart" %>
<%@ Register TagPrefix="telerik" Namespace="Telerik.Web.UI" Assembly="Telerik.Web.UI" %>
<!DOCTYPE html>
<html xmlns='http://www.w3.org/1999/xhtml'>
<head runat="server">
    <title>Telerik ASP.NET Example</title>
    <link href="styles.css" rel="stylesheet" />
</head>
<body>
    <form id="form1" runat="server">
    <telerik:RadScriptManager runat="server" ID="RadScriptManager1" />
    <telerik:RadSkinManager ID="RadSkinManager1" runat="server" ShowChooser="true" />
   <div class="demo-container no-bg">
 <telerik:RadAjaxLoadingPanel ID="RadAjaxLoadingPanel1" runat="server">
    </telerik:RadAjaxLoadingPanel>
    <telerik:RadAjaxManager ID="RadAjaxManager1" runat="server" DefaultLoadingPanelID="RadAjaxLoadingPanel1">
        <AjaxSettings>
            <telerik:AjaxSetting AjaxControlID="RadPivotGrid1">
                <UpdatedControls>
                    <telerik:AjaxUpdatedControl ControlID="RadPivotGrid1"></telerik:AjaxUpdatedControl>
                </UpdatedControls>
            </telerik:AjaxSetting>
            <telerik:AjaxSetting AjaxControlID="ConfiguratorPanel1">
                <UpdatedControls>
                    <telerik:AjaxUpdatedControl ControlID="RadPivotGrid1"></telerik:AjaxUpdatedControl>
                    <telerik:AjaxUpdatedControl ControlID="ConfiguratorPanel1"></telerik:AjaxUpdatedControl>
                </UpdatedControls>
            </telerik:AjaxSetting>
        </AjaxSettings>
    </telerik:RadAjaxManager>
    <div class="PivotGridWrapper">
        <telerik:RadPivotGrid RenderMode="Lightweight" AllowFiltering="true" AllowSorting="true" ID="RadPivotGrid1"
            RowTableLayout="Tabular" FieldsPopupSettings-AggregateFieldsMinCount="2" AllowPaging="true"
            PageSize="20" runat="server" EnableConfigurationPanel="true" EnableZoneContextMenu="true"
            AggregatesPosition="Rows">
            <PagerStyle AlwaysVisible="true" />
            <OlapSettings ProviderType="Xmla">
                <AdomdConnectionSettings Cube="Adventure Works" DataBase="Adventure Works DW 2008R2"
                    ConnectionString="Data Source=https://demos.telerik.com/service/v2/olap/msmdpump.dll;Catalog=Adventure Works DW 2008R2"></AdomdConnectionSettings>
                <XmlaConnectionSettings ServerAddress="https://demos.telerik.com/service/v2/olap/msmdpump.dll" 
                    Cube="Adventure Works" DataBase="Adventure Works DW 2008R2" />
            </OlapSettings>
            <RowHeaderCellStyle Width="100px" />
            <Fields>
                <telerik:PivotGridRowField DataField="[Product].[Category]" Caption="Category" UniqueName="Category">
                </telerik:PivotGridRowField>
                <telerik:PivotGridColumnField DataField="[Date].[Fiscal Year]" Caption="Year" UniqueName="FiscalYear">
                </telerik:PivotGridColumnField>
                <telerik:PivotGridAggregateField DataField="[Measures].[Internet Sales Amount]" Caption="Internet Sales Amount"
                    UniqueName="SalesAmount">
                </telerik:PivotGridAggregateField>
                <telerik:PivotGridAggregateField DataField="[Measures].[Internet Revenue Goal]" Caption="Revenue Goal"
                    UniqueName="RevenueGoal">
                </telerik:PivotGridAggregateField>
                <telerik:PivotGridAggregateField DataField="[Measures].[Internet Revenue Status]"
                    Caption="Revenue Status" UniqueName="SalesStatus">
                </telerik:PivotGridAggregateField>
                <telerik:PivotGridAggregateField DataField="[Measures].[Internet Revenue Trend]"
                    Caption="Revenue Trend" UniqueName="SalesTrend">
                </telerik:PivotGridAggregateField>
            </Fields>
            <ConfigurationPanelSettings Position="Left" LayoutType="OneByFour" DefaultDeferedLayoutUpdate="true" />
            <ClientSettings>
                <Scrolling AllowVerticalScroll="false" ScrollHeight="600px" />
            </ClientSettings>
        </telerik:RadPivotGrid>
    </div>
    </div>
    <telerik:ConfiguratorPanel runat="server" ID="ConfiguratorPanel1">
        <Views>
            <qsf:View>
                <qsf:RadioButtonList ID="rdlProviderType" runat="server" AutoPostBack="true" 
                    Label="Set the olap data provider"
                    OnSelectedIndexChanged="rdlProviderType_SelectedIndexChanged">
                    <asp:ListItem Text="Xmla" Value="Xmla" Selected="True">
                    </asp:ListItem>
                    <asp:ListItem Text="Adomd" Value="Adomd"></asp:ListItem>
                </qsf:RadioButtonList>
            </qsf:View>
        </Views>
    </telerik:ConfiguratorPanel>
    </form>
</body>
</html>

Support & Learning Resources

Find Assistance