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

Question

Summary

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!

2771861 · Answer

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

Thomas Dodds · Answer

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

Sherry George · Answer

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 ?