Aggregate functions In most cases, group reports should display some resulting information (such as: "total of a group," "number of group elements," etc). There are the so-called aggregate functions in FastReport designed for this purpose. With their help, one can count up some function of a defined value according to data span. Below is the list of aggregate functions:
SUM | Returns the total of the expression
MIN | Returns the minimal value of the expression
MAX | Returns the maximal value if the expression
AVG | Returns the average value of the expression
COUNT | Returns the number of lines in the data span
The syntax of all aggregate functions (except COUNT) is the following (let us examine it using the example of the "SUM" function): SUM(expression, band, flags) SUM(expression, band) SUM(expression) The parameters assignment is the following: expression – the expression, the value of which is to be handled band – the name of data band, on which handling of values will be performed flags – the bit field, which can contain the following values and their combinations 1 – consider the invisible band 2 – accumulate the value (do not reset the value during next displaying) As you can see, an expression is the only obligatory parameter; all the rest can be skipped. Nevertheless, it is recommended to always use band parameters, since it would allow to avoid mistakes. The "COUNT" function has the following syntax: COUNT(band, flags) COUNT(band) The parameters assignment is similar to the one described above. There is a general rule for all aggregate functions: a function can be counted only for the data-band and displayed only in the band’s footer (the following bands refer to the latter: footer, page footer, group footer, column footer, and report footer). How do aggregate functions work? We will examine it using our example of report with groups. Let us add new elements to the report: The Group."ItemsTotal" field on the data-band will display the current order total. We place the "Text" object, containing the aggregate SUM call, to the group footer. It will display the total of all orders placed by the given client. Starting the report on accomplishment and using a calculator, we can make sure that everything works: So, how do the aggregate functions work? Before constructing a report, FastReport scans the "Text" objects’ contents in order to find the aggregate functions. The functions found will be anchored to the corresponding data-bands (in our example, the "SUM" function in anchored to the "MasterData1" band). During construction of a report (when the data-band is displayed) the value of the aggregate functions linked to it is counted up. In our case, the "Group."ItemsTotal"" field’s values are accumulated. After outputting a group footer (the one where the accumulated value of the aggregate function is displayed) the function value is reset, and the cycle is repeated for the next groups. Now we should comment the purpose of the "Flags" parameter in the aggregate functions. In some reports, some of data-bands (or all of them) may be hidden, however, we might anyway need to count a value of the aggregate function considering all databands. So, in our example, the "Visible" property of the data-band can be disabled; after that it will stop displaying. To count a total on the hidden data-band, let us add the third parameter to the call of the function: [SUM(<Group."ItemsTotal">,MasterData1,1)] It will give us a report, which would look as follows: The "Flags = 2" parameter value allows to avoid reset of the accumulated function value right after it is displayed. This allows to receive the so-called "running total". Let us update the call of the function: [SUM(>Group."ItemsTotal">,MasterData1,3)] The "3" value is a bit combination of "1" and "2," which means that we need to take into consideration the invisible bands without resetting the total. As a result, we have: