ComponentOne
MVC OLAP 101 ASP.NET MVC Edition OLAP 101

Getting Started

The C1.Web.Mvc.Olap assembly contains controls that allow you to add Excel-like pivot table and pivot chart features to your ASP.NET MVC applications.

Steps for getting started with the Olap module in MVC applications are as follows:

  1. Create a new MVC project using the C1 ASP.NET MVC application template, in Visual Studio IDE.
  2. Add a controller and a corresponding view to the project.
  3. Initialize the PivotEngine control in the view using razor syntax.
  4. Use the Bind method to bind an array containing the raw data that you want to analyze.
  5. Initialize a PivotPanel control and set its ItemsSourceId property to the PivotEngine control if you want to customize the view definition.
  6. Add one or more PivotGrid and PivotChart controls to the page, and connect them to the PivotEngine by setting their ItemsSourceId property to the PivotEngine control or the PivotPanel control.

This will display a list of available fields in the PivotPanel, and users will be able to drag fields between summary areas to generate data summaries (also known as "views"). Users can also configure the fields by setting their headers, summary functions, filters, and formats.

Users can analyze the data and print the results, export them to XLSX or PDF, and save the view definitions to be re-used later.

Here is a simple example.

Adding a PivotEngine, a PivotPanel and a PivotGrid to the page

The PivotPanel control is similar to Microsoft Excel's "Field List" window, which is associated with pivot tables and charts.

  1. Add fields to the view by dragging or using checkboxes.
  2. Remove fields from the view by dragging them back to the top area, or by using their context menu.
  3. Configure the fields by using their context menu. You can modify their header, summary function, and format. Note that the format is used while grouping the data. For instance, you can group the data by day, month, quarter or year by changing the format of the date fields .

The PivotGrid control extends the FlexGrid control to support pivoting features. These include custom cell merging, hierarchical row and column groups, and a custom context menu, which allows users to configure the pivot fields and to drill-down into the data items that were used in the computation of specific summary cells.

Result (live):

@(Html.C1().PivotEngine().Id("pivotEngine") .Bind(Model.Data) .RowFields(pfcb => pfcb.Items("Country")) .ColumnFields(cfcb => cfcb.Items("Product")) .ValueFields(vfcb => vfcb.Items("Sales"))) @Html.C1().PivotPanel().Id("gsPivotPanel").CssStyle("height", "550px").ItemsSourceId("pivotEngine") @Html.C1().PivotGrid().Id("gsPivotGrid").ItemsSourceId("gsPivotPanel")
using System.Collections.Generic; using System.Linq; using System.Web.Mvc; using OLAP101.Models; using C1.Web.Mvc.Olap; namespace OLAP101.Controllers { public class HomeController : Controller { public ActionResult Index() { OLAP101Model model = new OLAP101Model(); model.Data = ProductData.GetData(10000).ToList(); return View(model); } } }

Configure the PivotPanel Properties

The PivotPanel control has properties that allow you to customize the view. Use the controls below to modify the values of some of these properties and see their effect.

Result (live):

