Jumbabox

So I started playing around with OpenOffice today and I wanted to see how easy it was to build a Pivot Table…er a DataPilot using an Access..er a Base database as the data source.

Step 1 - Create an OpenOffice Base Database

Lets first create a new Base database, open up OpenOffice Base.

The first dialog box is the Database Wizard, make sure you click on the Create a new database radio button and click on the Next >> button.

The next step in the Database Wizard will ask you if you want to:

• Register your database : Register your databases settings and data for future use (shown in this example)
• Open your database for editing : Open the database
• Create tables using the table wizard : Start the wizard to create a new table

I only want to register the database and open the database for editing as you can see from the options in the screen shot below. After selecting your options, click Finish.

You will then be asked to save the database, type in an appropriate and name and click Save.

Step 2 - Import Data from Calc to Base

I’m going to import data from an OpenOffice Calc sheet in this example. Firstly go to the OpenOffice Calc sheet which contains the data and copy the data set by right clicking and selecting Copy.

Switch back to our OpenOffice Base database, before continuing make sure you have clicked on the Tables option and the you can see the Tables area.

Right click in the Tables area and click the Paste option.

This will open up a Copy Table wizard, there is a set of options available:

• Table Name : The name of the new table
• Options
  • Definition and Data : Copies the structure and format of the data set
  • Definition : Copies the format of the data set only
  • As table view : If you are copying a SQL statement over you can use this statement as a view
  • Append Data : To append the data set which you have copied to another Table in the Base database
• Create primary key : To create a Primary Key for your Base database

I decided to select the options seen below, after setting up these options click the Next> button.

The next part of the Copy Table wizard will ask which existing columns you would like to have in your new table (within the database), clicking the >> button automatically moves over all fields. Click Next> once you have decided which columns you want to copy over.

The final step of the Copy Table wizard will ask the format/data type of each field we are importing, the only format I had to change was the Revenue and Loss fields which needed to be changed to Number. Once this has been correctly setup click the Create button.

Make sure you check all numeric fields are set to the Number type as you may encounter issues when aggregating (summing) these fields in the DataPilot.

Now you should be able to see the new table, SampleTbl in the Table area.

Step 3 - Create a Base Connection

We will now need to create a connection to the Base table within Calc, open up a blank Calc file and click on Tools > Options.

Expand the OpenOffice.org Base area and click on the Databases option. You can now see that in the Registered databases area you will see the Base database we just setup.

A handy feature of the OpenOffice Base is to register your database so that it is automatically set up to be used by other OpenOffice applications. So there is no need to create a new connection!

Step 4 - Setting up the DataPilot

In the same Calc file, click on Data > DataPilot > Start… option.

This will open up a Select Source dialog box, as we are not building this DataPilot on a sheet within Calc we will need to select the Data source registered in OpenOffice.org option and click the OK button.

A new dialog box will pop up asking you which Database you want to select, a default database is already registered called Bibliography, drop down the box and select your database, in this example it is called jumbabox.

After this drop down box, we need to select a Data source, click on the drop down box and select a table you want to use as the data source, in this example it is SampleTbl.

The last step is to select the Type which includes 4 different options:

• Sheet : A table in the Base database
• Query : A query in the Base database
• Sql : Sql statement
• Sql (Native) : Sql statement

As we are only building the DataPilot on a Table, I have selected Sheet, after this click the OK button.

Selecting a Table

Selecting a Query

If you were to choose Query you will need to select from the Data Source drop down box the name of the query you have created in the Base database. As you can see I created a Query named SampleQry.

Selecting by using SQL

If you want to use SQL, you can type in the direct sql statement in the Data source field.

The next dialog box will ask you to setup your DataPilot by using the Drag and Drop GUI.

After dragging and dropping my fields my DataPilot looked like the screenshot below:

Clicking on the More button will display some extra options, I left these to the defaults but you can always change these after you have created your DataPilot (by right clicking on the DataPilot and clicking the Start… option), click OK to finish setting up your DataPilot.

Here is a brief run over of what each option does:

• Ignore empty rows : If enabled the DataPilot will not show any rows which are empty or have no values
• Total columns : Show the Totals for the Columns
• Add filter : Makes the filter dialog box active
• Identify categories : Automatically assigns rows without labels to the next highest category as specified by a row label (from OpenOffice help)
• Total rows : Show the Totals for the Rows
• Enable drill to details : Enables the drill down feature of the DataPilot

\

Done! Your DataPilot has finally been created.

Step 4 - Refresh your DataPilot

If you need to refresh your DataPilot (as it is based on a Base database) all you need to do is right click in the DataPilot area and click on Refresh.

If you have questions or problems please comment.

Enjoy!