Date Parameter into Record Formula Selection

[sommererdbeere]

Hi,
I really really hope someone can help me.. i've been trying to figure this out for a long long time, but no solution found.. here is my problem:

In Record Formula Selection, i've try to pass in 3 parameter, part_no, begin_date, end_date.. however, the date doesn't pass correctly, it mess up the whole report i created... let say, i wanna select the date between 11/1/2003 - 11/10/2003, this date frame is not being selected.. the report shows ALL the date.. here how i wrote in parameter fields and the record formula for the date:

Parameter Fields: Begin_Date --> Value type(date)
--> default value (1/1/9999)
end_Date --> Value type(date)
--> default value (12/30/9999)

Record Formula Selection:


{CLOSE_DATE} >= {?Begin_Date} and
{CLOSE_DATE} <= {?end_Date}


i have tried to set Value Type (string) in the Parameter fields and do cdate({?Begin_Date}), date({?Begin_Date}), datevalue({?Begin_Date}) in Record Formula Selection for both begin and end date..

the date is pass in from oracle SQL database.
i've heard it is very tricky to pass in Date from Oracle Database into Crystal Report..

i hope someone can help w/this problem.. i will appreciate very very very much.. thanks for those who help and answer my question.. million million thanks..

mm

 

[Turkbear]

Hi,
The only really tricky part is remembering that a DATE type in Oracle is actually a DateTime ( it always includes a time component -Midnight ( 00:00:00), if not specified when stored) This can affect comparisions but should not affect ranges..

Try, as your selection formula:

{CLOSE_DATE} in {?Begin_Date} to {?end_Date}

hth,

 

[synapsevampire]

It's not tricky to pass the date from Crystal to Oracle (the reverse is not the case).

You might flesh out all of the details, such as whether you're using a Stored Procedure which has a date parm, or whether you're creating a parm in Crystal.

You stated that you had a part number, yet it isn't in this record selection forumla so this can't be the correct record selection formula. Try to avoid showing what doesn't work, or just part of what you're using, that will only require more discovery.

Your syntax is fine, you shouldn't opt for an IN clause, in fact a >= and <= will net better results for pass through SQL.

Check the Database->Show SQL Query to see what Crystal is passing to the database.

Paste what you find in here, as well as ALL of the record selection formula, it isn't that much more work to be thorough.

-k

 

[Turkbear]

Hi,
I find that Crystal translates the IN operator to 'standard' Oracle Sql ; I find it easier to write and understand:
With
{HR_CUR_RPTNG.BIRTH_DT} in Date(1956,01,01) to Date(1957,12,31)
Sql is:

SELECT 
    "HR_CUR_RPTNG"."BIRTH_DT", "HR_CUR_RPTNG"."EMPL_NBR"
FROM
    "WAREHOUSE"."HR_CUR_RPTNG" "HR_CUR_RPTNG"
WHERE
    "HR_CUR_RPTNG"."BIRTH_DT" >= TO_DATE ('01-01-1956', 'DD-MM-YYYY') AND
    &quot;HR_CUR_RPTNG&quot;.&quot;BIRTH_DT&quot; <= TO_DATE ('31-12-1957', 'DD-MM-YYYY')

And with
{HR_CUR_RPTNG.BIRTH_DT} >=Date(1956,01,01)
and
{HR_CUR_RPTNG.BIRTH_DT} <= Date(1957,12,31)
The Sql Is:

SELECT 
    "HR_CUR_RPTNG"."BIRTH_DT", "HR_CUR_RPTNG"."EMPL_NBR"
FROM
    "WAREHOUSE"."HR_CUR_RPTNG" "HR_CUR_RPTNG"
WHERE
    "HR_CUR_RPTNG"."BIRTH_DT" >= TO_DATE ('01-01-1956', 'DD-MM-YYYY') AND
    &quot;HR_CUR_RPTNG&quot;.&quot;BIRTH_DT&quot; <= TO_DATE ('31-12-1957', 'DD-MM-YYYY')

Maybe I'm just lazy....

 

[synapsevampire]

Turk: I see no difference in this example, but when the record selection formula gets haory, the IN can choke.

Anyway, since they're both passing the SQL, what was the advantage to them changing to that?

The real problem here is that they didn't share all of the onformation, choosing only to dole out part of it requiring ongoing discovery, once we have the facts, it's probably very easy to fix.

I forgot to mention before that if they're using ODBC, they should be using the CR supplied ODBC driver, NOT the Oracle driver.

-k

 

