Click here to Skip to main content
14,602,454 members

SparkLines

Rate this:
3.96 (8 votes)
Please Sign up or sign in to vote.
3.96 (8 votes)
25 May 2007
SparkLine is a small, high resolution line chart. This article explains how to create and use SparkLines in your ASP.NET application.

Screenshot - SparkLine.gif

Introduction

Spark lines is this really cool way to visualize information. They are small high resolution line charts that show you where things are heading. Note that they don't show any detailed data, but only the general trend. This article explains how to create and use spark lines in your ASP.NET application.

Using the code

An ASP.NET page (sparkline.aspx) is producing the binary output to generate the image. The data enters the page via URL parameters.

Dim sBgColor As String = "ffffff"
Dim sAvgLineColor As String = "gray"
Dim sLineColor As String = "#000000"
Dim sStdDevColor As String = "dcdcdc"

Dim bStdDev As Boolean = True
Dim sData As String = "1,2,3"

Dim iImageWidth As Integer = 200
Dim iImageHeight As Integer = 60

Dim iTopMargin As Integer = 5
Dim iBottomMargin As Integer = 5
Dim iLeftMargin As Integer = 5
Dim iRightMargin As Integer = 30

Dim iMax As Double = 0
Dim iMin As Double = 0
Dim iAvg As Double = 0
Dim iSum As Double = 0
Dim iStdDev As Double = 0

Private Sub Page_Load(ByVal sender As System.Object, _
                      ByVal e As System.EventArgs) _
                      Handles MyBase.Load

    SetVars()

    Dim oData() As String = sData.Split(",")
    If oData.Length <= 1 Then
        Exit Sub
    End If

    SetAvg()

    Dim oPoints(oData.Length - 1) As Point
    Dim iScale As Double = (iImageHeight - (iTopMargin + iBottomMargin)) / _
                            Math.Abs(iMax - iMin)
    Dim iStepWidth As Double = (iImageWidth - (iLeftMargin + _
                                iRightMargin)) / (oData.Length - 1)

    If Not Double.IsInfinity(iScale) Then
        For i As Integer = 0 To oData.Length - 1
            Dim sValue As String = oData(i)
            Dim iValue As Double = 0
            If sValue <> "" And IsNumeric(sValue) Then
                iValue = CDbl(sValue)
            End If

            Dim x As Integer = (i * iStepWidth) + iLeftMargin
            Dim y As Integer = iImageHeight - (Math.Abs(iValue - iMin) * _
                                               iScale) - iBottomMargin
            oPoints(i) = New Point(x, y)
        Next
    End If

    Dim oBitmap As Bitmap = New Bitmap(iImageWidth, iImageHeight)
    Dim oPen As System.Drawing.Pen = New System.Drawing.Pen(GetColor(sLineColor))
    Dim oAvgPen As System.Drawing.Pen = New System.Drawing.Pen(GetColor(sAvgLineColor))
    Dim oGraphics As Graphics = Graphics.FromImage(oBitmap)
    oGraphics.SmoothingMode = SmoothingMode.AntiAlias
    oGraphics.FillRectangle(New SolidBrush(GetColor(sBgColor)), _
                            0, 0, iImageWidth, iImageHeight)

    Dim iMiddleY As Integer
    If Not Double.IsInfinity(iScale) Then

        iMiddleY = iImageHeight - (Math.Abs(iAvg - iMin) * iScale) - iBottomMargin

        'StdDev
        If bStdDev Then
            Dim oRect As New Rectangle
            oRect.Width = iImageWidth - (iRightMargin + iLeftMargin)
            oRect.Height = iStdDev * iScale
            oRect.X = iLeftMargin
            oRect.Y = iMiddleY - (oRect.Height / 2)
            oGraphics.FillRectangle(New SolidBrush(GetColor(sStdDevColor)), oRect)
        End If

        'Agv Line
        oGraphics.DrawLine(oAvgPen, iLeftMargin, iMiddleY, _
                           iImageWidth - iRightMargin, iMiddleY)

        'Lines
        oGraphics.DrawLines(oPen, oPoints)

        'Final Point
        Dim oLastPoint As Point = oPoints(oPoints.Length - 1)
        Dim oBrush As New SolidBrush(Color.Red)
        oGraphics.FillPie(oBrush, oLastPoint.X - 2, oLastPoint.Y - 2, 4, 4, 0, 360)

        'Final Value
        Dim drawString As String = oData(oData.Length - 1)
        Dim drawFont As New Font("Arial", 8)
        Dim drawBrush As New SolidBrush(Color.Black)
        oGraphics.DrawString(drawString, drawFont, drawBrush, _
                             oLastPoint.X + 2, oLastPoint.Y - 6)
    Else
        iMiddleY = iImageHeight / 2
        oGraphics.DrawLine(oAvgPen, iLeftMargin, iMiddleY, _
                           iImageWidth - iRightMargin, iMiddleY)
    End If

    Response.ContentType = "image/jpeg"
    oBitmap.Save(Response.OutputStream, ImageFormat.Jpeg)
    oGraphics.Dispose()
    oBitmap.Dispose()

