Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, trackbacks - 64

My Links

Advertisement

News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.


Subscribe





Archives

Post Categories

Programming

Sports

SQL

ASP Report Class Northwind Sample

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>

 

 

Print | posted on Thursday, November 20, 2003 5:22 PM | Filed Under [ ASP ]

Feedback

Gravatar

# re: ASP Report Class Northwind Sample

Pl. tell me where is that Reportclass.asp is located.Is it system defined?
If possible send me a simple ASP report generation code along with a small Ms/access db.
regards to u all.
2/26/2004 12:48 AM | Prasun Ray Chaudhuri
Gravatar

# re: ASP Report Class Northwind Sample

nice work
8/9/2004 5:35 AM | frank
Gravatar

# re: ASP Report Class Northwind Sample

Thanks, this was very helpful. However, I am getting the following error when adding a section for grand totals for the report:
Microsoft VBScript runtime (0x800A01B6)
Object doesn't support this property or method: 'ColTotal'

I am adding grand totals after the Loop. It works if I am already in the report and hitting refresh. But if I come out of the report and re-launch I get the above error. Please advise
9/2/2005 11:34 AM | Moe Faqui
Gravatar

# re: ASP Report Class Northwind Sample

I am not necessarily getting an error, but it seems that when the code displays a large amount of records - it starts the loop over. Is this correct?
1/24/2006 1:12 PM | Wes
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET