Eclipse Community Forums: BIRT » Using an IN clause on an integer with report parameter

Help

Home

Home » Archived » BIRT » Using an IN clause on an integer with report parameter

Show: Today's Messages :: Show Polls :: Message Navigator

Using an IN clause on an integer with report parameter [message #1829197]

Sat, 27 June 2020 08:42

Andrew Sivyer

Messages: 2
Registered: June 2020

Junior Member

Hello,

I am brand new to BIRT, having finally found it as the solution to generating PDF reports for Oracle Apex.

I have managed to build my first report, but would like to add an 'IN' clause to my dataset to compare an integer to a report parameter. My long-term goal here is to be able to pass the parameter (probably a csv list) by URL.

I have added a dataset parameter based on params["myParam"].value.join(",");'

When I specify the parameter as a string, it will work for a single number in the report parameter (e.g. 19) but gives me an 'invalid number' error if I try a csv entry (e.g. 19, 20).

If I specify the parameter as an integer, it will still work for the single number but for the csv will only show the first record (e.g. 19, 20 shows only 19).

Can anyone explain what I am doing wrong?

Thanks in advance!

Report message to a moderator

Re: Using an IN clause on an integer with report parameter [message #1831119 is a reply to message #1829197]

Tue, 11 August 2020 21:27

Thomas Gutmann

Messages: 21
Registered: April 2020

Junior Member

Hello Andrew,

this is default behavior of BIRT but I found a small solution which I use on my reports.

On the parameter dialog of the dataset i use the expression builder "fx" and add params["myParam"].toString().
The result will be a csv-list (type is string) like '19,45,78'

On the WHERE-clause I serach after the according numbers in the string, e.g.:

WHERE INSTR( ? , cast(ID as varchar(10))) > 0
-- versio with values --
WHERE INSTR('19,45,78', cast(45 as varchar(10))) > 0 -- result is: true
WHERE INSTR('19,45,78', cast(55 as varchar(10))) > 0 -- result is: false

I hope it will help you a little bit.

Kind regards
Thomas

Report message to a moderator