- About Us
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 from the project's SVN repository.
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, which has three key components. The Report Controls toolbar contains buttons you can use to add fields, labels, graphics, and graphs to the report. The Properties pane to the right 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, and format. Three additional palettes -- Report Navigator, Sort and Grouping, and Add field -- are hidden by default, but it's a good idea to enable them from the start by choosing View -> Report Navigator, View -> Sorting and Viewing, and View -> Add Field. The Report Navigator lets you quickly locate and select a particular element in the report, while the Sorting and Grouping palette can be used to group and sort the report's elements. Using the Add Field palette you can quickly populate the report with fields by dragging them from the palette onto the report.
The blank report page is divided into three default sections: Page Header, Detail, and Page Footer. 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:
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:
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.
Every Monday we highlight a different extension, plugin, or add-on. Write an article of less than 1,000 words telling us about one that you use and how it makes your work easier, along with tips for getting the most out of it. If we publish it, we'll pay you $100. (Send us a query first to be sure we haven't already published a story on your chosen topic recently or have one in hand.)