[Turkbear]

Hi,
Good points...( as usual!)..



PS ( as you know from my previous posts, I truly hate ODBC, in any flavor)

[smole]

 

[Turkbear]

Hi agian,
I also hate [smole] when I meant

 

[synapsevampire]

I agree with you on using ODBC, but in Crystal's case, often times it's the only way to get things done, and overall it's usable.

-k

 

[sommererdbeere]

Hi,
thanks for everyone who reply.. actually, what i want to know is my formula correct.. the reason i asked 'coz if i take out the &quot;Date&quot; part, the parameter i pass in (part_no), is truly correct.. However, when i pass in the date part, everything got screw up..

i've tried the &quot;in&quot; and &quot;to&quot; form, but it doesn't work... what i mean is the date doesn't get recognized when i pass in.. this happen in Crystal report.. data is pulling/retrieve it from oracle database..

i cannot set the date initial 'coz the user can choose/pass in any date they want..

to make it more clear: let say
i wanna c the date from 11/1/2003 - 11/10/2003.. if this date is selected, isn't it true that the Start Date and End Date column should show all the data within/start & end by the above date i've input?? however, it isn't.. the start & end date in the report doesn't show 11/1/2003 - 11/10/2003.. it shows ALL the date (like: 3/21/2003, 2/1/2003, etc)...

i'm just thinking is it true that i need to write a special code inside Crystal report like:
{CLOSE_DATE} >= date({?Begin_Date} )?

what should i set for the parameter value??

i try to find a solution to this problem, but it seems like i can't find a correct formula for that..

it will be great if someone can give me some advice/guide me to this problem.. many many many thanks..

sommererdbeere

 

[lbass]

You still haven't showed us the whole record selection formula. Do you have any &quot;or's&quot; in it?

-LB

 

[sommererdbeere]

below is the whole record formula selection:

if i delete the date part, the Part_no works find.. however, if i included the date part, nth works right...

(UpperCase({PART_NO}) startswith UpperCase({?Part_No})) or
(
{CLOSE_DATE} >= {?Begin_Date} and
{CLOSE_DATE} <= {?end_Date}
)

i pass in as date.. if i pass in as string, i would have

(UpperCase({PART_NO}) startswith UpperCase({?Part_No})) or
(
{CLOSE_DATE} >= date({?Begin_Date}) and
{CLOSE_DATE} <= date({?end_Date})
)

however, none of the above seems to be correct...
on the report, i have filter part: Part_no, Begin_Date, end_Date

however, everything screw up when i have the &quot;date&quot; in my record formula selection.. it doesn't show the &quot;Date&quot; i want it.. it shows ALL the date and all the Part_no instead of specific part_no and date i input..

 

[lbass]

You need to change the &quot;or&quot; to &quot;and&quot;. Your current formula says &quot;show me part numbers starting with &quot;XXX&quot; of any date OR show me part numbers not starting with &quot;XXX&quot; that fall into this date range.&quot; The way an &quot;Or&quot; works is that if a record satisfies the first condition, it is included in the report. The second condition is only evaluated when the first condition does NOT apply. Since you seem to want part numbers from a certain date range, you want your record selection formula to be:

UpperCase({PART_NO}) startswith UpperCase({?Part_No}) and
{CLOSE_DATE} >= {?Begin_Date} and
{CLOSE_DATE} <= {?end_Date}

-LB

 

[sommererdbeere]

Hey Lbass,
thanks for your prompt response... here are 2 dilema.. u r right about the point.. i've change my formula to your current for formula.. which amazingly .. hehehee.. solve the Part_No issue.. now, it only shows the part_no i input.. however, the date still not right.. by that i meant, i input 11/1/2003 - 11/15/2003.. it shows all the date.. like 4/2/2003, 3/2/2003...
meaning: Part_No: H
Start_date: 11/1/2003
End_date: 11/15/2003

above is the first problem.. the date problem..

secondly, what if i wanna it to be choose in either form.. example, i only wanna choose the date: 11/1/2003-11/15/2003, but i don't need to choose Part_no.. by that i mean: Part_No: nth(no input)
Start_date: 11/1/2003
End_date: 11/15/2003

that's why i use &quot;or&quot; in my formula..

thank u for ur answer and ur help.. it would be great if u have any suggestion to me.. many many many many thanks.. i've been looking and playing around, but i still can't find a solution.. i really need help for this problem because i've been looking/finding solution for couple of days and i still can't figure out why.. many many many thanks

sommererdbeere

 

