VB.NET Creating a line chart

Hi,

I have a dataset table that contains rows for of three columns:

Name,
Month,
Value

Is it possible to create a line chart using the above table that will display a line per "Name" with the "Month" plotted on the X-axis and the "Value" on the Y-axis? Basically I need the series to group on Name.

Example.jpg
So far I can only get a line chart to work if there is only one Name in the data.

Thanks, Greg
spen_langAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

YZlatCommented:
what are you using to create a line chart?
spen_langAuthor Commented:
The Chart object in visual basic 2010 express
YZlatCommented:
You will need to create multiple series on the chart.

MyChart.Series.Add("Name");
MyChart.Series[0].ChartType=SeriesChartType.FastLine;
MyChart.Series[0].Color=Color.Red;

MyChart.Series.Add("Name2");
MyChart.Series[1].ChartType=SeriesChartType.FastLine;
MyChart.Series[1].Color=Color.Blue;

MyChart.Series.Add("Name3");
MyChart.Series[2].ChartType=SeriesChartType.FastLine;
MyChart.Series[2].Color=Color.Green;

Open in new window


can you post your code?
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

spen_langAuthor Commented:
   Private Sub getChart3()

        Dim ds As DataSet

        ds = objCharts.getChart3()

        Chart3.ChartAreas.Clear()
        Chart3.Series.Clear()

        ' set datasource
        Chart3.DataSource = ds.Tables(0)

        ' chart area
        Dim area As New ChartArea("AREA")
        Chart3.ChartAreas.Add(area)
        area.BackColor = Color.WhiteSmoke
        area.AxisX.Interval = 1
        area.AxisX.MajorGrid.Enabled = False
        area.AxisX.LabelStyle.Font = New System.Drawing.Font("Arial", 8.0F, System.Drawing.FontStyle.Regular)

        area.AxisY.LabelStyle.Format = "#,##0"
        area.AxisY.LabelStyle.Font = New System.Drawing.Font("Arial", 8.0F, System.Drawing.FontStyle.Regular)
        area.AxisY.MajorGrid.Enabled = True
        area.AxisY.MajorGrid.LineColor = Color.LightGray

        ' chart series
        Dim series1 As New Series("SERIES1")
        Chart3.Series.Add(series1)
        series1.ChartType = SeriesChartType.Line

        series1.XValueMember = "ReadingDate"
        series1.YValueMembers = "PrintCount"

        Chart3.DataBind()

        series1.ToolTip = UCase("#AXISLABEL") + vbCrLf + "#VALY{#,##0} PAGES PRINTED"
        series1.Font = New System.Drawing.Font("Arial", 8.0F, System.Drawing.FontStyle.Regular)
        series1.IsValueShownAsLabel = False
        series1.BorderColor = Color.Black
        series1.Color = ColorTranslator.FromHtml("#0065b2")
        series1.BackSecondaryColor = Color.White
        series1.BackGradientStyle = GradientStyle.TopBottom

        Dim sTitle As Title = Chart3.Titles.Add("PRINTERS PRINT COUNT BY DEPOT")

        With sTitle
            .ForeColor = Color.Black
            .BackColor = Color.WhiteSmoke

            ' setting font, font size and bold/italicizing
            .Font = New System.Drawing.Font("Arial", 11.0F, System.Drawing.FontStyle.Bold)
            .BorderWidth = 0
            .BorderColor = Color.Black
            .BorderDashStyle = ChartDashStyle.Solid
        End With

        ' setting the legend
        Dim legend1 As Legend = Chart3.Legends(0)
        legend1.Enabled = True

    End Sub

Open in new window

YZlatCommented:
try something like this. This code will dynamically add multiple series. It should also generate a random color for each line

  Private Sub getChart3()

        Dim ds As DataSet

        ds = objCharts.getChart3()

        Chart3.ChartAreas.Clear()
        Chart3.Series.Clear()

        ' set datasource
        Chart3.DataSource = ds.Tables(0)

        ' chart area
        Dim area As New ChartArea("AREA")
        Chart3.ChartAreas.Add(area)
        area.BackColor = Color.WhiteSmoke
        area.AxisX.Interval = 1
        area.AxisX.MajorGrid.Enabled = False
        area.AxisX.LabelStyle.Font = New System.Drawing.Font("Arial", 8.0F, System.Drawing.FontStyle.Regular)

        area.AxisY.LabelStyle.Format = "#,##0"
        area.AxisY.LabelStyle.Font = New System.Drawing.Font("Arial", 8.0F, System.Drawing.FontStyle.Regular)
        area.AxisY.MajorGrid.Enabled = True
        area.AxisY.MajorGrid.LineColor = Color.LightGray

 Private m_Rnd As New Random

        
        ' chart series
