In Business Objects Query Filters, it is possble to say use an IN condition with multiple values
where country in list ('France', 'USA', 'Canada) etc.
However when we have to match sub-strings the matches pattern only allows you to input one value
This means, i cannot do
country matches pattern like ('%SA','Can%')
However such a contains functionality i am told by users exists in other BI products.
Can any one suggest any alternatives or work-arounds?
Based on the scenario you've given, there are no other alternatives. If other tools provide this functionality, then more "stuff" must be going on behind the scenes. In its basic form, WebI is building an SQL query based on actions that the user takes in the "Edit Query" mode. Though you want to use pattern matching with an "in" construct, this is not even possible in an SQL query. There are two solutions:
- use matches pattern multiple times (and link together via "or")
- create a substring of the data field adding it as a new object in the universe (or create a new column of data that is permanently stored in your database in the substring form), however, the substring or new column of data will be dependent upon consistent layout and use of the data (for example, "USA or "FRANCE" always appears in the nth position of the existing column, etc, etc).
Other tools may be able to "suck in" a an input of " like ('%SA','Can%')" and then extrapolate it to multiple like/or statements, but WebI in its current form does not do it that way.