Max Function in @Prompt

28 Views

Hi,

We have a week dimension in our Cal table. The user wants to use a condition where if he will enter week number (for eg: 13-2009) then data of that particular week should be displayed and if he will enter ' * ' then data of last week in the table will be displayed.

To fetch the last week we can use max(cal.week). But how to use it in prompt? Because when I am using max function then getting an error that group functions are not supported in prompt. Below is the prompt condition I am using:

CAL.WEEK IN (decode(@Prompt('Enter week (or * for last week)', 'A',, MONO,FREE),'*',(MAX(CAL.WEEK)),@Prompt('Enter week (or * for last week)', 'A',, MONO,FREE)))

I tried it by creating an object for MAX(CAL.WEEK) and then tried to use it in prompt, but since prompt doesnot support it, this object was not visible in the object list while creating the prompt.

Please help.

Thanks,

Kirti