BIRT - Reducing large queries and custom navigation controls

Often times when writing a report that requires large amounts of data, the query times become burdensome. This will lead to dissatisfaction from customers of your report. To get around this issue certain companies like Actuate implement progressive viewing in their products that start returning formatted data before the report generation process is complete. This allows users of the report to start getting data long before the total report is completed.

Another option is to reduce the large queries into smaller queries and add navigation controls to your report to get the next smaller set of data. For example, if you have a customer listing report, the first query may return all customers whose name starts with the letter A. A set of navigation controls could have a next button that returns customers whose name starts with the letter B, etc. This approach is very simple in BIRT and this post details one way of doing it.

As an example we can use the classic models sample database that comes with BIRT. It contains a customers table. To return all customers starting with A we could use the query select * from customers where customername like ‘A%’. We want the report to be dynamic, so we will need script to modify the query on the fly. We can begin by just adding a new datasource that uses the classic models database. Next we create a dataset with the following query:

Select * from customers

The next step is to add a hidden report parameter that we use in script to add a where clause to the query. In this example the parameter is named “like”.



The parameter is hidden and has a default value of A. We can now use this parameter in the beforeOpen script of the data set we created earlier. This script should look like the following:

this.queryText = this.queryText + " where customername like '" + params["like"].value + "%'";

This script will change the query every time the report runs and will append the where clause with the hidden report parameter. The default value for the parameter is A which should list all customers starting with A. Before adding navigation controls to the report we need to create and calculate some global variables that our controls will need. Ideally we will add a previous and a next button to the report. In addition it would also be nice to have a list A-Z that the user could click on to go directly to a set of customers. So at the minimum when the report runs we need to get the parameter for the report and calculate what the previous and next customer names should start with. For example if the parameter is C, previous should be B and next should be D. This can be done with a simple beforeFactory script like:




switch( params['like'].value ){
case 'A':
prev='Z';
next='B';
break;

case 'B':
prev='A';
next='C';
break;

case 'C':
prev='B';
next='D';
break;
.
.
.
}



The beforeFactory script is used because it runs before data is retrieved and executes only once. The BIRT initialize script can be executed multiple times so it is not ideal for this script. The var keyword is also not used with the variables prev and next which will make them visible to downstream events. You could also use the reportContext.setGlobalVariable method to do store the variables as well.

After the table is added to the report you can then add a Text element that contains a script tag to implement your prev and next buttons.



Remember to set the type to HTML. The script tag contains a client side script that just reloads the report with a new value for the like parameter. This function is called nextData and takes the new like value as a parameter. The text element also contains two buttons that call the nextData function with the prev and next server side javascript variables we calculated in the beforeFactory. Notice this is done with the VALUE-OF tag. This tag allows us to combine server side scripting with client side scripting.

<script>
function nextData( a ){
var cl = new String(location.href);
var targetURL = new String();
var tst = cl.indexOf("&like");
if(  tst == -1 ){
 cl = cl + "&like=" + a;
}else{
 var ch = cl.indexOf("&like=");
 cl = cl.substring(0, ch) + "&like=" + a;
    
}
window.location = cl;
}
</script>
<input type="button" value="Previous"
onclick="nextData('<VALUE-OF>prev;</VALUE-OF>')" >
<input type="button" value="Next"
onclick="nextData('<VALUE-OF>next;</VALUE-OF>')" >

The last thing we add to the Text element is an Alpha index for all letters of the alphabet.

<br>
<a href="#" onclick="nextData('A');return false">A</href>
<a href="#" onclick="nextData('B');return false">B</href>
<a href="#" onclick="nextData('C');return false">C</href>
<a href="#" onclick="nextData('D');return false">D</href>
<a href="#" onclick="nextData('E');return false">E</href>
.
.

This allows the user to directly select a letter to use in the like clause. The output for the report looks like the following.


The report also handles the situation when no data is returned.



The no data label is placed in a grid below the table. The grid is bound to the table so it uses the same dataset. The dataset contains an aggregate computed column that counts the number of rows. This computed column is then used in the grid and the table visibility property.



For example the grid visibility is shown below.



The expression is:
(row["myrowcount"] > 0)
The expression returns true when rows exist. This will then hide the grid and label. The opposite condition is used on the table.
(row["myrowcount"] < 1)
This report is available at Birt-Exchange.