Drilldown Silverlight Charts using Visifire

Chirag

Creating Drilldown chart is a common requirement in any RIA dashboard. Drilldown charts give rich user experience. In this blog let’s see how we can create drilldown charts using Visifire.

In this sample application, chart can be drilled down till 2 levels. The default chart would show yearly sales of a fictitious company for the period 2006-2008. On click of column representing the sales for an year, monthly sales for that year is shown up. Further it can be drilled down to find product wise sales for the selected month.

This sample uses Javascript API to generate charts. The complete application can be downloaded from here. The code is completely commented & explained wherever necessary. Refer the rest of the blog if you need more help.

Click to see the sample in action

Data can be pulled in from any data source. To keep the example simple let’s store the sales data in an array.

var sales;
// Create array for the Sales data
sales = [{year: 2006, month: "Jan", ProductX: 20000, ProductY: 25000, ProductZ: 15000},
         {year: 2006, month: "Feb", ProductX: 10000, ProductY: 23000, ProductZ: 25000},
         {year: 2006, month: "Mar", ProductX: 22000, ProductY: 38000, ProductZ: 83000},
         {year: 2006, month: "Apr", ProductX: 23456, ProductY: 32435, ProductZ: 24223},
         {year: 2006, month: "May", ProductX: 25142, ProductY: 32415, ProductZ: 10923},
         {year: 2006, month: "Jun", ProductX: 18254, ProductY: 15342, ProductZ: 10934},
         {year: 2006, month: "Jul", ProductX: 18562, ProductY: 19832, ProductZ: 10293},
         {year: 2006, month: "Aug", ProductX: 28376, ProductY: ..............

Construct the XML string from the data stored in the array.

function getYearlySales()
{
    isChart1Added = true;
    var myXAML;
    // Constructing Data XAML
    myXAML = '<vc:Chart Theme="Theme2" Width="650" Height="450" ColorSet="Visifire1" Background="Black" xmlns:vc="clr-namespace:Visifire.Charts;assembly=SLVisifire.Charts" >' + "\n" + "\n";
    myXAML = myXAML + '<vc:Chart.Titles>' + "\n";
    myXAML = myXAML + '<vc:Title Text= "Yearly Sales" />' + "\n";
    myXAML = myXAML + '</vc:Chart.Titles>' + "\n";
    myXAML = myXAML + '<vc:Chart.PlotArea>' + "\n";
    myXAML = myXAML + '<vc:PlotArea Background= "#0A0A0A" />' + "\n";
    myXAML = myXAML + '</vc:Chart.PlotArea>' + "\n";
    myXAML = myXAML + '<vc:Chart.AxesX>' + "\n";
    myXAML = myXAML + '<vc:Axis Title="Year" />' + "\n";
    myXAML = myXAML + '</vc:Chart.AxesX>' + "\n";
    myXAML = myXAML + '<vc:Chart.AxesY>' + "\n";
    myXAML = myXAML + '<vc:Axis Title="Amount" Prefix="$" />' + "\n" + "\n";
    myXAML = myXAML + '</vc:Chart.AxesY>' + "\n";
    myXAML = myXAML + '<vc:Chart.Series>' + "\n";
    myXAML = myXAML + '<vc:DataSeries RenderAs="Column">' + "\n";
    myXAML = myXAML + '<vc:DataSeries.DataPoints>' + "\n";
    sales.sort(sortByYear);
    var totalSales = 0;
    currYear = sales[0].year;
    for (var arrayIndex = 0; arrayIndex < sales.length; arrayIndex++)
    {
        if(sales[arrayIndex].year == currYear)
        {
            currYear = sales[arrayIndex].year;
            totalSales += sales[arrayIndex].ProductX + sales[arrayIndex].ProductY + sales[arrayIndex].ProductZ;
        }
        else
        {
            myXAML = myXAML + '<vc:DataPoint AxisXLabel="' + currYear + '" YValue="' + totalSales + '"/>' + "\n";
            totalSales = 0;
            currYear++;
        }
    }
    myXAML = myXAML + '<vc:DataPoint AxisXLabel="' + currYear + '" YValue="' + totalSales + '"/>' + "\n";
    myXAML = myXAML + '</vc:DataSeries.DataPoints>' + "\n";
    myXAML = myXAML + "\n" + '</vc:DataSeries>' + "\n";
    myXAML = myXAML + '</vc:Chart.Series>' + "\n";
    myXAML = myXAML + '</vc:Chart>';
    return myXAML;
}

First, create the chart for yearly sales. Next, attach an event handler for “MouseLeftButtonUp” event to the DataSeries. On fire of this event, this chart is replaced with a chart with monthly sales for the year represented by the selected DataPoint in that DataSeries. Event handler can be attached as shown in the code snippet below.

// Attaching event handler for MouseLeftButtonUp event
vChart1.preLoad = function(args) {
    chart1 = args[0];
    chart1.Series[0].MouseLeftButtonUp = function(e) {
        onMouseUpMonthlySales(e.AxisXLabel); //Calling the function to replace the chart
    };
};

Use the same logic to replace this chart with a chart with product wise sales for the selected month. The crux of a drilldown chart is to attach an event handler to update the chart on fire of some event. I think we have accomplished the same swiftly in this sample. A link/button to return back to the previous state of the chart would give a holistic user experience.

You can see the sample in action here. The complete solution can be downloaded from here.

Cheers,

Team Visifire.


Comments

  1. Aziz
    May 20th, 2009 | 3:41 pm

    This is great! Is there documentation on how to bind to external data sources? Thanks

  2. vivek
    May 21st, 2009 | 11:00 am

    Hi Aziz,

    With external Data Sources, do you mean creating chart by fetching data from SQL Server or some other source?

  3. Aziz
    June 23rd, 2009 | 5:32 pm

    Yes, creating chart by fetching data from from SQL Server. Thanks!

  4. vivek
    June 24th, 2009 | 1:32 am

    Hi Aziz,

    Yes you can create a chart by fetching data from SQL Server. Please have a look at the blog post below:

    http://www.visifire.com/blog/2009/05/21/creating-silverlight-charts-from-sql-data-using-aspnet-and-visfire/

  5. August 5th, 2009 | 4:45 pm

    [...] Ejemplo II. [...]

  6. Vinod
    August 17th, 2009 | 12:27 pm

    Hi, when dowloading source code I get SL.Visifire.charts.xap file and Visifire2.js file. i am interested in looking into source code of xap file as to how it is constructed,any pointers where I can find ? Thanks.

  7. Girish
    November 16th, 2009 | 2:09 am

    The Total for
    2006 is 1,172,425
    2007 is 1,063,997 were as Figure shown in 960,046 shortage of 103,951
    i.e. for Jan 2007 Figures are left out

    2008 is 1,097,997 were as Figure shown in 1,039,734 shortage of 58,243
    i.e. for Jan 2008 Figures are left out

    Tried to solve could not succeed added the following lines on to html file
    Works fine, Could your please get the bug and mail me the results
    {year: 2007, month: “Jan”, ProductX: 0, ProductY: 0, ProductZ: 0},
    {year: 2008, month: “Jan”, ProductX: 0, ProductY: 0, ProductZ: 0},

    ========================================================================================
    Instead of 2 levels and 3 Products will it possible to increase from 3 products to 6 product
    if so what are the changes required, Please inform

  8. vivek
    November 16th, 2009 | 2:52 am

    Hi Grirish,

    The sales for 2007 and 2008 for the month of January was left out. You can do the changes by adding the following line inside the else part of the for loop (inside getYearlySales() function).

    totalSales += sales[arrayIndex].ProductX + sales[arrayIndex].ProductY + sales[arrayIndex].ProductZ;

    I have updated the sample, you can download it now and check.

    Yes you can increase as many products as you want. You just need to put them inside the array and changes will be required in getProductwiseMonthlySales() function.

  9. Girish
    November 16th, 2009 | 8:51 am

    Hi Vivek,
    Thanks for your quick response. Did the changes as advised, Works Fine.
    I tried to increase by one product i.e. instead of Product x,y and Z added Product A
    Unfortunately could not succeed, It hangs nothing comes, Unable to Debug
    If possible make one sample increasing both Years and Product.

  10. Anand
    January 27th, 2010 | 6:52 am

    A sample drilldown code for use with a sharepoint list too would be much appreciated.
    TIA.

  11. Blackburn
    March 11th, 2010 | 5:13 am

    Hi everyone,

    I’m interested in a simple example for this script but taking the data from a SQL db.

    I have tried to do it, but can’t see the chart in a html file

    PS: sorry for my english

  12. vivek
    March 11th, 2010 | 10:14 am

    Hi Blackburn,

    You can use Asp.Net and connect to a Sql server DataBase in order to get the data for Chart.

    Please have a look at the documentation sample below:
    http://www.visifire.com/documentation/Visifire_Documentation/Common_Tasks/Creating_Chart_From_SQL_Data_using_Asp.Net_and_Visifire.htm

  13. Blackburn
    March 12th, 2010 | 7:24 am

    Thanks Vivek;

    I’ve resolved my problem, the solution isn’t the most “elegant” but it run.

    Thank you again.

  14. dan
    December 15th, 2010 | 11:34 pm

    hi,

    thank you for your work. i am very stuck, please help.

    in my code behind C#, on mouse up event of data series, i clear all of the data series of the chart and then insert new data series and data points.

    it works. but the animation is not there.

    How do i make sure that on the mouse up event, after new data series are added, the chart can replay the animations?

  15. dan
    December 15th, 2010 | 11:42 pm

    i got it to work by managed code but really don’t like the flickering and reloading of the entire chart. only the bars should have refreshed, not the entire chart

  16. Somnath
    December 16th, 2010 | 5:50 am

    Dan,

    Visifire Chart supports real-time update. I will suggest you to clear the old DataSeries and add new DataSeries in existing Chart in order to avoid flickering.

  17. Akash
    April 18th, 2011 | 7:37 am

    i need to create drill down analysis on chart, this will be a 5 level deep drill
    data will be coming from data table and should be in following format:

    Row Labels MOC 1 MOC 2 MOC 3 MOC 4 MOC 5 MOC 6
    National 2 2 2 3 2 2
    -East 3 2 3 2 3 2
    –Area7 3 2 3 2 3 2
    —LM13 4 2 3 2 4 2
    —LM14 2 3 4 2 1 3
    –Area8 2 2 2 2 2 2
    —LM15 2 2 2 3 3 1
    —-CUST30 1 3 1 2 5 1
    —-CUST31 2 0 2 3 0 1
    —LM16 4 2 2 2 2 5
    —-CUST32 4 2 2 2 2 5
    -North 2 2 2 4 2 1
    –Area1 2 3 2 3 2 1
    —LM1 1 2 2 2 1 1
    —-CUST1 3 5 0 1 0 1
    —-CUST2 0 0 5 2 2 0
    —-CUST3 0 2 1 4 2 1
    —LM2 3 4 3 5 4 3
    —-CUST4 2 3 3 5 4 5
    —-CUST5 3 4 2 5 3 0
    –Area2 1 2 3 4 2 1
    —LM3 2 1 3 4 1 1
    —-CUST6 1 0 5 3 0 1
    —-CUST7 3 1 1 4 1 1
    —LM4 0 4 2 4 4 1
    —-CUST8 0 4 2 4 4 1
    South 2 2 2 3 2 3
    –Area5 2 2 2 3 2 3
    —LM10 2 3 3 4 2 2
    —-CUST20 3 3 4 2 3 2
    —-CUST21 1 3 1 5 0 1
    —LM9 1 2 2 3 2 4
    —-CUST17 0 1 1 2 5 5
    —-CUST18 4 1 0 4 0 2
    —-CUST19 0 3 5 3 0 4
    –Area6 3 3 1 3 3 2
    —LM11 3 2 2 3 2 3
    —-CUST22 5 0 0 2 4 4
    —-CUST23 1 3 4 4 0 1
    —LM12 4 5 0 3 4 1
    —-CUST24 4 5 0 3 4 1
    West 2 2 2 2 3 3
    –Area3 1 2 3 2 3 3
    —LM5 0 2 3 3 3 3
    —-CUST10 0 4 3 0 0 4
    —-CUST11 0 0 4 5 5 3
    —-CUST9 1 2 1 4 4 2
    —LM6 3 3 3 2 3 3
    —-CUST12 4 3 3 2 5 5
    —-CUST13 2 3 2 1 1 0
    –Area4 3 2 1 2 3 2
    —LM7 4 0 2 3 4 2
    —-CUST14 5 0 1 0 3 0
    —-CUST15 2 0 2 5 4 4
    —LM8 3 5 0 0 3 2
    —-CUST16 3 5 0 0 3 2

    Following are the levels:
    1. National (will always have four regions)
    2. Regional (can have 5-8 Areas)
    3. Area (can have 5-8 LM)
    4. LM (can have 2-3 CUST)
    5. CUST

    i need to create a multiline drilldown chart for each level.

    please help me

  18. Vivek
    April 18th, 2011 | 8:54 am

    Aksah,

    Do you want to create new chart for each level with one Line DataSeries.

    Regards,
    Vivek

  19. February 14th, 2013 | 7:25 am

    Hi,
    I want to create nth level dril down chart using . its posible or not ?

    Thanks

  20. February 14th, 2013 | 7:26 am

    Hi,
    I want to create nth level dril down chart using visifire. its posible or not ?

    Thanks

  21. vivek
    February 14th, 2013 | 8:50 am

    ganpat,

    Can you please elaborate the chart’s drill down flow (from level 1) so that we can suggest you a solution.

Leave a reply