Sunday, May 06, 2007

BIRT: Resort Columns After Report Has Been Run

BIRT strong suit is in the realm of online reporting. One of the features that is common with online applications that display information in a grid format is the ability to click on a column header and have the information resort based on that column. In this article I will show you how to do just that with BIRT.

The report that we will do this on is a report that lists customers and their payment information. It will be based off of the Classic Cars databases. These instructions are for BIRT 2.2 and will not work on previous versions.

1. Create a new report in an BIRT reporting project. Call this new report “customerPayment.rptdesign”.
2. Create a new Data Source from the Classic Cars Inc. Sample Database.
3. Create a new dataset using the following query:
select
*
from
CUSTOMERS,
PAYMENTS
where
CUSTOMERS.CUSTOMERNUMBER = PAYMENTS.CUSTOMERNUMBER
and customers.customernumber = ?

  • a. Name the parameter dsprmCustomerID
  • b. Under the dialog for the data set parameter, create and link to a report parameter called rptprmCustomerID. Set it as a text box entry.
4. Drag and drop the newly created data set over to the report design pane. Delete all columns except for the following
  • a. Customer Number
  • b. Customer Name
  • c. Payment Date
  • d. Amount


Figure 1. Report will Look Like after step 4

5. Create a new report parameter called rptprmSortOrder. Set it to allow null values and hidden.
6. Add the following script to the OnPrepare event of the table.

//We only want to add this into our code when the value is not null for the
//parameter sort
if ( params["paramSortOrder"].value != null )
{
//Bring in the BIRT Report Model API and for CONSTANTS
importPackage( Packages.org.eclipse.birt.report.engine.api.script.element );

//Create a dynamic sort condition
var sortCondition = StructureScriptAPIFactory.createSortCondition();

//Based on the value of the sort parameter, set the appropriate key value for sorting
switch (params["paramSortOrder"].value)
{
//Remember that for the key, we need to use the fully qualified row and field name as a string, not as a value
case "date" :
sortCondition.setKey("row[\"PAYMENTDATE\"]");
break;
case "price" :
sortCondition.setKey("row[\"AMOUNT\"]");
break;
}

//set condition to ascending order
sortCondition.setDirection("asc");

//Add to the table
this.addSortCondition(sortCondition);
}


7. In the header row, we need to create hyperlinks that will call this report, and pass in parameters to tell which column to sort by. So save the report as it is, othehrwise the parameters will not show up in the drill down dialog. Select the PaymentDate column header, and create a hyperlink like so:
  • a. Select drill down
  • b. Link to the customerPayment.rptdesign file
  • c. Select the rptprmCustomerID field, and set the value to params["rptprmCustomerID"]
  • d. Select the rptprmSortOrder parameter, and set the value to “date” with the quotation marks.
  • e. Set to open in the same window.
  • 8. Do the same thing for the Amount column, except set the value of rptprmSortorder to “price”.

With that done, now you can reorder the report in view time when the user clicks on the date or the amount columns. With a little more logic developed in you can have the report do both ascending and descending sorts, and even have it refresh the report without having to refresh the viewing page.

24 comments:

Anonymous said...

I want to link two reports with conditions. e.g. In one report i have list of the accounts and in another report i have account details. Now when i'' click on any account from the list i want to display only account details which is clicked..

John Ward said...

What you are talking about doing is called a drill down report. What you do is on the data element int he report with your customer informat, under the properties tab, go to the Hyperlink section. Change the Link To option to Drill Through. From there, you select your other report, and specify which data elements and report parameters go with the target reports parameters. Be sure that you have a parameter in the target report with the customer id in order to filter that data. Once done, you can run the report with the employee information and click on the link and pull up the detail information.

Hope that helps. Ill try to write an article explaining how to do that in the next few days.

John

Luther said...

This seems straight forward; however, I have been unable to apply this to a simple case I'm working on.

"importPackage( Packages.org.eclipse.birt.report.engine.api.script.element );

var sortCondition = StructureScriptAPIFactory.createSortCondition();

sortCondition.setKey("row[\"Personnel_Services\"]");

sortCondition.setDirection("asc");

this.addSortCondition(sortCondition);"

The variable sortCondition winds up as type SortConditionImpl and the table (MultiRowItem) expects the ISortCondition type. Exception is:
org.mozilla.javascript.EvaluatorException: Can't find method org.eclipse.birt.report.model.simpleapi.MultiRowItem.addSortCondition(org.eclipse.birt.report.engine.script.internal.element.SortConditionImpl).

Any thoughts would really help me out.

