"All" option in the LOV

[system]

Can anybody explain in details that how to implement ‘All’ option in the LOV of an object? I am working on SQL Server2005.

---

abhishekpatnaik (BOB member since 2006-02-15)

[system]

From Designer’s FAQ:

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]

That portion i have understood that how to add “ALL” in the LOV but now how do i calculate my query for all the LOV. Say i have 3 city name in the LOV. After i select “ALL” option in the LOV my report should give me the result for all the 3 cities. Please explain the steps involved

---

abhishekpatnaik (BOB member since 2006-02-15)

[system]

There is another Designer’s FAQ that explains it:

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…

---

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

[system]

Thanks a lot. It worked. Now i have another query. If i want to have casading prompt with all option how do i achieve it? Say I have 3 levels of hierarchy Countr–>State–>City. User can select a particular country and would like to see all the state and city for it. Or can select a country and then state and a particular city. How do i do it? How do i apply ALL option here in the LOV?

---

abhishekpatnaik (BOB member since 2006-02-15)

[system]

Hi,

Dave Rathbun presented a way how to use “ALL” with cascading prompts in his last year presentation at the BO user conference in Orlando. There were two parts of the presentation:
http://www.dagira.com/presentations/2007/insight/2007_10_ninja_part_one.zip
http://www.dagira.com/presentations/2007/insight/2007_10_ninja_part_two.zip

Check both of them as I am not sure in which of them it is.

---

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