Averages in Report Builder
Posted: Tue Dec 20, 2011 5:25 pm
Hi,
I'm working on a report that uses a listbox (as in invoice list on sample debtors). As in the invoice list example I use the listbox to select a number of categories and create a data table with a join for all records for a specific category. I then want to print some summary statistics for the combined records that belong to every category. This sort of works the way I want in that I can get aggregate sums for the fields within a column. However, I need to compute averages, and it looks like the report builder will only compute sums?
I played around with the invoice list report, and if I include a field AVG(tra_total) on the page footer it remains blank, even though SUM(tra_total) happily sums the total for all transactions.
In my own report I can retrieve the total sum and the number of items I need to compute an average over, but if I include a field sum(amount) / sum(count) I get an error message.
Is there any way to display an average within the report builder, or do I need to do this within the PHP code? If the latter, how do I do that when working with a listbox? The idea is that the listbox will allow me to select the categories over which I want to group the records. Then I want to compute averages for every category. Thus, in terms of the invoice list example, I'd want to be able to print all invoices or transactions for the customer(s) selected, and then compute (for instance) the average amount per invoice for each customer.
Cheers,
Dan
I'm working on a report that uses a listbox (as in invoice list on sample debtors). As in the invoice list example I use the listbox to select a number of categories and create a data table with a join for all records for a specific category. I then want to print some summary statistics for the combined records that belong to every category. This sort of works the way I want in that I can get aggregate sums for the fields within a column. However, I need to compute averages, and it looks like the report builder will only compute sums?
I played around with the invoice list report, and if I include a field AVG(tra_total) on the page footer it remains blank, even though SUM(tra_total) happily sums the total for all transactions.
In my own report I can retrieve the total sum and the number of items I need to compute an average over, but if I include a field sum(amount) / sum(count) I get an error message.
Is there any way to display an average within the report builder, or do I need to do this within the PHP code? If the latter, how do I do that when working with a listbox? The idea is that the listbox will allow me to select the categories over which I want to group the records. Then I want to compute averages for every category. Thus, in terms of the invoice list example, I'd want to be able to print all invoices or transactions for the customer(s) selected, and then compute (for instance) the average amount per invoice for each customer.
Cheers,
Dan