Date on X-Axis on line chart

[Ambu]

I am trying to add dates to my x-axis, but only generic dates are being returned. Am I missing something?

Here is my dataset:
CountryName    LastUpdate                         Sites   Running Total
United States    2009-02-02 00:00:00.000   4         4
United States    2009-02-19 00:00:00.000   1         5

More Details
1. The SQL returns the LastUpdate value as the datetime datatype.
2. AutoScale and IsZeroBased values for the X-Axis are set to false
3. My code is included below.

<telerik:RadChart ID="RadChart1" runat="server" AutoLayout="True" DataGroupColumn="CountryName"
            DataSourceID="SqlDataSource1" DefaultType="Line" Height="400px">
            <Series>
                <telerik:ChartSeries DataYColumn="RunningTotal" Name="United States" Type="Line">
                    <Appearance>
                        <FillStyle MainColor="213, 247, 255">
                        </FillStyle>
                    </Appearance>
                </telerik:ChartSeries>
            </Series>
            <PlotArea>
                <XAxis AutoScale="False" DataLabelsColumn="LastUpdate" IsZeroBased="False" MaxValue="2"
                    MinValue="1" Step="1">
                    <Appearance ValueFormat="ShortDate">
                        <LabelAppearance Position-AlignedPosition="Top" RotationAngle="45">
                        </LabelAppearance>
                    </Appearance>
                    <AxisLabel Visible="True">
                        <Appearance Visible="True">
                        </Appearance>
                        <TextBlock Text="Timeline">
                        </TextBlock>
                    </AxisLabel>
                    <Items>
                        <telerik:ChartAxisItem Value="1">
                        </telerik:ChartAxisItem>
                        <telerik:ChartAxisItem Value="2">
                        </telerik:ChartAxisItem>
                    </Items>
                </XAxis>
                <YAxis>
                    <AxisLabel Visible="True">
                        <Appearance Visible="True">
                        </Appearance>
                        <TextBlock Text="Number of Sites">
                        </TextBlock>
                    </AxisLabel>
                </YAxis>
            </PlotArea>
            <ChartTitle>
                <TextBlock Text="Site Completion Summary">
                </TextBlock>
            </ChartTitle>
        </telerik:RadChart>

SQL:

Select a.CountryName, Convert(datetime, a.LastUpdate, 101) as LastUpdate,
       a.Sites,
       SUM(b.Sites) AS RunningTotal
From
(
    Select DATEADD(dd, 0, DATEDIFF(dd, 0, atss.LastUpdate)) as LastUpdate, c.CountryName,
           Max(DATEADD(dd, 0, DATEDIFF(dd, 0, atss.LastUpdate))) as LastUpdateMax,
           Count(ts.TrackedSiteID) as Sites
    From SLLS_TrackedSite ts
    Left Outer Join SLLS_AuditTrackSiteStatus atss On atss.TrackedSiteID = ts.TrackedSiteID
    Left Outer Join SLLS_Country c On c.CountryID = ts.CountryID
    Where ts.SiteStatusID = 1 And
          atss.SiteStatusID = 1 And
          ts.CountryID = 1
    Group By DATEADD(dd, 0, DATEDIFF(dd, 0, atss.LastUpdate)), c.CountryName
) a
Cross Join
(
    Select DATEADD(dd, 0, DATEDIFF(dd, 0, atss.LastUpdate)) as LastUpdate, c.CountryName,
           Max(DATEADD(dd, 0, DATEDIFF(dd, 0, atss.LastUpdate))) as LastUpdateMax,
           Count(ts.TrackedSiteID) as Sites
    From SLLS_TrackedSite ts
    Left Outer Join SLLS_AuditTrackSiteStatus atss On atss.TrackedSiteID = ts.TrackedSiteID
    Left Outer Join SLLS_Country c On c.CountryID = ts.CountryID
    Where ts.SiteStatusID = 1 And
          atss.SiteStatusID = 1 And
          ts.CountryID = 1
    Group By DATEADD(dd, 0, DATEDIFF(dd, 0, atss.LastUpdate)), c.CountryName
) b
WHERE (b.LastUpdate <= a.LastUpdate)
GROUP BY a.CountryName,a.LastUpdate,a.Sites
ORDER BY a.CountryName,a.LastUpdate,a.Sites

[Dwight]

Hi Allan,

Our RadChart control does not support DateTime values. Instead you should use double (OLE Automation).
If set through code, you should use something like:

DateTime dateTime = DateTime.Today; // set some date
double valueForChart = dateTime.ToOADate(); // convert to OLE Automation representation

Since you use SQL query, you can directly retrieve the data in the correct format adding a cast to the query. Here is a simple example that does the trick:

SELECT CAST([Date] AS FLOAT) + 2 as [FloatDate] FROM [Table_1]

All the best,
Evtim
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.