Features
Overview
Overview
OLAP Component displays data from Google Analytics Traffic table using ADO.NET Connector for Google Analytics.
Change provider
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 ofthe formats supported by the extension modules provided with the FlexGrid. Thelist 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
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.GoogleAnalytics; using System.Globalization; using System; using Microsoft.Extensions.Options; using System.IO; namespace DataConnectorExplorer.Controllers { /// <summary> /// /// </summary> public class GoogleAnalyticsController : Controller { private readonly IOptions<GoogleAnalyticsConnection> config; /// <summary> /// /// </summary> /// <param name="config"></param> public GoogleAnalyticsController(IOptions<GoogleAnalyticsConnection> config) { this.config = config; } private IEnumerable<GoogleAnalyticsTrafficData> GetData() { //Create the connection to Google Analytics server //Ex: //const string keyFile = @"Wijmo-53f854dc3ad3.json"; //const string viewId = @"1243234"; //string connectionString = string.Format("Key File={0};View Id={1}", config.KeyFile, config.ViewId); var config = this.config.Value; string _cacheFolder = "Cache"; if (!Directory.Exists(_cacheFolder)) { Directory.CreateDirectory(_cacheFolder); } string specifiedCacheFile = Path.Combine(_cacheFolder, "C1CacheFile.GA.db"); string connectionStringCachePart = string.Format("Use Cache = 'true'; Cache Location='{0}'; Cache Tolerance = 28800", specifiedCacheFile); string connectionString = string.Format("Key File={0};View Id={1};", config.KeyFile, config.ViewId); connectionString += connectionStringCachePart; using (C1GoogleAnalyticsConnection con = new C1GoogleAnalyticsConnection(connectionString)) { con.Open(); var cmd = con.CreateCommand(); //Query to select from Google Analytics server. cmd.CommandText = "Select Sessions,Browser,Source,City,OperatingSystem,Country,Month from Traffic where Country is not null"; var reader = cmd.ExecuteReader(); List<GoogleAnalyticsTrafficData> result = new List<GoogleAnalyticsTrafficData>(); CultureInfo provider = CultureInfo.InvariantCulture; //Generate List<GoogleAnalyticsTrafficData> from reader var count = 0; while (reader.Read() && count < 1000) { //Put the default value if Sessions is null. Just use for demo var sessions = String.IsNullOrEmpty(reader["Sessions"].ToString()) ? 0 : Convert.ToInt32(reader["Sessions"].ToString()); var browser = reader["Browser"].ToString(); var source = reader["Source"].ToString(); var city = reader["City"].ToString(); var operatingSystem = reader["OperatingSystem"].ToString(); var country = reader["Country"].ToString(); var month = reader["Month"].ToString(); if(!country.Equals("(not set)") && !browser.Equals("(not set)") && !source.Equals("(not set)") && !city.Equals("(not set)") && !source.Equals("(not set)") && !operatingSystem.Equals("(not set)")) { //Add GoogleAnalyticsTrafficData model from the reader result.Add(new GoogleAnalyticsTrafficData { Sessions = sessions, Browser = browser, Source = source, City = city, OperatingSystem = operatingSystem, Country = country, Month = month, }); count++; } } return result; } } 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> /// /// </summary> /// <returns></returns> public IActionResult Index() { IEnumerable Data = GetData().ToList(); OlapModel.ControlId = "indexPanel"; ViewBag.DemoOptions = OlapModel; return View(Data); } } }
@model IEnumerable<GoogleAnalyticsTrafficData> @{ 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="Operating System" binding="OperatingSystem"></c1-pivot-field> <c1-pivot-field header="Source" binding="Source"></c1-pivot-field> <c1-pivot-field header="Browser" binding="Browser"></c1-pivot-field> <c1-pivot-field header="Month" binding="Month"></c1-pivot-field> <c1-pivot-field header="Sessions" binding="Sessions"></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="Operating System"></c1-view-field-collection> <c1-view-field-collection c1-property="ValueFields" items="Sessions" 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 <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 src="https://cdnjs.cloudflare.com/ajax/libs/jszip/2.5.0/jszip.min.js"></script> <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_GoogleAnalytics_Description)</p> }