BusinessObjects Board

Modify the Lov query to add ALL in list of values of prompt

building semantic layer
#1

How do i modify the LOV query to add ALL in the list of values of prompt.

Thanks for help.
2270

2270 (BOB member since 2009-07-10)

Optional prompt display BOXI R3
Date Lov problam
#2

Hi, please read the FAQ post that contains the answer to your question. Welcome to B:bob:B!

Dave Rathbun :us: (BOB member since 2002-06-06)

#3

when i click on the sql button in the edit mode this is what i see

SELECT DISTINCT
Resort_Country.country
FROM
Country Resort_Country
WHERE
( Resort_Country.country in (@Prompt(‘Select Country’,‘A’,‘Resort\Country Prompt’,) ) or ‘ALL’ in (@Prompt(‘Select Country’,‘A’,‘Resort\Country Prompt’,)) )

Thanks
2270

2270 (BOB member since 2009-07-10)

#4

It seems that you’re mising two techniques. The “Or” clause is used in a condition object in order to handle if a user selects ALL from the LOV. The post I linked to includes instructions on how to get ALL into the LOV so that the user can pick. :slight_smile:

There is another FAQ post next to the one I linked that talks about building the condition object…

Dave Rathbun :us: (BOB member since 2002-06-06)

#5

Thanks for the reply. I am not able to locate that post can you please send me the link for that faq post.

Thanks a lot.
2270

2270 (BOB member since 2009-07-10)

#6

Just read the FAQ. :slight_smile:

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? Answer

Now that my LOV lets me use ALL for all values, how do I get the ALL in the list? Answer

Dave Rathbun :us: (BOB member since 2002-06-06)

#7

when i edit the SQL LIKE THIS:

SELECT DISTINCT
Resort_Country.country
FROM
Country Resort_Country
WHERE
( Resort_Country.country in (@Prompt(‘Select Country’,‘A’,‘Resort\Country Prompt’,) ) or ‘ALL’ in (@Prompt(‘Select Country’,‘A’,‘Resort\Country Prompt’,)) )
UNION SELECT ‘ALL’ from Country Resort_Country

It gives me message that Microsoft Jet database engine cannot find the input table or query DUAL. make sure it exists and that its name is spelled correctly.

Thanks for help.
2270

2270 (BOB member since 2009-07-10)

#8

You’re missing the point I made earlier. This code:

( Resort_Country.country in (@Prompt('Select Country','A','Resort\Country Prompt',,) ) or 'ALL' in (@Prompt('Select Country','A','Resort\Country Prompt',,)) )

… does not go in the LOV query at all.

MS Access does not have a DUAL table. You would need to figure out some other table that only has one row (you can create one in the database if needed). I am assuming you’re using Access only for a prototype, and not developing a final solution using it as your database?

DUAL is an Oracle table only.

Dave Rathbun :us: (BOB member since 2002-06-06)