RadChart Grouping and Aggregation

| April 15, 2013 | 0 Comments

Telerik provides number of controls for Asp.Net developers. I was using one of the control RadChart that is used for representing information in chart. But the asp.net radchart control lacks grouping and aggregation feature directly. However RadChart for Silverlight provide this feature. You can check it from here.

Allow me to discuss a problem I was facing. I am developing an web application for showing earnings of freelancers by month for single year. Now suppose if freelancer get payment four times for a month, he or she will record those details in database as follow:

There are four entries for March month.

There are four entries for March month.

So I want to display these records in RadChart. I want to show Date in X-axis and Amount in Y-axis. The chart should display total amount for month if there are more entries for date. The end result should look like below snapshot.

RadChart Grouping

See the March (3) month values sum up.

So how we were able to do this? We just used following code snippet:

<telerik:RadChart ID="RadChartEarning" runat="server" Width="960px" Skin="DeepGray"
        DataSourceID="sdsEarningChartData" IntelligentLabelsEnabled="True">
        <PlotArea>
            <XAxis Step="1" DataLabelsColumn="oDate" AutoScale="false" />
            <YAxis Step="2000" AxisMode="Extended" />
        </PlotArea>
        <ChartTitle>
            <TextBlock Text="No data available!" />
        </ChartTitle>
        <Series>
            <telerik:ChartSeries Name="Salary per Month" Type="Line" DataXColumn="oDate" DataYColumn="amount">
                <Appearance LegendDisplayMode="Nothing" />
            <!--<span class="hiddenSpellError" pre=""-->telerik:ChartSeries>
        </Series>
    <!--<span class="hiddenSpellError" pre=""-->telerik:RadChart>
    <asp:<span class="hiddenSpellError">SqlDataSource ID="sdsEarningChartData" runat="server" ConnectionString="<%$ ConnectionStrings:accdbConString%>"
        ProviderName="<%$ ConnectionStrings:accdbConString.ProviderName %>" SelectCommand="SELECT MONTH(OnDate) AS oDate, SUM(Amount) AS amount FROM Earning WHERE YEAR(OnDate) = @YearValue GROUP BY MONTH(Earning.OnDate)">
        <SelectParameters>
            <asp:<span class="hiddenSpellError">ControlParameter ControlID="rcbYears" DefaultValue="2013" Name="YearValue" PropertyName="SelectedValue" />
        </SelectParameters>
    </asp:SqlDataSource>


The point to note is sql query used in SqlDataSource. It perform three things:

  1. Retrieve month value in numeric from date column.
  2. Sum the whole amount for the month. Use of aggregation function.
  3. Perform grouping on month.

By this way we were able to perform RadChart grouping and aggregation.

Tags: ,

Category: Asp.Net

Leave a Reply

%d bloggers like this: