October 19, 2007

Sun Report Builder: Better reporting in OpenOffice.org

Author: Dmitri Popov

OpenOffice.org Base is undoubtedly a powerful database application, but when it comes to its built-in reporting engine, words like "underpowered" and "outdated" come to mind. Fortunately, you don't have to put up with this situation any longer: with the Sun Report Builder (SRB) extension, you can add nifty reporting features based on Pentaho reporting engine -- assuming you can figure out how to use it without any help.

Pentaho develops top-notch open source business intelligence and reporting tools. The new Report Builder is the first fruit of a collaboration between Sun and Pentaho announced a few months ago. While the first incarnation of the report builder is not as powerful as Pentaho's own reporting tools, it's still a huge improvement over the existing reporting feature. The only fly in the ointment is that Sun Report Builder works only with OpenOffice.org 2.3 or higher, so users of older versions must upgrade the office suite to take advantage of the new tool.

Since SRB is distributed as an .oxt package, you can install it as a conventional OpenOffice.org extension. To do this, download the latest sun-report-builder_x.x.x.oxt package, launch OpenOffice.org, and choose Tools -> Extension Manager. Select the My Extensions section and press the Add button. Select the downloaded extension, press OK, and restart OpenOffice.org.

Unlike other extensions, SRB doesn't add any menu items: instead, it integrates directly into OpenOffice.org Base. To open the report builder, you have to switch to the Reports section in the Base main window, and click the Create Report in Design View link.

119870_Thumb.png

SRB looks and acts a lot like Base's Form Builder, so if you've created forms before, you will quickly figure out SRB's interface and basic features. By default, the blank report window is divided into three sections: Page Header, Detail, and Page Footer. The header and footer sections are usually used for things like report name, date, and page count. The dynamic content pulled by the report from the database goes in the Detail section. The Properties window to the left shows all the options that are available for the currently selected element in the report, while the Report Controls and other toolbars give quick access to report design tools.

To design a report, you add fields to the appropriate page sections and specify their properties. Before you can add any field, however, you have to specify which table the report should use. Press the Report Navigator button in the Main toolbar (or press F5) and select the top Report item. In the Properties window, switch to the Data tab, select Table from the Content type drop-down list, then select the table you want to use from the Content list.

To add a field to the report, press the appropriate button on the Report Controls toolbar (e.g. Text Box), and draw a field where you want it to appear. Under the Data tab in the Properties window, select the desired data field, and you are done.

Sorting and grouping the data in a report are equally straightforward. Choose View Sorting and Grouping, select the field you want to use as a group or sort key, and specify the available properties. When you add sorting or grouping criteria, SRB inserts a new page header where you can add the desired grouping or sorting title.

Besides simple text fields, SRB allows you to create calculation fields called functions. To create a function, right-click on the Function item in the Report Navigator and choose New Function. Specify the calculation you want in the Formula field in the Properties window and enter the function's name in the Name field. You can then bind the created function to a field in the report.

While creating functions is not particularly complicated, the difficult part is figuring out how exactly to write formulas. SRB comes without any documentation, so what operators and formats are supported is still a mystery.

This is, indeed, the weakest point of the current release of SRB. Obviously, it sports a few clever features, but the complete lack of online help or any kind of documentation renders them virtually useless. The SRB wiki contains a few initial notes, but they cannot be considered documentation by any stretch of imagination.

All in all, Sun has done a commendable job with the first release of the reporting tool, but until the software is appropriately documented, most of its advanced features will leave average users scratching their heads.

Category:

  • Office Software
Click Here!