Problem in selecting Maximum of Week Number

[Former Member]

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

[Former Member · Accepted solution]

you cant use max or min in the record selection due to evaluation time

create a parameter with the default option of zero(0)

that will stand for all

in the record selection place this

IF {?P_WEEK} =0 THEN true

else

if {SALES_TABLE.WEEK} = {?P_WEEK}

then true

that should return all records or the selected week

[Former Member · Accepted solution]

Thanks for your reply but it will not solve the issue. In case I enter zero, I want to see only the latest week's data.

Any other thoughts?

[Former Member · Accepted solution]

Remove the record selection condition and go to report>selection formulas>group and paste the same condition here

IF {?P_WEEK} <>0 THEN
{SALES_TABLE.WEEK} = {?P_WEEK}
ELSE
{SALES_TABLE.WEEK} = Maximum ({SALES_TABLE.WEEK})

HTH

Raghavendra.G

[Former Member · Accepted solution]

Or, base your report on an SQL command, add the week number as a parameter to the SQL command, and use the SQL command above...

HTH,

Carl