Features

Overview

Overview

The PivotEngine component binds to an IEnumerable object.

Features

header
header
header
Product
header
Country
Date
header
Sales (Sum)
Sales:0;
Product:Aoba;Sales:0;
Product:Olap;Sales:0;
Product:Wijmo;Sales:0;
Product:Xuni;Sales:0;
4,945,038
1,178,564
1,364,739
1,187,083
1,214,652
457,861
145,145
118,476
88,151
106,089

OutlineMode  ShowValueFieldHeaders 

You can use the viewDefinition property to save and restore view definitions, as shown in the following example:

The PivotGrid control extends the FlexGrid control, so you can export it to any of
the formats supported by the extension modules provided with the FlexGrid. The
list of the supported formats includes .xlsx, .csv, and .pdf.

For example, clicking the button below creates an Excel file with two sheets: the current view and a transposed version of the current view:

Settings

Description

In this sample, the PivotEngine binds to an IEnumerable object which provides the data to be aggregated.
All the data will be transferred to the client. The PivotEngine will calculate the data at the client-side.
The PivotPanel control, the PivotGrid control binds the PivotEngine.
You can change the view definition in the PivotPanel control.
The PivotGrid control reflects the changes in the aggregated data.
You can find the detailed raw data shown in a grid by double-clicking a cell in the PivotGrid control.
You can use OutlineMode property of PivotGrid to compact the amount of white space shown on the screen.
You can also force showing headers of value fields in PivotGrid by using showValueFieldHeaders property even if only one value field available.

When the data row count is lower than 10,000, you can use Bind(data) or Bind(url) mode.
Otherwise, please use BindService(url) mode.

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
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
    {
        private static IEnumerable Data = ProductData.GetData(1000).ToList();
 
        private readonly ClientSettingsModel OlapModel = new ClientSettingsModel
        {
            Settings = new Dictionary<string, object[]>
            {
                {"RowTotals", new object[] { ShowTotals.Subtotals, ShowTotals.None, ShowTotals.GrandTotals} },
                {"ColumnTotals", new object[] { ShowTotals.Subtotals, ShowTotals.None, ShowTotals.GrandTotals} },
                {"ShowZeros", new object[] { false, true } },
                { "AllowMerging", new object[] {
                  C1.Web.Mvc.Grid.AllowMerging.All,
                  C1.Web.Mvc.Grid.AllowMerging.AllHeaders,
                  C1.Web.Mvc.Grid.AllowMerging.Cells,
                  C1.Web.Mvc.Grid.AllowMerging.ColumnHeaders,
                  C1.Web.Mvc.Grid.AllowMerging.None,
                  C1.Web.Mvc.Grid.AllowMerging.RowHeaders
                } }
            }
        };
 
        // GET: PivotGrid
        public ActionResult Index()
        {
            OlapModel.ControlId = "indexPanel";
            ViewBag.DemoSettingsModel = OlapModel;
            return View(Data);
        }
    }
}
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
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
@model IEnumerable<ProductData>
@{
    ClientSettingsModel optionsModel = ViewBag.DemoSettingsModel;
}
 
@(Html.C1().PivotEngine().Id("indexEngine")       
        .ShowRowTotals(ShowTotals.Subtotals)
        .TotalsBeforeData(true)
        .Bind(Model)
        .RowFields(pfcb => pfcb.Items("Country"))
        .RowFields(pfcb => pfcb.Items("Date"))
        .ColumnFields(cfcb=>cfcb.Items("Product"))
        .ValueFields(vfcb => vfcb.Items("Sales")))
 
<div class="row">
    <div class="col-sm-4 col-md-4">
        @Html.C1().PivotPanel().Id(optionsModel.ControlId).ItemsSourceId("indexEngine")
    </div>
    <div class="col-sm-8 col-md-8">   
        @(Html.C1().PivotGrid().Id("indexGrid")
            .ItemsSourceId("indexEngine")
            .OutlineMode(false)
            .ShowValueFieldHeaders(false))
        <p>
            <span style="vertical-align:top; color:black;">
                OutlineMode&nbsp;<input id="outlineMode" type="checkbox" onchange="toggleOulineMode(event)" style="margin-right:5em;" />
                ShowValueFieldHeaders&nbsp;<input id="ShowValueFieldHeaders" type="checkbox" onchange="toggleShowValueFieldHeaders(event)" />
            </span>
        </p>
    </div>
</div>
<p>@Html.Raw(Resources.Olap.Index_Text0)</p>
 
<button type="button" class="btn btn-default" onclick="saveView()">@Html.Raw(Resources.Olap.Index_Text7)</button>
<button type="button" class="btn btn-default" onclick="loadView()">@Html.Raw(Resources.Olap.Index_Text8)</button>
<p></p>
<p>@Html.Raw(Resources.Olap.Index_Text2)</p>
 
<p>@Html.Raw(Resources.Olap.Index_Text3)</p>
 
<button type="button" class="btn btn-default"
        onclick="excelExport()">
    @Html.Raw(Resources.Olap.Index_Text9)
