How to create a report with a date range and subsections.

[Janusz]

Hi,
Here in the video you can find basic information how to create report with data from specific date range and how to make sub groups on the reports.

1st video - report with date range
https://youtu.be/hrhSQsGuExc

2nd video - report with sub sections
https://youtu.be/Z3Vo-vs6iDY

3rd video - hash cookies and reports
https://youtu.be/QegbJKt19nQ

[apmuthu]

Thanks. Wikied.

[icoso]

Your first video shows that by placing the hash cookies of the date fields from the search form after they are assigned in the PHP, should be available on the report. They are not. I can get the SQL to use the has cookies to run the SQL for the report, by they do not display on my report.

Please look at my comments and pictures in post : https://forums.nubuilder.cloud/viewtopic.php?f=21&t=10842

How can I get these HAsh Cookies to print on my report?

[Janusz]

Hi,
For the hash cookies from the first video example it's constructed in the following way.

Report is generated by clicking on button so first we need to define in the object: Input / Button / Custome code: onclick

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');

1st part of the code is to read the current values of 2 fields where I enter the date:

Code: Select all

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

next I make sure that it will always work, may happen that someone will just enter one field only or why to force people to enter both days if someone is interested only with start date or end date or if anyone wants the whole report he is not entering anything (then dates are assigned like from year 2000 to 2099):

Code: Select all

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;}

after if I am sure that my data with dates are properly declared I define the hash cookies which can be available in SQL (the same way as to define the hash cookies for PHP procedures):

Code: Select all

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

and next just to call the report with:

Code: Select all

nuRunReport('FR1');

the query used for the report is following and is referring to the hash cookies defined just above:

Code: Select all

SELECT *,'1' as counter FROM payments LEFT JOIN recipient ON pay_for_whome=recipient_id LEFT JOIN charge ON pay_for_what=charge_id
WHERE pay_until_date BETWEEN '#PAY_PDF_START#' AND  '#PAY_PDF_END#'

You can use as well the Run function to generate report but then you will not have possibility to process the date data. Me personally prefer the above solution but of course you can try other possibilities.
When you are on the report you can refer and display directly the hash cookies in object type Label for example:

reporthash.JPG

Additional info: if you generate report from specific Edit form you can refer to the data objects from that form directly without the need to use nuSetProperty. You can use directly in your SQL code the hashed names like: #objectID#.

[icoso]

There it is in the last picture..... the '#PAY_PDF_START#' AND '#PAY_PDF_END#' hash code on the reports are LABELS. I had them listed as FIELDS on my report. I changed them to labels and they printed. I just assumed that since they are Hash Cookies they are then part of the $nudata record and as such are part of the field data, not labels. In your video it doesn't actually open those fields on the report to be able to see that they are labels.

Its the little things...

Thanks for clarifying...

It still won't run the original way I had the report set up... Where I run the report from the Actual report forms, not the launch forms...

[Janusz]

Concerning the original way you probably mean the Run. Please find enclosed short video how to use Run. In fact in hash you can refer to any input field on the current form - can be from launch or edit form.
https://drive.google.com/file/d/15e4Y79 ... sp=sharing
(to have good quality of video make sure that the resolution on you player is set to HD and the best in full screen)
(tip: to open button properties in this video I click on it with middle button - so then I have object properties opened not just the report generation - what would be the case with left button click)

[icoso]

Thanks, Janusz, the video didn't appear to have any audio, but no. I documented what I mean in this post. https://forums.nubuilder.cloud/viewtopic.php?f=21&t=10842

As an overview I would think intuitively that I can use the Builders, Fast Report or Report builder to create a report, then from the Home -> Setup menu there is a button to Run a Report. I click it, choose the report I created in the Builders menu, and Cick it and it opens and then I click the Run button at the top and it runs. For the first report I created It runs on the WHOLE Table, all the records. Then I thought well how do I specify a date range. There's nothing in the Report builder that allows you to do that. I researched a lot, read the manuals, watched the videos, etc. and documented everything in that post.

The ONLY way that I could get the report to actually run and use the date fields is if I ran it from a form I created and had a button on that form that ran the report, and not by Running it from the Setup menu then the Run a Report button. They are two different ways of running a report.

[Janusz]

Normally in production the Setup/RunReport is not visible to the users and reports are never run from it - it's only for admin - and myself practically do not use that option.
IF the report query use no hash cookies you can use that option or potentially you can use there only with the global hash cookies.
https://wiki.nubuilder.cloud/ ... sh_Cookies
So in production anyway you need to run the report from the form and not from Setup.
Please find enclosed one more video (with voice ) giving some explanation how to use hash cookies.
https://youtu.be/QegbJKt19nQ

[Keith-i]

I'm trying to achieve something similar and get the search/filter criteria from my search form to get displayed on the report. My report SQL successfuly pulls the values from the search/filter boxes I created but I just cannot get the same values to show as labels on the report. I've followed Janusz's guide above and added some nuSetProperty code on to the onclick button but it just isn't parsing the values. Any thoughts please?

Here's the code from my onClick for the 'Print Report' button.

Code: Select all

//set hash cookies to pass to report to display search criteria
nuSetProperty('rpt_minprice', fltr_min_price.value);
nuSetProperty('rpt_maxprice', fltr_min_price.value);
nuSetProperty('rpt_datefrom', fltr_date_from.value);
nuSetProperty('rpt_dateto', fltr_date_to.value);


nuRunReport('rpt_salesdata');
//nuDebug($select);

Here's the report SQL for reference. It simply uses the search/filter box ID's as hash cookies which according to one of Janusz's posts above works fine when you are starting from an edit form.

Additional info: if you generate report from specific Edit form you can refer to the data objects from that form directly without the need to use nuSetProperty. You can use directly in your SQL code the hashed names like: #objectID#.

Code: Select all

SELECT
 tblValues.*,
    tblProperties.*,
    tblRoads.*

FROM
    tblValues
        JOIN tblProperties ON tblProperties.idProperties = tblValues.id_Properties
        JOIN tblRoads ON tblRoads.idRoads = tblProperties.id_Roads

WHERE
    (`SaleDate` BETWEEN '#fltr_date_from#' AND '#fltr_date_to#') AND (`SaleValue` Between '#fltr_min_price#' AND '#fltr_max_price#')

[kev1n]

Try passing true for the 3rd parameter to make it a global Hash Cookie:

Instead of

Code: Select all

nuSetProperty('rpt_minprice', fltr_min_price.value);

Write:

Code: Select all

nuSetProperty('rpt_minprice', fltr_min_price.value, true);