@(Html.C1().PivotEngine().Id("pivotEngine") .Bind(Model.Data) .RowFields(pfcb => pfcb.Items("Country")) .ColumnFields(cfcb => cfcb.Items("Product")) .ValueFields(vfcb => vfcb.Items("Sales"))) @Html.C1().PivotPanel().Id(Model.ControlId).CssStyle("height", "550px").ItemsSourceId("pivotEngine") @Html.C1().PivotGrid().Id("cPPPivotGrid").ItemsSourceId(Model.ControlId) @{ if (Model != null && Model.Settings != null && !string.IsNullOrEmpty(Model.ControlId)) { foreach (var setting in Model.Settings) { var propertyName = setting.Key.Replace(".", "_"); var commandName = "change" + propertyName; var customChangeName = "customChange" + propertyName; var converterName = "convert" + propertyName; var headerName = string.Join(" ", Regex.Split(setting.Key.Replace(".", ""), @"(? s.First().ToString().ToLower() + s.Substring(1))); var updateProperty = "update" + propertyName; var defaultValue = setting.Value.First(); if (Model.DefaultValues != null && Model.DefaultValues.ContainsKey(setting.Key)) { defaultValue = Model.DefaultValues[setting.Key]; } <script type="text/javascript"> function @(commandName)(menu) { menu.header = "@(headerName)<b>" + menu.selectedItem.Header + "</b>"; var control = wijmo.Control.getControl("#@(Model.ControlId)"); var value = menu.selectedItem.CommandParameter; if(typeof @(customChangeName) === 'function') { @(customChangeName)(control, value); return; } if(typeof @(updateProperty) === 'function') { @(updateProperty)(control, value); } else { control.@(propertyPaths) = (typeof @(converterName) === 'undefined' ? value : @(converterName)(value)); control.invalidate(); } } </script> @(Html.C1().Menu().Id(propertyName) .Header(headerName + "<b>" + defaultValue + "</b>").MenuItems(items => { foreach (var option in setting.Value) { items.Add().Header(option.ToString()).CommandParameter(option); } }) .OnClientItemClicked(commandName)) } } }
// Configure the PivotPanel Properties function customChangeRowTotals(olapControl, value) { if (olapControl && olapControl.engine) { olapControl.engine.showRowTotals = value; } }; function customChangeColumnTotals(olapControl, value) { if (olapControl && olapControl.engine) { olapControl.engine.showColumnTotals = value; } }; function customChangeShowZeros(olapControl, value) { if (olapControl && olapControl.engine) { olapControl.engine.showZeros = value; } }; function customChangeTotalsBeforeData(olapControl, value) { if (olapControl && olapControl.engine) { olapControl.engine.totalsBeforeData = value; } };
using System.Collections.Generic; using System.Linq; using System.Web.Mvc; using OLAP101.Models; using C1.Web.Mvc.Olap; namespace OLAP101.Controllers { public class HomeController : Controller { private IDictionary<string, object[]> GetSettings() { var 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 } }, {"TotalsBeforeData", new object[] { false, true } } }; return settings; } public ActionResult Index() { OLAP101Model model = new OLAP101Model(); model.Data = ProductData.GetData(10000).ToList(); model.Settings = GetSettings(); model.ControlId = "indexPanel"; return View(model); } } }

Show the Results in a PivotChart

The PivotChart control provides a graphical visualization of the results. It is similar to Microsoft Excel's pivot charts, and includes support for multiple chart types and hierarchical axes.

To use the PivotChart control, connect it to a PivotPanel or PivotEngine using the ItemsSourceId property.

Result (live):

Chart Type :
@(Html.C1().PivotEngine().Id("pivotEngine") .Bind(Model.Data) .RowFields(pfcb => pfcb.Items("Country")) .ColumnFields(cfcb => cfcb.Items("Product")) .ValueFields(vfcb => vfcb.Items("Sales"))) @(Html.C1().PivotPanel().Id("rPCPivotPanel").CssStyle("height", "550px").ItemsSourceId("pivotEngine") Chart Type : @Html.C1().ComboBox().Id("cmbChartType").Bind(Model.Settings["ChartType"]).OnClientSelectedIndexChanged("cmbChartType_SelectedIndexChanged") @Html.C1().PivotChart().Id("rPCPivotChart").ItemsSourceId("rPCPivotPanel")
// Show the Results in a PivotChart function cmbChartType_SelectedIndexChanged(sender) { var value = sender.selectedValue; var control = wijmo.Control.getControl("#rPCPivotChart"); if (control) { control.chartType = value; } };
using System.Collections.Generic; using System.Linq; using System.Web.Mvc; using OLAP101.Models; using C1.Web.Mvc.Olap; namespace OLAP101.Controllers { public class HomeController : Controller { public ActionResult Index() { OLAP101Model model = new OLAP101Model(); model.Data = ProductData.GetData(10000).ToList(); model.Settings = GetSettings(); return View(model); } private IDictionary<string, object[]> GetSettings() { var settings = new Dictionary<string, object[]> { {"ChartType", new object[] { PivotChartType.Column, PivotChartType.Area, PivotChartType.Bar, PivotChartType.Line, PivotChartType.Pie, PivotChartType.Scatter}.Select(item => item.ToString()).ToArray() } }; return settings; } } }

View and Edit the Source Data

The following pivot view is live. If you edit any of the 10,000 items, the pivot view will be updated automatically.

Result (live):

FlexGrid
PivotGrid
@(Html.C1().PivotEngine().Id("pivotEngine") .Bind(Model.Data) .RowFields(pfcb => pfcb.Items("Country")) .ColumnFields(cfcb => cfcb.Items("Product")) .ValueFields(vfcb => vfcb.Items("Sales"))) @(Html.C1().FlexGrid().Id("vESourceFGrid") .ShowSelectedHeaders(C1.Web.Mvc.Grid.HeadersVisibility.All).CssStyle("border", "none") .Filterable()) @Html.C1().PivotGrid().Id("vESourcePivotGrid").ItemsSourceId("pivotEngine")
// view and edit the source data var pivotEngine = null, vESourceFGrid = null; function InitialControls() { // view and edit the source data pivotEngine = c1.getService('pivotEngine'); vESourceFGrid = wijmo.Control.getControl('#vESourceFGrid'); vESourceFGrid.itemsSource = pivotEngine.itemsSource; };
using System.Collections.Generic; using System.Linq; using System.Web.Mvc; using OLAP101.Models; using C1.Web.Mvc.Olap; namespace OLAP101.Controllers { public class HomeController : Controller { public ActionResult Index() { OLAP101Model model = new OLAP101Model(); model.Data = ProductData.GetData(10000).ToList(); return View(model); } } }

Export the Results to Excel

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 three sheets: the current view, a transposed version of the current view, and the raw data.

Result (live):

@(Html.C1().PivotEngine().Id("pivotEngine") .Bind(Model.Data) .RowFields(pfcb => pfcb.Items("Country")) .ColumnFields(cfcb => cfcb.Items("Product")) .ValueFields(vfcb => vfcb.Items("Sales"))) @Html.C1().PivotGrid().Id("exportExcelPivotGrid").ItemsSourceId("pivotEngine") <button class="btn btn-primary" onclick="exportToExcel()">Export to XLSX</button>
// export the result to excel var pivotEngine = null, exportExcelPivotGrid = null; function InitialControls() { // export the result to excel pivotEngine = c1.getService('pivotEngine'); exportExcelPivotGrid = wijmo.Control.getControl('#exportExcelPivotGrid'); } // export to excel function exportToExcel() { // create book with current view var book = wijmo.grid.xlsx.FlexGridXlsxConverter.save(exportExcelPivotGrid, { includeColumnHeaders: true, includeRowHeaders: true }); book.sheets[0].name = 'Main View'; addTitleCell(book.sheets[0], getViewTitle(pivotEngine)); // add sheet with transposed view transposeView(pivotEngine); var transposed = wijmo.grid.xlsx.FlexGridXlsxConverter.save(exportExcelPivotGrid, { includeColumnHeaders: true, includeRowHeaders: true }); transposed.sheets[0].name = 'Transposed View'; addTitleCell(transposed.sheets[0], getViewTitle(pivotEngine)); book.sheets.push(transposed.sheets[0]); transposeView(pivotEngine); // save the book book.save('wijmo.olap.xlsx'); }; // save/load/transpose/export views 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]); } }); } // 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; } // 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); } // gets a random integer between zero and max function randomInt(max) { return Math.floor(Math.random() * (max + 1)); }
using System.Collections.Generic; using System.Linq; using System.Web.Mvc; using OLAP101.Models; using C1.Web.Mvc.Olap; namespace OLAP101.Controllers { public class HomeController : Controller { public ActionResult Index() { OLAP101Model model = new OLAP101Model(); model.Data = ProductData.GetData(10000).ToList(); return View(model); } } }

Customize the PivotGrid Cells

The PivotGrid control extends the FlexGrid control, so you can customize the display of the grid cells using the ItemFormatter event and modify the content of each cell with flexibility.

For example, the PivotGrid control below uses colors and icons, similar to the ones in Microsoft Excel's icon sets, to show how sales changed from quarter to quarter.

Result (live):

@(Html.C1().PivotEngine().Id("pivotEngine") .Bind(Model.Data) .RowFields(pfcb => pfcb.Items("Country")) .ColumnFields(cfcb => cfcb.Items("Product")) .ValueFields(vfcb => vfcb.Items("Sales"))) @Html.C1().PivotPanel().Id("cTPGCPivotPanel").CssStyle("height", "550px").ItemsSourceId("pivotEngine") @(Html.C1().PivotGrid().Id("cTPGCPivotGrid").ItemsSourceId("customizeEngine") .ShowSelectedHeaders(C1.Web.Mvc.Grid.HeadersVisibility.All) .ItemFormatter("cTPGCPivotGrid_ItemFormatter"))
// Customize the PivotGrid Cells function cTPGCPivotGrid_ItemFormatter(panel, r, c, cell) { if (wijmo.grid.CellType.Cell == panel.cellType && c % 2 == 1) { var value = panel.getCellData(r, c), color = '#d8b400', glyph = 'circle'; if (value != null) { if (value < 0) { // negative variation color = '#9f0000'; glyph = 'down'; } else if (value > 0.05) { // positive variation color = '#4c8f00'; glyph = 'down'; } cell.style.color = color; cell.innerHTML += ' <span style="font-size:120%" class="wj-glyph-' + glyph + '"></span>'; } } };
using System.Collections.Generic; using System.Linq; using System.Web.Mvc; using OLAP101.Models; using C1.Web.Mvc.Olap; namespace OLAP101.Controllers { public class HomeController : Controller { public ActionResult Index() { OLAP101Model model = new OLAP101Model(); model.Data = ProductData.GetData(10000).ToList(); return View(model); } } }

Save and Load Views

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

Result (live):

By using the viewDefinition property, you can also build a list of pre-defined views for the user to pick from, as shown in the following example:

    @(Html.C1().PivotEngine().Id("pivotEngine") .Bind(Model.Data) .RowFields(pfcb => pfcb.Items("Country")) .ColumnFields(cfcb => cfcb.Items("Product")) .ValueFields(vfcb => vfcb.Items("Sales"))) @Html.C1().PivotPanel().Id("saveLoadViewsPivotPanel").CssStyle("height", "550px").ItemsSourceId("pivotEngine") @(Html.C1().PivotGrid().Id("saveLoadViewsPivotGrid").ItemsSourceId("saveLoadViewsPivotPanel")) <button class="btn btn-primary" onclick="saveView()">Save View</button> <button class="btn btn-primary" onclick="loadView()">Load View</button> <ul id="views"></ul>
    // save and load views var app = { viewDefs: null }; var saveLoadViewsPivotPanel = null; function InitialControls() { // save and load views // pre-defined views app.viewDefs = [ { name: "Sales by Product", def: "{\"showColumnTotals\":2,\"showRowTotals\":2,\"defaultFilterType\":3,\"fields\":[{\"dataType\":2,\"format\":\"n0\",\"aggregate\":1,\"showAs\":0,\"descending\":false,\"isContentHtml\":false,\"binding\":\"ID\",\"header\":\"ID\"},{\"dataType\":1,\"format\":\"\",\"aggregate\":2,\"showAs\":0,\"descending\":false,\"isContentHtml\":false,\"binding\":\"Product\",\"header\":\"Product\"},{\"dataType\":1,\"format\":\"\",\"aggregate\":2,\"showAs\":0,\"descending\":false,\"isContentHtml\":false,\"binding\":\"Country\",\"header\":\"Country\"},{\"dataType\":4,\"format\":\"d\",\"aggregate\":2,\"showAs\":0,\"descending\":false,\"isContentHtml\":false,\"binding\":\"Date\",\"header\":\"Date\"},{\"dataType\":2,\"format\":\"n0\",\"aggregate\":1,\"showAs\":0,\"descending\":false,\"isContentHtml\":false,\"binding\":\"Sales\",\"header\":\"Sales\"},{\"dataType\":2,\"format\":\"n0\",\"aggregate\":1,\"showAs\":0,\"descending\":false,\"isContentHtml\":false,\"binding\":\"Downloads\",\"header\":\"Downloads\"},{\"dataType\":3,\"format\":\"\",\"aggregate\":2,\"showAs\":0,\"descending\":false,\"isContentHtml\":false,\"binding\":\"Active\",\"header\":\"Active\"},{\"dataType\":2,\"format\":\"n0\",\"aggregate\":1,\"showAs\":0,\"descending\":false,\"isContentHtml\":false,\"binding\":\"Discount\",\"header\":\"Discount\"}],\"rowFields\":{\"items\":[\"Product\"]},\"columnFields\":{\"items\":[]},\"filterFields\":{\"items\":[]},\"valueFields\":{\"items\":[\"Sales\"]}}" }, { name: "Sales by Country", def: "{\"showZeros\":false,\"showColumnTotals\":2,\"showRowTotals\":2,\"defaultFilterType\":3,\"fields\":[{\"dataType\":2,\"format\":\"n0\",\"aggregate\":1,\"showAs\":0,\"descending\":false,\"isContentHtml\":false,\"binding\":\"ID\",\"header\":\"ID\"},{\"dataType\":1,\"format\":\"\",\"aggregate\":2,\"showAs\":0,\"descending\":false,\"isContentHtml\":false,\"binding\":\"Product\",\"header\":\"Product\"},{\"dataType\":1,\"format\":\"\",\"aggregate\":2,\"showAs\":0,\"descending\":false,\"isContentHtml\":false,\"binding\":\"Country\",\"header\":\"Country\"},{\"dataType\":4,\"format\":\"d\",\"aggregate\":2,\"showAs\":0,\"descending\":false,\"isContentHtml\":false,\"binding\":\"Date\",\"header\":\"Date\"},{\"dataType\":2,\"format\":\"n0\",\"aggregate\":1,\"showAs\":0,\"descending\":false,\"isContentHtml\":false,\"binding\":\"Sales\",\"header\":\"Sales\"},{\"dataType\":2,\"format\":\"n0\",\"aggregate\":1,\"showAs\":0,\"descending\":false,\"isContentHtml\":false,\"binding\":\"Downloads\",\"header\":\"Downloads\"},{\"dataType\":3,\"format\":\"\",\"aggregate\":2,\"showAs\":0,\"descending\":false,\"isContentHtml\":false,\"binding\":\"Active\",\"header\":\"Active\"},{\"dataType\":2,\"format\":\"n0\",\"aggregate\":1,\"showAs\":0,\"descending\":false,\"isContentHtml\":false,\"binding\":\"Discount\",\"header\":\"Discount\"}],\"rowFields\":{\"items\":[\"Country\"]},\"columnFields\":{\"items\":[]},\"filterFields\":{\"items\":[]},\"valueFields\":{\"items\":[\"Sales\"]}}" }, { name: "Sales and Downloads by Country", def: "{\"showZeros\":false,\"showColumnTotals\":2,\"showRowTotals\":2,\"defaultFilterType\":3,\"fields\":[{\"dataType\":2,\"format\":\"n0\",\"aggregate\":1,\"showAs\":0,\"descending\":false,\"isContentHtml\":false,\"binding\":\"ID\",\"header\":\"ID\"},{\"dataType\":1,\"format\":\"\",\"aggregate\":2,\"showAs\":0,\"descending\":false,\"isContentHtml\":false,\"binding\":\"Product\",\"header\":\"Product\"},{\"dataType\":1,\"format\":\"\",\"aggregate\":2,\"showAs\":0,\"descending\":false,\"isContentHtml\":false,\"binding\":\"Country\",\"header\":\"Country\"},{\"dataType\":4,\"format\":\"d\",\"aggregate\":2,\"showAs\":0,\"descending\":false,\"isContentHtml\":false,\"binding\":\"Date\",\"header\":\"Date\"},{\"dataType\":2,\"format\":\"n0\",\"aggregate\":1,\"showAs\":0,\"descending\":false,\"isContentHtml\":false,\"binding\":\"Sales\",\"header\":\"Sales\"},{\"dataType\":2,\"format\":\"n0\",\"aggregate\":1,\"showAs\":0,\"descending\":false,\"isContentHtml\":false,\"binding\":\"Downloads\",\"header\":\"Downloads\"},{\"dataType\":3,\"format\":\"\",\"aggregate\":2,\"showAs\":0,\"descending\":false,\"isContentHtml\":false,\"binding\":\"Active\",\"header\":\"Active\"},{\"dataType\":2,\"format\":\"n0\",\"aggregate\":1,\"showAs\":0,\"descending\":false,\"isContentHtml\":false,\"binding\":\"Discount\",\"header\":\"Discount\"}],\"rowFields\":{\"items\":[\"Country\"]},\"columnFields\":{\"items\":[]},\"filterFields\":{\"items\":[]},\"valueFields\":{\"items\":[\"Sales\",\"Downloads\"]}}" }, { name: "Sales Trend by Product", def: "{\"showZeros\": false, \"showColumnTotals\": 2, \"showRowTotals\": 2, \"defaultFilterType\": 3, \"fields\": [{ \"dataType\": 2, \"format\": \"n0\", \"aggregate\": 1, \"showAs\": 0, \"descending\": false, \"isContentHtml\": false, \"binding\": \"ID\", \"header\": \"ID\" }, { \"dataType\": 1, \"format\": \"\", \"aggregate\": 2, \"showAs\": 0, \"descending\": false, \"isContentHtml\": false, \"binding\": \"Product\", \"header\": \"Product\" }, { \"dataType\": 1, \"format\": \"\", \"aggregate\": 2, \"showAs\": 0, \"descending\": false, \"isContentHtml\": false, \"binding\": \"Country\", \"header\": \"Country\" }, { \"dataType\": 4, \"format\": \"yyyy \\\"Q\\\"q\", \"aggregate\": 2, \"showAs\": 0, \"descending\": false, \"isContentHtml\": false, \"binding\": \"Date\", \"header\": \"Date\" }, { \"dataType\": 2, \"format\": \"p2\", \"aggregate\": 3, \"showAs\": 2, \"descending\": false, \"isContentHtml\": false, \"binding\": \"Sales\", \"header\": \"Sales\" }, { \"dataType\": 2, \"format\": \"n0\", \"aggregate\": 1, \"showAs\": 0, \"descending\": false, \"isContentHtml\": false, \"binding\": \"Downloads\", \"header\": \"Downloads\" }, { \"dataType\": 3, \"format\": \"\", \"aggregate\": 2, \"showAs\": 0, \"descending\": false, \"isContentHtml\": false, \"binding\": \"Active\", \"header\": \"Active\" }, { \"dataType\": 2, \"format\": \"n0\", \"aggregate\": 1, \"showAs\": 0, \"descending\": false, \"isContentHtml\": false, \"binding\": \"Discount\", \"header\": \"Discount\" }, { \"dataType\": 2, \"format\": \"n0\", \"aggregate\": 1, \"showAs\": 0, \"descending\": false, \"isContentHtml\": false, \"binding\": \"Sales\", \"header\": \"Sales2\" }], \"rowFields\": { \"items\": [\"Date\"] }, \"columnFields\": { \"items\": [\"Product\"] }, \"filterFields\": { \"items\": [] }, \"valueFields\": { \"items\": [\"Sales\"]}}" } ] saveLoadViewsPivotPanel = wijmo.Control.getControl('#saveLoadViewsPivotPanel'); // populate list of pre-defined views var viewList = document.getElementById('views'); for (var i = 0; i < app.viewDefs.length; i++) { var li = wijmo.createElement('<li><a href="#theView" index="' + i + '">' +app.viewDefs[i].name + '</a></li>'); viewList.appendChild(li); } // apply pre-defined views viewList.addEventListener('click', function (e) { var el = e.toElement; if (el.tagName == 'A') { var index = el.getAttribute('index'); saveLoadViewsPivotPanel.viewDefinition = app.viewDefs[index].def; } }); }; // save/load view definition // save view definition function saveView() { if (saveLoadEngine && saveLoadEngine.viewDefinition) localStorage.setItem('viewDefinition', saveLoadEngine.viewDefinition); }; // restore view definition function loadView() { if (localStorage.getItem('viewDefinition')) saveLoadEngine.viewDefinition = localStorage.getItem('viewDefinition'); };
    using System.Collections.Generic; using System.Linq; using System.Web.Mvc; using OLAP101.Models; using C1.Web.Mvc.Olap; namespace OLAP101.Controllers { public class HomeController : Controller { public ActionResult Index() { OLAP101Model model = new OLAP101Model(); model.Data = ProductData.GetData(10000).ToList(); return View(model); } } }

    Analyze Huge Data

    In this example, the PivotEngine component binds to a DataEngine service. The DataEngine web api is responsible for data aggregation when the OLAP control binds to DataEngine service. DataEngine Web API uses a column-oriented data model which is widely used in many open source and commercial analytical databases and libraries. Data Engine can handle up to hundreds of millions records in a fraction of a second. The service url should be provided to the PivotEngine in this mode.

    You may host DataEngine WebAPI separately or within the same MVC application.
    To create and host DataEngine Web API separately, please refer the Web API documentation.
    To use DataEngine in the same MVC project, ensure the following:

    In this example, the PivotEngine component connects the DataEngine data. The PivotPanel control and the PivotGrid control are bound to the PivotEngine. You can change the view definition in the PivotPanel control. The aggregated data will be obtained from the service. Then the PivotGrid control displays the aggregated data. You can find the detailed raw data shown in a grid by double-clicking a cell in the PivotGrid control.

    Result (live):

    @(Html.C1().PivotEngine().Id("dataEngineE").BindService("~/api/dataengine/complex10") .RowFields(pfcb => pfcb.Items("Country")) .ColumnFields(cfcb => cfcb.Items("Product")) .ValueFields(vfcb => vfcb.Items("Sales"))) @Html.C1().PivotPanel().ItemsSourceId("dataEngineE") @Html.C1().PivotGrid().Id("dataEnginePivotGrid").ItemsSourceId("dataEngineE")
    using System.Collections.Generic; using System.Linq; using System.Web.Mvc; namespace OLAP101.Controllers { public class HomeController : Controller { public ActionResult Index() { return View(); } } }
    using Microsoft.Owin; using Olap101.Models; using Owin; [assembly: OwinStartupAttribute(typeof(Olap101.Startup))] namespace Olap101 { public partial class Startup { public void Configuration(IAppBuilder app) { app.UseDataEngineProviders() .AddDataEngine("complex10", () => ProductData.GetData(100000)); } } }