Problem in selecting Maximum of Week Number

37 Views

Hi Gurus,

I have a report in Crystal based on sales table. The table stores values for various weeks.

I have a parameter on week number. So, while running the report, I pass in a week number. Its working

fine so far. But now I want to enhance the selection that if I enter week number 0 (zero) then it

should display sales values for the latest week otherwise it should bring values for the given week.

I am trying following in the Record Selection formula:

IF {?P_WEEK} <> 0 THEN

{SALES_TABLE.WEEK} = {?P_WEEK}

ELSE

{SALES_TABLE.WEEK} = Maximum ({SALES_TABLE.WEEK})

But the above formula gives following error message on compile:

This function cannot be used because it must be evaluated later.

In plain SQL, I can get it with the following statement:

SELECT * FROM sales_table

WHERE week = (SELECT MAX(week) from SALES_TABLE);

How can I achieve this in Crystal?

Please help!

Thanks,

Zahid