Features

Calculated Aggregates

Calculated Aggregates

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

Features

header
header
header
header
Product
Date
Range
header
Sales (Sum)
Downloads (Sum)
Conversion (Sum)
Sales:0;
Downloads:0;
Conversion:0;
2,485,017
2,458,336
101%
518,031
514,407
101%
279,463
287,531
97%
139,236
74,821
186%
28,543
78,335
36%
111,684
134,375
83%
238,568
226,876
105%
124,149
53,459
232%
24,140
63,112
38%
90,279
110,305
82%
614,565
581,567
106%
313,934
272,883
115%
190,550
78,373
243%
27,658
90,245
31%
95,726
104,265
92%
300,631
308,684
97%
128,353
60,084
214%
37,520
126,969
30%
134,758
121,631
111%
634,126
624,565
102%
322,960
302,265
107%
189,341
117,044
162%
30,294
77,196
39%
103,325
108,025
96%
311,166
322,300
97%
129,683
75,964
171%
28,945
92,965
31%
152,538
153,371
99%
718,295
737,797
97%
389,906
348,165
112%
196,910
91,600
215%
28,382
102,893
28%
164,614
153,672
107%
328,389
389,632
84%
166,537
117,890
141%
36,669
135,903
27%
125,183
135,839
92%

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