Case Study #30 - Grid Aggregate functions

ASP-db has a group of properties to support aggregate operations ->

X.dbAggSQL - Define an SQL statement to obtain the aggregate values
X.dbAggMagicCell - Perform MagicCell on the total values (<B><I><HREF> etc…
X.dbUserLocalText - Define the Text description of the aggregate values (TOTAL, Average etc…)
X.dbAggNameTag - Define the TD tag of the Name (COLSPAN, ALIGN etc…)

Example #1 -

<!-- #include file=..\AspDB.Inc -->
<CENTER><h3><b>Aggregate function and display formatting Demo #1</b></h3></CENTER>
<%
Set X=Server.CreateObject("ASP.DB")
X.dbQuickProps="1;NWIND;;Grid;4,,,,,true;;;ACCESS;5;1;1"
X.dbGridTableTag="border=1"
X.dbFormTableTag="border=1"
X.dbGridDisplayFlds="0,1,2,3,4"
X.dbGridInc=100
X.dbSQL="SELECT * FROM [order details] Where ORDERID=10294"
X.dbMagicCell="4,,format=[0%]"
X.dbBlankText="&nbsp;"
X.dbAggSQL="SELECT '#skip#',sum(UnitPrice),sum(Quantity),'' FROM [order details] Where ORDERID=10294"
X.dbAggMagicCell="1,bgcolor=yellow,<B><I>#1#</I></B>;2,bgcolor=yellow,<B><I>#2#</I></B>"
s = "(~,)" & zAggName & ",<B>Sub Total</B>"
X.dbUserLocalText=s
X.dbAggNameTag="ColSPAN=2 align=left"
X.ASPdb
%>

The X.dbAggSQL property defines the SQL to obtain the aggregate values of the grid columns. Each field can be of a different type. The returned RS should return the same number of fields in order to map in to the grid. If the Column is non numeric. A blank string or ‘N/A’ can be used. It cannot be left blank. It is not necessary to use named fields like “sum(UnitPrice) as UnitPrice”. Using Field index in this case is more efficient.

The X.dbAggMagicCell property defines a MagicCell macro on the aggregate values. Use only attributes that applies.

The X.dbUserLocalText propery defines the Name text which include all the HTML attributes.

 The X.dbAggNameTag property places the Name Text. Normally, The Name text is placed at Column “0”. Note that position of column “0” depends on whether the grid has an index column. In this particular example, there is only one aggregate column at the end and Name Text can be placed next to the value by using a COLSPAN tag. This is only possible if the aggregate cells are adjacent to each other and has no empty cells in between. If all columns have aggregate values, then a grid index must exist in order for the Name Text to be placed. Otherwise, you must set Name Text (zAggName) to be blank.

Example #2 -

<!-- #include file=..\AspDB.Inc -->  
<CENTER><b>Aggregate function and display formatting Demo #2</b></CENTER>
<%
Set X=Server.CreateObject("ASP.DB")
X.dbQuickProps="1;NWIND;;grid;4;;;ACCESS;10;1;1"
X.dbSQL="SELECT * FROM orders where ShippedDate > #6/1/96#"
X.dbGridDisplayFlds="0,1,2,3,4,5,6,7"
X.dbMagicCell="Freight,align=right"
X.dbAggSQL="SELECT '#skip#','#skip#','#skip#','#skip#','#skip#','#skip#','#skip#',sum(Freight) FROM orders where ShippedDate > #6/1/96#"
X.dbAggMagicCell="7,bgcolor=yellow,<B><I>#7#</I></B>"
s = zAggName & ",<B>Total Freight Cost </B>"
X.dbUserLocalText=s
X.dbAggNameTag="ColSPAN=7 align=right"
X.ASPDB
%>

Notes -