(Note: this is definitely an ASP topic, but because it relates directly to report-writing and using SQL Server easily and efficiently, I wanted to put an article here.)

Regular old ASP with ADO works great for printing the contents of recordsets as web pages, and I know many of us out there still use these technologies.  A common need when outputing a recordset as HTML is to display what report writers often call “groups” on your web page, with headers and footers and subtotals for various columns in your recordset.

This can be tricky, because you need to keep track of when to print these group headers or footers, and you must also manually keep track with variables all the columns you wish to subtotal at all the different levels, and know when to reset them. 

After writing essentially the same code over and over for each of these pages, I finally created a very handy ASP class: The “Report” class.

The Report Class

This class makes it really easy to handle footers, headers and all of your subtotal (and running total) needs.  It also uses the GetRows() method of ADO to immediately return and close the entire recordset before processing.  But, it still allows you to use a “movenext” method, and to check for “EOF” so you don't need to worry about keeping track of the current row.  It also lets you reference columns by their names, just as you do with a recordset object.

The Report object works like this:

First, you open up the ADO recordset you wish to use.  Make sure it is sorted by the columns you wish to group on.  For example, if you wish to show headers and footers for each Office, Customer and Month, then be sure it is sorted by those 3 columns.

Once you have created an object as a new instance of the Report class, you must then initialize it by calling its init method, passing 3 parameters:

• The open recordset you wish to use
• an Array() of the column names you wish to group on
• an Array() of the column names you wish to subtotal

The init method does some initializing and calls the GetRows() method to bring in the entire recordset.  It then closes the recordset so the connection to the database can then be closed if you like.  Finally, it sets the current row in the Report object to the first row in the recordset.

Now, you can reference the following properties and methods of the Report object:

• EOF  -- returns TRUE if you are at the end of the recordset. 
• MoveNext -- This method moves to the next row of the recordset, and automatically keeps track of all totals and groups.
• Col(colname) -- returns the value in the column colname for the current row.  This is also the default property of the class, just like in ADO.  So if your report object is called Rpt, you can say Rpt(“Emp”) or Rpt.Col(“Emp”) to return the value of the Emp column.
• ColVal(colname) -- return the numeric value of the colname, or zero if it is not numeric or is null. 
• ColTotal(colname) -- returns the running total of colname, which must be one of the columns in the init method you chose to total on.  At the end of the report, this will return the grand total.
• Groups(groupname) -- this property returns a Group object for groupname, which also must have been specified in the init method.

The Group class has the following properties:

• Header -- returns true if you should output the Header for this group
• Footer -- returns true if you should output the Footer for this group
• ColTotal(colname) -- returns this group's running totals for the colname column.  Again, this must be a column specified during the init method as one you wish to subtotal. When footer is true, this returns the group totals for each column.

So, you just print headers for each group when Header is true, then print the details, then footers when Footer is true, and then movenext until you reach EOF.  All along the way you have access to running totals for all groups and for the recordset as a whole.

When you are done with the Report object, just set it to Nothing.  Remember, it doesn't keep an open recordset so as soon as you call the init method, you can close the connection to the database if you no longer need it. (The init method closes the recordset automatically).

That's basically it.  A typical ASP report with group headers, footers, and subtotals would look something like this:

dim Rpt  ' this will be the Report object

set r = conn.execute(”some SQL or proc”)

set Rpt = new Report

' here's the key method: init the object telling it which ADO recordset to use,

' which groups to keep track of, and which columns to subtotal:

Rpt.init r, Array(“Group1” ,“Group2”) , Array(“Val1” ,“Val2”)

' and we can close the connection now:

conn.close 

' then, just like with a recordset object, we have a main loop until EOF:

do while not Rpt.EOF

if rpt.Groups(“Group1”).Header then

      'output the Grp1 header here

end if

if rpt.Groups(“Group2”).Header then

      ' group 2 header

end if

' output details here.

' you can reference any column in your report as:

rpt(“AnyColumn”)

' you can output running totals for any group here as:

rpt.Groups(“any group”).ColTotal(“any column”)

' unlike with ADO, we now know if we need to print footers before

' moving to the next row.

if rpt.Groups(“Group2”).Footer then

     ' group 2 footer

     ' reference this groups subtotals as:

     rpt.Groups(“Group2”).ColTotal(“Val1”) 

end if

