Categories
- All Categories
- Oracle Analytics Learning Hub
- 19 Oracle Analytics Sharing Center
- 18 Oracle Analytics Lounge
- 232 Oracle Analytics News
- 44 Oracle Analytics Videos
- 15.9K Oracle Analytics Forums
- 6.2K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 87 Oracle Analytics Trainings
- 15 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Issue with multi-select filter in report
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?)
Best Answer
-
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.
1
Answers
-
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.
0 -
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)
) != 00 -
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.
0 -
That works for me. Thank you very much for your help.
Best regards,
Kauan Bohn0 -
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.
0
