BusinessObjects Board

How to use ALL in the where clause in prompts

building reports webintelligence
#1

In BO document i read the following:

If you do not want the prompt to be mandatory to limit the number of rows, try the
following technique, originally suggested by Walter Muellner of Mercury Business
Solutions in Austria. The Where clause uses an OR statement to allow users to either answer
ALL or choose individual values from the list:
‘ALL’ IN @Prompt(‘Enter City or ALL’,‘A’, ‘Customer\City’,multi,)
OR
City.city IN @Prompt(‘Enter City or ALL’,‘A’, ‘Customer\City’,multi,)

Pls tell me how to use ‘ALL’ in where clause as i could not get the same.

Regards

Rajiv1107 (BOB member since 2007-01-25)

#2

You already have the correct syntax to implement the ‘ALL’ for the prompt.
You will use this in a predefined condition in the universe, drag it into query panel which when prompted and ‘ALL’ typed in the box should fetch data for all values in the LOV.
Also read the Designer FAQ All in LOV

.

haider :es: (BOB member since 2005-07-18)

#3

hi,
Here is the scenerio,

  1. take a sample object in which you want to add ‘ALL’ in the LOV.

  2. go to Properties–>edit—>Query panel will come–>go to SQL–>override the sql by adding the following stmt
    UNION
    SELECT ‘ALL’ FROM DUAL

  3. Save and close–>Apply–>OK

  4. Then Create a prompt for the above object by giving the following Stmt,

@Select(Class\Object) IN @Prompt(‘Prompt Text’,‘A’,Class\Object’,Multi,Free) OR ’ ALL’ IN @Prompt(‘Prompt Text’,‘A’,Class\Object’,Multi,Free)

Now you can select All as well individual values from the LOV’s
Hope it was clear.

Thanks,
Anil.

anil.ganga1 :us: (BOB member since 2007-07-04)

#4

thanks, it worked !!!

Rajiv1107 (BOB member since 2007-01-25)

#5

Hi,

I am trying to do this on WebI report in BOXIr2.

I am successfull to get ALL in LOV on Report. But when I select ALL and run Report giving me no data. But if I select specific country. Giving me data. Am I missing some thing ?

Please help!!

Thanks in Advance

j_ratoz (BOB member since 2005-11-04)

#6

If you use the prompt syntax given above, you should be ok… I would suggest you to check you syntax…

rimpa :india: (BOB member since 2008-04-14)

#7

Syntex is okay I have just copy paste and change objects/class

but thing is when I select ALL on report LOV. Its not fetching any data. But when I select spefic country which having data giving me data for that country on report.

here is what I am using on object

SELECT DISTINCT
  CTSALD_OWNER.CTS_COUNTRY_CODE_REF.COUNTRY
FROM
  CTSALD_OWNER.CTS_COUNTRY_CODE_REF
UNION
    SELECT 'ALL' 
FROM DUAL

and where clause :

@Select(Ctsald Owner Cts Country Code Ref\Country) IN @Prompt('Country (or ALL to select all values)','A','Ctsald Owner Cts Country Code Ref\Country',Multi,Free) 
OR 
'ALL' IN @Prompt('Country (or ALL to select all values)','A','Ctsald Owner Cts Country Code Ref\Country',Multi,Free)

anything wrong ?

j_ratoz (BOB member since 2005-11-04)

#8

The “Union” Clause should be in the LOV for the Country Object within the universe…

rimpa :india: (BOB member since 2008-04-14)