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,461,274
2,500,534
98%
582,275
569,999
102%
314,579
312,301
101%
177,051
124,229
143%
18,436
53,747
34%
119,092
134,325
89%
267,696
257,698
104%
153,325
98,309
156%
21,260
86,544
25%
93,111
72,845
128%
562,655
562,031
100%
300,382
297,573
101%
133,083
68,407
195%
39,161
89,939
44%
128,138
139,227
92%
262,273
264,458
99%
118,318
61,254
193%
22,877
76,603
30%
121,078
126,601
96%
721,616
715,982
101%
297,280
311,164
96%
107,175
58,134
184%
32,589
82,292
40%
157,516
170,738
92%
424,336
404,818
105%
269,744
164,196
164%
51,193
154,049
33%
103,399
86,573
119%
594,728
652,522
91%
271,358
329,757
82%
102,308
59,761
171%
27,274
111,026
25%
141,776
158,970
89%
323,370
322,765
100%
123,882
79,954
155%
21,485
100,236
21%
178,003
142,575
125%

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