Hi elvisidrizi1,
Check this example. Now please take its reference and correct your code.
For changing the CalloutAnnotation Text get the details from the DataPoint like department, Year and based on that change the Text.
You can fetch the details based on department, Year from database and set the Text accordingly.
Namespaces
C#
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Windows.Forms.DataVisualization.Charting;
VB.Net
Imports System.Data
Imports System.Data.SqlClient
Imports System.Drawing
Imports System.Windows.Forms.DataVisualization.Charting
Code
C#
public partial class Form1 : Form
{
private string constr = "Data Source=####/SQLEXPRESS;Initial Catalog=Expenses;Trusted_Connection=yes;"
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
DataCharts();
}
private void DataCharts()
{
DataTable dt = GetData("Chart_Monthly1");
List<string> departments = (from p in dt.AsEnumerable()
select p.Field<string>("Department")).Distinct().ToList();
List<string> years = (from p in dt.AsEnumerable()
orderby p.Field<string>("year")
select p.Field<string>("year")).Distinct().ToList();
if (Chart1.Series.Count() == 1)
{
Chart1.Series.Remove(Chart1.Series[0]);
}
Chart1.Series.Clear();
foreach (string department in departments)
{
List<string> x = new List<string>();
List<decimal> y = new List<decimal>();
foreach (string year in years)
{
x.Add(year);
y.Add((from p in dt.AsEnumerable()
where p.Field<string>("Department") == department & p.Field<string>("year") == year
orderby p.Field<string>("year")
select p.Field<decimal>("Total")).FirstOrDefault());
}
Chart1.Series.Add(new Series(department));
Chart1.Series[department].IsValueShownAsLabel = true;
Chart1.Series[department].BorderWidth = 2;
Chart1.Series[department].ChartType = SeriesChartType.Spline;
Chart1.Series[department].Points.DataBindXY(x, y);
Chart1.Series[department].LabelForeColor = Color.MediumSeaGreen;
Chart1.Series[department].EmptyPointStyle.Color = Color.Red;
Chart1.Series[department].EmptyPointStyle.AxisLabel = "Empty";
}
Chart1.Legends[0].Enabled = true;
float[] MyData = new float[Chart1.Series.Count];
for (var i = 0; i < Chart1.Series.Count; i++)
{
MyData[i] = Convert.ToSingle(5 * Math.Sin(i / (double)2));
var withBlock = Chart1.Series[i];
withBlock.BorderWidth = 2;
withBlock.MarkerStyle = MarkerStyle.Circle;
withBlock.MarkerSize = 8;
}
}
private DataTable GetData(string query)
{
DataTable dt = new DataTable();
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand command = new SqlCommand(query, con))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@DateFrom", "2020-01-01");
command.Parameters.AddWithValue("@DateTo", "2020-07-07");
using (SqlDataAdapter sda = new SqlDataAdapter(command))
{
sda.Fill(dt);
}
}
}
return dt;
}
private void Chart1_MouseMove(object sender, MouseEventArgs e)
{
Chart1.Annotations.Clear();
HitTestResult result = Chart1.HitTest(e.X, e.Y);
if (result.ChartElementType == ChartElementType.DataPoint)
{
string department = result.Series.Name;
string year = result.Series.Points[result.PointIndex].AxisLabel;
double total = result.Series.Points[result.PointIndex].YValues[0];
PointF thisPt = new PointF(Convert.ToSingle(result.Series.Points[result.PointIndex].XValue),
Convert.ToSingle(result.Series.Points[result.PointIndex].YValues[0]));
CalloutAnnotation ca = new CalloutAnnotation()
{
AnchorDataPoint = result.Series.Points[result.PointIndex],
X = thisPt.X + 1,
Y = thisPt.Y + 1,
CalloutStyle = CalloutStyle.SimpleLine,
ForeColor = Color.Red,
Font = new Font("Tahoma", 8, FontStyle.Bold),
Name = department,
Text = GetDetails(year, department)
};
Chart1.Annotations[department] = ca;
Chart1.Invalidate();
}
}
private string GetDetails(string year, string department)
{
string data = "";
string query = "SELECT Date,Description,Price,Quantity FROM [dbo].[Expenses] WHERE CONVERT(char(7), Date, 120) = @Year AND LTRIM(RTRIM(Department)) = @Dept";
DataTable dt = new DataTable();
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand command = new SqlCommand(query, con))
{
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@Year", year.Trim());
command.Parameters.AddWithValue("@Dept", department.Trim());
using (SqlDataAdapter sda = new SqlDataAdapter(command))
{
sda.Fill(dt);
}
}
}
foreach (DataRow row in dt.Rows)
{
string date = Convert.ToDateTime(row["Date"]).ToString("dd/MM/yyyy");
string description = row["Description"].ToString();
string price = row["Price"].ToString() == "" ? "0" : row["Price"].ToString();
string quantity = row["Quantity"].ToString() == "" ? "0" : row["Quantity"].ToString();
data = data + date + " - " + description + " - " + price + " - " + quantity + Environment.NewLine;
}
return data;
}
}
VB.Net
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
DataCharts()
End Sub
Private Sub DataCharts()
Dim dt As DataTable = GetData("Chart_Monthly1")
Dim departments As List(Of String) = (From p In dt.AsEnumerable()
Select p.Field(Of String)("Department")).Distinct().ToList()
Dim years As List(Of String) = (From p In dt.AsEnumerable()
Order By p.Field(Of String)("year")
Select p.Field(Of String)("year")).Distinct().ToList()
If Chart1.Series.Count() = 1 Then
Chart1.Series.Remove(Chart1.Series(0))
End If
Chart1.Series.Clear()
For Each department As String In departments
Dim x As List(Of String) = New List(Of String)()
Dim y As List(Of Decimal) = New List(Of Decimal)()
For Each year As String In years
x.Add(year)
y.Add((From p In dt.AsEnumerable()
Where p.Field(Of String)("Department") = department And p.Field(Of String)("year") = year
Order By p.Field(Of String)("year")
Select p.Field(Of Decimal)("Total")).FirstOrDefault())
Next
Chart1.Series.Add(New Series(department))
Chart1.Series(department).IsValueShownAsLabel = True
Chart1.Series(department).BorderWidth = 2
Chart1.Series(department).ChartType = SeriesChartType.Spline
Chart1.Series(department).Points.DataBindXY(x, y)
Chart1.Series(department).LabelForeColor = Color.MediumSeaGreen
Chart1.Series(department).EmptyPointStyle.Color = Color.Red
Chart1.Series(department).EmptyPointStyle.AxisLabel = "Empty"
Next
Chart1.Legends(0).Enabled = True
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
Next
End Sub
Private Function GetData(ByVal query As String) As DataTable
Dim dt As DataTable = New DataTable()
Using con As SqlConnection = New SqlConnection(constr)
Using command As New SqlCommand(query, con)
command.CommandType = CommandType.StoredProcedure
command.Parameters.AddWithValue("@DateFrom", "2020-01-01")
command.Parameters.AddWithValue("@DateTo", "2020-07-07")
Using sda As SqlDataAdapter = New SqlDataAdapter(command)
sda.Fill(dt)
End Using
End Using
End Using
Return dt
End Function
Private Sub Chart1_MouseMove(ByVal sender As Object, ByVal e As MouseEventArgs) Handles Chart1.MouseMove
Chart1.Annotations.Clear()
Dim result As HitTestResult = Chart1.HitTest(e.X, e.Y)
If result.ChartElementType = ChartElementType.DataPoint Then
Dim department As String = result.Series.Name
Dim year As String = result.Series.Points(result.PointIndex).AxisLabel
Dim total As Double = result.Series.Points(result.PointIndex).YValues(0)
Dim thisPt As PointF = New PointF(Convert.ToSingle(result.Series.Points(result.PointIndex).XValue),
Convert.ToSingle(result.Series.Points(result.PointIndex).YValues(0)))
Dim ca As CalloutAnnotation = New CalloutAnnotation With {
.AnchorDataPoint = result.Series.Points(result.PointIndex),
.X = thisPt.X + 1,
.Y = thisPt.Y + 1,
.CalloutStyle = CalloutStyle.SimpleLine,
.ForeColor = Color.Red,
.Font = New Font("Tahoma", 8, FontStyle.Bold),
.Name = department,
.Text = GetDetails(year, department)
}
Chart1.Annotations(department) = ca
Chart1.Invalidate()
End If
End Sub
Private Function GetDetails(ByVal year As String, ByVal department As String) As String
Dim data As String = ""
Dim query As String = "SELECT Date,Description,Price,Quantity FROM [dbo].[Expenses] WHERE CONVERT(char(7), Date, 120) = @Year AND LTRIM(RTRIM(Department)) = @Dept"
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())
command.Parameters.AddWithValue("@Dept", department.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 [date] As String = Convert.ToDateTime(row("Date")).ToString("dd/MM/yyyy")
Dim description As String = row("Description")
Dim price As String = If(row("Price").ToString = "", 0, row("Price").ToString)
Dim quantity As String = If(row("Quantity").ToString = "", 0, row("Quantity").ToString)
data = data & [date] & " - " & description & " - " & price & " - " & quantity & Environment.NewLine
Next
Return data
End Function
End Class
Screenshot
