| |
| |||||||
| Sponsored Links |
 |
| | LinkBack | Thread Tools | Display Modes |
|
#1
May 22nd, 2008, 02:10 PM
| ||||
| ||||
 Summing for each group only Hi All, I need help trying to get results like below, the part in red. I can calculate the: qtr qtys to get qty total and qty total x cost to get total cost my issue is with adding only for each Group (see text in red). right now i can sum all to get grand total, but need help with just making it add for group1, then group 2, etc. is this normally handled in the sql query or in the asp rs? any help/suggestions is greatly appreciated. ----------------1Q - 2Q - 3 Q - 4Q - Qty Total- Cost - Total Cost Group1 -subGroup1 -----------Item1 1 - 1 - 0 - 0 - 2 - $100 - $200 -----------Item2 0 - 1 - 0 - 1 - 2 - $100 - $200 -subGroup2 -----------Item3 1 - 0 - 0 - 1 - 2 - $100 - $200 -----------Item4 0 - 0 - 0 - 1 - 1 - $100 - $100 Group2 -subGroup3 -----------Item5 0 - 0 - 0 - 1 - 1 - $100 - $100 -----------Item6 2 - 2 - 2 - 1 - 7 - $100 - $700 ETC.... Group1Totals 2 - 2 - 0 - 3 - 7 - blank -$700 Group2Totals 2 - 2 - 2 - 1 - 7 - blank -$800 ETC.... GRAND Total 4 - 4 - 2 - 4 - 14 - blank -$1500 |
| Sponsored Links |
|
#2
May 30th, 2008, 11:00 AM
| ||||
| ||||
 Ok, I've taken out the code for my grand total, I'm trying to just get the totals for each Category. I went back and grouped my sql view and create SumItem for ItemCost. Here is what I have so far but I'm having some trouble. 2 Problems - problem one is I'm not getting the right CategoryDesc to match the right Sum (code in red). problem two, it's not really where I want to have it but I've moved it and moved it and not getting the results I want. I would like this row to really be at the bottom (like example in first post). Please ignore the blank rows in code below, I haven't yet added those. Code: <%
Dim strLastCategory, strLastGrp, strCost,strSumItem
%>
<TABLE border="3" width=1000>
<col width=100><col width=10><col width=10><col width=10><col width=10><col width=10><col width=20><col width=20><col width=20><col width=20><col width=20><col width=20>
<TR bgcolor="#6699CC">
<TH>Description</TH>
<TH>1Q</TH>
<TH>2Q</TH>
<TH>3Q</TH>
<TH>4Q</TH>
<TH>Total Qty</TH>
<TH>Cost</TH>
<TH>1Q Sum</TH>
<TH>2Q Sum</TH>
<TH>3Q Sum</TH>
<TH>4Q Sum</TH>
<TH>Year Total</TH>
</TR>
<%
strLastCategory = ""
strLastGrp = ""
strCost = ""
do while not rs.EOF
if i > iStopRec then
exit do
end if
strCurrentCategory = rs("CategoryDesc")
strCurrentGrp = rs("GrpDesc")
If strCurrentCategory<>strLastCategory Then
Response.Write("<tr bgcolor=#D3D3D3><td colwidth=""100"" colspan=""12""><b>" & strCurrentCategory & "</b></td></tr>")
Response.Write("<tr bgcolor=yellow><td colwidth=""100""><b>" & strCurrentCategory & "</b></td>")
Response.Write("<td colwidth=""100""> </td>")
Response.Write("<td colwidth=""100""> </td>")
Response.Write("<td colwidth=""100""> </td>")
Response.Write("<td colwidth=""100""> </td>")
Response.Write("<td colwidth=""100""> </td>")
Response.Write("<td colwidth=""100""><b>" & strSumItem & "</b></td></tr>")
strSumItem = 0
End If
strLastCategory = strCurrentCategory
strSumItem = strSumItem + rs("SumItem")
If strCurrentGrp<>strLastGrp Then
Response.Write("<tr bgcolor=#D3D3D3><td colwidth=""100"" colspan=""12""><i> " & strCurrentGrp & "</i></td></tr>")
End If
strLastGrp = strCurrentGrp
i = i + 1
%>
<%
intItemCost = rs("ItemCost")
intItem = FormatCurrency((intItemCost / 1000),0)
%>
<TR>
<TD align=right><%=rs("ItemDesc")%> </TD>
<TD bgcolor="#F4A460" align=center> </TD>
<TD bgcolor="#F4A460" align=center> </TD>
<TD bgcolor="#F4A460" align=center> </TD>
<TD bgcolor="#F4A460" align=center> </TD>
<TD bgcolor="#F4A460" align=center> </TD>
<TD bgcolor="#E0FFFF" align=center><%= intItem %></TD>
<TD bgcolor="#F4A460" align=center> </TD>
<TD bgcolor="#F4A460" align=center> </TD>
<TD bgcolor="#F4A460" align=center> </TD>
<TD bgcolor="#F4A460" align=center> </TD>
<TD bgcolor="#F4A460" align=center> </TD>
</TR>
<%
rs.MoveNext
loop
%>
<%
rs.Close
%>
</table>
 |
