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!
Please mark as 'answer' if it is helpful. Thanks in advance!
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……：
NIthya Natha...
Member
6 Points
59 Posts
Total by deparment in different column
May 03, 2012 04:41 AM|LINK
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!
ishq
Member
256 Points
76 Posts
Re: Displaying total by category.
May 03, 2012 11:13 AM|LINK
You can find the sum in query. Use sub query for calculating sum, group by DeptId
Lathika Morthodi
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Total by deparment in different column
May 05, 2012 01:58 AM|LINK
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