Features
Overview
Overview
OLAP Component displays data from Dynamics365Sales Invoice table using ADO.NET Connector for Dynamics365Sales.
Features
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 Dynamics365InvoiceReport.Models; using System.Collections; using Microsoft.AspNetCore.Mvc; using System.Linq; using System.Collections.Generic; using C1.Web.Mvc.Olap; using C1.AdoNet.D365S; using System.Globalization; using System; using Microsoft.Extensions.Options; namespace Dynamics365InvoiceReport.Controllers.Olap { public partial class IndexController : Controller { private readonly IOptions<DynamicConnection> config; public IndexController(IOptions<DynamicConnection> config) { this.config = config; } private IEnumerable<InvoiceData> GetData() { //Create the connection to Dynamic 365 server //connstr is connection string to Dynamic 365 server //Exp: //const string urlDynamics = @"https://...api.crm8.dynamics.com/api/data/v9.1/"; //const string clientID = "..."; //const string cllentSecret = "..."; //const string username = "..."; //const string password = "..."; //const string tokenEnpoint = @"https://login.microsoftonline.com/.../oauth2/token"; //const string extendProperties = @"{""resource"":""https://....crm8.dynamics.com/""}"; //static string connstr = $@"Url={urlDynamics};Use Etag=true;OAuthClientId={clientID};OAuthClientSecret={cllentSecret};OAuthTokenEndpoint={tokenEnpoint};OAuthExtendProperties={extendProperties};Max Page Size = 100"; var config = this.config.Value; string extendProperties = "{\"resource\":\"" + config.Resource + "\"}"; var conectionStr = $@"Url={config.UrlDynamics};Use Etag=true;OAuthClientId={config.ClientID};OAuthClientSecret={config.CllentSecret};OAuthTokenEndpoint={config.TokenEnpoint};OAuthExtendProperties={extendProperties};Max Page Size = 100"; using (C1D365SConnection con = new C1D365SConnection(conectionStr)) { //Open the connection to dynamic 365 server con.Open(); var cmd = con.CreateCommand(); //query to select from dynamic 365 server cmd.CommandText = "Select totalamount, billto_postalcode, billto_country, datedelivered, discountpercentage, totaltax, totaldiscountamount, name from invoices where (billto_country = 'India' or billto_country='United states' or billto_country='Japan') limit 1000"; var reader = cmd.ExecuteReader(); List<InvoiceData> result = new List<InvoiceData>(); CultureInfo provider = CultureInfo.InvariantCulture; //Generate List<InvoiceData> from reader while (reader.Read()) { //Put the default value if totalamount is null. Just use for demo var totalamount = String.IsNullOrEmpty(reader[0].ToString()) ? 0 : Convert.ToDecimal(reader[0].ToString()); var billto_postalcode = reader[1].ToString(); var billto_country = reader[2].ToString(); var datedelivered = reader[3].ToString(); DateTime? deliveredDate = null; if (!String.IsNullOrEmpty(datedelivered) && DateTime.TryParse(datedelivered, out DateTime convertedDate)) { deliveredDate = convertedDate; } else { //Put the default value if datedelivered is null. Just use for demo deliveredDate = new DateTime(2019, 12, 1); } //Put the default value if discountpercentage is null. Just use for demo var discountpercentage = String.IsNullOrEmpty(reader[4].ToString()) ? 0 : Convert.ToDecimal(reader[4].ToString()); //Put the default value if totaltax is null. Just use for demo var totaltax = String.IsNullOrEmpty(reader[5].ToString()) ? 0 : Convert.ToDecimal(reader[5].ToString()); //Put the default value if totaldiscountamount is null. Just use for demo var totaldiscountamount = String.IsNullOrEmpty(reader[6].ToString()) ? 0 : Convert.ToDecimal(reader[6].ToString()); var name = reader[7].ToString(); //Add InvoiceData model from the reader result.Add(new InvoiceData { Totalamount = totalamount, Billtopostalcode = billto_postalcode, //Put the default value if billto_country is null. And Standardize the billto_country. Just use for demo Billtocountry = String.IsNullOrEmpty(billto_country) ? StandardizeName("United States") : StandardizeName(billto_country), Discountpercentage = discountpercentage, Totaltax = totaltax, Totaldiscountamount = totaldiscountamount, //Standardize the Name. Just use for demo Name = StandardizeName(name), Datedelivered = deliveredDate.Value.ToString(CultureInfo.InvariantCulture), Year = deliveredDate.Value.Year.ToString(), Month = deliveredDate.Value.ToString("MMMM"), }); ; } return result; } } /// <summary> /// Function to Standardize string. Upper first letter of string /// </summary> /// <param name="input"></param> /// <returns></returns> 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 } } } }; public ActionResult Index() { IEnumerable Data = GetData().ToList(); OlapModel.ControlId = "indexPanel"; ViewBag.DemoOptions = OlapModel; return View(Data); } } }
@model IEnumerable<InvoiceData> @{ 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="Bill To Country" binding="Billtocountry" descending="true"></c1-pivot-field> <c1-pivot-field header="Year" binding="Year"></c1-pivot-field> <c1-pivot-field header="Total Amount" binding="Totalamount"></c1-pivot-field> <c1-pivot-field header="Bill To Postal Code" binding="Billtopostalcode"></c1-pivot-field> <c1-pivot-field header="Discount Percentage" binding="Discountpercentage"></c1-pivot-field> <c1-pivot-field header="Total Tax" binding="Totaltax"></c1-pivot-field> <c1-pivot-field header="Total Discount Amount" binding="Totaldiscountamount"></c1-pivot-field> <c1-pivot-field header="Date Delivered" binding="Datedelivered"></c1-pivot-field> <c1-pivot-field header="Name" binding="Name"></c1-pivot-field> <c1-pivot-field header="Month" binding="Month"></c1-pivot-field> </c1-pivot-field-collection> <c1-view-field-collection c1-property="RowFields" items="Bill To Country,Month"></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="Total 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 <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.Index_Text6)</p> }