As promised, here's a sample ASP report from Northwind using my ASP Report Class (http://weblogs.sqlteam.com/jeffs/posts/526.aspx).
Hopefully, everyone will agree that the script is very easy to write and read and much shorter. All grouping (for both headers and footers), subtotals and running totals are completely taken care of by using the class, and it is giving you recordset functionality (movenext and EOF and column reference by name) using the speedy GetRows() method under the hood.
For this to work, you must have the class saved as “ReportClass.asp“ in the same folder as this script (notice the include).
<%@ Language=VBScript %>
<%Option Explicit%>
<%@ Language=VBScript %>
<%Option Explicit%>
<!-- #include file="ReportClass.asp" -->
<%
dim c,r,g
set c = server.CreateObject("ADODB.Connection")
c.open “your connect string to Northwind here“
set r = c.Execute ("Select * from Invoices ORDER BY SalesPerson, CustomerID, OrderDate, ProductID")
' here we declare our Report object and intialize it
set g = new Report
g.Init r,array("SalesPerson","CustomerID","OrderDate"), array("Quantity","Discount","ExtendedPrice")
' close the DB connection; we're done with it!
c.close
%>
<table width="100%" ID="Table1">
<col width="20%"><col width="20%"><col width="20%">
<col width="10%"><col width="10%"><col width="10%">
<col width="10%">
<%do while not g.eof
' Group Headers:
if g.Groups("Salesperson").header then%>
<TR style="font-size:large; font-weight:bold;">
<TD col colspan=8><%=g("SalesPerson")%></TD>
</TR>
<%
end if
if g.groups("CustomerID").header then%>
<TR style="font-weight:bold;">
<TD></TD>
<TD colspan=7>Customer: <%=g("CustomerName")%></TD>
</TR>
<%
end if
if g.groups("OrderDate").header then%>
<TR>
<TD colspan=2></TD>
<TD colspan=6>Order Date: <%=g("OrderDate")%></TD>
</TR>
<%
end if
' Details (note the running total accross the
' "order date" group):
%>
<TR>
<TD colspan=3>
<TD><%=g("ProductName")%></TD>
<TD><%=g("Quantity")%></TD>
<TD><%=g("Discount")%></TD>
<TD><%=g("ExtendedPrice")%></TD>
<TD><%=g.Groups("OrderDate").ColTotal("ExtendedPrice")%></TD>
</TR>
<%
' Group Footers:
if g.groups("OrderDate").Footer then %>
<TR style="font-style:italic;">
<TD Colspan=2>
<TD colspan=2>Order Totals</TD>
<TD><%=g.Groups("OrderDate").ColTotal("Quantity")%></TD>
<TD><%=g.Groups("OrderDate").ColTotal("Discount")%></TD>
<TD><%=g.Groups("OrderDate").ColTotal("ExtendedPrice")%></TD>
</TR>
<%
end if
if g.groups("CustomerID").footer then %>
<TR style="Font-weight:bold;">
<TD></TD>
<TD>Customer Totals</TD>
<TD colspan=2></TD>
<TD><%=g.Groups("CustomerID").ColTotal("Quantity")%></TD>
<TD><%=g.Groups("CustomerID").ColTotal("Discount")%></TD>
<TD><%=g.Groups("CustomerID").ColTotal("ExtendedPrice")%></TD>
</TR>
<TR>
<TD colspan=9><HR></TD>
<TR>
<% end if
if g.groups("SalesPerson").footer then %>
<TR>
<TD colspan=9><HR size=4></TD>
<TR>
<% end if
g.movenext ' move next until EOF, just like with ADO recordsets
loop
%>
</Table>
Grand total for Extended price is: <%=g.CoLTotal("ExtendedPrice")%><BR>
</body>
</html>