Adding a default time to a date prompt
8 January, 2014
Hi ,
I have a query for which I want to prompt for an opening balance and a closing balance. From a stock snapshot which has a date the snapshot was taken. I chose to create two prompts to do that. I then want to use those prompts to search for transaction history which is a datetime field that occurs between those dates and just append 23:40 to the opening and closing date prompts and use those for a sub query on the transactions table. I cannot work out how to do this any suggestions would be greatly appreciated.
Regards,
Dieter
I have a query for which I want to prompt for an opening balance and a closing balance. From a stock snapshot which has a date the snapshot was taken. I chose to create two prompts to do that. I then want to use those prompts to search for transaction history which is a datetime field that occurs between those dates and just append 23:40 to the opening and closing date prompts and use those for a sub query on the transactions table. I cannot work out how to do this any suggestions would be greatly appreciated.
Regards,
Dieter
Hi Dieter,
In order to achieve this I needed the Date and Time fields separately.
Yellowfin does have a native Date function Calculated Field, but not a Time function.
I had to manually add the Time Function to the Custom Functions file, using the fllowing code.
[code]
Time, MySQL
1
TimeStamp
datetime
TIME($1)
time($1)
MySQL
time
[/code]
NB! (This is the MySQL function for time as I'm using a MySQL db for this example)
I then added the Date and Time fields to my View.
Once added I can now use them in my report, as my filters.
I hard-coded my Time values to the start of the day and the end of the day allowing the User only to select the Start and End Dates.
I hope this helps you to achieve what you are trying to do
Regards,
Stephen
In order to achieve this I needed the Date and Time fields separately.
Yellowfin does have a native Date function Calculated Field, but not a Time function.
I had to manually add the Time Function to the Custom Functions file, using the fllowing code.
[code]
Time, MySQL
1
TimeStamp
datetime
TIME($1)
time($1)
MySQL
time
[/code]
NB! (This is the MySQL function for time as I'm using a MySQL db for this example)
I then added the Date and Time fields to my View.
Once added I can now use them in my report, as my filters.
I hard-coded my Time values to the start of the day and the end of the day allowing the User only to select the Start and End Dates.
I hope this helps you to achieve what you are trying to do
Regards,
Stephen
Not really if i read the sql right (without testing ) that will not generate any results say if the trans date is between the start and end date date but lest than 12:00AM because the fields are separate ?
Hi Dieter
The SQL definitely returns results
12:00 AM = 00:00 all transactions before that is from the previous day.
Below is an example where i select the same start and end dates
This lists all transactions from 23/07/2013 00:00 to 23/07/2014 23:59
Below is an example where i select a different end date
This lists all transactions from 23/07/2013 00:00 to 24/07/2014 23:59
Hope that clear it up for you
Regards,
Stephen
The SQL definitely returns results
12:00 AM = 00:00 all transactions before that is from the previous day.
Below is an example where i select the same start and end dates
This lists all transactions from 23/07/2013 00:00 to 23/07/2014 23:59
Below is an example where i select a different end date
This lists all transactions from 23/07/2013 00:00 to 24/07/2014 23:59
Hope that clear it up for you
Regards,
Stephen
Could you post the resulting SQL produced by the report as I think I have done the same things without success, it may come down to the custom function I am using given it is a different database. As soon as I add the time element in I get no rows
Hi Dieter
Below is the SQL produced by the above filter setup
Regards,
Stephen
Below is the SQL produced by the above filter setup
Regards,
Stephen
OK try changing the time 2pm so you want to see all the transactions between 2pm on the 23rd and 2pm on the 24th you should get all your transactions as shown above but I don't think you will.
Hi Dieter,
The example i gave you above was based on the assumption that you wanted to specify a default time values that lists all transactions for the days selected, and that the time values will be hard-coded and never changed.
Selecting 2pm for both the 23rd and 24th will only give me transactions done at exactly 2pm for the selected days.
If you are going to change the time values as well your setup will be a little more complex than the example above.
Here is an example that allows for time selection as well.
I selected 2pm for the 23rd and the 24th as requested.
Filter Setup
Report Output
SQL Generated
Hope this is what you are after
Regards,
Stephen
The example i gave you above was based on the assumption that you wanted to specify a default time values that lists all transactions for the days selected, and that the time values will be hard-coded and never changed.
Selecting 2pm for both the 23rd and 24th will only give me transactions done at exactly 2pm for the selected days.
If you are going to change the time values as well your setup will be a little more complex than the example above.
Here is an example that allows for time selection as well.
I selected 2pm for the 23rd and the 24th as requested.
Filter Setup
Report Output
SQL Generated
Hope this is what you are after
Regards,
Stephen
I don't want to change the time ie it is fixed however the only way I could get it to work was with a similar query. However I had to enter the date twice. How do you get the link to filter option ?
Hi Dieter,
It is the last option in the drop-down when selecting your operand.
Regards,
Stephen
It is the last option in the drop-down when selecting your operand.
Regards,
Stephen
Thanks very much problem solved