</button>
@section Scripts{
    <script type="text/javascript">
        function saveView() {
            var ng = c1.getService('indexEngine');
            if (ng && ng.isViewDefined) {
                localStorage.viewDefinition = ng.viewDefinition;
            }
        }
        function loadView() {
            var ng = c1.getService('indexEngine');
            if (ng && localStorage.viewDefinition) {
                ng.viewDefinition = localStorage.viewDefinition;
                var cmbRowTotals = wijmo.Control.getControl('#RowTotals');
                if (cmbRowTotals) {
                    cmbRowTotals.selectedValue = ng.showRowTotals;
                }
 
                var cmbColTotals = wijmo.Control.getControl('#ColTotals');
                if (cmbColTotals) {
                    cmbColTotals.selectedValue = ng.showColumnTotals;
                }
 
                var chkShowZeros = document.getElementById('ColTotals');
                if (chkShowZeros) {
                    chkShowZeros.checked = ng.showZeros;
                }
            }
        }
        function excelExport() {
            var pivotGrid = wijmo.Control.getControl('#indexGrid');
 
            // create book with current view
            var book = wijmo.grid.xlsx.FlexGridXlsxConverter.save(pivotGrid, {
                includeColumnHeaders: true,
                includeRowHeaders: true
            });
            book.sheets[0].name = 'Main View';
            addTitleCell(book.sheets[0], getViewTitle(pivotGrid.engine));
 
            // add sheet with transposed view
            transposeView(pivotGrid.engine);
            var transposed = wijmo.grid.xlsx.FlexGridXlsxConverter.save(pivotGrid, {
                includeColumnHeaders: true,
                includeRowHeaders: true
            });
            transposed.sheets[0].name = 'Transposed View';
            addTitleCell(transposed.sheets[0], getViewTitle(pivotGrid.engine));
            book.sheets.push(transposed.sheets[0]);
            transposeView(pivotGrid.engine);
 
            // save the book
            book.save('wijmo.olap.xlsx');
        }
 
        // build a title for the current view
        function getViewTitle(ng) {
            var title = '';
            for (var i = 0; i < ng.valueFields.length; i++) {
                if (i > 0) title += ', ';
                title += ng.valueFields[i].header;
            }
            title += ' by ';
            if (ng.rowFields.length) {
                for (var i = 0; i < ng.rowFields.length; i++) {
                    if (i > 0) title += ', ';
                    title += ng.rowFields[i].header;
                }
            }
            if (ng.rowFields.length && ng.columnFields.length) {
                title += ' and by ';
            }
            if (ng.columnFields.length) {
                for (var i = 0; i < ng.columnFields.length; i++) {
                    if (i > 0) title += ', ';
                    title += ng.columnFields[i].header;
                }
            }
            return title;
        }
 
        function transposeView(ng) {
            ng.deferUpdate(function () {
 
                // save row/col fields
                var rows = [],
                    cols = [];
                for (var r = 0; r < ng.rowFields.length; r++) {
                    rows.push(ng.rowFields[r].header);
                }
                for (var c = 0; c < ng.columnFields.length; c++) {
                    cols.push(ng.columnFields[c].header);
                }
 
                // clear row/col fields
                ng.rowFields.clear();
                ng.columnFields.clear();
 
                // restore row/col fields in transposed order
                for (var r = 0; r < rows.length; r++) {
                    ng.columnFields.push(rows[r]);
                }
                for (var c = 0; c < cols.length; c++) {
                    ng.rowFields.push(cols[c]);
                }
            });
        }
 
        // adds a title cell into an xlxs sheet
        function addTitleCell(sheet, title) {
 
            // create cell
            var cell = new wijmo.xlsx.WorkbookCell();
            cell.value = title;
            cell.style = new wijmo.xlsx.WorkbookStyle();
            cell.style.font = new wijmo.xlsx.WorkbookFont();
            cell.style.font.bold = true;
 
            // create row to hold the cell
            var row = new wijmo.xlsx.WorkbookRow();
            row.cells[0] = cell;
 
            // and add the new row to the sheet
            sheet.rows.splice(0, 0, row);
        }
 
        // toggle outline mode     
        function toggleOulineMode(e) {
            var pivotGrid = wijmo.Control.getControl('#indexGrid');
            pivotGrid.outlineMode = e.target.checked;   
        }
        // toggle ShowValueFieldHeaders  
        function toggleShowValueFieldHeaders(e) {
            var pivotGrid = wijmo.Control.getControl('#indexGrid');
            pivotGrid.showValueFieldHeaders = e.target.checked;
        }
    </script>
}
 
@section Description{
<p>
    @Html.Raw(Resources.Olap.Index_Text4)<br />
    @Html.Raw(Resources.Olap.Index_Text10)
</p>
<p>@Html.Raw(Resources.Olap.Index_Text5)</p>
 
}
@section Summary{
<p>@Html.Raw(Resources.Olap.Index_Text6)</p>
 
}