Features

Overview

Overview

OLAP Component displays data from OData Invoice table using ADO.NET Connector for OData.

Change provider

header
header
header
Year
header
Country
header
Amount (Sum)
Year:1996;Amount:0;
Year:1997;Amount:0;
Year:1998;Amount:0;
Year:2019;Amount:0;
Amount:0;
238
1,272
305
1,815
17,170
50,624
35,321
10,122
113,237
6,521
19,003
25,529
51,053
1,833
8,883
11,105
1,310
23,130
15,714
41,374
13,468
1,102
71,658
16,738
36,977
37,701
858
92,273
9,142
25,379
22,796
57,317
231
1,314
1,546
593
7,252
1,174
9,019
1,322
5,762
68
7,152
2,360
8,053
7,552
17,965
7,415
28,025
24,084
59,524
1,799
6,658
4,429
12,886
3,710
16,993
22,093
42,795
338
12,231
9,853
710
23,131
2,221
13,869
9,010
25,100
86,875
281,551
226,769
14,406
609,602

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.

Amount by Country and Year1996199719982019ArgentinaAustriaBrazilCanadaFranceGermanyIrelandItalyMexicoPortugalSpainSwedenSwitzerlandUKUSAVenezuela0

Settings

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
using DataConnectorExplorer.Models;
using System.Collections;
using Microsoft.AspNetCore.Mvc;
using System.Linq;
using System.Collections.Generic;
using C1.Web.Mvc.Olap;
using C1.AdoNet.OData;
using System.Globalization;
using System;
using Microsoft.Extensions.Options;
using System.IO;
  
namespace DataConnectorExplorer.Controllers
{
    /// <summary>
    /// Controller for OData
    /// </summary>
    public class IndexController : Controller
    {
        private readonly IOptions<ODataConnection> config;
        /// <summary>
        /// 
        /// </summary>
        /// <param name="config"></param>
        public IndexController(IOptions<ODataConnection> config)
        {
            this.config = config;
        }
        private IEnumerable<ODataInvoiceData> GetData()
        {
            //Create the connection to OData server
            //connstr is connection string to OData server
            //Exp:
            //const string url = "...";
            //static string connstr = "Url={url};Max Page Size = 100"
  
            var config = this.config.Value;
            string _cacheFolder = "Cache";
            if (!Directory.Exists(_cacheFolder))
            {
                Directory.CreateDirectory(_cacheFolder);
            }
            string specifiedCacheFile = Path.Combine(_cacheFolder, "C1CacheFile.OData.db");
            string connectionStringCachePart = string.Format("Use Cache = 'true'; Cache Location='{0}'; Cache Tolerance = 28800", specifiedCacheFile);
  
            var conectionStr = $@"Url={config.Url};Max Page Size = 100;";
            conectionStr += connectionStringCachePart;
            using (C1ODataConnection con = new C1ODataConnection(conectionStr))
            {
                //Open the connection to OData server
                con.Open();
                var cmd = con.CreateCommand();
                //query to select from OData server
                cmd.CommandText = "Select UnitPrice, Quantity, PostalCode, Country, Address, City, Region, CustomerName, ShippedDate from Invoices limit 1000";
                var reader = cmd.ExecuteReader();
                List<ODataInvoiceData> result = new List<ODataInvoiceData>();
                CultureInfo provider = CultureInfo.InvariantCulture;
                //Generate List<ODataInvoiceData> from reader
                while (reader.Read())
                {
                    var unitPrice = String.IsNullOrEmpty(reader["UnitPrice"].ToString()) ? 0 : Convert.ToDecimal(reader["UnitPrice"].ToString());
                    var quantity = String.IsNullOrEmpty(reader["Quantity"].ToString()) ? 0 : Convert.ToDecimal(reader["Quantity"].ToString());
                    var amount = unitPrice * quantity;
                    var country = reader["Country"].ToString();
                    var city = reader["City"].ToString();
                    var region = reader["Region"].ToString();
                    var shippedDateValue = reader["ShippedDate"].ToString();
                    DateTime? shippedDate;
                    if (!String.IsNullOrEmpty(shippedDateValue) && DateTime.TryParse(shippedDateValue, out DateTime convertedDate))
                    {
                        shippedDate = convertedDate;
                    }
                    else
                    {
                        //Put the default value if shippedDate is null. Just use for demo
                        shippedDate = new DateTime(2019, 12, 1);
                    }
                    var customerName = reader["CustomerName"].ToString();
                    //Add ODataInvoiceData model from the reader
                    result.Add(new ODataInvoiceData
                    {
                        Amount = amount,
                        Country = country,
                        City = city,
                        Region = region,
                        CustomerName = customerName,
                        ShippedDate = shippedDate.Value.ToString(CultureInfo.InvariantCulture),
                        Year = shippedDate.Value.Year.ToString(),
                        Month = shippedDate.Value.ToString("MMMM"),
                    });
                    ;
                }
                return result;
            }
        }
  
