Using IN/LIKE Clause in BIRT

If you have been using BIRT (www.eclipse.org/birt) you would notice that simply writing a query like this for IN and LIKE clause:

SELECT * FROM LaneCodes WHERE Lanecode_ID IN ?

would not give you the results you expect. But here is how you can make it work.

Lets say I have a table called LaneCodes as shown below:

Sample Table

now in my dataset which i named dsAllLanes  i write my query as :

sample query

sample query

 Now make a report parameter called “rptparamLanecodes” as shown below:

BIRT Report Parameter

the dsAllLanes1 dataset is the second dataset whcih just gets all the LaneCode_ID and LaneCode_Description from the database.
Now we will concatenate the WHERE clause to dsAllLanes query using javascript in the beforeOpen event of the dataset. You can go there by clicking on the dsAllLanes dataset and click on Script and selecting beforeOpen form the top dropdown as show below:

javascript IN clause

 

and here is the result:


similarly LIKE clause can also be used.

Advertisements

About this entry