Features

Calculated Aggregates

Calculated Aggregates

The PivotEngine support GetAggregateValue function in PivotField to calculate aggregate field for each summary row.

Features

Overview
Slicer
Remote Bind
DataEngine Service(DataEngine)
DataEngine Service(DataSource)
DataEngine Service(SSAS)
SSAS (OLAP Cube)
Calculated Aggregates
header
header
header
header
Product
Date
Range
header
Sales (Sum)
Downloads (Sum)
Conversion (Sum)
Sales:0;
Downloads:0;
Conversion:0;
2,442,775
2,501,668
98%
633,684
684,015
93%
280,807
280,942
100%
147,951
73,246
202%
33,928
100,755
34%
98,928
106,941
93%
352,877
403,073
88%
174,526
130,204
134%
50,161
150,732
33%
128,190
122,137
105%
514,712
519,400
99%
240,354
269,597
89%
128,356
78,941
163%
19,800
78,977
25%
92,198
111,679
83%
274,358
249,803
110%
175,483
97,791
179%
17,238
84,226
20%
81,637
67,786
120%
681,634
660,003
103%
378,463
367,903
103%
188,675
107,108
176%
29,573
109,972
27%
160,215
150,823
106%
303,171
292,100
104%
158,042
80,820
196%
35,697
109,049
33%
109,432
102,231
107%
612,745
638,250
96%
278,317
313,519
89%
144,139
92,985
155%
40,534
146,154
28%
93,644
74,380
126%
334,428
324,731
103%
208,117
100,238
208%
26,413
119,174
22%
99,898
105,319
95%

Description

PivotField objects have two properties as function that support custom calculations:
  • The GetValue function takes a data item as a parameter and returns a value that is calculated based on other properties of the data item. The function has no access to any aggregate information.
  • The GetAggregateValue function takes a summary row as a parameter and returns a value to be displayed as an aggregate for the field. The function has no access to the individual/raw data items.
This sample uses the GetValue function to calculate a Range field that categorizes raw sales value into three levels: "High", "Medium", or "Low".
And uses GetAggregateValue function to calculate a Conversion field that shows the Sales/Downloads ratio for each summary row.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
using OlapExplorer.Models;
using System.Collections;
using Microsoft.AspNetCore.Mvc;
using System.Linq;
using System.Collections.Generic;
using C1.Web.Mvc.Olap;
  
namespace OlapExplorer.Controllers.Olap
{
    public partial class OlapController : Controller
    {
        // GET: PivotGrid
        public ActionResult CalculatedAggregates()
        {
            return View(ProductData.GetData(500));
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
@using C1.Web.Mvc.Grid
@model IEnumerable<ProductData>
  
<c1-pivot-engine id="pvEngine" show-row-totals="Subtotals" show-column-totals="Subtotals" totals-before-data="true">
    <c1-items-source source-collection="Model"></c1-items-source>
    <c1-pivot-field-collection>
        <c1-pivot-field header="Country" binding="Country"></c1-pivot-field>
        <c1-pivot-field header="Product" binding="Product"></c1-pivot-field>
    <c1-pivot-field header="Date" binding="Date" format=@("yyyy \"Q\"q")></c1-pivot-field>
        <c1-pivot-field header="Range" type="DataType.String" aggregate="Cnt" get-value="getValue"></c1-pivot-field>
        <c1-pivot-field header="Sales" binding="Sales" format="n0"></c1-pivot-field>
        <c1-pivot-field header="Downloads" binding="Downloads" format="n0"></c1-pivot-field>
    <c1-pivot-field header="Conversion" type="DataType.Number" get-aggregate-value="getAggregateValue" format="p0"></c1-pivot-field>
    </c1-pivot-field-collection>
    <c1-view-field-collection c1-property="RowFields" items="Product,Date,Range"></c1-view-field-collection>
    <c1-view-field-collection c1-property="ValueFields" items="Sales,Downloads,Conversion"></c1-view-field-collection>
</c1-pivot-engine>
  
<div class="row">
    <div class="col-sm-4 col-md-4">
        <c1-pivot-panel id="pvPanel" items-source-id="pvEngine"></c1-pivot-panel>
    </div>
    <div class="col-sm-8 col-md-8">
        <c1-pivot-grid id="pvGrid" items-source-id="pvEngine" outline-mode="false" show-value-field-headers="false"></c1-pivot-grid>
    </div>
</div>
  
@section Scripts{
    <script type="text/javascript">
        function getValue(item) {
            let sales = item.Sales;
            return sales <= 3000 ? 'Low' : sales <= 7000 ? 'Medium' : 'High';
        }
  
        function getAggregateValue(row) {
            return row.Downloads ? row.Sales / row.Downloads : 0;
        }
    </script>
}
  
@section Settings{
}
  
@section Description{
  
        @Html.Raw(OlapRes.CalculatedAggregates_Text1)
  <ul>
    <li>@Html.Raw(OlapRes.CalculatedAggregates_Text2)</li>
    <li>@Html.Raw(OlapRes.CalculatedAggregates_Text3)</li>
  </ul>
    @Html.Raw(OlapRes.CalculatedAggregates_Text4)
  <br />
    @Html.Raw(OlapRes.CalculatedAggregates_Text5)
  
}
  
@section Summary{
    <p>@Html.Raw(OlapRes.CalculatedAggregates_Text0)</p>
}