Features

Calculated Aggregates

Calculated Aggregates

The PivotEngine supports 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
Sales:0;
Downloads:0;
Conversion:0;
2,552,571
2,590,510
99%
603,525
620,975
97%
248,596
215,587
115%
133,298
47,009
284%
22,806
70,917
32%
92,492
97,661
95%
354,929
405,388
88%
188,741
130,965
144%
46,158
158,026
29%
120,030
116,397
103%
702,486
662,152
106%
374,571
369,513
101%
208,970
139,729
150%
36,361
73,209
50%
129,240
156,575
83%
327,915
292,639
112%
191,836
114,824
167%
25,382
80,128
32%
110,697
97,687
113%
588,855
621,870
95%
364,644
369,941
99%
142,442
78,294
182%
40,158
130,005
31%
182,044
161,642
113%
224,211
251,929
89%
85,162
44,830
190%
32,934
103,861
32%
106,115
103,238
103%
657,705
685,513
96%
357,450
390,667
91%
189,327
122,495
155%
26,773
114,082
23%
141,350
154,090
92%
300,255
294,846
102%
203,466
135,584
150%
23,171
79,019
29%
73,618
80,243
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 System.Web.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
60
61
62
63
64
65
66
@using C1.Web.Mvc.Grid
@model IEnumerable<ProductData>
 
@(Html.C1().PivotEngine().Id("pvEngine")
        .ShowRowTotals(ShowTotals.Subtotals)
        .ShowColumnTotals(ShowTotals.Subtotals)
        .TotalsBeforeData(true)
        .Bind(Model)
        .Fields(pfcb => pfcb.Items(fif =>
        {
            fif.AddPivotField(pfb => pfb.Header("Country").Binding("Country"));
            fif.AddPivotField(pfb => pfb.Header("Product").Binding("Product"));
            fif.AddPivotField(pfb => pfb.Header("Date").Binding("Date").Format("yyyy \"Q\"q"));
            fif.AddPivotField(pfb => pfb.Header("Range").DataType(DataType.String).Aggregate(Aggregate.Cnt).GetValue("getValue"));
            fif.AddPivotField(pfb => pfb.Header("Sales").Binding("Sales").Format("n0"));
            fif.AddPivotField(pfb => pfb.Header("Downloads").Binding("Downloads").Format("n0"));
            fif.AddPivotField(pfb => pfb.Header("Conversion").DataType(DataType.Number).Format("p0").GetAggregateValue("getAggregateValue"));           
        }))
        .RowFields(pfcb => pfcb.Items("Product"))
        .RowFields(pfcb => pfcb.Items("Date"))
        .RowFields(pfcb => pfcb.Items("Range"))
        .ValueFields(vfcb => vfcb.Items("Sales"))
        .ValueFields(vfcb => vfcb.Items("Downloads"))
        .ValueFields(vfcb => vfcb.Items("Conversion")))
 
<div class="row">
    <div class="col-sm-4 col-md-4">
        @Html.C1().PivotPanel().Id("pvPanel").ItemsSourceId("pvEngine")
    </div>
    <div class="col-sm-8 col-md-8">   
        @(Html.C1().PivotGrid().Id("pvGrid")
            .ItemsSourceId("pvEngine")
            .OutlineMode(false)
            .ShowValueFieldHeaders(false))
    </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 Description{
     
        @Html.Raw(Resources.Olap.CalculatedAggregates_Text1)
    <ul>
        <li>@Html.Raw(Resources.Olap.CalculatedAggregates_Text2)</li>
        <li>@Html.Raw(Resources.Olap.CalculatedAggregates_Text3)</li>
    </ul>
        @Html.Raw(Resources.Olap.CalculatedAggregates_Text4)
    <br />
        @Html.Raw(Resources.Olap.CalculatedAggregates_Text5)
     
}
 
@section Summary{
    <p>@Html.Raw(Resources.Olap.CalculatedAggregates_Text0)</p>
}