For Each dr as DataRow IN ds.Tables(0).Rows
	Dim serieName As String = dr("name").ToString()

	Chart3.Series.Add(serieName)
Chart3.Series(i).ChartType = SeriesChartType.Line
	Chart3.Series(i).ChartType = SeriesChartType.Line
Chart3.Series(I).COlor=Color.FromArgb(255,  m_Rnd.Next(0, 255), m_Rnd.Next(0, 255), m_Rnd.Next(0, 255))


	Chart3.Series(serieName).XValueMember = "ReadingDate"

	Chart3.Series(serieName).YValueMembers = "PrintCount"
Next

        Chart3.DataBind()

        series1.ToolTip = UCase("#AXISLABEL") + vbCrLf + "#VALY{#,##0} PAGES PRINTED"
        series1.Font = New System.Drawing.Font("Arial", 8.0F, System.Drawing.FontStyle.Regular)
        series1.IsValueShownAsLabel = False
        series1.BorderColor = Color.Black
        series1.Color = ColorTranslator.FromHtml("#0065b2")
        series1.BackSecondaryColor = Color.White
        series1.BackGradientStyle = GradientStyle.TopBottom

        Dim sTitle As Title = Chart3.Titles.Add("PRINTERS PRINT COUNT BY DEPOT")

        With sTitle
            .ForeColor = Color.Black
            .BackColor = Color.WhiteSmoke

            ' setting font, font size and bold/italicizing
            .Font = New System.Drawing.Font("Arial", 11.0F, System.Drawing.FontStyle.Bold)
            .BorderWidth = 0
            .BorderColor = Color.Black
            .BorderDashStyle = ChartDashStyle.Solid
        End With

        ' setting the legend
        Dim legend1 As Legend = Chart3.Legends(0)
        legend1.Enabled = True

 

    End Sub

Open in new window

Éric MoreauSenior .Net ConsultantCommented:
spen_langAuthor Commented:
@YZlat This will attempt to create a series per each row. WHen I tried your solution I got the following error message on the line Chart3.Series.Add(serieName)

A chart element with the name 'TED' already exists in the 'SeriesCollection'.

What I need is one series for TED and one series for FRED
YZlatCommented:
you can fix that by ensuring your dataset returns unique rows. QAs you can see from the code, it adds "name" field from each data row od the first table of your dataset.

If you cannot make those unique, you could check whether particular series already exists before adding each one.

Try something like this:

For Each dr as DataRow In ds.Tables(0).Rows
	Dim serieName As String = dr("name").ToString()
	If Chart3.Series.IndexOf(serieName,StringComparison.InvariantCultureIgnoreCase)<>-1 then
		idx=Chart3.Series.IndexOf(serieName,StringComparison.InvariantCultureIgnoreCase)
	Else
		Chart3.Series.Add(serieName)
		idx=Chart3.Series.IndexOf(serieName,StringComparison.InvariantCultureIgnoreCase)
	End If
Chart3.Series(idx).ChartType = SeriesChartType.Line
	Chart3.Series(idx).ChartType = SeriesChartType.Line
Chart3.Series(idx).Color=Color.FromArgb(255,  m_Rnd.Next(0, 255), m_Rnd.Next(0, 255), m_Rnd.Next(0, 255))


	Chart3.Series(serieName).XValueMember = "ReadingDate"

	Chart3.Series(serieName).YValueMembers = "PrintCount"
Next

Open in new window

spen_langAuthor Commented:
The rows are unique but the name will not be per each row. The data is grouped by name and then month number, showing a total. I cannot get the data to have one line per name. Ther could be up to twelve row per name, which will need to plotted against the name.
YZlatCommented:
In that case try the code snippet I provided. It should take care of that

