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.