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:
axonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('nav_menu') AND tt.term_id IN ('32') ORDER BY tr.object_id ASC 67 | 0.0005 | enabled | not cached | 11135 | SELECT wp_posts.* FROM wp_posts WHERE 1=1 AND wp_posts.ID IN (225,226,227,228,229) AND wp_posts.post_type = 'nav_menu_item' AND ((wp_posts.post_status = 'publish')) ORDER BY wp_posts.menu_order ASC 68 | 0.0002 | enabled | not cached | 2087 | SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (226) ORDER BY meta_id ASC 69 | 0.0002 | enabled | not cached | 2087 | SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (229) ORDER BY meta_id ASC 70 | 0.0002 | enabled | not cached | 2087 | SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (227) ORDER BY meta_id ASC 71 | 0.0002 | enabled | not cached | 2087 | SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (225) ORDER BY meta_id ASC 72 | 0.0002 | enabled | not cached | 2087 | SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (228) ORDER BY meta_id ASC 73 | 0.0003 | enabled | not cached | 4495 | SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN ('category') AND t.term_id IN ( 4,8,9,2,3 ) ORDER BY t.name ASC 74 | 0.0001 | enabled | cached | 737 | SELECT t.term_id FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category') AND tr.object_id IN (339) ORDER BY t.name ASC 75 | 0.0002 | enabled | not cached | 529 | SELECT option_value FROM wp_options WHERE option_name = 'text_direction' LIMIT 1 76 | 0.0003 | enabled | not cached | 529 | SELECT option_value FROM wp_options WHERE option_name = 'disabled_likes' LIMIT 1 77 | 0.0004 | enabled | not cached | 8065 | SELECT * FROM wp_posts WHERE ID = 340 LIMIT 1 78 | 0.0003 | enabled | not cached | 2065 | SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (340) ORDER BY meta_id ASC 79 | 0.0008 | enabled | not cached | 3818 | SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('post_tag') AND tr.object_id IN (339) ORDER BY t.name ASC 80 | 0.0007 | enabled | not cached | 3563 | SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category') AND tr.object_id IN (339) ORDER BY t.name ASC 81 | 0.0005 | enabled | not cached | 4904 | SELECT * FROM wp_comments WHERE ( comment_approved = '1' ) AND comment_post_ID = 339 ORDER BY comment_date_gmt ASC 82 | 0.0004 | enabled | not cached | 631 | SELECT p.ID FROM wp_posts AS p WHERE p.post_date < '2013-04-15 19:03:30' AND p.post_type = 'post' AND p.post_status = 'publish' ORDER BY p.post_date DESC LIMIT 1 83 | 0.0003 | enabled | not cached | 11898 | SELECT * FROM wp_posts WHERE ID = 1 LIMIT 1 84 | 0.0004 | enabled | not cached | 632 | SELECT p.ID FROM wp_posts AS p WHERE p.post_date > '2013-04-15 19:03:30' AND p.post_type = 'post' AND p.post_status = 'publish' ORDER BY p.post_date ASC LIMIT 1 85 | 0.0002 | enabled | not cached | 11572 | SELECT * FROM wp_posts WHERE ID = 353 LIMIT 1 86 | 0.001 | enabled | not cached | 2512 | SELECT p.ID AS 'id', p.post_title AS 'title', p.post_date AS 'date', p.post_author AS 'uid', ( SUM(v.pageviews)/(IF ( DATEDIFF('2017-04-16 10:15:36', DATE_SUB('2017-04-16 10:15:36', INTERVAL 1 WEEK)) > 0, DATEDIFF('2017-04-16 10:15:36', DATE_SUB('2017-04-16 10:15:36', INTERVAL 1 WEEK)), 1) ) ) AS 'avg_views' FROM wp_popularpostssummary v LEFT JOIN wp_posts p ON v.postid = p.ID WHERE 1 = 1 AND p.post_type IN('post','page') AND v.last_viewed > DATE_SUB('2017-04-16 10:15:36', INTERVAL 1 WEEK) AND p.post_password = '' AND p.post_status = 'publish' GROUP BY v.postid ORDER BY avg_views DESC LIMIT 5; 87 | 0.0005 | disabled (query) | not cached | 0 | SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') ORDER BY wp_posts.post_date DESC LIMIT 0, 5 88 | 0.0001 | disabled (query) | not cached | 0 | SELECT FOUND_ROWS() 89 | 0.0003 | enabled | not cached | 14241 | SELECT wp_posts.* FROM wp_posts WHERE ID IN (2,3) 90 | 0.0008 | enabled | not cached | 5047 | SELECT t.*, tt.*, tr.object_id FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category', 'post_tag', 'post_format') AND tr.object_id IN (2, 3) ORDER BY t.name ASC 91 | 0.0019 | enabled | not cached | 6385 | SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (2,3) ORDER BY meta_id ASC 92 | 0.0022 | enabled | not cached | 7381 | SELECT DISTINCT ID, post_title, post_password, comment_ID, comment_post_ID, comment_author, comment_author_email, comment_date_gmt, comment_approved, comment_type,comment_author_url, SUBSTRING(comment_content,1,100) AS com_excerpt FROM wp_comments LEFT OUTER JOIN wp_posts ON (wp_comments.comment_post_ID = wp_posts.ID) WHERE comment_approved = '1' AND comment_type = '' AND post_password = '' ORDER BY comment_date_gmt DESC LIMIT 5 93 | 0.0002 | enabled | not cached | 10061 | SELECT * FROM wp_posts WHERE ID = 15 LIMIT 1 94 | 0.0002 | enabled | not cached | 14705 | SELECT * FROM wp_posts WHERE ID = 42 LIMIT 1 95 | 0.0004 | enabled | not cached | 2074 | SELECT YEAR(post_date) AS `year`, MONTH(post_date) AS `month`, count(ID) as posts FROM wp_posts WHERE post_type = 'post' AND post_status = 'publish' GROUP BY YEAR(post_date), MONTH(post_date) ORDER BY post_date DESC 96 | 0.0002 | enabled | cached | 4714 | SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN ('category') ORDER BY t.name ASC 97 | 0.0008 | enabled | not cached | 8853 | SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN ('post_tag') AND tt.count > 0 ORDER BY tt.count DESC LIMIT 200 98 | 0.0004 | enabled | not cached | 3470 | SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy = 'nav_menu' AND t.term_id = 33 LIMIT 1 99 | 0.0003 | enabled | not cached | 946 | SELECT tr.object_id FROM wp_term_relationships AS tr INNER JOIN wp_term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('nav_menu') AND tt.term_id IN ('33') ORDER BY tr.object_id ASC 100 | 0.0007 | enabled | not cached | 11910 | SELECT wp_posts.* FROM wp_posts WHERE 1=1 AND wp_posts.ID IN (231,232,234,244,289,306) AND wp_posts.post_type = 'nav_menu_item' AND ((wp_posts.post_status = 'publish')) ORDER BY wp_posts.menu_order ASC 101 | 0.0003 | enabled | not cached | 2109 | SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (234) ORDER BY meta_id ASC 102 | 0.0003 | enabled | not cached | 2092 | SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (231) ORDER BY meta_id ASC 103 | 0.0004 | enabled | not cached | 2086 | SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (232) ORDER BY meta_id ASC 104 | 0.0003 | enabled | not cached | 2086 | SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (289) ORDER BY meta_id ASC 105 | 0.0003 | enabled | not cached | 2086 | SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (244) ORDER BY meta_id ASC 106 | 0.0005 | enabled | not cached | 2086 | SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (306) ORDER BY meta_id ASC 107 | 0.0007 | enabled | not cached | 26613 | SELECT wp_posts.* FROM wp_posts WHERE 1=1 AND wp_posts.ID IN (207,209,279,239,301) AND wp_posts.post_type = 'page' AND ((wp_posts.post_status = 'publish')) ORDER BY wp_posts.post_date DESC 108 | 0.0001 | enabled | cached | 737 | SELECT t.term_id FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category') AND tr.object_id IN (339) ORDER BY t.name ASC 109 | 0.0001 | enabled | cached | 559 | select meta_key from wp_postmeta where meta_key like '_wp_page_template' and meta_value like 'page-youtube.php' 110 | 0.0001 | enabled | cached | 625 | select meta_key from wp_postmeta where meta_key like '_wp_page_template' and meta_value like 'page-portfolio.php' 111 | 0.0001 | enabled | cached | 559 | select meta_key from wp_postmeta where meta_key like '_wp_page_template' and meta_value like 'page-youtube.php' 112 | 0.0001 | enabled | cached | 625 | select meta_key from wp_postmeta where meta_key like '_wp_page_template' and meta_value like 'page-portfolio.php' -->