Filter Analysis based on substring of presentation variable

Question

I have a presentation variable in a prompt. The variable is a select statement which is picking up a field that is concatenated so that the user has a clue of what they are picking up. What I'm trying to do is intercept the presentation variable so i can do a substring on it and only grab the first two characters.  Can i do this ? In my filter Ive tried doing this

"xxx"."xxxx Code Level 1" IN ( LEFT(@{ACCT_TYPE}['@'],2){''}   )

to grab the first two characters of the string, but yet it keeps failing. If I remove the "left" function, everything works but I get the full string.

Is there any way to do this?

I essentially just want to grab the first two characters of the presentation variable.

Gianni Ceresa · Answer

Aren't you having a "modelling" issue?

Your reason for concatenating things in the prompt seems to be "the user has a clue of what they are picking up": why is it like that?

Can't you use the "Descriptor ID column" in your RPD? So that the human-readable column has the "technical" 2 characters column set as descriptor ID and you can use that one as filter?

That's how OBIEE deals with technical codes vs human readable values (knowing that in a database a technical code column works better with indexing and filters at the DB level than long human readable strings).

mlov83 · Answer

Thanks Gianni Ceresa ,

That is very helpful! I'm much closer to the results I'm looking for, however, is there anyway that I can use something substring or something similar to get the first two characters of every string while using the "in" clause. I really appreciate your help. This is very helpful!

Gianni Ceresa · Answer

LEFT isn't applied to every single elements of the variable (if multiple values) before to add ' ' and add them as options in the IN but it is applied to the final string itself, which means you are breaking it.

To use it with LEFT you must make your variable single value only and change your IN into a = .