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,501,294
2,463,125
102%
572,018
575,271
99%
311,513
343,432
91%
126,032
56,382
224%
32,481
131,736
25%
153,000
155,314
99%
260,505
231,839
112%
119,307
48,141
248%
23,915
97,396
25%
117,283
86,302
136%
615,270
639,704
96%
299,410
275,771
109%
152,570
73,305
208%
24,281
70,808
34%
122,559
131,658
93%
315,860
363,933
87%
150,604
85,399
176%
26,331
112,047
23%
138,925
166,487
83%
643,376
629,619
102%
292,294
276,185
106%
149,464
79,763
187%
24,181
68,401
35%
118,649
128,021
93%
351,082
353,434
99%
180,141
92,412
195%
35,714
128,897
28%
135,227
132,125
102%
670,630
618,531
108%
370,868
348,169
107%
205,460
129,810
158%
32,544
108,169
30%
132,864
110,190
121%
299,762
270,362
111%
169,641
84,094
202%
21,958
71,645
31%
108,163
114,623
94%

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