How to create report with subtotals

How to create report with subtotals

Postby alextouch » Fri Jan 17, 2014 11:18 pm

Hi

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') ";


And in the report builder I create two sections, tt_forn_ragsoc (the supplier's name) and co_prog_titolo (the name of the project).

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
alextouch
 
Posts: 27
Joined: Tue Jun 05, 2012 10:10 pm
Location: Bologna, Italy

Re: How to create report with subtotals

Postby admin » Mon Jan 20, 2014 7:56 am

Alex,

nuBuilder can calculate them for you..
http://wiki.nubuilder.com/tiki-index.ph ... light=sum(

Steven
admin
Site Admin
 
Posts: 3079
Joined: Mon Jun 15, 2009 9:53 am

Re: How to create report with subtotals

Postby alextouch » Mon Jan 20, 2014 7:53 pm

Steven

GREAT!!!
Thank you very much, you've given me a big help!

Alex
alextouch
 
Posts: 27
Joined: Tue Jun 05, 2012 10:10 pm
Location: Bologna, Italy

Re: How to create report with subtotals

Postby massiws » Fri Feb 14, 2014 2:23 am

.
massiws
 
Posts: 503
Joined: Thu May 24, 2012 9:38 am
Location: Milan, Italy


Return to Report Writer



cron