Date on XAxis causes error

[Khanh Nguyen]

I have datacolumn(LeadDate)  that is returned from SQL,
if the format is DateTime,  i get the following error:

An error has occured while processing Report '':
Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

When applying the column to the xaxis of a chart.

Example of data with type of datetime from SQL:

LeadDate            DayActuals
2010-02-01 00:00:00 27
2010-02-02 00:00:00 48
2010-02-03 00:00:00 23
2010-02-04 00:00:00 56
2010-02-05 00:00:00 31
2010-02-06 00:00:00 19
2010-02-07 00:00:00 24
2010-02-08 00:00:00 27
2010-02-09 00:00:00 62
2010-02-10 00:00:00 6

When the column is CONVERT(VARCHAR(8),LeadDate,1) as LeadDate , like below, there's no error.
But the Column is no longer DateFormat  and I'm not able to format the LeadDate column on the chart.

LeadDate    DayActuals
02/01/10    27
02/02/10    48
02/03/10    23
02/04/10    56
02/05/10    31
02/06/10    19
02/07/10    24
02/08/10    27
02/09/10    62
02/10/10    7

I want to return the column as DateTime format so I can format the data on the xaxis using
using this technique: http://www.telerik.com/community/forums/reporting/telerik-reporting/how-to-format-x-axis-value-on-chart.aspx

[Steve]

Hello Khanh,

You can find more information on formatting the chart item axis labels to date in the following blog post: Formatting RadChart Labels.

Greetings,
Steve
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.

[Khanh Nguyen]

Steve using the steps from the blog,
the labels for me don't show up. The data is correct, but the XAxis label, displays the wrong date, looks like it's starting from Null (12/31/1899) instead of the value that's coming from the database date column ( 2010-02-01 )

foreach (DataRow  row in data )

{

DateTime LeadDate = (DateTime)row["LeadDate"];

Telerik.Reporting.Charting.ChartAxisItem item = new Telerik.Reporting.Charting.ChartAxisItem();

item.Value = (decimal)LeadDate.ToOADate();

chartPacing.PlotArea.XAxis.Items.Add(new Telerik.Reporting.Charting.ChartAxisItem(item ));

}

// if i uncomment the row below, i would get Unable to cast object of type System.DateTime to type System.String

//chartPacing.PlotArea.XAxis.DataLabelsColumn = "LeadDate";

chartPacing.PlotArea.XAxis.Appearance.ValueFormat = Telerik.Reporting.Charting.Styles.ChartValueFormat.ShortDate ;

chartPacing.PlotArea.XAxis.Appearance.CustomFormat = "MM/dd/yyyy";

// if i uncomment the row below, i would get Unable to cast object of type System.DateTime to type System.String

//chartPacing.PlotArea.XAxis.DataLabelsColumn = "LeadDate";

The attached image shows the xaxis with the incorrect labels. the Y axis is correct, you can see from the datatable below.
LeadDate as DateTime, DayActuals = int from SQL

LeadDate            DayActuals
2010-02-01 00:00:00 27
2010-02-02 00:00:00 48
2010-02-03 00:00:00 23
2010-02-04 00:00:00 56
2010-02-05 00:00:00 31
2010-02-06 00:00:00 19
2010-02-07 00:00:00 24
2010-02-08 00:00:00 27
2010-02-09 00:00:00 62
2010-02-10 00:00:00 6

[Ves]

Hi Khanh,

RadChart will not work directly with DateTime values. Instead, it supports the DateTime OLE Automation equivalent through the ToOADate method, which you are already using.

Here is how to achieve this conversion in your sql query:

"SELECT [Something], CAST([Date] AS FLOAT) + 2 as float_date FROM [Table_1]"

Now you will be able to use the following lines in the loop:

double LeadDate = (doublerow["LeadDate"];
item.Value = (decimal)LeadDate;


Note, that this way the X axis items will have values like ~40000. In order to place the bars correctly along the X axis, you will need to set their XValue property (myChartSeriesItem.XValue = LeadDate). If the chart is databound, you can achieve this by setting myChartSeries.DataXColumn = "LeadDate". Hope this helps.

Sincerely,
Ves
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.

[Khanh Nguyen]

hmm.... well that seems to defeat the purpose of getting back a DateTime Column.
If I'm going to convert on the SQL side, i might as well convert as varchar and get the formatting i want. (mm/dd , etc. ) and call it varchar_date to use as the label and axes.