aaron said...

I couldn't get his example to work either. But this works.

importPackage( Packages.org.eclipse.birt.report.engine.api.script.element );
importPackage( Packages.org.eclipse.birt.report.model.api.simpleapi );

var sortCondition = StructureScriptAPIFactory.createSortCondition();
sortCondition.setKey("row[\"WORKGROUPID\"]");
sortCondition.setDirection("desc");

var sortKey = sortCondition.getStructure();
this.addSortCondition( SimpleElementFactory.getInstance().createSortCondition(sortKey));

aaron said...

So does anyone have a suggestion on how to get the ASC and DESC to toggle if the user clicks on the header again (twice)?

Anonymous said...

We are in the process of migrating an existing statistics-solution from Oracle with Olap-option on Orion server to our Birt/porgresql/jboss - environment.
The existing solutions provides functionality for rotation of the result-table/cross-tab. The rotaion works in such a way that the enduser can manipulate the way the table is presented after it is generated. For instance: The enduser can choose to rotate the result-table eigther left, right or even freely. If the "rotate left" option is choosen then one of the horizontal indexes is moved to the vertical axis, and at the same time one of the vertical indexes is moved to the horisontal axis. You can see the existing solution here: http://www.shdir.no/pasopp/index.jsp - try and push the upper button named "Vis tabell" (which is equivalent to "show table" in Norwegian).
Any idea on how to replicate identical or similar behaviour with Birt? Is the solution to create multiple different cross-tabs?

Thanks
Alvi

John Ward said...

Alvi,

I can't really make heads or tails of the site. Unfortunately I am limited to only English and Spanish as languages. With that being said, based on your description, there are two things you might want to consider. I believe you can do what you are looking for with BIRT by using the design Engine API to manipulate the Cube that generates the crosstab. Its a little bit of legwork, but I believe it can be done. If your looking for something a little easier to develop, Actuate, who are the developers of BIRT, have a few commercial options that build cubes, but I haven't worked with those products specifically.

John

Anonymous said...

Hi John,

Thanks for response. I would like to know how I can pass parameters from one report to another report. Could you tell me any tutorial or give me example.

Thanks
Alvi

John Ward said...

Alvi,

Yes, what you are looking for is called a drill-through. It is he same steps as the last section of this article, where you would select a text/data/label element, and in the property editor, go to the hyperlink section, and choose drill-through as your action type. Here are a couple of articles on how to do that.

http://help.eclipse.org/help31/index.jsp?topic=/org.eclipse.birt.doc/birt/birt-18-4.html
http://www.eclipsemag.net/ecm/ecmfeatures/psecom,id,44,nodeid,3.html
http://www.eclipse.org/birt/phoenix/examples/reports/birt2.1/drill/index.php

There should also be a section in the BIRT help file about how to do this.

Anonymous said...

Hi John,

All these above example passed single value but I want to pass multi selected value. In Master report I have a multi selected value parameter I want to pass this parameter to detail report. I used drill-through (select detail report parameter and given a master parameter). It generates the following error in detail report.

ReportDesign (id = 1):
+ Cannot get the result set metadata.
SQL statement does not return a ResultSet object.
SQL error #1: ERROR: syntax error at or near "["

Cannot get the result set metadata.
SQL statement does not return a ResultSet object.
SQL error #1: ERROR: syntax error at or near

Can you guide me how I can solve this problem.

Thanks

Alvi

Yves W said...

I tried the example with this code:

importPackage( Packages.org.eclipse.birt.report.engine.api.script.element );
importPackage( Packages.org.eclipse.birt.report.model.api.simpleapi );

var sortCondition = StructureScriptAPIFactory.createSortCondition();
sortCondition.setKey("row[\"WORKGROUPID\"]");
sortCondition.setDirection("desc");

var sortKey = sortCondition.getStructure();
this.addSortCondition( SimpleElementFactory.getInstance().createSortCondition(sortKey));


in Eclipse-preview the snippet works, but with tomcat it throws following error:

java.lang.NoClassDefFoundError: org/eclipse/birt/report/engine/script/internal/element/SortConditionImpl

any idea what this error could cause?

greets, yves

John Ward said...

I reworked the script. Apparantly there has been a change in the accessibility of some of the classes, and have been re-exposed as SimpleApi. I redid the script itself to use this interface instead. This will fix the issue you are experiencing on Tomcat.

