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

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, 

 description,

 (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!