End Sub

Private Sub SetAvg()

    Dim oData() As String = sData.Split(",")

    For i As Integer = 0 To oData.Length - 1
        Dim sValue As String = oData(i)
        Dim iValue As Double = 0
        If sValue <> "" And IsNumeric(sValue) Then
            iValue = CDbl(sValue)
        End If

        iSum += iValue
        If i = 0 Then
            iMax = iValue
            iMin = iValue
        Else
            If iMax < iValue Then iMax = iValue
            If iMin > iValue Then iMin = iValue
        End If
    Next

    iAvg = iSum / oData.Length

    Dim iVar As Double

    If bStdDev Then

        For i As Integer = 0 To oData.Length - 1
            Dim sValue As String = oData(i)
            Dim iValue As Double = 0
            If sValue <> "" And IsNumeric(sValue) Then
                iValue = CDbl(sValue)
            End If

            iVar += Math.Pow(iValue - iAvg, 2)
        Next

        iStdDev = Math.Sqrt(iVar / oData.Length)

    End If

End Sub

Private Sub SetVars()
    If Request.QueryString("data") <> "" Then
        sData = Request.QueryString("data")
    End If

    If Request.QueryString("StdDev") = "0" Then
        bStdDev = False
    End If

    If Request.QueryString("bgcolor") <> "" Then
        sBgColor = Request.QueryString("bgcolor")
    End If

    If Request.QueryString("avgcolor") <> "" Then
        sAvgLineColor = Request.QueryString("avgcolor")
    End If

    If Request.QueryString("linecolor") <> "" Then
        sLineColor = Request.QueryString("linecolor")
    End If

    If Request.QueryString("top") <> "" Then
        iTopMargin = Request.QueryString("top")
    End If

    If Request.QueryString("bottom") <> "" Then
        iBottomMargin = Request.QueryString("bottom")
    End If

    If Request.QueryString("left") <> "" Then
        iLeftMargin = Request.QueryString("left")
    End If

    If Request.QueryString("right") <> "" Then
        iRightMargin = Request.QueryString("right")
    End If

    If Request.QueryString("width") <> "" Then
        iImageWidth = Request.QueryString("width")
    End If

    If Request.QueryString("height") <> "" Then
        iImageHeight = Request.QueryString("height")
    End If

End Sub

Private Function GetColor(ByVal sColor As String) As System.Drawing.Color
    sColor = sColor.Replace("#", "")

    Dim oColor As Color = Color.FromName(sColor)
    Dim bColorEmpty As Boolean = oColor.R = 0 And oColor.G = 0 And oColor.B = 0
    If (bColorEmpty = False) Then Return oColor

    If sColor.Length <> 6 Then
        'On Error Return White
        Return Color.White
    End If

    Dim sRed As String = sColor.Substring(0, 2)
    Dim sGreen As String = sColor.Substring(2, 2)
    Dim sBlue As String = sColor.Substring(4, 2)

    oColor = System.Drawing.Color.FromArgb(HexToInt(sRed), _
             HexToInt(sGreen), HexToInt(sBlue))
    Return oColor
End Function

Function HexToInt(ByVal hexString As String) As Integer
    Return Integer.Parse(hexString, _
           System.Globalization.NumberStyles.HexNumber, Nothing)
End Function

The table below shows the list of parameters that the image page (sparkline.aspx) can accept.

ParameterDescriptionSample Data
dataComma delimited data to be visualized1,10,10,1
StdDevStandard Deviation Band1-show, 0-hide
bgcolorBackground coloryellow
avgcolorAverage line colorred
linecolorLine colorred
topTop margin5
bottomBottom margin5
leftLeft margin5
rightRight margin5
widthChart width200
heightChart height60

