Difference between revisions of "SUN Report Builder/Documentation"

Revision as of 12:19, 21 November 2008

Who Needs Reporting

You may be forgiven for thinking that the sole purpose of reports is to provide nice-looking printouts of database records. A reporting engine provides essential tools for putting the data in a database to some practical use. Reports allow you to slice data and view them in many different ways. As a freelancer, you can use reports to keep track of the time you spend on a particular project, view your earnings during a specified month, and see which customer brings you most work. Besides that, you can use reports to generate ready-to-send invoices. If you are learning a foreign language, you can use reports to print a list of new words for a particular topic and keep track of your progress. You can also create a simple database to keep track of your achievements, and then generate a professionally-looking resume using a report. In other words, you can use reports for a wide variety of tasks.

Why Sun Report Builder

Since OpenOffice.org comes with a built-in reporting feature you might wonder why you'd want to bolt another reporting engine on top of it. For starters, the built-in reporting capabilities can't be used for anything but the most simple reports. While the Report Wizard makes the process of creating a report rather straightforward, the default reporting tools are not very intuitive and not particularly flexible, which limits your ability to customize reports to your liking. The Sun Report Builder (SRB) is designed to overcome the shortcomings of the built-in reporting capabilities by adding a powerful reporting engine and a slew of nifty tools that allow you to create rather advanced reports. The extension adds advanced sorting and grouping capabilities, as well as the ability to use SQL queries and functions in reports. More importantly, SRB makes it significantly easier to create a complex report layout via drag-and-drop. In addition to that, the extension lets you insert charts in your reports.

System Requirements

The SRB extension requires a fairly recent version of OpenOffice.org. Ideally, you should use the latest version of the productivity suite. Since SRB is written in Java, you should also check whether the Java Runtime Environment (JRE) software is installed on your machine, and whether OpenOffice.org is configured to use it. To do this, choose Tools -> Options -> OpenOffice.org -> Java. Make sure that the Use a Java runtime environment check box is ticked, and a JRE is selected in the list of installed JREs. If this list is empty, you have to install the JRE software which you can download from www.java.com.

Installing Sun Report Builder

Since SRB is just a regular OpenOffice.org extension, installing it on your machine is not particularly difficult. First of all, download the latest version of the SRB from the OpenOffice.org Extension Repository. In OpenOffice.org, choose Tools -> Extension Manager. Press the Add button, select the downloaded .oxt file, and press Open. Restart OpenOffice.org (on Windows, you have to terminate Quicklauncher, too), and you are done.

Getting Started with Sun Report Builder

The Sun Report Builder extension adds powerful reporting capabilities to OpenOffice.org Base, and using it to create reports is easy, as we can see with a simple example. Suppose you're a freelance writer, and you want to keep track of your submissions using a simple OpenOffice.org Base database that stores article titles, publications, submission dates, current status, and payment rates. This is a useful solution, but adding reporting capabilities turns the database into a handy analytical tool. With Sun Report Builder you can generate a list of articles grouped by publication, shows the sum of article payments, and displays a chart of payments for each publication.

To make it easier for you to follow the instructions below, I've added the submissions table and the Submissions_by_Publication report to the WriterDB.odb database. This database is part of the Writer's Tools extension which you can download here.

Once you have the database ready, open it in OpenOffice.org Base, switch to the Reports section, and click on the Create Report in Design View link. This opens the Report Designer window, and before you start working on a report, let's take a closer look at Report Designer's essential components:

Sun Report Builder's Interface

A - The Report Controls toolbar contains buttons you can use to add fields, labels, graphics, and graphs to the report.

B - Main toolbar that allows you to enable the Add Field, Report Navigator, and Sorting and Grouping palettes.

C - Align and Align at Section toolbar can help you to align fields and other elements in the report.

D - The blank report page is divided into three default sections: Page Header, Detail, and Page Footer.

E - Using the Add Field palette, you can quickly populate the report with fields by dragging them from the palette onto the report.

F - Sorting and Grouping pallet can be used to group and sort the report's elements.

G - The Report Navigator lets you quickly locate and select a particular element in the report.

H - The Properties pane lets you specify the properties of the currently selected control. For example, if you select a field, you can use the Properties pane to specify the field's data source and properties such as size, position, format, etc.

