In the last part of FusionCharts XT with ASP.NET series, we will create LinkedCharts using MS SQL Server data and C#. LinkedCharts is a pioneering concept by FusionCharts, which allows you to create unlimited levels of drill-down using a single data source. LinkedCharts is most useful when the user requires to go from a summarized view to a granular view, such as annual revenue >> quarterly revenue >> monthly revenue, and back in the reverse order. LinkedCharts help in maintaining the context of the complete data set when the user is traversing down (or up). In LinkedCharts, the parent chart contains all the data or data sources for its child charts. The child charts can also be configured in every way imaginable.
Table of Contents
FusionCharts XT with ASP.NET
What we are going to visualize
In the previous article of this series, we created a drill-down chart, where the user can go from the annual revenue chart to the monthly revenue chart. We will make use of the same data, and create a LinkedChart: Theoverlay
button at the top-right helps a user immensely while traversing up or down a data set. You can check out a few more implementations of LinkedCharts in our Features Gallery.
.NET library bundled with FusionCharts XT
FusionCharts XT is essentially a JavaScript charting library. In order to render a chart, a certain amount of HTML and JavaScript is required, along with the chart data in XML or JSON. To automatically generate this HTML and JavaScript code, we provide a .NET library with FusionCharts XT, which generates the HTML and JavaScript required. You can find this library inFusionCharts XT Download Package > Code > CS > Bin > FusionCharts.dll
.
What we will need
- Visual Studio or Visual Web Developer
- SQL Server
- The latest version of FusionCharts XT
- The sample project that we created in the 2nd part of this series. This project has a database included in the Visual Studio solution. You will only need to modify the path to the database in the connection string.
Attributes of LinkedCharts
When creating LinkedCharts, you will have to provide the data for the child charts in either Data URL or Data String formats using thelink
attribute.
-
LinkedCharts using Data URL
newChart
prefix is used to indicate that it must invoke LinkedCharts. xmlURL
indicates that Data URL method is used to specify XML data for the linked child charts. In case of JSON data, jsonurl
is used. URL
specifies the data path for the linked chart that opens when this column is clicked.
So according to the syntax, you would provide the child chart’s data in this way:
We can also point to a virtual data provider, such as a server-side script which returns only XML, with Content-type
set to text/xml
:
-
LinkedCharts using Data String
{child chart data comes here}According to this syntax, this demo’s code would have to be:
-
Data URL or Data String?
Creating LinkedCharts using Data URL method
As you already know from the first 3 articles, the Data URL method requires 3 things:- The parent chart (which we created in the 2nd article)
- The correct path to the data provider in the
link
attribute (see below) - The data provider (see further below!)
Using_MS_SQL_Server.aspx
. Rename it to LinkedCharts_DataURL_Method.aspx
. In the code-behind for this page, edit the part where you create the dataplots as below:
// Construct the chart data in XML format // Provide the path to the data provider in the 'link' attribute, along with the 'year' in the querystring. xmlStr.AppendFormat("", rst["yr"].ToString(), rst["AMOUNT"].ToString(), Server.UrlEncode("DataProvider.aspx?year=" + rst["yr"].ToString()));Save. Let us now create the data provider for the child charts.
- Add a web form to the Solution and save it as
DataProvider.aspx
. - From
DataProvider.aspx
, delete everything except the first line. Since this page is supposed to only return the XML, we would not need any HTML tags here.
DataProvider.aspx.cs
, write the following code:
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data; using System.Data.Sql; using System.Data.SqlClient; using System.Text; using InfoSoftGlobal; public partial class DataProvider : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { // Construct the connection string to interface with the SQL Server Database string connStr2 = @"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Fusion Charts\Documents\Visual Studio 2010\WebSites\FusionChartsXT_with_ASPNET\App_Data\FusionChartsDB.mdf;Integrated Security=True;User Instance=True"; // Initialize the string which would contain the chart data in XML format StringBuilder xmlStr_monthly_data = new StringBuilder(); // Capture the year from the querystring String year = Request.QueryString["year"]; // Provide the relevant customization attributes to the chart xmlStr_monthly_data.Append(""); // Create a SQLConnection object using (SqlConnection conn2 = new SqlConnection(connStr2)) { // Establish the connection with the database conn2.Open(); // Construct and execute SQL query which would return the total amount of the sales for each month of the year specified. SqlCommand query_monthly_data = new SqlCommand("SELECT SUM(FC_OrderDetails.UnitPrice * FC_OrderDetails.Quantity) AS AMOUNT, DATENAME(MONTH, FC_Orders.OrderDate) AS Month FROM FC_Orders INNER JOIN FC_OrderDetails ON FC_OrderDetails.OrderID = FC_Orders.OrderID WHERE YEAR(FC_Orders.OrderDate) = '" + year + "' GROUP BY MONTH(FC_Orders.OrderDate), DATENAME(MONTH, FC_Orders.OrderDate) ORDER BY MONTH(FC_Orders.OrderDate), DATENAME(MONTH, FC_Orders.OrderDate)", conn2); // Begin iterating through the result set SqlDataReader monthly_data = query_monthly_data.ExecuteReader(); while (monthly_data.Read()) { // Construct the chart data in XML format xmlStr_monthly_data.AppendFormat("", monthly_data["Month"].ToString(), monthly_data["AMOUNT"].ToString()); } // End the XML string xmlStr_monthly_data.Append(""); // Close the result set Reader object and the Connection object monthly_data.Close(); conn2.Close(); // Set the ContentType to "text/xml" and write the XML string in response Response.ContentType = "text/xml"; Response.Write(xmlStr_monthly_data.ToString()); } } }Save. Switch to
LinkedCharts_DataURL_Method.aspx
and run the project. This is the LinkedChart that you should see:
Creating LinkedCharts using Data String method
For the Data String method, we would need only 1 page to do both – provide the data and render the chart. Add a new web form to the Solution and save it asLinkedCharts_DataString_Method.aspx
.
You can copy the contents from LinkedCharts_DataURL_Method.aspx
to LinkedCharts_DataString_Method.aspx
. Let us now concern ourselves with the code-behind:
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data; using System.Data.Sql; using System.Data.SqlClient; using System.Text; using InfoSoftGlobal; public partial class Using_MS_SQL_Server : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { // Create a blank array, and add each 'year' to it string[] year = new string[4]; // To iterate through the array int i = 0; // Construct the connection string to interface with the SQL Server Database string connStr = @"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Fusion Charts\Documents\Visual Studio 2010\WebSites\FusionChartsXT_with_ASPNET\App_Data\FusionChartsDB.mdf;Integrated Security=True;User Instance=True"; // Initialize the string which would contain the chart data in XML format StringBuilder xmlStr = new StringBuilder(); // Provide the relevant customization attributes to the chart xmlStr.Append(""); // Create a SQLConnection object using (SqlConnection conn = new SqlConnection(connStr)) { // Establish the connection with the database conn.Open(); // Construct and execute SQL query which would return the total revenue for each year SqlCommand query = new SqlCommand("SELECT SUM(FC_OrderDetails.UnitPrice * FC_OrderDetails.Quantity) AS AMOUNT, YEAR(FC_Orders.OrderDate) AS yr FROM FC_Orders INNER JOIN FC_OrderDetails ON FC_OrderDetails.OrderID = FC_Orders.OrderID GROUP BY YEAR(FC_Orders.OrderDate) ORDER BY YEAR(FC_Orders.OrderDate)", conn); // Begin iterating through the result set SqlDataReader rst = query.ExecuteReader(); while (rst.Read()) { // Accumulate the 'year' in the array. This will be useful later. year[i] = rst["yr"].ToString(); // Construct the chart data in XML format, and provide the links to the child charts' data xmlStr.AppendFormat("", rst["yr"].ToString(), rst["AMOUNT"].ToString(), rst["yr"].ToString()); i++; } // Close the result set Reader object and the Connection object rst.Close(); for(int j = 0; j < 3; j++) { // Construct and execute SQL query which would return the total revenue for each month of the specified year SqlCommand linkedChartQuery = new SqlCommand("SELECT SUM(FC_OrderDetails.UnitPrice * FC_OrderDetails.Quantity) AS AMOUNT, DATENAME(MONTH, FC_Orders.OrderDate) AS Month FROM FC_Orders INNER JOIN FC_OrderDetails ON FC_OrderDetails.OrderID = FC_Orders.OrderID WHERE YEAR(FC_Orders.OrderDate) = '" + year[j] + "' GROUP BY MONTH(FC_Orders.OrderDate), DATENAME(MONTH, FC_Orders.OrderDate) ORDER BY MONTH(FC_Orders.OrderDate), DATENAME(MONTH, FC_Orders.OrderDate)", conn); // Begin iterating through the result set SqlDataReader linkedChartRst = linkedChartQuery.ExecuteReader(); // Specify the ID for each child chart xmlStr.AppendFormat(""); // Construct the XML data for the child chart xmlStr.AppendFormat(""); while (linkedChartRst.Read()) { // Construct the chart data in XML format xmlStr.AppendFormat("", linkedChartRst["Month"].ToString(), linkedChartRst["AMOUNT"].ToString()); } // Terminate the child chart's data, as well as the 'linkeddata' attribute xmlStr.Append(""); // Close the result set Reader object linkedChartRst.Close(); } // Close the XML for the parent chart xmlStr.Append(""); conn.Close(); // Set the rendering mode to JavaScript, from the default Flash. FusionCharts.SetRenderer("javascript"); // Call the RenderChart method, pass the correct parameters, and write the return value to the Literal tag chart_from_db.Text = FusionCharts.RenderChart( "FusionChartsXT/Column2D.swf", // Path to chart's SWF "", // Page which returns chart data. Leave blank when using Data String. xmlStr.ToString(), // String containing the chart data. Leave blank when using Data URL. "annual_revenue", // Unique chart ID "640", "340", // Width & Height of chart false, // Disable Debug Mode true); // Register with JavaScript object } } }Save. Run the project. This is what you should see:
Configuring LinkedCharts
Using the FusionCharts XT JavaScript API, you can configure every aspect of the child charts. For this, we’ve provided theconfigureLink()
function. You can pass all the properties that a FusionCharts constructor accepts, as parameters to this function.
The syntax for the configureLink()
is:
configureLink(objConfiguration: Object, level:Number)As two separate parameters, the first parameter is an Object containing all configurations (chart and overlay-Button). The second parameter accepts a number which denotes the level being configured. The first drill-down level is 0 (zero). By default, the child charts have the same type of chart as the parent. We can configure them to use different chart types. Let us see how we can change the child chart we created above to a Pie chart. In
LinkedChart_DataURL_Method.aspx
, write the following JavaScript code after the
tag:
Save the file, navigate to this page, and click on one of the columns again. This is what you should see:
You can also render LinkedCharts in another container, or within a lightbox. Read more about such configurations in our documentation.
LinkedCharts Events
The FusionCharts XT JavaScript API provides a number of events when LinkedCharts are in action. Each parent chart can listen to the events and can add more functionality to the implementation. The events are as follows:- BeforeLinkedItemOpen: Fires before a child chart is created
- LinkedItemOpened: Fires after a child chart is created
- BeforeLinkItemClose: Fires before a child chart is closed
- LinkedItemClosed: Fires after a child chart is closed
Download sample project
![Download Sample Project](https://blog.fusioncharts.com/wp-content/uploads/2012/04/Download_Sample_Code1.png)
Ruby
April 24, 2014, 11:05 pm1.Is it possible that using HLinearGauges to be child chart?
2.How to realize that one parent chart control two child chart? Is that possible?
Waiting for your answer. Thank you!
Ruby
April 25, 2014, 7:14 am1.Is it possible that using HLinearGauge to be child chart?
2.Can one parent chart link two child charts using configureLink()?
Swarnam
April 25, 2014, 10:19 amHi Ruby,
1.Is it possible that using HLinearGauge to be child chart?
>>Yes, it is possible to render HLinearGauge as a child chart. To configure the descendant charts, call configureLink function on the chart instance and to this function, we pass an Object as parameter. The Object contains the property type with its value “HLinearGauge”. This helps in configuring the descendant charts to show up as Horizontal Linera Gauge.
Ref.Code:
FusionCharts(“myChartId”).configureLink( { type: “HLinearGauge” })
2.Can one parent chart link two child charts using configureLink()?
>>No, it is not possible to render two child charts using configureLink(). However, you can call custom JavaScript function which will render two descendant chart.
Athiban
February 25, 2015, 3:16 pmI need to call a javascript function on eacch drill downs.???How is this possible
link=’newchart-xml-” and link=’JavaScript:alert(” + text +”);..Both required……