[sommererdbeere]

also, quick question...

what if there's no start date that i've choosen in the database.. meaning, there's no start_date from 11/1/2003..so, i'll have an if statement in the formular..

if isnull({?Begin_Date}) then true;

many many thanks...

 

[lbass]

Assuming that {?Part_No} is a string parameter, add a default &quot;All&quot; and make your record selection formula:

({?Part_No} = &quot;All&quot; or
UpperCase({PART_NO}) startswith UpperCase({?Part_No})) and
{CLOSE_DATE} >= {?Begin_Date} and
{CLOSE_DATE} <= {?end_Date}

This gives you the option of returning all records within the date range or only records with selected part numbers within the date range.

Can you verify that you have each of the two date parameters set up as a discrete date parameter? And please post the selection formula you are now using, since I cannot see how you could possibly return all dates if you were using my formula.

You don't need to have a date in the database that corresponds to {?begin_date} for the formula to work, but if there were no records with close dates in the range, you would end up with a blank report (no records returned).

-LB

 

[sommererdbeere]

i have tried using ur formula, but nothing comes out.. i guess it doesn't take the word, &quot;ALL&quot;.. for the formula i'm using now, it is:

UpperCase({PART_NO}) startswith UpperCase({?Part_No}) and
{CLOSE_DATE} >= {?Begin_Date} and
{CLOSE_DATE} <= {?end_Date}

which u have suggested.. instead of having an &quot;or&quot; after ({?Part_No}), i put an &quot;and&quot;...

as i've asked before, the above formula corrects one of the problem which is it output/shows the part_no i asked.. however, it doesn't show the correct date i ask for.. it shows ALL the date.. it didn't show a specific date range i want/input into the field parameter...

i would like to thank for those who help me and thanks for Lbass for ur prompt response..

i really hope i can get this problem resolve 'czo i've been looking for a solution @ least 3 days already.. many many many thanks..

 

[lbass]

Did you enter &quot;All&quot; as a default in the parameter set up screen?

You did not verify whether you have the date parameters set up as discrete date parameters. Please respond.

My formula works when tested here, so there is something else going on that we don't know about.

After changing the record selection formula, did you refresh the report and enter your date criteria? Maybe you should eliminate the default dates altogether.

-LB

 

[sommererdbeere]

hey lbass,
i have enter:

(UpperCase({?Part_No}) like &quot;%&quot or
UpperCase({PART_NO}) startswith UpperCase({?Part_No}) and
{CLOSE_DATE} >= {?Begin_Date} and
{CLOSE_DATE} <= {?end_Date}

i use &quot;%&quot; instead of &quot;ALL&quot; because &quot;ALL&quot; doesn't work.. &quot;%&quot; works..

i set all data parameter as Discrete Value..

i refresh the report whenever i've made changes to the formular.. but still, my specify date still doesn't shows up..

i really wanna know it is true i need to do:
{CLOSE_DATE} >= datevalue{?Begin_Date} and
{CLOSE_DATE} <= datevalue{?end_Date}

for the date???? 'coz this report(btw, i'm using Crystal report v.8.5) is pulling the data from Oracle database.. so, it is true that i need to write some code or use some function in the record formula in order to have the date generate correctly?

many many many thanks..

 

[synapsevampire]

Quite a lot of effort being exposed here.

Try setting the default parameter value to &quot;All&quot; as LB suggested, and then use:

(
if {?Part_No} <> &quot;All&quot; then
{table.PART_NO} startswith UpperCase({?Part_No})
else if {?Part_No} = &quot;All&quot; then
true
)
and
{table.CLOSE_DATE} >= {?Begin_Date} and
{table.CLOSE_DATE} <= {?end_Date}

Make sure that the date field is reognized by CR as a date by right clicking it and selecting browse data. What data type does Crystal think it is?

What you're trying to accomplish is fairly common, and it works fine.

-k

 

[sommererdbeere]

so, as what lbass and u suggested that i should type &quot;All&quot; into the default value (Parameter Field --> set default value)?

2nd. when i right click on Parameter field of {?Begin_Date}, there's nothing that i can select browse data type.. (rt. click on begin_date --> insert to report, edit, new, delete, etc... )

is it true that u mean, i should have &quot;Value Type&quot; in Edit Parameter field for Begin_date as &quot;Date&quot; instead of &quot;String&quot;?? i've try that as well, but it doesn't work..

i try to select from Browse table, but there's nothing for me to select... can u give me a clearer direction on how to approach.. many many thanks