I have this scenario: I have invoices where I can select the supplier and a project linked to that purchase.
I want to create a report that groups invoices by suppliers then by projects, e.g.:
-----------
Supplier: SONY
Project: Project1
Invoice1 € 10,00
Invoice2 € 15,00
...
Sum of the totals of the invoices for this section: € 25,00
Supplier: SONY
Project: Project2
Invoice5 € 44,00
...
Sum of the totals of the invoices for this section: € 44,00
Supplier: PHILIPS
Project: Project1
Invoice 11 € 55,00
...
Sum of the totals of the invoices for this section: € 55,00
and so on, then:
GRAND TOTAL € 124,00
-----------
I was able to group by suppliers and projects using section headers/footers, but I don't know how to obtain subtotals of each section.
This is the report query:
Code: Select all
$sql="CREATE TABLE #dataTable# SELECT co_acqu_data, co_acqu_numfat, co_acqu_importo, ";
$sql.="co_acqu_iva, co_acqu_totale, co_acqu_scadenza, tt_forn_ragsoc, co_prog_titolo ";
$sql.="FROM co_acquisti JOIN tt_fornitori ON co_acquisti.co_acqu_idfor = tt_fornitori.tt_forn_id ";
$sql.="JOIN co_progetti ON co_acquisti.co_acqu_idprog = co_progetti.co_prog_id ";
$sql.="WHERE co_acqu_scadenza BETWEEN STR_TO_DATE('$scaddal', '%d-%m-%Y') ";
$sql.="AND STR_TO_DATE('$scadal', '%d-%m-%Y') ";
$sql.="AND co_acqu_data BETWEEN STR_TO_DATE('$acqudal', '%d-%m-%Y') ";
$sql.="AND STR_TO_DATE('$acqual', '%d-%m-%Y') ";
There is a way in that nuBuilder can calculate subtotals for each section?
Or I have to do a recursive query so that I can calculate subtotals for each pair project/supplier?
Thank you
Alex