Setting DataXColumn for every Databound Series

[Michael]

Hi,

I'm trying to put together a line chart showing transactions per day over a period of time, separated out by store.  I noticed that the dates on the X-axis are not correct when compared to the actual data.  The dates on the bottom of the page don't scale, (instead it just uses each date point, so a hash mark can be one day, then jump to a week) and rather than place the datapoint at the right date, it just places it on the next one over.

When I set the DataXColumn setting for some hardcoded series, it worked fine.  So, is there a way to set the DataXColumn for each series and have the chart display the correct dates?  My code is below.

<telerik:RadChart runat="server" ID = "chartTransactions" AutoLayout="true" DataSourceID="datTransactions" DefaultType="Line" DataGroupColumn = "Store" Width="800" >
        <ChartTitle><TextBlock Text = "Transactions - Store"></TextBlock></ChartTitle>
        <PlotArea>
            <XAxis DataLabelsColumn = "AppRejDate" >
                <Appearance ValueFormat="ShortDate">
                    <LabelAppearance RotationAngle="270">
                    </LabelAppearance>
                </Appearance>
            </XAxis>
        </PlotArea>
    </telerik:RadChart>
<asp:SqlDataSource ID = "datTransactions" runat="server" ConnectionString="<%$ ConnectionStrings:DBString %>" SelectCommand = "Select tblStore_Ref.Description, CAST(DATEADD(Day, DATEDIFF(Day, 0, AppRejDateTime), 0) AS Float) + 2 AS AppRejDate, Count(*) as Transactions From tblTransactionHistory INNER JOIN tblTransactionItems ON tblTransactionHistory.TransactionID = tblTransactionItems.TransactionID INNER JOIN tblStore_Ref ON tblTransactionItems.Store = tblStore_Ref.Store Where Status = 'A' AND AppRejDateTime Between @dateBegin AND @dateEnd Group By DATEADD(Day, DATEDIFF(Day, 0, AppRejDateTime), 0), tblStore_Ref.Description Order By AppRejDate, tblStore_Ref.Description">
        <SelectParameters>
            <asp:ControlParameter ControlID = "dateBegin" Name = "dateBegin" PropertyName="SelectedDate" />
            <asp:ControlParameter ControlID = "dateEnd" Name = "dateEnd" PropertyName="SelectedDate" />
        </SelectParameters>
    </asp:SqlDataSource>

Some sample data:

40513   S-2   1
40518   S-2   2
40519   S-2   1
40549   S-2   4
40553   S-2   3

Thanks.

[Michael]

Updated the previous post, but I'm having major issues with getting a Date line chart working properly.

The bottom X-axis isn't scaling, it just has different hash marks corresponding to different data point's dates and the datapoints aren't matching up with the right date.

[Michael]

I figured out how to scale the X-Axis with Dates.  I had to use an AddRange during the DataBound event like so:

Protected Sub chartTransactions_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles chartTransactions.DataBound
        Dim dblBegin As Double, dblEnd As Double
        dblBegin = DateTime.Parse(dateBegin.SelectedDate).Date.ToOADate
        dblEnd = DateTime.Parse(dateEnd.SelectedDate).Date.ToOADate
        chartTransactions.PlotArea.XAxis.AutoScale = False
        chartTransactions.PlotArea.XAxis.IsZeroBased = False
        chartTransactions.PlotArea.XAxis.AddRange(dblBegin, dblEnd, 2)

    End Sub

And my XAxis works awesome.  Now, my only problem is getting the created series to use the dates as the X Column and not just put them in the order the datasource returns.

I tried using the DataManager.ValuesXColumn but that seems to have no effect.  I'll keep plugging away, but this is much harder than I thought it would be.

[Yavor]

Hi Michael,

RadChart treats your dates as categories because  you are mapping your DateTime column to the DataLabelsColumn property of the Axis-X. If you map it to the DataXColumn the dates will appear normally. You can set the DataXColumn on the series like this:

