Skip to Content
author's profile photo Former Member
Former Member

Problem in selecting Maximum of Week Number

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Aug 11, 2009 at 03:17 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.