if rpt.Groups(“Group1”).Footer then

     ' group1 footer

     ' reference this groups subtotals as:

     rpt.Groups(“Group1”).ColTotal(“Val1”) 

end if

rpt.movenext   ' just like ADO

loop

' now, you can access grand totals as:

rpt.ColTotal(“Val1”)

rpt.ColTotal(“Val2”)

set rpt = nothing  ' clean it up

At the end of this article is the code for this class.  I hope you find it helpful.  Keep in mind the following:

• For the groups and the totals to work, the data must be accessed sequentially.  I.e., you can only movenext through the data until the EOF.
• Feel free to add more functionality as you see fit!  It is just an open-source script, so modify as you like
• The script may seem kind of long, but the bulk of it is the init method which is called just once.
• References to the column and group names are not case-sensitive.

Enjoy!  It will really make your ASP pages much shorter and very easy to read and maintain.  Hopefully, they will perform better as well since you can now use the the speed of the GetRows() method with the ease of use of the old movenext/EOF technique.

Please give me some feedback !  I have posted a sample ASP page using this class and the Northwind database, in case none of this is clear at all yet.  But if you do any complex report writing at all, this should really help you out. 

class Group

  public Header

  public Footer

  public ColTotal

end class

class Report

  private GrpArray

  public Groups

  private Vals

  private CurrRow

  private TotalRows

  private Columns

  public ColTotal

 public function Init(ADO_Recordset, ArrayOfGroupFields, ArrayOfTotalFields)

   dim i, n, f

   GrpArray = ArrayOfGroupFields

   set Columns = server.createobject("Scripting.Dictionary")

   columns.CompareMode = 2

   with ado_recordset

     for i=0 to .fields.count-1

       columns.Add .fields(i).name, i

     next

   end with

   if ADO_Recordset.EOF then

     CurrRow=1

     TotalRows = 0

     ADO_Recordset.close

     exit function

   end if

   Vals = ADO_Recordset.getRows

   ADO_Recordset.close

   set ADO_Recordset = nothing

   set groups = server.createobject("Scripting.Dictionary")

   groups.CompareMode = 2

   CurrRow = 0

   TotalRows = ubound(Vals,2)

   for i = 0 to ubound(GrpArray)

     set n = new Group

     n.Header = true

     n.Footer = isDifferent(grparray(i),1)

     set n.ColTotal = server.createobject("Scripting.Dictionary")

     for f = 0 to ubound(ArrayOftotalFields)

       n.ColTotal.add arrayOfTotalFields(f), colval(arrayofTotalFields(f))

     next

     groups.Add grparray(i), n

   next

   'finally, grand totals:

   set ColTotal = server.createobject("Scripting.Dictionary")

   Coltotal.CompareMode = 2

   for f = 0 to ubound(ArrayOftotalFields)

     ColTotal.add arrayOfTotalFields(f), colval(arrayofTotalFields(f))

   next

 end function

 public property Get Eof()

   Eof = (CurrRow > TotalRows)

 end property

 private function isDifferent(colname, RelativeRow)

   dim r

   r = currRow + RelativeRow

   if (r<0) or (r>TotalRows) then

     isDifferent = True

     exit function

   end if

   isDifferent = vals(columns(colname),currRow) <> vals(columns(colname),r)

 end function

 public function MoveNext()

   dim i, RestHeads, RestFoots, s, t

   currRow = currRow + 1

   if Eof then exit function

   RestHeads = false

   RestFoots = false

   for i = 0 to ubound(grpArray)

     s = grparray(i) 'the group column name

     with groups(s)

       if RestFoots or isDifferent(s,1) then

         restfoots = true

         .footer = true

       else

         .footer = false

       end if

       if RestHeads or isDifferent(s,-1) then

         restHeads = true

         .header = true

         for each t in .ColTotal

           .ColTotal(t) = ColVal(t)

         next

       else

         .header = false

         for each t in .ColTotal

           .ColTotal(t) = .coltotal(t) + ColVal(t)

         next

       end if

     end with

   next

   for each t in colTotal

     coltotal(t) = coltotal(t) + ColVal(t)

   next

 end function

 public default property Get Col(ColName)

   Col = Vals(Columns(colName),currRow)

 end property

 public property Get ColVal(ColName)

   dim t

   t = col(ColName)

   if isnumeric(t) then

     CoLVal = t

   else

     CoLVal = 0

   end if

 end property

end class