Page 1 of 1
Create Report With Date Range
Posted: Fri Dec 21, 2018 8:29 am
by yusa82
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
Re: Create Report With Date Range
Posted: Fri Dec 21, 2018 10:35 pm
by admin
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
Re: Create Report With Date Range
Posted: Wed Jan 15, 2020 11:46 pm
by brick
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.
Re: Create Report With Date Range
Posted: Thu Jan 16, 2020 12:10 am
by Janusz
Re: Create Report With Date Range
Posted: Thu Jan 16, 2020 6:49 am
by kev1n
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"
Re: Create Report With Date Range
Posted: Fri Jan 17, 2020 3:49 pm
by brick
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.
Re: Create Report With Date Range
Posted: Fri Jan 17, 2020 3:59 pm
by kev1n
MySQL expects DATE values to be single-quoted.
Code: Select all
'#test_start_date#' AND '#test_end_date#'
Re: Create Report With Date Range
Posted: Fri Jan 17, 2020 4:41 pm
by brick
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.
Re: Create Report With Date Range
Posted: Fri Jan 17, 2020 4:52 pm
by brick
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!