Answered by:
Displaying total balance by department.

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}%'">
<FilterParameters>
<asp:ControlParameter Name="dept_ID" ControlID="ddlDept" PropertyName="SelectedValue" />
</FilterParameters>
</asp:SqlDataSource>Thanks in advance!
- Edited by Nithya Nathan Thursday, May 3, 2012 7:03 AM
- Moved by Karel ZikmundMicrosoft employee Monday, May 7, 2012 5:42 AM Either ASP.NET or database question (From:Building Development and Diagnostic Tools for .Net)
Thursday, May 3, 2012 7:02 AM
Answers
-
I suggest you post your table schema here. It will be more helpful to reslove this problem.
Have a nice day.
Ghost,
Call me ghost for short, Thanks
To get the better answer, it should be a better question.- Proposed as answer by Ed Price - MSFTMicrosoft employee Thursday, May 10, 2012 2:22 AM
- Marked as answer by Ed Price - MSFTMicrosoft employee Friday, May 18, 2012 7:26 AM
Friday, May 4, 2012 9:03 AM -
For ASP questions, go here: http://forums.asp.net/
Thanks!
Ed Price (a.k.a User Ed), SQL Server Experience Program Manager (Blog, Twitter, Wiki)
- Proposed as answer by Ed Price - MSFTMicrosoft employee Thursday, May 10, 2012 2:22 AM
- Marked as answer by Ed Price - MSFTMicrosoft employee Friday, May 18, 2012 7:26 AM
Thursday, May 10, 2012 2:22 AM
All replies
-
I suggest you post your table schema here. It will be more helpful to reslove this problem.
Have a nice day.
Ghost,
Call me ghost for short, Thanks
To get the better answer, it should be a better question.- Proposed as answer by Ed Price - MSFTMicrosoft employee Thursday, May 10, 2012 2:22 AM
- Marked as answer by Ed Price - MSFTMicrosoft employee Friday, May 18, 2012 7:26 AM
Friday, May 4, 2012 9:03 AM -
For ASP questions, go here: http://forums.asp.net/
Thanks!
Ed Price (a.k.a User Ed), SQL Server Experience Program Manager (Blog, Twitter, Wiki)
- Proposed as answer by Ed Price - MSFTMicrosoft employee Thursday, May 10, 2012 2:22 AM
- Marked as answer by Ed Price - MSFTMicrosoft employee Friday, May 18, 2012 7:26 AM
Thursday, May 10, 2012 2:22 AM