nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

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

Hippy

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

Find more posts tagged with

FDI

Needs Votes

Status: Needs Votes

Comments

Oracle Analytics Wizard-Oracle

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.

Krishna Prasad Kotti-Oracle

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

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

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 :

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

Try something like this:

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

Hi OAWiz-Oracle,

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

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%'))

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

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?