Create Report With Date Range

Questions related to using nuBuilder Forte.

Create Report With Date Range

Postby yusa82 » Fri Dec 21, 2018 4:59 pm

Hi,
How can i create a report with date range?
Can anyone help?
What should i build first?
I've create bunch of form with date in it, what i would like is to get the report that i've made but with date range.
How can i do that?
Please help
yusa82
 
Posts: 19
Joined: Tue Oct 02, 2018 12:59 pm

Re: Create Report With Date Range

Postby admin » Sat Dec 22, 2018 7:05 am

yusa82,

1. Create a Form with from and to dates.

2. Use the SQL Builder to create a SELECT statement using Hash Cookies...

Code: Select all
SELECT * FROM invoices WHERE  inv_date BETWEEN '#from#' and '#to#'


3. Use created Form as the Launch From. and the SQL record as the Table for the Report you create.

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

Re: Create Report With Date Range

Postby brick » Thu Jan 16, 2020 8:16 am

Could you explain the hash cookies part in more detail. I understand what you mean about creating the form and report but I'm not understanding how to make my SELECT statement point to my input in the "from" and "to" textboxes.
Attachments
2020-01-15 16_25_05-Settings.png
2020-01-15 16_25_05-Settings.png (5.06 KiB) Viewed 144 times
brick
 
Posts: 11
Joined: Fri Jan 10, 2020 7:46 am

Re: Create Report With Date Range

Postby Janusz » Thu Jan 16, 2020 8:40 am

Hi, maybe this can help. It's not for date range - but the way how to use use the hash cookies is the same.

https://forums.nubuilder.com/viewtopic.php?f=21&t=9635#p19150
https://drive.google.com/open?id=1p0sfHZfca9V_SfzILz5gyM4RMsG9vlIb
Janusz
 
Posts: 276
Joined: Fri Dec 28, 2018 10:11 pm
Location: Krakow, Poland

Re: Create Report With Date Range

Postby kev1n » Thu Jan 16, 2020 3:19 pm

If the Id of "Stat Date" is e.g. start_date ( -> ID in object properties), the hash cookie to be used in your SQL statement is #start_date#. The same for "End Date"
Attachments
id.PNG
id.PNG (4.95 KiB) Viewed 134 times
kev1n
 
Posts: 471
Joined: Mon Oct 15, 2018 2:13 am

Re: Create Report With Date Range

Postby brick » Sat Jan 18, 2020 12:19 am

Thanks for the quick feedback. The good news is I was on the right track with the ID of the object, however I'm still not able to get it to work. I'm not sure if its some kind of syntax issue with my SQL statement or something else that I'm over looking. Here is a screenshot of the error i get after hitting the "run" button and a screenshot of the SQL statement.
Attachments
report_error.png
report_error.png (84.11 KiB) Viewed 117 times
sql_builder.png
sql_builder.png (29.05 KiB) Viewed 117 times
brick
 
Posts: 11
Joined: Fri Jan 10, 2020 7:46 am

Re: Create Report With Date Range

Postby kev1n » Sat Jan 18, 2020 12:29 am

MySQL expects DATE values to be single-quoted.

Code: Select all
'#test_start_date#' AND '#test_end_date#'
kev1n
 
Posts: 471
Joined: Mon Oct 15, 2018 2:13 am

Re: Create Report With Date Range

Postby brick » Sat Jan 18, 2020 1:11 am

Ok i think i figured out a few of my issues. One was I left out the single quote (is that only expected with DATE values?) Two I didn't have my report designed. Now that I have it working my other issues I only get the run button when I open it through reports rather than through user home.
Attachments
screenshot2.png
screenshot2.png (4.51 KiB) Viewed 113 times
screenshot1.png
screenshot1.png (3.82 KiB) Viewed 113 times
brick
 
Posts: 11
Joined: Fri Jan 10, 2020 7:46 am

Re: Create Report With Date Range

Postby brick » Sat Jan 18, 2020 1:22 am

I got the button thing figured out as well. I have another question that advances on what I have already done but I'm going to start another thread for that. Thanks everyone for you assistance!
brick
 
Posts: 11
Joined: Fri Jan 10, 2020 7:46 am


Return to General