BIRT/FAQ/Data Access

< To: BIRT/FAQ

General

Q: What is a "data source" and a "data set"?

BIRT uses these terms to define how the report accesses data.

• Data Set – is a description of data to be retrieved. SQL queries are the ones most often used, but BIRT also supports a wide variety of sources such as stored procedures, Java classes and so on.
• Joint Data Set - A Joint Data Set is combination of two Data Sets created using an INNER or (Left/Right) OUTER join.
• Data Source – is a connection to an external system, such as a JDBC connection.

Any one BIRT report can contain any number of data sets and data sources.

Q: Can I build my own data set?

Yes. The only requirement is that your data set return data in tabular format so that BIRT can perform sorting, aggregation, grouping and so on. There are two ways to build a data set:

• Scripted data set – Best for data sets that are used by one specific report. (For example, to access a specific set of application objects.)
• Extended data set – Created using the Data Tools Open Data Access (ODA) framework. Extended data sets are best when creating a generic feature used access a type of data source. (For example, to access web logs.)

Supported Data Sources

Q: What data sources does BIRT support?

BIRT supports the following data sources:

• JDBC – Works with any JDBC driver.
• Scripted – Lets you write code to access other data sources.
• FlatFile - Allows acessing flat file data using standard delimiters.
• XML - XML files or streams can be used as a data source. XPath syntax is used to specify columns.

Q: How do I add my JDBC driver to BIRT?

See the installation instructions.

Q: Which Oracle JDBC driver do I need?

Use the ojdbc14.jar driver. The older classes12.jar drivers are for use with Java JDK 1.2 and 1.3. The ojdbc14.jar drivers are for use with JDK 1.4, which is what BIRT uses.

Q: How do I connect to an ODBC data source?

Sun offers a reference JDBC-ODBC bridge that is included in the JDK. This can be used by entering

sun.jdbc.odbc.JdbcOdbcDriver

in the driver url for the JDBC data source. Next enter a database URL similar to:

jdbc:odbc:myodbcdatabase

Q: How do I use a stored procedure?

The JDBC ODA plugin included in BIRT currently supports SQL SELECT queries and simple stored procedure queries. That is, those that 1) use scalar input parameters only or no parameters, and 2) retrieve a single result set directly, like those in SQL Server or Sybase (instead of via a cursor output parameter like those in Oracle).

BIRT does support Input/Output Parameters from stored procedures:

{call testProcedure(?)}

If the parameter in this example is an output parameter, you could reference it in the expression builder like:

outputParams["param1"]

Q: Can I use the same report against two JDBC data sources?

When writting a query against some JDBC data sources change the case of the result columns. For example

select id,url from table;
table with two columns:

Some databases will return id others will return ID. When this value is used in BIRT expressions, it will default to what the database returned. dataSetRow["id"] or dataSetRow["ID"]. This will prevent the same report from working with the different databases. One solution to this problem is to use the alias column in the Data Set Editor. If you initially design the report and the output column is in lowercase, add the column name in uppercase in the alias field. This should allow the Expression Builder to reference it as either value.

Q: What is the precedence when both JDBC URL and JNDI name are specified?

You can specify JDBC Driver URL and/or JNDI name. The JNDI service can be used when BIRT is deployed to an application server. It can also be used in BIRT Report Designer for data preview, if your JNDI service provider supports client-side access. You may use a jndi.properties resource file to configure the JNDI initial context environment. See description in Bugzilla 131662 for more details.

If your report design contains a JDBC data source defined with both JDBC Driver URL and JNDI name, then JNDI service will be used first and JDBC driver will be used as fallback. The username and password properties, if specified, are used in both cases. When you need to ensure that JNDI service is really used, increase logging level of BIRT engine; in case of JDBC fallback you will see JNDI lookup errors in the logs.

Optimizing Filters & Sorting

Q: Does BIRT push sorting & filtering to the database?

No, BIRT will not modify your SELECT statement in any way. Real-world SELECT statements are often complex and carefully crafted, and you can count on BIRT to use the statement as you wrote it. This gives you control over whether filtering and sorting is done within BIRT or within the database. Further, BIRT works with a wide variety of data sources, not just SQL. Many non-SQL data source do not provide the means to do sorting & filtering, and so you'll want BIRT to perform these tasks.

Q: What's the difference between BIRT filtering and a SQL WHERE clause?

BIRT provides filtering capabilities in both the data set editor and in the Property editor for report items. These filters can include JavaScript expressions, including calls into your Java code. BIRT executes such expressions in its own data engine.

The SQL SELECT statement also performs filtering in the WHERE clause. In the case, the filtering happens on the database, and cannot include JavaScript code: it must include only SQL valid for your target database.

Q: When do I use BIRT vs. SQL filtering?

