Friday, May 09, 2008

BIRT: Swapping Data Set at Runtime

Often it is a requirement to design a report against a development database and then switch the connection at runtime. This can be done in many ways with BIRT. The options include:

JNDI - Look up the data source.
Property Binding - Swap the data source at runtime using property binding expression.
Script – Write a script in the beforeOpen event handler to modify the data source properties.
Connection Profiles – Store the connection information in a shared profile.
DTP driverBridge extension – Implement a driverBridge extension to intercept calls to a specific driver.
Design Engine API (DE API) - Swap the dataset property on a specific table before running the report.

Using the DE API often serves as a useful alternative, because the data sources may be different types. For example your deployed report may use a scripted data source to retrieve some EJB value, but at design time you may not have access to this data source. So you could develop a stubbed data source that uses JDBC to test your report layout and when deployed swap the data set on elements that are bound to the data set. To do this you need to do a few things. First name your elements that use the data set to be swapped. This can be set in the general properties for each report element. Next make sure your two data sets (one for design time and one for deploy time) have the same result columns. If the result columns are not the same much more work needs to be done in order to implement this solution. Finally implement a beforeFactory script that modifies the report design before it is executed. Assume we have two data sources/data sets and one table. If I name my table “mytable” and my data sets are named “viewer” and “designer” the beforeFactory event script would look as follows:



des = reportContext.getHttpServletRequest().getParameter("__designer");

if( des == null || des == "false" ){
mytable = reportContext.getReportRunnable().designHandle.getDesignHandle().findElement("mytable");
mytable.setProperty( "dataSet", "viewer" );
}


Note that the “designer” data set is the default data set used in the report. This is the data set that you should drag to the canvas to build your report


The first line of code determines whether we are using the designer. The designer adds the __designer parameter to URL when the report is previewed. This parameter will not exist when deployed. If this parameter does not exist or is set to false we assume that the report is deployed. This line:



reportContext.getReportRunnable().designHandle.getDesignHandle()



returns the report design handle. BIRT 2.3 has an easier way of getting the report design, but this method should work for BIRT 2.2 and 2.3. We then use the report design handle to locate the table element to be modified. Finally we set the dataSet property to the viewer dataset.


An example report illustrating this concept is located here.

36 comments:

Samuel Santos said...

Since getDesignHandle() is deprecated you should now use task.getReportRunnable().getDesignHandle().getModuleHandle().findElement("mytable") (using BIRT RE).

Jason Weathersby said...

Samuel thanks for the comment. You should be able to do it the two following ways in BIRT in addition to your comment.

Using birt 2.3 you can use:

//script
reportContext.getDesignHandle().findElement("mytable");

//ReAPI
(ReportDesignHandle)design.getDesignHandle().findElement("mytable");

Where design is a IReportRunnable that was used to open the report design.

Jason

Virgil said...
This comment has been removed by the author.
Virgil said...

Hi Jason, It seems the URL is not working to the example. Based on a quick search, I think the correct URL is here

Jason Weathersby said...

Thanks Virgil. Post Updated.

Rigga said...

I am able to create report in /report folder using report api.

but when i am trying to display it it's giving me this error

"Apr 28, 2008 5:12:26 PM org.eclipse.birt.report.engine.api.impl.ReportDocumentWriter saveDesign

SEVERE: Failed to save design IR!"

but i am saving it using

designHandle.saveAs(rPath+"/sample.rptdesign");//need to un comment

designHandle.close( );

but still while displaying it's giving this error.

Actually when i m trying to display using run servlet it's working fine but using
frameset it's giving me above errors.
so will u plz let me know what exactly is going on there and how can i resolve it

vidu said...

HI guys

I have been searching for about securing the BIRT report URLs

can some prepare a article or something on that? I'm new to BIRT and i see there are more people around who have asked similar question and most of the questions are not seem directing to a proper answer ,

I'm using the out=of the box viewer, please assist us .

vidu

Anonymous said...

I'm looking to do something similar, but with a scripted data set. I have already retrieved all my data from the database (in my Java application) and formatted the result set into a java collection. I want to be able to pass this to the report design and then using REAPI, generate the report. Please let me know how I can achieve this. Thanks a lot for your help.

Jason Weathersby said...

If you are using the REAPI to run the report you can add your object to the app context like:

config = new EngineConfig( );
HashMap hm = config.getAppContext();
hm.put( "yourobject", yourobjinst);
config.setAppContext(hm);
config.setBIRTHome("yourbirtengine");

Platform.startup( config );
IReportEngineFactory factory = (IReportEngineFactory) Platform.createFactoryObject( IReportEngineFactory.EXTENSION_REPORT_ENGINE_FACTORY );

engine = factory.createReportEngine( config );

Then in the report you can reference the object by name in script or in an expression like:

yourobject.hasMoreElements()

Anonymous said...

Jason,

Thank you very much for the help. It's working fine when I followed your suggestion. I wanted to ask you one more question. Is it possible to set the data object and other parameters (like report header, image, etc.) on the design (from a java class - one for each report design) and then invoke another java class (which takes this design - IReportrunnable as a parameter) which will generate the PDF/HTML report?

