locked
Charting values in two different series in the same chart RRS feed

  • Question

  • I have been trying to figure out how to use a chart for a specific purpose and have not been able to do so. Here is the scenario:

    Lets say I have a DB Table as follows:

    AcctStatus char(1)

    Amount Double

    Account status can have one of two values: H or N.  Call it "Haves" and "HaveNot". The amount is an amount that can vary from lets say a value of -3 to +3 with accuracy up to 7 decimal places. The population of the database in on the order of 250,000 rows.

    I want to chart the distribution of values of Amount with one line for the Haves and one line for the HaveNots. I want the values of Amount along the X axis, and the number of accounts on the Y axis. So I am a little stuck both on how to construct the query to get the values, then how to split the data in that query between the two series so I can show the comparison of the distribution of Amount values between the Have and HaveNots. Any clues would help.

    It seems I can get the data, but since there is only one datasource per chart, how do I divide the values up?


    Mike

    Thursday, September 5, 2013 10:07 PM

Answers

  • Which version of VS are you using?  Are you using the Chart control? Does the Chart control you are using have the DataBindingCrossTable property?

    The VS 2008 chart control DataBindingCrossTable property would be set as:

    Chart1.DataBindingCrossTable(dv, "AcctStatus", "Amt", "Count", "")

    where dv is a dataview of the datatable containing the data:

    dim dv as new dataview(dt)

    The looping was assuming you were drawing the chart without the chart control.


    The difference between genius and stupidity is that genius has its limits.


    • Edited by Spooky201 Friday, September 6, 2013 5:52 PM
    • Marked as answer by MihaIvan Saturday, September 7, 2013 4:43 PM
    Friday, September 6, 2013 5:50 PM
  • (Part 2 - creating the chart.)

    To get two series on the same chart, you need to use two series. You can decide which series each data point goes into by checking the AcctStatus, like this:

    Sub CreateChart()
    
        Chart1.Series.Clear()
    
        Dim haves As New Series
        Dim haveNots As New Series
    
        haves.Name = "Haves"
        haveNots.Name = "HaveNots"
    
        'TODO: use your connection string here
        Dim connStr = "server=.\SQLEXPRESS;initial catalog=testing;integrated security=SSPI;"
    
        Using conn As New SqlConnection(connStr)
            Dim count As Integer
            Dim status As String
            Dim amountBin As Decimal
    
            Dim sql = <sql>
                          SELECT COUNT(*) AS [Count]
                                ,[AcctStatus]
                                ,CAST(Amount AS DECIMAL(3,0)) AS [Bin]
                            FROM [testing].[dbo].[HaveHaveNots]
                            GROUP BY [AcctStatus], CAST(Amount AS DECIMAL(3,0))
                      </sql>.Value
    
            Dim sqlCmd As New SqlCommand(sql, conn)
            conn.Open()
            Dim rdr = sqlCmd.ExecuteReader
            While rdr.Read
                count = rdr.GetInt32(0)
                status = rdr.GetString(1)
                amountBin = rdr.GetDecimal(2)
    
                If status = "H" Then
                    haves.Points.Add(New DataPoint(amountBin, count))
                Else
                    haveNots.Points.Add(New DataPoint(amountBin, count))
                End If
            End While
    
            conn.Close()
    
        End Using
    
        'TODO: give the chart a pleasing appearance.
        Chart1.Series.Add(haves)
        Chart1.Series.Add(haveNots)
    
    End Sub

    - you'll need these Imports for that:

    Imports System.Data.SqlClient
    Imports System.Windows.Forms.DataVisualization.Charting
    

    and with my test data you get a chart like this:

    HTH,

    Andrew


    • Edited by Andrew Morton Friday, September 6, 2013 6:28 PM Imports
    • Marked as answer by MihaIvan Saturday, September 7, 2013 4:47 PM
    Friday, September 6, 2013 6:26 PM

