Tuesday, February 14, 2012

Short Demo on YouTube

Short demo of how to use Pentaho Reporting for making reports on Sql-Ledger.

Tuesday, February 7, 2012

Installing The Pentaho Bi-server

When you have created several reports, it is time to share them with your team. For that we have to install the Pentaho Bi-server. Download Pentaho bi-server , unzip and place the postgres driver postgresql-9.1-901.jdbc3.jar in /biserver-ce/tomcat/lib. 
To tune the bi-server to work also with iPhone, read this and follow the instructions. 
To tune the bi-server, not to give the evaluation login link, see picture below, read this, and follow the instructions

Change biserver-ce/pentaho-solutions/system/publisher_config.xml and add pentaho as publisher password (or what you prefer..)

To start the bi-server, on the Mac, first make the *.sh files in the directory biserver-ce-3 and tomcat/bin executable by doing a chmod +x to the files. Goto directory biserver-ce-3 and

sudo chmod +x *.sh

Same as in the directory biserver-ce-3/tomcat/bin Goto this directory and again

sudo chmod +x *.sh

And finally to start it, goto the directory biserver-ce-3:

sudo ./start-pentaho.sh 

To start the administration console go to the directory, administration-console and 

sudo chmod + x *.sh

then start it by 

sudo ./start-pac.sh

The bi-server is now setup for localhost on your Mac. You can find them now on localhost:8080 and the admin console on localhost:8099.

First go to localhost:8099 and login using credentials: admin password and make a user with admin rights.

