Essential Studio for Asp.Net | Demos

SHOWCASE SAMPLES
Showcase samples
    Mode:
    Editing:
    <%@ Page Title="PivotGrid-Cell Editing-ASP.NET-SYNCFUSION"   Language="C#" MetaDescription="This demo for Syncfusion Essential JS1 for ASP.NET pivot grid control describes how to edit the value cells at runtime." MasterPageFile="~/Samplebrowser.Master" AutoEventWireup="true" CodeBehind="CellEditing.aspx.cs" Inherits="WebSampleBrowser.CellEditing" %>
    
    <%@ Register Assembly="Syncfusion.EJ.Pivot" Namespace="Syncfusion.JavaScript.Web" TagPrefix="ej" %>
    <%@ Register Assembly="Syncfusion.EJ.Pivot" Namespace="Syncfusion.JavaScript.Models" TagPrefix="ej" %>
    
    <asp:Content ID="Content1" runat="server" ContentPlaceHolderID="ControlsSection">
        
        <ej:PivotGrid ID="PivotGrid1" EnableCellEditing="true" runat="server" 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="Date" FieldCaption="Date"></ej:Field>
                    <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="ScriptContent" runat="server" ContentPlaceHolderID="ScriptSection">
        <script type="text/javascript">
            var pivotGrid;
            $(function () {
                $("#clientMode, #serverMode").ejRadioButton({ change: "renderGrid" });
                $("#Btn").ejButton({
                    roundedCorner: true,
                    size: "small",
                    type: ej.ButtonType.Button,
                    click: "Reset"
                });
                pivotGrid = $("#PivotGrid1").data("ejPivotGrid");
                $("#sampleProperties").ejPropertiesPanel();
            });
            function onLoad(args) {
                if (args.model.dataSource.data == null)
                    args.model.dataSource.data = pivot_dataset;
            }
            function renderGrid(args) {
                $(pivotGrid.element).html("");
                if (args.model.id == "clientMode") {
                    pivotGrid.model.operationalMode = ej.PivotGrid.OperationalMode.ClientMode;
                    pivotGrid.model.url = "";
                    pivotGrid.setOlapReport(null);
                    pivotGrid.model.dataSource = {
                        data: pivot_dataset, cube: "",
                        rows: [{ fieldName: "Country", fieldCaption: "Country"}, { fieldName: "State", fieldCaption: "State" }],
                        columns: [{ fieldName: "Date", fieldCaption: "Date" },{ fieldName: "Product", fieldCaption: "Product" }],
                        values: [{ fieldName: "Amount", fieldCaption: "Amount" }, { fieldName: "Quantity", fieldCaption: "Quantity" }],
                        filters:[]
                    };
                }
                else {
                    pivotGrid.model.dataSource = { data: null, cube: "" };
                    pivotGrid.model.operationalMode = ej.PivotGrid.OperationalMode.ServerMode;
                    pivotGrid.model.url = "../api/RelationalGrid";
                }
                pivotGrid.refreshPivotGrid();
            }
            function Reset(args)
            {
                pivotGrid.refreshPivotGrid();
            }
         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="Content2" runat="server" ContentPlaceHolderID="StyleSection">
        <style type="text/css">
            #PivotGrid1 {
                height: 350px; 
                width: 100%; 
                overflow: auto;
                float:left;
            }
            .row .cols-sample-area  {
                 overflow: auto;
                 width: 100%
            }
             .row .cols-prop-area {
                margin-top:10px;
                width: 45%;
                height: auto;
                min-height: 80px;
            }
            label {
                font-weight:normal;
            }
            @media (min-width: 1920px) {.row .cols-prop-area { width: 26% !important; } }
        </style>
    </asp:Content>
    
    <asp:Content ID="Content3" runat="server" ContentPlaceHolderID="PropertySection">
         <div id="sampleProperties">
                    <div class="prop-grid">
                       <div class="row">
                            <table>
                                <tr>
                                    <td class="col-md-3" style="width:100px">
                                        Mode:
                                    </td>
                                    <td class="col-md-3" style="width:120px">
                                        <input type="radio" name="dftView" id="clientMode" checked="checked" /><label for="clientMode" class="clslab">Client Mode</label>
                                    </td>
                                    <td class="col-md-3" style="width:120px">
                                        <input type="radio" name="dftView" id="serverMode" /><label for="serverMode" class="clslab">Server Mode</label>
                                    </td>
                                </tr>
                            </table>
                        </div>
                        <div class="row">
                            <table>
                                <tr>
                                    <td class="col-md-3" style="width:100px">
                                        Editing:
                                    </td>
                                    <td class="col-md-3" style="width:120px;margin-top:-4px">
                                        <input type="button" id="Btn" value="Reset" />
                                    </td>
                                </tr>
                            </table>
                        </div>
                    </div>
                </div>
    </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 CellEditing : 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>
            {
            }
        }
    }