Adding ALL in object lov

[system]

I have an object Area in universe i am using this in report, here i want to add ALL so that if i select ALL in prompt all values of area should come in report.
How can implement this at universe level?
Please help me

---

irajeshkr (BOB member since 2009-02-05)

[system]

You can modify the SQL statement that generates the LOV.
For your [Area] object in Designer:
Go to the Properties tab --> Click Edit
Click the SQL button
Add a UNION ALL statement

For example:

SELECT Area
FROM Areas_Table
UNION ALL
SELECT 'ALL' 

This will add the ‘ALL’ option to the LOV along w/the list of Areas.

Now create a predefined condition that prompts the user, using the modified object that you just created:

–pseudocode
@Select(Area) IN @Prompt(Area Prompt) OR ‘ALL’ IN @Prompt(Area Prompt)
Add this condition to your query and you should be good to go.

---

jresendez (BOB member since 2004-05-03)

[system]

[Moderator Note: Moved from General Discussion to Semantic Layer / Universe Designer]

---

Marek Chladny (BOB member since 2003-11-27)

[system]

Thanks for your suggestion, But can you please little moew explain this step
@Select(Area) IN @Prompt(Area Prompt) OR ‘ALL’ IN @Prompt(Area Prompt)
because when i am creating this formula it is giving error.
Let me tell you my approach.
First i created an object EST_Area as you mention and then i am trying to use here @Select(Estimate \EST_Area) but this is giving error after parsing.

---

irajeshkr (BOB member since 2009-02-05)

[system]

Designer’s FAQs:

FAQ: Designer

Is there any way to set up a prompt that will allow a user to select one value, many values, or type ‘ALL’ for all values in a list? Yes. Below is a sample of the syntax required to allow a user to select one, many, or all values from a prompt. (@Select(Sales Geography\Country Group) IN @Prompt('Select Country Group or enter * for all groups','a','Sales Geography\Country Group',multi,free) ) OR ('*' in @Prompt('Select Country Group or enter * for all groups','a','Sales Geography\Co…

FAQ: Designer

Now that my LOV lets me use ALL for all values, how do I get the ALL in the list? Author: Dwayne Hoffpauir There are two approaches - overriding the LOV SQL, and creating a universe object. Overriding the LOV SQL Press the SQL button in the query panel of the LOV Add the phrase UNION SELECT ‘ALL’ FROM DUAL or similar dummy table for your particular database. For SQL Server (if you don’t care if the object parses or not) add SELECT xx From xx UNION SELECT ‘ALL’ (You don’t need a FRO…

---

Marek Chladny (BOB member since 2003-11-27)

[system]

Please see attachment for problem description and screen shots.
Thanks, Maineman
LOVpromptALL.doc (134.0 KB)

---

maineman65 (BOB member since 2007-02-14)

[system]

My description left a little to be desired. It’s in the attachment. Basically, I can’t get the ALL option for LOV to work.
Thanks, Maineman

---

maineman65 (BOB member since 2007-02-14)