I'm creating a DotNet.Highcharts chart that is using data from two tables: Expenditures and Incomings. I am using an SQL statement to create a DataTable
for each. The first (for Incomings) called Dt
contains IncCost
and IncDate
. The second (for Expenditures) called Dt2
contains ExpCost
and ExpDate
.
I am able to plot IncCost
against IncDate
and ExpCost
against ExpDate
. The problem arises when I try to concatenate IncDate
and ExpDate
(final) as the cost for IncCost
and ExpCost
is plotting against the position of the date in (final), and not the actual date it is related to.
Here are the calls I use to create each DataTable:
SqlDataAdapter Adp = new SqlDataAdapter("select CONVERT(DATETIME, IncDate, 103) AS IncDate, SUM(IncCost) AS IncCost from Incomings GROUP BY CONVERT(DATETIME, IncDate, 103) ORDER BY CONVERT(DATETIME, IncDate, 103)", con);
SqlDataAdapter Adp2 = new SqlDataAdapter("select CONVERT(DATETIME, ExpDate, 103) AS ExpDate, SUM(ExpCost) AS ExpCost from Expenditures GROUP BY CONVERT(DATETIME, ExpDate, 103) ORDER BY CONVERT(DATETIME, ExpDate, 103)", con);
Here is the code for calling each of these from the datatable
var dateArr = Dt.AsEnumerable().Select(r => r.Field<DateTime>("IncDate")).ToArray();
var objectArr = Dt.AsEnumerable().Select(r => r.Field<object>("IncCost")).ToArray();
var dateArr2 = Dt2.AsEnumerable().Select(r => r.Field<DateTime>("ExpDate")).ToArray();
var objectArr2 = Dt2.AsEnumerable().Select(r => r.Field<object>("ExpCost")).ToArray();
var res = dateArr.Concat(dateArr2).ToArray();
var final = res.Select(d => d.ToString(@"dd/MM/yyyy")).ToArray();
And here is the code I use to create my highchart:
Highcharts chart = new Highcharts("graph")
.SetTitle(new Title { Text = "Incoming Stats" })
.SetXAxis(new[] { new XAxis { Categories = final } })
.SetYAxis(new YAxis { Title = new YAxisTitle { Text = "Amount Incoming" } })
.SetSeries(new[]
{
new Series { Name = "inc", Data = new Data(objectArr)},
new Series { Name = "exp", Data = new Data(objectArr2) }
});
As you can see, the series are being created but they are being plotted against the number in the array, and not against the date it should be linked to.
I'm not sure how close or far I am to a solution but any help is appreciated.
Here is my incomings datatable
Here is my expenditure datatable
Genuinely don't know how to go about this. any information is greatly appreciated
See Question&Answers more detail:
os