Aggregate Functions

Tornado offers 4 type of Aggregate functions - Sum, Max, Min, Average and Count. Aggregate can be performed in both X and Y direction. User can specify the Sum function via Val() or Numeric. If Val() is used then 1 Box + 2 Boxes + 3 boxes = 6 while the Numeric option (default) will result 0.

Simple Aggregate SUM Function

<script language='vb' runat='server'>
Sub Page_Load(Source as Object, E as EventArgs)
   Dim ag As New Tornado.Z()
   With ag
      .dbQP="U=61| S=plain| PS=-1| Ni=None| D=Nwind.mdb"
      .dbGridMagicCell = "fi=0| tag=align=middle, fi=1| tag=align=right| mac=#1:c#, fi=2| tag=align=right"
      .dbSQL = "SELECT ProductId,UnitPrice,Quantity from [order details] where OrderID=10572"
      .dbAggregate = "(~^:)Fields=1^2|AggType=SUM|BottomTemp=<tr bgcolor=yellow align=right><td><b>Total </b></td><td align=right>[[1:c]]</td><td align=right>[[2:g]]</td>|CellFormat=Val"
      .dbTextHolder="Title=Order Details for Order 10572"
      .ASPdbNET
   End with
End Sub
</script>

In this example, the bottom Aggregate row template is defined as [[Field:Format]] for each field.

Advanced X-Y Aggregate Functions

<script language="vb" runat="server">
Sub Page_Load(Source as Object, E as EventArgs)
   Dim AggProp = "(~^:)fields=1^2^3^4|aggtype=SUM|bottomtemp=<tr align=right><td class=gh>Col Total</td><td class=CS>[[1:###]] Items</td><td class=CS>[[2:###.00]]</td><td class=CS>[[3:N]] Units</td><td class=CS>[[4:N]] Units</td><td class=CS align=right><b>[[GrandTotal:###,###.00]]</b></td><td class=CS colspan=3> </td></tr>|sumcolheader=<td class=gh><b>Row Total</b></td>|headertag=class=CS align=right|cellformat=Val"
   AggProp &= "~fields=1^2^3^4|aggtype=MAX|bottomtemp=<tr align=right><td class=gh>Col Max</td><td class=CS>[[1]]</td><td class=CS>[[2]]</td><td class=CS>[[3]]</td><td class=CS>[[4]]</td><td class=CS colspan=4> </td></tr>|sumcolheader=<td class=gh>Row Max</td>|headertag=class=CS align=right|cellformat=Val"
   AggProp &= "~fields=1^2^3^4|aggtype=MIN|bottomtemp=<tr align=right><td class=gh>Col Min</td><td class=CS>[[1]]</td><td class=CS>[[2]]</td><td class=CS>[[3]]</td><td class=CS>[[4]]</td><td class=CS colspan=4> </td></tr>|sumcolheader=<td class=gh><b>Row Min</b></td>|headertag=class=CS align=right|cellformat=Val"
   AggProp &= "~fields=1^2^3^4|aggtype=AVG|bottomtemp=<tr align=right><td class=gh>Col Avg</td><td class=CS>[[1:f]]</td><td class=CS>[[2:f]]</td><td class=CS>[[3:f]]</td><td class=CS>[[4:f]]</td><td class=CS colspan=4> </td></tr>|sumcolheader=<td class=gh>Row Avg</td>|headertag=class=CS align=right|cellformat=Val"
   AggProp &= "~fields=1^2^3^4|aggtype=COUNT|bottomtemp=<tr align=right><td class=gh>Col Count</td><td class=CS>[[1]]</td><td class=CS>[[2]]</td><td class=CS>[[3]]</td><td class=CS>[[4]]</td><td class=CS colspan=4> </td></tr>|cellformat=Val"
   Dim Agg As New Tornado.Z()
   With Agg
      .dbQP="U=33| S=33| PS=-1| D=Nwind.mdb| ni=none| Th=Tit=Aggregation Functions"
      .dbGridMagicCell = "fi=1|tag=align=right|fi=2|tag=align=right|fi=3|tag=align=right|fi=4|tag=align=right"
      .dbSQL = "Select ProductName, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder FROM Products"
      .dbAggregate = AggProp
      .ASPdbNET()
   End With
