Using COUNT() in report

[icoso]

Is there a COUNT() function in a report ?

In this post from 2018 the admin stated they were going to be working on a COUNT() function.

https://forums.nubuilder.cloud/viewtopic. ... ilit=count

I also tried SUM() on some fee fields and that doesn't seem to work either? I placed an object that the FIELD NAME is: SUM(Fee1) and I placed it in the Page Footer, and Report Footer, and nothing happens. My report is grouped on Billing Date. ALl I get is the listing of line items. No sub totals, no totals. Except a printed date at the bottom.

Can ANYONE provide an example of How to create a simple report that I run for a date range, that is grouped by Billing Date: Displays: The Billing Date, Fee1, Fee2, Fee3, Total Amount, Cust #.
this report should be grouped by date and have subtotals Per date. That means for every day, there would be subtotal line on Fee1, Fee2, Fee3, Total Amount. and a COUNT of how many items are listed for that day.

This is a VERY basic & simple report that the report builder doesn't seem to be able to perform as-is.

[icoso]

Does anyone respond to these?

[Janusz]

For the report you can use some sub totals as in the example:

nureport.pdf

subsections.JPG

COUNT as far as I know is not available.

[icoso]

Why??? a COUNT function is one of the most basic functions of a reporting system for subtotals. Much like a Summary or Average function or even a running Total . Oh and pulling a report against a date range...

[Janusz]

I am just honestly saying what is available and what is not available - and I am unable to answer why some functionalities have not been implemented so far. We are constantly expanding the functionality of nuBuilder and will add additional report features to the list of possible future improvements.

[icoso]

Just thought maybe it was hidden somewhere or some special add-in out on Git Hub (I been looking at every possible codelib) to implement a basic COUNT function in reporting, since this was first brought up over 3 years ago in this post: https://forums.nubuilder.cloud/viewtopic. ... ilit=count and the admin said " I will get to adding count() soon but in the meantime..."

and then they go into creating temporary tables, new fields, new SQL statements, procedures, etc ... It really shouldn't be this difficult...

[apmuthu]

Workaround:

SUM(1)

Make a column / (calculated) field "1 as Cnt" in the SELECT part of the statement (nuSQL) or in a view used for the report and then SUM on that field (Cnt).

[icoso]

I would but apparently I cant get my SQL to run on this report....
I even tried this in my SQL statement thats attached to my report:

SELECT * FROM TaxCustomers WHERE TaxCustomers.tax_billdate BETWEEN concat(SUBSTR('02-10-2021', 7, 4),"-",SUBSTR('02-10-2021', 1, 5)) AND concat(SUBSTR('02-21-2021', 7, 4),"-",SUBSTR('02-21-2021', 1, 5))

I put the above in the SQL for my report and I get a completely blank screen. If I run the SQL in myPHPAdmin I get the two rows that I'm expecting to get retrieved. SO I KNOW my SQL statement is good and working.

If I change it to this in the SQL statement that is attached to my report: I dont get any data but at least I get the heading text and subtotal lines and the footer text. I dont get the data NOR the date fields on it.

TaxCustomers.tax_billdate BETWEEN concat(SUBSTR(#RPT_date1#, 7, 4),"-",SUBSTR(#RPT_date1#, 1, 5)) AND concat(SUBSTR(#RPT_date2#, 7, 4),"-",SUBSTR(#RPT_date2#, 1, 5))

SO what does that tell us? If I put a known good SQL statement that works in phpMYADmin to retrieve records and I hard code the dates in it, and I put it in the SQL statement that the report uses, I get absolutely nothing, its like the system cant even parse it. But if I put eh hash codes #RPT_date1# in place of the date strings, I at least get the headings and lines and footer text printed.

[apmuthu]

I expect your tax_billdate field to be a date field (MySQL stores it as yyyy-mm-dd). If so, try:

Code: Select all

SELECT TaxCustomers.*, 1 AS sumrec FROM TaxCustomers WHERE TaxCustomers.tax_billdate BETWEEN '2021-02-10' AND '2021-02-21';

[icoso]

#1. Why is it that this runs in myPHPAdmin just fine and it retreives the two records:

SELECT TaxCustomers.* FROM TaxCustomers WHERE TaxCustomers.tax_billdate BETWEEN concat(SUBSTR('02-10-2021', 7, 4),"-",SUBSTR('02-10-2021', 1, 5)) AND concat(SUBSTR('02-21-2021', 7, 4),"-",SUBSTR('02-21-2021', 1, 5));

but in the nuBuilder SQL it doesn't?

When I run this in nuBuilder:

SELECT TaxCustomers.* FROM TaxCustomers WHERE TaxCustomers.tax_billdate BETWEEN '2021-02-10' AND '2021-02-21';

It actually works! I get the report The Concat Statements in the 1st SQL statement return the exact same things.

#2. How do I get the SQL to read the date fields off my form when I click the Run button? (see the picture of the form that the report calls and opens that I uploaded previously in other post) I added the code:

nuSetProperty('RPT_date1', date1);
nuSetProperty('RPT_date2', date2);

to the Custom Code of the form to move the date1 & date2 fields to the fields being used on the report and that I tried to use in the SQL in place of the dates? I even tried changing my SQL to be:

SELECT TaxCustomers.* FROM TaxCustomers WHERE TaxCustomers.tax_billdate BETWEEN '#RPT_date1#' AND '#RPT_date2';

And I get no data, just the headings and lines on my report. SO somehow the date1 & date2 fields on the form are not getting passed to the SQL. What code am I missing and Where should I put it?