Issue with multi-select filter in report

Kauan_Bohn

I'm developing a report and encountered a problem when trying to select items in a filter. In its current configuration, I can extract either ONE specific item or ALL items, but I can't select multiple specific items.

Here's my WHERE clause:
WHERE
xah.application_id = 707
AND xal.application_id = 707
and ccd.cost_book_id = '300000082235861'
AND ccov.cost_org_name IN (:P_FILIAL)
AND gcc.segment4 IN (:P_CONTA)
AND xah.period_name IN (:P_PERIODO)
AND (
:P_ITEM IS NULL
OR esi.item_number IN (:P_ITEM)

Does anyone know how to modify this to work for all options? (All items, one item, or multiple selected items?)

Bhaskar Konar

Hi @Kauan_Bohn,

Additionally, can you please try the following one?

AND ( esi.item_number IN (:P_ITEM) OR 'All' IN (:P_ITEM || 'All')
)

Hope this help.

Thank you.

Bhaskar Konar

https://community.oracle.com/products/oracleanalytics/discussion/25806/issue-with-multi-select-filter-in-report

Hi @Kauan_Bohn,

Can you please try the following code instead of your last 'AND Condition'

AND (

esi.item_number in (:P_ITEM) IS NULL 

OR esi.item_number IN (:P_ITEM)

)

Hope this help.

Thank you.

Kauan_Bohn

Unfortunately, the error indicates a missing right parenthesis

ORA-00907: missing right parenthesis

WHERE
xah.application_id = 707
AND xal.application_id = 707
and ccd.cost_book_id = '300000082235861'
AND ccov.cost_org_name IN (:P_FILIAL)
AND gcc.segment4 IN (:P_CONTA)
AND xah.period_name IN (:P_PERIODO)
AND (

esi.item_number in (:P_ITEM) IS NULL

OR esi.item_number IN (:P_ITEM)

)
AND (
(CASE
WHEN xec.name = 'WIP Resource Transaction' THEN NVL(xal.accounted_dr, 0) - NVL(xal.accounted_cr, 0)
WHEN cceb.cost_element_code = 'GGF' THEN NVL(xal.accounted_dr, 0) - NVL(xal.accounted_cr, 0)
ELSE 0
END)
+
(CASE
WHEN xec.name = 'WIP Material Transaction' THEN NVL(xal.accounted_dr, 0) - NVL(xal.accounted_cr, 0)
WHEN cceb.cost_element_code = 'MATERIAL' THEN NVL(xal.accounted_dr, 0) - NVL(xal.accounted_cr, 0)
ELSE 0
END)
) != 0

Bhaskar Konar

https://community.oracle.com/products/oracleanalytics/discussion/comment/67818#Comment_67818

Hi @Kauan_Bohn,

Can you please share your data model here so that we can try to replicate the issue in our instance.

Thank you.

Kauan_Bohn

That works for me. Thank you very much for your help.

Best regards,

Kauan Bohn

Rich Merkel

In past we had success for multi select parameters (also exceeding 1000 values), using similar to below.

in parameter options, choose multiple selection, can select all, NULL value passed

AND ((esi.item_number = (COALESCE(null, :P_ITEM, esi.item_number))) or (esi.item_number in (:P_ITEM)))

^^give this a try, we use similar for project number in multi-select parameter

AND ((PRJ.segment1 = (COALESCE(null, :P_PROJECT_NUM, prj.segment1))) or (prj.segment1 in (:P_PROJECT_NUM)))

^^^this is the code in data model we use, and parameter/lov setup like below.