End Sub
</script>

The above code looks dense but actually most of the code is the display template of the bottom and right side aggregate data. This template enables you to customize the Aggregate row or column to your requirement.

Another variation of Aggregate function is the Export of the Aggregate data. Besides presenting the aggregate data with a grid, user may want to customize this data. For example, plot a chart of the aggregate data. The following is another version of the Remote Data example illustrating the export of the aggregate data to plot two charts.

Advanced Aggregate Data Export

<script language="vb" runat="server">
Sub Page_Load(Source as Object, E as EventArgs)
   Dim MyServer as string=Request.ServerVariables("SERVER_NAME")
   Dim GD As New Tornado.Getdata()
   Dim Rm As New Tornado.Z()
   With Rm
      .dbRemoteURL = "http://" & MyServer & "/tornado/DB/sales1.data|http://" & MyServer & "/tornado/DB/sales2.data"
      .ASPdbRemoteDataCollection("Sales")
'------- Process Consolidated Data - Plot a Cluster Chart --------
      .dbQP="U=95| S=7| M=Gc| PS=-1| Q=[[RemoteTable]]| Th=tit=Aggregate Data Export"
      .dbDSN = "Provider=Microsoft.Jet.OLEDB.4.0; Extended Properties=Text; Data Source=" & Server.MapPath("/tornado/scratch")
      .dbChartData = "dataset0Labels=#0#| _yValues=#1#,#2#,#3#,#4#,#5#,#6#,#7#,#8#,#9#,#10#,#11#,#12#"
      .dbChartParams = "ID=1| TitleString='Global Sales Report'| titleFont='Arial,14,1| Type=indColumn| Archieve=/tornado/Jars/| Width=700| Height=300| 3D=True| textLabelsOn=True| BackgroundColor=lightgray| valueLabelsOn=True| defaultFont=Arial,10,1|PlotAreaLeft=.1| PlotAreaRight=.95| outlineColor=black| dwellUseLabelString=true|dwellLabelsOn=true|dwellUseXValue=false|dwellYString=Sales:#"
      .dbGridTemplate = "<center><table border=0><tr valign=top><td align=center>[[Grid]]</td></tr><tr><td align=center><br>[[Chart]]</td></tr> </table></center>"
      .dbAggregate = "(~^:)Fields=1^2^3^4^5^6^7^8^9^10^11^12|AggType=SUM|bottomtemp=<tr align=right><td class=gh><b>Total Sales</b></td><td class=CH>[[1]]</td><td class=CH>[[2]]</td><td class=CH>[[3]]</td><td class=CH>[[4]]</td><td class=CH>[[5]]</td><td class=CH>[[6]]</td><td class=CH>[[7]]</td><td class=CH>[[8]]</td><td class=CH>[[9]]</td><td class=CH>[[10]]</td><td class=CH>[[11]]</td><td class=CH>[[12]]</td></tr>|cellformat=Val"
      .dbAggregateExportTemplate = "[[C1]],[[C2]],[[C3]],[[C4]],[[C5]],[[C6]],[[C7]],[[C8]],[[C9]],[[C10]],[[C11]],[[C12]]|[[R1]],[[R2]],[[R3]],[[R4]],[[R5]],[[R6]]"
      .ASPdbNET()
   End with
'----------- Continue to Plot a Pie Chart from the Aggregate ------------
   Dim Rm1 As New Tornado.Z()
   With Rm1
      .dbQP="U=96| S=7| Nh=t| M=Uc"
      Dim CR() As String = GD.Get_CRsum(95)
      .dbChartData = "dataset0yValues=" & CR(0)
      .dbChartParams = "ID=2| Type=26| Archieve=/tornado/Jars/| Width=400| Height=400| titleFont='Arial,14,1| TitleString='Which Month is the Most Profitable?'| 3D=True| valueLabelsOn=True| textLabelsOn=True| dataset0Labels=12Months|ExplodeSlice=" & Tornado.Chart.Find_Max(CR(0))
      .dbGridTemplate = "<center>[[Chart]]</center>"
      response.write(.ASPdbUserChart())
   End With
End Sub
</script>