|
| |
|
|
xgd
Posts: 59
Joined: 5/1/2007
Status: offline
 |
Advanced grouping and Sum - 10/31/2007 14:08:56
Greetings,
I can't seem to get a hold of this one.
I am trying to use sum and advanced grouping
What I have:
Query:fp_sQry="SELECT AR1_CustomerMaster.CustomerName, ARO_InvHistoryDetail.SOItemNumber, ARO_InvHistoryDetail.SODescription, Sum(ARO_InvHistoryDetail.SOUnitPrice) AS SumOfSOUnitPrice FROM (ARN_InvHistoryHeader INNER JOIN ARO_InvHistoryDetail ON ARN_InvHistoryHeader.InvoiceNumber = ARO_InvHistoryDetail.InvoiceNumber) INNER JOIN AR1_CustomerMaster ON ARN_InvHistoryHeader.CustomerNumber = AR1_CustomerMaster.CustomerNumber GROUP BY AR1_CustomerMaster.CustomerName, ARO_InvHistoryDetail.SOItemNumber, ARO_InvHistoryDetail.SODescription HAVING (((ARO_InvHistoryDetail.SOItemNumber) Is Not Null)) ORDER BY AR1_CustomerMaster.CustomerName;"
And
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<%
DIM customer,tempcustomer
customer=FP_FieldVal(fp_rs,"CustomerName")
If customer <> tempcustomer then
tempcustomer=customer
%>
<td bgcolor="#FFFFCC" colspan=2><%=tempcustomer%></td>
</tr>
<%end if%>
<tr>
<td bgcolor="#FFFFFF" >
<%=FP_FieldVal(fp_rs,"SODescription")%> <%=formatcurrency(FP_FieldVal(fp_rs,"SumofSOUnitPrice"),2)%></td>
</tr>
<!--#include file="_fpclass/fpdbrgn2.inc"-->
This works great and returns what I expect:
CustomerName1
Grouped Product Description1 - Grouped Product Price
Grouped Product Description2 - Grouped Product Price
CustomerName2
Grouped Product Description1 - Grouped Product Price
Grouped Product Description2 - Grouped Product Price
etc....
What I need is:
CustomerName1 - SUM(Grouped Product Price)
Grouped Product Description1 - Grouped Product Price
Grouped Product Description2 - Grouped Product Price
CustomerName2 - SUM(Grouped Product Price)
Grouped Product Description1 - Grouped Product Price
Grouped Product Description2 - Grouped Product Price
etc....
Any guidance will be greatly appreciated,
Thanks,
gd
|
|
|
|
William Lee
Posts: 1080
Joined: 1/25/2002
From: Singapore
Status: offline
|
RE: Advanced grouping and Sum - 11/3/2007 12:31:40
I am thinking of Master/Details pages.
Construct a query that will display only the CustomerName and SumOfSOUnitPrice next to it. This should show the Grandsum for each customer.
In this Master page, hyperlink the customername and the corresponding Grandsum as parameters to the next page:
details.asp?CustomerName=<%=FP_FieldVal(fp_rs,"CustomerName")&GrandSum=<%=FP_FieldVal(fp_rs,"SumOfSOUnitPrice")
The details.asp should have a DRW with query that pulls data based on this CustomerName.
Before the repeat results region, you can response.write the 2 parameters that are passed.
<%=Request("CustomerName")%> - <%=Request("GrandSum")%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<%
<td bgcolor="#FFFFFF" >
<%=FP_FieldVal(fp_rs,"SODescription")%> <%=formatcurrency(FP_FieldVal(fp_rs,"SumofSOUnitPrice"),2)%></td>
</tr>
<!--#include file="_fpclass/fpdbrgn2.inc"-->
Hope this helps,
_____________________________
William Lee
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
