Totals on Reports using LinqDataSource

One common request for reporting is to show aggregations as well, like sums, averages, counts, etc among with the report itself. So for example if an expenses/charges/profits report shows the list of all products sold in some date/time range, then the person who analyzes the report would like to see and some totals (sum of the costs, sum of the charges, sum of profits and the total number of products), somewhere in the report's bottom.

If the number of reports is slightly lower and the features are limited, like the reports need to support only the HTML version, LINQ with LinqDataSource can be the right thing to use, for RAD reports. The control is built so the developers can write less and do more, so basically they set the ContextTypeName, TableName, Select statement if any, WhereParameters, OrderParameters, etc, but this control it doesn't yield the aggregates.

I extended the LinqDataSource in the spirit of "write less do more" principle and I changed its default behavior to be used for the aggregation purpose. This new data source will receive the ID of the reports datasource. Based on this ID it gets and sets the ContextTypeName, the Where parameters and it the GroupBy is "1".

 public class bsDataSourceTotals : LinqDataSource
    {
        public string DataSourceID {get; set;}

        protected override void OnInit(EventArgs e)
        {
            this.Load += new EventHandler(bsDataSourceTotals_Load);
            base.OnInit(e);
        }

        void bsDataSourceTotals_Load(object sender, EventArgs e)
        {
            var dataSource = FindControlRecursive(this.Page, DataSourceID) as LinqDataSource; 

            this.TableName = dataSource.TableName;
            this.WhereParameters.Clear();
            foreach (var param in dataSource.WhereParameters.Cast())
            {
                this.WhereParameters.Add(param);
            }
            this.Where = dataSource.Where;
            this.GroupBy = "1";
            this.OrderBy = null;
            this.OrderByParameters.Clear();
        }

        private Control FindControlRecursive(Control root, string id)
        {
            if (root.ID == id)
            {
                return root;
            }
            foreach (Control c in root.Controls)
            {
                Control t = FindControlRecursive(c, id);
                if (t != null)
                {
                    return t;
                }
            }
            return null;
        }
    }

I'll show here a working example from an application I wrote.

<asp:LinqDataSource ID="bsDataSource1" runat="server"
        ContextTypeName="..."
        Select="new (campaign_name, 
     campaign_id, 
     campaign_total_recipients,
     client.client_id as client_id, 
     client.client_name as client_name,
     campaign_cost, 
     campaign_charged, 
     profit, 
     campaign_sent_date)"
        TableName="campaigns" 
 Where='campaign_sent_date.Date >= @start_date.Date
                AND campaign_sent_date.Date <= @end_date.Date'
        AutoGenerateOrderByClause="true">
        <WhereParameters>
            <asp:ControlParameter Name="start_date" Type="DateTime" ControlID="ddStart" PropertyName="Date" />
            <asp:ControlParameter Name="end_date" Type="DateTime" ControlID="ddFinish" PropertyName="Date" />                                       
        </WhereParameters>
</asp:LinqDataSource>


<ep:bsDataSourceTotals ID="bsDataSource1Totals"
        runat="server" 
 DataSourceID="bsDataSource1"
        Select="new ( Sum(campaign_total_recipients) as Recipients,
                      Sum(campaign_cost) as Cost,
                      Sum(campaign_charged) as Charged,
                      Sum(profit) as Profit )">
</ep:bsDataSourceTotals>

The report is created with a ListView control and in its LayoutTemplate is a Repeater bound to the "bsDataSource1Totals" data source.

 <asp:Repeater ID="rpTotals" runat="server" DataSourceID="bsDataSource1Totals">
 <ItemTemplate>
              <td><%# Eval("Recipients", "{0:#,##0}")%></td>
              <td><%# Eval("Cost", "${0:#,##0.00}")%></td>
             <td><%# Eval("Charged", "£{0:#,##0.00}")%></td>
             <td><%# Eval("Profit", "£{0:#,##0.00}")%></td>
 </ItemTemplate>
</asp:Repeater>

The main trick is GroupBy="1", which I found it here.