BusinessObjects Board

Show 'ALL' in LOV in a prompt

building semantic layer
#1

Hello All,

I have put a prompt in the ‘WHERE’ clause of an object and the code is as follows:

@Select(Location Data\Tax Entity) in @Prompt(‘Enter State Abbreviation or * for ALL’,‘A’,‘Location Data\Tax Entity’,multi,free)
OR
‘*’ in @Prompt(‘Enter State Abbreviation or * for ALL’,‘A’,‘Location Data\Tax Entity’,multi,free)

I am having two problems. First is that I want to show * in the LOV. How can I achieve that, Second is that when the user puts that object in Deski, it shows all the values in LOV (of cource except *) but the LOV is not shown in webi. I checked the box ‘Export with universe’. It still doesn’t work. The sql for LOV is as follows:

SELECT DISTINCT
PRODUCTION.V_TAX_CODE.TAX_ENTITY
FROM
PRODUCTION.V_TAX_CODE
WHERE
( PRODUCTION.V_TAX_CODE.TAX_ENTITY in @Prompt(‘Enter State Abbreviation or * for ALL’,‘A’,‘Location Data\Tax Entity’,multi,free)
OR
‘*’ in @Prompt(‘Enter State Abbreviation or * for ALL’,‘A’,‘Location Data\Tax Entity’,multi,free) )
ORDER BY
1

How can I modify the sql for LOV so that it shows * and other values?

Thanks a lot!

Purnima Sharma

prnmsharma :us: (BOB member since 2008-03-18)

#2

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

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

#3

Hello Dave,

I read your article and did everything accordingly but it still didn’t work. I could not check the button ‘Do not generate sql before running’. It kept giving me error. May be, we are using older version.

Someone suggested the following sql for LOV and it worked well. I am just putting it here so it may help someone.

SELECT DISTINCT

case PRODUCTION.V_TAX_CODE.TAX_ENTITY WHEN PRODUCTION.V_TAX_CODE.TAX_ENTITY then ‘ALL’ else PRODUCTION.V_TAX_CODE.TAX_ENTITY end

FROM
PRODUCTION.V_TAX_CODE

UNION

select distinct PRODUCTION.V_TAX_CODE.TAX_ENTITY from PRODUCTION.V_TAX_CODE

prnmsharma :us: (BOB member since 2008-03-18)

#4

What you wrote is the second (and preferred) solution posted in the FAQ. :sunglasses:

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