Getting started
13 Feb 201924 minutes to read
Important
Starting with v16.2.0.x, if you refer to Syncfusion assemblies from trial setup or from the NuGet feed, include a license key in your projects. Refer to this link to learn about registering Syncfusion license key in your ASP.NET Core application to use our components.
Creating a simple application with pivot client and relational data sources (client mode)
This section covers the information required to populate a simple pivot client with relational data completely on the client-side.
Project initialization
Create a new ASP.NET Empty Web Application by using the Visual Studio IDE and name the project “PivotClientDemo”.
Now, add a Web Form to the empty web application. For adding a Web Form, right-click the project in the solution explorer and select Add > New Item. In the Add New Item window, select Web Form and name it GettingStarted.aspx, and then click Add.
Now, add the following dependency libraries as references to your web application. To add them to your application, right-click References in the solution explorer and select Add Reference. In the Reference Manager dialog, under Assemblies > Extension, the following Syncfusion libraries will be found.
- Syncfusion.EJ
- Syncfusion.EJ.Pivot
- Syncfusion.EJ.Export
Register the referred assemblies in Web.config files available at the root of the application.
<compilation debug="true" targetFramework="4.5">
<assemblies>
……
……
<add assembly="Syncfusion.EJ, Version= 24.2450.3, Culture=neutral, PublicKeyToken=3d67ed1f87d44c89" />
<add assembly="Syncfusion.EJ.Pivot, Version= 24.2450.3, Culture=neutral, PublicKeyToken=3d67ed1f87d44c89" />
<add assembly="Syncfusion.EJ.Export, Version= 24.2450.3, Culture=neutral, PublicKeyToken=3d67ed1f87d44c89" />
</assemblies>
</compilation>Scripts and CSS references
Create a GettingStarted.aspx page, and scripts and style sheets that are required to render the pivot client control in a web application are mentioned below in an appropriate order:
- ej.web.all.min.css
- jQuery-3.0.0.min.js
- ej.web.all.min.js
- jsrender.min.js
Scripts and style sheets are referred under the <head> tag in the GettingStarted.aspx page.
<head>
<link href="http://cdn.syncfusion.com/24.2.3/js/web/flat-azure/ej.web.all.min.css" rel="stylesheet" type="text/css" />
<script src="http://cdn.syncfusion.com/js/assets/external/jquery-3.0.0.min.js" type="text/javascript"></script>
<script src="http://cdn.syncfusion.com/24.2.3/js/web/ej.web.all.min.js" type="text/javascript"></script>
<script src="http://cdn.syncfusion.com/js/assets/external/jsrender.min.js" type="text/javascript"></script>
</head>Initialize pivot client
You can drag and drop the pivot client control from the toolbox (under Syncfusion BI Web category) or manually define the control as shown in the following code sample of the “GettingStarted.aspx” page.
After placing the control into the web page, add ‘ScriptManager’ next to it to generate appropriate scripts.
<%@ Register Assembly="Syncfusion.EJ.Pivot" Namespace="Syncfusion.JavaScript.Web" TagPrefix="ej" %>
<%@ Register Assembly="Syncfusion.EJ.Pivot" Namespace="Syncfusion.JavaScript.Models" TagPrefix="ej" %>
<html>
……
……
<body>
<form runat="server">
<ej:PivotClient ID="PivotClient1" runat="server" Title="Relational Browser" ClientIDMode="Static">
</ej:PivotClient>
<asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>
</form>
</body>
</html>Populate pivot client with data
This section illustrates how to populate the pivot client control by using a sample JSON data as shown below:
<ej:PivotClient ID="PivotClient1" runat="server" Title="Relational Browser" ClientIDMode="Static">
<ClientSideEvents Load="onLoad" />
</ej:PivotClient>
<asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>
<script type="text/javascript">
function onLoad(args) {
args.model.dataSource.data = [
{ Amount: 100, Country: "Canada", Date: "FY 2005", Product: "Bike", Quantity: 2, State: "Alberta" },
{ Amount: 200, Country: "Canada", Date: "FY 2006", Product: "Van", Quantity: 3, State: "British Columbia" },
{ Amount: 300, Country: "Canada", Date: "FY 2007", Product: "Car", Quantity: 4, State: "Brunswick" },
{ Amount: 150, Country: "Canada", Date: "FY 2008", Product: "Bike", Quantity: 3, State: "Manitoba" },
{ Amount: 200, Country: "Canada", Date: "FY 2006", Product: "Car", Quantity: 4, State: "Ontario" },
{ Amount: 100, Country: "Canada", Date: "FY 2007", Product: "Van", Quantity: 1, State: "Quebec" },
{ Amount: 200, Country: "France", Date: "FY 2005", Product: "Bike", Quantity: 2, State: "Charente-Maritime" },
{ Amount: 250, Country: "France", Date: "FY 2006", Product: "Van", Quantity: 4, State: "Essonne" },
{ Amount: 300, Country: "France", Date: "FY 2007", Product: "Car", Quantity: 3, State: "Garonne (Haute)" },
{ Amount: 150, Country: "France", Date: "FY 2008", Product: "Van", Quantity: 2, State: "Gers" },
{ Amount: 200, Country: "Germany", Date: "FY 2006", Product: "Van", Quantity: 3, State: "Bayern" },
{ Amount: 250, Country: "Germany", Date: "FY 2007", Product: "Car", Quantity: 3, State: "Brandenburg" },
{ Amount: 150, Country: "Germany", Date: "FY 2008", Product: "Car", Quantity: 4, State: "Hamburg" },
{ Amount: 200, Country: "Germany", Date: "FY 2008", Product: "Bike", Quantity: 4, State: "Hessen" },
{ Amount: 150, Country: "Germany", Date: "FY 2007", Product: "Van", Quantity: 3, State: "Nordrhein-Westfalen" },
{ Amount: 100, Country: "Germany", Date: "FY 2005", Product: "Bike", Quantity: 2, State: "Saarland" },
{ Amount: 150, Country: "United Kingdom", Date: "FY 2008", Product: "Bike", Quantity: 5, State: "England" },
{ Amount: 250, Country: "United States", Date: "FY 2007", Product: "Car", Quantity: 4, State: "Alabama" },
{ Amount: 200, Country: "United States", Date: "FY 2005", Product: "Van", Quantity: 4, State: "California" },
{ Amount: 100, Country: "United States", Date: "FY 2006", Product: "Bike", Quantity: 2, State: "Colorado" },
{ Amount: 150, Country: "United States", Date: "FY 2008", Product: "Car", Quantity: 3, State: "New Mexico" },
{ Amount: 200, Country: "United States", Date: "FY 2005", Product: "Bike", Quantity: 4, State: "New York" },
{ Amount: 250, Country: "United States", Date: "FY 2008", Product: "Car", Quantity: 3, State: "North Carolina" },
{ Amount: 300, Country: "United States", Date: "FY 2007", Product: "Van", Quantity: 4, State: "South Carolina" }
]
}
</script>The JSON data is set to the “Data” property present in the “DataSource” object. The “DataSource” object allows you to set both data source and fields that should be displayed in the row, column, value, and filter section of the pivot client control.
<ej:PivotClient ID="PivotClient1" runat="server" Title="Relational Browser" ClientIDMode="Static">
<DataSource>
<Rows>
<ej:Field FieldName="Country" FieldCaption="Country"></ej:Field>
</Rows>
<Columns>
<ej:Field FieldName="Product" FieldCaption="Product"></ej:Field>
</Columns>
<Values>
<ej:Field FieldName="Amount" FieldCaption="Amount"></ej:Field>
</Values>
</DataSource>
<ClientSideEvents Load="onLoad" />
</ej:PivotClient>Now, the pivot client is rendered with pivot chart, and the pivot grid is rendered with “Country” field in the row, “Product” field in the column, and “Amount” field in the value section.
Creating a simple application with pivot client and relational data sources(server mode)
This section covers the information required to create a simple pivot client bound to relational data source.
NOTE
ASP.NET MVC web application contains a service that will transfer the data to server-side, process it, and return it to client-side for control rendering and re-rendering. The service utilized for communication can be WCF or WebAPI based on user requirement.
Project initialization
Create a new ASP.NET Empty Web Application by using the Visual Studio IDE and name the project “PivotClientDemo”.
Now, add a Web Form to the empty web application. For adding a Web Form, right-click the project in the solution explorer and select Add > New Item. In the Add New Item window, select Web Form and name it GettingStarted.aspx, and then click Add.
To set an appropriate start page, right-click the “GettingStarted.aspx” in the solution explorer and select “Set As Start Page”.
Now, add the following dependency libraries as references to your web application. To add them to your application, right-click References in the solution explorer and select Add Reference. In the Reference Manager dialog, under Assemblies > Extension, the following Syncfusion libraries will be found.
NOTE
If you have installed any version of Essential Studio, then the location of Syncfusion libraries is [system drive:\Program Files (x86)\Syncfusion\Essential Studio\24.2.3\Assemblies].
- Syncfusion.Compression.Base
- Syncfusion.Linq.Base
- Syncfusion.Olap.Base
- Syncfusion.PivotAnalysis.Base
- System.Data.SqlServerCe (Version: 4.0.0.0)
- Syncfusion.XlsIO.Base
- Syncfusion.Pdf.Base
- Syncfusion.DocIO.Base
- Syncfusion.EJ
- Syncfusion.EJ.Web
- Syncfusion.EJ.Export
- Syncfusion.EJ.Pivot
Scripts and CSS initialization
The scripts and style sheets that are required to render the pivot client control in a web application are mentioned below in an appropriate order:
- ej.web.all.min.css
- jQuery-3.0.0.min.js
- ej.web.all.min.js
- jsrender.min.js
Click here to know more about scripts and style sheets available in online (CDN link).
Scripts and style sheets are referred under the <head> tag in the GettingStarted.aspx page.
<head>
<link href="http://cdn.syncfusion.com/24.2.3/js/web/flat-azure/ej.web.all.min.css" rel="stylesheet" type="text/css" />
<script src="http://cdn.syncfusion.com/js/assets/external/jquery-3.0.0.min.js" type="text/javascript"></script>
<script src="http://cdn.syncfusion.com/24.2.3/js/web/ej.web.all.min.js" type="text/javascript"></script>
<script src="http://cdn.syncfusion.com/js/assets/external/jsrender.min.js" type="text/javascript"></script>
</head>Control initialization
You can drag and drop the pivot client control from the toolbox (under Syncfusion BI Web category) or manually define the control as shown in the following code sample of the “GettingStarted.aspx” page:
After placing the control into the web page, add ‘ScriptManager’ next to it to generate appropriate scripts.
<%@ Register Assembly="Syncfusion.EJ.Pivot, Version=24.2450.3, Culture=neutral, PublicKeyToken=3d67ed1f87d44c89" Namespace="Syncfusion.JavaScript.Web" TagPrefix="ej" %>
<html>
……
……
<body>
<form runat="server">
<ej:PivotClient ID="PivotClient1" Url="/Relational" runat="server" Title="Relational Browser" ClientIDMode="Static"></ej:PivotClient>
<asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>
</form>
</body>
</html>The “Url” property in the pivot client control points the service endpoint, where the data is processed and fetched in the form of JSON. The services used in the pivot client control as endpoint are WCF and WebAPI.
NOTE
The above “GettingStarted.aspx” contains WebAPI URL, which is “/Relational”. If the WCF service is used as an endpoint, then the URL will look like “/RelationalService.svc”.
If you enter the code manually instead of drag and drop from the toolbox, then you can register the referenced assemblies in the Web.config file.
<compilation debug="true" targetFramework="4.5">
<assemblies>
……
……
<add assembly="Syncfusion.EJ, Version= 24.2450.3, Culture=neutral, PublicKeyToken=3d67ed1f87d44c89" />
<add assembly="Syncfusion.EJ.Web, Version= 24.2450.3, Culture=neutral, PublicKeyToken=3d67ed1f87d44c89" />
<add assembly="Syncfusion.EJ.Pivot, Version= 24.2450.3, Culture=neutral, PublicKeyToken=3d67ed1f87d44c89" />
<add assembly="Syncfusion.EJ.Export, Version= 24.2450.3, Culture=neutral, PublicKeyToken=3d67ed1f87d44c89" />
<add assembly="Syncfusion.Compression.Base, Version= 24.2450.3, Culture=neutral, PublicKeyToken=3d67ed1f87d44c89" />
<add assembly="Syncfusion.Linq.Base, Version= 24.2450.3, Culture=neutral, PublicKeyToken=3d67ed1f87d44c89" />
<add assembly="Syncfusion.Olap.Base, Version= 24.2450.3, Culture=neutral, PublicKeyToken=3d67ed1f87d44c89" />
<add assembly="Syncfusion.PivotAnalysis.Base, Version= 24.2450.3, Culture=neutral, PublicKeyToken=3d67ed1f87d44c89" />
<add assembly="Syncfusion.Pdf.Base, Version= 24.2450.3, Culture=neutral, PublicKeyToken=3d67ed1f87d44c89" />
<add assembly="Syncfusion.XlsIO.Base, Version= 24.2450.3, Culture=neutral, PublicKeyToken=3d67ed1f87d44c89" />
<add assembly="Syncfusion.DocIO.Base, Version= 24.2450.3, Culture=neutral, PublicKeyToken=3d67ed1f87d44c89" />
</assemblies>
</compilation>WebAPI
Adding a WebAPI controller
To add a WebAPI controller in an existing web application, right-click the project in the solution explorer and select Add > New Item. In the Add New Item window, select WebAPI Controller Class and name it RelationalController.cs, and then click Add.
Now, the WebAPI controller is added to the application that comprises the “RelationalController.cs” file.
NOTE
While adding the WebAPI controller class, add the mandatory suffix ‘Controller’. For example, in this demo, the controller is named as “RelationalController”.
Next, remove all existing methods such as “Get”, “Post”, “Put”, and “Delete” present in the RelationalController.cs file.
namespace PivotClientDemo
{
public class RelationalController: ApiController
{
}
}Adding the list of namespaces
The following is the list of namespaces to be added on top of the main class in the RelationalController.cs file:
using Syncfusion.JavaScript;
using Syncfusion.PivotAnalysis.Base;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlServerCe;
using System.Linq;
using System.Net.Http;
using System.Text;
using System.Web;
using System.Web.Http;
using System.Web.Script.Serialization;
namespace PivotClientDemo
{
public class RelationalController : ApiController
{
}
}Data source initialization
A simple collection is provided as a data source for the pivot client in this demo section. This data source is placed inside a separate class named “ProductSales” in the RelationalController.cs file. Please find the code sample below:
namespace PivotClientDemo
{
………
………
………
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>
{
}
}
}Service methods in WebAPI controller
Now, you can define the service methods in the RelationalController class, find in the RelationalController.cs file which was created while adding the WebAPI controller class to your web application.
namespace PivotClientDemo
{
public class RelationalController : ApiController
{
PivotClient pivotClient = new PivotClient();
PivotChart pivotChart = new PivotChart();
PivotGrid pivotGrid = new PivotGrid();
JavaScriptSerializer serializer = new JavaScriptSerializer();
string conStringforDB = "";//Enter appropriate connection string to connect database for saving and loading operation of reports
[System.Web.Http.ActionName("InitializeClient")]
[System.Web.Http.HttpPost]
public Dictionary<string, object> InitializeClient(Dictionary<string, object> jsonResult)
{
this.BindData();
return pivotClient.GetJsonData(jsonResult["action"].ToString(), ProductSales.GetSalesData(), null);
}
[System.Web.Http.ActionName("FetchMembers")]
[System.Web.Http.HttpPost]
public Dictionary<string, object> FetchMembers(Dictionary<string, object> jsonResult)
{
pivotClient.PopulateData(jsonResult["currentReport"].ToString());
var dict = pivotClient.GetJsonData(jsonResult["action"].ToString(), ProductSales.GetSalesData(), jsonResult["headerTag"].ToString());
return dict;
}
[System.Web.Http.ActionName("DrillChart")]
[System.Web.Http.HttpPost]
public Dictionary<string, object> DrillChart(Dictionary<string, object> jsonResult)
{
this.BindData();
this.pivotChart.PivotEngine.PivotRows = this.pivotClient.PivotReport.PivotRows;
this.pivotChart.PivotEngine.PivotColumns = this.pivotClient.PivotReport.PivotColumns;
this.pivotChart.PivotEngine.PivotCalculations = this.pivotClient.PivotReport.PivotCalculations;
return pivotChart.GetJsonData(jsonResult["action"].ToString(), ProductSales.GetSalesData(), jsonResult["drilledSeries"].ToString());
}
[System.Web.Http.ActionName("Filtering")]
[System.Web.Http.HttpPost]
public Dictionary<string, object> Filtering(Dictionary<string, object> jsonResult)
{
pivotClient.PopulateData(jsonResult["currentReport"].ToString());
var dict = pivotClient.GetJsonData(jsonResult["action"].ToString(), ProductSales.GetSalesData(), jsonResult["filterParams"].ToString());
return dict;
}
[System.Web.Http.ActionName("NodeDropped")]
[System.Web.Http.HttpPost]
public Dictionary<string, object> NodeDropped(Dictionary<string, object> jsonResult)
{
Dictionary<string, object> dict = pivotClient.GetJsonData(jsonResult["action"].ToString(), ProductSales.GetSalesData(), jsonResult["args"].ToString());
return dict;
}
[System.Web.Http.ActionName("ToolbarOperations")]
[System.Web.Http.HttpPost]
public Dictionary<string, object> ToolbarOperations(Dictionary<string, object> jsonResult)
{
Dictionary<string, object> dict = pivotClient.GetJsonData(jsonResult["action"].ToString(), ProductSales.GetSalesData(), jsonResult["args"].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);
pivotClient.PopulateData(gridParams["currentReport"]);
string fileName = "Sample";
pivotClient.ExportPivotClient(ProductSales.GetSalesData(), args, fileName, System.Web.HttpContext.Current.Response);
}
[System.Web.Http.ActionName("SaveReportToDB")]
[System.Web.Http.HttpPost]
public Dictionary<string, object> SaveReportToDB(Dictionary<string, object> jsonResult)
{
string operationalMode = jsonResult["operationalMode"].ToString(), analysisMode = jsonResult["analysisMode"].ToString(), reportName = string.Empty;
bool isDuplicate = true;
SqlCeConnection con = new SqlCeConnection() { ConnectionString = conStringforDB };
con.Open();
reportName = jsonResult["reportName"].ToString() + "##" + operationalMode.ToLower() + "#>>#" + analysisMode.ToLower();
SqlCeCommand cmd1 = null;
foreach (DataRow row in GetDataTable().Rows)
{
if ((row.ItemArray[0] as string).Equals(reportName))
{
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.Add("@ReportName", reportName);
cmd1.Parameters.Add("@Reports", Encoding.UTF8.GetBytes(jsonResult["clientReports"].ToString()).ToArray());
cmd1.ExecuteNonQuery();
con.Close();
Dictionary<string, object> dictionary = new Dictionary<string, object>();
dictionary.Add("CurrentAction", "Save");
return dictionary;
}
[System.Web.Http.ActionName("RemoveReportFromDB")]
[System.Web.Http.HttpPost]
public Dictionary<string, object> RemoveReportFromDB(Dictionary<string, object> jsonResult)
{
string operationalMode = jsonResult["operationalMode"].ToString(), analysisMode = jsonResult["analysisMode"].ToString(), reportName = string.Empty;
SqlCeConnection con = new SqlCeConnection() { ConnectionString = conStringforDB };
con.Open();
reportName = jsonResult["reportName"].ToString() + "##" + operationalMode.ToLower() + "#>>#" + analysisMode.ToLower();
SqlCeCommand cmd1 = null;
foreach (DataRow row in GetDataTable().Rows)
{
if ((row.ItemArray[0] as string).Equals(reportName))
{
cmd1 = new SqlCeCommand("DELETE FROM ReportsTable WHERE ReportName LIKE '%" + reportName + "%'", con);
}
}
cmd1.ExecuteNonQuery();
con.Close();
Dictionary<string, object> dictionary = new Dictionary<string, object>();
dictionary.Add("CurrentAction", "Remove");
return dictionary;
}
[System.Web.Http.ActionName("RenameReportInDB")]
[System.Web.Http.HttpPost]
public Dictionary<string, object> RenameReportInDB(Dictionary<string, object> jsonResult)
{
string operationalMode = jsonResult["operationalMode"].ToString(), analysisMode = jsonResult["analysisMode"].ToString(), reportName = string.Empty, renameReport = string.Empty;
SqlCeConnection con = new SqlCeConnection() { ConnectionString = conStringforDB };
con.Open();
reportName = jsonResult["selectedReport"].ToString() + "##" + operationalMode.ToLower() + "#>>#" + analysisMode.ToLower();
renameReport = jsonResult["renameReport"].ToString() + "##" + operationalMode.ToLower() + "#>>#" + analysisMode.ToLower();
SqlCeCommand cmd1 = null;
foreach (DataRow row in GetDataTable().Rows)
{
if ((row.ItemArray[0] as string).Equals(reportName))
{
cmd1 = new SqlCeCommand("update ReportsTable set ReportName=@RenameReport where ReportName like '%" + reportName + "%'", con);
}
}
cmd1.Parameters.Add("@RenameReport", renameReport);
cmd1.ExecuteNonQuery();
con.Close();
Dictionary<string, object> dictionary = new Dictionary<string, object>();
dictionary.Add("CurrentAction", "Rename");
return dictionary;
}
[System.Web.Http.ActionName("FetchReportListFromDB")]
[System.Web.Http.HttpPost]
public Dictionary<string, object> FetchReportListFromDB(Dictionary<string, object> jsonResult)
{
string reportNames = string.Empty, currentRptName = string.Empty, operationalMode = jsonResult["operationalMode"].ToString(), analysisMode = jsonResult["analysisMode"].ToString();
foreach (System.Data.DataRow row in GetDataTable().Rows)
{
currentRptName = (row.ItemArray[0] as string);
if (currentRptName.IndexOf("##" + operationalMode + "#>>#" + analysisMode) >= 0)
{
currentRptName = currentRptName.Replace("##" + operationalMode + "#>>#" + analysisMode, "");
reportNames = reportNames == "" ? currentRptName : reportNames + "__" + currentRptName;
}
}
Dictionary<string, object> dictionary = new Dictionary<string, object>();
dictionary.Add("ReportNameList", reportNames);
dictionary.Add("action", jsonResult["action"].ToString());
return dictionary;
}
[System.Web.Http.ActionName("LoadReportFromDB")]
[System.Web.Http.HttpPost]
public Dictionary<string, object> LoadReportFromDB(Dictionary<string, object> jsonResult)
{
PivotReport report = new PivotReport();
string operationalMode = jsonResult["operationalMode"].ToString(), analysisMode = jsonResult["analysisMode"].ToString();
Dictionary<string, object> dictionary = new Dictionary<string, object>();
string currentRptName = string.Empty;
foreach (DataRow row in GetDataTable().Rows)
{
currentRptName = (row.ItemArray[0] as string).Replace("##" + operationalMode.ToLower() + "#>>#" + analysisMode.ToLower(), "");
if (currentRptName.Equals(jsonResult["reportName"].ToString()))
{
byte[] reportString = new byte[2 * 1024];
reportString = (row.ItemArray[1] as byte[]);
if (analysisMode.ToLower() == "pivot" && operationalMode.ToLower() == "servermode")
dictionary = pivotClient.GetJsonData("LoadReport", ProductSales.GetSalesData(), Encoding.UTF8.GetString(reportString));
else
dictionary.Add("report", Encoding.UTF8.GetString(reportString));
break;
}
}
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];
}
private void BindData()
{
this.pivotClient.PivotReport.PivotRows.Add(new PivotItem { FieldMappingName = "Country", FieldHeader = "Country", TotalHeader = "Total" });
this.pivotClient.PivotReport.PivotColumns.Add(new PivotItem { FieldMappingName = "Product", FieldHeader = "Product", TotalHeader = "Total" });
this.pivotClient.PivotReport.PivotCalculations.Add(new PivotComputationInfo { CalculationName = "Amount", Description = "Amount", FieldHeader = "Amount", FieldName = "Amount", Format = "C", SummaryType = Syncfusion.PivotAnalysis.Base.SummaryType.DoubleTotalSum });
}
}
.....
..... // Initialize the datasource
.....
}Configure routing in the global application class
To add a Global.asax, right-click the project in the solution explorer and select Add > New Item. In the Add New Item window, select Global Application Class and name it Global.asax, and then click Add. After adding the Global.asax file, immediately add the namespace “using System.Web.Http;”, and then configure the routing as shown in the following code example:
public class Global: System.Web.HttpApplication
{
protected void Application_Start(object sender, EventArgs e)
{
GlobalConfiguration.Configuration.Routes.MapHttpRoute(name: "DefaultApi", routeTemplate: "{controller}/{action}/{id}", defaults: new
{
id = RouteParameter.Optional
});
AppDomain.CurrentDomain.SetData("SQLServerCompactEditionUnderWebHosting", true);
}
}Now, the pivot client is rendered with the pivot chart, and the pivot grid is rendered with “Country” field in the row, “Product” field in the column, and “Amount” field in the value section.
WCF
This section demonstrates the utilization of WCF service as endpoint binding the relational data source to a simple pivot client. For more details on this topic, click here.