Hi,
I am trying to add an all object to my LOVs. I have been through many of the posts and I did find one that referred to SQL Server, but I cannot make it work.
I have added the following to my LOV
UNION
SELECT ‘ALL’
When I select my LOV I can see ALL being displayed, but when using ALL in a condition it returns nothing when it should return everyting. Can someone please tell me what I am doing wrong.
Thanks
Anna
lustigb (BOB member since 2007-01-29)
Just displaying ‘ALL’ in the LOV object is not enough
Use the following to create a predefined prompt condition :
@Select(Sales\Currency) IN @Prompt('Enter currency code or ALL','A',Sales\Currency',MULTI,FREE)
or 'ALL' in @Prompt('Enter currency code or ALL','A',Sales\Currency',MULTI,FREE) Drag this condition in the report and then selecting ‘ALL’ will bring all values.
hyder
haider 
(BOB member since 2005-07-18)
Thanks for reply
What if I don’t want to create a predefined condition for every single LOV in the universe.
How would I make this work on report level. There must be something I have missed in the universe to make the ALL work correctly on reporting level.
Thanks,
Anna
lustigb (BOB member since 2007-01-29)
Try simulating the predefined condition.
Create a dummy object in universe which doesnt reference any table and type ‘ALL’ in the select box.
In the report query panel drag and create a condition for currency field which looks like this:
Currency inlist prompt(''Enter currency code or ALL for all')Then drag the dummy object into conditions pane and create this:
ALL inlist prompt(''Enter currency code or ALL for all')
Double click on “and” operator to change to “or”
Run the report, select ALL from LOV or desired values.
Hyder
haider (BOB member since 2005-07-18)
Hi All,
Many thanks for that information.
Many Regards to all.
Marco
mpinheiro 
(BOB member since 2005-03-21)
Hi Haider,
Thanks for your valuable information. But, i couldn’t get any lov after running.
It gives me an error : “The Query doesnot reference a table (QP0007)”
well , i am using Oracle 10g, BO6.5.1
Thank You
Mond (BOB member since 2006-07-31)
Hello Everyone,
I did this before but i am missing something.
I have a object called department and i need to have a option in the prompt so that user can select one department or ALL from the list of values.
What i did: I clicked properties and edit and added in sql
UNION
SELECT ‘ALL’
but it throws me error.
Our database is DB2. I did union select ‘ALL’ from dual in Oracle.
Any thought on this?
Thanks in advance
Santhoshini
santhoshini (BOB member since 2007-11-08)
Try selecting from any of your tables. It should be fine as you are selecting a literal value from it. Pick a small one of course or create your own dummy table like dual. i haven’t tried it on db2 though.
maverick976 
(BOB member since 2004-07-06)
thanks.
I am confused now.I am just wondering why should we create a dummy table in the universe. because i did this before in the universe where database was ORACLE. In that case i haven’t create a dummy table.
Can you please provide some input how to see ALL in my prompt condition for the department
Thanks in advance
santhoshini (BOB member since 2007-11-08)
Because you had dual and you don’t have it in SQL server.
From what I see you have everything that you need, in this post, to do this.
Mak 1 (BOB member since 2005-01-06)
Does db2 have dual table? Our database is DB2
Thanks
Santhoshini
santhoshini (BOB member since 2007-11-08)
Nope :).
Either create a single row table for thi,s or associate it with a table in your schema that has a small row count.
Mak 1 (BOB member since 2005-01-06)