Monday, January 30, 2012

Tutorial Part 1

For all of you who are working with sql-ledger, this post is about how to integrate Pentaho reporting with sql-ledger.
I have been working with ledger for several years now and I was looking for a reporting tool, so that I can make live reports on the ledger system for management. There are several ways to Rome for reporting and I just bought a book about Pentaho, so that is why.

I assume that you have a little knowledge about sql-ledger and you have some knowledge about databases and setting up software and a not scared in using terminal prompt.

Because I have a Mac, the whole tutorial is who to make it work on a Mac.

Just to see who it works let's start with downloading the report tool.

  • Download Pentaho Report Designer, I use 3.8.2 GA for mac, prd-ce-mac-3.8.2-stable.tar.gz
  • When downloaded drag the whole thing to the applications folder.
  • For Pentaho to work with Sql-Ledger, we have to add a jdbc driver, postgres.
  • Download the driver from postgresql-9.1-901.jdbc3.jar
  • Open Finder and right click the Pentaho application and select show package contents.
  • Drop the driver in the Pentaho Report Designer directory in /jdbc/lib

Before we can do some reporting we have to get a database to play with. Because all mac osx are shipped with Postgres we can do some testing locally on the mac!
Go to your sql-ledger environment and select System -> Backup - > Save to File
When you got the backup file name it to something short, and drop it in /library/postgresql/8.4/bin directory. If you are using OSX Snow leopard it is: /library/postgresql8/bin

Now the terminal part. Open terminal and enter the following:
cd /
cd /library/postgresql/8.4/bin (or where you bin directory is)

Now we are going to make a database in Postgres and restore the backup
sudo ./createdb -U postgres yourdatabasename
./psql -U postgres yourdatabasename < yourbackupname.sql

Whenever you want to remove the database, type:
sudo ./dropdb -U postgres yourdatabasename

OK, now we can make some nice reports. And, yes, they are still local on you machine, but as soon as we made a few reports we will instal the bi-server to share them. Even on your IPhone!

No comments:

Post a Comment