BusinessObjects Board

Accepting default value '%' in prompt to fetch all values

building semantic layer
#1

Hi,
I need to provide the user LOV’s on prompts with the flexibility to type in % and retrieve all values in the report. To achieve the same I have created a condition object in the universe with the following code.

(FUND.CODE IN @Prompt('Enter Fund ',‘A’,‘Fund Details\Fund Code’,Multi,Free) or ‘%’ IN @Prompt('Enter Fund ',‘A’,‘Fund Details\Fund Code’,Multi,Free) ).

When this condition object is included in the report and report is run I get the following error message.
Connection or SQL Sentence Error (DA0005)
Details shows - [Ardent][SQL Client] [RPC] error code=8100181001

The underlying database being used is Unidata.

Can someone pls help.

nikcmc (BOB member since 2005-11-28)

#2

Hello,
I don’t know the database Unidata, but I think you can try :

(FUND.CODE LIKE @Prompt('Enter Fund ',‘A’,‘Fund Details\Fund Code’,Mono,Free)

Jean-Philippe Maignel

jpmaignel :fr: (BOB member since 2005-06-20)

#3

Thanks. This works but this solution limits the user to select either one or all values (by entering %). The user wants a flexibility to select either one, multiple or all values.

nikcmc (BOB member since 2005-11-28)

#4

Search Optional Prompts. Its probably one of the most posted topics :mrgreen:

maverick976 :us: (BOB member since 2004-07-06)

#5

Try using something besides % as your “wildcard” and see if that helps.

Namlemez :djibouti: (BOB member since 2005-03-14)

#6

To get more information, Follow this link:

https://bobj-board.org/t/15227/2

BO_Chief :us: (BOB member since 2004-06-06)

#7

Hello

Just use this syntax

(GHSCJ5.GAB33D00.APPL_CTRL_FLG LIKE @Prompt(‘APPL_CTRL_FLG’,‘A’,
‘Ghscj5 Gab33d00\Appl Ctrl Flg’,MONO,FREE) ||’%’)

let me know after it works

Thanks
kiran

reddy108 (BOB member since 2005-09-08)

#8

I tried using other characters such as ‘*’, ‘ALL’ as wildcards but it doesnt work.

Kiran,
When i create a conditional object using the code suggested by you the object does not parse. If i use this object in a report i get an error .

nikcmc (BOB member since 2005-11-28)

#9

If you use this it will not find null values (Oracle 9i, so make sure you account for those. We use *.

(@Select(Campaign\Campaign Status Name) IN @Prompt(‘Select Campaign Status’,‘A’,‘Campaign\Campaign Status Name’,multi,constrained) OR @Select(Report Objects\All Campaign Status) in @Prompt(‘Select Campaign Status’,‘A’,‘Campaign History Report\Campaign Status Name’,multi,constrained))

Object All Campaign Status is a literal ‘*’ selected from dual so it parses

maverick976 :us: (BOB member since 2004-07-06)

#10

Hi,

You can edit the LOV of the Fund Code object and add the following in the SQL script editor:

SELECT distinct FUND_CODE
FROM TABLE_NAME
union all
SELECT distinct “ALL”
FROM TABLE_NAME;

Click on the check-box “Do not generate SQL before running” and run the query.The value “ALL” should appear in your LOVs.

Now add a pre-defined filter with the folowing code:

FUND.CODE LIKE (CASE WHEN @Prompt('Enter Fund ',‘A’,‘Fund Details\Fund Code’,Multi,Constrained)=‘ALL’ THEN ‘%’ END)
By this mechanism users can either chose one, multiple or ALL from the LOV and run the query.

Let me know if it worked for you.

Cheers.

sumantapaul (BOB member since 2005-06-02)

#11

Hi,

The LOV SQL should be changed as mentioned by Sumant to including ‘All’ as one of the values using union or union all.

You can use your original code with a small change like below.

(FUND.CODE IN @Prompt('Enter Fund ‘,‘A’,‘Fund Details\Fund Code’,Multi,Free) or All’ IN @Prompt('Enter Fund ',‘A’,‘Fund Details\Fund Code’,Multi,Free) ).

This will accept ‘All’ or multiple values.

Saraswathi

Saraswathi :australia: (BOB member since 2004-05-17)