how to fetch data between two date parameters
Printed From: Crystal Reports Book — Forum Name: Technical Questions
Posted By: Padma — 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
Posted By: rkrowland — 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
Posted By: Padma — 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.
Posted By: lockwelle — 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.
Posted By: Gurbs — 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
Posted By: TheSaint — 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!
Posted By: comatt1 — 31 Aug 2012 at 5:51am
somewhere in the where clause put "isdate({field)) if the value in database is a string.
Posted By: zanyar-Jalal — 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
Posted By: Alexander — 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
Posted By: lockwelle — 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.
|