In general, use an SQL WHERE clause whenever possible. It is more efficient to restrict the data within the query to eliminate the overhead of sending the data to BIRT. However, if you are not using a SQL database, or your expression requires JavaScript or Java code, then, go ahead and perform the filtering within BIRT.

Q: What's the difference between BIRT sorting and a SQL ORDER BY clause?

BIRT provides a sorting option on tables and lists. When you define groups, BIRT implicitly defines a sort order. When using these options, BIRT will perform the sorting within BIRT's own data engine.

If your SELECT statement has an ORDER BY clause, and you omit the sort specification within BIRT, then you can ensure that the sorting is done on the database instead of in BIRT.

Q: When do I use BIRT vs. SQL sorting?

For performance, use SQL sorting whenever possible, especially if your database has indexes that will speed up the sort. If your report has no groups, and your SELECT statement has the correct ORDER BY clause, then simply do not specify a sort order within BIRT.

Scripted Data Sources

Q: How do I get data from a POJO (Plain Old Java Object)?

This can be done using the scripted data set element. Let's take an example: exposing an org chart as a data set. Let's assume that the org chart is represented by a tree, and that the report wants to present this data in the form of, say, a phone list. We'll need to write some "glue" code to link the POJO to BIRT.

Next, we need to identify the right element within the design to hold the glue code. In this case, since we're creating a data set, we'd choose a "scripted data set."

Then, we work out exactly what the glue code needs to do. The scripted data set has four methods: describe, open, fetch and close. Let's suppose that the open method will get a handle to our org chart, perhaps by calling a factory method exposed in Java. JavaScript has an import statement that creates a bridge to our Java objects. So, we'd import, say, the factory class and the "org chart node" class. In open we might say something like the following:

// Import your package.

importPackage( Packages.com.company.YourApp );

// Get an instance of the chart node.

var theChart = OrgChartFactory.getChart( );
// the jar containing the class should be in : 
//             eclipse\plugins\org.eclipse.birt.report.viewer\birt\WEB-INF\lib or
//             eclipse\plugins\org.eclipse.birt.report.viewer\birt\scriptlib  
//

// The variable iter becomes a property of the data set
// instance object.

iter = theChart.iterator( );

Then, in the fetch method, we'd need to create a row for each org chart node:

// Done?
if ( ! iter.hasNext( ) ) { return false; }
// Turn the node into a data row
var node = iter.next( );
row.name = node.getName( );
row.deptNo = node.getDeptNo( );
row.title = node.getTitle( );
row.phone = node.getPhoneNo( );
return true;

Q: How do I get data from a Java Bean?

See: How do I get data from a POJO (Plain Old Java Object)?

Q: How do I get data from an EJB?

There are many ways to do this, but it is probably most appropriate to create an intermediate class, that calls the EJB and returns the values. You would then treat this class as any DAO/POJO object within BIRT, to retrieve the data.

For example if I have an EJB with the JNDI name "greeter", that has a method getGreeting() that returns a string, I would create a class that has two methods like

// Example Methods
public void setupEJB( String contextString, String providerString, String JNDIName ) throws Exception {
     Properties p = new Properties();
     p.put("java.naming.factory.initial", contextString );
     p.put("java.naming.provider.url", providerString );
     initContext = new javax.naming.InitialContext(p);
     Object objref = initContext.lookup(JNDIName);
     myGreeterHome = (GreeterHome)PortableRemoteObject.narrow(objref,
                                           GreeterHome.class);
}
public String makeCall() throws Exception {
     myGreeterRemote = myGreeterHome.create();
     String theMessage = myGreeterRemote.getGreeting();
     return (theMessage);
}

Within the scripted data set you would then call these methods like

// Open method of the data set
ejbclient = new Packages.GreeterEjbClient();
ejbclient.setupEJB( "com.sun.jndi.cosnaming.CNCtxFactory", "iiop://localhost:3700", "greeter" );
//assuming only one row will be needed
rowcount = 0;

//Fetch method of the data set
if( rowcount > 0 ){
	return false;
}else{
	var rtnString = ejbclient.makeCall();
	dataSetRow["message"] = rtnString;
	rowcount++;
	return true;
}

//Close method of the data set
ejbclient = null;

Q: How do I use XML as a POJO Scripted Data Source?

BIRT Supports an XML data source. See the examples page for a demonstration. Another method is to use the Scripted Data Source to communicate to Java objects that are used to parse and interogate the raw XML.

Scripted Data Sources always have three basic tasks:

• Open - Initialize connections and Instantiate required Java Objects
• Fetch - Bind values to the dataSetRow
• Close - Clean up any connections and Java Objects

The following code is typical for an open method. In this case we are creating a URL object, and a DataInputStream to that object.

rurl= new Packages.java.net.URL("http://rss.cnn.com/rss/cnn_topstories/rss");
is = rurl.openStream();
dis = new Packages.java.io.DataInputStream( new Packages.java.io.BufferedInputStream( is ));

