Search prompt — Oracle Analytics

Oracle Analytics Cloud and Server

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

Search prompt

Received Response
11
Views
3
Comments
mmajdana1
mmajdana1 Rank 6 - Analytics Lead

Hi I am Using OBIEE 12.2.1.2.0

I hava a request to make a prompt where i can select one or more persons.

Thing is the list of valuies contains approx 3000 persons.

Is there a possibility to make a prompt like this, or something simmilar?

pastedImage_0.png

Kr, Matija

Answers

  • Jerry Casey
    Jerry Casey Rank 6 - Analytics Lead

    I've done this with a two page dashboard prompt.  The first page is simply a variable prompt called varNameSearch.

    pastedImage_0.pngpastedImage_0.png

    The second page column prompt  populates a series of check boxes using SQL and a regular expression to find employee names containing any comma separated values in 'varNameSearch'.

    SELECT "People Profile"."Employee Name" FROM "People-Free" WHERE EVALUATE('REGEXP_INSTR(%1,%2)',UPPER("People Profile"."Employee Name"), UPPER(REPLACE('@{varNameSearch}{xxx}',',','|'))) > '0'

    This REGEXP returns a count for the number of times it finds any of the values in varNameSearch in a record, so return records  >0.

    The UPPER function allows users to enter either upper or lower case values in the first prompt page.

    The REPLACE function is replacing any commas in the first prompt with a single pipe "|", which the REGEXP uses as a value separator.

    pastedImage_2.png

    It looks like this:

    pastedImage_3.png

    Select "Apply", then "Next"

    It brings up any names with casey or savoy in them:

    pastedImage_5.png

    Check the ones you want, hit Apply, to run your query.

  • Jerry Casey
    Jerry Casey Rank 6 - Analytics Lead

    Did you try this?

  • mmajdana1
    mmajdana1 Rank 6 - Analytics Lead

    Hi i did something simmilar, but this was great template, thank you very much!