If you want run it on your server, read this in Dutch (or this in a language you can read, (google translate) But first, keep on reading this blog, below, what are (better) setting....Below is the setup for Debian.

#Pentaho has it's own tomcat installation, but no Java Environment. 
#You have to install this separately. We used Sun version 1.6. 


aptitude install sun-java6-jdk sun-java6-jre
aptitude install sun-java6-plugin sun-java6-bin
aptitude install libcommons-beanutils-java libcommons-collections-java libcommons-collections3-java libcommons-daemon-java
aptitude install libcommons-io-java libcommons-launcher-java libcommons-logging-java libcommons-modeler-java
aptitude install libcommons-pool-java libcommons-validator-java

# Perl also needs a specific CPAN module(s):

perl -MCPAN -e "install Text::Iconv"

# On the postgresql server, add a line in the file /etc/postgresql/*/main/pg_hba.conf 
# to give access to the server where pentaho is running so that pentaho has access to the

# Postgres database it has to use.

host    <pg_username>            <pg_database>         <IP-pentaho-server>/32    md5

# In the weblink they install the software, in the home dir of root. 
# This is a NO! NO!
# Choose a subdir under /var/www/ (standard Apache dir) or a subdir under /home/. 
# In this example /home/pentaho/.

# A comment for the user where pentaho will be running under. 
# In the weblink they use root, but this is also a NO! NO!
# Create a separate user for pentaho:

useradd -u 10000 -d /home/pentaho -m pentaho

# Install pentaho now according to the weblink. 

chown -R pentaho.pentaho /home/pentaho

# Finally, the startup we do, is also different from the weblink:

vi /etc/rc.local
 su - pentaho -c "/home/pentaho/startup.sh start" >/dev/null 2>&1

vi /home/pentaho/startup.sh
# Only run as user pentaho

[ $(id -u) -ne 10000 ] && exit 1

# Set the paths according to your distro.

export JAVA_HOME="/usr/lib/jvm/java-6-sun"
export JRE_HOME="/usr/lib/jvm/java-6-sun/jre"
export PATH=$PATH:$JAVA_HOME/bin

case $1 in
               /home/pentaho/biserver-ce/start-pentaho.sh &
               cd /home/pentaho/administration-console
               /home/pentaho/administration-console/start-pac.sh &
               /home/pentaho/biserver-ce/stop-pentaho.sh &
               cd /home/pentaho/administration-console
               /home/pentaho/administration-console/stop-pac.sh &
               $0 stop
               $0 start
               echo "usage: $0 <start|stop|restart>"

Tuesday, January 31, 2012

Tutorial Part III

Most difficult thing, for making the reports in Pentaho Designer, is making the queries to the database.
The good thing is that every user of Sql-ledger has the same database layout, so a good report can be used by everyone! 

To make life a bit easier, here is a trick, so that you can see what the queries are when you use Sql-ledger.
See this blog how to log all the queries from postgres.
Be aware that Sql-ledger is a web application, so it has to build the screens in parts. In the log you will see several queries fired to build one screen.
If you scout your postgres.log you will find the queries

Example Q4 2011 query from sql-ledger to see what we bought:
SELECT a.id, a.invnumber, a.ordnumber, a.transdate,
                a.duedate, (SELECT sum(ac.amount)
 FROM acc_trans ac
 JOIN chart c ON (c.id = ac.chart_id)
 WHERE ac.trans_id = a.id
 AND ac.approved = '1'
 AND c.link LIKE '%_tax%'
              AND ac.transdate <= '31-12-2011') * -1 AS tax,
a.amount, (a.paid) AS paid,
a.invoice, a.datepaid, a.terms, a.notes,
a.shipvia, a.waybill, a.shippingpoint,
e.name AS employee, vc.name, vc.vendornumber,
a.vendor_id, a.till, m.name AS manager, a.curr,
a.exchangerate, d.description AS department,
a.ponumber, a.warehouse_id, w.description AS warehouse,
a.description, a.dcn, pm.description AS paymentmethod,
a.datepaid - a.duedate AS paymentdiff,
ad.address1, ad.address2, ad.city, ad.zipcode, ad.country
        FROM ap a
     JOIN vendor vc ON (a.vendor_id = vc.id)
     JOIN address ad ON (ad.trans_id = vc.id)
     LEFT JOIN employee e ON (a.employee_id = e.id)
     LEFT JOIN employee m ON (e.managerid = m.id)
     LEFT JOIN department d ON (a.department_id = d.id)
     LEFT JOIN warehouse w ON (a.warehouse_id = w.id)
     LEFT JOIN paymentmethod pm ON (pm.id = a.paymentmethod_id)
            WHERE a.approved = '1' AND a.transdate >= '01-10-2011' AND a.transdate <= '31-12-2011'
            ORDER by 4 ASC,2,17

Monday, January 30, 2012

Tutorial Part II

After installing Pentaho Report Designer and creating a sample database we will make our first report.
Fire up the Report Designer and close the welcome screen.

Then choose File - Report Wizard. Select a nice theme and click next.

Now we have to define the database connection. Select the Sample Query and click on the pencil icon.

Of the left pane, again click the pencil icon.

Define the connection as shown here below. The password for postgres is the admin password of your mac.

After your filled in and selected the connection type, PostgreSQL, test the connection by pressing test.

You will get a dialog stating that the connection was successful. Press OK to close the dialog (if the test was ok...) Now we can create a query. 

On the right pane, Available Queries, press the plus sign. 
It makes a Query 1, you can rename it to a more smarter name.

In the Query Dialog (with the yellow line) insert the Postgres Query below. (copy paste) This query shows the trial balance for Q1 2011. After you have pasted it, click preview to see the query result.

SELECT accno, 
 (SELECT SUM(amount) FROM acc_trans ac 
 WHERE ac.chart_id = chart.id 
 AND TO_CHAR(transdate, 'YY-MM') = '11-01') AS jan,
 (SELECT SUM(amount) FROM acc_trans ac 
 WHERE ac.chart_id = chart.id 
 AND TO_CHAR(transdate, 'YY-MM') = '11-02') AS feb,
 (SELECT SUM(amount) FROM acc_trans ac 
 WHERE ac.chart_id = chart.id 
 AND TO_CHAR(transdate, 'YY-MM') = '11-03') AS mar
FROM chart
WHERE charttype = 'A'
ORDER BY accno

Click Ok and select the Query 1 to be used.

In the next screen put the available items in the box below and sort them accordingly

Press Next and set width accno 7%, description 30% and change the data format for jan, feb and mar to #,###;(#,###) 

Your are done! Finish will give the trial report, you can always press preview to see the result in the wizard.

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!