BusinessObjects Board

ALL prompt in 6.5

building semantic layer
#1

All,
I read some where that we can implement the ALL prompt in a different way to take advantage of the index in 6.5. I searched the forum and I didn’t find it any where. I know how to implement ALL prompt in BO5. It creates a SQL like
COLUMN in () OR ‘’ In '’ . Since the second condition is always true, it never uses the index. I would really appreciate if someone can help me with this.
Thx

talk2kesh (BOB member since 2004-08-17)

#2

Basically you have got the syntax.

  1. First you need to decide on the wildcard value for a prompt (ALL, *, whatever)

  2. Then you can define a dummy object in your universe with the SQL set to your dummy value. In the Select field, type:


'ALL'

You can call your object something like “ALL query response object”

  1. When you create a condition on a report, you drag this object over for the second part of the condition, as follows:

Prompt text: Select invoice numbers or type 'ALL' for all invoices

Condition: Invoice number in list "Select invoice numbers or type 'ALL' for all invoices" OR ALL query response object in list  "Select invoice numbers or type 'ALL' for all invoices"

Hope this helps,
Judy

JMulders :us: (BOB member since 2002-06-20)

#3

Judy,
This syntax is from BO5. My whole point is to avoid the use of OR in SQL as it will not use the index.
Thx,

talk2kesh (BOB member since 2004-08-17)

#4

I misunderstood you then. I think you can try playing around with substituting the actual table.column for the ALL. I don’t have any more specifics for you but I think I have seen that done somewhere.

Good luck
Judy

JMulders :us: (BOB member since 2002-06-20)

#5

Will this post help you.

https://bobj-board.org/t/60982

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