Creating a prompt where user can select "all"

[system]

Hello,

The word “all” appears in the LOV and the user can select all of the values without selecting them individually?

I know I have seen this done before, but there doesn’t seem to be a current report in our repository where this was done, and I have spent a little time searching in Bob and on the BO technical site without finding the solution.

My recollection is kind of vague, but it seems like an object was created, maybe in the universe, using an @prompt or something, and it allowed the users to select all of the values in the LOV by clicking on the word “all.”
Could someone point me to information on this?

Thanks,

---

ninabrenner (BOB member since 2002-08-15)

[system]

Check the Designer FAQs… There is one there detailing exactly what you want.

-RM

---

digpen (BOB member since 2002-08-15)

[system]

This is a thread on the topic I found useful

FAQ: Designer

[b:1d54a67631]Designer FAQ[/b:1d54a67631] [url=http://www.forumtopics.com/busobj/viewtopic.php?p=495481#495481]How should I impose a WHERE clause in my object(s) in the universe?[/url] [url=http://www.forumtopics.com/busobj/viewtopic.php?p=43275#43275]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? [/url] [url=http://www.forumtopics.com/busobj/viewtopic.php?p=99071#99071]Now that my LOV lets me use ALL for al…

---

Paul Shovlar (BOB member since 2002-09-05)

[system]

This is very useful

@Paul Shovlar

FAQ: Designer

Additionally, if you want the word ‘ALL’ to appear in your LOV, you can do a UNION in your LOV query, so that you append the constant ’ ALL’ to the top of the list (the space is used to have this value sort first above all other values). If you use ’ ALL’, don’t forget to the space when you create your @Prompts, etc.

Cheers!

---

lgonzalez (BOB member since 2002-07-17)

[system]

First you need to modify your LOv, make it custom

If using Oracle:
Select distinct var1 from tab1
Union
Select ‘All’ from dual

If using SQL Server:
Select distinct var1 from tab
Union
Select ‘ALL’

Than you have to create a prompt

var1 in @Prompt(‘enter field’,‘A’,‘point it to lov’,MULTI,FREE) or ‘ALL’ in @Prompt(‘enter field’,‘A’,‘point it to lov’,MULTI,FREE)

Hope it helps

Reema

---

reemagupta (BOB member since 2002-09-18)

[system]

Thanks, Reema, for outlining the procedure for the Union idea.

@reemagupta

First you need to modify your LOv, make it custom

If using Oracle:
Select distinct var1 from tab1
Union
Select ‘All’ from dual

If using SQL Server:
Select distinct var1 from tab
Union
Select ‘ALL’

Than you have to create a prompt

var1 in @Prompt(‘enter field’,‘A’,‘point it to lov’,MULTI,FREE) or ‘ALL’ in @Prompt(‘enter field’,‘A’,‘point it to lov’,MULTI,FREE)

I did not want to introduce it because I didn’t want to get too detailed and confuse people, but now that you’ve started…

1. You have to decide how to add that darn ALL to the Union. Are you going to hard-code the SQL and use ‘Do not regenerate SQL’? Or will you create a dummy object with the constant ’ ALL’ so that you can use it in the query panel for this and any other custom LOV you create in the same way.

2. You really need to think about the constant you use in the Union and the @Prompt condition. Once you are able to create your custom LOV, if you don’t take care of the sorting with a leading space or special character, the word ‘ALL’ may sort in between values ‘ALBERT’ and ‘ALMOND’. If you want ‘ALL’ to appear at the top of the LOV, you will have to lead the value with a space or special character, and then don’t forget to use the same in the @Prompt.

Cheers!

---

lgonzalez (BOB member since 2002-07-17)

[system]

@reemagupta

var1 in @Prompt(‘enter field’,‘A’,‘point it to lov’,MULTI,FREE) or ‘ALL’ in @Prompt(‘enter field’,‘A’,‘point it to lov’,MULTI,FREE)

make sure you get the quotes around ‘point it to lov’ … if you just double-click the object from the classes and objects section, it leaves the single quotes off … causing the always hard to debug, “UNV0023: Invalid Definition” Error

---

Chris Pohl (BOB member since 2002-06-18)

[system]

THAT was too obvious a place to look–thank you digpen and also Cindy Clayton for posting the wildcard prompt code. That’s already working and it’s so simple.

@digpen

Check the Designer FAQs… There is one there detailing exactly what you want.

-RM

---

ninabrenner (BOB member since 2002-08-15)

[system]

Chris Pohl wrote:

make sure you get the quotes around ‘point it to lov’ … if you just double-click the object from the classes and objects section, it leaves the single quotes off … causing the always hard to debug, “UNV0023: Invalid Definition” Error

LOL–how true-- I already went there and debugged it before reading your post

---

ninabrenner (BOB member since 2002-08-15)

[system]

After defining these prompts, how in the report does one reflect the values selected? Using a prompt defined in the report it looks like:

="Region(s): "&UserResponse (“dataprovider” , “Select the Region:”)

How do you get the prompt from the universe into this?

---

ninabrenner (BOB member since 2002-08-15)

[system]

If your data provider is using a prompt (no matter where this prompt is orignally coming from - be it as a prompt by building a condition in your data provider, be it a universe object which is defined as a @prompt in the Select box, or a predefined condition, or a self-join) you can refer to that prompt just as you would refer to any “normal” prompt using the UserResponse function in BO Reporter (or in BO Reporter go to menu Insert| Special Field| Query prompt…).

---

Andreas (BOB member since 2002-06-20)

[system]

Thank you, Andreas

---

ninabrenner (BOB member since 2002-08-15)

[system]

@lgonzalez

1. You have to decide how to add that darn ALL to the Union. Are you going to hard-code the SQL and use ‘Do not regenerate SQL’? Or will you create a dummy object with the constant ’ ALL’ so that you can use it in the query panel for this and any other custom LOV you create in the same way.

OK…I’ve not done this before and this is what I want. I was able to edit the LOV and add the ’ ALL’ from dual but then I have to not regenerate the SQL, right? If I don’t regenerate the SQL, will they still be able to refresh the LOV if values are added? Or is it stuck there?

It looks like the preferable method would be to put the ’ ALL’ in a constant. How can I do that?

---

Eileen King (BOB member since 2002-07-10)

[system]

Hi, Eileen.

Here’s the procedure you would need to follow in Designer for the object:

1. Go to the Object Properties and in the Properties tab, click on the Edit button.
2. Click on the SQL button to modify the LOV query’s SQL.
3. Insert a line above the “SELECT DISTINCT” statement and enter the following:

SELECT ' ALL' FROM <table_name>
UNION

For the table_name, you can use the same table as the object’s, or, in Oracle, you may use DUAL. With the Union, this adds the word ’ ALL’ to the result set for the LOV.
4. Click on the checkbox for “Do not regenerate SQL.”

Users can still refresh the LOV to get new values, and with this last checkbox enabled, the SQL will not get regenerated and so it will always use the UNION to retrieve new results. I would also recommend checking the object property for “Export with Universe” so that users can get this modified defintion to the LOV.

Cheers!

---

lgonzalez (BOB member since 2002-07-17)

[system]

Thanks!

The mistake I was making was trying to do the union with the @Select object rather than with the actual table.field object.

Now I’m having one strange result…it displays just fine in the LOV, but when it displays back on the screen, it’s displayed with quotes so that I’m seeing " ALL". Can I make that go away? If I select any of the other answers, it’s fine…

---

Eileen King (BOB member since 2002-07-10)

[system]

This is weird…it must have something to do with “in list”! When I use the prompt with and ‘equal to’ it displays back fine…with I use ‘in list’ it comes back with double quotes around " ALL"!

I’m on 5.1.6…anybody know anything about this?

---

Eileen King (BOB member since 2002-07-10)

[system]

Hi, Eileen.

Did you use single quotes or double quotes around the word ALL? You should be using single quotes.

What database are you using? Maybe it’s DB-specific. I have tried the procedure as documented on Access, SQL Server, Oracle, and none presented the ALL with double-quotes.

Also, maybe you can experiment with different characters. The space in ’ ALL’ is so that it sorts before your other values. You might try using ‘ALL’ with no spaces, but this would add ALL in between your other values, so maybe try a different character other than a space. The database or connection is probably adding the double-quotes to deal with the space.

Cheers,

---

lgonzalez (BOB member since 2002-07-17)

[system]

I’m using Oracle. Here’s the code for the LOV. I tried it with and without the second ALL…

SELECT ALL ' ALL' FROM DUAL 
UNION 
SELECT DISTINCT
  UO_WEB_MENU.MENU_NM
FROM
  UO_WEB_MENU
WHERE
  ( 
  UO_WEB_MENU.MENU_NM  NOT IN  ('NONE')
  )

By taking out the space, it seems to work fine…

---

Eileen King (BOB member since 2002-07-10)

[system]

@Eileen King

This is weird…it must have something to do with “in list”! When I use the prompt with and ‘equal to’ it displays back fine…with I use ‘in list’ it comes back with double quotes around " ALL"!

That is weird.
The LOV query should be completely independent of your data query.

Is your column MENU_NM numeric? This is all I can think of… that thru the Union, you are trying to combine string data with numeric data; Oracle requires the same datatype.

Cheers,

---

lgonzalez (BOB member since 2002-07-17)

[system]

I was just looking following this question and tried to do this with:

SELECT ’ ALL’ FROM DUAL
UNION
SELECT DISTINCT …

But I am getting an error saying " expression must have same datatype as corresponding expression( ORA-01790)"

Whats could be the problem
VA

---

vanneb (BOB member since 2002-11-14)