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