Additional aggregate functions
Back again, I've been saving this one up :-)
All the examples in FastReport seem to assume that in a group footer all you will ever want is one of the true aggregation functions (count, sum etc). However in some cases (where the group may have extended over multiple pages) I want to use one or more "name" fields to identify the group summary. eg:
Totals for [Dataset."Name"]:
There are also instances (using Firebird selectable stored procedures) where the aggregation is already done on a returned value and all I want to do is place the last instance of this value in the footer (having not shown the progressive total in the group detail).
It is possible to just insert a field into the footer, and presumably this inserts the value of the field from the last seen record. However this is not reliable, it seems that at the last instance of the group the row has already gone and all I get is a blank field value. I am not sure if this is intentional or a bug. I am using the IBObjects dataset stuff, FR v3.0.9.
Working on the assumption that this was intentional behaviour I came up with three additional aggregations that can be useful. I have even coded these into FR and they work. These functions are:
- FIRST = return the value from the first row in the group
- LAST = return the value from the last row in the group
- ONLY = same as last, but also reports an error if it discovers that the value is not the same in all rows of the group.
I have found that the "only" aggregation is a useful way to check that the rows are being returned as expected. If the dataset has been setup incorrectly this can help identify problems, otherwise certain subtle situations can pass unnoticed.
If the people at FastReport are interested in these ideas I would be more than happy to email the source changes that I have made (as having them integrated will save me having to reintegrate in new releases). You could also check that I have done it correctly ;-)
I also noticed while making these changes that crosstab entries in storage use an enumeration (number) for the aggregation rather than the aggregation function name. This means that you must ensure that you do not insert new functions, only add them to the end. IMO it probably should use the aggregation name.
All the examples in FastReport seem to assume that in a group footer all you will ever want is one of the true aggregation functions (count, sum etc). However in some cases (where the group may have extended over multiple pages) I want to use one or more "name" fields to identify the group summary. eg:
Totals for [Dataset."Name"]:
There are also instances (using Firebird selectable stored procedures) where the aggregation is already done on a returned value and all I want to do is place the last instance of this value in the footer (having not shown the progressive total in the group detail).
It is possible to just insert a field into the footer, and presumably this inserts the value of the field from the last seen record. However this is not reliable, it seems that at the last instance of the group the row has already gone and all I get is a blank field value. I am not sure if this is intentional or a bug. I am using the IBObjects dataset stuff, FR v3.0.9.
Working on the assumption that this was intentional behaviour I came up with three additional aggregations that can be useful. I have even coded these into FR and they work. These functions are:
- FIRST = return the value from the first row in the group
- LAST = return the value from the last row in the group
- ONLY = same as last, but also reports an error if it discovers that the value is not the same in all rows of the group.
I have found that the "only" aggregation is a useful way to check that the rows are being returned as expected. If the dataset has been setup incorrectly this can help identify problems, otherwise certain subtle situations can pass unnoticed.
If the people at FastReport are interested in these ideas I would be more than happy to email the source changes that I have made (as having them integrated will save me having to reintegrate in new releases). You could also check that I have done it correctly ;-)
I also noticed while making these changes that crosstab entries in storage use an enumeration (number) for the aggregation rather than the aggregation function name. This means that you must ensure that you do not insert new functions, only add them to the end. IMO it probably should use the aggregation name.