This is a migrated thread and some comments may be shown as answers.

Date on XAxis causes error

4 Answers 152 Views
General Discussions
This is a migrated thread and some comments may be shown as answers.
Khanh Nguyen
Top achievements
Rank 1
Khanh Nguyen asked on 10 Feb 2010, 06:37 PM
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


4 Answers, 1 is accepted

Sort by
0
Steve
Telerik team
answered on 11 Feb 2010, 11:53 AM
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.
0
Khanh Nguyen
Top achievements
Rank 1
answered on 11 Feb 2010, 05:15 PM
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  


0
Ves
Telerik team
answered on 12 Feb 2010, 09:41 AM
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.
0
Khanh Nguyen
Top achievements
Rank 1
answered on 13 Feb 2010, 01:50 AM
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.


Tags
General Discussions
Asked by
Khanh Nguyen
Top achievements
Rank 1
Answers by
Steve
Telerik team
Khanh Nguyen
Top achievements
Rank 1
Ves
Telerik team
Share this question
or