Jason Weathersby said...

If I understand correctly the answer is yes. You can use the DE API to build a report from scratch or modify an existing one, and then use the RE API to run this report. Take a look at this example.
http://wiki.eclipse.org/Java_-_Execute_Modified_Report_%28BIRT%29

Jason Weathersby said...

http://wiki.eclipse.org
/Java_-_Execute_Modified_Report_%28BIRT%29

Anonymous said...

I have added a scripted dataset in my report design and the js event handlers are:
open
totalrows = myData.size();
currentrow = 0;

fetch
if( currentrow >= totalrows ){
return( false );
}
var calrow = myData.get(currentrow);

row["accountNumber"]=calrow.accountNumber;
row["accountTitle"]=calrow.accountTitle;
row["homePhone"]=calrow.homePhone;
row["businessPhone"]=calrow.businessPhone;

currentrow = currentrow + 1;
return ( true );

I'm trying to set myData using the DE API:

//Open the report design
design = engine.openReportDesign("D:\\eclipse3.3\\workspaces\\BDRIA_WS\\WBR_Report_Project\\test_report.rptdesign");
ReportDesignHandle report = (ReportDesignHandle) design.getDesignHandle();
report.setProperty("myData", myObj); // this throws the following exception

org.eclipse.birt.report.model.api.command.PropertyNameException: The property "myData" is invalid for element "{1}".

How do I set myObj as a variable for the report? Otherwise, how do I use myObj as the scripted dataset?

Thanks..

Jason Weathersby said...

You want to use the RE API for this.

Try

EngineConfig config = new EngineConfig();

config.getAppContext().put("myData", yourdataobject);

or put it on the task.

task.getAppContext().put("myData", yourdataobject );

Jason

Anonymous said...

Sorry for going in circles. I know you have suggested to me this before using REAPI. I was only trying to see if this can be done using DEAPI. I think I have the clear picture (almost) now. I need to use DEAPI to set report parameters, then using REAPI set the data object on the task. The request will then be forwarded to a common Java class (will be used by several reports) which will generate the report from this task.

Thank you very much for all the help you have given me.

Jason Weathersby said...

You use the DE API anytime you want to modify the report design ie create a new parameter/table etc. You use the RE API to run the report and set parameter values ie task.setParameterValue(s)


Jason

Anonymous said...

Can you please tell me how I can change an image URL using either DEAPI or REAPI? I need to change the URL depending on the user's login credentials. Also, I have quite a few report elements (like label, text, etc.) whose displayed text needs to be changed using the APIs (from Java class). Do I need to get each element handle and change the value on it? I need to also hide/show table columns depending on some criteria. Is it possible to do this at run time without saving the changes to the report design file?

Thanks for your help.

Jason Weathersby said...

You can use the DE API to change the URL. For example if you are using the RE API to run a report, you can get a handle to the design that you have opened with the report engine and set a named image like:

design = engine.openReportDesign("Reports/TopNPercent.rptdesign");

ReportDesignHandle report = (ReportDesignHandle) design.getDesignHandle( );

ImageHandle image=report.findElement("MyImage");

image.setURL( "\"http://www.eclipse.org/birt/phoenix/tutorial/basic/multichip-4.jpg\"" );

Similar methods can be used to change text and labels. You can also change these things using the event handler for a report item, which can be written in Java or Java/Script. This is usefull if you use the Web Viewer and not the RE API to run the report.

You can set a visibility expression on most report items that can be based on parameters etc to hide columns/tables/labels etc.

Jason

Anonymous said...

Everything works fine in a standalone application, but when I deploy it as a web application to WebLogic 8.1, I get an exception. I'm using BirtEngine class provided at the example site and calling initBirtConfig() from my servlet's init method.

It gives me the following exception.
java.lang.NoClassDefFoundError: org/eclipse/birt/core/framework/IPlatformContext
at com.bear.webcms.servlet.DataServlet.init(DataServlet.java:49)
at javax.servlet.GenericServlet.init(GenericServlet.java:258)
at weblogic.servlet.internal.ServletStubImpl$ServletInitAction.run(ServletStubImpl.java:1099)
at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321)
at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:121)
at weblogic.servlet.internal.ServletStubImpl.createServlet(ServletStubImpl.java:975)
at weblogic.servlet.internal.ServletStubImpl.createInstances(ServletStubImpl.java:954)
at weblogic.servlet.internal.ServletStubImpl.prepareServlet(ServletStubImpl.java:893)
at weblogic.servlet.internal.ServletStubImpl.getServlet(ServletStubImpl.java:598)
at weblogic.servlet.internal.ServletStubImpl.invokeServlet(ServletStubImpl.java:406)
at weblogic.servlet.internal.ServletStubImpl.invokeServlet(ServletStubImpl.java:348)
.......

I did copy all the required jars to WEB-INF/lib and copied platform directory to WEB-INF.

Can someone please shed some light as to whether there are restrictions with WebLogic 8.1 (which works with JDK1.4) and BIRT 2.3.1

Jason Weathersby said...

2.3.1 requires Java 5. Can you upgrade the JDK or use an earlier version of BIRT

Anonymous said...