|
#3
May 30th, 2008, 11:05 AM
| ||||
| ||||
| i'm not sure i'm understanding you ... can you give an example of what is displaying, what the data is and why it's wrong.
__________________ Quote of the Month: Quality: The race for quality has no finish line- so technically, it's more like a death march. Questions to Ponder: What do you do when you see an endangered animal eating an endangered plant? iif([sarcasm]=true,iif([you have to ask]=true,"didn't work","ha ha ha"),"not sarcasm") copyright © 2008 sbenj69 |
|
#4
May 30th, 2008, 11:27 AM
| ||||
| ||||
| ok, i hope this shows up ok. Description---1Q--2Q--3Q--4Q--TotalQty--Cost--1QSum--2QSum--3QSum--4QSum--YrTotal CategoryName1 CategoryName1---------------------------Cost Is Blank (should show $36) --GroupName1 ----Item1--------------------------------$10---------------------------- ----Item2--------------------------------$11---------------------------- --GroupName2 ----Item3--------------------------------$5----------------------------- ----Item4--------------------------------$2----------------------------- ----Item5--------------------------------$8----------------------------- CategoryName2 CategoryName2---------------------------$36 (this total is correct 4 Category1, when it should show Category2 total $15) --GroupName3 ----Item6---------------------------------$7----------------------------- ----Item7---------------------------------$5----------------------------- --GroupName4 ----Item8---------------------------------$3----------------------------- CategoryName3 CategoryName3---------------------------$15 (this total is correct 4 Category2) Right now, I have all the qtr data blank, concentrating on the Cost field here. |
|
#5
May 30th, 2008, 11:31 AM
| ||||
| ||||
| in order to get the correct sum ... in the correct category you need to display it at the end. since you are looping through the records to get your total. until you get to the records, you don't have a total. |
|
#6
May 30th, 2008, 11:36 AM
| ||||
| ||||
| Quote:
|
|
#7
May 30th, 2008, 11:59 AM
| ||||
| ||||
| okay ... try changing this line Code: If strCurrentCategory<>strLastCategory Then Code: If strCurrentCategory<>strLastCategory and strLastCategory <> "" Then |
|
#8
May 30th, 2008, 12:16 PM
| ||||
| ||||
| Quote:
I'll keep moving it around and see where I can put it....when I tried to put at the end it would just give me the lastCategoryName with a total of everything...I need to to show each CategoryName and sum items in that Category. Thanks...I'll keep trying but if you have any other ideas please let me know. |
|
#9
May 30th, 2008, 12:21 PM
| ||||
| ||||
| okay ... try this: Code: If strCurrentCategory<>strLastCategory Then
if strLastCategory <> "" then
Response.Write("<tr bgcolor=yellow><td colwidth=""100""><b>" & strLastCategory & "</b></td>")
Response.Write("<td colwidth=""100""> </td>")
Response.Write("<td colwidth=""100""> </td>")
Response.Write("<td colwidth=""100""> </td>")
Response.Write("<td colwidth=""100""> </td>")
Response.Write("<td colwidth=""100""> </td>")
Response.Write("<td colwidth=""100""><b>" & strSumItem & "</b></td></tr>")
strSumItem = 0
end if
Response.Write("<tr bgcolor=#D3D3D3><td colwidth=""100"" colspan=""12""><b>" & strCurrentCategory & "</b></td></tr>")
Last edited by mehere; May 30th, 2008 at 12:37 PM. |
|
#10
May 30th, 2008, 12:32 PM
| ||||
| ||||
| Not sure exactly what you are trying to do, but to display your categories for each change in category, use this logic: Code: Do While Not rs.EOF
strCurrentCategory = rs("Category")
If strCurrentCategory <> strLastCategory OR strLastCategory = "" Then
response.write strCurrentCategory
End If
' Display Records Here
strLastCategory = strCurrentCategory
rs.MoveNext
Loop
__________________ "You'll never be as perfect as BLaaaaaaaaarche." |
|
|
| Bookmarks |
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
