How to Answer Recurring Questions with Pre-Defined Views in MVC OLAP

December 20 2016

As you can see from the previous post, it is really easy to build views by dragging fields within the PivotPanel control. If your users are familiar with Excel’s Pivot Tables, they will feel right at home with OLAP.

Demos: ASP.NET 4.0 | ASP.NET Core | Download C1Studio

But you may want your application to include some pre-defined views to answer recurring questions. In these cases, it would be nice to store the pre-defined views and allow users to pick them from a list instead of re-building them from scratch each time.

Full OLAP series:

Getting Started with ASP.NET MVC OLAP : Create Basic Pivot Tables
Answer Recurring Questions with Pre-Defined Views

The client PivotEngine class has a viewDefinition property that allows you to save and restore views using a simple JSON string. This makes it easy to build a catalog of standard questions so users can get the view they want instantly.

The HTML may look like this:


@(Html.C1().PivotPanel().Id("saveLoadViewsPivotPanel").CssStyle("height", "550px")  
    .Bind(Model.Data)  
        .RowFields(pfcb => pfcb.Items("Country"))  
        .ColumnFields(cfcb => cfcb.Items("Product"))  
        .ValueFields(vfcb => vfcb.Items("Sales"))  
        )  
@(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>

Or build a list of pre-defined views for the user to pick from.

For example, the code behind starts by defining an app.viewDefs array containing pre-defined views. Each entry in the array has a name and a JSON string representing the view definition:


// 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,…  
{  
name: "Sales and Downloads by Country",  
def: "{\\"showZeros\\":false,\\"showColumnTotals\\":2, …  
},  
… // more view definitions

The array is used to build the list of pre-defined views on the page, hosted by the views element:


// populate list of pre-defined views  
var viewList = document.getElementById('views');  
    for (var i = 0; i < app.viewDefs.length; i++) {  
        var li = wijmo.createElement('*   ' +app.viewDefs[i].name + '
');  
        viewList.appendChild(li);  
    }

The code handles clicks on list items to load the pre-defined view by setting the PivotEngine‘s viewDefinition property:


// get a reference to the PivotEngine  
saveLoadViewsPivotPanel = wijmo.Control.getControl('#saveLoadViewsPivotPanel');  

viewList.addEventListener('click', function (e) {  
        var el = e.toElement;  
        if (el.tagName == 'A') {  
            var index = el.getAttribute('index');  
            saveLoadViewsPivotPanel.viewDefinition = app.viewDefs[index].def;  
        }  
    });

Finally, clicks on the Save View and Load View buttons are handled as follows:


//// 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');  
};

The result looks like this:

See the demo: ASP.NET 4.0 | ASP.NET Core

Take a look at the documentation for more details.

Download C1Studio

MESCIUS inc.