I am attempting to make my first UltraCategoryChart. I will describe the end result I'd like first. We are comparing quantities for the last three years. So the X axis will be months, January through December. For each month there will be 3 columns grouped together representing each year. So January will have 2018, 2019, and 2020 with those counts supplied by a SQL database query:
select year(acctdate) as year, month(acctdate) as month, FORMAT(AcctDate, 'MMMM') as MyMonthName,count(*) as count from Acct where year(acctdate) >=2018group by year(acctdate), month(acctdate), FORMAT(AcctDate, 'MMMM')order by month(acctdate), year(acctdate)
Resulting in a dataset that looks like this (showing just the first two months here):
2018 1 January 13792019 1 January 19482020 1 January 14072018 2 February 14172019 2 February 11362020 2 February 1126
I've cobbled together code from online documentation, but I don't see anything on how to create the chart using a SQL database. I've attempted to replace the UltraCategoryChart.DataSource with a SQL DbDataReader resultset, but I get nothing displayed on the chart. There are no errors. Can anyone tell me what is missing?
Dim strSQL As String = "select year(acctdate) as Year, month(acctdate) as Month, FORMAT(AcctDate, 'MMMM') as MonthName,count(*) as Count from Acct " _ & "where year(acctdate) >=2018 " _ & "group by year(acctdate), month(acctdate), FORMAT(AcctDate, 'MMMM') " _ & "order by month(acctdate), year(acctdate)"
Dim adoRs As DbDataReader adoRs = My.Application.GlobalAuditDB.MExecuteReader(strSQL)
Dim series As New AreaSeries() Dim xAxis As New CategoryXAxis() Dim yAxis As New NumericYAxis()
UltraCategoryChart1.ChartType = CategoryChartType.Column UltraCategoryChart1.DataSource = adoRs
UltraCategoryChart1.IncludedProperties = New String() {"Year", "Month", "Count"} UltraCategoryChart1.ExcludedProperties = New String() {"MonthName"}
UltraCategoryChart1.Visible = True UltraCategoryChart1.BringToFront()
Do I need to add the DataSource in the property pages prior (and in addition) to running the above code?
Hello Ted,
I have been investigating into the behavior you are looking to achieve, and it appears that the DbDataReader is not a valid data source for the UltraCategoryChart at the moment. I would recommend reading your DbDataReader into a DataTable. For example, here is a code-example that I used using the AdventureWorks 2012 database:
SqlConnection connection = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand("select * from DimEmployee", connection); connection.Open(); SqlDataReader reader = cmd.ExecuteReader(); DataTable table = new DataTable(); table.Columns.Add("Name", typeof(string)); table.Columns.Add("BaseRate", typeof(double)); while (reader.Read()) { DataRow row = table.NewRow(); string name = reader["FirstName"] + " " + reader["LastName"]; var rate = reader["BaseRate"]; row[0] = name; row[1] = rate; table.Rows.Add(row); } ultraCategoryChart1.DataSource = table.DefaultView;
I hope this helps you. Please let me know if you have any other questions or concerns on this matter.