Birt Multi Value Cascading Parameters

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.

1

2

3

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.

4

5

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)

6

The following link has some interesting info: http://download.birtexchange.com/products/demos/birt/Parameters/Parameters.html Uses the dataset filter IN option to use a multi-value parameter

7

Here I’ve build a filter expression for the dataset that uses the In clause and the report multi-value ACCESSION parameter

8

9

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.

10

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

11

12

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";

13

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