BIP - how to filter report for a range of data using prompt — Oracle Analytics

Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

BIP - how to filter report for a range of data using prompt

Received Response
184
Views
6
Comments
2771861
2771861 Rank 4 - Community Specialist
in Oracle Analytics Cloud and Server

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!

Answers

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    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

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    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

  • Sherry George
    Sherry George Rank 7 - Analytics Coach

    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 ?

  • 2771861
    2771861 Rank 4 - Community Specialist

    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:

    image

    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:

    image

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

    image

    Any help much appreciated!

  • 2771861
    2771861 Rank 4 - Community Specialist

    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

  • 2771861
    2771861 Rank 4 - Community Specialist

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