Essential Studio for Asp.Net | Demos

SHOWCASE SAMPLES
Showcase samples
    DataSource:
    Mode:
    Save Report:
    Load Report:
    <%@ Page Title="PivotGrid-Save and Load-ASP.NET-SYNCFUSION" MetaDescription="This demo for Syncfusion Essential JS1 for ASP.NET pivot grid control describes how to save and load pivot reports to the local web browser" Language="C#" MasterPageFile="~/Samplebrowser.Master" AutoEventWireup="true" CodeBehind="SaveAndLoad.aspx.cs" Inherits="WebSampleBrowser.SaveLoadReport" %>
    
    <%@ Register Assembly="Syncfusion.EJ.Pivot" Namespace="Syncfusion.JavaScript.Web" TagPrefix="ej" %>
    
    <asp:Content ID="Content1" runat="server" ContentPlaceHolderID="ControlsSection">
        <ej:PivotGrid ID="PivotGrid1" runat="server" EnableGroupingBar="true" ClientIDMode="Static">
            <DataSource>
                <Rows>
                    <ej:Field FieldName="Country" FieldCaption="Country"></ej:Field>
                    <ej:Field FieldName="State" FieldCaption="State"></ej:Field>
                </Rows>
                <Columns>
                    <ej:Field FieldName="Product" FieldCaption="Product"></ej:Field>
                </Columns>
                <Values>
                    <ej:Field FieldName="Amount" FieldCaption="Amount"></ej:Field>
                    <ej:Field FieldName="Quantity" FieldCaption="Quantity"></ej:Field>
                </Values>
            </DataSource>
            <ClientSideEvents Load="onLoad" />
        </ej:PivotGrid>
    </asp:Content>
    
    <asp:Content ID="Content2" runat="server" ContentPlaceHolderID="PropertySection">
                    <div id="sampleProperties">
                        <div class="prop-grid">
                            <div class="row">
                                <table>
                                    <tr>
                                        <td class="chkrad">
                                            DataSource:
                                        </td>
                                        <td class="chkrad">
                                            <input type="radio" name="dataSource" id="rdbRelational" checked="checked" /><label for="rdbRelational" class="radioBtnLabel">Relational</label>
                                        </td>
                                        <td class="chkrad" colspan="2">
                                            <input type="radio" name="dataSource" id="rdbOlap" /><label for="rdbOlap" class="radioBtnLabel">OLAP</label>
                                        </td>
                                    </tr>
                                </table>
                            </div>
                            <div class="row" style="margin-top: 5px;">
                                <table>
                                    <tr>
                                        <td class=" chkrad">
                                            Mode:
                                        </td>
                                        <td class="chkrad">
                                            <input type="radio" name="dataMode" id="rdbClient" checked /><label for="rdbClient" class="radioBtnLabel">Client</label>
                                        </td>
                                        <td class="chkrad" colspan="2">
                                            <input type="radio" name="dataMode" id="rdbServer" /><label for="rdbServer" class="radioBtnLabel">Server</label>
                                        </td>
                                    </tr>
                                </table>
                            </div>
                            <div class="row" >
                                <table cellspacing="10">
                                    <tr class="savereport">
                                        <td class=" chkrad">
                                            Save Report:
                                        </td>
                                        <td class=" chkrad">
                                            <input id="savedreport" type="text" />
                                        </td>
                                        <td class=" chkrad">
                                            <button id="btnSave" value="Save" name="Save" title="Save">Save</button>
                                        </td>
                                    </tr>
                                    <tr class="loadreport">
                                        <td class=" chkrad">
                                            Load Report:
                                        </td>
                                        <td class=" chkrad">
                                            <input type="text" id="drpdwn" />
                                        </td>
                                        <td class=" chkrad">
                                            <button id="btnLoad" value="Load" name="Load" title="Load">Load</button>
                                        </td>
                                    </tr>
                                </table>
                        </div>
                    </div>
                </div>
    </asp:Content>
    
    <asp:Content ID="ScriptContent" runat="server" ContentPlaceHolderID="ScriptSection">
              <script type="text/javascript">
                  var btnTarget, rdbRelational, rdbOlap, rdbClient, rdbServer, pGridObj, relationalclientrptmode = [], olapclientrptmode = [], relationalserverrptmode = [], olapserverrptmode = [], datasource = [];
    
                  $(function () {
                      $("#sampleProperties").ejPropertiesPanel();
                      $("#rdbRelational,#rdbOlap,#rdbClient,#rdbServer").ejRadioButton({ change: "renderPivotGrid" });
                      $("#btnSave").ejButton({ type: "button", roundedCorner: true, size: "small", click: "save" });
                      $("#btnLoad").ejButton({ type: "button", roundedCorner: true, size: "small", click: "load" });
                      rdbRelational = $('#rdbRelational').data("ejRadioButton"); rdbOlap = $('#rdbOlap').data("ejRadioButton"); rdbClient = $('#rdbClient').data("ejRadioButton"); rdbServer = $('#rdbServer').data("ejRadioButton");
                      pGridObj = $('#PivotGrid1').data("ejPivotGrid");
                      $("#savedreport").ejMaskEdit({ name: "mask", inputMode: ej.InputMode.Text, watermarkText: "Save Report", width: "70%" });
                      $('#drpdwn').ejDropDownList({ dataSource: [], targetID: "drpdwn", watermarkText: "Load Report", width: "70%" });
                  });
                  function onLoad(args) {
                      if (args.model.dataSource.data == null)
                          args.model.dataSource.data = pivot_dataset;
                  }
                  function save() {
                      var obj; isDuplicate = false, mode = null, reportCollection = [relationalclientrptmode, olapclientrptmode, relationalserverrptmode, olapserverrptmode];
                      var savedreport = $("#savedreport").data("ejMaskEdit");
                      if (rdbRelational.model.checked && rdbClient.model.checked)
                          mode = "Relational-Client";
                      else if (rdbRelational.model.checked && rdbServer.model.checked)
                          mode = "Relational-Server";
                      else if (rdbOlap.model.checked && rdbClient.model.checked)
                          mode = "Olap-Client";
                      else
                          mode = "Olap-Server";
                      if (savedreport.model.value == null) { window.alert("Report name can't be empty"); return; }
                      $.each(reportCollection, function (i, value) {
                          if (value.length > 0)
                              for (var j = 0; j < value.length; j++) {
                                  if (!ej.isNullOrUndefined(value[j]))
                                      if (value[j].text == savedreport.model.value)
                                          value.splice(j, 1);
                              }
                      });
                      obj = { text: savedreport.model.value, value: mode };
    
                      if (obj.value == "Relational-Client") {
                          $.each(relationalclientrptmode, function (index, item) {
                              if (item.text == obj.text)
                                  isDuplicate = true;
                          })
                          if (!isDuplicate)
                              relationalclientrptmode.push(obj);
                      }
                      else if (obj.value == "Relational-Server") {
                          $.each(relationalserverrptmode, function (index, item) {
                              if (item.text == obj.text)
                                  isDuplicate = true;
                          })
                          if (!isDuplicate)
                              relationalserverrptmode.push(obj);
                      }
                      else if (obj.value == "Olap-Server") {
                          $.each(olapserverrptmode, function (index, item) {
                              if (item.text == obj.text)
                                  isDuplicate = true;
                          })
                          if (!isDuplicate)
                              olapserverrptmode.push(obj);
                      }
                      else {
                          $.each(olapclientrptmode, function (index, item) {
                              if (item.text == obj.text)
                                  isDuplicate = true;
                          })
                          if (!isDuplicate)
                              olapclientrptmode.push(obj);
                      }
                      $("#savedreport").val('');
                      if (rdbRelational.model.checked && rdbClient.model.checked)
                          datasource = relationalclientrptmode;
                      else if (rdbRelational.model.checked && rdbServer.model.checked)
                          datasource = relationalserverrptmode;
                      else if (rdbOlap.model.checked && rdbClient.model.checked)
                          datasource = olapclientrptmode;
                      else
                          datasource = olapserverrptmode;
                      $('#drpdwn').ejDropDownList({ dataSource: datasource, targetID: "drpdwn", watermarkText: "Load Report", width: "70%" });
    
                      url = rdbRelational.model.checked ? "../api/RelationalGrid" : "../api/OlapGrid";
                      name = savedreport.model.value;
                      storage = "db";
                      pGridObj.saveReport(name, storage, url);
                  }
                  function load() {
                      var loadreport = $("#drpdwn").data("ejDropDownList");
                      if (loadreport._currentText == null) { window.alert("Report name can't be empty"); return; }
                      url = (loadreport._selectedValue == "Relational-Client" || loadreport._selectedValue == "Relational-Server") ? "../api/RelationalGrid" : "../api/OlapGrid";
                      name = loadreport._currentText;
                      storage = "db";
                      pGridObj.loadReport(name, storage, url);
                  }
                  function renderPivotGrid(args) {
                      var pivotdataSource = { data: pivot_dataset, cube: "", rows: [{ fieldName: "Country", fieldCaption: "Country" }, { fieldName: "State", fieldCaption: "State" }], columns: [{ fieldName: "Product", fieldCaption: "Product" }], values: [{ fieldName: "Amount", fieldCaption: "Amount" }, { fieldName: "Quantity", fieldCaption: "Quantity" }], filters: [] };
                      var olapDataSource = { data: "//bi.syncfusion.com/olap/msmdpump.dll", catalog: "Adventure Works DW 2008 SE", cube: "Adventure Works", rows: [{ fieldName: "[Date].[Fiscal]" }], columns: [{ fieldName: "[Customer].[Customer Geography]" }], values: [{ measures: [{ fieldName: "[Measures].[Internet Sales Amount]" }], axis: "columns" }], filters: [] };
                      if (rdbRelational.model.checked && rdbClient.model.checked)
                          datasource = relationalclientrptmode;
                      else if (rdbRelational.model.checked && rdbServer.model.checked)
                          datasource = relationalserverrptmode;
                      else if (rdbOlap.model.checked && rdbClient.model.checked)
                          datasource = olapclientrptmode;
                      else
                          datasource = olapserverrptmode;
                      $('#drpdwn').ejDropDownList({ dataSource: datasource, targetID: "drpdwn", watermarkText: "Load Report", width: "70%" });
                      $(pGridObj.element).html("");
                      pGridObj._waitingPopup.show();
                      if (rdbClient.model.checked) {
                          pGridObj.model.dataSource = rdbRelational.model.checked ? pivotdataSource : rdbOlap.model.checked ? olapDataSource : pivotdataSource;
                          pGridObj.model.url = "";
                      }
                      else {
                          pGridObj.model.dataSource = { data: null, cube: "" };
                          pGridObj.model.operationalMode = "servermode";
                          pGridObj.model.url = rdbRelational.model.checked ? "../api/RelationalGrid" : "../api/OlapGrid";
                      }
                      pGridObj._currentReportItems = [];
                      pGridObj._tempFilterData = [];
                      pGridObj._load();
                  }
                  window.loadPivotGridFrameTheme = function () {
                      window.setTimeout(function () {
                          var pivotGridElement = $("#PivotGrid1").data("ejPivotGrid");
                          if (pivotGridElement && pivotGridElement.model.enableGroupingBar && pivotGridElement.getJSONRecords() != null)
                              pivotGridElement.renderControlFromJSON();
                      }, 2500);
                  }
                </script>
    </asp:Content>
    
    <asp:Content ID="Content3" runat="server" ContentPlaceHolderID="StyleSection">
        <style type="text/css">
            #PivotGrid1 {
                height: 350px;
                width: 100%;
                overflow: auto;
                float:left;
            }
            .row .cols-sample-area{
                width:100%;
            }
            .radioBtnLabel {
                margin-left: 5px;
            }
    
            .cols-prop-area .content {
                width: auto;
            }
    
            .row .cols-prop-area {
                min-height: 210px;
                width: auto;
            }
            tr.savereport > td
            {
                padding-bottom: 1em;
            }
            label {
                font-weight: normal;
            }
        </style>
    </asp:Content>
    
    
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    
    namespace WebSampleBrowser
    {
        public partial class SaveLoadReport : System.Web.UI.Page
        {
            protected void Page_Load(object sender, EventArgs e)
            {
    
            }
        }
    }
    using Syncfusion.JavaScript;
    using Syncfusion.PivotAnalysis.Base;
    using System;
    using System.Collections.Generic;
    using System.Configuration;
    using System.Data;
    using System.Data.SqlServerCe;
    using System.Linq;
    using System.Net;
    using System.Net.Http;
    using System.Text;
    using System.Web;
    using System.Web.Http;
    using System.Web.Script.Serialization;
    using OLAPUTILS = Syncfusion.JavaScript.Olap;
    
    namespace WebSampleBrowser
    {
        public class RelationalGridController : ApiController
        {
            Syncfusion.JavaScript.PivotGrid htmlHelper = new Syncfusion.JavaScript.PivotGrid();
            JavaScriptSerializer serializer = new JavaScriptSerializer();
            Dictionary<string, object> dict = new Dictionary<string, object>();
            static int cultureIDInfoval = 1033;
            static string connectionString = ConfigurationManager.ConnectionStrings["Adventure Works"].ConnectionString + "locale identifier=" + cultureIDInfoval + ";";
            string conStringforDB = "DataSource=" + HttpContext.Current.Server.MapPath(".").Split(new string[] { "\\api" }, StringSplitOptions.None)[0] + "\\database\\ReportsTable.sdf; Persist Security Info=False";
    
            [System.Web.Http.ActionName("InitializeGrid")]
            [System.Web.Http.HttpPost]
            public Dictionary<string, object> InitializeGrid(Dictionary<string, object> jsonResult)
            {
                dynamic customData = serializer.Deserialize<dynamic>(jsonResult["customObject"].ToString());
                if (jsonResult.ContainsKey("currentReport"))
                    htmlHelper.PopulateData(jsonResult["currentReport"].ToString());
                else
                    htmlHelper.PivotReport = BindDefaultData((customData is Dictionary<string, object> && jsonResult.ContainsKey("isCalculatedField")) ? customData["isCalculatedField"] : false);
                if ((customData is Dictionary<string, object> && customData.ContainsKey("isPaging") && customData["isPaging"]))
                {
                    htmlHelper.PivotReport.EnablePaging = true;
                    htmlHelper.PivotReport.PagerOptions.CategoricalPageSize = 5;
                    htmlHelper.PivotReport.PagerOptions.SeriesPageSize = 5;
                    htmlHelper.PivotReport.PagerOptions.CategoricalCurrentPage = 1;
                    htmlHelper.PivotReport.PagerOptions.SeriesCurrentPage = 1;                
                }            
                dict = htmlHelper.GetJsonData(jsonResult["action"].ToString(), ProductSales.GetSalesData(), jsonResult["valueSorting"].ToString());
                return dict;
            }
    
            [System.Web.Http.ActionName("FetchMembers")]
            [System.Web.Http.HttpPost]
            public Dictionary<string, object> FetchMembers(Dictionary<string, object> jsonResult)
            {
                htmlHelper.PopulateData(jsonResult["currentReport"].ToString());
                dict = htmlHelper.GetJsonData(jsonResult["action"].ToString(), ProductSales.GetSalesData(), jsonResult["headerTag"].ToString(), jsonResult["sortedHeaders"].ToString());
                return dict;
            }
    
            [System.Web.Http.ActionName("Filtering")]
            [System.Web.Http.HttpPost]
            public Dictionary<string, object> Filtering(Dictionary<string, object> jsonResult)
            {
                htmlHelper.PopulateData(jsonResult["currentReport"].ToString());
                dict = htmlHelper.GetJsonData(jsonResult["action"].ToString(), ProductSales.GetSalesData(), null, null, null, jsonResult["filterParams"].ToString(), jsonResult["sortedHeaders"].ToString(), jsonResult.ContainsKey("valueSorting") ? jsonResult["valueSorting"].ToString() : null);
                return dict;
            }
    
            [System.Web.Http.ActionName("NodeStateModified")]
            [System.Web.Http.HttpPost]
            public Dictionary<string, object> NodeStateModified(Dictionary<string, object> jsonResult)
            {
                htmlHelper.PopulateData(jsonResult["currentReport"].ToString());
                dict = htmlHelper.GetJsonData(jsonResult["action"].ToString(), ProductSales.GetSalesData(), null, jsonResult["headerTag"].ToString(), jsonResult["dropAxis"].ToString(), jsonResult["filterParams"].ToString(), jsonResult["sortedHeaders"].ToString(), jsonResult["valueSorting"].ToString());
                return dict;
            }
    
            [System.Web.Http.ActionName("NodeDropped")]
            [System.Web.Http.HttpPost]
            public Dictionary<string, object> NodeDropped(Dictionary<string, object> jsonResult)
            {
                htmlHelper.PopulateData(jsonResult["currentReport"].ToString());
                dict = htmlHelper.GetJsonData(jsonResult["action"].ToString(), ProductSales.GetSalesData(), null, jsonResult["dropAxis"].ToString(), jsonResult["headerTag"].ToString(), jsonResult.ContainsKey("filterParams") ? jsonResult["filterParams"].ToString() : null, jsonResult["sortedHeaders"].ToString(), jsonResult["valueSorting"].ToString());
                return dict;
            }
    
            [System.Web.Http.ActionName("Sorting")]
            [System.Web.Http.HttpPost]
            public Dictionary<string, object> Sorting(Dictionary<string, object> jsonResult)
            {
                htmlHelper.PopulateData(jsonResult["currentReport"].ToString());
                dict = htmlHelper.GetJsonData(jsonResult["action"].ToString(), ProductSales.GetSalesData(), jsonResult["sortedHeaders"].ToString());
                return dict;
            }
    
            [System.Web.Http.ActionName("ValueSorting")]
            [System.Web.Http.HttpPost]
            public Dictionary<string, object> ValueSorting(Dictionary<string, object> jsonResult)
            {
                htmlHelper.PopulateData(jsonResult["currentReport"].ToString());
                dict = htmlHelper.GetJsonData(jsonResult["action"].ToString(), ProductSales.GetSalesData(), jsonResult["valueSorting"].ToString());
                return dict;
            }
    
            [System.Web.Http.ActionName("CalculatedField")]
            [System.Web.Http.HttpPost]
            public Dictionary<string, object> CalculatedField(Dictionary<string, object> jsonResult)
            {
                htmlHelper.PopulateData(jsonResult["currentReport"].ToString());
                dict = htmlHelper.GetJsonData(jsonResult["action"].ToString(), ProductSales.GetSalesData(), null, null, jsonResult["headerTag"].ToString(), null, null, jsonResult["valueSorting"].ToString());
                return dict;
            }
    
            [System.Web.Http.ActionName("Paging")]
            [System.Web.Http.HttpPost]
            public Dictionary<string, object> Paging(Dictionary<string, object> jsonResult)
            {
                htmlHelper.PopulateData(jsonResult["currentReport"].ToString());
                if (jsonResult["axis"].ToString() == "series")
                    htmlHelper.PivotReport.PagerOptions.SeriesCurrentPage = Convert.ToInt32(jsonResult["pageNo"].ToString());
                else
                    htmlHelper.PivotReport.PagerOptions.CategoricalCurrentPage = Convert.ToInt32(jsonResult["pageNo"].ToString());
                dict = htmlHelper.GetJsonData(jsonResult["action"].ToString(), ProductSales.GetSalesData(), jsonResult["drillHeaders"].ToString(), jsonResult["valueSorting"].ToString());
                return dict;
            }
    
            [System.Web.Http.ActionName("DrillGrid")]
            [System.Web.Http.HttpPost]
            public Dictionary<string, object> drillGrid(Dictionary<string, object> jsonResult)
            {
                htmlHelper.PopulateData(jsonResult["currentReport"].ToString());
                dict = htmlHelper.GetJsonData(jsonResult["action"].ToString(), ProductSales.GetSalesData(), jsonResult["drillHeaders"].ToString(), jsonResult["valueSorting"].ToString());
                return dict;
            }
    
            [System.Web.Http.ActionName("Export")]
            [System.Web.Http.HttpPost]
            public void Export()
            {
                string args = HttpContext.Current.Request.Form.GetValues(0)[0];
                Dictionary<string, string> gridParams = serializer.Deserialize<Dictionary<string, string>>(args);
                htmlHelper.PopulateData(gridParams["currentReport"]);
                string fileName = "Sample";
                htmlHelper.ExportPivotGrid(ProductSales.GetSalesData(), args, fileName, HttpContext.Current.Response);
            }
    
            [System.Web.Http.ActionName("SaveReport")]
            [System.Web.Http.HttpPost]
            public Dictionary<string, object> SaveReport(Dictionary<string, object> jsonResult)
            {
                string mode = jsonResult["operationalMode"].ToString();
                bool isDuplicate = true;
                SqlCeConnection con = new SqlCeConnection() { ConnectionString = conStringforDB };
                con.Open();
                SqlCeCommand cmd1 = null;
                foreach (DataRow row in GetDataTable().Rows)
                {
                    if ((row.ItemArray[0] as string).Equals(jsonResult["reportName"].ToString()))
                    {
                        isDuplicate = false;
                        cmd1 = new SqlCeCommand("update ReportsTable set Report=@Reports where ReportName like @ReportName", con);
                    }
                }
                if (isDuplicate)
                {
                    cmd1 = new SqlCeCommand("insert into ReportsTable Values(@ReportName,@Reports)", con);
                }
                cmd1.Parameters.AddWithValue("@ReportName", jsonResult["reportName"].ToString());
                if (mode == "serverMode")
                    cmd1.Parameters.AddWithValue("@Reports", OLAPUTILS.Utils.GetReportStream(jsonResult["clientReports"].ToString()).ToArray());
                else if (mode == "clientMode")
                    cmd1.Parameters.AddWithValue("@Reports", Encoding.UTF8.GetBytes(jsonResult["clientReports"].ToString()).ToArray());
                cmd1.ExecuteNonQuery();
                con.Close();
                return null;
            }
    
            [System.Web.Http.ActionName("LoadReportFromDB")]
            [System.Web.Http.HttpPost]
            public Dictionary<string, object> LoadReportFromDB(Dictionary<string, object> jsonResult)
            {
                byte[] reportString = new byte[2 * 1024];
                PivotReport report = new PivotReport();
                var reports = "";
                string mode = jsonResult["operationalMode"].ToString();
                Dictionary<string, object> dictionary = new Dictionary<string, object>();
                if (mode == "serverMode" && jsonResult.ContainsKey("clientReports"))
                {
                    reports = jsonResult["clientReports"].ToString();
                }
                else
                {
                    foreach (DataRow row in GetDataTable().Rows)
                    {
                        if ((row.ItemArray[0] as string).Equals(jsonResult["reportName"].ToString()))
                        {
                            if (mode == "clientMode")
                            {
                                reportString = (row.ItemArray[1] as byte[]);
                                dictionary.Add("report", Encoding.UTF8.GetString(reportString));
                                break;
                            }
                            else if (mode == "serverMode")
                            {
                                reports = OLAPUTILS.Utils.CompressData(row.ItemArray[1] as byte[]);
                                break;
                            }
                        }
                    }
                }
                if (reports != "")
                {
                    report = htmlHelper.DeserializedReports(reports);
                    htmlHelper.PivotReport = report;
                    dictionary = htmlHelper.GetJsonData("loadOperation", ProductSales.GetSalesData(), "Load Report", jsonResult["reportName"].ToString());
                }
                return dictionary;
            }
    
    
            private DataTable GetDataTable()
            {
                SqlCeConnection con = new SqlCeConnection() { ConnectionString = conStringforDB };
                con.Open();
                DataSet dSet = new DataSet();
                new SqlCeDataAdapter("Select * from ReportsTable", con).Fill(dSet);
                con.Close();
                return dSet.Tables[0];
            }
    
    
            [System.Web.Http.ActionName("DeferUpdate")]
            [System.Web.Http.HttpPost]
            public Dictionary<string, object> DeferUpdate(Dictionary<string, object> jsonResult)
            {
                htmlHelper.PopulateData(jsonResult["currentReport"].ToString());
                dict = htmlHelper.GetJsonData(jsonResult["action"].ToString(), ProductSales.GetSalesData(), null, null, null, jsonResult["sortedHeaders"].ToString(), jsonResult["filterParams"].ToString(), jsonResult["valueSorting"].ToString());
                return dict;
            }
    
            [System.Web.Http.ActionName("CellEditing")]
            [System.Web.Http.HttpPost]
            public Dictionary<string, object> CellEditing(Dictionary<string, object> jsonResult)
            {
                htmlHelper.PopulateData(jsonResult["currentReport"].ToString());
                dict = htmlHelper.GetJsonData(jsonResult["action"].ToString(), ProductSales.GetSalesData(), null, jsonResult["index"].ToString(), jsonResult["summaryValues"].ToString(), null, jsonResult["valueHeaders"].ToString(), jsonResult["valueSorting"].ToString());
                return dict;
            }
    
            private PivotReport BindDefaultData(bool isCalculatedField)
            {
                PivotReport pivotSetting = new PivotReport();
                if (isCalculatedField)
                {
                    pivotSetting.PivotRows.Add(new PivotItem { FieldMappingName = "Date", FieldHeader = "Date", TotalHeader = "Total" });
                    pivotSetting.PivotRows.Add(new PivotItem { FieldMappingName = "Product", FieldHeader = "Product", TotalHeader = "Total" });
                    pivotSetting.PivotColumns.Add(new PivotItem { FieldMappingName = "Country", FieldHeader = "Country", TotalHeader = "Total" });
                    pivotSetting.PivotCalculations.Add(new PivotComputationInfo { CalculationName = "Amount", Description = "Amount", FieldHeader = "Amount", FieldName = "Amount", Format = "C", SummaryType = Syncfusion.PivotAnalysis.Base.SummaryType.DoubleTotalSum });
                    pivotSetting.PivotCalculations.Add(new PivotComputationInfo
                    {
                        CalculationName = "Price",
                        FieldHeader = "Price",
                        FieldName = "Price",
                        CalculationType = CalculationType.Formula,
                        Formula = "Amount + 12"
                    });
                }
                else
                {
                    pivotSetting.PivotRows.Add(new PivotItem { FieldMappingName = "Date", FieldHeader = "Date", TotalHeader = "Total" });
                    pivotSetting.PivotRows.Add(new PivotItem { FieldMappingName = "Product", FieldHeader = "Product", TotalHeader = "Total" });
                    pivotSetting.PivotColumns.Add(new PivotItem { FieldMappingName = "Country", FieldHeader = "Country", TotalHeader = "Total" });
                    pivotSetting.PivotColumns.Add(new PivotItem { FieldMappingName = "State", FieldHeader = "State", TotalHeader = "Total" });
                    pivotSetting.PivotCalculations.Add(new PivotComputationInfo { CalculationName = "Amount", Description = "Amount", FieldHeader = "Amount", FieldName = "Amount", Format = "C", SummaryType = Syncfusion.PivotAnalysis.Base.SummaryType.DoubleTotalSum });
                }
                return pivotSetting;
            }
        }
    
        internal class ProductSales
        {
            public string Product { get; set; }
    
            public string Date { get; set; }
    
            public string Country { get; set; }
    
            public string State { get; set; }
    
            public int Quantity { get; set; }
    
            public double Amount { get; set; }
    
            public static ProductSalesCollection GetSalesData()
            {
                /// Geography
                string[] countries = new string[] { "Australia", "Canada", "France", "Germany", "United Kingdom", "United States" };
                string[] ausStates = new string[] { "New South Wales", "Queensland", "South Australia", "Tasmania", "Victoria" };
                string[] canadaStates = new string[] { "Alberta", "British Columbia", "Brunswick", "Manitoba", "Ontario", "Quebec" };
                string[] franceStates = new string[] { "Charente-Maritime", "Essonne", "Garonne (Haute)", "Gers", };
                string[] germanyStates = new string[] { "Bayern", "Brandenburg", "Hamburg", "Hessen", "Nordrhein-Westfalen", "Saarland" };
                string[] ukStates = new string[] { "England" };
                string[] ussStates = new string[] { "New York", "North Carolina", "Alabama", "California", "Colorado", "New Mexico", "South Carolina" };
    
                /// Time
                string[] dates = new string[] { "FY 2005", "FY 2006", "FY 2007", "FY 2008", "FY 2009" };
    
                /// Products
                string[] products = new string[] { "Bike", "Van", "Car" };
                Random r = new Random(123345345);
    
                int numberOfRecords = 2000;
                ProductSalesCollection listOfProductSales = new ProductSalesCollection();
                for (int i = 0; i < numberOfRecords; i++)
                {
                    ProductSales sales = new ProductSales();
                    sales.Country = countries[r.Next(1, countries.GetLength(0))];
                    sales.Quantity = r.Next(1, 12);
                    /// 1 percent discount for 1 quantity
                    double discount = (30000 * sales.Quantity) * (double.Parse(sales.Quantity.ToString()) / 100);
                    sales.Amount = (30000 * sales.Quantity) - discount;
                    sales.Date = dates[r.Next(r.Next(dates.GetLength(0) + 1))];
                    sales.Product = products[r.Next(r.Next(products.GetLength(0) + 1))];
                    switch (sales.Product)
                    {
                        case "Car":
                            {
                                sales.Date = "FY 2005";
                                break;
                            }
                    }
                    switch (sales.Country)
                    {
                        case "Australia":
                            {
                                sales.State = ausStates[r.Next(ausStates.GetLength(0))];
                                break;
                            }
                        case "Canada":
                            {
                                sales.State = canadaStates[r.Next(canadaStates.GetLength(0))];
                                break;
                            }
                        case "France":
                            {
                                sales.State = franceStates[r.Next(franceStates.GetLength(0))];
                                break;
                            }
                        case "Germany":
                            {
                                sales.State = germanyStates[r.Next(germanyStates.GetLength(0))];
                                break;
                            }
                        case "United Kingdom":
                            {
                                sales.State = ukStates[r.Next(ukStates.GetLength(0))];
                                break;
                            }
                        case "United States":
                            {
                                sales.State = ussStates[r.Next(ussStates.GetLength(0))];
                                break;
                            }
                    }
                    listOfProductSales.Add(sales);
                }
                return listOfProductSales;
            }
    
            public override string ToString()
            {
                return string.Format("{0}-{1}-{2}", this.Country, this.State, this.Product);
            }
    
            public class ProductSalesCollection : List<ProductSales>
            {
            }
        }
    }
    using Syncfusion.Olap.Manager;
    using Syncfusion.Olap.Reports;
    using System;
    using System.Collections.Generic;
    using System.Configuration;
    using System.Linq;
    using System.Net;
    using System.Net.Http;
    using System.Web.Http;
    using System.Web.Script.Serialization;
    using Syncfusion.JavaScript.Olap;
    using System.Web;
    using System.Data.SqlServerCe;
    using OLAPUTILS = Syncfusion.JavaScript.Olap;
    using System.Data;
    using System.Text;
    
    namespace WebSampleBrowser
    {
        public class OLAPGridController : ApiController
        {
            Syncfusion.JavaScript.PivotGrid htmlHelper = new Syncfusion.JavaScript.PivotGrid();
            static string connectionString = ConfigurationManager.ConnectionStrings["Adventure Works"].ConnectionString + "locale identifier=1033;";
            static int cultureIDInfoval = 1033; 
            JavaScriptSerializer serializer = new JavaScriptSerializer();
            string conStringforDB = "DataSource=" + HttpContext.Current.Server.MapPath(".").Split(new string[] { "\\api" }, StringSplitOptions.None)[0] + "\\database\\ReportsTable.sdf; Persist Security Info=False";
    
            [System.Web.Http.ActionName("InitializeGrid")]
            [System.Web.Http.HttpPost]
            public Dictionary<string, object> InitializeOlapGrid(Dictionary<string, object> jsonResult)
            {
                OlapDataManager DataManager = null;
                dynamic customData = serializer.Deserialize<dynamic>(jsonResult["customObject"].ToString());
                var cultureIDInfo = new System.Globalization.CultureInfo(("en-US")).LCID;
                if (customData is Dictionary<string, object> && customData.ContainsKey("Language"))
                {
                    cultureIDInfo = new System.Globalization.CultureInfo((customData["Language"])).LCID;
                }
                connectionString = connectionString.Replace("" + cultureIDInfoval + "", "" + cultureIDInfo + "");
                cultureIDInfoval = cultureIDInfo;
                DataManager = new OlapDataManager(connectionString);
                DataManager.Culture = new System.Globalization.CultureInfo((cultureIDInfo));
                DataManager.OverrideDefaultFormatStrings = true;
                if (jsonResult.ContainsKey("currentReport") && jsonResult["currentReport"].ToString() != "")
                    DataManager.SetCurrentReport(OLAPUTILS.Utils.DeserializeOlapReport(jsonResult["currentReport"].ToString()));
                else
                    DataManager.SetCurrentReport(CreateOlapReport());
                return htmlHelper.GetJsonData(jsonResult["action"].ToString(), DataManager, jsonResult.ContainsKey("gridLayout") ? jsonResult["gridLayout"].ToString() : null, Convert.ToBoolean(jsonResult["enablePivotFieldList"].ToString()));
            }
    
            [System.Web.Http.ActionName("DrillGrid")]
            [System.Web.Http.HttpPost]
            public Dictionary<string, object> DrillGrid(Dictionary<string, object> jsonResult)
            {
                dynamic customData = serializer.Deserialize<dynamic>(jsonResult["customObject"].ToString());
                OlapDataManager DataManager = new OlapDataManager(connectionString);
                if (customData is Dictionary<string, object> && customData.ContainsKey("Language"))
                {
                    var cultureIDInfo = new System.Globalization.CultureInfo((customData["Language"])).LCID;
                    connectionString = connectionString.Replace("" + cultureIDInfoval + "", "" + cultureIDInfo + "");
                    cultureIDInfoval = cultureIDInfo;
                    DataManager = new OlapDataManager(connectionString);
                    DataManager.Culture = new System.Globalization.CultureInfo((customData["Language"]));
                    DataManager.OverrideDefaultFormatStrings = true;
                }
                else
                    DataManager = new OlapDataManager(connectionString);
                DataManager.SetCurrentReport(OLAPUTILS.Utils.DeserializeOlapReport(jsonResult["currentReport"].ToString()));
                return htmlHelper.GetJsonData(jsonResult["action"].ToString(), connectionString, DataManager, jsonResult["cellPosition"].ToString(), jsonResult["headerInfo"].ToString(), jsonResult["layout"].ToString());
            }
    
            [System.Web.Http.ActionName("NodeDropped")]
            [System.Web.Http.HttpPost]
            public Dictionary<string, object> NodeDropped(Dictionary<string, object> jsonResult)
            {
                OlapDataManager DataManager = new OlapDataManager(connectionString);
                dynamic customData = serializer.Deserialize<dynamic>(jsonResult["customObject"].ToString());
                if (customData is Dictionary<string, object> && customData.ContainsKey("Language"))
                {
                    DataManager.Culture = new System.Globalization.CultureInfo((customData["Language"]));
                    DataManager.OverrideDefaultFormatStrings = true;
                }
                DataManager.SetCurrentReport(OLAPUTILS.Utils.DeserializeOlapReport(jsonResult["currentReport"].ToString()));
                return htmlHelper.GetJsonData(jsonResult["action"].ToString(), DataManager, jsonResult["dropType"].ToString(), jsonResult["nodeInfo"].ToString(), jsonResult.ContainsKey("filterParams") ? jsonResult["filterParams"].ToString() : null, jsonResult["gridLayout"].ToString(), true);
            }
    
            [System.Web.Http.ActionName("Filtering")]
            [System.Web.Http.HttpPost]
            public Dictionary<string, object> Filtering(Dictionary<string, object> jsonResult)
            {
                OlapDataManager DataManager = new OlapDataManager(connectionString);
                dynamic customData = serializer.Deserialize<dynamic>(jsonResult["customObject"].ToString());
                if (customData is Dictionary<string, object> && customData.ContainsKey("Language"))
                {
                    DataManager.Culture = new System.Globalization.CultureInfo((customData["Language"]));
                    DataManager.OverrideDefaultFormatStrings = true;
                }
                DataManager.SetCurrentReport(OLAPUTILS.Utils.DeserializeOlapReport(jsonResult["currentReport"].ToString()));
                return htmlHelper.GetJsonData(jsonResult["action"].ToString(), connectionString, DataManager, null, jsonResult["filterParams"].ToString(), jsonResult["gridLayout"].ToString());
            }
    
            [System.Web.Http.ActionName("FetchMembers")]
            [System.Web.Http.HttpPost]
            public Dictionary<string, object> FetchMembers(Dictionary<string, object> jsonResult)
            {
                OlapDataManager DataManager = new OlapDataManager(connectionString);
                DataManager.SetCurrentReport(OLAPUTILS.Utils.DeserializeOlapReport(jsonResult["currentReport"].ToString()));
                return htmlHelper.GetJsonData(jsonResult["action"].ToString(), DataManager, null, jsonResult["headerTag"].ToString());
            }
    
            [System.Web.Http.ActionName("Paging")]
            [System.Web.Http.HttpPost]
            public Dictionary<string, object> Paging(Dictionary<string, object> jsonResult)
            {
                OlapDataManager DataManager = new OlapDataManager(connectionString);
                DataManager.SetCurrentReport(htmlHelper.SetPaging(jsonResult["currentReport"].ToString(), jsonResult["pagingInfo"].ToString()));
                return htmlHelper.GetJsonData(jsonResult["action"].ToString(), DataManager, jsonResult["gridLayout"].ToString());
            }
    
            [System.Web.Http.ActionName("RemoveButton")]
            [System.Web.Http.HttpPost]
            public Dictionary<string, object> RemoveButton(Dictionary<string, object> jsonResult)
            {
                OlapDataManager DataManager = new OlapDataManager(connectionString);
                dynamic customData = serializer.Deserialize<dynamic>(jsonResult["customObject"].ToString());
                if (customData is Dictionary<string, object> && customData.ContainsKey("Language"))
                {
                    DataManager.Culture = new System.Globalization.CultureInfo((customData["Language"]));
                    DataManager.OverrideDefaultFormatStrings = true;
                }
                DataManager.SetCurrentReport(OLAPUTILS.Utils.DeserializeOlapReport(jsonResult["currentReport"].ToString()));
                return htmlHelper.GetJsonData(jsonResult["action"].ToString(), connectionString, DataManager, null, jsonResult["headerInfo"].ToString(), jsonResult["gridLayout"].ToString());
            }
    
            [System.Web.Http.ActionName("MemberExpanded")]
            [System.Web.Http.HttpPost]
            public Dictionary<string, object> MemberExpanded(Dictionary<string, object> jsonResult)
            {
                OlapDataManager DataManager = new OlapDataManager(connectionString);
                if (!string.IsNullOrEmpty(jsonResult["currentReport"].ToString()))
                    DataManager.SetCurrentReport(OLAPUTILS.Utils.DeserializeOlapReport(jsonResult["currentReport"].ToString()));
                return htmlHelper.GetJsonData(jsonResult["action"].ToString(), DataManager, Convert.ToBoolean(jsonResult["checkedStatus"].ToString()), jsonResult["parentNode"].ToString(), jsonResult["tag"].ToString(), jsonResult["cubeName"].ToString());
            }
    
            [System.Web.Http.ActionName("Export")]
            [System.Web.Http.HttpPost]
            public void Export()
            {
                string args = HttpContext.Current.Request.Form.GetValues(0)[0];
                OlapDataManager DataManager = new OlapDataManager(connectionString);
                string fileName = "Sample";
                htmlHelper.ExportPivotGrid(DataManager, args, fileName, System.Web.HttpContext.Current.Response);
            }
    
            [System.Web.Http.ActionName("SaveReport")]
            [System.Web.Http.HttpPost]
            public Dictionary<string, object> SaveReport(Dictionary<string, object> jsonResult)
            {
                string mode = jsonResult["operationalMode"].ToString();
                bool isDuplicate = true;
                SqlCeConnection con = new SqlCeConnection() { ConnectionString = conStringforDB };
                con.Open();
                SqlCeCommand cmd1 = null;
                foreach (DataRow row in GetDataTable().Rows)
                {
                    if ((row.ItemArray[0] as string).Equals(jsonResult["reportName"].ToString()))
                    {
                        isDuplicate = false;
                        cmd1 = new SqlCeCommand("update ReportsTable set Report=@Reports where ReportName like @ReportName", con);
                    }
                }
                if (isDuplicate)
                {
                    cmd1 = new SqlCeCommand("insert into ReportsTable Values(@ReportName,@Reports)", con);
                }
                cmd1.Parameters.AddWithValue("@ReportName", jsonResult["reportName"].ToString());
                if (mode == "clientMode")
                    cmd1.Parameters.AddWithValue("@Reports", Encoding.UTF8.GetBytes(jsonResult["clientReports"].ToString()).ToArray());
                else if (mode == "serverMode")
                    cmd1.Parameters.AddWithValue("@Reports", OLAPUTILS.Utils.GetReportStream(jsonResult["clientReports"].ToString()).ToArray());
                cmd1.ExecuteNonQuery();
                con.Close();
                return null;
            }
    
    
            [System.Web.Http.ActionName("LoadReportFromDB")]
            [System.Web.Http.HttpPost]
            public Dictionary<string, object> LoadReportFromDB(Dictionary<string, object> jsonResult)
            {
                string mode = jsonResult["operationalMode"].ToString();
                byte[] reportString = new byte[4 * 1024];
                var reports = "";
                Dictionary<string, object> dictionary = new Dictionary<string, object>();
                if (mode == "serverMode" && jsonResult.ContainsKey("clientReports"))
                {
                    reports = jsonResult["clientReports"].ToString();
                }
                else
                {
                    foreach (DataRow row in GetDataTable().Rows)
                    {
                        if ((row.ItemArray[0] as string).Equals(jsonResult["reportName"].ToString()))
                        {
                            if (mode == "clientMode")
                            {
                                reportString = row.ItemArray[1] as byte[];
                                dictionary.Add("report", Encoding.UTF8.GetString(reportString));
                                break;
                            }
                            else if (mode == "serverMode")
                            {
                                reports = OLAPUTILS.Utils.CompressData(row.ItemArray[1] as byte[]);
                                break;
                            }
                        }
                    }
                }
                if (reports != "")
                {
                    OlapDataManager DataManager = new OlapDataManager(connectionString);
                    dynamic customData = serializer.Deserialize<dynamic>(jsonResult["customObject"].ToString());
                    var cultureIDInfo = new System.Globalization.CultureInfo(("en-US")).LCID;
                    if (customData is Dictionary<string, object> && customData.ContainsKey("Language"))
                    {
                        cultureIDInfo = new System.Globalization.CultureInfo((customData["Language"])).LCID;
                    }
                    connectionString = connectionString.Replace("" + cultureIDInfoval + "", "" + cultureIDInfo + "");
                    cultureIDInfoval = cultureIDInfo;
                    DataManager.Culture = new System.Globalization.CultureInfo((cultureIDInfo));
                    DataManager.SetCurrentReport(OLAPUTILS.Utils.DeserializeOlapReport(reports));
                    DataManager.OverrideDefaultFormatStrings = true;
                    dictionary = htmlHelper.GetJsonData(jsonResult["action"].ToString(), DataManager, jsonResult["gridLayout"].ToString(), Convert.ToBoolean(jsonResult["enablePivotFieldList"].ToString()));
                }
                return dictionary;
            }
    
            private DataTable GetDataTable()
            {
                SqlCeConnection con = new SqlCeConnection() { ConnectionString = conStringforDB };
                con.Open();
                DataSet dSet = new DataSet();
                new SqlCeDataAdapter("Select * from ReportsTable", con).Fill(dSet);
                con.Close();
                return dSet.Tables[0];
            }
    
            [System.Web.Http.ActionName("DeferUpdate")]
            [System.Web.Http.HttpPost]
            public Dictionary<string, object> DeferUpdate(Dictionary<string, object> jsonResult)
            {
                OlapDataManager DataManager = new OlapDataManager(connectionString);
                dynamic customData = serializer.Deserialize<dynamic>(jsonResult["customObject"].ToString());
                if (customData is Dictionary<string, object> && customData.ContainsKey("Language"))
                {
                    DataManager.Culture = new System.Globalization.CultureInfo((customData["Language"]));
                    DataManager.OverrideDefaultFormatStrings = true;
                }
                DataManager.SetCurrentReport(OLAPUTILS.Utils.DeserializeOlapReport(jsonResult["currentReport"].ToString()));
                return htmlHelper.GetJsonData(jsonResult["action"].ToString(), connectionString, DataManager, null, jsonResult["filterParams"].ToString(), jsonResult.ContainsKey("gridLayout") ? jsonResult["gridLayout"].ToString() : null);
            }
    
            private OlapReport CreateOlapReport()
            {
                OlapReport olapReport = new OlapReport();
                olapReport.CurrentCubeName = "Adventure Works";
    
                MeasureElements measureElement = new MeasureElements();
                measureElement.Elements.Add(new MeasureElement { UniqueName = "[Measures].[Internet Sales Amount]" });
    
                DimensionElement dimensionElementRow = new DimensionElement();
                dimensionElementRow.Name = "Date";
                dimensionElementRow.AddLevel("Fiscal", "Fiscal Year");
    
                DimensionElement dimensionElementColumn = new DimensionElement();
                dimensionElementColumn.Name = "Customer";
                dimensionElementColumn.AddLevel("Customer Geography", "Country");
    
                olapReport.SeriesElements.Add(dimensionElementRow);
                olapReport.CategoricalElements.Add(dimensionElementColumn);
                olapReport.CategoricalElements.Add(measureElement);
    
                return olapReport;
            }
        }
    }