Oracle Analytics Publisher

Passing multiple values in BI Publisher text type parameter

Closed
3126
Views
12
Comments
3601907
3601907 ✭✭✭✭

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

  • 3601907
    3601907 ✭✭✭✭

    Hi Kavitha,

    I am using the following Oracle BI Publisher 11.1.1.9.0

    (build:20171003.2259). See below a screenshot of the error. Redacted are

    just parameter fields and report name. I hope this helps. Thank you

    On Mon, Jan 29, 2018 at 11:56 PM, community-admin <

  • Hi,

    What is your BIP version.

    Please provide more details for the error you are facing, kindly upload screenshot showing the error

    Regards,

    Kavitha

  • Dir_Pal
    Dir_Pal ✭✭✭✭✭

    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). 

  • Dir_Pal
    Dir_Pal ✭✭✭✭✭

    this will not much be help of getting ur help .

    You have to handle data model query to accept "all values"  and "multiple values"  ,

  • 3601907
    3601907 ✭✭✭✭

    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 <

  • Dir_Pal
    Dir_Pal ✭✭✭✭✭

    not able to click on the image provided??

  • 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

  • please post query associated with above report and also post screen shot of parameter section in data model. Thanks

  • 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

    Error.PNG

  • Thanks a lot posting i got similar issue and resolved with yur god bless you 

  • 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

  • Hi @User_UN2OH ,

    Can you please go to https://community.oracle.com/products/oracleanalytics/categories/bi-publisher 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

This discussion has been closed.