[Solved] Chart Annotation on Mouse Move not showing in Windows Application

Last Reply 2 months ago By dharmendr

Posted 2 months ago

Hi Everyone, 

I have this code which supposed to read data from the chart but it does not read anything from the chart information.

The chart looks like this :

and the code behind it is like this :

    Private Function GetData1() As DataTable
        Dim dtChartData As New DataTable()
        Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("db").ConnectionString)
            Using cmd As New SqlCommand("Chart_12years", conn) ' Total working hours in the month -- step3
                cmd.CommandType = CommandType.StoredProcedure
                conn.Open()
                Dim reader As SqlDataReader = cmd.ExecuteReader()
                dtChartData.Load(reader)
            End Using
        End Using

        Return dtChartData
    End Function

    Private Sub LoadYearData()
        Chart1.DataSource = GetData1()
        Chart1.Series("Chart1").Points.Clear()
        Chart1.Series("Chart1").XValueMember = "Year"
        Chart1.Series("Chart1").YValueMembers = "Total"
        'Chart1.Series("Chart1").Color = Color.FromArgb(152, 251, 152)

        Chart1.Series("Chart1").IsValueShownAsLabel = True
        Chart1.Series("Chart1").BorderWidth = 2
        Chart1.Series("Chart1").ChartType = SeriesChartType.Spline
        ' Chart1.Series("Chart1").Points.DataBindXY(x, y)
        Chart1.Series("Chart1").LabelForeColor = Color.NavajoWhite
        Chart1.Series("Chart1").EmptyPointStyle.Color = Color.Red
        Chart1.Series("Chart1").EmptyPointStyle.AxisLabel = "Empty"
        Chart1.Series("Chart1").EmptyPointStyle.IsValueShownAsLabel = False
        Chart1.Legends(0).Enabled = False
        Dim MyData As Single() = New Single(Chart1.Series.Count - 1) {}
        For i = 0 To Chart1.Series.Count - 1
            MyData(i) = Convert.ToSingle(5 * Math.Sin(i / CDbl(2)))
            Dim withBlock = Chart1.Series(i)
            withBlock.BorderWidth = 2
            withBlock.MarkerStyle = MarkerStyle.Circle
            withBlock.MarkerSize = 8
            Chart1.Annotations.Add(New CalloutAnnotation())
        Next
    End Sub

and the SQL behind it is like this :

 Begin
   SELECT Sum(Price) As Total,
      DATEPART(yyyy, Date) As [year],
      Sum (CONVERT(INT, CONVERT(VARCHAR(MAX),quantity))) as Lesh	
    FROM dbo.Expenses
    WHERE Date >= DATEADD(year, -12, GETDATE()) 
    GROUP BY DATEPART(yyyy, Date)
  End

and the data annotation looks like this :

  Private prevPosition As Point? = Nothing
    Private tooltip As ToolTip = New ToolTip()
    Private Sub Chart1_MouseMove(ByVal sender As Object, ByVal e As MouseEventArgs) Handles Chart1.MouseMove
        Dim pos = e.Location
        If prevPosition.HasValue AndAlso pos = prevPosition.Value Then Return
        tooltip.RemoveAll()
        prevPosition = pos
        Dim results = Chart1.HitTest(pos.X, pos.Y, False, ChartElementType.DataPoint)
        For Each result In results
            If result.ChartElementType = ChartElementType.DataPoint Then
                Dim year As String = result.Series.Points(result.PointIndex).AxisLabel
                tooltip.BackColor = Color.LightGray
                tooltip.ToolTipTitle = "Data Information"
                tooltip.Show(GetDetails(year), Chart1, pos.X, pos.Y - 15)
            End If
        Next
    End Sub
    Private Sub toolTip_Draw(ByVal sender As Object, ByVal e As DrawToolTipEventArgs)
        Dim f As Font = New Font("Arial", 8.0F)
        Dim b As Brush = New SolidBrush(tooltip.BackColor)
        e.Graphics.FillRectangle(b, e.Bounds)
        e.DrawBackground()
        e.DrawBorder()
        e.Graphics.DrawString(e.ToolTipText, f, SystemBrushes.ActiveCaptionText, e.Bounds)
    End Sub

    Private Function GetDetails(ByVal year As String) As String
        Dim data As String = ""
        Dim constr As String = "..."
        Dim query As String = "SELECT Sum(Price) As Total, DATEPART(yyyy, Date) As [Year], Department FROM dbo.Expenses "
        query += " WHERE DATEPART(yyyy, Date) = @Year GROUP BY DATEPART(yyyy, Date), Department"
        Dim dt As DataTable = New DataTable()
        Using con As SqlConnection = New SqlConnection(constr)
            Using command As New SqlCommand(query, con)
                command.CommandType = CommandType.Text
                command.Parameters.AddWithValue("@Year", year.Trim())
                Using sda As SqlDataAdapter = New SqlDataAdapter(command)
                    sda.Fill(dt)
                End Using
            End Using
        End Using

        For Each row As DataRow In dt.Rows
            Dim department As String = row("Department").ToString().Trim()
            Dim total As String = row("Total").ToString()
            data = data & total & " - " & year & " - " & department & Environment.NewLine
        Next
        Return data
    End Function

I did try it different ways but it won't fix it, but when I insert manually year in this line :

tooltip.Show(GetDetails(year), Chart1, pos.X, pos.Y - 15)

instead of year, I insert the year 2020 and then I get all the information needed, otherwise it won't read the information in the vb.net chart. 

