1

Last updated May 8, 2009

This document provides some tips and tricks in using BIRT reports with

cascade parameters and multi-value selections.

I am describing three interesting tricks:

1. How to enable multi-select capability in a report cascade parameter

presented as a list.

2. How to use the multi-selected values in building the IN clause of a

query

3. How to allow user entry from a combo box to drive the next level of a

parameter cascade

4. How to pass a multi-value selection as a parameter to a drill-down

report.

2

3

4

Can we enable multi-selection when working with Cascading parameters

in a BIRT report? Note that this Cascading parameter dialog box (on the

right) does not present the option of multi-selecting in a list while the

dialog for building a standard report parameter (on the left) does.

5

6

So, to achieve a multi-select on a CASCADING parameters we must

change the ‘Scalar Parameter Type’ to Multi Value (under the Advanced

properties section of the parameter)

7

The following link has some interesting info: http://download.birt-

exchange.com/products/demos/birt/Parameters/Parameters.html

Uses the dataset filter IN option to use a multi-value parameter

8

Here I’ve build a filter expression for the dataset that uses the In clause

and the report multi-value ACCESSION parameter

9

10

Note that an expression can be inserted in the beforeOpen event handler

of the dataset (under Advanced properties). However, it still does not work

with multi-value parameters

After much head banging an important breakthrough!

By using toString() instead of value, I’m able to indeed get what I want, a

comma separated list of values that can be further manipulated using the

replace function! See next slide.

11

It seems that unless the toString() function is applied we get back a java

object that can’t be directly used with java script!

(See next slide for what you get with value instead of toString)

So in conclusion multi-value properties can be used in expressions and

can be formatted as IN clauses for SQL

12

13

The dynamic query clause:

this.queryText="select c.accession, a.peptide, a.frequency, c.description,

b.predictedmass_mono, b.predictedpi, c.polcrc64 FROM (select distinct

rp.peptide, count(*) FREQUENCY from REF_PEPTIDE@tip_link rp, ( select

distinct peptide from ref_peptide@tip_link rpep, ref_protein@tip_link rpro

where rpep.polCRC64=rpro.polCRC64 and rpro.accession IN

('"+params["ACCESSIONS"].toString().replace(/,/g,"','")+"') and

rpep.missed_cleavage=0 )q where q.PEPTIDE=rp.peptide group by

rp.peptide having count(rp.peptide)=1 )a, ref_peptide@tip_link b,

ref_protein@tip_link c where a.peptide=b.peptide and

b.polCRC64=c.polCRC64 and c.accession IN

('"+params["ACCESSIONS"].toString().replace(/,/g,"','")+"') order by

accession desc";

All current examples of BIRT report cascading parameters start with a

parameter list from which the user can start the cascade.

I did not find any examples where the user can start the cascade by

typing a fragment of the query text, preferably with Oracle ‘wild’

characters. When the initial list is long the query becomes slow and the

difficulty of finding the term in the list increases dramatically.

14

This is a useful trick to reduce the complexity of the initial list that the

user will select from. Basically it creates an INDEX of the GENEID (s) in the

REF_PROTEIN database and appends the % oracle ‘wild’ character. When

a user selects one of the options this parameter is passed into the next

cascading dataset which searches with this string.

15

Once a gene index has been selected the cascading parameter GeneIDlist

is populated from the query shown in the previous slide. In turn selecting

the geneID populates the gene-related proteins and it populates the last

parameter list where the user can select the proteins of interest.

The query that populates the protein list is shown below;

Select DISTINCT rpro.POLCRC64, rpro.REFDBID, rpro.DEscription

, rpro.REFDBID||' : '||rpro.DEscription XDESCRIPTION

from ref_protein rpro

where rpro.geneid IN (?)

order by description asc

16

All cascading parameters require to be bound to a data set and can be

only displayed as list boxes or COMBO boxes (and there hides the

solution!)

17

This was a totally serendipitous discovery and I have not seen another

example like this.

It is indeed very useful. The only limitation is that we still have to start the

cascade with a data set based combo box rather than a simple text box.

This is still a small price to pay for the increased filtering functionality and

flexibility.

18

It would be useful to query for a ‘list of items’ which can then be

submitted to a downstream report as a parameter

19

User selections made from the multi-select list are reported and links are

provided for submitting this list to one or more tools that accept an

accession list as a parameter. In the example shown the list is reported

and two links are provided so that this list can be submitted either to the

AIMS or the Peptide Degeneracy Calculators. How do we link the multi-

select parameter to these drill-down reports?

20

To use the value of the multi-select parameter in an IN query clause, I

need to format it appropriately and then pass it in to the

corresponding drill-down report parameter.

1. The inSelectedAccession parameter is set in the beforeOpen event

handler of the REFSELECTION data set (which also refines the query

for the data set for the first stage report).

I then engineered appropriate hyperlinks to drill-down reports that can

work with the inSelectedAccesion parameter.

This way I can link to a drill-down report passing the value of this

parameter as the IN clause to the next report.

21

The parameter is formatted exactly as required by the AIMS calculator

tool so that the user is relieved from having to copy and format the

accession list from another source.

22