To create a report that groups all submissions by publication, you have to add a new group header page section by selecting the Publication field from the Sorting and Grouping palette. Specify the desired sorting option (Ascending or Descending) and select Present from the Group Header drop-down list. Use the Add Field palette to place the Publication field inside the Publication Header section. You can then use the available formatting options under the General tab in the Properties pane to format the field to your liking. The Detail section of the page is designed to generate a list of database records. In our case the list will contain records related to a particular publication. Using the Add Field palette, add the desired fields (e.g., Title, Submission Date, and Payment) to the Details section. Aligning fields and labels in the reports can be a bit tricky, but the Align and Align at Section toolbar contains buttons that can help you with that. You can preview the report by pressing the Execute Report button in the Main toolbar, and thereby generate a list of all submissions grouped by publication. Although you can use the created report as it is, you can do a few other things to make it even more useful. For example, you might want to add a field that calculates the sum of payments for each publication and in total. Let's start with the latter. Click on the Text Box button in the Report Controls toolbar, and draw a field in the Page Footer section. In The Properties pane, switch to the Data tab, and select the options as follows:

Data Field Type: Function
Data field: Payment
Function: Accumulation
Scope: Report

To add a field that displays the sum of payments for each publication, you have to enable the Group Footer page section first. Select Present from the Group Footer drop-down list in the Sorting and Grouping palette. Draw then a field in the added Publication Footer, and specify the field's options as follows:

Data Field Type: Function
Data field: Payment
Function: Accumulation
Scope: Group: Publication

You can also add a graph that displays payments by publication. To do this, you first need to create a query that pulls data for the graph. Close the report, switch to the Queries section, and click on the Create Query in Design View link to create a new query. Select the table containing submission data and add the Publication and Payment fields to the query. From the Function drop-down list, select Group in the Publication column and Sum in the Payment column. Save the created query and open the report for editing. Click on the Graph button in the Main toolbar, and draw a graph in the Page Footer section. In the Properties pane, switch to the Data tab, and select Query from the Content type drop-down list and the created query from the Content list.

If you don't fancy the default column graph, you can tweak its properties by double-clicking on the graph to enter editing mode. Here you can specify different settings using the right-click context menu. For example, using the Chart Type menu item, you can change the default column chart to pie chart and add a 3-D look to it. As you can see, creating reports with Sun Report Builder is straightforward, and once you've mastered the basics, you can use the reporting capabilities to make sense of the data stored in OpenOffice.org Base.

Conditional Print Expression

A conditional print expression allows the user to narrow the output which should be visible when executing the report. If the condition evaluates to true then the element (section or element) will be printed in the report. The syntax is the same as for functions.

Functions

A function can be created when opening the report navigator. In the context menu on the functions entry you can create one function.

This function can either be in the global context of the report itself or in any group.

The illustration below shows how subtotal fields are created in Report Builder, using functions :

The report source table is shown on the upper left.

The resulting report layout is illustrated below the table. A group has been defined on the plProductType field. The sum field is created on the group footer. The rightmost part of the image shows the definition of a function that serves as the data source for the sum field.

Computed fields

Computed fields can be created in the way that the formula is entered directly into the data field.

E.g. 100*[Population]/[PopulationCount] to get the percentage of the current population

Syntax

quote for [ is \
\\
[] as reference for formula or field name
"" to quote strings
. as decimal separator
date as in java specific
parameter separator is ; (semicolon) and it is allowed to let some parameters empty inside a call like XYZ(;kk;;kkb)

Supported Functions

Generally all functions which are defined in the OpenFormula should be supported. But this is currently still ongoing work and may change after each release of the Sun Report Builder.

The Sun Report Builder Verson 1.0.x includes functions in the following categories:

Datetime
Date, Datetime, Day, Hour, Month, Now, Year, Today, Weekday, Time,

Financial
Financial

Information
Choose, HasChanged, Information, IsBlank, IsErr, IsError, IsEven, IsLogical, IsNA, IsNonText, IsNumber, IsOdd, IsRef, IsText, Na

Logical
And, False, If, Not, Or, True, Xor

Math
Abs, Average, Even, Odd, Sum

Rounding
Int

Text
Exact, Find, Left, Len, Lower, Mid, Replace, Rept, Right, Substitute, Text, T, Trim, Upper, URL

Userdefined
Mapped, Null

A detailed explanation will be found here.

EuroOffice Map Chart

One extension further extends the functionality of Sun Report Builder. A detailed description of its use together with the basic steps of creating your first report can be found here. This tutorial goes from creating your first report to defining a quite complicated new function, so it can be a useful resource for beginners and advanced users alike (not just for those looking for a way to create map charts).