importPackage (Packages.org.eclipse.birt.report.model.api.simpleapi);
if ( params["rptprmSortOrder"].value != null )
{
var sortCondition = SimpleElementFactory.getInstance().createSortCondition();

switch (params["rptprmSortOrder"].value)
{
case "date" :
sortCondition.setKey("row[\"PAYMENTDATE\"]");
break;
case "price" :
sortCondition.setKey("row[\"AMOUNT\"]");
break;
}

sortCondition.setDirection("asc");

this.addSortCondition(sortCondition);
}

Anonymous said...

Hi,

I have made one customize html parameter page in jsp. Now when i tried to get htlm text value into birt script, it return null value. how i can get html text value in birt report.

This is my birt script code:

req = reportContext.getHttpServletRequest();
val =req.getParameter("myArea");


Can you pleas tell me how i can get html text value in birt script.

Thanks

John Ward said...

Well, that depends on how your calling your report. If you have a customized JSP parameter page, are you including the BIRT sample web app, or are you embedding the Report Engine API into your app and calling reports that way. If your using the sample web app, you only need to call the web app with a URL parameter like "myArea=value". If your embedding it, then the reportContext.getHttpServletRequest() will only work if you are calling the calling portion via a POST request. Id need a little more info to know how your calling the report to answer that for sure.

Anonymous said...

Hi John,

Thanks. I have made customize jsp parameter page. in this page user select value from list box. now i want to get these selected value in report. If customized jsp page is not right way then please tell how i can make it? like embedding.

Thanks.

John Ward said...

Again, thats going to depend on the deployment scenario. If your using the BIRT Web Viewer, your just going to add the values in your JSP page to the URL. If your using an embedded BIRT Report Engine, your going to take the values from POST, and add them to the Parameter Map in the Run task. Without knowing how you have BIRT set up, I really can't tell you for sure.

Anonymous said...

Hi John ward,

I am using web viewer.

Anonymous said...

Hi,

I am working on birt 2.2.2. I made a report using cross tab(data cubes). It gives error when i tried to fetch many rows(almost 300 rows) while its working fine if i fetch some rows(less then 300 rows). Is there any limitation in Birt? I want to show 20000 rows can you please tell me how i can generate report with 20000 rows.

Thanks.

I got following error

"The following items have errors:

ReportDesign (id = 1):
- Retrieving binding data error
Retrieving binding data error ( 5 997 time(s) )
detail : org.eclipse.birt.report.engine.api.EngineException: Retrieving binding data error at org.eclipse.birt.report.engine.internal.document.v4.DataItemExecutor.doExecute(DataItemExecutor.java:96) at org.eclipse.birt.report.engine.internal.document.v4.ReportItemExecutor.execute(ReportItemExecutor.java:293) at org.eclipse.birt.report.engine.internal.document.v4.ExtendedItemExecutor.prepareChildExecutor(ExtendedItemExecutor"

Anonymous said...

I want detail report(I am using drill through approach)to run for the same date range which is given for master report.eg if master report is supposed to run for may,june 2008 then details should be for this range.
I would appreciate if someone can help me.
Thank You

John Ward said...

To do a date range drill through, you would have two Report Parameters in the detail report for the date range.

In the master report, when creating your drill through, you would set the target reports parameters to the appropriate parameters in the summary report. So in the summary report, if your date range was called startDate and endDate, you would set the startDate and endDate in the Target report to something like params['startDate'] and params['endDate']. The expression builder will help you put in the correct paramters.

See:
http://birtworld.blogspot.com/2008/05/birt-drill-through.html

http://www.birt-exchange.com/devshare/designing-birt-reports/389-birt-drill-through/#description

Anonymous said...

Thank You Sir, You gave me a wonderful idea,It is working as I wanted.

Anonymous said...

I need to give a space for comments where comments can be added electronically by users Is it possible in Birt report.I would highly appreciate if someone can give me solution.

neeti said...

Hi John Ward

you had mentioned:
If you are using an embedded BIRT Report Engine, your going to take the values from POST, and add them to the Parameter Map in the Run task.

I am trying to send parameters through request object. I want to use the following in initialize function of the report:

var id = reportContext.getHttpServletRequest(). getAttribute("list");

reportContext.setParameterValue("Report_Param",id);

How do i pass the request object from my JSP to the BIRT report? I have set the value of "list" using request.setAttribute("list", list) in the application.

thanks
Neeti

Anonymous said...

Thanks John for the article. It was helpful.

I created a sample report with the sorting option as you have explained.

This might be helpful if anyone is having issues.
http://www.birt-exchange.org/org/forum/index.php/topic/17847-sorting-in-birt/page__st__20__p__99634&#entry99634