Creating report by Date Range.

[icoso]

I created a report using the FAST Report Builder. I spent hours grouping, aligning and doing creating subtotals by reading post after post after post ( and trying to read the wiki and the 4.5 manual on howto create a report. I finally got my report built and ALL I want to do is now generate this report by selecting some date range. One would think the intuitively this would be easily done perhaps by using the Groupings or since MOST reports of ANY type ar done by date ranges, that one would think something would be built-in to this "No Code" database system that is easier and better than Access where all I have to do is specify the date field form my table that Im running this report against and the system would run a SIMPLE select on that date range.

BUT NO! There's nothing intuitive about this. So I search and search some more, and read and I find a post that Says oh you have to follow the manual to try to create a selection FORM to have it run the report.
https://forums.nubuilder.cloud/viewtopic.php?f=21&t=10065

So I look in v4.5 of the manual and THERE'S NOTHING about how to do this. I figured they must mean the version 3.x of the manual so there I find it and I read it and go step by step through it and it has an EXAMPLE of pulling a report ON AN ORGANIZATION NAME - NOT A DATE RANGE!!! and it uses the zzzzzzzzzzzsys_setup table because well the form has to link to some table.... Now I have to fill out the Browse screen but according to the manual the SQL on that screen should already be completed and I "do not need to fill out anything else on that screen." WRONG!!!! I do and kev1n referred to it in the post above, but his diagram showed the DISPLAY field as blank, BUT that can't be left blank so I selected a field from the table zzzzzzzzzzzsys_setup and saved it.

NOW back to the Version 3 manual and I have to create an OBJECT. BUT the manual says to select a PREVIOUSLY made object in their example. It doesn't tell you how to create an object or how that object will tie into your Report, but just create a Select Object by cloning a previously created Select Object and change some of fields. I tried that and followed the figures 81, 82, 83 .... BUT of course since this is version 3.x of the manual the screens are NOT the same as version 4.5 so there's different fields, But I went ahead and just changed what the manual said to do. I left the ID fieldalone, I guess it has NOTHING to do with the report Im about to use all this on.

Now to build the selection SQL page 69 of the VERSION 3 manual NOT the 4.5 version. OH GREAT! back to the selecting an organization in my select statement NOT how to select a date range! But I know how to type a SQL statement to select a date range. HOW DO I DO THIS FROM A FORM!!!!!?!??!?!?!?!?!?!?!?!?!??? The manual doesn't give you clue on how to create a form put a couple of date range fields on it and then REFER TO THOSE in an SQL statement and then connect ALL this to a simple report!

SO Kev1n says read this topic! https://forums.nubuilder.cloud/viewtopic. ... 17&p=21705

Which I do but this again takes you NOWHERE useful! Kev1n says to either create a launch form and refers you to the wiki (which is NO help at ALL) or "Run the report on a button click and show it in an iframe/new tab." Again no help to figuring out out to run a report using a date range which is how MOST REPORTS are generated in ANY database system.

ALL I WANT TO DO is run a simple report based on a date range of the cust_billdate field in my table. Why is this so convoluted?

[icoso]

This is not working:

Here is my SQL statement:
SELECT
*
FROM
Customers
WHERE
((Customers.cus_billdate >= concat(SUBSTR(#date1#, 7, 4),"-",SUBSTR(#date1#, 1, 5))) && (Customers.cus_billdate <= concat(SUBSTR(#date2#, 7, 4),"-",SUBSTR(#date2#, 1, 5))) )

I tested this SQL in myPHPAdmin replacing the #date1# fields with a date that looked like 02-01-2021 and date2 = 02-23-2021 adn it performed exactly as expected. It returned my records.

I created a form and the ONLY objects on that form are two date fields: date1 and date2

Example-DateForm1.png

Example-DateForm2.png

My report references the SQL and the Form as follows:

Example-DateForm3.png

It returns Nothing. I cloned this report form my original report in which I run it and it returns all of the records from the table and performs beautifully. But when I cloned it and attached that to the SQL and the form as per following the pseudo manual. it returns nothing on the report.

Any idea's whats wrong????

[Janusz]

Hi,
Please find enclosed short video / working example how to create a report with specific date range.

https://youtu.be/hrhSQsGuExc

In your code please check if date formats are coherent between nuBuilder and MariaDB.
I would suggest to start first with very basic case and if it works then to extend to final query.

[icoso]

Hi,
In your code please check if date formats are coherent between nuBuilder and MariaDB.
I would suggest to start first with very basic case and if it works then to extend to final query.

Thanks Janusz!

The report function does not appear to be reading the date1 and date2 fields from my form. I added them as objects on my report and they dont print. Is there something I have to do to set these so that nuBuilder reads them?

I tested the SQL in myPHPadmin on my host replacing the #date1# fields with a date that looked like 02-01-2021 and date2 = 02-23-2021 and it performed exactly as expected. It returned my records.

So I know my SQL statement is correct. In the database table MySQL saves the dates as 2021-02-15 YYY-MM-DD so most people around here use the format MM-DD-YYYY on my form I have them enter it that way using a mask to ensure the data is entered MM-DD-YYYY and then I use my SQL statement to parse the fields appropriately. It works as tested. Its just that the process I laid out for creating the report and then using a form and that SQL statement doesn't work.

Im getting really frustrated by this system and its quirks and losing hope that its going to be a viable solution for a really basic function that I think I probably could have just written the interface for myself. This whole report SQL for a simple date range I could have done in a PHP file myself in a couple of hours. This is now going on a couple of solid days of work to get this or each of anything with this system working.

What is the form with all those buttons on it in your video? How did you get that? When I run the report it calls the form that I created that looks like:

ReportScreen1.png

I only have a Run button that shows up. After I select my Dates I click Run, then the report runs and nothing shows up. Even those dates that I entered #date1# #date2# do not show up on the report.

[Janusz]

the code from example to read values from objects:

Code: Select all

var start_date=$('#pdf_start_date').val();
var end_date=$('#pdf_end_date').val();

if (start_date==='') {start_date='2000-01-01';}
if (end_date==='')   {end_date='2099-01-01';}

if ((new Date(start_date)-new Date(end_date))>0) {alert('Wrong date range!'); return;}

nuSetProperty('PAY_PDF_START', start_date);
nuSetProperty('PAY_PDF_END', end_date);

nuRunReport('FR1');

but initially use just only this (adjust to your case):

Code: Select all

nuSetProperty('PAY_PDF_START', '2000-01-01');
nuSetProperty('PAY_PDF_END', '2099-01-01');

nuRunReport('FR1');

and in the report SQL use them as:

Code: Select all

.... WHERE pay_until_date BETWEEN '#PAY_PDF_START#' AND  '#PAY_PDF_END#'

use as well console.log in JS code to see the values read from the objects.

[icoso]

Why do I need this? This is not what the manual or wiki for the reports explain how to do this. There is nothing in the manuals, wiki, or anything that Ive come across that says I have to use coding to take the fields values tht are on the form and assign them to another field that's on the report. the fields on the form are date1 and date2. On the report and in my SQL Im referring to them as #date1# #date2#. Why do i have to have another layer of code that assigns them to some other memory (temp) variable.

Where do I put this code to make them assign it? In the Custom Code of the Form?

What is console.log and how do I use that? Where? in what case?

Why do I have to use: nuRunReport('FR1'); Im running the report from my Home -> Run Report button. I select the report CRTest and run it, then it displays the form with the RUN button on it, then I enter the dates and click RUN. Why s there seeming another form with buttons on it in your example? For what reason?

This is the MOST BASIC case that has become so difficult to try to implement and nothing is intuitive about it. I created a Flat table. WIth customer info in it and some other data to track tax return filings and payments. Its a flat table. I created a report to run against that flat table,there are NO relational objects AT ALL!. As I said above, MY report runs fine against this table but it runs against all the records. I want to run it against a date range (something that the VAST majority or reports for ANY database system are run for) I have to create multiple forms, and SQL statements, and develop code and temporary variables, etc.... Why? This makes no sense to be this difficult to run a report against a simple date range?

[icoso]

Heres what my report looks like. Not too difficult and IT WORKS, if I run it against the entire table without all the links to the forms and SQL then I get nothing.

ReportScreen2.png

I figured out how to do Summary fields, and modify the headings for each group, etc... But there is NO COUNT function (another basic of all reporting that isn't part of this system). This and running a report against a date range requires So much more crossed coding and linking to other things that its hardly worth it. I could use Crystal Reports or even Excel with MS Query against my MySQL database and have it done faster than this.

[icoso]

Ive tried adding the

var start_date=$('#pdf_start_date').val();
var end_date=$('#pdf_end_date').val();

if (start_date==='') {start_date='2000-01-01';}
if (end_date==='') {end_date='2099-01-01';}

if ((new Date(start_date)-new Date(end_date))>0) {alert('Wrong date range!'); return;}

nuSetProperty('PAY_PDF_START', start_date);
nuSetProperty('PAY_PDF_END', end_date);

code to my Custom code on my Form. and changing the fields appropriately for my field names and report names. AND updating my SQL and it still doesn't work. I get no results.

I'm giving up... I was really hoping this system would be something very useful and it can be, but there is just too much it that is not documented, explained, or intuitively designed for someone who has NEVER used this system before. The wiki states what a function does, but doesn't give any real examples that helps explain HOW it does it. The JavaScript and PHP functions list does the same thing. They explain what they do, but not HOW to use them or HOW they interact with the system to make them functional. There's no real world examples. The manuals are outdated, when the whole reporting module is based on a version 3.x when the system is on version 4.5, its woefully inadequate. The Codelib on Github has been a little useful, but again the functions out there don't explain HOW it works, or WHY it works or HOW its implemented, So there's no learning to it, you either use it and it works or not.

I've done a lot of coding and development over the last few decades and never have had this much trouble trying to figure something out.

[apmuthu]

The Wiki is a work in progress. v3 is more mature having been operational for quite some time now. The v4.5 repo was a work in progress and quite a bit of DB schema changes were done initially but it has been stable for quite a while now. That was why I had created the v4 update repo so as to freeze the DB schema and focus on design and deployment till v4.5 stabilised it's DB schema and codebase. v4.5 has many new features and enhancements that does make it worthwhile with update caveats and is now considered stable and all new users are encouraged to use it and existing users upgrade after backing up code and sql first.

Every time design in v4.5 is done, make sure you capture the records (INSERTS and user changes to default records) specific to your application is a separate application backup sql file. When updating (not upgrading from 4) to latest v4.5 codebase, DROP all the zzzsys_% tables. They will get re-created with all changes in the nubuilder4.sql file. The user tables with their records are left untouched. Then the application backup sql file can be restored.

Keep in mind that the said application backup sql file may have to be tweaked to accomodate the schema changes that may have occurred since the last v4.5 install/update was done.

The normal DB update button in nuBuilder v4.5 is supposed to achieve all this but when the schema itself changes, the DB ALTER statements may not answer all intermediate installs/updates of v4.5 builds.

Console.log is a skill essential for any nuBuilder user. There may be some youtube videos on this topic that is not specific to nuBuilder but would suffice.

[icoso]

Mine is a brand new install as of 2/5/2021. I origianlly installed version 4.5. I did not even find this system until I first installed it. I never used version 3, 4, or any other prior versions. I downloaded and installed version 4.5. That was the FIRST time I used it. Every file I have updated, nucommon.js, nucommon.php, nuform.js, nudata.php as per kev1n I have made a backup copy of the original.

Most of what I have figured out about the DOM for this system and the field IDs and class ID's has been from using the built-in FireFox Element inspector and the use of the console and debugger tabs therein. I still can't figure out why the date fields are not being passed from my form to the SQL that is used to run the report. IS the code supposed to attached to the RUN button that appears at the top of the screen? How do I do that? That is not a button that put on there. The system did it.