Welcome to the nuBuilder forums!

Please register and login to view forums and other content only available to registered users.

How to create report with subtotals

Locked
alextouch
Posts: 38
Joined: Tue Jun 05, 2012 2:40 pm
Location: Bologna, Italy
Contact:

How to create report with subtotals

Unread post by alextouch »

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
admin
Site Admin
Posts: 2778
Joined: Mon Jun 15, 2009 2:23 am
nuBuilder Version: 4.5

Re: How to create report with subtotals

Unread post by admin »

Alex,

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

Steven
alextouch
Posts: 38
Joined: Tue Jun 05, 2012 2:40 pm
Location: Bologna, Italy
Contact:

Re: How to create report with subtotals

Unread post by alextouch »

Steven

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

Alex
massiws
Posts: 503
Joined: Thu May 24, 2012 2:08 am
Location: Milan, Italy
Contact:

Re: How to create report with subtotals

Unread post by massiws »

.
Locked