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,528,195
2,505,346
101%
585,521
584,845
100%
315,752
320,104
99%
181,276
125,659
144%
24,611
92,049
27%
109,865
102,396
107%
269,769
264,741
102%
111,514
50,664
220%
19,763
59,678
33%
138,492
154,399
90%
734,676
729,900
101%
384,680
384,947
100%
216,529
124,072
175%
29,026
105,618
27%
139,125
155,257
90%
349,996
344,953
101%
158,584
127,990
124%
20,403
82,178
25%
171,009
134,785
127%
550,816
533,763
103%
229,441
224,028
102%
115,732
69,251
167%
22,918
79,743
29%
90,791
75,034
121%
321,375
309,735
104%
185,886
122,709
151%
22,290
54,487
41%
113,199
132,539
85%
657,182
656,838
100%
324,526
335,193
97%
173,020
103,540
167%
43,732
121,236
36%
107,774
110,417
98%
332,656
321,645
103%
200,567
125,678
160%
27,004
90,718
30%
105,085
105,249
100%

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>
}