Pentaho 5.0 Reporting by Example: Beginner’s Guide

Table of Contents

Pentaho 5.0 Reporting by Example Beginner’s Guide

Credits

About the Authors

About the Reviewers

www.PacktPub.com

Support files, eBooks, discount offers and more

Why Subscribe?

Free Access for Packt account holders

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Time for action – heading

What just happened?

Pop quiz – heading

Have a go hero – heading

Reader feedback

Customer support

Errata

Piracy

Questions

1. What is Pentaho Report Designer?

Types of reports

Main features of Pentaho Report Designer

History

Examples of typical reports

The buyer report

The income statement

The inventory list

Invoice

Product Sales

Top N Customers

HTML actions

Summary

2. Installation and Configuration

Configuring the environment

System requirements

Time for action – installing JDK and configuring the environment variables

What just happened?

Time for action – downloading, installing, and configuring PRD

What just happened?

Learning the Sakila database

Time for action – downloading and installing Sakila DB

What just happened?

The Sakila database schema

Customer Data

Inventory

Business

Have a go hero

Pop quiz – system requirements, JDBC driver, Sakila DB, and RAM

Summary

3. Start PRD and the User Interface (UI) Layout

Starting PRD

Time for action – starting PRD and creating a new report

What just happened?

PRD layout

The Work area

Insertable objects

Report tree structure

Style and Attributes

The Data panel

The menu bar

Shortcuts

The tab section

Format tools

Message section and memory indicator

Have a go hero

Pop Quiz – layout of PRD

Summary

4. Instant Gratification – Creating Your First Report with PRD

Creating your first report with PRD

Time for action – creating a new report and creating a table based on data sets

What just happened?

Time for action – configuring the Report Header and Details Header sections

What just happened?

Time for action – configuring details

What just happened?

The second half of the report

Time for action – adding functions

What just happened?

Time for action – configuring the Details Footer section

What just happened?

Time for action – adding more details

What just happened?

Output types

Time for action – exporting in different formats

What just happened?

Have a go hero

Pop Quiz – data sets, functions, and objects

Summary

5. Adding a Relational Data Source

Learning about JDBC driver

Time for action – configuring drivers

What just happened?

Creating a new data set

Time for action – creating a new data set

What just happened?

Time for action – modifying the header and detail

What just happened?

Time for action – modifying the report's footer

What just happened?

Aggregation functions

Time for action – using functions to configure styles

What just happened?

Encoding charset

Have a go hero

Pop quiz – JDBC, Functions, and Encoding Charset

Summary

6. Adding Groups

Starting practice

Time for action – modifying the SQL query

What just happened?

Time for action – configuring the layout

What just happened?

Configuring the Group section

Time for action – adding groups

What just happened?

Modifying functions

Time for action – modifying functions and page breaks

What just happened?

Learning more about groups

Have a go hero

Pop Quiz – positioning hierarchy and groups

Summary

7. Adding Parameters

Starting practice

Time for action – creating a new report

What just happened?

Parameters

Time for action – adding parameters

What just happened?

Time for action – creating nested parameters

What just happened?

Have a go hero

Pop quiz – format and parameters

Summary

8. Using Formulas in Our Reports

Starting practice

Time for action – making a copy of the previous report

What just happened?

Formulas

Creating formulas

The formula editor

Time for action – creating a new formula

What just happened?

Time for action – styles with formulas

What just happened?

Have a go hero

Pop quiz – formulas

Summary

9. Adding Charts

Starting practice

Time for action – creating a new report

What just happened?

Charts

Creating and configuring charts

Types of charts

The bar chart

The line chart

The area chart

The pie chart

The multipie chart

The barline chart

The ring chart

The bubble chart

The scatter-plot chart

The XY bar chart

The XY line chart

The XY area chart

The extended XY line chart

The waterfall chart

The radar chart

The XY area line chart

Restarting practice

Time for action – creating a pie chart

What just happened?

Time for action – creating a bar chart

What just happened?

The relationship between charts and report sections

Have a go hero

Pop quiz – charts

Summary

10. Adding Subreports

Starting practice

Time for action – creating a new report

What just happened?

Subreports

Subreports in PRD

The relation between Reports and Subreports

The relation between sections of the Report and Subreports

Creating and configuring Subreports

Opening and closing Subreports

Restarting practice

Time for action – creating our first Subreport

What just happened?

Time for action – configuring our first Subreport

What just happened?

Time for action – creating and configuring the second Subreport

