Oracle Analytics

Get Involved. Join the Conversation.

Topic

    Carley Howell
    BIP - how to filter report for a range of data using prompt
    Topic posted May 3, 2017 by Carley HowellRed Ribbon: 250+ Points, last edited September 11, 2019 
    40 Views, 6 Comments
    Title:
    BIP - how to filter report for a range of data using prompt
    Summary:
    BIP - how to filter report for a range of data using prompt
    Content:

    Hi,

    Is there a way to set up a prompt within BI Publisher that selects a range of values? e.g. I want to see invoices 25-32. Without individually selecting each value between 25 and 32.

    I know this can be done using a standard dashboard prompt but it needs to be done using only BIP, client does not want to use dashboards. Can't get it to work so far with the following method:

    - set up 2 parameters "to" and "from"

    - set up list of values to populate the "to" and "from" parameters drop down, list of values returns all invoice numbers

    - change data set to include a where clause: WHERE invoice_number BETWEEN :to and :from

    Many thanks in advance!

    Comment

     

    • Tdodds74

      Need more details ... what version BIP and what database are you going after?

      Using BIP in Oracle Business Intelligence 11.1.1.7.140527 I was able to get this to work without issue.  My initial thoughts are you need to make sure you've got datatypes right and that you aren't using values that are space padded (char versus varchar2 etc.)

      1. similar to what you describe, my set up is:

      2017-05-03 07_41_26-Oracle BI Publisher.png

      2. I get results

      my where clause is ACCT_ID BETWEEN :ACCT_START and :ACCT_END  (Oracle 11gR2 DB)

      2017-05-03 07_43_53-Oracle BI Publisher.png

    • Carley Howell

      Thanks a lot for your response Thomas!

      It is version 11.1.1.7.150120. Database is Oracle 12c Enterprise Edition 12.1.0.2.0

      Have you had to cast any of your columns to get it to work? What are the datatypes of the 2 columns you're using to pass in values?

      Thanks again.

      Oracle BI Publisher 11.1.1.7.150120

    • Carley Howell

      Hi again,

      Just a bit more detail..

      The way I have set this up is exactly as you have shown in your screenshots as you can see below:

      pastedImage_1.png

      When I try to view data I get a "Failed to load XML" error. I checked the log for more information on the error and can see this error message which I've searched for online and can find barely anything, apart from a couple of other posts on here with no answers:

      Query Failed: [nQSError: 19002] Incorrect use of parameters. The parameters used in one of the Operators cannot be resolved without ambiguity. 

      The datatype of the column I'm using is Double:

      pastedImage_0.png

      When I click "OK" from the data set window, I am prompted to enter values, not sure if that's relevant or not:

      pastedImage_3.png

      Any help much appreciated!

    • Sherry George

      Can you try running the same SQL(in the data set) in your database (using something like SQL developer) and see if it is returning the results ?

    • Tdodds74

      I didn't have to mucky with datatypes ... I've done this between with dates and chars and numbers ...

      see if swapping it to

      "A01-00 AQUISITIONS"."Aquisition Number" >= :acq_from

      AND

      "A01-00 AQUISITIONS"."Aquisition Number" <= :acq_to

      Do you get an error?

      Also check the datatype on the parameter itself ... FLOAT,STRING or INTEGER to DOUBLE might be your issue ...

      2017-05-04 14_40_39-Clipboard.png

    • Carley Howell

      Thank you so much Thomas, the suggestion of using <= and >= instead of BETWEEN worked!