        private static string StandardizeName(string input)
        {
            if (!String.IsNullOrEmpty(input))
            {
                return input.First().ToString().ToUpper() + input.Substring(1).ToLower();
            }
            else
            {
                return "";
            }
        }
  
        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
                    }
                }
            }
        };
        /// <summary>
        /// Action Index
        /// </summary>
        /// <returns></returns>
        public IActionResult Index()
        {
            IEnumerable Data = GetData().ToList();
            OlapModel.ControlId = "indexPanel";
            ViewBag.DemoOptions = 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
196
197
198
199
@model IEnumerable<ODataInvoiceData>
@{
    ClientSettingsModel optionsModel = ViewBag.DemoOptions;
}
  
<c1-pivot-engine id="indexEngine" show-row-totals="Subtotals" show-column-totals="Subtotals" totals-before-data="false">
    <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="City" binding="City"></c1-pivot-field>
        <c1-pivot-field header="Region" binding="Region"></c1-pivot-field>
        <c1-pivot-field header="Year" binding="Year"></c1-pivot-field>
        <c1-pivot-field header="Month" binding="Month"></c1-pivot-field>
        <c1-pivot-field header="Customer Name" binding="CustomerName"></c1-pivot-field>
        <c1-pivot-field header="Amount" binding="Amount"></c1-pivot-field>
    </c1-pivot-field-collection>
    <c1-view-field-collection c1-property="RowFields" items="Country"></c1-view-field-collection>
    <c1-view-field-collection c1-property="ColumnFields" items="Year"></c1-view-field-collection>
    <c1-view-field-collection c1-property="ValueFields" items="Amount" ca></c1-view-field-collection>
  
</c1-pivot-engine>
  
<div class="row">
    <div class="col-sm-4 col-md-4">
        <c1-pivot-panel id="@(optionsModel.ControlId)" items-source-id="indexEngine"></c1-pivot-panel>
    </div>
    <div class="col-sm-8 col-md-8">
        <c1-pivot-grid id="indexGrid" items-source-id="indexEngine" outline-mode="false" show-value-field-headers="false"></c1-pivot-grid>
        <p>
            <span style="vertical-align:top; color:black;">
                ShowValueFieldHeaders&nbsp;<input id="ShowValueFieldHeaders" type="checkbox" onchange="toggleShowValueFieldHeaders(event)" />
            </span>
        </p>
    </div>
</div>
  
<p>@Html.Raw(OlapRes.Index_Text0)</p>
  
<button type="button" class="btn btn-default" onclick="saveView()">@Html.Raw(OlapRes.Index_Text7)</button>
<button type="button" class="btn btn-default" onclick="loadView()">@Html.Raw(OlapRes.Index_Text8)</button>
<p></p>
<p>@Html.Raw(OlapRes.Index_Text2)</p>
  
<p>@Html.Raw(OlapRes.Index_Text3)</p>
  
<button type="button" class="btn btn-default"
        onclick="excelExport()">
    @Html.Raw(OlapRes.Index_Text9)
</button>
  
<c1-pivot-chart id="demoChart" items-source-id="indexEngine"></c1-pivot-chart>
  
@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 xlsx 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 Settings{
    @Html.Partial("_OptionsMenu", optionsModel)
}
  
@section Summary{
    <p>@Html.Raw(OlapRes.Provider_OData_Description)</p>
  
}