locked
Total by deparment in different column RRS feed

  • Question

  • User1414062701 posted

    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 this part. 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}%'"> 
                <FilterParameters> 
                <asp:ControlParameter Name="dept_ID" ControlID="ddlDept" PropertyName="SelectedValue" /> 
                </FilterParameters> 
                </asp:SqlDataSource>

    Thanks in advance!

    Thursday, May 3, 2012 12:41 AM

Answers

All replies

  • User-830235260 posted

    You can find the sum in query. Use sub query for calculating sum, group by DeptId

    Thursday, May 3, 2012 7:13 AM
  • User3866881 posted

    Hello:)

    As far as I see,I think it not easy to deal with directly with LINQ to SQL……But I think you can use the sample of MSDN to group the GridView with the same cell's value and fetch the total result as Sum……:

    http://www.agrinei.com/gridviewhelper/gridviewhelper_en.htm

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 4, 2012 9:58 PM