date parameter

[ridhirao22]

Using CR 2008, Orcale 11g


I am using a command object as source to the report. I created parameter {?startdate} and {?Enddate}.

Command object looks like this:

Select col1,col2,col3 from
Tab1 a
Where a.datefield >= {?startdate}
And a.datefield <= {?Enddate}


Report doesn’t seem to read any data even though the input dates I am entering has data.
Can anyone advise what is it am doing wrong? Tired with “between - and” clause(didn’t work)

It worked fine when not using any {?Enddate} and enter sql as below in command box

Select col1,col2,col3 from
Tab1 a
Where trunc(a.datefield) >= {?startdate}
And trunc(a.datefield) < trunc(sysdate) +1

Tried creating the parameter in the report rather than in the command. But how can I see if the dates were passed to SQL. Show SQL feature doesn’t show the passed dates after report execution.

TIA,
RR

 

[hilfy]

When you created your parameters, did you create them in the command or in the report? If you created them in the report, you need to delete them and create them in the command in order for them to work.

Another thing you can do is go to View SQL. It should prompt you to enter the dates, if you haven't already, and then show you what Crystal is sending to the database.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[ridhirao22]

Yes, I have created the parameters in the command, which are carried into the report.

Thank you,
RR

 

[hilfy]

So, you need to use similar syntax in the command to get your null value. If your command just pulls data from a county lookup table, you would do something like this:

Select null as County_Code, 'NA' as County_Name from Dual
UNION
Select County_Code, County_Name from Counties

(The "From Dual" syntax is for Oracle, you would set this up however your database requires it.)

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[ridhirao22]

I guess you post in wrong place!

 

[hilfy]

Another option is to change the way the report is filtered - use something like this:

(IsNull({table.County}) or {table.County} in {?param_county})

Note the enclosing parenthese - you need them in order for this to work when you have other filter criteria. Also, the check for null MUST come before the chech for a value or else this won't work.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[ridhirao22]

Silliest me! breaking my head since morning.

The parameters created in the command were auto order by alphabetic
and it's been prompting the end date first and start date next where I was enter the start date for end and end date for start. No records read.

Thank you! Dell for taking time to respond to my query.

RR