What just happened?

Have a go hero

Pop quiz – Subreports

Summary

11. Publishing and Running Reports in Pentaho BA Server

Learning Pentaho

Defining Pentaho

Pentaho components

Principal Pentaho projects

A brief historical review

Principal characteristics of Pentaho

Downloading Pentaho BA Server

System requirements

Time for action – installing and running Pentaho BA Server

What just happened?

General layout of Pentaho User Console

The Home perspective

The Browse Files perspective

The Opened perspective

The Schedules perspective

The Administration perspective

Creating a new solutions folder

Time for action – publishing reports in Pentaho BA Server

What just happened?

Executing reports in Pentaho BA Server

Creating a schedule

Uploading reports

Have a go hero

Pop quiz – Pentaho

Summary

12. Making a Difference – Reports with Hyperlinks and Sparklines

Starting practice

Time for action – configuring the layout

What just happened?

Learning about hyperlinks

Types of hyperlinks in PRD

Interaction between PRD and Pentaho BA Server

Restarting practice

Time for action – creating our first hyperlink

What just happened?

Time for action – hyperlinks in charts

What just happened?

Learning about sparklines

Restarting practice

Time for action – creating our first sparkline

What just happened?

Have a go hero

Pop quiz: Hyperlinks and Sparklines

Summary

13. Environment Variables, Stylesheets, and Crosstabs

Learning about environment variables

Environment variables in PRD

Using the environment variables

On practice

Learning CSS

Stylesheets in PRD

The Style Definition Editor

Applying CSS

Starting practice

Time for action – configuring the layout

What just happened?

Time for action – creating and applying our stylesheet

What just happened?

Trying out stylesheets

Learning about crosstabs

The crosstabs in PRD

Using crosstabs

Crosstabs editor

Time for action – configuring the layout

What just happened?

Have a go hero

Pop Quiz – environment variables, stylesheets, and crosstabs

Summary

14. PRD Reports Embedded in Web Applications

Tools and technologies we will be using

Learning JEE

Learning Apache Tomcat

Time for action – installing and starting Apache Tomcat

What just happened?

Learning Eclipse and WTP

Time for action – installing and initiating Eclipse WTP

What just happened?

Time for action – creating a new Dynamic Web project

What just happened?

Configuring Eclipse Perspectives

Infrastructure configurations

Time for action – creating a Tomcat instance

What just happened?

Time for action – setting a JNDI/JDBC connection pool

What just happened?

Time for action – configuration of libraries and the PRD report

What just happened?

Code components

Time for action – creating a context Listener

What just happened?

Time for action – creating a web client

What just happened?

Time for action – creating a standalone application

What just happened?

Have a go hero

Pop quiz – graphics

Summary

A. Sakila DB Data Dictionary

The customer table

The address table

The city table

The country table

Inventory

The film table

The category table

The film_category table

The language table

The actor table

The film_actor table

The inventory table

The film_text table

Business

The staff table

The store table

The rental table

The payment table

B. Pop Quiz Answers

Chapter 2, Installation and Configuration

Pop quiz – system requirements, JDBC driver, Sakila DB, and RAM

Chapter 3, Start PRD and the User Interface (UI) Layout

Pop quiz – layout of PRD

Chapter 4, Instant Gratification – creating your first report with PRD

Pop quiz – data sets, functions, and objects

Chapter 5, Adding a Relational Data Source

Pop quiz – JDBC, functions y encoding charset

Chapter 6, Adding Groups

Pop quiz – positioning hierarchy and gGroups

Chapter 7, Adding Parameters

Pop quiz – format y Parameters

Chapter 8, Using Formulas in Our Reports

Pop quiz – formulas

Chapter 9, Adding Charts

Pop quiz – charts

Chapter 10, Adding Subreports

Pop quiz – Subreports

Chapter 11, Publish and Run Reports in Pentaho BA Server

Pop quiz – Pentaho

Chapter 12, Making a Difference – Reports with Hyperlinks and Sparklines

Pop quiz – Hyperlinks and sparklines

Chapter 13, Environment, Stylesheets, and Crosstabs

Pop quiz – environment, stylesheets, and crosstabs

Chapter 14, PRD Reports Embedded in Web Applications

Pop quiz – graphics

Index

Pentaho 5.0 Reporting by Example Beginner’s Guide

Pentaho 5.0 Reporting by Example Beginner’s Guide

Copyright © 2013 Packt Publishing

