Use Array Functions For Aggregates
A lot of us in our ColdFusion jobs create reports with aggregate numbers in them. Most of the time, you can pull the first level of the aggregates from the database. Then, often there are secondary aggregates that are usually based on the first set.
A common example would be a report that has counts of sales by salesperson. This report would usually have a total of sales at the bottom. The way that I have often accomplished this (and seen most people do it) is to set a "salestotal" variable before looping to create the rows. Then, while looping, the subtotals are added to the "salestotal" variable.
This above method works just fine. However, the below example is cleaner and easier. Take a look!
SalesQry = QueryNew('');
SalesPersonArr = ['Ben Forta','Chris Phillips','Bob Smith'];
SalesCountArr = ['1337','999','45'];
QueryAddColumn(SalesQry,'SalesPerson','VarChar',SalesPersonArr);
QueryAddColumn(SalesQry,'SalesCount','VarChar',SalesCountArr);
</cfscript>
<cfoutput>
<table cellpadding="2" border="1">
<thead>
<tr>
<th>Sales Person</th><th>Month-To-Date Sales</th>
</tr>
</thead>
<tbody>
<cfloop query="SalesQry">
<tr>
<td>#SalesQry.SalesPerson#</td><td>#SalesQry.SalesCount#</td>
</tr>
</cfloop>
</tbody>
<tfoot>
<tr>
<td><strong>Total Sales:</strong></td><td>#ArraySum(SalesQry['SalesCount'])#</td>
</tr>
<tr>
<td><strong>Average Sales:</strong></td><td>#Round(ArrayAvg(SalesQry['SalesCount']))#</td>
</tr>
</tfoot>
</table>
</cfoutput>
I hope you noticed that the aggregates were done with code like this "#ArraySum(SalesQry['SalesCount'])#". No more counter variables or extra queries. Just pure sweet ColdFusion.
OK. Now back to work making those reports! And don't forget the TPS cover sheets.
SELECT SalesPerson, SUM(SalesCount) AS TotalSales, AVG(SalesCount) AS AvgSales
FROM SalesData
GROUP BY SalesPerson
WITH ROLLUP
This is a nice approach for those occasions when doing a rollup in the database may not be possible, or when your adding rollups to an existing report.
I've used the array functions as well in reporting. They are very nice as long as you can count on a number in each element of the array. If not, Error.
The array functions are great when supporting different databases. In that case, database platform specific extensions can't be used effectively.
DW
Thanks for the tip.
I gave WITH ROLLUP a quick try (after reading the Books Online docs).
Works great!
I'll keep that in mind next time I'm doing a reporting project.