ASP group by recordset field (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

Joe Kauffman -> 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!



rdouglass -> 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?



Joe Kauffman -> 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.



rdouglass -> RE: ASP group by recordset field (6/13/2005 14:48:11)

Is 'chkamnt' a number, text, or currency field?



Joe Kauffman -> RE: ASP group by recordset field (6/13/2005 15:32:16)

It's a number field. Acutally Decimal in SQL.



rdouglass -> 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?



Joe Kauffman -> 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.



rdouglass -> 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?



Joe Kauffman -> RE: ASP group by recordset field (6/13/2005 15:55:46)

Sorry bout that, yes I did.



rdouglass -> 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 ?



Joe Kauffman -> 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.



rdouglass -> 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?



Joe Kauffman -> 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?



rdouglass -> 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"))%>



Joe Kauffman -> 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



rdouglass -> 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).



Joe Kauffman -> RE: ASP group by recordset field (6/13/2005 16:25:49)

<%myTotal = myTotal + poreport("chkamnt")%>



rdouglass -> RE: ASP group by recordset field (6/13/2005 16:27:31)

Did you give this one a try?

<%myTotal = myTotal + clng(poreport("chkamnt"))%>

EDIT: I may be using the wrong function. [:'(]

Possibly:

<%myTotal = myTotal + cdbl(poreport("chkamnt"))%>

Give me 100 guesses and I just might get it.[:D]



Joe Kauffman -> 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>



rdouglass -> 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.



Joe Kauffman -> 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>



Joe Kauffman -> 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!





rdouglass -> 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?



Joe Kauffman -> 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%>



rdouglass -> 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?



Joe Kauffman -> 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.




rdouglass -> 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.



Joe Kauffman -> RE: ASP group by recordset field (6/16/2005 14:11:59)

That did it! Thanks a ton for all your help!




Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.125