How to create and publish Pentaho reports on a MySQL datasource

This how-to will guide you through your first own made pentaho report. The documentation and the Pentaho-wiki aren’t very useful for the beginning Pentaho developer.

This how-to is developed on Ubuntu 8.10, MySQL 5.0 and Pentaho 1.7.1 GA. Most things will work on windows, offcourse with different commands.

In this guide I use the MySQL Sakila sample database. Installation is simple:

  1. download the tar or zip and extract it on your desktop
  2. Open a console and go to the created directory.
  3. connect to MySQL: mysql -u root -p
  4. import by execute two commands: source sakila-schema.sql , source sakila-data.sql
  5. Give the user ‘pentaho-user’ rights to this database

Now that we have data we have to create the Pentaho data sources. Open the server.xml with an editor:

sudo gedit /etc/tomcat6/server.xml

and add:

<Resource name="jdbc/sakila" auth="Container" type="javax.sql.DataSource" maxActive="20"
maxIdle="5" maxWait="10000" username="pentaho_user" password="password" validationQuery="Select 1"
factory="org.apache.commons.dbcp.BasicDataSourceFactory" driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost/sakila" testOnBorrow="true" testWhileIdle="true" timeBetweenEvictionRunsMillis="10000"
maxEvictableIdleTimeMillis="60000"/>

Before we want to make any reports we have to make sure we can publish reports on the Pentaho BI server. Pentaho keeps this password in the file “publish_config.xml” found in pentaho-solutions/system/. Default this password is blank… but I will make it the same as the Pentaho default:

<publisher-config>
<publisher-password>password</publisher-password>
</publisher-config>

You should know that this file is in the pentaho-solutions directory which is usually made readable for everyone. In a production environment further action must be taken to secure the BI server.

Reportdesign

There are several ways to create Pentaho reports:

  1. Using the design studio (PDS)
  2. Using the report designer (PRD)
  3. Using the report design wizard
  4. Using the online AdHoc Reporting tool (WAQR)

Number 3 and 4 are maybe useful for end users, but have limited capabilities to create reports as an alternative against the commercial tools. The report design wizard can be executed from the design studio and report designer. In this example I will use the report design wizard only because it’s the quickest way to create a good looking example:

  1. Start the report designer
  2. Start Report Design Wizard under file menu or press CTRL-Shift-n
  3. The report design wizard opens with step 1. Choose a title and description (”films”, “list of films”), press next.
  4. choose connection type JNDI an click Add, make it something like this (fill in your specific  username/password and test it):
    New JNDI connection
  5. Specify Query String or use the query designer:
    SELECT title, category, price , length FROM film_list
  6. Make sure you select the created connection an click Next
  7. Add the available items to the Selected Items (default) and click Next
  8. Click Next again
  9. click OK

We now have a simple report design with a list and a total. You can preview the report by clicking the preview button or click one of the two play icons or press CTRL-r.

Save the report.

Publish it

Now we can publish it to the Pentaho BI server by pressing CTRL-shift-p. Fill in the right credential (defaults are: publish= password; username= joe; pw= password). Click OK.

Fill report name, description and choose a location ( pentaho-solution/samples ) an click publish… Ready!

Next?

This was just a very short howto and this example isn’t very useful, but it will guide you in the right direction. Next things todo can be: design (dynamic) parameter driver reports, create your own solution repository, dashboard design…

Comments (2)

publisher-killerNovember 18th, 2008 at 15:46

The publisher-password thing was killing me. thanks!

keep’em comming!

joeJuly 20th, 2009 at 14:29

it is not clear please make it clear by giving examples

Leave a comment

Your comment