All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.

Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the authors, nor Packt Publishing, and its dealers and distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book.

Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.

First published: August 2013

Production Reference: 1140813

Published by Packt Publishing Ltd.

Livery Place

35 Livery Street

Birmingham B3 2PB, UK.

ISBN 978-1-78216-224-7

www.packtpub.com

Cover Image by Emilce Riot (<[email protected]>)

Credits

Authors

Mariano García Mattío

Dario R. Bernabeu

Reviewers

Dan Keeley

Diethard Steiner

Acquisition Editor

Edward Gordon

Commissioning Editor

Llewellyn Rozario

Lead Technical Editor

Anila Vincent

Technical Editors

Vrinda Nitesh Bhosale

Jalasha D'costa

Mausam Kothari

Dominic Pereira

Sonali S. Vernekar

Copy Editors

Gladson Monteiro

Insiya Morbiwala

Aditya Nair

Adithi Shetty

Laxmi Subramanian

Project Coordinator

Rahul Dixit

Proofreaders

Faye Coulman

Jonathan Todd

Indexer

Rekha Nair

Graphics

Ronak Dhruv

Production Coordinator

Pooja Chiplunkar

Cover Work

Pooja Chiplunkar

About the Authors

Mariano García Mattío is a systems engineer for the IUA and specialist in distributed systems and services for the Facultad de Matemática Astronomía y Física (Faculty of Mathematics Astronomy and Physics) FaMAF UNC. He is an associate professor of: databases 1, databases 2, and advance database systems at the IUA, school of engineering; database engines at the IUA, school of administration; object-oriented programming paradigm, and distributed systems at the IUA's master in embedded systems. He is the teacher in charge of assignments for applied databases at the UCC. Also, Mariano is the co-director of the research project on new information and communication technologies at the UCC and co-director of the research project on networks monitoring and communication systems at the IUA. He is also a member of the Virtual Laboratories research project at the IUA and co-founder of eGluBI. He is the coordinator of the social network Open BI Network. He specializes in Java SE and Java EE technologies, node.js, administration and design of databases, and OSBI. His blog site is http://jmagm.blogspot.com/.

Dario R. Bernabeu is a systems engineer at the Instituto Universitario Aeronáutico (University Aeronautic Institute) IUA. He is the co-founder of eGluBI (www.eglubi.com.ar). He specializes in development and implementation of OSBI solutions (Open Source Business Intelligence), project management, analysis of requirements/needs, deployment and configuration of BI solutions, design of data integration processes, data warehouse modelling, design of multidimensional cubes and business models, development of ad hoc reports, advanced reports, interactive analysis, dashboards, and so on. A teacher, researcher, geek, and open source software enthusiast, his most notable publication is Data Warehousing: Research and Concept Systematization – HEFESTO: Methodology for the Construction of a DW. Being the coordinator of the social network Open BI Network (www.redopenbi.com), he makes many contributions to various forums, wikis, blogs, and so on. You can find his blog site at http://tgx-hefesto.blogspot.com/.

About the Reviewers

Dan Keeley is a computer science graduate who started his career in open source BI tools at the very beginning. He has full experience of the entire Pentaho stack and many successful implementations under his belt. He is a well-known Pentaho community member and founder of the Pentaho London Usergroup.

