CODEWITHSUNDEEP
birt
Simon Greener
Simon Greener

Reputation: 425

BIRT List of Values parameter query

I have a large detail table for which one column is a foreign key to a list of values/lookup table.

The BIRT report is on the detail table.

I want to select rows from the detail table given a user-selected value from the foreign key table.

Detail:

select id, o.owner_name, ....
  from detail as d
       inner join
       owner as o
       on (d.owner_id = o.owner_id)
where d.owner_id = ?

Foreign Key/LOV/Lookup table

select owner_id, owner_name
  from owner;

So, on report execution, the user is presented with a pulldown menu populated with the owner_id/owner_name values from the owner table. The user selects the required owner_name whose owner_id value is then provided to the detail where clause parameter.

Is this possible?

Upvotes: 0

Views: 588

Answers (2)

BlainTheMono
BlainTheMono

Reputation: 59

you crate your first dataset as you did but without die "?". you use some random string, that will never be a valid parameter such as:
enter image description here
you write your second dataset as you did and link it to a parameter as dynamic
such as:
enter image description here

now, if you want multiple values from your parameter passed to your first dataset, you need to pass the values.
so you klick on your first dataset (marking it) and then you klick on "Script"-Tab where you can edit, what will happen with your dataset wenn you open the report.
in that script "beforeOpen" you write a small script, that passes the values of your parameter as sql needs the IN parameter formated like this: enter image description here

when you open your report and select multiple values of your OwnerIDParam parameter, it will be passed for your IN clause of your first sql

Upvotes: 0

Simon Greener
Simon Greener

Reputation: 425

I didn't see it after quite a bit of time looking, but in the end RTFM got me there.

  1. Create two data sets, one for the detail the other for the lookup;
  2. In report's parameters, see "Selection list values"/Dynamic

Upvotes: 1

Related Questions