How to pass parameter selection to SQL

[timky]

Hi experts,

I have a real problem here. I have checked with our local senior crystal consultant and they were not able to help me.

I have defined a friendly date parameter called (?Process Date) and allow user to select
P - (current biz date)
C - (current system date) or
user input date (dd/MM/yyyy format)

The following formula is to parser the (?Process Date)

(@Parser Date)

LOCAL NUMBERVAR ndd:=0;
LOCAL NUMBERVAR nmm:=0;
LOCAL NUMBERVAR nyyyy:=0;
LOCAL STRINGVAR DbParser;

IF {?Process Date} = 'P' THEN
DATE(GetBizProcessDate)
ELSE IF {?Process Date} = 'C' THEN currentdate
ELSE
IF ISDATE({?Process Date}) THEN
(
nmm := ToNumber(TOTEXT(CDATE({?Process Date}), "MM&quot);
ndd := ToNumber(TOTEXT(CDATE({?Process Date}), "dd&quot);
nyyyy := ToNumber(TOTEXT(CDATE({?Process Date}), "yyyy&quot);

IF ((nmm < 13) AND (ndd < 13)) THEN
(
DbParser := TOTEXT(CDATE(nyyyy, ndd, nmm), &quot;MM/dd/yyyy&quot;
CDATE(DbParser);
) ELSE CDATE({?Process Date})
)
ELSE DATE(0,0,0)

If user select 'P' options, the report will use the UFL function (GetBizProcessDate) to retrieve current biz process date from database.

Ok, the real problem is when I add this formula into the Selection Formula, it will not pass to SQL to process until it return all the records.

How can I improve this type of selection so that it will pass to SQL?

Your help is very much appriecated.

 

[synapsevampire]

Tim: I think that Annette meant that this formula is to go into the report selection formula->record:

If {?ReportDate} = 'C' then
{ORDERS.ORDER_DATE} = CurrentDate
Else If {?ReportDate} = 'P' then
{ORDERS.ORDER_DATE} = {%Get_Date}
Else
{ORDERS.ORDER_DATE} =
Date (ToNumber (Right({?ReportDate},4)),
ToNumber (Mid({?ReportDate},4,2)),
ToNumber (Left({?ReportDate},2)))

I'd create 2 parameters though, having people enter dates as MM/DD/YYYY can be problematic. Create 1 parm which selects from the defaults or O (for other), and then use a real date parm if they want to enter one.

Then your record selection might be:

If {?ReportDateDefault} = 'C' then
{ORDERS.ORDER_DATE} = CurrentDate
Else If {?ReportDateDefault} = 'P' then
{ORDERS.ORDER_DATE} = {%Get_Date}
Else if {?ReportDateDefault} = 'O' then
{ORDERS.ORDER_DATE} = {?ReportDate}

Either way will work, though the string based parm in Annette's example will require that users ALWAYS use &quot;MM/DD/YYYY&quot; format, meaning that they have to enter 01/01/2003, not 1/1/2003, and they can't select using the CR calendar.

-k

 

[timky]

kai,

As I mentioned earlier, if we contruct in this way, it wouldn't get pass down to SQL database. It will return all the reconds and then only filter the selected date.

pls help.

Tim