Essential Studio for Asp.Net | Demos

SHOWCASE SAMPLES
Showcase samples
    <%@ Page Title="PivotGrid-WebAPI-ASP.NET-SYNCFUSION"   Language="C#" MetaDescription="This demo for Syncfusion Essential JS1 for ASP.NET pivot grid control describes how to bind the pivot data through Web API service" MasterPageFile="~/Samplebrowser.Master" AutoEventWireup="true" CodeBehind="RelationalWebAPI.aspx.cs" Inherits="WebSampleBrowser.RelationalWebAPI" %>
    
    
    <%@ 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" url="../api/RelationalGrid" EnableGroupingBar="true" ClientIDMode="Static">
            <ClientSideEvents RenderSuccess="renderFieldList"/>
        </ej:PivotGrid>
        <ej:PivotSchemaDesigner ID="PivotSchemaDesigner" runat="server"></ej:PivotSchemaDesigner>
    </asp:Content>
    
    <asp:Content ID="ScriptContent" runat="server" ContentPlaceHolderID="ScriptSection">
        <script type="text/javascript">
            var pivotGrid;
            $(function () {
                $("#sampleProperties").ejPropertiesPanel();
                $("#chkGroupingBar,#chkFieldList").ejCheckBox({ size: "medium", checked: true, change: "renderPivotGrid" });
                chkGroupingBar = $('#chkGroupingBar').data("ejCheckBox"); chkFieldList = $('#chkFieldList').data("ejCheckBox");
                pivotGrid = $("#PivotGrid").data("ejPivotGrid");
            });
            function renderPivotGrid(args) {
                var groupingBar, fieldlist = false, gridPanel = "", gridSchemaPanel = "";
                $(".e-pivotgrid").remove();
                $(".e-pivotschemadesigner").remove();
                groupingBar = chkGroupingBar.model.checked;
                fieldlist = chkFieldList.model.checked;
                gridPanel = ej.buildTag("div#PivotGrid1", "", { width: !chkFieldList.model.checked ? "90%" : "50%" })[0].outerHTML;
                if (chkFieldList.model.checked)
                    gridSchemaPanel = ej.buildTag("div#PivotSchemaDesigner", "", { height: "650px", width: "40%", "float": "right", "display": "block", "margin-top": "20px" })[0].outerHTML;
                $(gridPanel).appendTo(".cols-sample-area");
                $(gridSchemaPanel).appendTo(".cols-sample-area");
                $("#PivotGrid1").ejPivotGrid({
                    url: "../api/RelationalGrid", enableGroupingBar: groupingBar, afterServiceInvoke: fieldlist ? "renderFieldList" : ""
                });
            }
            function renderFieldList(args) {
                if (args.action == "initialize") {
                    var PivotSchemaDesigner = $(".e-pivotschemadesigner").data('ejPivotSchemaDesigner');
                    if (PivotSchemaDesigner != null && PivotSchemaDesigner.model.pivotControl == null) {
                        PivotSchemaDesigner.model.pivotControl = this;
                        PivotSchemaDesigner.model.layout = "excel";
                        PivotSchemaDesigner.model.enableWrapper = true;
                        PivotSchemaDesigner._load();
                    }
                    else if (PivotSchemaDesigner == null) {
                        $("#PivotSchemaDesigner").ejPivotSchemaDesigner({ pivotControl: this, layout: ej.PivotSchemaDesigner.Layouts.Excel });
                    }
                }
            }
            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">
            .e-pivotgrid {
                height: 350px; 
                width: 50%; 
                overflow: auto;
                float:left;
            }
            .e-pivotschemadesigner{
            width: 40% !important; 
            }
            .row .cols-prop-area {
                margin-top:30px;
                width: 30%;
                height: 120px;
                min-height: 0px;
            }
             .row .cols-sample-area {
                 width: 100%;
             }
             @media (min-width: 1920px) {.row .cols-prop-area { width: 21% !important; } }
        </style>
    </asp:Content>
    
    <asp:Content ID="Content3" runat="server" ContentPlaceHolderID="PropertySection">
                    <div id="sampleProperties">
                        <div class="prop-grid">
                            <table>
                                <tr>
                                    <td class="chkrad">
                                        <label for="chkGroupingBar">GroupingBar:  </label> <input type="checkbox" id="chkGroupingBar" />
                                         </td>
                                    <td class="chkrad">
                                        <label for="chkFieldList">FieldList:  </label> <input type="checkbox" id="chkFieldList" />
                                    </td>
                                </tr>
                            </table>
                        </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 RelationalWebAPI : 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>
            {
            }
        }
    }