Filter Analysis based on substring of presentation variable — Oracle Analytics

Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Filter Analysis based on substring of presentation variable

Received Response
53
Views
3
Comments
mlov83
mlov83 Rank 4 - Community Specialist
edited Aug 13, 2024 5:18PM in Oracle Analytics Cloud and Server

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.

Answers

  • Gianni Ceresa
    Gianni Ceresa Mod

    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 = .

  • mlov83
    mlov83 Rank 4 - Community Specialist

    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
    Gianni Ceresa Mod

    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).