Thank you

You are viewing reply posted by: dharmendr 2 months ago.
Posted 2 months ago

Hi elvisidrizi1,

I have checked your code. Its working.

elvisidrizi1 says:
Dim year As String = result.Series.Points(result.PointIndex).AxisLabel

Just change AxisLabel with XValue.

Dim year As String = result.Series.Points(result.PointIndex).XValue

Refer the sample. Instead of stored procedure i have binded with inline query.

Code

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

Public Class Form1
    Dim constr As String = "Data Source=####/SQLEXPRESS;Initial Catalog=Expenses;Trusted_Connection=yes;"
    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        LoadYearData()
    End Sub

    Private Function GetData1() As DataTable
        Dim dtChartData As New DataTable()
        Using conn As New SqlConnection(constr)
            Using cmd As New SqlCommand("SELECT Sum(Price) As Total,
                                        DATEPART(yyyy, Date) As [year],
                                        Sum (CONVERT(INT, CONVERT(VARCHAR(MAX),quantity))) as Lesh        
                                        FROM dbo.Expenses
                                        WHERE Date >= DATEADD(year, -12, GETDATE())
                                        GROUP BY DATEPART(yyyy, Date)", conn)
                cmd.CommandType = CommandType.Text

                conn.Open()
                Dim reader As SqlDataReader = cmd.ExecuteReader()
                dtChartData.Load(reader)

            End Using
        End Using

        Return dtChartData

        Return dtChartData
    End Function

    Private Sub LoadYearData()
        Chart1.DataSource = GetData1()
        Chart1.Series("Chart1").Points.Clear()
        Chart1.Series("Chart1").XValueMember = "Year"
        Chart1.Series("Chart1").YValueMembers = "Total"
        'Chart1.Series("Chart1").Color = Color.FromArgb(152, 251, 152)

        Chart1.Series("Chart1").IsValueShownAsLabel = True
        Chart1.Series("Chart1").BorderWidth = 2
        Chart1.Series("Chart1").ChartType = SeriesChartType.Spline
        ' Chart1.Series("Chart1").Points.DataBindXY(x, y)
        Chart1.Series("Chart1").LabelForeColor = Color.NavajoWhite
        Chart1.Series("Chart1").EmptyPointStyle.Color = Color.Red
        Chart1.Series("Chart1").EmptyPointStyle.AxisLabel = "Empty"
        Chart1.Series("Chart1").EmptyPointStyle.IsValueShownAsLabel = False
        Chart1.Legends(0).Enabled = False
        Dim MyData As Single() = New Single(Chart1.Series.Count - 1) {}
        For i = 0 To Chart1.Series.Count - 1
            MyData(i) = Convert.ToSingle(5 * Math.Sin(i / CDbl(2)))
            Dim withBlock = Chart1.Series(i)
            withBlock.BorderWidth = 2
            withBlock.MarkerStyle = MarkerStyle.Circle
            withBlock.MarkerSize = 8
            Chart1.Annotations.Add(New CalloutAnnotation())
        Next
    End Sub

    Private prevPosition As Point? = Nothing
    Private tooltip As ToolTip = New ToolTip()
    Private Sub Chart1_MouseMove(ByVal sender As Object, ByVal e As MouseEventArgs) Handles Chart1.MouseMove
        Dim pos = e.Location
        If prevPosition.HasValue AndAlso pos = prevPosition.Value Then Return
        tooltip.RemoveAll()
        prevPosition = pos
        Dim results = Chart1.HitTest(pos.X, pos.Y, False, ChartElementType.DataPoint)
        For Each result In results
            If result.ChartElementType = ChartElementType.DataPoint Then
                Dim year As String = result.Series.Points(result.PointIndex).XValue
                tooltip.BackColor = Color.LightGray
                tooltip.ToolTipTitle = "Data Information"
                tooltip.Show(GetDetails(year), Chart1, pos.X, pos.Y - 15)
            End If
        Next
    End Sub

    Private Sub toolTip_Draw(ByVal sender As Object, ByVal e As DrawToolTipEventArgs)
        Dim f As Font = New Font("Arial", 8.0F)
        Dim b As Brush = New SolidBrush(tooltip.BackColor)
        e.Graphics.FillRectangle(b, e.Bounds)
        e.DrawBackground()
        e.DrawBorder()
        e.Graphics.DrawString(e.ToolTipText, f, SystemBrushes.ActiveCaptionText, e.Bounds)
    End Sub

    Private Function GetDetails(ByVal year As String) As String
        Dim data As String = ""
        Dim query As String = "SELECT Sum(Price) As Total, DATEPART(yyyy, Date) As [year], Department FROM dbo.Expenses "
        query += " WHERE DATEPART(yyyy, Date) = @Year GROUP BY DATEPART(yyyy, Date), Department"
        Dim dt As DataTable = New DataTable()
        Using con As SqlConnection = New SqlConnection(constr)
            Using command As New SqlCommand(query, con)
                command.CommandType = CommandType.Text
                command.Parameters.AddWithValue("@Year", year.Trim())
                Using sda As SqlDataAdapter = New SqlDataAdapter(command)
                    sda.Fill(dt)
                End Using
            End Using
        End Using

        For Each row As DataRow In dt.Rows
            Dim department As String = row("Department").ToString().Trim()
            Dim total As String = row("Total").ToString()
            data = data & total & " - " & year & " - " & department & Environment.NewLine
        Next
        Return data
    End Function
End Class

Screenshot