In the Fetch method we will use the DataInputStream to access the content of the XML Stream. This code reads one line at a time from the XML URL and sets a row variable values which are used in the report. Obviously, you will want use an XML parser in this method to parse the document and set several row variable values based on all of the XML attribute and entity values.

if( ( rline=dis.readLine() != null ){
     dataSetRow["Test"] = rline;
     return true;
}
return false;

In the close method of the data set, you would clean up any objects or connections that are used while acquiring the data.

rurl = null;
is = null;
dis = null;

Q: How do I use Web Services as a data source?

TBD.

Q: How do I use popular ORM (Object-Relational Mapping) tools such as Hibernate and JDO as a data source?

JDO (Java Data Objects) is used to connect to any source (relational or non-relational) while Hibernate is used to connect to relational databases.

TBD. You can access the objects as is described for POJOs above. An example of this will be placed on the web site shortly.

Q: My class implements java.sql.ResultSet. How do I use it as a data source?

TBD. You can access the objects as is described for POJOs above.

Data Set Parameters

Q: How can I use a data set parameter with an SQL query?

Start with an SQL parameter. That is:

WHERE custID = ?

Then, follow the SQL rules for parameters. An SQL "?" Parameter must represent an actual data value such as "Foo" or 10. You can also do:

WHERE customName LIKE ?

In which case you could include wildcards such as %. (Note: the wildcards are DB-specific.)

Next, define a data set parameter. The parameters must have a one-to-one correspondence with your "?" markers.

A parameter can be one of three types: input, output, or in/out (both input and output.) An SQL SELECT allows only input parameters.

Data set parameters take a default value. In theory, the default value should not be required unless it makes logical sense for your report design. (For example, get active customers [status = "A"] unless a user of the data set specifically wants some other status.) It is possible to specify a report parameter as a default value, such as params["status"].

Q: How do I use Data Set parameters?

There are two common ways to use data set parameters. First, you can bind them to a report parameter so that your user can select the data to display in the report. See the Report Parameters FAQ for information on doing this.

Second, you can pass in data from another part of your report. For example, you can create a subreport that takes its parameter value from a master report. For example, you might get a list of customers (the master report) from an Oracle database. Then, for each customer, you might get the list of orders (the detail report) from a DB2 database. You'd create a "customer ID" parameter in the detail database, and bind it to the "customer id" column in the outer database using the Binding tab within the Property Editor for the List or Table item that represents the detail report.

Here's how it works at runtime. If a report item bound to a data set has a parameter binding, evaluate that expression and use the value as the data set parameter. If not, then check if the data set parameter has a default value and use that. Otherwise, use null as the parameter value. Then, check if the parameter value is null. If it is, and if the parameter does not allow null (is not "nullable"), issue a runtime error.

Application Integration

Q: How can I pass a connection object to BIRT?

A JNDI property exist for the JDBC Data Source that allows a connection to be retrieved from a pool.

As an example you can setup connection on Tomcat as follows.

Reference Tomcat 5.5 Documents

1. Install Your JDBC Driver Make an appropriate JDBC driver available to both Tomcat internal classes and to your web application. This is most easily accomplished by installing the driver's JAR file(s) into the $CATALINA_HOME/common/lib directory.

2. Declare Your Resource Requirements

In the webapps/WebViewerExample/WEB-INF/web.xml file, add the following entry to setup your JNDI service:

   <resource-ref> 
       <description>Resource reference to a factory for java.sql.Connection</description> 
       <res-ref-name>jdbc/MySqlDB</res-ref-name> 
       <res-type>javax.sql.DataSource</res-type> 
       <res-auth>Container</res-auth> 
   </resource-ref> 

where MySqlDB is your JNDI name.

3. Configure Tomcat's Resource Factory

To configure Tomcat's resource factory, add an element like this to the webapps/WebViewerExample/META-INF/context.xml file in the web application.

 <Resource name="jdbc/MySqlDB" 
       auth="Container" 
           type="javax.sql.DataSource" 
       maxActive="5" maxIdle="-1" maxWait="10000" 
       username="myuser" password="mypassword" 
           driverClassName="com.mysql.jdbc.Driver" 
       url="jdbc:mysql://localhost:3306/classicmodels" 
       description="MySQL Sfdata DB"/> 

4. Set the JNDI property in your report to: java:comp/env/jdbc/MySqlDB

Q: How can I secure the user name and password used to connect to a data source?

The designer prompts you to enter the server name, user name and password when building a JDBC data source. While this works for testing, production systems will often externalize this information from the report design so that the same design can work against both a test and production database, or against different production databases.

BIRT provides property binding for Data Sources and Data Sets that can accommodate this scenario. Alternatively, you can reference a dynamic Connection Profile stored in an encrypted profile file.

Q: My customers generally don't know SQL, XML, database schemas etc. They just know the domain specific terms. Can I create a custom data source and custom data set designer?

Yes, BIRT provides excellent extension features via ODA as described above. You can provide both the run-time mechanism to access the data, as well as the design-time UI for defining your data set.

If your users are comforatable using a tool such as Eclipse, then they can use your custom UI to build reports within the BIRT designer. If, however, your users want a very simple tool, you can provide your own designer that asks the right questions, then generates the report for them, filling in all the "boiler plate", perhaps using one of several standard layouts defined by your app. This reflects the "embedded" nature of BIRT.

Q: How can I access application-specific metadata to simplify report design?

Some applications want to integrate BIRT into their application so that BIRT has access to the Business Process Model (BPM) (or other metadata) for the application's data objects and their schemas. This avoids having the report designer duplicate the efforts of the BPM designer. The result allow the report design tool to have Business Process Intelligence built-in.

To see how, let's divide reporting into two steps: design and execution. The design step is the one where BPM integration would likely occur.

A BIRT design is an XML file that contains instructions for accessing a data source, transforming the data (sorting, filtering, totals), and presenting the results. An integrated application needs the ability to build a query based on the BPM. Once the report developer has that data, he'd use BIRT's features for identifying totals and formatting the results. The result of this process would be a BIRT XML design file that the BIRT Report Engine executes to produce your final report.

There are two ways that you can integrate BIRT with the BPM system at design time.

First, you can develop a custom data set builder extension to the BIRT designer. The builder could leverage the meta-data information in the BPM. The custom builder would be integrated with BIRT using the extension features of Eclipse. The result would be a custom data set that looks to BIRT just like any other data set.

Suppose that the BPM system identifies that there is a Customer concept, and that Orders are related to Customers. Suppose further that the BPM system can provide descriptive information about customers, and about the fields for a customer. A custom data set builder could display this information to help the user understand the data, and use the relationships to help the user choose the data of interest. The details, of course, are likely to depend on the specific BPM system in question.

Second, you can develop a custom report designer that is tightly integrated with the BPM system. Suppose that the system provides sufficient information to generate a wide range of reports. Suppose further that the application requires only a small set of formats, so that designs could be created automatically. In this case, you can write an application that generates BIRT designs directly from the BPM system. You can then directly execute those designs in the BIRT report engine, or load them into the BIRT designer for further refinement.

In either case, you'd leverage one of BIRT's key goals: the ability for others to add application-specific functionality to BIRT.

Extended Data Sets

Q: How can I create a custom extended data set?

BIRT uses the Data Tools Platform (DTP) Open Data Access (ODA) extension framework. ODA provides extension points to allow anyone to add a custom data access driver. See the DTP Connectivity page for details.

DTP ODA extension points define a set of public interfaces. It adopts the Eclipse plug-in framework to provide a designer tool, and a runtime driver for data retrieval during report generation.

In short, a custom data source implements the runtime data access interfaces. The implementation wraps the data source specific APIs (such as web services, etc.) to retrieve data rows. The ODA runtime interfaces are JDBC-like, but have been extended to support additional capabilities of non-RDBMS data sources. For example, the use of data source specific query text (i.e. not SQL specific), complex parameters, etc. A ResultSet-like interface with a next method implements an iterator, which your implementation could control and optimize the data generation or retrieval.

At design time, the Eclipse plug-in for data extension allows you to provide a designer GUI tool specific to an external data source. The plug-in designer tool then uses the DTP ODA public API to specify the data set and data source definitions. The custom data source and data set information is saved in the report design file. BIRT Data Engine passes the data definition to the extension's ODA runtime driver. The driver can use this information for query execution. ODA then uses the result set API to retrieve results.

The ODA source are available in the Eclipse Data Tools Platform SDK download, and in DTP CVS source code repository.

The ODA JDBC implementation is available as a sample. It is in the Eclipse CVS under the org.eclipse.birt.report.data.oda.jdbc and ...oda.jdbc.ui subprojects.

Q: Are the DTP ODA extension points documented anywhere?

The DTP ODA extension point reference doc describes how its elements and attributes are used. The ODA public interfaces API are described in the packages Javadoc. They are available either from the source, or DTP doc plugin (org.eclipse.datatools.doc.isv_<version>.jar) that is provided in the DTP SDK download. With the doc plugin installed, in Eclipse IDE, select Help menu -> Help Contents, and select the "Data Tools" book.

ODA API Use Cases

• Creating a Custom ODA Data Source
• Extending an ODA Runtime Driver
• Consuming ODA Data Sources

Troubleshooting

Q: My report runs in the Viewer, but produced no output. What happened?

If your report works within Eclipse when you preview, but does not work when run in your app server, then one possible problem is that you need to copy your JDBC driver into your app server. See the install instructions and viewer integration instructions for details.