Below is the simplified version:
For Each dr as DataRow In ds.Tables(0).Rows
	Dim serieName As String = dr("name").ToString()
	If Chart3.Series.IndexOf(serieName,StringComparison.InvariantCultureIgnoreCase)=-1 then	
		Chart3.Series.Add(serieName)
	End If
	Chart3.Series(serieName).ChartType = SeriesChartType.Line
	Chart3.Series(serieName).ChartType = SeriesChartType.Line
	Chart3.Series(serieName).Color=Color.FromArgb(255,  m_Rnd.Next(0, 255), m_Rnd.Next(0, 255), m_Rnd.Next(0, 255))
	Chart3.Series(serieName).XValueMember = "ReadingDate"
	Chart3.Series(serieName).YValueMembers = "PrintCount"
Next

Open in new window

spen_langAuthor Commented:
Sorry I somehow missed that snippet... What is idx in the code as it is not declared?
YZlatCommented:
It is an integer. But you could just use the latest snipped. Let me know how it worked out for you
spen_langAuthor Commented:
This did not work, it works OK if there is only one name and many rows for that name but not if there is more that one name. If there is more than one name then all data is still one line in the chart.
spen_langAuthor Commented:
This is what the chart looks like...

Capture.JPG
spen_langAuthor Commented:
Hi,

By using your code I have made something that works for me, let me know what you think...

Dim m_Rnd As New Random

Dim dtDistinct As DataTable = ds.Tables(0).DefaultView.ToTable(True, "Name")
Dim dt As New DataTable

dt = ds.Tables(0)

For Each dr As DataRow In dtDistinct.Rows
	Dim sSeriesName As String = dr.Item("Name")

	Dim series As New Series(sSeriesName)
	Chart4.Series.Add(series)

	ds.Tables(0).DefaultView.RowFilter = "Name = '" + sSeriesName + "'"

	Dim dv As New DataView(ds.Tables(0))
	dv.RowFilter = "Name = '" + sSeriesName + "'"

	series.Points.DataBind(dv, "Date", "Count", "Name=Name")

	series.ChartType = SeriesChartType.Line
	series.Color = Color.FromArgb(255, m_Rnd.Next(0, 255), m_Rnd.Next(0, 255), m_Rnd.Next(0, 255))

	series.ToolTip = "#CUSTOMPROPERTY(Name) - " + "#AXISLABEL" + vbCrLf + "#VALY{#,##0} PAGES PRINTED"
	series.Font = New System.Drawing.Font("Arial", 8.0F, System.Drawing.FontStyle.Regular)
	series.IsValueShownAsLabel = False

Next

Chart4.DataBind()

Open in new window

YZlatCommented:
I'd suggest you declare your variables outside the loop:

Dim sSeriesName As String
Dim series As Series
For Each dr As DataRow In dtDistinct.Rows
	sSeriesName  = dr.Item("Name")

	series = New Series(sSeriesName)
	Chart4.Series.Add(series)

	ds.Tables(0).DefaultView.RowFilter = "Name = '" + sSeriesName + "'"

	Dim dv As New DataView(ds.Tables(0))
	dv.RowFilter = "Name = '" + sSeriesName + "'"

	series.Points.DataBind(dv, "Date", "Count", "Name=Name")

	series.ChartType = SeriesChartType.Line
	series.Color = Color.FromArgb(255, m_Rnd.Next(0, 255), m_Rnd.Next(0, 255), m_Rnd.Next(0, 255))

	series.ToolTip = "#CUSTOMPROPERTY(Name) - " + "#AXISLABEL" + vbCrLf + "#VALY{#,##0} PAGES PRINTED"
	series.Font = New System.Drawing.Font("Arial", 8.0F, System.Drawing.FontStyle.Regular)
	series.IsValueShownAsLabel = False

Next

Open in new window


otherwise I think it looks good. Does it produce the desired result?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
spen_langAuthor Commented:
It sure does, thank you for your help which brought me to this
YZlatCommented:
glad if I could help.

One more thing. Do you get an error for duplicate series name?

perhaps you should try:

Dim series As New Series(sSeriesName)
            If Chart3.Series.IndexOf(sSeriesName) = -1 Then
                Chart3.Series.Add(series)
            End If

Open in new window

spen_langAuthor Commented:
I resolve this issue by creating a new datatable that includes the distinct names from the original datatable

Dim dtDistinct As DataTable = ds.Tables(0).DefaultView.ToTable(True, "Name")
Dim dt As New DataTable

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.