ChartSeries series = new ChartSeries();
series.Type = ChartSeriesType.Line;
series.DataYColumn = "Value";
series.DataXColumn = "Date";

Series are mapped to data as it is set to the datasource. RadChart doesn't sort your data, so you have to sort your data by the date column that your axis-X is mapped to prior to setting it to the datasource. You can do this using SQL in your query or perhaps you can do it in code behind using LINQ. Feel free to choose the approach that suits you best.

I have attached a small sample project that demonstrates how to bind RadChart to a collection of objects using DateTime as value for X-axis.

Hope this helps!

All the best,
Yavor Ivanov
the Telerik team

[Michael]

I knew I could set it to the DataXColumn manually, but the problem was that I wanted multiple series created based on the data.  So, if there were 3 categories, I wanted 3 lines and I couldn't figure out how to set the DataXColumn on each of those 3 series.

The solution turned out to be the DataManager:

Protected Sub chartTransactions_DataBinding(ByVal sender As Object, ByVal e As System.EventArgs) Handles chartTransactions.DataBinding
        chartTransactions.DataManager.ValuesXColumn = "AppRejDate"
    End Sub

It had to be done in the DataBinding for it to work, but now everything works properly.

When I have time, I'll post my full solution.

[Michael]

As promised, the full solution:  A Line Chart with a Scaling Date X-Axis and multiple series based on database results.

.aspx file

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Dash_Transactions.aspx.vb" Inherits="Dash_Transactions" Title="Dashboard: Transactions" %>

<%@ Register Assembly="Telerik.Web.UI" Namespace="Telerik.Charting" TagPrefix="telerik" %>




<table>

    <tr>

        <td>

            <asp:Label ID="lblStartDate" runat="server" Text="Starting Date" Font-Names="Tahoma"></asp:Label>

            <telerik:RadDatePicker runat="server" ID = "dateBegin"></telerik:RadDatePicker>

            <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator1" ControlToValidate="dateBegin"

                ErrorMessage="Enter a date!" Display="Dynamic"></asp:RequiredFieldValidator>

        </td>

        <td>

            <asp:Label ID="lblEndDate" runat="server" Text="Ending Date" Font-Names="Tahoma"></asp:Label>

            <telerik:RadDatePicker runat="server" ID = "dateEnd"></telerik:RadDatePicker>

             <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator2" ControlToValidate="dateEnd"

                ErrorMessage="Enter a date!" Display="Dynamic"></asp:RequiredFieldValidator>

            <asp:Button Text = "Update" runat="server" ID="btnUpdate" />

            <asp:Button Text = "Reset" runat="server" ID="btnReset" />

             <asp:CompareValidator ID="dateCompareValidator" runat="server" ControlToValidate="dateEnd"

                ControlToCompare="dateBegin" Operator="GreaterThan" Type="Date" ErrorMessage="The second date must be after the first one." />

        </td>

       

    </tr>

    </table>

 

<telerik:RadChart runat="server" ID = "chartTransactions" AutoLayout="true" DataSourceID="datTransactions" DefaultType="Line" DataGroupColumn = "Store" Width="800" >

        <ChartTitle><TextBlock Text = "Transactions - Store"></TextBlock></ChartTitle>     

        <PlotArea>

            <XAxis>

                <Appearance ValueFormat="ShortDate">

                    <LabelAppearance RotationAngle="270">

                    </LabelAppearance>

                </Appearance>

            </XAxis>

        </PlotArea>

    </telerik:RadChart>

