OBIEE: How can I make a filter that supports "LIKE" and "IN", so for example, the user can enter in the analysis prompt either A3% or A31;A34;A3C
1) This works for IN: UPPER("Fund Status - Direct"."Proj Nbr") IN (@{V_PROJ_NUMBER}[UPPER('@')]{UPPER("Fund Status - Direct"."Proj Nbr")})
2) This works for LIKE: UPPER("Fund Status - Direct"."Proj Nbr") LIKE (@{V_PROJ_NUMBER} [UPPER('@%')]{UPPER("Fund Status - Direct"."Proj Nbr")})
3) When I try to combine them in the Criteria filters, the LIKE filter crashes when user enters a delimited string such as A1;A2
11 Answer
a) Having to force UPPER clearly states a data issue which should be adressed beforehand. This isn't helpful in self-service analytics.
b) You know that you can configure prompts so that the user himself can choose the operand?
c) You know that OBIEE also does google-style typeahead prompts? But that of course is something which will always work better when your data is well defined and no crutches like UPPER come into play.
This was introduced in 2010.
tl;dr - always give preference to out-of-the-box functionalities and try to tackle issues at their root rshter than hacking around the issues with custom code
5
