Connecting onCourse to OpenOffice

Using OpenOffice 2.2 with onCourse to create a mail merge

There are many reasons why you might want to access data in onCourse through a tool such as OpenOffice. You might need to generate a custom report and its important to have full access to your data for that reason. Here, we’ll show you one of the simpler features in OpenOffice, but it can be quite an important thing to be able to do – create a mail merge to all your students in onCourse.

Before you begin, you will need to have OpenOffice installed on your machine which can be obtained from the OpenOffice website.

Setting OpenOffice with the right JDBC to talk to onCourse

• Download our onCourse OpenOffice package which contains two files to help you get started with setting up OpenOffice to talk with onCourse. The first file is “derbyclient.jar” and is needed by OpenOffice to actually talk to the onCourse Server. The second file is “oncourse.db” and is an OpenOffice Base file which will require mininal modification before it can be used to pull data out of onCourse.
• From the downloaded archive extract the file named “derbyclient.jar” and place it somewhere on the hard disk. Remember where you put it.
• Run OpenOffice (Any of the applications should do – ie Writer or Calc). Open “Tools” -> “Options” . Go to “OpenOffice.org” -> “Java”. Click on “Class Path” button. Click on “Add archive”. Find and select the derbyclient.jar file and press “Open”.
  Add “Derbyclient.jar” to OpenOffice
  Press OK in Class Path window. Press OK in options window. OpenOffice will ask you to restart before changes will take effect, and you will be ready to progress with using “oncourse.odb” to generate reports and perform mail merges in the following steps.

If it prompts for a username and password, use “report” and “948ty”

OpenOffice MailMerge

First steps after downloading oncourse.odb

• The first thing to do is to make sure that the oncourse.odb file knows to where to find your onCourse Server on the network. Open oncourse.odb and click on the “Edit” -> “Database” -> “Properties” and change “localhost” part of the “Datasource URL” to the IP of the machine that is running your onCourse Server.
  Change Datasource URL
• Verify that it connects successfully by hitting the “Test Connection” button. It should prompt you to enter a password.
• Save the settings by going to “File” -> “Save”
• Next we move on to registering the “oncourse.odb” as database for OpenOffice. This step is necessary for OpenOffice to recognise that it exists as source for other types of documents (spreadsheets, labels, letters etc). Go to “Tools” -> “Options”. Choose “OpenOffice.org Base” -> “Databases” on the left. Click “New”. Add the database file (should have extension odb) by clicking on browse, locating the “oncourse.odb” then clicking on “Open”.
  Resgister database
• Click “OK” and “OK” again to finish.

Performing the Mail Merge Using the Wizard

• Start OpenOffice Writer.
• Create a letter – easiest way is to use the “File”-> “Wizards” ->“Letter” option. For our sample one, it’s fairly simple to just hit “Finish” on this wizard.
  Choose Letter Wizard
• Go to “Tools” -> “Mail Merge Wizard” to create a mail merge where the data is pulled from the database.
  Use the Mail Merge Wizard
• For our example, we’ll use our current document, so for Step 1, just click on the “Next” button.
• Step 2 – The “Letter” option is fine. Click on the “Next” button.
• Step 3 – “Insert address block” is the heart of the mail merge operation. To do this click on the “Select Address List…” button. Click on “oncourse” and choose the table called “ONCOURSE.STUDENT”.
• Click “OK” and “OK” again.
  Select the Student table
• Add the fields you want to appear in the address block. You can customize what appears in the address block by clicking on the “More…” button.
• Next click the “Match Fields” button to match the fields in the address block to fields in the database. IE. You will want to match <First Name> to “FIRSTNAME”, <Last Name> to “SURNAME”, <Gender> to ISMALE at the least. Note the wizard will not let you click “Next” until you’ve matched all fields that will appear in the address block.
  Match Fields
• Step 4 – Adding a Salutation – to determine an addressee is a female, the “Field name” should be set to “ISMALE” and the “Field value” should be set to “0” (zero)
  Selecting Gender
• After this step most of the options should be self-explanatory and at the end of the wizard you will be asked if you wise to save, send or print the mail merge.
  Saving your mail merge

Extra Documentation

There is plenty of documentation for OpenOffice available to get further information on particular features.

A tutorial on how to do mail merges with a database for OpenOffice.