All replies

  • Not tested, but try this SQL Statement.

    SELECT  Count(*) as Count, AcctStatus, Cast(Amount as Decimal(3,2)) as amt
      FROM TableName group by AcctStatus, Cast(Amount as Decimal(3,2)) Order By AcctStatus, Cast(Amount as Decimal(3,2))

    I cast Amount as a decimal and rounded to 2 places

    Loop through the datareader (datatable) and when AcctStatus changes, change the color of the pen. 


    The difference between genius and stupidity is that genius has its limits.




    • Edited by Spooky201 Friday, September 6, 2013 12:15 AM
    Thursday, September 5, 2013 10:43 PM
  • The select works great. I think. However I am still confused as to how I get this broken down into two series on the chart. I assume that the last sentence has something to do with this, but I have no idea of what you are talking about.

    I though all you had to do was plug the datatable into the chart and the rest was handled by the chart control. This is a Windows Form application by the way, not a web app.

    So where does the looping through the datareader happen, and how is the "Pen" changed? How does this divide it into the two series on the chart? I think I can see how to plot two DIFFERENT Y values from the same datatable using two different colums applied across the same X axis, but how can I use the SAME Y value (count) for two subsets of the whole table?


    Mike

    Friday, September 6, 2013 4:29 PM
  • Which version of VS are you using?  Are you using the Chart control? Does the Chart control you are using have the DataBindingCrossTable property?

    The VS 2008 chart control DataBindingCrossTable property would be set as:

    Chart1.DataBindingCrossTable(dv, "AcctStatus", "Amt", "Count", "")

    where dv is a dataview of the datatable containing the data:

    dim dv as new dataview(dt)

    The looping was assuming you were drawing the chart without the chart control.


    The difference between genius and stupidity is that genius has its limits.


    • Edited by Spooky201 Friday, September 6, 2013 5:52 PM
    • Marked as answer by MihaIvan Saturday, September 7, 2013 4:43 PM
    Friday, September 6, 2013 5:50 PM
  • As you have a lot of x-values (the amounts), you will probably want to bin them into discrete amounts (as will happen with Topdog224's use of rounding). For example, given a table [HaveHaveNots] with this data:

    AcctStatus	Amount
    H	1.23
    H	1.34
    H	-1.2
    H	2.7
    N	-2.7
    N	-2
    N	-2.3

    You probably want to get results like this:

    Count	AcctStatus	AmountBin
    1	N	-3
    2	N	-2
    1	H	-1
    2	H	1
    1	H	3

    (Due to the limited amount of data in my example table, I have rounded the amounts to zero decimal places.)

    Which you can get with this query:

    SELECT COUNT(*) AS [Count]
           ,[AcctStatus]
           ,CAST(Amount AS DECIMAL(3,0)) AS [AmountBin]
    FROM [HaveHaveNots]
    GROUP BY [AcctStatus], CAST(Amount AS DECIMAL(3,0))

    HTH,

    Andrew

    Friday, September 6, 2013 6:14 PM
  • (Part 2 - creating the chart.)

    To get two series on the same chart, you need to use two series. You can decide which series each data point goes into by checking the AcctStatus, like this:

    Sub CreateChart()
    
        Chart1.Series.Clear()
    
        Dim haves As New Series
        Dim haveNots As New Series
    
        haves.Name = "Haves"
        haveNots.Name = "HaveNots"
    
        'TODO: use your connection string here
        Dim connStr = "server=.\SQLEXPRESS;initial catalog=testing;integrated security=SSPI;"
    
        Using conn As New SqlConnection(connStr)
            Dim count As Integer
            Dim status As String
            Dim amountBin As Decimal
    
            Dim sql = <sql>
                          SELECT COUNT(*) AS [Count]
                                ,[AcctStatus]
                                ,CAST(Amount AS DECIMAL(3,0)) AS [Bin]
                            FROM [testing].[dbo].[HaveHaveNots]
                            GROUP BY [AcctStatus], CAST(Amount AS DECIMAL(3,0))
                      </sql>.Value
    
            Dim sqlCmd As New SqlCommand(sql, conn)
            conn.Open()
            Dim rdr = sqlCmd.ExecuteReader
            While rdr.Read
                count = rdr.GetInt32(0)
                status = rdr.GetString(1)
                amountBin = rdr.GetDecimal(2)
    
                If status = "H" Then
                    haves.Points.Add(New DataPoint(amountBin, count))
                Else
                    haveNots.Points.Add(New DataPoint(amountBin, count))
                End If
            End While
    
            conn.Close()
    
        End Using
    
        'TODO: give the chart a pleasing appearance.
        Chart1.Series.Add(haves)
        Chart1.Series.Add(haveNots)
    
    End Sub

    - you'll need these Imports for that:

    Imports System.Data.SqlClient
    Imports System.Windows.Forms.DataVisualization.Charting
    

    and with my test data you get a chart like this:

    HTH,

    Andrew


    • Edited by Andrew Morton Friday, September 6, 2013 6:28 PM Imports
    • Marked as answer by MihaIvan Saturday, September 7, 2013 4:47 PM
    Friday, September 6, 2013 6:26 PM
  • This did the trick. Of course when it automatically created the Series, it did it with the default chart type of Bar and an automatically generated legend. So after it created the Series, I just went in and changed the chart type and legend to what I wanted and it worked fine.

    Thanks.


    Mike

    Saturday, September 7, 2013 4:42 PM
  • This would work, but with more code than the other solution that worked from TopDog224. Good information in case I need to have more intimate control of the chart and have to build it manually.

    Thanks


    Mike

    Saturday, September 7, 2013 4:47 PM