Oracle Fusion Data Intelligence Idea Lab

Home› Oracle Analytics› Oracle Analytics Idea Labs› Oracle Fusion Data Intelligence Idea Lab

Categories

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

FAW: to replicate a filter from Oracle BI Answer that use ‘contains any’ operator into OAC

Needs Votes
139
Views
6
Comments
Hippy
Hippy Rank 2 - Community Beginner
edited Sep 3, 2024 5:17PM in Oracle Fusion Data Intelligence Idea Lab
SR contains any.docx

Hi,

we have this version FAW 23.R4.

Customer has old system using /analytics prompts and analysis.

When the prompt is passed it is filtering the data that contains clob or bclob data .

In classic home this is based on contain any fitler with presentation variables.

Customer migrated to faw and using oac dv and using expression filters or parameters and query is mainly on how to convert this requirement.

That same reports cannot be used in faw oac classic home as a workaround and as per business requirements we are converting all the classic reports to oac dv .

The field I want to filter has these values inside : '9047_IP|9047_bis|9047_def|9047'

In the database the condition is translated like this :

(("Commesse a piano"."X_COMMESSE_FIGLIE" LIKE '%9047_bis%' OR

"Commesse a piano"."X_COMMESSE_FIGLIE" LIKE '%9047_IP%' OR

"Commesse a piano"."X_COMMESSE_FIGLIE" LIKE '%9047_def%')))

We have opened a SR and support suggests opening an idea request in idea lab . 

We ask that this functionality be created in OAC as well.

Thanks Anto

Tagged:
2
Up
2 votes

Needs Votes · Last Updated

Comments

  • Oracle Analytics Wizard-Oracle
    Oracle Analytics Wizard-Oracle Rank 5 - Community Champion
    edited Jan 16, 2024 4:48PM

    Hi Anto,

    You can use the "create expression filter" feature in OAC Data Visualization to do a 'contains any' filter in the workbook / canvas filters section.

    image.png


  • Krishna Prasad Kotti-Oracle
    Krishna Prasad Kotti-Oracle Mod

    Below are additional details


    https://docs.oracle.com/middleware/1221/biee/BIVUG/GUID-75CA62BC-5FB4-4889-9847-9CB9BCE1D0FB.htm


    https://docs.oracle.com/en/cloud/paas/analytics-cloud/acubi/apply-different-filter-types.html#GUID-367ADE2B-0D9D-44BB-9CCA-70D31787FD89

  • Hippy
    Hippy Rank 2 - Community Beginner

    Hi OAwiz-Oracle,

    we tried to use the expression filter but there is no way to replicate the functionality ‘contains any’ operator.

    We tried to write the expression like this

    image.png
    But the server constructs the condition like this and it's not good, it's syntactically incorrect:
    CAP_DW_PROJECT_PIANO_D"."Commesse figlie" like ('9047_capital','9047_def')))

    We tried to write the expression like this :

    image.png

    but the validation fails.

    We need to focus on the 'contains any' functionality.

    Where the "COMMESSE_FIGLIE" field is a CLOB type field that contains many values: 9047_IP|po47_bis|9047_def|9047

    In the database the condition is translated like this :

    (("Commesse a piano"."COMMESSE_FIGLIE" LIKE '%9047_capital%' OR

    "Commesse a piano"."COMMESSE_FIGLIE" LIKE '%9047_def%' OR

    "Commesse a piano"."COMMESSE_FIGLIE" LIKE '%po47_bis%')))

    We have to do the same table.field LIKE '%comessa operativa param%'

    IF pv_comessa_operativa has more values then it add the OR

    I hope I have explained myself better now.

    Do you have any suggestions for constructing the expression correctly?


    Thanks Anto

  • Oracle Analytics Wizard-Oracle
    Oracle Analytics Wizard-Oracle Rank 5 - Community Champion

    Try something like this:

    image.png

    Insert your presentation variable name here --> <PresVar>


    If above method does not work, what I would also suggest is to grab the logical SQL from the OAC classic analysis and then use that to create your workbook in DV using Developer Options

  • Hippy
    Hippy Rank 2 - Community Beginner

    Hi OAWiz-Oracle,

    We have already tried but it doesn't work if the variable has multiple values in the filter:

    image.png


    image.png

    Stato: HY000. Codice: 43275. [nQSError: 43275] Messaggio restituito da OBIS [ecid:40c31139-f8ff-48bd-9d2e-de6d5cb4a80b-000613cd,0:1:2:4 ts:2024-01-18T12:26:18.535+00:00].(HY000)Stato: HY000. Codice: 27002. [nQSError: 27002] Accanto a <,>: errore di sintassi (HY000)


    annuali e totali commesse piano')."CAP_DW_PROJECT_PIANO_D"."Commesse figlie" like CONCAT(CONCAT('%', ('9047','9047_def')), '%')OR XSA('m.zonelli@reply.it'.'CAP - Costi e ricavi annuali e totali commesse piano')."CAP_DW_PROJECT_PIANO_D"."Commesse figlie" like '%PIPPO%'))

    image.png

    For "If above method does not work, what I would also suggest is to grab the logical SQL from the OAC classic analysis and then use that to create your workbook in DV using Developer Options"

    Can you give an example?


    Thanks Anto

  • Oracle Analytics Wizard-Oracle
    Oracle Analytics Wizard-Oracle Rank 5 - Community Champion

    I just tried to replicate that myself to show you an example but it doesn't work for me anymore. In this case, you may have to stick with Classic for this report and/or use case.

    Unless anyone else has any ideas?