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!