<asp:SqlDataSource ID = "datTransactions" runat="server" ConnectionString="<%$ ConnectionStrings:DBString %>" SelectCommand = "Select tblStore_Ref.Description, CAST(DATEADD(Day, DATEDIFF(Day, 0, AppRejDateTime), 0) AS Float) + 2 AS AppRejDate, Count(*) as Transactions From tblTransactionHistory INNER JOIN tblTransactionItems ON tblTransactionHistory.TransactionID = tblTransactionItems.TransactionID INNER JOIN tblStore_Ref ON tblTransactionItems.Store = tblStore_Ref.Store Where Status = 'A' AND AppRejDateTime Between @dateBegin AND @dateEnd Group By DATEADD(Day, DATEDIFF(Day, 0, AppRejDateTime), 0), tblStore_Ref.Description Order By AppRejDate, tblStore_Ref.Description">

        <SelectParameters>

            <asp:ControlParameter ControlID = "dateBegin" Name = "dateBegin" PropertyName="SelectedDate" />

            <asp:ControlParameter ControlID = "dateEnd" Name = "dateEnd" PropertyName="SelectedDate" />

        </SelectParameters>

    </asp:SqlDataSource>

The DataGroupColumn sets the grouping, which creates series based on the Store.

The CodeBehind:

Imports Telerik.Charting

Partial Class Dash_Transactions

    Inherits System.Web.UI.Page

     'For every series, includes a pointmark that's the color of the series line.

  Protected Sub chartTransactions_BeforeLayout(ByVal sender As Object, ByVal e As System.EventArgs) Handles chartTransactions.BeforeLayout

        Dim series As ChartSeries

        For Each series In chartTransactions.Series

            series.Appearance.PointMark.Visible = True

            series.Appearance.ShowLabels = False

            series.Appearance.PointMark.FillStyle.MainColor = series.Appearance.LineSeriesAppearance.Color

        Next

    End Sub

          'Scales the X-Axis properly based on the chosen start date/end date

    Protected Sub chartTransactions_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles chartTransactions.DataBound

        Dim dblBegin As Double, dblEnd As Double

        dblBegin = DateTime.Parse(dateBegin.SelectedDate).Date.ToOADate

        dblEnd = DateTime.Parse(dateEnd.SelectedDate).Date.ToOADate

        chartTransactions.PlotArea.XAxis.AutoScale = False

        chartTransactions.PlotArea.XAxis.IsZeroBased = False

        chartTransactions.PlotArea.XAxis.AddRange(dblBegin, dblEnd, 4)

 

    End Sub

 
          'Sets the proper X-values.

    Protected Sub chartTransactions_DataBinding(ByVal sender As Object, ByVal e As System.EventArgs) Handles chartTransactions.DataBinding

        chartTransactions.DataManager.ValuesXColumn = "AppRejDate"

    End Sub

 

End Class

[Michael]

Final Problem:

I'm adding some DrillDown behavior to the chart.  I have PointMarks implemented on the Line Chart.

If I click on the legend, the OnClick method is called and everything works.

If I click on the pointmarks, it throws an exception without even going to the OnClick.  Here's the exception:

Server Error in Application.
--------------------------------------------------------------------------------

Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:


[ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index]
   System.ThrowHelper.ThrowArgumentOutOfRangeException(ExceptionArgument argument, ExceptionResource resource) +64
   System.ThrowHelper.ThrowArgumentOutOfRangeException() +15
   System.Collections.Generic.List`1.get_Item(Int32 index) +7497276
   Telerik.Charting.ChartSeriesCollection.get_Item(Int32 index) +48
   Telerik.Web.UI.RadChart.RaisePostBackEvent(String eventArg) +452
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +175
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565




--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:2.0.50727.3082; ASP.NET Version:2.0.50727.3082

Help!  Thanks.

[Evgenia]

Hi Michael,

We are unable to define the state of the problem from this stack trace. Could you open a Support thread and send us the sample project attached where this issue occurs so that we will be able to investigate it and help you?

Greetings,
Evgenia
the Telerik team

[Michael]

Sent in a support ticket and they fixed it!

I was calling RemoveAllSeries() in the Page_Load section, when it should be in the Chart onClick event handler.

Thanks a lot.