Thursday, April 28, 2011

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.

19 comments:

Vijay said...

Thanks Sir , This helps me and hope others will also like it ...

Vijay
agl.vijay@gmail.com

Vijay said...

Since i have some mandetory parameters as well , thus i need to append those in window.location. to do so i am using follwoing logic:
var sOutput = "";
var parameterArray = reportContext.getDesignHandle().getParameters();
var paramaterCount = parameterArray.getCount();

sOutput="";
for( var i = 0; i < paramaterCount; i++ )
{
var sParName = parameterArray.get(i).getFullName();

var sParVal = reportContext.getParameterValue( sParName );
sParName = sParName.replace(" ","+");
sOutput = sOutput + "&" + sParName + "=" + sParVal;

}
}


With above code there are two issues
1) When i use paramter group , sParVal comes as 'null'
2)If one of the paramter is of type date Time , format of the datetime is getting changed and birt throws date time format exceoption

Please advise,

Vj

Vijay said...

Sometime following gives -60 when "from date" is greater than "to date" and some time it gives 43190. (when there is a minut diffrence)

dateFrom = params["Date From"].value;
dateTo = params["Date To"].value;
BirtDateTime.diffSecond(dateFrom,dateTo)

Jason Weathersby said...

try this script"

sOutput="";
var parameterArray = reportContext.getDesignHandle().getAllParameters();;
var paramaterCount = parameterArray.size();

for( var i = 0; i < paramaterCount; i++ )
{
var sParName = parameterArray.get(i).getFullName();
if( parameterArray.get(i).getClass().toString() == "class org.eclipse.birt.report.model.api.ScalarParameterHandle"){
var sParVal = reportContext.getParameterValue( sParName );
sParName = sParName.replace(" ","+");
sOutput = sOutput + "&" + sParName + "=" + sParVal;
}
}

as far as the datediff are you using a datetime parameter?

Anonymous said...

Yes Sir , my FromDate and Todate paramter is of type date time.

Jason Weathersby said...

Can you log a a bug for this?

Anonymous said...

Sir how to reset progressive variable?

In my case i have added rownum variable, each time i click on next rownum will be increased by 1000.If user want to create fresh report with default value of rownum by going to paramter screen: it takes current value of rownum . where to put logic rownum=0 ?

Jason Weathersby said...

I am not sure what you need. In my example I set it so the user can click on any alpha character to set a staring point. In your case maybe you put a link on the report that re-runs the report with your variable reset.

Anonymous said...

https://bugs.eclipse.org/bugs/show_bug.cgi?id=345310

Jason Weathersby said...

Thanks for posting

Anonymous said...

Sir , now if i click on next button then after getting the report output if i do rightclick>proerties
i am able to see the whole URL.
How can i hide that ?

Jason Weathersby said...

You could try to modify the script to use form variables instead of url parameters.

Anonymous said...

if possible plz provide short Example !!

Thanks.

Jason Weathersby said...

Can you try this example
http://www.birt-exchange.org/org/devshare/designing-birt-reports/1365-customer-list-using-hidden-form-variables-to-re-run-report/

vijay said...

Hi,
This is very good and useful post for me.
Just wanted to know one more things

instead of link for A,B...Z Can I use a text box where I enter the Characters like A,B..Z and onclick of some button I will get records for that character?
Please let me know how this is possible. I am able to send hard coded value on click of button, but not able to send text box value.

vijay said...

Hi,
The problem for sending text box value has resolved.

but got one more problem. my report is working fine with eclipse. but when I am running same report from my application (which is configured with BIRT Viewer engine3.7) then I am getting error which clicking next or prev.
work of my application is only select the report and pass it to BIRT Engine.

please suggest me some solution.

Jason Weathersby said...

Can you give a bit more detail? Are you using the Viewer or the Report Engine API?

vijay said...

Hi Jason,
Thanks for reply.

I have created a BIRT report with my own Navigation buttons(next and prev). When I am executing my report in the eclipse from Run->View Report->In Web Viewer, then it is working fine, when I click next it is displaying next page. But when I execute my report from my application where I am using BIRT Engine 3.7 here first time it is giving me result but my own navigation buttons are not working here.

I want when I click next it should display next page.

Please help me......
Thank you very much in advance..

Anonymous said...

Can we use the script , instead of static value in parameter like ex. current date and operations on it ?