Database Reports

OpenOffice.org as a database front-end


Databases contain data. The data in a database is organised to conform to requirements of database systems. For use by humans, the data often needs to organized differently. Also : data are just data. To become information, you want to see relations, or trends, or look at the same data from different angles. Or maybe you just want to get selected addresses from a database and use them in form letters, address labels or name tags, or to create an address list.
This is done with what is called 'reports'.

Reports are created with report tools. In this paper, we'll look into how we can use OpenOffice.org as a report tool and database front-end, against MySQL, MS SQL, and Oracle databases. With Base, OpenOffice.org's database, you can even input data into tables of these database back-ends, or add tables, create queries, etc, so it can be used as an easy database administration tool (although limited : you can't use it for user and privilege management unless by inserting/modifying data in the database system tables directly).

The advantage of using OpenOffice as a database front end is that you do not need to invest in specialized report tools, that users can work with reports in an office environment they're familiar with and handle the presentation of the data themselves, and that you leverage the strong points of each application : an rdbms to store and manage data, the Writer word processor to create, edit, format, ... reports with data from the rdbms, the Base database application to create views or queries, forms, ... and so on. Unless you need elaborate reporting and Business Intelligence tools, OpenOffice.org may be all the database front-end you'll ever need.

Both MySQL and OpenOffice run on Linux and Windows. Oracle can run on Linux and Windows as well. So we can have several combinations of client- and server operating systems. I won't cover them all, but the examples given here will probably get you in the right direction should you want to try a different combination. For an existing database, it won't really matter what operating system it runs on, as the client will set up the connection to the database through native or generic client software (ODBC, JDBC, Oracle client tools, ...) specific to the client operating system.


MySQL (Linux) with OpenOffice.org (Linux, JDBC)

The example here will be based on a small test database on a Linux server. You can repeat this setup, or adapt what follows to your situation, like when you're connecting to an existing database. ( set up a MySQL database). What you'll need to know is

Concept / Design

The concept of what we'll try to do is to create an OOo Base database that shows us the tables from the MySQL database. These tables can be used to create views and queries in OOo Base. We can also use Base forms to create a user interface that allow us to input or modify data in the mysql database, or can be used to look-up data. This also allows you to create tables in the MySQL database (if you have sufficient database privileges to do so). The report wizard in OOo Base can be used to create database reports. Furthermore, we can create mail merge form letters in OOo Writer, using data from our MySQL database (through the BASE database). And so on.
Something like this : concept scheme

If you're planning on using OpenOffice as an end-user tool, creating some appropriate views might be a wise decision.

setting up JDBC

The generic database connector on Linux, especially for client applications written in or supporting java, is JDBC, Java Database Connectivity. As OpenOffice supports Java, JDBC is the preferred approach. You can also use ODBC, and with OpenOffice on a Windows system, ODBC will actually be the preferred choice.

Here's how to setup JDBC for Open Office on a Ubuntu Linux desktop. The procedure will be similar or near identical on other Linux distribitions.

		apt-cache search jdbc
				libmysql-java - Java database (JDBC) driver for MySQL

		apt-get install libmysql-java
	

From OpenOffice.org : Help : JDBC Connection

You can use a JDBC driver class to connect to a JDBC database from OpenOffice.org. The driver class is provided by the database manufacturer. Two examples of JDBC databases are Oracle and MySQL.
Before you can use a JDBC driver, you need to add its class path. Choose Tools - Options - OpenOffice.org - Java, and click the Class Path button. After you add the path information, restart OpenOffice.org.

The file you need to point to is /usr/share/java/mysql-connector-java.jar. Additional help can be found at the MySQL Developers Web site or the OpenOffice.org Wiki. When you've set it up correctly, you can hit the "test ClassPath" button; it should return "jdbc driver loaded succesfully".

creating an OOo database connecting to an existing database

Start OpenOffice Base. This will open the New Database wizard. Choose "Connect to an existing database" and select the type of database : MySQL, and indicate how you want to connect to this database. Choose JDBC, as we set up JDBC earlier.
Database Wizzard select connection type

Next, you'll be asked details about the database you want to connect to : the database name (eg dbAppTest from our test setup), server URL - actually the server hostname or IP address, port (default for MySQL is 3306, use that unless you've set a different value in the MySQL configuration file), and the MySQL JDBC Driver Class : com.mysql.jdbc.Driver (default, don't change unless you know you've set up a different JDBC).

Next, you can set up user authentication for access to the mysql database. Use an account that you created for this purpose (see mysql setup and configuration quick start guide), with sufficient privileges. You most likely set up the account with a password, so tick the check box "password required". This will make sure you are given the opportunity to provide the password when OOo Base accesses the mysql database.

