SUN Report Builder/Documentation
Contents
- 1 Who Needs Reporting
- 2 Why Sun Report Builder
- 3 System Requirements
- 4 Installing Oracle Report Builder
- 5 Getting Started with Sun Report Builder
- 6 Viewing the Report Navigator in Sun Report Builder
- 6.1 Report Navigator -> Report
- 6.1.1 Report Navigator -> Report -> Properties of General Tab
- 6.1.2 Report Navigator -> Report -> Properties of Data Tab
- 6.1.3 Report Navigator -> Report -> Functions
- 6.1.4 Report Navigator -> Report -> Page Header -> Properties of General Tab
- 6.1.5 Report Navigator -> Report -> Report Header -> Properties of General Tab
- 6.1.6 Report Navigator -> Report -> Groups -> Sorting and Grouping Window
- 6.1.7 Report Navigator -> Report -> Detail -> Properties of General Tab
- 6.1.8 Report Navigator -> Report -> Report Footer -> Properties of General Tab
- 6.1.9 Report Navigator -> Report -> Page Footer -> Properties of General Tab
- 6.1 Report Navigator -> Report
- 7 Common components in any section
- 7.1 Report Controls Toolbar -> Label Field -> Properties of General Tab
- 7.2 Report Controls Toolbar -> Formatted Field -> Properties of General Tab
- 7.3 Report Controls Toolbar -> Formatted Field -> Properties of Data Tab
- 7.4 Report Controls Toolbar -> Image Control -> Properties of General Tab
- 7.5 Report Controls Toolbar -> Image Control -> Properties of Data Tab
- 7.6 Report Controls Toolbar -> Chart/Shape -> Properties of General Tab
- 7.7 Report Controls Toolbar -> Chart/Shape -> Properties of Data Tab
- 7.8 Report Controls Toolbar -> Fixed Line -> Properties of General Tab
- 8 Syntax
- 9 Functions
- 10 Examples
- 11 Charts in report builder
Who Needs Reporting
You may be forgiven for thinking 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 for some practical use. Reports allow you to slice and view data 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. Likewise, you can use reports to generate ready-to-send invoices. If you are learning a foreign language, then 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 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 came 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) was designed to overcome the shortcomings of the built-in reporting capabilities by adding a powerful reporting engine and a slew of nifty tools allowing you to create more advanced reports. The extension adds both advanced sorting and grouping capabilities, as well as the ability to use SQL queries and functions in reports. More importantly, SRB made it significantly easier to create a complex report layout via drag-and-drop. Further, SRB let you insert charts in your reports. As of 2008 the SRB is called ORB (Oracle Report Builder).
System Requirements
The ORB extension requires a fairly recent version of Apache OpenOffice. Ideally, you should use the latest version of the productivity suite. Since ORB is written in Java, you should also check whether the Java Runtime Environment (JRE) software is installed on your machine and whether Apache OpenOffice is configured to use it. To do this, choose Tools → Options → OpenOffice → Java. Make sure 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, then you have to install the JRE software which you can download from www.java.com or from Adoptium.
Installing Oracle Report Builder
Since ORB is an Apache OpenOffice extension, installing it on your machine is not particularly difficult. Download the latest version of ORB from OpenOffice Extension Repository. In Apache OpenOffice, choose Tools → Extension Manager. Press the Add button, select the downloaded .oxt file, and press Open. Restart Apache OpenOffice (on Windows, you have to terminate Quickstarter, too), and you are now ready.
Getting Started with Sun Report Builder
The following make reference to SRB version 1.0.x.
The Sun Report Builder extension adds powerful reporting capabilities to Apache OpenOffice 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 Apache OpenOffice 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 showing article payment sums and charts 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 Apache OpenOffice 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:
Oracle 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 allows you to enable the Add Field, Report Navigator, and Sorting and Grouping palettes.
C - Align and Align at Section toolbar helps you 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 - The Add Field palette allows you to quickly populate the report with fields by dragging them from the palette onto the report.
F - The 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, then 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 should 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 Property's 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 help. You can preview the report by pressing the Execute Report button in the Main toolbar to 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 as well as the grand 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 first need to enable the Group Footer page section. 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, then 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, by 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. Once you've mastered the basics, you can use the reporting capabilities to make sense of the data stored in Apache OpenOffice Base.
Let's identify a few key components of the SRB as seen in the Report Navigator. The Report Navigator is accessed under the View pull-down menu. To view the properties window, toggle it to be displayed under the View pull-down menu. The extended tips identify a lot of information about the components. To view the extended tips, toggle it to be displayed under the Help pull-down menu. The text below supplements the extended tips of your particular SRB version. Further, context help is available for various dialoges by pressing F1.
Shows the various options for the report as a whole. These are different than the various options of individual components of the report. For example, if you use a filter on the report as a whole, then don't expect to perform functions on values you just filtered out.
This controls the layout and presentation of the report.
- Name: The name of your report can be most anything of your choosing.
- Page Header:
- Page Footer:
- Print repeated values: You may not want repeated values printed for every report.
This controls the data from the database that is displayed in the report.
- Content Type: Can be Table, Query, or SQL command
- Content: Values dependent upon Content Type above.
- Analyze SQL command: Whether to allow the SQL command to be analyzed.
- Filter: The SQL filter command to perform on the database data
This controls the functions calculated in the report using OpenFormula. 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.
A function allows the user to perform calculations (see Supported Functions section) on the database data and display the results. Functions can either be in the global context of the report itself or in any group (see Groups section below). Functions are created using View -> Report Navigator -> Report -> Function. Right-click on Function and select New Function. Once you have selected your new function, in the context menu on the functions entry you can customize your function.
- Name: The name of your function can be most anything of your choosing.
- Formula: See Function Example below
- Initial value: See Function Example below
- Deep Traversing:
- Pre evaluation: You may not want the function to be evaluated until the report is finished.
This controls the header on each page of the report. In a multi-page report, this can show at the top of each page. It can contain other components such as text fields, text boxes, images controls, charts, lines, etc. Each of these components also have their respective components.
- Name:
- Visible:
- Height:
- Conditional Print Expression: A conditional print expression allows the user to specify whether an element or section should or should not be visible when executing the report. If the print expression evaluates to true, then the element will be printed in the report. The syntax is the same as for functions.
- Background Color:
- Background Transparent:
This controls the header of the report. In a multi-page report, this will show only once at top of the report. It can contain other components such as text fields, text boxes, images controls, charts, lines, etc. Each of these components also have their respective components.
- Name:
- Force New Page: Whether the current section and/or the next section is printed on a new page
- Keep Together: If the current section doesn't completely fit on a page, then print it on the next page
- Visible:
- Height:
- Conditional Print Expression:
- Background Color:
- Background Transparent:
This controls whether and how you are grouping together database data. You can insert a data grouping into the report by using View -> Sorting and Grouping. It details on which fields, what sort order, what conditions, etc. you will be grouping your data. For more details, please see this.
- Field/Expression: Either the field from the database or the expression. An example of an expressions might be to give the user the possibility to do arithmetic calculations with the value of other expressions as well as with the value of columns. (The actual implementation is part of 4.1 expression engine.)
- Sorting: Ascending or descending
- Group Header: Either show or hide
- Group Footer: Either show or hide
- Group On: What the data is grouped on depends upon the type of data chosen in Field/Expression
- Group Interval: This also depends upon the type of data chosen in Field/Expression
- Keep Together:
This controls the visual presentation of your groupings.
- Keep Together:
- Start New Column: (In future releases, multiple columns will be available from the menu Format -> Page)
- Reset Page Number:
This allows you to perform calculations (see Supported Functions section) on the data groups and display the results. Again, functions are created by right-clicking on Function and select New Function.
- Name:
- Formula: Input using the OpenFormula syntax
- Initial Value: The initial evaluation value of the OpenFormula above
- Deep Traversing:
- Pre Evaluation:
This controls under which condition the group header is displayed as well as many other attributes of the group header.
- Name:
- Force New Page:
- Keep Together:
- Repeat Section:
- Visible:
- Height:
- Conditional Print Expression:
- Background Color:
- Background Transparent:
This controls the details of the report. It is the main body of the report. It can contain other components such as text fields, text boxes, images controls, charts, lines, etc. Undoubtedly, this section contains the database data you want pulled in your report.
- Name:
- Force New Page:
- Keep Together:
- Visible:
- Height:
- Conditional Print Expression:
- Background Color:
- Background Transparent:
This compliments the Report Header section and contains the same components as the Report Header.
This compliments the Page Header section and contains the same components as the Page Header.
Common components in any section
In each of the above sections you can have commonly-used data components from the Report Controls toolbar that make good use of the report. They are as follows:
Report Controls Toolbar -> Label Field -> Properties of General Tab
- Name:
- Label:
- Position X:
- Position Y:
- Width:
- Height:
- Print Repeated Values:
- Conditional Print Expression:
- Font:
- Print When Group Change:
- Background Color:
- Background Transparent:
- (Horizontal) Alignment:
- Vertical Alignment:
Report Controls Toolbar -> Formatted Field -> Properties of General Tab
- Name:
- Position X:
- Position Y:
- Width:
- Height:
- Print Repeated Values:
- Conditional Print Expression:
- Font:
- Print When Group Change:
- Background Color:
- Background Transparent:
- Formatting:
- (Horizontal) Alignment:
Report Controls Toolbar -> Formatted Field -> Properties of Data Tab
- Data Field Type: The kind of data (Field or formula, Function, Counter, User Defined Function)
- Data Field: The columns/fields of the report source
- Function: Such as Sum, Minimum, Maximum
- Scope: Contains all names of all groups. This includes the group where this control is contained in as well as the report itself as top level scope.
See here for more details.
Report Controls Toolbar -> Image Control -> Properties of General Tab
- Name:
- Preserve as link:
- Position X:
- Position Y:
- Width:
- Height:
- Print Repeated Values:
- Conditional Print Expression:
- Print When Group Change:
- Background Color:
- Background Transparent:
- Graphics:
- Scale:
Report Controls Toolbar -> Image Control -> Properties of Data Tab
- Data Field Type:
- Data Field:
- Function:
- Scope:
Report Controls Toolbar -> Chart/Shape -> Properties of General Tab
- Name:
- Position X:
- Position Y:
- Width:
- Height:
- Print Repeated Values:
- Conditional Print Expression:
- Print When Group Change:
- Font:
- Chart Type:
- Preview Rows:
Report Controls Toolbar -> Chart/Shape -> Properties of Data Tab
- Content Type:
- Content:
- Analyze SQL Command:
- Filter:
- Sort:
- Link Master Fields:
- Link Slave Fields:
Report Controls Toolbar -> Fixed Line -> Properties of General Tab
- Name:
- Position X:
- Position Y:
- Width:
- Height:
- Orientation:
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)
Functions
The current Sun Report Builder (version 1.0.x) includes functions in the following categories:
Datetime
DATE, DATEDIF, DATEVALUE, DAY, DAYS, HOUR, MINUTE, MONTH, NOW, SECOND, TIME, TIMEVALUE, TODAY, WEEKDAY, YEAR
Financial
Financial
Information
CHOOSE, COUNT, COUNTA, COUNTBLANK, HASCHANGED, ISBLANK, ISERR, ISERROR, ISEVEN, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISODD, ISREF, ISTEXT, NA
Logical
AND, FALSE, IF, NOT, OR, TRUE, XOR
Math
ABS, AVERAGE, EVEN, MAX, MAXA, MIN, MINA, MOD, ODD, SUM
Rounding
INT
Text
EXACT, FIND, LEFT, LEN, LOWER, MID, REPLACE, REPT, RIGHT, SUBSTITUTE, T, TEXT, TRIM, UNICHAR, UNICODE, UPPER, URLENCODE
Userdefined
NULL
A detailed explanation of the above functions will be found here. Additional details and code can be found here.
CONCAT or CONCATENATE functions are not supported, but you can use [Textfieldname]&" "&[Textfieldname] to concatenate text fields, not forget to format it as text.
Please note that these functions are specific to the SRB. Additional functionality may be obtained from the database backend capabilities. For example, if you are using Apache OpenOffice's native HSQL database, then you may also use HSQL functions and stored procedures along with Apache OpenOffice's queries to generate a wide variety of information shown in reports.
Examples
Function Example
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.
Query Example
The URL below shows how to use queries in Report Builder:
See here.
Computed Fields Example
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
Charts in report builder
You can insert Chart into report builder report, into any places, but only some has a good reason to do.
Not wise to add to details section, it is for single data output, the headers and footer the right place for charts where you print summarized data, and chart for explaining data series, trends, etc.
Open report for edit, and insert Chart into Group/Page/Report header/footer.
When you inserted it shows columns chart as default, now needs to bound to some data source, Select Table/Query/SQL commands. When you selected, the chart shows data what can retrieve from data source.
When you decide to use chart in report useful to create query, which contain only that data what you want to show in that place, without excessive data, because you can not remove them with data ranges dialog as you can do in Calc.
The Charts in report builder has bunch of limitations:
The “First row as label” grayed out and as default selected the data source field names (or aliases names), this is not changeable in through UI in report builder. For this reason I suggest to use query or SQL commands as report/chart source not a Table.
The first field data (column in Calc and in Chart) in table or query, can be used as categories, by selecting “First column as label ”.
The field names bounded to the places in table, the counting starts from 0, the first field is 0.
The first field name presented as “label 0”, the second field name is“label1”, … etc.
You can not select data series interactively, only can remove whole data series from chart. The data series range not changeable, it contain whole data in table/query, and it has an ugly name “0”, “1”, … etc., which you can see in data series window.
The Categories not changeable, it is hard coded to data fields.