| <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.0 transitional//EN"> |
| <html> |
| |
| <head> |
| <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"> |
| </head> |
| |
| <body> |
| |
| <p class="head">BIRT FAQ</p> |
| <p class="subhead">Data Access</p> |
| |
| <h1>General</h1> |
| |
| <h2>Q: What is a "data source" and a "data set"?</h2> |
| <p> |
| 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. |
| </dl> |
| Any one BIRT report can contain any number of data sets and |
| data sources.<h2>Q: Can I build my own data set?</h2> |
| <p> |
| 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.) |
| </dl></p> |
| |
| <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"> |
| <dt>JDBC |
| <dd>Works with any JDBC driver. |
| |
| <dt>Scripted |
| <dd>Lets you write code to access other data sources. |
| </dl></p> |
| |
| <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. 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 & Sorting</h1> |
| |
| <h2>Q: Does BIRT push sorting & 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 & 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 |
| BIRT.</p> |
| |
| <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> |
| <p> |
| 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. |
| <p> |
| 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." |
| <p> |
| 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 |
| following: |
| |
| <pre class="code-block">// Import your package. |
| |
| importPackage( com.company.YourApp ); |
| |
| // 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( ); |
| </pre> |
| <p>Then, in the fetch method, we'd need to create a row for each org chart |
| node: |
| |
| <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 = iter.next( ); |
| var row = newDataRow( ); |
| row.name = node.getName( ); |
| row.deptNo = node.getDeptNo( ); |
| row.title = node.getTitle( ); |
| row.phone = node.getPhoneNo( ); |
| return row; |
| </pre> |
| |
| <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> |
| <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> |
| <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> |
| <p>TBD.</p> |
| |
| <h2>Q: How do I use XML as a data source?</h2> |
| <p>TBD.</p> |
| |
| <h2>Q: How do I use Web Services as a data source?</h2> |
| <p>TBD.</p> |
| |
| <h2>Q: How do I use web server logs as a data source?</h2> |
| <p> |
| TBD. The BIRT project is working with the |
| <a href="http://www.eclipse.org/webtools">Web Tools Project</a> (WTP) |
| to create reports from web logs, but the work is very much at an early stage. |
| </p> |
| |
| <h2>Q: How do I use popular ORM (Object-Relational Mapping) tools such as |
| Hibernate and JDO as a data source?</h2> |
| <p> |
| JDO (Java Data Objects) is used to connect to any source (relational or non-relational) |
| while Hibernate is used to connect to relational databases. |
| <p> |
| TBD. You can access the objects as is described for POJOs above. |
| </p> |
| |
| <h2>Q: My class implements java.sql.ResultSet. How do I use it as |
| a data source?</h2> |
| <p> |
| TBD. You can access the objects as is described for POJOs above. |
| </p> |
| |
| <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 "?" Parameter must represent an actual data value such as "Foo" |
| 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 |
| DB-specific.)</p> |
| |
| <p>Next, define a data set parameter. The parameters must have a |
| one-to-one correspondence with your "?" 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 = "A"] |
| 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 "test configuration." 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, 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 "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.</p> |
| |
| <h2>Data Set parameters have an "is nullable" 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 "nullable"), and issue a runtime error.</p> |
| |
| <h1>Application Integration</h1> |
| |
| <h2>Q: How can I pass a connection object to BIRT?</h2> |
| <p> |
| Not at present. We're looking into how to achieve this in future release. |
| </p> |
| |
| <h2>Q: How can I secure the user name and password used to connect |
| to a data source?</h2> |
| <p> |
| 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. |
| <p> |
| 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. |
| </p> |
| |
| <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> |
| <p> |
| 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. |
| <p> |
| 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. |
| </p> |
| |
| <h2>Q: How can I access application-specific metadata to simplify |
| report design?</h2> |
| <p> |
| 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. |
| <p> |
| 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. |
| <p> |
| 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. |
| <p> |
| There are two ways that you can integrate BIRT with the BPM system at |
| design time. |
| <p> |
| 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. |
| <p> |
| 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. |
| <p> |
| 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. |
| <p> |
| In either case, you'd leverage one of BIRT's key goals: the ability for |
| others to add application-specific functionality to BIRT. |
| </p> |
| |
| <h1>Extended Data Sets</h1> |
| |
| <h2>Q: How can I create a custom extended data set?</h2> |
| <p> |
| 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. |
| <p> |
| 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. |
| <p> |
| 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 |
| retrieval. |
| <p> |
| 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. |
| <p> |
| 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. |
| <p> |
| For a preview, the ODA runtime interfaces source are available in the |
| <a href="../build">Eclipse BIRT CVS</a> |
| source code repository, in the org.eclipse.birt.data.oda package under the |
| org.eclipse.birt.data subproject. |
| <p> |
| The 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. |
| </p> |
| |
| <h2>Q: Is odaconfig.xml documented anywhere?</h2> |
| <p> |
| ODA and its configuration files are documented in |
| the JavaDoc for ODA itself. It is in: |
| <br> |
| source/org.eclipse.birt.data/src/org/eclipse/birt/data/oda |
| <p> |
| 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/org.eclipse.birt.data/schema folder. |
| The JDBC ODA driver's odaconfig.xml is a fairly good example of the main |
| configurable options. |
| </p> |
| |
| |
| <h1>Troubleshooting</h1> |
| |
| <h2>Q: My report runs in the Viewer, but produced no output. What |
| happened?</h2> |
| |
| <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 |
| org.eclipse.birt.report.data.oda.jdbc plugin directory; again in the |
| org.eclipse.birt.report.viewer 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> |
| |
| |
| </body> |
| </html> |