Adding COUNTs and SUMs to groups

[mikep345678]

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

[admin]

Mike,

Good question.

Get the latest from Github first, but I have just added this to the Wiki...
http://wiki.nubuilder.net/nubuilderfort ... #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

Which will give you this...

pdf.PNG

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

Steven

[mikep345678]

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

[admin]

Mike,

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

http://wiki.nubuilder.net/nubuilderfort ... orts#Table

Steven

[mikep345678]

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

[admin]

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

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

Steven

[mikep345678]

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

[admin]

Enjoy!