|
| |
|
|
Joe Kauffman
Posts: 204
Joined: 6/9/2004
Status: offline
 |
ASP group by recordset field - 6/13/2005 13:31:40
Hi all,
I have an asp page that lists out some information by the search criteria a user enters. In this page, I have been able to come up with a grouping heading where I can group the records and display a header name for each group. I now need to be able to enter the "totalamount" field at the bottom of each grouping. Any ideas on how I can do this? I've tried to mimic what I did with the name grouping "AZDESC" but it doesn't work. Here's the code:
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/sql.asp" -->
<% Set objRS = Server.CreateObject("ADODB.Recordset")
Set objConn=Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = MM_sql_STRING
objConn.Open
If Request.QueryString("startdate") <> "" Then
startdate = Request.QueryString("startdate")
Else
startdate = Request.Form("startdate")
End If
If Request.QueryString("enddate") <> "" Then
enddate = Request.QueryString("enddate")
Else
enddate = Request.Form("enddate")
End If
If Request.QueryString("vendor") <> "" Then
vendor = Request.QueryString("vendor")
Else
vendor = Request.Form("vendor")
End If
IF Request.QueryString("subdivision") <> " " Then
subdivision= Request.QueryString("subdivision")
Else
subdivision = Request.Form("subdivision")
End If
myCriteria = ""
IF trim(startdate)&"">"" THEN
myCriteria = " WHERE chkmnth between'" &request.querystring("startdate")& "' and '" &request.QueryString("enddate")&"'"
END IF
IF trim(vendor)&"">"" THEN
IF myCriteria > "" THEN
myCriteria = myCriteria & " AND"
ELSE
myCriteria = myCriteria & " WHERE"
END IF
myCriteria = myCriteria & "( vendor LIKE '%" & vendor & "%')"
END IF
IF trim(subdivision)&"">"" THEN
IF myCriteria > "" THEN
myCriteria = myCriteria & " AND"
ELSE
myCriteria = myCriteria & " WHERE"
END IF
myCriteria = myCriteria & " ( project LIKE '%" & subdivision & "%')"
END IF
strSQL = "SELECT * FROM dbo.vw_POreportConst " & myCriteria
Set poreport = objConn.Execute(strSQL)
If NOT poreport.EOF Then
End If
strTotal="select sum(chkamnt) as totalamount from dbo.vw_POReportConst" & myCriteria
Set total= objConn.Execute(strTotal)
If Not total.EOF then
End IF
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index
Repeat1__numRows = -1
Repeat1__index = 0
poreport_numRows = poreport_numRows + Repeat1__numRows
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Construction PO Report - Hacienda Builders</title>
<style type="text/css">
<!--
.style3 {
font-size: 14px;
font-weight: bold;
font-family: Verdana;
color: #000066;
}
.style10 {font-family: Verdana; font-size: 12px; }
.style12 {font-size: 16px}
.style13 {
font-size: 16px;
color: #000066;
font-weight: bold;
font-family: Arial, Helvetica, sans-serif;
}
.style15 {font-family: Verdana; font-size: 12px; font-weight: bold; }
-->
</style>
</head>
<body topmargin=".5" leftmargin=".5" rightmargin=".5" bottommargin="1.5" marginwidth=".5" marginheight=".5">
<div align="center">
<table border="0" cellspacing="0" cellpadding="0">
<tr>
<td colspan="7" class="style3 style12">Construction PO Report </td>
</tr>
<tr>
<td colspan="7"><hr color="#000080" size="1"></td>
</tr>
<%
While ((Repeat1__numRows <> 0) AND (NOT poreport.EOF))
%>
<%
DIM GroupName,tmpGroupName
GroupName=(poreport.Fields.Item("AZDESC").Value)
If GroupName <> tmpGroupName then
tmpGroupName=GroupName
%>
<td colspan=7 align=left bgcolor="#CCCCCC"><span class="style13"><u><%=tmpGroupName%> </u></span></td>
</tr>
<tr>
<%end if%>
<tr>
<td colspan="7"><div align="left"></div></td>
</tr>
<tr>
<td width="97" align="center"><span class="style10">Lot</span></td>
<td align="center" width="129"><span class="style10">Cost Type</span></td>
<td align="center" width="125"><span class="style10">Cost Code</span></td>
<td align="center" width="151"><span class="style10">Job Number</span></td>
<td align="center" width="189"><span class="style10">Check Date</span></td>
<td align="center" width="173"><span class="style10">Vendor</span></td>
<td align="center" width="153"><span class="style10">PO Amount</span></td>
</tr>
<tr>
<td align="center"><span class="style10"><%=(poreport.Fields.Item("Lot").Value)%></span></td>
<td align="center" width="129"><span class="style10"><%=(poreport.Fields.Item("csttype").Value)%></span></td>
<td align="center" width="125"><span class="style10"><%=(poreport.Fields.Item("cstcode").Value)%></span></td>
<td align="center" width="151"><span class="style10"><%=(poreport.Fields.Item("jobnum").Value)%></span></td>
<td width="189" align="center" class="style10"><%=(poreport.Fields.Item("CHECKDATE").Value)%></td>
<td align="center" width="173"><span class="style10"><%=(poreport.Fields.Item("vendor").Value)%></span></td>
<td align="center" width="153"><span class="style10"><%= FormatNumber((poreport.Fields.Item("chkamnt").Value), -1, -2, -1, -2) %></span></td>
</tr>
</tr>
<tr>
<td colspan="7">
</tr>
<tr>
</td>
</tr>
<tr>
<td colspan="7"><hr size="1"></td>
</tr>
<tr>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
poreport.MoveNext()
Wend
%>
<tr><td colspan="5"><div align="right" class="style15"></div></td>
<td class="style15">Total Amount: </td>
<td><span class="style15"><%=(total.Fields.Item("totalamount").Value)%></span></td>
</table>
</div>
</body>
</html>
Thanks for your help!
_____________________________
Regards,
Joe Kauffman
|
|
|
|
rdouglass
Posts: 9265
From: Biddeford, ME USA
Status: offline
|
RE: ASP group by recordset field - 6/13/2005 13:56:09
You say "it doesn't work". Does that mean nothing is displayed, wrong results, or an error on the page?
If you're looking for a "page total" (or for that matter a group total), I find this works well and will reduce your DB hit by 1:
DIM a variable to hold the total:
<%
DIM myTotal
myTotal = 0%>
Then, very time you show (or want to add a value), add it to myTotal. For instance:
<td align="center" width="153"><span class="style10"><%= FormatNumber((poreport.Fields.Item("chkamnt").Value), -1, -2, -1, -2) %><%myTotal = myTotal + poreport.Fields.Item("chkamnt").Value%></span></td>
Then, just display the myTotal at the bottom something like:
<%=FormatCurrency(myTotal,2)%>
That help any?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
Joe Kauffman
Posts: 204
Joined: 6/9/2004
Status: offline
|
RE: ASP group by recordset field - 6/13/2005 14:32:59
Makes sense. But I'm getting a type mismatch on this line:
<td align="center" width="153"><span class="style10"><%= FormatNumber((poreport.Fields.Item("chkamnt").Value), -1, -2, -1, -2) %><%myTotal = myTotal + poreport.Fields.Item("chkamnt").Value%></span></td>
Which is what I was getting when I tried it a different way.
_____________________________
Regards,
Joe Kauffman
| |
|
|
|
rdouglass
Posts: 9265
From: Biddeford, ME USA
Status: offline
|
RE: ASP group by recordset field - 6/13/2005 14:48:11
Is 'chkamnt' a number, text, or currency field?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
Joe Kauffman
Posts: 204
Joined: 6/9/2004
Status: offline
|
RE: ASP group by recordset field - 6/13/2005 15:32:16
It's a number field. Acutally Decimal in SQL.
_____________________________
Regards,
Joe Kauffman
| |
|
|
|
rdouglass
Posts: 9265
From: Biddeford, ME USA
Status: offline
|
RE: ASP group by recordset field - 6/13/2005 15:39:35
quote:
<%= FormatNumber((poreport.Fields.Item("chkamnt").Value), -1, -2, -1, -2) %>
If you change this to:
<%=poreport.Fields.Item("chkamnt").Value%>
What do you get? It should be just a number. If it is not, try this:
<%=poreport("chkamnt")%>
If that shows just the number, try this:
<%myTotal = myTotal + poreport("chkamnt")%>
Any luck with that?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
Joe Kauffman
Posts: 204
Joined: 6/9/2004
Status: offline
|
RE: ASP group by recordset field - 6/13/2005 15:50:50
I removed all the formatting and still no luck. It seems to be erroring out on the
myTotal = myTotal + (poreport.Fields.Item("chkamnt").Value)
part of the line.
_____________________________
Regards,
Joe Kauffman
| |
|
|
|
rdouglass
Posts: 9265
From: Biddeford, ME USA
Status: offline
|
RE: ASP group by recordset field - 6/13/2005 15:53:43
quote:
<%myTotal = myTotal + poreport("chkamnt")%>
Did you try that one and the other suggestions with it?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
Joe Kauffman
Posts: 204
Joined: 6/9/2004
Status: offline
|
RE: ASP group by recordset field - 6/13/2005 15:55:46
Sorry bout that, yes I did.
_____________________________
Regards,
Joe Kauffman
| |
|
|
|
rdouglass
Posts: 9265
From: Biddeford, ME USA
Status: offline
|
RE: ASP group by recordset field - 6/13/2005 16:01:33
So what displays if you do this:
<%=poreport("chkamnt")%>
instead of this:
<%myTotal = myTotal + poreport("chkamnt")%>
IOW what get's written to the browser with that ?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
Joe Kauffman
Posts: 204
Joined: 6/9/2004
Status: offline
|
RE: ASP group by recordset field - 6/13/2005 16:05:31
It runs fine and displays the values of the chkamnt field for each record.
_____________________________
Regards,
Joe Kauffman
| |
|
|
|
rdouglass
Posts: 9265
From: Biddeford, ME USA
Status: offline
|
RE: ASP group by recordset field - 6/13/2005 16:14:45
Is there any spaces or anything like that in there that is not a number when using <%=poreport("chkamnt")%>? $ signs or anything like that?
quote:
<%myTotal = myTotal + poreport("chkamnt")%>
And that did not work? You mentioned an error. Can you post that specific error message?
EDIT: Also, just double checking but did you DIM myTotal?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
Joe Kauffman
Posts: 204
Joined: 6/9/2004
Status: offline
|
RE: ASP group by recordset field - 6/13/2005 16:17:24
Yes I dimed mytotal. There are some records that have a - for a negative. Would this do it?
_____________________________
Regards,
Joe Kauffman
| |
|
|
|
rdouglass
Posts: 9265
From: Biddeford, ME USA
Status: offline
|
RE: ASP group by recordset field - 6/13/2005 16:19:59
quote:
You mentioned an error. Can you post that specific error message?
Sorry, just going thru my checklist. If you have that error, can you post it?
Also, 1 more version:
<%myTotal = myTotal + clng(poreport("chkamnt"))%>
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
Joe Kauffman
Posts: 204
Joined: 6/9/2004
Status: offline
|
RE: ASP group by recordset field - 6/13/2005 16:22:26
Error Type:
Microsoft VBScript runtime (0x800A000D)
Type mismatch
/intranet/poreportconst.asp, line 167
_____________________________
Regards,
Joe Kauffman
| |
|
|
|
rdouglass
Posts: 9265
From: Biddeford, ME USA
Status: offline
|
RE: ASP group by recordset field - 6/13/2005 16:23:28
ANd which code specifically are you using to get that error? (Sorry, just trying to keep it clear in my head).
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
Joe Kauffman
Posts: 204
Joined: 6/9/2004
Status: offline
|
RE: ASP group by recordset field - 6/13/2005 16:25:49
<%myTotal = myTotal + poreport("chkamnt")%>
_____________________________
Regards,
Joe Kauffman
| |
|
|
|
Joe Kauffman
Posts: 204
Joined: 6/9/2004
Status: offline
|
RE: ASP group by recordset field - 6/13/2005 16:41:48
It's fine with that code. I actually had to change it a bit to this: <%=(total.Fields.Item("totalamount").Value)%><%myTotal = myTotal + clng(total("totalamount"))%> where it is looking at the sum of the chkamnt. I just need to figure out where to place it now to show the total per the "AZDESC" field that I'm grouping with here:
<%
DIM GroupName,tmpGroupName
GroupName=(poreport.Fields.Item("AZDESC").Value)
If GroupName <> tmpGroupName then
tmpGroupName=GroupName
%>
What it shows now is a total for all the records. Which I will place at the bottom of the page for a complete total. Any ideas where to place this code to show the total for each grouping? Here's the code again:
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/sql.asp" -->
<% Set objRS = Server.CreateObject("ADODB.Recordset")
Set objConn=Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = MM_sql_STRING
objConn.Open
If Request.QueryString("startdate") <> "" Then
startdate = Request.QueryString("startdate")
Else
startdate = Request.Form("startdate")
End If
If Request.QueryString("enddate") <> "" Then
enddate = Request.QueryString("enddate")
Else
enddate = Request.Form("enddate")
End If
If Request.QueryString("vendor") <> "" Then
vendor = Request.QueryString("vendor")
Else
vendor = Request.Form("vendor")
End If
IF Request.QueryString("subdivision") <> " " Then
subdivision= Request.QueryString("subdivision")
Else
subdivision = Request.Form("subdivision")
End If
myCriteria = ""
IF trim(startdate)&"">"" THEN
myCriteria = " WHERE chkmnth between'" &request.querystring("startdate")& "' and '" &request.QueryString("enddate")&"'"
END IF
IF trim(vendor)&"">"" THEN
IF myCriteria > "" THEN
myCriteria = myCriteria & " AND"
ELSE
myCriteria = myCriteria & " WHERE"
END IF
myCriteria = myCriteria & "( vendor LIKE '%" & vendor & "%')"
END IF
IF trim(subdivision)&"">"" THEN
IF myCriteria > "" THEN
myCriteria = myCriteria & " AND"
ELSE
myCriteria = myCriteria & " WHERE"
END IF
myCriteria = myCriteria & " ( project LIKE '%" & subdivision & "%')"
END IF
strSQL = "SELECT * FROM dbo.vw_POreportConst " & myCriteria
Set poreport = objConn.Execute(strSQL)
If NOT poreport.EOF Then
End If
strTotal="select sum(chkamnt) as totalamount from dbo.vw_POReportConst" & myCriteria
Set total= objConn.Execute(strTotal)
If Not total.EOF then
End IF
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index
Repeat1__numRows = -1
Repeat1__index = 0
poreport_numRows = poreport_numRows + Repeat1__numRows
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Construction PO Report - Hacienda Builders</title>
<style type="text/css">
<!--
.style3 {
font-size: 14px;
font-weight: bold;
font-family: Verdana;
color: #000066;
}
.style10 {font-family: Verdana; font-size: 12px; }
.style12 {font-size: 16px}
.style13 {
font-size: 16px;
color: #000066;
font-weight: bold;
font-family: Arial, Helvetica, sans-serif;
}
.style15 {font-family: Verdana; font-size: 12px; font-weight: bold; }
-->
</style>
</head>
<body topmargin=".5" leftmargin=".5" rightmargin=".5" bottommargin="1.5" marginwidth=".5" marginheight=".5">
<div align="center">
<table border="0" cellspacing="0" cellpadding="0">
<tr>
<td colspan="7" class="style3 style12">Construction PO Report </td>
</tr>
<tr>
<td colspan="7"><hr color="#000080" size="1"></td>
</tr>
<%
While ((Repeat1__numRows <> 0) AND (NOT poreport.EOF))
%>
<%
DIM GroupName,tmpGroupName
GroupName=(poreport.Fields.Item("AZDESC").Value)
If GroupName <> tmpGroupName then
tmpGroupName=GroupName
%>
<td colspan=7 align=left bgcolor="#CCCCCC"><span class="style13"><u><%=tmpGroupName%> </u></span></td>
</tr>
<tr>
<%end if%>
<tr>
<td colspan="7"><div align="left"></div></td>
</tr>
<tr>
<td width="97" align="center"><span class="style10">Lot</span></td>
<td align="center" width="129"><span class="style10">Cost Type</span></td>
<td align="center" width="125"><span class="style10">Cost Code</span></td>
<td align="center" width="151"><span class="style10">Job Number</span></td>
<td align="center" width="189"><span class="style10">Check Date</span></td>
<td align="center" width="173"><span class="style10">Vendor</span></td>
<td align="center" width="153"><span class="style10">PO Amount</span></td>
</tr>
<tr>
<td align="center"><span class="style10"><%=(poreport.Fields.Item("Lot").Value)%></span></td>
<td align="center" width="129"><span class="style10"><%=(poreport.Fields.Item("csttype").Value)%></span></td>
<td align="center" width="125"><span class="style10"><%=(poreport.Fields.Item("cstcode").Value)%></span></td>
<td align="center" width="151"><span class="style10"><%=(poreport.Fields.Item("jobnum").Value)%></span></td>
<td width="189" align="center" class="style10"><%=(poreport.Fields.Item("CHECKDATE").Value)%></td>
<td align="center" width="173"><span class="style10"><%=(poreport.Fields.Item("vendor").Value)%></span></td>
<td align="center" width="153"><span class="style10"><%= FormatNumber((poreport.Fields.Item("chkamnt").Value), -1, -2, -1, -2) %></span></td>
</tr>
</tr>
<tr>
<td colspan="7">
</tr>
<tr>
</td>
</tr>
<tr>
<td colspan="7"><hr size="1"></td>
</tr>
<tr>
</tr>
<%
DIM myTotal
myTotal = 0
%>
<td align="center" width="153"><span class="style10"><%=(total.Fields.Item("totalamount").Value)%><%myTotal = myTotal + clng(total("totalamount"))%>
</span></td>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
poreport.MoveNext()
Wend
%>
<tr><td colspan="5"><div align="right" class="style15"></div></td>
<td>
</td>
</table>
</div>
</body>
</html>
_____________________________
Regards,
Joe Kauffman
| |
|
|
|
rdouglass
Posts: 9265
From: Biddeford, ME USA
Status: offline
|
RE: ASP group by recordset field - 6/13/2005 18:42:03
quote:
<%
DIM myTotal
myTotal = 0
%>
That should be very close to the top. Maybe that's part of why it didn't work - it was being reset to 0 all the time. Anyways:
quote:
If GroupName <> tmpGroupName then
tmpGroupName=GroupName
(assuming you no longer want myTotal to be the full running total but rather the sub total of each group)
I would do it somewhere around there. I'd do it like so:
....
If GroupName <> tmpGroupName then
IF Repeat1__index <> 0 THEN 'exclude the very first record%>
<td align="center" width="153"><span class="style10">SubTotal = <%myTotal%></span></td>
<%
myTotal = 0
END IF
tmpGroupName=GroupName
...
and put a line like:
<td align="center" width="153"><span class="style10">SubTotal = <%myTotal%></span></td>
just before your Grand Total (to take care of the last record).
At least that's how I'd do it. Hope it helps.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
Joe Kauffman
Posts: 204
Joined: 6/9/2004
Status: offline
|
RE: ASP group by recordset field - 6/14/2005 13:57:10
That's exactly what I'm looking to do. I've got everything you suggested placed in, but it shows the grand total at the first grouping (instead of that groups total), then the rest of the groups just show "Subtotal = 0".
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/sql.asp" -->
<% Set objRS = Server.CreateObject("ADODB.Recordset")
Set objConn=Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = MM_sql_STRING
objConn.Open
If Request.QueryString("startdate") <> "" Then
startdate = Request.QueryString("startdate")
Else
startdate = Request.Form("startdate")
End If
If Request.QueryString("enddate") <> "" Then
enddate = Request.QueryString("enddate")
Else
enddate = Request.Form("enddate")
End If
If Request.QueryString("vendor") <> "" Then
vendor = Request.QueryString("vendor")
Else
vendor = Request.Form("vendor")
End If
IF Request.QueryString("subdivision") <> " " Then
subdivision= Request.QueryString("subdivision")
Else
subdivision = Request.Form("subdivision")
End If
myCriteria = ""
IF trim(startdate)&"">"" THEN
myCriteria = " WHERE chkmnth between'" &request.querystring("startdate")& "' and '" &request.QueryString("enddate")&"'"
END IF
IF trim(vendor)&"">"" THEN
IF myCriteria > "" THEN
myCriteria = myCriteria & " AND"
ELSE
myCriteria = myCriteria & " WHERE"
END IF
myCriteria = myCriteria & "( vendor LIKE '%" & vendor & "%')"
END IF
IF trim(subdivision)&"">"" THEN
IF myCriteria > "" THEN
myCriteria = myCriteria & " AND"
ELSE
myCriteria = myCriteria & " WHERE"
END IF
myCriteria = myCriteria & " ( project LIKE '%" & subdivision & "%')"
END IF
strSQL = "SELECT * FROM dbo.vw_POreportConst " & myCriteria
Set poreport = objConn.Execute(strSQL)
If NOT poreport.EOF Then
End If
strTotal="SELECT SUM(chkamnt) AS totalamount FROM dbo.vw_POReportConst" & myCriteria
Set total= objConn.Execute(strTotal)
If Not total.EOF then
End IF
%>
<%
DIM myTotal
myTotal = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index
Repeat1__numRows = -1
Repeat1__index = 0
poreport_numRows = poreport_numRows + Repeat1__numRows
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Construction PO Report - Hacienda Builders</title>
<style type="text/css">
<!--
.style3 {
font-size: 14px;
font-weight: bold;
font-family: Verdana;
color: #000066;
}
.style10 {font-family: Verdana; font-size: 12px; }
.style12 {font-size: 16px}
.style13 {
font-size: 16px;
color: #000066;
font-weight: bold;
font-family: Arial, Helvetica, sans-serif;
}
.style15 {font-family: Verdana; font-size: 12px; font-weight: bold; }
-->
</style>
</head>
<body topmargin=".5" leftmargin=".5" rightmargin=".5" bottommargin="1.5" marginwidth=".5" marginheight=".5">
<div align="center">
<table border="0" cellspacing="0" cellpadding="0">
<tr>
<td colspan="7" class="style3 style12">Construction PO Report </td>
</tr>
<tr>
<td colspan="7"><hr color="#000080" size="1"></td>
</tr>
<%
While ((Repeat1__numRows <> 0) AND (NOT poreport.EOF))
%>
<%
DIM GroupName,tmpGroupName
myTotal = myTotal + clng(total("totalamount"))
GroupName=(poreport.Fields.Item("AZDESC").Value)
If GroupName <> tmpGroupName then
IF Repeat1__index <> 0 THEN 'exclude the very first record
myTotal = 0
END IF
tmpGroupName=GroupName
%>
<td colspan=7 align=left bgcolor="#CCCCCC"><span class="style13"><u><%=tmpGroupName%> - SubTotal = <%=myTotal%> </u></span></td>
</tr>
<tr>
<%end if%>
<tr>
<td colspan="7"><div align="left"></div></td>
</tr>
<tr>
<td width="97" align="center"><span class="style10">Lot</span></td>
<td align="center" width="129"><span class="style10">Cost Type</span></td>
<td align="center" width="125"><span class="style10">Cost Code</span></td>
<td align="center" width="151"><span class="style10">Job Number</span></td>
<td align="center" width="189"><span class="style10">Check Date</span></td>
<td align="center" width="173"><span class="style10">Vendor</span></td>
<td align="center" width="153"><span class="style10">PO Amount</span></td>
</tr>
<tr>
<td align="center"><span class="style10"><%=(poreport.Fields.Item("Lot").Value)%></span></td>
<td align="center" width="129"><span class="style10"><%=(poreport.Fields.Item("csttype").Value)%></span></td>
<td align="center" width="125"><span class="style10"><%=(poreport.Fields.Item("cstcode").Value)%></span></td>
<td align="center" width="151"><span class="style10"><%=(poreport.Fields.Item("jobnum").Value)%></span></td>
<td width="189" align="center" class="style10"><%=(poreport.Fields.Item("CHECKDATE").Value)%></td>
<td align="center" width="173"><span class="style10"><%=(poreport.Fields.Item("vendor").Value)%></span></td>
<td align="center" width="153"><span class="style10"><%= FormatNumber((poreport.Fields.Item("chkamnt").Value), -1, -2, -1, -2) %></span></td>
</tr>
</tr>
<tr>
<td colspan="7">
</tr>
<tr>
</td>
</tr>
<tr>
<td colspan="7"><hr size="1"></td>
</tr>
<tr>
</tr>
<td align="center" width="153"><span class="style10">
</span></td>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
poreport.MoveNext()
Wend
%>
<tr><td colspan="5"><div align="right" class="style15"></div></td>
<td>
</td>
</table>
</div>
</body>
</html>
_____________________________
Regards,
Joe Kauffman
| |
|
|
|
Joe Kauffman
Posts: 204
Joined: 6/9/2004
Status: offline
|
RE: ASP group by recordset field - 6/16/2005 12:41:20
I've made some headway using some of the code you suggested. I'm now getting the total for all records at the bottom of the page, but have a problem that's probably pretty simple with the group total. What I'm getting with the below code is a running total for each record and I need it to be a total for each group and have it reset on the change of the group. I know I'm close but can't seem to find what needs to be moved around or added. Any thoughts?
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/sql.asp" -->
<% Set objRS = Server.CreateObject("ADODB.Recordset")
Set objConn=Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = MM_sql_STRING
objConn.Open
If Request.QueryString("startdate") <> "" Then
startdate = Request.QueryString("startdate")
Else
startdate = Request.Form("startdate")
End If
If Request.QueryString("enddate") <> "" Then
enddate = Request.QueryString("enddate")
Else
enddate = Request.Form("enddate")
End If
If Request.QueryString("vendor") <> "" Then
vendor = Request.QueryString("vendor")
Else
vendor = Request.Form("vendor")
End If
IF Request.QueryString("subdivision") <> " " Then
subdivision= Request.QueryString("subdivision")
Else
subdivision = Request.Form("subdivision")
End If
myCriteria = ""
IF trim(startdate)&"">"" THEN
myCriteria = " WHERE chkmnth between'" &request.querystring("startdate")& "' and '" &request.QueryString("enddate")&"'"
END IF
IF trim(vendor)&"">"" THEN
IF myCriteria > "" THEN
myCriteria = myCriteria & " AND"
ELSE
myCriteria = myCriteria & " WHERE"
END IF
myCriteria = myCriteria & "( vendor LIKE '%" & vendor & "%')"
END IF
IF trim(subdivision)&"">"" THEN
IF myCriteria > "" THEN
myCriteria = myCriteria & " AND"
ELSE
myCriteria = myCriteria & " WHERE"
END IF
myCriteria = myCriteria & " ( project LIKE '%" & subdivision & "%')"
END IF
strSQL = "SELECT * FROM dbo.vw_POreportConst " & myCriteria
Set poreport = objConn.Execute(strSQL)
If NOT poreport.EOF Then
End If
strTotal="select sum(chkamnt) as totalamount from dbo.vw_POReportConst" & myCriteria
Set total= objConn.Execute(strTotal)
If Not total.EOF then
End IF
%>
<%
DIM myTotal
myTotal = 0%>
<%
Dim Repeat1__numRows
Dim Repeat1__index
Repeat1__numRows = -1
Repeat1__index = 0
poreport_numRows = poreport_numRows + Repeat1__numRows
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Construction PO Report - Hacienda Builders</title>
<style type="text/css">
<!--
.style3 {
font-size: 14px;
font-weight: bold;
font-family: Verdana;
color: #000066;
}
.style10 {font-family: Verdana; font-size: 12px; }
.style12 {font-size: 16px}
.style13 {
font-size: 16px;
color: #000066;
font-weight: bold;
font-family: Arial, Helvetica, sans-serif;
}
.style15 {font-family: Verdana; font-size: 12px; font-weight: bold; }
-->
</style>
</head>
<body topmargin=".5" leftmargin=".5" rightmargin=".5" bottommargin="1.5" marginwidth=".5" marginheight=".5">
<div align="center">
<table border="0" cellspacing="0" cellpadding="0">
<tr>
<td colspan="7" class="style3 style12">Construction PO Report </td>
</tr>
<tr>
<td colspan="7"><hr color="#000080" size="1"></td>
</tr>
<%
While ((Repeat1__numRows <> 0) AND (NOT poreport.EOF))
%>
<%
DIM GroupName,tmpGroupName
GroupName=(poreport.Fields.Item("AZDESC").Value)
If GroupName <> tmpGroupName then
tmpGroupName=GroupName
%>
<td colspan=7 align=left bgcolor="#CCCCCC"><span class="style13"><u><%=tmpGroupName%> </u></span></td>
</tr>
<tr>
<%end if%>
<tr>
<td colspan="7"><div align="left"></div></td>
</tr>
<tr>
<td width="97" align="center"><span class="style10">Lot</span></td>
<td align="center" width="129"><span class="style10">Cost Type</span></td>
<td align="center" width="125"><span class="style10">Cost Code</span></td>
<td align="center" width="151"><span class="style10">Job Number</span></td>
<td align="center" width="189"><span class="style10">Check Date</span></td>
<td align="center" width="173"><span class="style10">Vendor</span></td>
<td align="center" width="153"><span class="style10">PO Amount</span></td>
</tr>
<tr>
<td rowspan="2" align="center"><span class="style10"><%=(poreport.Fields.Item("Lot").Value)%></span></td>
<td width="129" rowspan="2" align="center"><span class="style10"><%=(poreport.Fields.Item("csttype").Value)%></span></td>
<td width="125" rowspan="2" align="center"><span class="style10"><%=(poreport.Fields.Item("cstcode").Value)%></span></td>
<td width="151" rowspan="2" align="center"><span class="style10"><%=(poreport.Fields.Item("jobnum").Value)%></span></td>
<td width="189" rowspan="2" align="center" class="style10"><%=(poreport.Fields.Item("CHECKDATE").Value)%></td>
<td width="173" rowspan="2" align="center"><span class="style10"><%=(poreport.Fields.Item("vendor").Value)%></span></td>
<td align="center" width="153"><span class="style10"><%=poreport("chkamnt")%><%myTotal = myTotal + clng(poreport("chkamnt"))%></span></td>
</tr>
<%
''''''''''''''insert the total for each group''''''''''''''''
%>
<tr>
<td align="center"><%=FormatCurrency(myTotal,2)%></td>
</tr>
</tr>
<tr>
<td colspan="7">
</tr>
<tr>
</td>
</tr>
<tr>
<td colspan="7"><hr size="1"></td>
</tr>
<tr>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
poreport.MoveNext()
Wend
%>
<tr><td colspan="5"><div align="right" class="style15"></div></td>
<td class="style15">Total Amount: </td>
<%
'''''''''''''' insert total for all records ''''''''''''''''''
%>
<td><span class="style15"><%=FormatCurrency(myTotal,2)%></span></td>
</table>
</div>
</body>
</html>
Thanks for your help!
_____________________________
Regards,
Joe Kauffman
| |
|
|
|
rdouglass
Posts: 9265
From: Biddeford, ME USA
Status: offline
|
RE: ASP group by recordset field - 6/16/2005 12:45:38
I've looked at this a few times and thought I had it solved but I guess I'm unclear about where *exactly* are you breaking the groups. IOW what variable (or line of code) are you using to distinguish the ending of one group and the beginning of the next?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
Joe Kauffman
Posts: 204
Joined: 6/9/2004
Status: offline
|
RE: ASP group by recordset field - 6/16/2005 12:49:02
I'm using Spooky's Grouping Records code:
<%
DIM GroupName,tmpGroupName
GroupName=(poreport.Fields.Item("AZDESC").Value)
If GroupName <> tmpGroupName then
tmpGroupName=GroupName
%>
<td colspan=7 align=left bgcolor="#CCCCCC"><span class="style13"><u><%=tmpGroupName%> </u></span></td>
</tr>
<tr>
<%end if%>
_____________________________
Regards,
Joe Kauffman
| |
|
|
|
rdouglass
Posts: 9265
From: Biddeford, ME USA
Status: offline
|
RE: ASP group by recordset field - 6/16/2005 13:12:28
quote:
<%
DIM GroupName,tmpGroupName
GroupName=(poreport.Fields.Item("AZDESC").Value)
If GroupName <> tmpGroupName then
tmpGroupName=GroupName
%>
<td colspan=7 align=left bgcolor="#CCCCCC"><span class="style13"><u><%=tmpGroupName%> </u></span></td>
</tr>
<tr>
<%end if%>
Try using this version:
<%
DIM GroupName,tmpGroupName
GroupName=(poreport.Fields.Item("AZDESC").Value)
If GroupName <> tmpGroupName then
IF Repeat1__index <> 0 THEN 'exclude the very first record%>
<td align="center" width="153"><span class="style10">SubTotal = <%myTotal%></span></td>
<%
myTotal = 0
END IF
tmpGroupName=GroupName
%>
<td colspan=7 align=left bgcolor="#CCCCCC"><span class="style13"><u><%=tmpGroupName%> </u></span></td>
</tr>
<tr>
<%end if
myTotal = myTotal + cdbl(poreport("chkamnt"))%>
That make any diff?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
Joe Kauffman
Posts: 204
Joined: 6/9/2004
Status: offline
|
RE: ASP group by recordset field - 6/16/2005 13:42:24
It seems to be working great, but it is doubling the records value before it adds them. For example,
Record 1 has a value of 100
Total=200
Record 2 has a value of 200
Total=600
Where it is doubling record 1, then doubling record 2 then adding them together.
_____________________________
Regards,
Joe Kauffman
| |
|
|
|
rdouglass
Posts: 9265
From: Biddeford, ME USA
Status: offline
|
RE: ASP group by recordset field - 6/16/2005 14:02:03
Can you search the code for:
myTotal + cdbl(poreport("chkamnt"))
it must be adding them twice somewhere in the code.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
Joe Kauffman
Posts: 204
Joined: 6/9/2004
Status: offline
|
RE: ASP group by recordset field - 6/16/2005 14:11:59
That did it! Thanks a ton for all your help!
_____________________________
Regards,
Joe Kauffman
| |
|
 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 
