JavaScript LinkedCharts in ASP.NET (C#) with FusionCharts XT – Part 4

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

• Part 1 – Create JavaScript charts in ASP.NET (C#)
• Part 2 – Plot JavaScript charts using SQL Server data
• Part 3 – Create Drill-Down charts in ASP.NET(C#)
• Part 4 – Create LinkedCharts in ASP.NET(C#)

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: The overlay 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 in FusionCharts 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 the link attribute.

• LinkedCharts using Data URL

In the Data URL method, we can point to a static XML file which provides the data for this particular child. The syntax for Data URL method is:

The 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

When using the Data String method, the child chart’s data will have to be within the parent chart. Each data plot item in the parent chart is then linked to the child chart’s data (embedded in parent data source) by means of a unique data identifier. The syntax for Data String method is:

{child chart data comes here}

According to this syntax, this demo’s code would have to be:

• Data URL or Data String?

It is apparent from the samples above, the Data URL method is more concise than the Data String method. If you have more than 1 level of drill-down, your XML string will get large, and consequently difficult to debug. Load times for your charts might suffer too.

Creating LinkedCharts using Data URL method

As you already know from the first 3 articles, the Data URL method requires 3 things:

1. The parent chart (which we created in the 2nd article)
2. The correct path to the data provider in the link attribute (see below)
3. The data provider (see further below!)

In the project that you downloaded above, you will have 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.

In 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 as LinkedCharts_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 the configureLink() 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

Read more about Events in FusionCharts XT JavaScript API.

Download sample project

Concluding the series

In this series, we’ve covered a lot about creating interactive JavaScript charts from the ASP.NET (C#) environment. Here is a recap of all the articles in this series:

• Part 1 – Create JavaScript charts in ASP.NET (C#)
• Part 2 – Plot JavaScript charts using SQL Server data
• Part 3 – Create Drill-Down charts in ASP.NET(C#)
• Part 4 – Create LinkedCharts in ASP.NET(C#)

Let us know in the comments how you liked this series. We’re putting out more interesting content for all you developers. Stay tuned..