blob: 7a155044a5e765683ce7cfce852ec7002f378863 [file] [log] [blame]
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.0 transitional//EN">
<title>BIRT FAQ</title>
<link rel="stylesheet" href="../style/compose.css" type="text/css"/>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<p class="head">BIRT FAQ</p>
<p class="subhead">Data Access</p>
<h2>Q: What is a "data source" and a "data set"?</h2>
BIRT uses these two terms to define how the report accesses data. <br>
<dl class="arrow-list">
<dt>Data Set
<dd>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.
<dt>Data Source
<dd>is a connection to an external system, such as a JDBC
connection. Data sources are optional, some data sets, such as XML or CSV
files, don't need a connection.
Any one BIRT report can contain any number of data sets and
data sources.<h2>Q: Can I build my own data set?</h2>
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:
<dl class="arrow-list">
<dt>Scripted data set
<dd>Best for data sets that are used by one specific report. (For example, to
access a specific set of application objects.)
<dt>Extended data set
<dd>Created using BIRT's 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.)
<h1>Supported Data Sources</h1>
<h2>Q: What data sources does BIRT support?</h2>
<p>BIRT Release 1 supports two data sources:
<dl class="arrow-list">
<dd>Works with any JDBC driver.
<dd>Lets you write code to access other data sources.
<h2>Q: How do I add my JDBC driver to BIRT?</h2>
<p>See the
<a href="../build/install.html">installation instructions</a>.</p>
<h2>Q: Which Oracle JDBC driver do I need?</h2>
<p>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.</p>
<h2>Q: How do I use a stored procedure?</h2>
<p>The JDBC ODA plugin included in BIRT currently supports SQL
SELECT queries and simple stored procedure queries.&nbsp; 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 <br>
output parameter like those in Oracle).</p>
<p>You can access a simple stored procedure call statement using a
statement in the JDBC query editor. Create a JDBC data set with a dummy query.
Use the Edit data set option to open the data set editor. replace the SQL SELECT
statement with your stored procedure call.</p>
<p>We encourage the BIRT community to provide additional Open Data
Access (ODA) extensions to handle various types of data sources, including
complex stored procedures.</p>
<h1>Optimizing Filters &amp; Sorting</h1>
<h2>Q: Does BIRT push sorting &amp; filtering to the database?</h2>
<p>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 &amp; filtering, and so you'll want
BIRT to perform these tasks. </p>
<h2>Q: What's the difference between BIRT filtering and a SQL WHERE clause?</h2>
<p>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.</p>
<p>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.</p>
<h2>Q: When do I use BIRT vs. SQL filtering?</h2>
<p>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
<p class="caution"><span class="caution-head">Release 1.0 Note</span>: BIRT
holds your entire result set in memory. Therefore, it is important that
filtering be done on the database to reduce the amount of memory required by a
BIRT report.</p>
<h2>Q: What's the difference between BIRT sorting and a SQL ORDER BY clause?</h2>
<p>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.</p>
<p>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.</p>
<h2>Q: When do I use BIRT vs. SQL sorting?</h2>
<p>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.</p>
<p class="caution"><span class="caution-head">Release 1.0 Note</span>: BIRT
holds your entire result set in memory, and always sorts your data whether or
not the database already did the sort. (The in-memory sort is slightly faster if
the data is presorted.) In particular, if your report includes grouping, then BIRT
will resort your data, and you must include a BIRT-defined sort order for the
detail rows. A later release may provide a way to tell BIRT to use database
sorting even for reports that have groups.</p>
<p>Of course, if your data source does not support sorting, then, again,
use the sorting features of BIRT to sort your data.</p>
<h1>Scripted Data Sources</h1>
<h2>Q: How do I get data from a POJO (Plain Old Java Object)?</h2>
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
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 three methods: <code>open</code>, <code>fetch</code> and
<code>close</code>. Let's suppose that the <code>open</code>
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 <code>open</code> we might say something like the
<pre class="code-block">// Import your package.
importPackage( );
// Get an instance of the chart node.
var theChart = OrgChartFactory.getChart( );
// The variable iter becomes a property of the data set
// instance object.
iter = theChart.iterator( );
<p>Then, in the fetch method, we'd need to create a row for each org chart
<pre class="code-block">// This variable becomes a property of the data set
instance object;
// Done?
if ( ! iter.hasNext( ) ) { return null; }
// Turn the node into a data row
var node = );
var row = newDataRow( ); = node.getName( );
row.deptNo = node.getDeptNo( );
row.title = node.getTitle( ); = node.getPhoneNo( );
return row;
<p class="caution"><span class="caution-head">Release 1.0M1 Note:</span>
The above code samples are preliminary; the scripted data set is not yet
available, and so its implemetation is subject to change.</p>
It is necessary to copy the data into a data row so that the Report Engine
can resort it. In our case, the iterator may return the data using a
depth-first traversal, but we want it sorted by name to create a phone list.
You could also create the data row in Java if you prefer.</p>
There is some other boilerplate needed to declare the row schema, but the
above illustrates the key point about calling into Java.
<h2>Q: How do I get data from a Java Bean?</h2>
<h2>Q: How do I use XML as a data source?</h2>
<h2>Q: How do I use Web Services as a data source?</h2>
<h2>Q: How do I use web server logs as a data source?</h2>
TBD. The BIRT project is working with the
<a href="">Web Tools Project</a> (WTP)
to create reports from web logs, but the work is very much at an early stage.
<h2>Q: How do I use popular ORM (Object-Relational Mapping) tools such as
Hibernate and JDO as a data source?</h2>
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.
<h2>Q: My class implements java.sql.ResultSet. How do I use it as
a data source?</h2>
TBD. You can access the objects as is described for POJOs above.
<h1>Data Set Parameters</h1>
<h2>How can I use a data set parameter with an SQL query?</h2>
<p>Parameters are still under development, so the following may change in the
run-up to the 1.0 release.</p>
<p>Start with an SQL parameter. That is:</p>
<pre class="code-block">WHERE custID = ?</pre>
<p>Then, follow the SQL rules for
parameters. An SQL &quot;?&quot; Parameter must represent an actual data value such as &quot;Foo&quot;
or 10. You can also do:</p>
<pre class="code-block">WHERE customName LIKE ?</pre>
<p>In which case you could include wildcards such as %. (Note: the wildcards are
<p>Next, define a data set parameter. The parameters must have a
one-to-one correspondence with your &quot;?&quot; markers.</p>
<p>A parameter can be one of three types: input, output, or in/out (both input
and output.) The M2 build has separate properties for input and output, allowing
an invalid fourth state: neither input nor output. Unfortunately, that invalid
fourth state is the default. An SQL SELECT allows only input parameters. At
present, does not automatically mark your parameters as input. So, you'll need
to do that yourself.</p>
<p>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 = &quot;A&quot;]
unless a user of the data set specifically wants some other status.) In BIRT
1.0, the default value should not be needed very often; it will be more useful
when BIRT supports data set reuse. When you decide to test a data set, the UI
will eventually prompt you for the data set parameter values, and give you the option to save
them as a &quot;test configuration.&quot; In the mean time, you must provide a
default (test value) in order to preview your data set. A side effect of this is that, since the default is a test value,
then it should be a constant. In M2,&nbsp; if you enter an expression such as params.RptParam,
you'll get errors.</p>
<p>So, bottom line, in M2, you must enter a default value if you want to test
your data set using the data set preview feature of the data set builder.</p>
<h2>How do I use Data Set parameters?</h2>
<p>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 <a href="params.html">Report Parameters FAQ</a> for
information on doing this.</p>
<p>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 &quot;customer ID&quot; parameter in
the detail database, and bind it to the &quot;customer id&quot; column in the outer
database using the Binding tab within the Property Editor for the List or Table
item that represents the detail report.</p>
<h2>Data Set parameters have an &quot;is nullable&quot; property. What does this mean?</h2>
<p>This means that the value can be a (database) null.</p>
<p>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 &quot;nullable&quot;), and issue a runtime error.</p>
<h1>Application Integration</h1>
<h2>Q: How can I pass a connection object to BIRT?</h2>
Not at present. We're looking into how to achieve this in future release.
<h2>Q: How can I secure the user name and password used to connect
to a data source?</h2>
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
BIRT provides a <i>configuration variable</i> feature that lets you bind named
values to values set outside of the report design. The details of this feature are
still under discussion.
<h2>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?</h2>
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.
<h2>Q: How can I access application-specific metadata to simplify
report design?</h2>
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
While Release 1 of BIRT won't
provide such integration out-of-the-box, others can certainly build on top
of BIRT to provide it. 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.
<h1>Extended Data Sets</h1>
<h2>Q: How can I create a custom extended data set?</h2>
BIRT provides an Open Data Access (ODA) extension framework.
ODA provides extension points to allow anyone to add a custom <i>data
access driver</i>. See the ROM Data Specification for details.
BIRT ODA extension defines 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 BIRT runtime data access interfaces. This interface is similar to JDBC,
and it supports additional capabilities such as any types of data source, 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
At design time, the Eclipse plug-in for BIRT data extension allows you to provide a
designer GUI tool specific to an external data source. The plug-in designer tool
then uses the BIRT 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. The 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 runtime interfaces are JDBC-like, but have been extended to support
additional capabilities of non-RDBMS data sources. An ODA driver basically
implements the public runtime interfaces. This implementation wraps
the data source specific APIs (such as web services, etc.) to retrieve data rows.
For a preview, the ODA runtime interfaces source are available in the
<a href="../build">Eclipse BIRT CVS</a>
source code repository, in the package under the subproject.
The JDBC implementation is available as a sample. It is in the Eclipse
CVS under the and ...oda.jdbc.ui
<h2>Q: Is odaconfig.xml documented anywhere?</h2>
ODA and its configuration files are documented in
the JavaDoc for ODA itself. It is in:
A copy of the ODA configuration's XML schema definition (ODAConfig.xsd) and
related annotations can be found in BIRT's Eclipse CVS, under the
source/ folder.
The JDBC ODA driver's odaconfig.xml is a fairly good example of the main
configurable options.
<h2>Q: My report runs in the Viewer, but produced no output. What
<p>BIRT build M2 and earlier do not yet report database errors, but
this is planned for a future release. 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. If the
data set tests OK in the BIRT designer, but the report won't preview, then you
may need to copy your JDBC driver into the BIRT viewer plugin. (Currently, your
JDBC drivers need to appear in three places: in the plugin directory; again in the plugin directory and once more in the viewer
deployed to your app server. See the <a href="../build/install.html">install
instructions</a> and <a href="../deploy/viewer-deploy.html">viewer integration</a>
instructions for details.</p>