Categories
- All Categories
- 4 Oracle Analytics Videos
- 13.7K Oracle Analytics Forums
- 5.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 56 Oracle Analytics News
- 31 Oracle Analytics Trainings
- 52 Oracle Analytics Data Visualizations
- 2 Oracle Analytics Career
- 2 Oracle Analytics Industry
- Find Partners
- For Partners
Passing multiple values in BI Publisher text type parameter
Summary
Passing multiple values in BI Publisher text type parameter
Content
I am using a presentation variable to pass values to a BIP report. The BIP parameters have been changed to TEXT to allow for this to happen. Single selections are working just fine. The problem comes when I try passing multiple values. I get an error. How can I successfully pass multiple values to the BIP report? I have checked the option below the parameters which says user can select multiple values but no joy. Anyone?
Answers
-
-
Hi,
What is your BIP version.
Please provide more details for the error you are facing, kindly upload screenshot showing the error
Regards,
Kavitha
0 -
To make reports work with Multiple Select Prompts/Parameters, we need to handle that logic inside the sql dataset. Usually this functionality can be achieved using IN operator in WHERE clause .
Example:
SELECT * FROM W_INVENTORY_PRODUCT_D
WHERE PRODUCT_NUM IN (:P_NUM)
Above query works well until P_NUM parameter holds less than 1000 literals. Whenever it reaches more than 1000 then sql throws exception like as mentioned above. Because IN operator won’t allow more than 1000 literals or hard coded values.
Let say we passed two values(Part_ABC,Part_XYZ) from prompt,then in side BI Publisher query will be generated as below.
SELECT * FROM W_INVENTORY_PRODUCT_D
WHERE PRODUCT_NUM IN (:P_NUM4118,:P_NUM4119,'X')
Bind Variables ...
1: P_NUM4118:Part_ABC
Bind Variables ...
2: P_NUM4119:Part_XYZ
So for each selected value XDO engine creating Bind Variable and assigning appropriate value at run time .Hence if we select more than 1000 values from the prompt then XDO engine will create more than 1000 Bind variables where it will cause IN operator exception.
3.Resolution
To make it work for even more than 1000 values, need to alter sql dataset code. This change is very small and of course worth.
Parameter Definition:
Create a Parameter :P_NUM
and enable
Multiple Selection
Can Select All (Null Value Passed) options
Dataset definition:
SELECT * FROM
(SELECT PRODUCT_NUM FROM W_INVENTORY_PRODUCT_D)
WHERE
PRODUCT_NUM IN (:P_NUM) OR LEAST(:P_NUM) IS NULL
Here LEAST() is a function that returns the least value . NULL will be returned if it has null as argument.
Example
LEAST(1,3,5) returns 1
LEAST (1,null,7) returns null
LEAST(null) returns null
As per the Parameter definition NULL will be passed if ALL value selected in the prompt.
So in this case when ALL selected ,condition LEAST(:P_NUM) IS NULL becomes true and acts like 1=1 ,so all rows can be fetched. So that we can avoid the sql error maximum number 1000 reached by passing null (ALL).
1 -
this will not much be help of getting ur help .
You have to handle data model query to accept "all values" and "multiple values" ,
0 -
Sorry about that. Not sure why it will not work for you. I have attached
the image. Hope you are able to view it.
On Tue, Jan 30, 2018 at 10:39 AM, community-admin <
0 -
not able to click on the image provided??
0 -
please check the Click to add to Favorites Fusion BI Publisher - Getting Error While Passing Multiple Values In Dashboard Prompt For BI Publisher Report (Doc ID 2214582.1) To BottomTo Bottom In this Document Symptoms Cause Solution References Applies to: Oracle Fusion Incentive Compensation - Version 11.1.11.1.0 and later Oracle Fusion Application Toolkit Cloud Service - Version 11.1.11.1.0 and later Oracle Fusion Application Toolkit - Version 11.1.11.1.0 and later Information in this document applies to any platform. Symptoms You created a BI Publisher report in which you are passing a presentation variable. Based on that presentation variable you have built a dashboard prompt. When trying to pass multiple values to the report via the previously created prompt, you receive the following error: ERROR ----------------------- "Error occurred on retrieving data xml". When passing single value from prompt to the report no issue occurs. Cause OBIEE is passing the prompt selection ID instead of the selection value to BI publisher. Because of this issue, BI publisher is unable to find the matching data from its dataset. Solution The solution is provided via bug 18943284. References BUG:18943284
0 -
please post query associated with above report and also post screen shot of parameter section in data model. Thanks
0 -
Hi,
i am also facing same problem when i am trying to pass multiple values getting an error. "oracle.xdo.servlet.data.DataException: oracle.xdo.servlet.data.DataException: oracle.xdo.servlet.data.DataException: oracle.xdo.XDOException: java.sql.SQLSyntaxErrorException: ORA-00909: invalid number of arguments
". Can anyone please help me out.
Regards,
Ashish
0 -
Thanks a lot posting i got similar issue and resolved with yur god bless you
1 -
Hi Experts, i want to pass below values in BIP report params, how it is possible my test itself having ','
XXX,lmt
YYY,lmt
ZZZ,lmt
if i pass like this XXX,lmt , YYY,lmt , ZZZ,lmt… it is not working since my test itself having the comma and it is considering 'XXX' is one sting and 'lmt' is another. so i want this param shoule pass like XXX,lmt as one string.
please help me and thanks for your help in advance
0 -
Hi @User_UN2OH ,
Can you please go to
and ask a new question?This is a 6 years old thread, it's better if you ask a new question by providing all the information about your question, including the product version you are using.
Thanks
0