Displaying total balance by department. RRS feed

  • Question

  • Guys I have a situation here as below:

    Department    |    Cost Centre    |     Balance   | Total

        HR                         CC1               Value 1        Value 1 + Value 2 + Value 3

                                     CC2               Value 2

                                     CC3               Value 3

       IT                           CC4               Value 4          Value 4 + Value 5

                                     CC5               Value 5

       OIL                         CC6               Value 6           Value 6

    How do i get the total in th 'Total' column as above? Any idea how to sum it according to the department and display it in a single row and in the column 'Total'?

    Here's the code that i construct until 'Balance' column. Im clueless on the part to get the total by department. Any help wud be great.

    <asp:SqlDataSource ID="dsGridView" runat="server" ConnectionString="<%$ ConnectionStrings:MyConn %>" ProviderName="<%$ ConnectionStrings:MyConn.ProviderName %>" 
            SelectCommand="SELECT d.dept_ID, d.dept_name, c.cc_ID, c.costCentre_name, IIF(ISNULL(i.Net, 0), 0, i.Net) - IIF(ISNULL(u.Net), 0, u.Net) AS Balance 
    FROM ((((department d) 
    INNER JOIN costCentre c ON d.dept_ID=c.dept_ID) 
    LEFT JOIN (SELECT cc_ID, SUM(amount) AS Net FROM monthlyIncome GROUP BY cc_ID) i ON c.cc_ID=i.cc_ID) 
    LEFT JOIN (SELECT cc_ID, SUM(amount) AS Net FROM utilization GROUP BY cc_ID) u ON c.cc_ID=u.cc_ID)" 
            FilterExpression="Convert(dept_ID, 'System.String')  like '{0}%'"> 
    <asp:ControlParameter Name="dept_ID" ControlID="ddlDept" PropertyName="SelectedValue" /> 

    Thanks in advance!

    Thursday, May 3, 2012 7:02 AM


All replies