Default Date Parameter with Add command sql

[ramkrish11]

Hi Folks,

Was trying to use a Daterange Parameter eg(DAILY, MONTHLY, YEARLY,ADHOC) as one of the parameters to the report.

Based on the above parameter, I populate the Start and End Date parameter in the record selection formula editor.

Problem 1: How do I make Start Date and End Date parameter optional ? ie For Daily, monthly, yearly in DateRange, user does not need to enter any start date, end date but for adhoc he needs to. Right now, report wont proceed without entering something in these parameter fields. How do I get around it ?

Problem 2: THe SQL to which I pass the start date and end date parameters is a "Add Command" SQL and it doesnt even seem to realize I have something in the record selection formula (makes sense, since the SQL is kind of stand alone). So question, how do I make Crystal look at recod selection formula before it starts to execute the SQL.

We are using Crystal Reports XI with Oracle 9.2 database.

Thanks very much for your help.

Ram

 

[GJParker]

When using the 'Add Command' as a datasource the parameters must be created in the Command dialog itself

Database menu -> database expert -> right click on command -> edit command -> click on Create to create a parameter. Once created the parametr can then be added to the SQL of your command.

HTH




Gary Parker
MIS Data Analyst
Manchester, England

 

[ramkrish11]

Yes. That is how we create the parameters for "Add command" option. But is there a way the parameter value can be set in the record selection formula screen ?

thanks

 

[GJParker]

If you remove the parameters from the Add Command, you can then create parameters in the CR designer as normal and use these in the record selection formula, unfortunately I think these will not be passed to the db but processed locally.

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[ramkrish11]

But What I have seen is that parameters for Add Command SQL are to be created right in that screen and does not accept the ones created in the CR Designer.

 

[GJParker]

I can only speak for version 10 which is the version I am using. This does allow selction formulas but as I said doesn't send the filters to the db.





Gary Parker
MIS Data Analyst
Manchester, England

 

[BobSuruncle]

Parameters created in the "Add Command" dialog will show up in the main report in the Field Explorer.
The parameters are added to the SQL statement by setting the cursor where you want the parameter (usually in the WHERE clause ) and then double-clicking the parameter. Remember to put single quotes around any string type parameters.
There is then no need to use the parameters in the Record Selection formula.(unless you want further filtering.)


Bob Suruncle