I have a table called Income where i will store costCentre and their income per Month. In this case, one costCentre can have few income amounts in that one table.
So im trying to form a gridview that will tell the total income for a cost centre from that table, where it have to calculate all the amounts related to that particular costCentre and sum it up. The gridview will look like this:
Department | Cost Centre | Balance Income
HR 1 23.00
2 26.00
3 45.00
IT 4 34.00
5 22.00
*the balance income is the sum of incomes from table income that related to each costCentre.
Right now, im unsure on the way to calculate the total that is associated to the costCentre. Below is the code that i have tried but im getting this error : "You tried to execute a query that does not include the specified expression 'dept_ID' as part of
an aggregate function."
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" AllowPaging="true" ShowFooter="true" AllowSorting="true" DataSourceID="dsGridView" PageSize="10" OnRowDataBound="GridView1_RowDataBound">
<Columns>
<asp:BoundField DataField="dept_name" HeaderText="Department Name" SortExpression="dept_ID" />
<asp:BoundField DataField="costCentre_name" HeaderText="Cost Centre" SortExpression="cc_ID" />
<asp:BoundField DataField="balance" HeaderText="Balance" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="dsGridView" runat="server" ConnectionString="<%$ ConnectionStrings:MyConn %>" ProviderName="<%$ ConnectionStrings:MyConn.ProviderName %>"
SelectCommand="SELECT DISTINCT d.[dept_ID], d.[dept_name], c.[cc_ID], c.[costCentre_name], (SUM(m.amount)) AS [balance]
FROM department d, costCentre c, monthlyIncome m, utilization u
WHERE d.[dept_ID]=c.[dept_ID] AND c.[cc_ID]=m.[cc_ID] AND 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>
Can anyone assist me on the way to get the related value to be displayed on the correct cell in the gridview?
Thanks in advance!
Please mark as 'answer' if it is helpful. Thanks in advance!
SelectCommand="SELECT DISTINCT d.[dept_ID], d.[dept_name], c.[cc_ID], c.[costCentre_name], (SUM(m.amount)) AS [balance]
FROM department d, costCentre c, monthlyIncome m, utilization u
WHERE d.[dept_ID]=c.[dept_ID] AND c.[cc_ID]=m.[cc_ID] AND c.[cc_ID]=u.[cc_ID]"
FilterExpression="Convert(dept_ID, 'System.String') like '{0}%'">
Hello：）
I noticed it that you'd renamed the table "department" as another name called "d"，So maybe Convert cannot recognize it……you can try to remove the alias name and do the following thing：
SELECT DISTINCT d.[dept_ID], d.[dept_name], c.[cc_ID], c.[costCentre_name], (SUM(m.amount)) AS [balance]
FROM department costCentre c, monthlyIncome m, utilization u
WHERE department.[dept_ID]=c.[dept_ID] AND c.[cc_ID]=m.[cc_ID] AND c.[cc_ID]=u.[cc_ID]"
FilterExpression="Convert(dept_ID, 'System.String') like '{0}%'">
One department can have many cost centres. So these cost centre will have their income every month and it will be stored in monthlyIncome table by month. So basically monthlyIncome can have many incomes for just one particular cost centre. Meanwhile, utilization
table works in same way but value will be stored when income is used.
So what im trying to do is actually to display the balance available for a particular cost centre to the user. So what it have to do is to calculate the sum in monthlyIncome and minus the sum in utilization according to costCentre and display it in dedicated
row as per the related costCentre.
Example:
Department | Cost Centre | Balance Income
HR 1 23.00<----Value here is the sum(monthlyIncome) - sum(utilization), dedicated value for row
2 26.00
3 45.00
IT 4 34.00
5 22.00
Hope im clear here, any idea how to make it possible?
Please mark as 'answer' if it is helpful. Thanks in advance!
NIthya Natha...
Member
6 Points
59 Posts
Displaying sum from other table.
Apr 27, 2012 07:50 AM|LINK
Guys,
I have a table called Income where i will store costCentre and their income per Month. In this case, one costCentre can have few income amounts in that one table.
So im trying to form a gridview that will tell the total income for a cost centre from that table, where it have to calculate all the amounts related to that particular costCentre and sum it up. The gridview will look like this:
Department | Cost Centre | Balance Income
HR 1 23.00
2 26.00
3 45.00
IT 4 34.00
5 22.00
*the balance income is the sum of incomes from table income that related to each costCentre.
Right now, im unsure on the way to calculate the total that is associated to the costCentre. Below is the code that i have tried but im getting this error : "You tried to execute a query that does not include the specified expression 'dept_ID' as part of an aggregate function."
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" AllowPaging="true" ShowFooter="true" AllowSorting="true" DataSourceID="dsGridView" PageSize="10" OnRowDataBound="GridView1_RowDataBound"> <Columns> <asp:BoundField DataField="dept_name" HeaderText="Department Name" SortExpression="dept_ID" /> <asp:BoundField DataField="costCentre_name" HeaderText="Cost Centre" SortExpression="cc_ID" /> <asp:BoundField DataField="balance" HeaderText="Balance" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="dsGridView" runat="server" ConnectionString="<%$ ConnectionStrings:MyConn %>" ProviderName="<%$ ConnectionStrings:MyConn.ProviderName %>" SelectCommand="SELECT DISTINCT d.[dept_ID], d.[dept_name], c.[cc_ID], c.[costCentre_name], (SUM(m.amount)) AS [balance] FROM department d, costCentre c, monthlyIncome m, utilization u WHERE d.[dept_ID]=c.[dept_ID] AND c.[cc_ID]=m.[cc_ID] AND 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>Can anyone assist me on the way to get the related value to be displayed on the correct cell in the gridview?
Thanks in advance!
kelviyean
Member
228 Points
54 Posts
Re: Displaying sum from other table.
Apr 27, 2012 08:26 AM|LINK
Hello,
Can you please provide your table structure that you are using to build the query.
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Displaying sum from other table.
Apr 29, 2012 12:48 AM|LINK
Hello：）
I noticed it that you'd renamed the table "department" as another name called "d"，So maybe Convert cannot recognize it……you can try to remove the alias name and do the following thing：
SELECT DISTINCT d.[dept_ID], d.[dept_name], c.[cc_ID], c.[costCentre_name], (SUM(m.amount)) AS [balance]
FROM department costCentre c, monthlyIncome m, utilization u
WHERE department.[dept_ID]=c.[dept_ID] AND c.[cc_ID]=m.[cc_ID] AND c.[cc_ID]=u.[cc_ID]"
FilterExpression="Convert(dept_ID, 'System.String') like '{0}%'">
NIthya Natha...
Member
6 Points
59 Posts
Re: Displaying sum from other table.
Apr 30, 2012 01:44 AM|LINK
I have 4 tables:
*department(dept_ID, dept_short_name)
*costCentre(cc_ID, dept_ID, costCentre_name)
*monthlyIncome(income_ID, cc_ID, month, amount)
*utilization(util_ID, cc_ID, month, amount)
One department can have many cost centres. So these cost centre will have their income every month and it will be stored in monthlyIncome table by month. So basically monthlyIncome can have many incomes for just one particular cost centre. Meanwhile, utilization table works in same way but value will be stored when income is used.
So what im trying to do is actually to display the balance available for a particular cost centre to the user. So what it have to do is to calculate the sum in monthlyIncome and minus the sum in utilization according to costCentre and display it in dedicated row as per the related costCentre.
Example:
Department | Cost Centre | Balance Income
HR 1 23.00<----Value here is the sum(monthlyIncome) - sum(utilization), dedicated value for row
2 26.00
3 45.00
IT 4 34.00
5 22.00
Hope im clear here, any idea how to make it possible?
TimoYang
Contributor
3732 Points
1275 Posts
Re: Displaying sum from other table.
Apr 30, 2012 06:54 AM|LINK
Hi——
You should show all the records first，and then combine them together to show one for duplicated values……
For more please refer this—— Groupped GridView：http://www.agrinei.com/gridviewhelper/gridviewhelper_en.htm