BusinessObjects Board

FAQ: Prompt allows to select one value, many values or %

Hi folks,

With the help of following FAQ I was able to solve my problem: FAQ: Designer

There’s one more additional question regarding this. Hope there’s a solution. When we are entering data with % or just one value and multiple values. The results show up fine.

Is there a way to do something like this in the same prompt - VAL%, which will show everything that starts with ‘VAL’. When I try and do that it says, “No data to fetch”

Thanks :mrgreen:


BOBeeJ (BOB member since 2005-07-08)

The reason it does that is because, in order to do pattern-matching, you have to use the Matches Pattern (or Different from pattern) operator – with just one value.

When you use a different operator like Equal to or In List – the % value is not treated as a wildcard. Instead, it is a literal. So, you get No Data to Fetch because you do not literally have any rows with VAL% in the value.


Anita Craig :us: (BOB member since 2002-06-17)

Thanks Anita for the quick response. I understand what you are saying, I wish my client would understand the same thing :rotf: But is there a solution to it? Does anyone have any ideas?

I mean my client sometimes would want to select one value or multiple values or All. So I had no option but to go with that code and now they also want something that matches pattern. eg: “VAL%”.

I tried initially at the Reporter level where:

  1. LIKE(Matches Pattern) would allow me to select: One Value or VAL%. Also I noticed that %(All) does not retrive NULL values NOT SURE WHY (values not entered in the database for particular object) in one of the objects, which was a major hinderance. It does not allow to select Multiple Values

  2. IN LIST would allow me to select: One Value or Multiple Values. It does not allow me to select [i]VAL% or %/i.

So the best option was to go with Designer and implement something that would give me more than the earlier two options. Also the Designer code would retrive NULl values when filled with %(exactly what we wanted). Since they always want more now we want to go for VAL%.

Please correct me if I am wrong somewhere or if you or if someone has more knowledge than I have in any of the topics I am talking about.

Thank you once again.


BOBeeJ (BOB member since 2005-07-08)

I have implemented this kind of logic in one of the complex report in BO 6.1.

Try to implement following condition at universe level.

(1=(CASE WHEN upper(PROMPT)=‘ALL%’ THEN 1 ELSE 0) * 1)
OR (1=(CASE WHEN upper(PROMPT)=‘ALL’ THEN 1 ELSE 0) * 1)
OR (1=(CASE WHEN INSTR((PROMPT),’%’) > 0 THEN 1 ELSE 0) * (CASE WHEN TABLE.COLUMN LIKE (PROMPT) THEN 1 ELSE 0 END))
OR (1=(CASE WHEN INSTR((PROMPT),’%’) = 0 THEN 1 ELSE 0) * (CASE WHEN TABLE.COLUMN IN (PROMPT) THEN 1 ELSE 0 END))

Bottom line, we got to return true (1=1) or false (1=0).
I tried to implement simple math like (1= (1/0) * (1/0)).

Only one suggestion is after successfully implementation of such logic take help from DBA to tune the data base by defining functional index etc.

Hope this will help you.
Sujit Patange.


sujit_patange (BOB member since 2005-07-19)