The connection settings you're creating here can be edited later, from the menu in Base: Edit : Database : Connection Type. This may be convenient to correct errors or for some minor troubleshooting.

Basically, you're done now. If all went well, the OpenOffice Base database will now show you the tables in the mysql database you connected to. Connection Ready
You can start using those tables to create queries en view that you might use for form letters, or use data from the tables directly. Connection Ready Connection Ready
You can create forms as a user interface for data look-up or data input into the MySQL database (if the user account has INSERT rights, obviously). If need be, you can create additional tables in base and join them with the mysql tables. You can create queries or views that match you end user's business needs so it becomes a breeze for them to create their own reports. Or you can let power users define their own views on the mySQL data, in Base. In fact, you can do anything Base has to offer, all while using data from the MySQL database. That includes generating reports, which will be outputted as documents that can be further edited with OpenOffice Writer.

Pitt falls, Caveats and Troubleshooting

If things don't work right-away, check which Java your OpenOffice is using. You need to select "Sun Java JRE" in "Options"; the FSF Java doesn't work here.

A common problem for (undefined) connection errors is a character set mismatch between the mysql database and OpenOffice. If you're connecting to an existing database, you can get information about the rdbms or database charset settings from the dba, and adjust the open office jdbc settings accordingly. Mysql by default uses "Latin1", which matches with ISO-8859-1 in OpenOffice. You can set this under Edit : Database : Connection Type, in the properties.

If you're the MySQL dba, you can set a char set on databases or even individual tables, so you can make them match OOo's defaults (UTF-8), if you want.

Additional information : in the Open Office wiki : Connect MySQL and Base

reporting

There are several ways you can retrieve data and use them in reports :

When done creating the report, choose "Print".This triggers the merge (with a dialog for further selections and other finetuning). There's an option to print to a file, i.e. create the document with fields replaced by their values. You can just save this document if you don't really need a printed version.


MySQL (Linux) with OpenOffice.org (Windows, ODBC)

Since OpenOffice runs equally well on Windows as on Linux, you can use Open Office on a Windows workstation as an interface to a MySQL database on Linux.

The concept and setup is roughly identical to the Linux configuration described before, except that you'll be using ODBC.

setting up ODBC

You can download and install a Windows ODBC driver for MySQL from the MySQL website. To set it up, unpack the zip archive to a suitable folder and run the install.bat. This copies the binary files to the Windows system directory and creates the required registry keys. Then, open ODBC in Control Panel and create a datasource. A system DSN is usually a good idea. Click "Add" to create a new DSN and for driver, select the MySQL Connector.

You're presented a screen where you can give the DSN a name, that you'll need later in OpenOffice to 'link' to the MySQL database tables, and the values required for the database connection : server (hostname or IP address), port, account name and password, database to connect to. Hit the 'details' button to see additional configurable options, they may come in handy some day.

Then, start OpenOffice.org Base, and select New Database : connect to an existing database. For Connection type, choose ODBC, and select the Data Source Name you created earlier. A few clicks later, you're looking at the Mysql database tables from within Base, and you can continue as discussed before.

Note that in the "connect to an existing database" dialog, there's also an item mysql. This requires the presence of a suitable JDBC driver on the system. You can download the MySQL Connector/J for Windows and install it. From there one, the configuration is identical to the Linux JDBC configuration.

See also the Open Office wiki : Connect MySQL and Base


Oracle (Linux) with OpenOffice.org(Windows)

There are several ways to set up a connection to an Oracle database. They all require some sort of client-side software. The traditional way is to install the "Oracle Client Tools" that provide an ODBC driver and some configuration specific to the database system that you'll connect to (TNS names, ...). Even with the Microsoft ODBC driver, which for some applications works better that Oracle's ODBC driver, you'll need the Oracle Client to provide required information to the ODBC driver.
Once you have that figured out, You can create an ODBC DSN for the Oracle database and connect Base to it, as discussed before.

Apart from ODBC, you can also use JDBC. That's something I haven't tried

As with MySQL, you can also use JDBC to connect to An Oracle Database, provided you install the appropriate Driver Class first. Database Wizzard select database


Microsoft SQL Server with OpenOffice.org (Windows)

This evolves around a straightforward ODBC setup, as discussed earlier for MySQL. There is a Microsoft SQL Server ODBC driver present by default on the windows system, so you can just go and configure a DSN, and use that to create a BASE database that connects to a Microsoft SQL Server database.

Pretty much the same goes for any database that you can find a suitable ODBC driver for.


Databases en database frontends (interactive SQL, report tools, ...)


Koen Noens
December 2008