PivotGrid - Calculations

TotalPriceQuantityAverage PriceYear
CategoryProductName
199619971998Forecast for 1999Total Sum of TotalPriceTotal Sum of QuantityTotal Average Price
Sum of TotalPriceSum of QuantityAverage PriceSum of TotalPriceSum of QuantityAverage PriceSum of TotalPriceSum of QuantityAverage PriceSum of TotalPriceSum of QuantityAverage Price
$1,800.00125$14.40$5,295.60304$17.42$7,182.00399$18.00$7,521.75415$18.12$21,799.351243$17.54
$3,435.20226$15.20$7,600.00435$17.47$7,524.00396$19.00$5,895.70283.5$20.80$24,454.901340.5$18.24
$3,830.40266$14.40$4,928.40283$17.41$4,392.00244$18.00$3,510.90203.5$17.25$16,661.70996.5$16.72
$29,512.00140$210.80$51,962.20223$233.01$68,510.00260$263.50$74,570.50261.25$285.44$224,554.70884.25$253.95
$568.80158$3.60$1,756.80421$4.17$2,457.00546$4.50$2,624.18556.75$4.71$7,406.781681.75$4.40
$5,004.80136$36.80$11,518.40258$44.64$8,556.00186$46.00$3,521.3077.25$45.58$28,600.50657.25$43.52
$2,102.40146$14.40$7,707.60447$17.24$6,984.00388$18.00$4,248.90223.5$19.01$21,042.901204.5$17.47
$56.005$11.20$910.0065$14.00$1,596.00114$14.00$1,876.00134.375$13.96$4,438.00318.375$13.94
$1,872.00156$12.00$5,880.00413$14.24$3,720.00248$15.00$327.007.25$45.10$11,799.00824.25$14.31
$744.00120$6.20$4,767.80630$7.57$3,138.75405$7.75$204.4116.875$12.11$8,854.961171.875$7.56
$226,298.509581$23.62$658,388.7525489$25.83$469,771.3416247$28.91$272,407.297800.75$34.92$1,626,865.8859117.75$27.52
BeveragesChai
Chang
Chartreuse verte
Côte de Blaye
Guaraná Fantástica
Ipoh Coffee
Lakkalikööri
Laughing Lumberjack Lager
Outback Lager
Rhönbräu Klosterbier
Grand Total
Page size:
Isolate this demo as a stand-alone application
  • About this demo
  • C#
  • VB

This demo shows two types of custom data calculations. Calculated fields and calculated items. Such calculated data in RadPivotGrid extends the data available directly from the Database, by providing expression or making more complex calculations.

  • The calculated field used in the demo is the "Average Price" field.
    The difference between calculated AggregateField regular AggregateField in the declaration of RadPivotGrid is the presence of CalculationDataFields and CalculationExpression properties.
    • CalculationDataFields - DataFields that the calculation is based on.
    • CalculationExpression - The expression used for calculation.
  • Calculated item is such item that do not in the database, but it is calculated by custom server side logic.
    Such items in this demo are the"Forecast for 1999" items in the group shown as part of "Year" DataField.
    To add such items you need to declarate PivotGridCalculatedItem inside CalculatedItems collection of PivotGridColumnField or PivotGridRowField
    To perform the custom calculation for given item, you will need to handle the OnItemNeedCalculation of RadPivotGrid.
  • DefaultCS.aspx
  • DefaultCS.aspx.cs
<%@ Page Language="c#" CodeFile="DefaultCS.aspx.cs" Inherits="Telerik.PivotGridExamplesCSharp.Calculatons.DefaultCS" %>

<%@ Register TagPrefix="telerik" Namespace="Telerik.Web.UI" Assembly="Telerik.Web.UI" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns='http://www.w3.org/1999/xhtml'>
<head runat="server">
    <title>Telerik ASP.NET Example</title>
</head>
<body>
    <form id="form1" runat="server">
    <telerik:RadScriptManager runat="server" ID="RadScriptManager1" />
    <telerik:RadSkinManager ID="RadSkinManager1" runat="server" ShowChooser="true" />
    <telerik:RadAjaxManager ID="RadAjaxManager1" runat="server">
        <AjaxSettings>
            <telerik:AjaxSetting AjaxControlID="RadPivotGrid1">
                <UpdatedControls>
                    <telerik:AjaxUpdatedControl ControlID="RadPivotGrid1" LoadingPanelID="RadAjaxLoadingPanel1" />
                </UpdatedControls>
            </telerik:AjaxSetting>
        </AjaxSettings>
    </telerik:RadAjaxManager>
    <telerik:RadAjaxLoadingPanel ID="RadAjaxLoadingPanel1" runat="server"></telerik:RadAjaxLoadingPanel>
    <telerik:RadPivotGrid RenderMode="Lightweight" AllowPaging="true" OnNeedDataSource="RadPivotGrid1_NeedDataSource"
        ID="RadPivotGrid1" runat="server" ColumnHeaderZoneText="ColumnHeaderZone"
        ShowFilterHeaderZone="false" AllowFiltering="false" OnItemNeedCalculation="RadPivotGrid1_ItemNeedCalculation"
        OnCellDataBound="RadPivotGrid1_CellDataBound">
        <Fields>
            <telerik:PivotGridRowField DataField="Category">
            </telerik:PivotGridRowField>
            <telerik:PivotGridRowField DataField="ProductName">
            </telerik:PivotGridRowField>
            <telerik:PivotGridColumnField DataField="Year">
                <CalculatedItems>
                    <telerik:PivotGridCalculatedItem GroupName="Forecast for 1999"></telerik:PivotGridCalculatedItem>
                </CalculatedItems>
            </telerik:PivotGridColumnField>
            <telerik:PivotGridAggregateField DataField="TotalPrice" Aggregate="Sum" DataFormatString="{0:C}">
            </telerik:PivotGridAggregateField>
            <telerik:PivotGridAggregateField DataField="Quantity" Aggregate="Sum">
            </telerik:PivotGridAggregateField>
            <telerik:PivotGridAggregateField DataField="Average Price" CalculationDataFields="TotalPrice,Quantity" CalculationExpression="{0}/{1}" DataFormatString="{0:C}" CellStyle-Font-Italic="true">
            </telerik:PivotGridAggregateField>
        </Fields>
        <SortExpressions>
            <telerik:PivotGridSortExpression FieldName="TotalPrice" SortOrder="Descending" />
        </SortExpressions>
    </telerik:RadPivotGrid>
    </form>
</body>
</html>

Find Assistance

Help Us Improve

Was this example helpful?