| Author |
Message |
Padma
Newbie
Joined: 09 Sep 2010
Location: India
Online Status: Offline
Posts: 20
|
   Topic: how to fetch data between two date parameters
Posted: 25 Jan 2012 at 2:34am |
Can someone help to how to write a formula to fetch the data between 2 date parameters in cyrstal report from the database.
EG: start_month_year (parameter1) = Jan-2011
end_month_year (parameter2) = Mar-2011
I need the report to show the records which are in present between these dates.
Thanks,
Padma
|
|
priya
|
 IP Logged |
|
|
rkrowland
Senior Member
Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
|
Posted: 25 Jan 2012 at 3:09am |
create a formula;
if
{table.datefield} >= {?Paramter1}
and
{table.datefield} <= {?Parameter2}
then "Show"
else "Hide"
Now goto Report > Select Expert and apply a filter on @SelectionFormula is equal to "Show".
Regards,
Ryan.
Edited by rkrowland - 25 Jan 2012 at 3:10am
|
|
IP Logged |
|
Padma
Newbie
Joined: 09 Sep 2010
Location: India
Online Status: Offline
Posts: 20
|
Posted: 25 Jan 2012 at 3:59am |
Thanks for your help,
but since im a newbie to crystal report can you please help where to write this formula and how to apply filter on the selection formula.
|
|
priya
|
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 25 Jan 2012 at 8:30am |
rkrowland has pretty much given the solution, create a formula and what to do with it, but... the bigger question is what does your date look like in your data? if your datefield is a datefield, and your parameter is a string (which I am guessing it is) you will need to convert one to the other...probably the datefield to a string, but telling if a string is between two other strings is much trickier...so on second thought, convert the parameters to dates...still not fun but something like: shared datetimevar param1; local numbervar yr; local numbervar mth; local numbervar dash; local stringvar month; dash := instr((?parameter1), "-"); month :=left((?parameter1), 3); if month = "Jan" then mnth:=1 else if month = "Feb" then mnth:=2 else if month = "Mar" then mnth:=3 else if month = "Apr" then mnth:=4 else if month = "May" then mnth:=5 else if month = "Jun" then mnth:=6 else if month = "Jul" then mnth:=7 else if month = "Aug" then mnth:=8 else if month = "Sep" then mnth:=9 else if month = "Oct" then mnth:=10 else if month = "Nov" then mnth:=11 else if month = "Dec" then mnth:=12; yr:=mid({?parameter1}, dash + 1); param1 := cdate(yr, mnth, 1); you would do the same for other parameter, though to ensure the last day of the month I would probably do something like param2 := cdate(yr, mnth, 1); param2 := dateadd("d", -1, dateadd("m", 1, param2)); now you can check that the table date is in the range of parameters, like: {table.datefield} in param1 to param2; HTH i know that there is a lot here. The outline is to convert the parameter to a date value (since it is probably a string value) and then to check if the record in question is in this range. Typically for filtering out records, I will use the Report/Selection Formulas/Record. the last line {} in {} to {}, is either true or false, and if it is true, then record will be in the recordset, otherwise it will be excluded.
|
|
IP Logged |
|
Gurbs
Senior Member
Joined: 16 Feb 2012
Location: Ireland
Online Status: Offline
Posts: 216
|
Posted: 17 Feb 2012 at 12:17am |
|
If you have already created your parameters, it will be fairly simple. Go to Report -> Selection formulas -> Records, and type the following code
{table.fieldname} in {?Start date} to {?End date}
Where {?Start date} is de name of your parameter for the start date, and {?End date} is de name of your parameter for the end date.
When you run the report now, you will be asked for your start and end date, and he will only return records between these 2 dates
|
|
IP Logged |
|
TheSaint
Newbie
Joined: 30 Aug 2012
Online Status: Offline
Posts: 8
|
Posted: 30 Aug 2012 at 9:45am |
|
I know this thread is old, but I want to give it a bump because I just spent 3 days trying to figure this out!
|
|
Oops...
|
|
IP Logged |
|
comatt1
Senior Member
Joined: 19 May 2011
Online Status: Offline
Posts: 337
|
Posted: 31 Aug 2012 at 5:51am |
|
somewhere in the where clause put "isdate({field)) if the value in database is a string.
|
|
IP Logged |
|
zanyar-Jalal
Newbie
Joined: 06 Sep 2012
Location: Iraq
Online Status: Offline
Posts: 12
|
Posted: 12 Sep 2012 at 7:43pm |
|
Dear Padma,
this is the simplest and best way to create a date range parameters:
1)create a parameter for start date.
2)create a parameter for end date.
3)now go to report -> select expert -> record write this code:
{your field name} in {?first date parameter} to {?end date parameter}
Edited by zanyar-Jalal - 12 Sep 2012 at 7:48pm
|
|
IP Logged |
|
Alexander
Newbie
Joined: 10 Jan 2012
Online Status: Offline
Posts: 12
|
Posted: 28 Oct 2012 at 12:09am |
Dear Jalal, Going thru yr reply. can pls tell me if i dont want to give any date range & i just want to refresh the report from 01st of the month to till date of the month. Regards Alex
|
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 30 Oct 2012 at 8:47am |
you can create variables that mimic that range. the current date of the month is the Today() function. the first of the month, might have a function, but you can create the date using Date(year(today), month(today), 1) or something very similar, I am not sure if Date() is the correct function. Check Help.
|
|
IP Logged |
|
|
|
