Adding COUNTs and SUMs to groups

Questions related to nuBuilder Forte Reports and the Report Builder.

Adding COUNTs and SUMs to groups

Postby mikep345678 » Sat Jan 20, 2018 9:00 am

The Fast Reports builder includes a "sum" checkbox for each field. I need to SUM some transaction totals, but for each Customer as well as total.

It does not work to create a group header and footer for each customer, then to clone the report "Sum" object and move the clone to the Customer footer.

I'd also like to report a Count of the number of transactions for each customer.

Is there a way to do these things in Reports?


Thank you!
Mike
mikep345678
 
Posts: 14
Joined: Tue Jan 16, 2018 4:23 am

Re: Adding COUNTs and SUMs to groups

Postby admin » Sat Jan 20, 2018 4:15 pm

Mike,

Good question.

Get the latest from Github first, but I have just added this to the Wiki...
http://wiki.nubuilder.net/nubuilderforte/index.php/Report_Designer#Functions

You can use sum() or average(). - I will get to adding count() soon but in the meantime...

To simulate count() you will need to create a field for each record with the value of 1.

I would do it in a Procedure, rather than trying to make changes in the SQL Builder.

(Because if you try to manually change the SQL, the SQL Builder creates, it will get written over as soon as you do anything like drag a table.)

The Procedure could look like this...

Code: Select all

$s  = "

SELECT
    sta_last_name,
    sta_initials,
    sta_games,
    sta_runs,
    sta_captain,
    CONCAT(sta_last_name, ',', sta_initials) AS full_name,
    1 AS player

FROM
    stats

";

nuRunQuery("CREATE TABLE #TABLE_ID# $s");



Then you can do something like this...

report_functions3.PNG
report_functions3.PNG (43.79 KiB) Viewed 2796 times


Which will give you this...

pdf.PNG
pdf.PNG (41.03 KiB) Viewed 2805 times


ps. I hope no cricket tragic gets upset at me, I made up this grouping of captains.

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

Re: Adding COUNTs and SUMs to groups

Postby mikep345678 » Sun Jan 21, 2018 3:57 am

Steven,

First off: thank you for being so responsive to forum posts and so quick to implement suggestions and fixes!

What would be the most typical way to run this procedure before pulling the report? I can imagine running the procedure manually then running the report, but where does the table name come for nuRunQuery("CREATE TABLE #TABLE_ID# $s");?


Thank you!
Mike
mikep345678
 
Posts: 14
Joined: Tue Jan 16, 2018 4:23 am

Re: Adding COUNTs and SUMs to groups

Postby admin » Sun Jan 21, 2018 5:12 am

Mike,

I've just updated the wiki, let me know me if this answers your question.

http://wiki.nubuilder.net/nubuilderforte/index.php/Reports#Table

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

Re: Adding COUNTs and SUMs to groups

Postby mikep345678 » Sun Jan 21, 2018 8:18 am

I almost understand, I think... : )

So, if I create a procedure to create a temp table-- how do I base a report on that temporary table? Would I somehow have to create the temp table manually if i wanted to do a Fast Report?

How could I have multiple reports with different procedure-generated SQL statements?


Thank you for your patience and support!


Mike
mikep345678
 
Posts: 14
Joined: Tue Jan 16, 2018 4:23 am

Re: Adding COUNTs and SUMs to groups

Postby admin » Sun Jan 21, 2018 10:56 am

Mike,

You said...

Would I somehow have to create the temp table manually if i wanted to do a Fast Report?


Only if what you want to you display is more complicated than a single table (nuTABLE) and more complicated than a SELECT Query built by the SQL Builder (nuSQL).


I'm not sure what you meant here but I had a go at explaining what I thought you meant.

How could I have multiple reports with different procedure-generated SQL statements?


Each Report requires a Table, which could be a Procedure (or nuTable or nuSQL).

Which you can use on as many reports as you like.

report_tables.png
report_tables.png (31.94 KiB) Viewed 2787 times


BTW Procedures will only appear in this list if they contain the string #TABLE_ID#.

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

Re: Adding COUNTs and SUMs to groups

Postby mikep345678 » Sun Jan 21, 2018 2:10 pm

Ahh-- now I understand: what I was missing was that #TABLE_ID# is "filled in" as the name the procedure given... Clear as day now that I see it... : )

So, am now updating my nb install with the newest git pull, then will head off to exploit my new understanding... : )


Thank you,
Mike
mikep345678
 
Posts: 14
Joined: Tue Jan 16, 2018 4:23 am

Re: Adding COUNTs and SUMs to groups

Postby admin » Sun Jan 21, 2018 3:03 pm

Enjoy!
admin
Site Admin
 
Posts: 3078
Joined: Mon Jun 15, 2009 9:53 am


Return to Reports