Another ASP.NET page (SparklineTest.aspx) reads the SQL Server 2000 Northwind database and builds a dynamic table based on the data. It passes the sales information for each day to the SparkLines image page (sparkline.aspx).

Dim sConnectionString As String = "Provider=SQLOLEDB.1;Password=test;" & _
"User ID=test;Initial Catalog=Northwind;Data Source=(local)"

Private Sub Page_Load(ByVal sender As System.Object, _
                      ByVal e As System.EventArgs) Handles MyBase.Load
    Response.Expires = 0
End Sub

Public Sub ShowSparklines()
    Response.Write("<table border=1 cellspacing=0>")

    Response.Write("<tr>")
    Response.Write("<th>Category</th>")
    Response.Write("<th>1998 Sales</th>")
    Response.Write("</tr>")

    Dim sSql As String = "SELECT CategoryID, CategoryName FROM Categories"
    Dim dr As OleDbDataReader = GetDataReader(sSql)
    While dr.Read
        Response.Write("<tr>")
        Response.Write("<td>" & _
                       dr.GetValue(dr.GetOrdinal("CategoryName")) & _
                       "</td>")
        Dim sCategoryID As String = _
            dr.GetValue(dr.GetOrdinal("CategoryID")) & ""
        Response.Write("<td>" & _
                       GetSparkLine(sCategoryID, 1998) & _
                       "</td>")
        Response.Write("</tr>")
    End While
    dr.Close()

    Response.Write("</table>")
End Sub

Private Function GetSparkLine(ByVal sCategoryID As String, _
                 ByVal sYear As String) As String
    Dim sSql As String = "SELECT o.OrderDate, SUM(od.UnitPrice" & _ 
      " * od.Quantity) AS Sales" & _
      " FROM Orders o INNER JOIN " & _
      " [Order Details] od ON o.OrderID = od.OrderID INNER JOIN" & _
      " Products p ON od.ProductID = p.ProductID" & _
      " WHERE p.CategoryID = " & sCategoryID & _
      " AND YEAR(o.OrderDate) = 1998" & _
      " GROUP BY o.OrderDate" & _
      " ORDER BY o.OrderDate"

    Dim dr As OleDbDataReader = GetDataReader(sSql)
    Dim sData As String
    While dr.Read
        If sData <> "" Then
            sData += ","
        End If
        sData += dr.GetValue(dr.GetOrdinal("Sales")) & ""
    End While
    dr.Close()

    Dim iWidth As Integer = 150
    Dim iHeight As Integer = 50

    Return "<img width=" & iWidth & " height=" & iHeight & _
        " src='sparkline.aspx?width=" & iWidth & _
                            "&height=" & iHeight & _
                            "&data=" & sData & "'>"
End Function

Friend Function GetDataReader(ByVal sSql As String) As OleDb.OleDbDataReader
    Dim cn As New OleDb.OleDbConnection(sConnectionString)
    cn.Open()
    Dim cm As New OleDb.OleDbCommand(sSql, cn)
    Return cm.ExecuteReader(CommandBehavior.CloseConnection)
End Function

Points of interest

You can read more about SparkLines on Edward Tufte's website.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Igor Krupitsky
Web Developer
United States United States
Igor is a business intelligence consultant working in Tampa, Florida. He has a BS in Finance from University of South Carolina and Masters in Information Management System from University of South Florida. He also has following professional certifications: MCSD, MCDBA, MCAD.

Comments and Discussions

 
Generalsample code does not work in IIS Pin
b_apparao22-Jan-08 1:12
Memberb_apparao22-Jan-08 1:12 
GeneralRe: sample code does not work in IIS - update Pin
b_apparao22-Jan-08 1:21
Memberb_apparao22-Jan-08 1:21 
GeneralInteresting Idea Pin
merlin98128-May-07 7:09
professionalmerlin98128-May-07 7:09 
GeneralError! Pin
barbod_blue25-May-07 22:36
Memberbarbod_blue25-May-07 22:36 
AnswerRe: Error! Pin
Igor Krupitsky28-May-07 3:52
mvaIgor Krupitsky28-May-07 3:52 
What is the error that you get?

To deploy and test this application:


  1. Create Visual Basic ASP.NET project
  2. Add the files to the project
  3. Change the connection string in SparklineTest.aspx.vb Line 25 to your connection string.
  4. Compile it.

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Article
Posted 25 May 2007

Stats

57.5K views
470 downloads
40 bookmarked