Diethard Steiner, currently working as an independent senior consultant in London, UK, has been specialized in the field of open source business intelligence solutions for many years. He has been very passionate about his work, regularly publishing tutorials on his blog, Diethard Steiner on Business Intelligence (http://diethardsteiner.blogspot.co.uk/), which over the years has gained a loyal following. He has been implementing end-to-end solutions (from data integration to reporting and dashboards) for several clients and projects and has gained a deep understanding of the requirements and challenges of such solutions.

www.PacktPub.com

Support files, eBooks, discount offers and more

You might want to visit www.PacktPub.com for support files and downloads related to your book.

Did you know that Packt offers eBook versions of every book published, with PDF and ePub files available? You can upgrade to the eBook version at www.PacktPub.com and as a print book customer, you are entitled to a discount on the eBook copy. Get in touch with us at for more details.

At www.PacktPub.com, you can also read a collection of free technical articles, sign up for a range of free newsletters and receive exclusive discounts and offers on Packt books and eBooks.

http://PacktLib.PacktPub.com

Do you need instant solutions to your IT questions? PacktLib is Packt's online digital book library. Here, you can access, read and search across Packt's entire library of books.

Why Subscribe?

Fully searchable across every book published by Packt

Copy and paste, print and bookmark content

On demand and accessible via web browser

Free Access for Packt account holders

If you have an account with Packt at www.PacktPub.com, you can use this to access PacktLib today and view nine entirely free books. Simply use your login credentials for immediate access.

Preface

Pentaho 5.0 Reporting By Example Beginner’s Guide is a practical book that clearly presents the concepts that form the foundation of what is to be learned and then puts those concepts into practice through step-by-step visual guides. This book, in addition to giving the what and the how, specifies the why. It also encourages the reader to create his or her own reports. You will feel confident about creating your own professional reports with graphics, formulas, subreports, and so on.

What this book covers

Chapter 1, What is Pentaho Report Designer?, provides a quick overview of Pentaho Report Designer (PRD), its engine, its common uses, its main features, and its evolution.

Chapter 2, Installation and Configuration, explains how to download, install, and configure PRD 5.0. It also describes how to configure system requirements in PRD and explains database use in the book.

Chapter 3, Start PRD and the User Interface (UI) Layout, explains how to start PRD in different operating systems, explains its layout, and explains each area of its user interface.

Chapter 4, Instant Gratification – Creating Your First Report with PRD, explains how to create your first report, how to define its data sets, how to configure the report’s sections, how to add and set insertable objects and functions, and how to preview and export your report.

Chapter 5, Adding a Relational Data Source, explains how to download the JDBC driver of MySQL, how to create JDBC-type data sets, aggregation functions, and also how to modify the charset.

Chapter 6, Adding Groups, explains how to generate groups in the report and how to configure the behavior of the sections.

Chapter 7, Adding Parameters, explains how to create simple parameters and nested parameters, how to make a Parameter obtain its values through a data set, and how to dynamically construct SQL queries according to the values of Parameters.

Chapter 8, Using Formulas in Our Reports, explains how to create and use formulas.

Chapter 9, Adding Charts, explains a chart’s functions, the good and bad practices of using charts, each type of chart, and how to create and configure your own charts.

Chapter 10, Adding Subreports, explains how to add and configure subreports and how to set its internal Parameters.

Chapter 11, Publishing and Running Reports in Pentaho BA Server, explains about Pentaho BA Server and how to publish, display, and work with your reports in the Pentaho User Console.

Chapter 12, Making a Difference – Reports with Hyperlinks and Sparklines, explains how to create, configure, and use Hyperlinks and Sparklines in your reports.

Chapter 13, Environment, Stylesheets, and Crosstabs, explains how to use, add, and configure environment variables, stylesheets, and crosstabs.

Chapter 14, PRD Reports Embedded in Web Applications, explains how to embed and run PRD reports in your web applications.

Appendix A, Sakila DB Data Dictionary, explains each table's data dictionary, as well as a small sample of the data.

What you need for this book

In order to use this book, you need a computer that is less than four years old with at least 1 GB of RAM memory and a good Internet connection.

When we started writing this book, stable versions of Pentaho Report Designer 5 and Pentaho BA Server 5 were not available. If the stable versions are still not available, when you have this book, you can download TRUNK versions from the following links:

Pentaho Report Designer TRUNK (http://ci.pentaho.org/view/Reporting/job/git-report-designer/)

Pentaho BA Server TRUNK (http://ci.pentaho.com/view/Platform/job/BISERVER-CE/)

Who this book is for

This book is ideal for a wide variety of profiles, irrespective of whether you have recently taken your first steps or are experienced in the world of Business Intelligence; whether you need to make professional reports in your organization or business or you are a developer and want to improve reporting in your applications. This book assumes that you understand the basic notions of databases and the SQL language, and that you have a computer with a Windows or Linux operating system and have Internet access.

Conventions

In this book, you will find several headings appearing frequently.

To give clear instructions of how to complete a procedure or task, we use:

Time for action – heading

Action 1

Action 2

Action 3

Instructions often need some extra explanation so that they make sense, so they are followed with:

What just happened?

This heading explains the working of tasks or instructions that you have just completed.

You will also find some other learning aids in the book, including:

Pop quiz – heading

These are short multiple-choice questions intended to help you test your own understanding.

Have a go hero – heading

These practical challenges give you ideas for experimenting with what you have learned.

You will also find a number of styles of text that distinguish between different kinds of information. Here are some examples of these styles, and an explanation of their meaning.

Code words in text are shown as follows: "We made a copy of the report 05_Adding_Groups.prpt and saved it with the