Thanks, Jason, for clarifying this to me. I think "Deploying and Integrating BIRT Report Engine in Applications" forum is almost dead as I never get any response from there.

So, this means in order to use 2.3.1 I have to move to JDK1.5 and WebLogic 10. Can you please let me know which earlier version of BIRT is supported for WebLogic 8.1/JDK1.4?

Jason Weathersby said...

I believe 2.2.2 will work with JDK 1.4. I am glad you posted to the deploying forum, we are working on getting this heavily monitored.

Anonymous said...

Jason,

I again tried the forum (http://www.birt-exchange.com/forum/deploying-integrating-birt-report-engine-applications) for the issue I'm having (posted it on 2/13/09), but didn't get any response. So, please help with this.

I resolved all the issues (upgrading to JDK1.5,WebLogic10, etc.) and was able to generate the PDF report, but the table is empty. When I debug the application, I see that the data object is present in the task's appContext and there is data. None of this is rendered on the screen. I'm setting the data object in the AppContext.

task = birtReportEngine.createRunAndRenderTask(design);
task.getAppContext().put("CustActDataVec", vTableData_CALDATA);

Jason Weathersby said...

I responded to your thread at:
http://www.birt-exchange.com/forum/deploying-integrating-birt-report-engine-applications/13777-generating-pdf-report-using-report-engine-api.html#post43408

coolguy said...

I tried passing JDBC Resultset as a parameter to the report and it seems to work fine. Please find the URL for more details. Hope it helps someone. http://techieocean.blogspot.com/2009/06/how-to-pass-resultset-to-birt-report.html

Arun said...

Hi,

I tried passing the object as mentioned with scripted DataSource.

The below is the code :-

task.getAppContext().put("stock", data);

And is JS handler :-

Open :-

i = stock.size();

Fetch :-

if(i>0){
row["Line0"]=stock.get(0);
row["Line1"]=stock.get(1);
row["Line2"]=stock.get(2);
row["Line3"]=stock.get(3);
row["Line4"]=stock.get(4);
i--;
return true;
}
else
return false;


But while running it via standalone java application, I am getting the below error :-

... 27 more
28 Feb, 2010 7:19:22 PM org.eclipse.birt.report.engine.script.internal.DtEScriptExecutor handleJS
WARNING: A BIRT exception occurred: There are errors evaluating script "__bm_FETCH()":
ReferenceError: "i" is not defined. (#2). See next exception for more information.
There are errors evaluating script "__bm_FETCH()":
ReferenceError: "i" is not defined. .java:111)
at org.eclipse.birt.data.engine.impl.PreparedScriptDSQuery$ScriptDSQueryExecutor.executeOdiQuery(PreparedScriptDSQuery.java:223)

There are errors evaluating script "__bm_FETCH()":




Could anyone please help me in figuring what is missing ?

Jason Weathersby said...

It is probably not finding your stock class. Where do you have it?
Put this in:

i=0;
i = stock.size();

And you should see a different error.

Diego said...

hi guys, excelent blog by the way.

I 've been spending almost a week trying to create a paginated report with an Hibernate data source anda data set. The main problem we have is that our tables are dammed huge and we have to report them entirely!, so we MUST implement paginated queries to avoid bringing the whole table.

I tried setting the "Max results" parameter in the Hibernate data source and the "Max number of rows to fetch from data source" on the data set, but it seems to ignore theese parameters as the query that is excuted brings the whole table.

Does anyone faced a similar problem?

My las resort is to run the HQL right from java, create that way a dataset and somewhow pass this data set to the report.

Any help will be useful
Thanks in advance

Jason Weathersby said...

Diego,

Which version of BIRT are you using and what Hibernate ODA are you using? You can also use a Scripted Data Source to connect to Hibernate.

Jason

Diego said...

Jason, thanks for your reply.
I'm using Birt 2.5.1 and hibernate 3 on my web app, i dont know where to get the Hibernate ODA version.

I need to use the hibernate integration to de-couple the reports from the rest of the app and therefore be a sort of stand-alone report module. The main problem iam facing now is pagination and huge data sets retrieval avoiding. Thanks very much for your support.

Diego

Jason Weathersby said...

Diego,

I wrote a sample ODA for hibernate and JBOSS supplies a hibernate ODA with its BIRT integration. Are you using either one of these?

Jason

Diego said...

Sorry for not saying this detail before:

I am running the reports on the Birt Report Viewer

Jason Weathersby said...

How are you currently connecting to Hibernate?

Diego said...

I've set up an Hibernate config based on a database.cfg.xml on my webapp. Then i created the Data source just as

http://docs.jboss.org/tools/3.0.1.GA/en/jboss_birt_plugin_ref_guide/html/hibernate_datasource.html

sais so.

Thanks

Jason Weathersby said...

You may want to ask this question on the JBoss forums. Another option is to try a scripted dataset using the hibernate libs. Take a look at this article. It is old but the same principles apply.
http://www.birt-exchange.org/devshare/designing-birt-reports/839-scripted-data-set-with-hibernate/#description

Diego said...

Thanks Jason, i have already posted on birt-exchange, i will also on jboss forums.