Pentaho Report Designer Tips Collection


Set encoding for PDF exports

Tired of  errors in character set PDF export… here is a little help.

File –> Configuration –> output-pageable-pdf –> Enable .Encoding checkbox and set for example  ISO-8859-1 character set as value

 

Set Locale for using in date message-fields

Writes multiple data types (text, string field, date field, and numeric field) into one object. We may have a message field including date with the following value $(report.date,date,EEEE, d MMMM yyyy HH:mm) and we want to change development locale, here is how you can modify it.

File –> Configuration –> core-module  –> Enable .environment.designtime.Locale and set for example  es_ES as value for Report Designer development stage locale

 

Set CSV export separator character

File –> Configuration –> output-table-csv –> Enable .Separator and set for example  ; as value to override default , character

Easy way to pass down all parameters to Pentaho subreports


Reblog from http://diethardsteiner.blogspot.co.uk/2014/01/easy-way-to-pass-down-all-parameters-to.html

Easy way to pass down all parameters to Pentaho subreports
At yesterday’s Pentaho London User Group (PLUG) meetup I discussed with Thomas Morgner again the topic sub-reports. For a long time I’ve tried to explain that sub-reports are a concept that shouldn’t be exposed to the actual report designer. We should all just start with a blank canvas (with no bands) and add objects (tables, crosstabs, charts, etc) and then link these objects to the data sources (frankly quite similar to how it is done in some other report designers or CDE). And parameters should just be available everywhere without having to map them to the specific objects.
Anyways, during this discussion Thomas mentioned that there is actually no need to specify all the parameters in the parameter mapping dialog in the subreport. You can just add one mapping with stars (*) in it and all parameters will be passed down automatically to the subreport. Frankly, I was puzzled and astound about this … I remembered the days when I was working on monster reports with 30 or 40 subreports and all the time had to define 17 or so parameters for each of them. Why this was not documented anywhere, I was quite wondering. Needless to say that this is a real time safer!
My next question was then, why this was not the default behaviour? If I don’t specify any parameter mappings, PRD should just pass down all parameters by default. So, I just created this JIRA case therefore and we all hope that this will be implemented as soon as possible. Please vote for it!

So excited as I was about these news, this morning I had to quickly test this approach. Here the mapping in the subreport:

1Then I just output the values in the details band of the sub-report:

2And here is how the preview looks:

3Now that was easy! Thanks Thomas!

Book Review: Pentaho 5.0 Reporting by Example: Beginner’s Guide


Hello friends today I am going to review Pentaho 5.0 Reporting by Example: Beginner’s Guide  book:

Pentaho5_Libro cover

Below you can check the link to purchase it:

http://www.packtpub.com/pentaho-5-0-reporting-by-example-beginners-guide/book

Book review by: David Fombella Pombal (twitter: @pentaho_fan)

Book Title: Pentaho 5.0 Reporting by Example: Beginner’s Guide

Authors: Mariano García Mattío, Dario R. Bernabeu

Paperback: 329 pages

I like this book because if you are a noob in Pentaho Reporting you will gain a lot of knowledge of this excellent tool, besides if you are advanced with PRD you can use it as reference book.

The best of this book is that uses Pentaho 5 recent released version and shows how to publish and interacting with reports created with Report Designer 5 into the new Business Analytics server.

You should read this book because you will learn all details of this awesome tool. Maybe you have created reports using PRD but after reading this book you will improve your reporting skills.

Rating: 8 out of 10

Pentaho 5

Chapter List

Chapter 1 – What is Pentaho Report Designer?
In this chapter it is explained what is Pentaho Report Designer and some examples of typical reports are listed: Transactional Reporting, Tactical Reporting, Strategic Reporting, and Helper Reporting. Besides it is interesting to learn about the different versions of PRD along history.

Chapter 2 – Installation and Configuration
This chapters shows us how to download, install, and configure Pentaho Reporting Designer new version 5.0. Maybe it is not funny but this chapter is useful for beginners.

Chapter 3 – Start PRD and the User Interface (UI) Layout
In this chapter you will learn how to start PRD in different operating systems. Different areas of its UI are displayed too.

Pentaho5report

Chapter 4 – Instant Gratification – Creating Your First Report with PRD
Explains how to create an initial report, how to define its data sets and how to configure the report’s sections, how to add and set insertable objects and functions, and how to preview and export your report in all available formats suchs as CSV, PDF, HTML, Excel/Excel 2007. This topic is excellent to introduce non technical users to all visualization capabilities that Pentaho Report Designer includes.

Chapter 5 – Adding a Relational Data Source
Here we will create a copy of the report created in the previous chapter and modify it so that it takes its data from a relational source. We will explain what JDBC is, what a JDBC driver is, and how it is used. Using a JDBC driver you can connect to all available database systems: MySQL, Postgres, Oracle, Infobright, Vertica, Monet, Hadoop, SQL Server, PALO, Hypersonic, CouchDB…

Datasources Pentaho5

Chapter 6 – Adding Groups
In this chapter, we will use the report we created in the previous chapter and configure it to work with groups. The information included in this chapter allows us creating reports with partial group calculations and subtotals.

Chapter 7 – Adding Parameters
This chapter will be dedicated to parameters. Through the use of parameters, the final user will be able to interact with the report. A parameter is usually represented through a user interface component, which allows the selection or input of values. Pentaho Report Designer eases the task of parameter creation by providing us a simple and intuitive interface such as : Dropdowns, MultiValue Lists, Date Pickers, Text search components…

Parameters pentaho5

Chapter 8 – Using Formulas in Our Reports
In this chapter, we will talk about formulas (PRD uses OpenFormula ). We will explain how to create them and use them. As in previous chapters, we will use a practical example to guide us as we explain how to work with formulas, creating general-use formulas that we can use as an object and specific-use formulas that we can apply to our objects’ styles and attributes. By the end of the practical example, we will see how the combination of formulas and parameters opens up new horizons for the creation and personalization of reports and allows us great flexibility in design.

Chapter 9 – Adding Charts
This part of the book is dedicated to charts. We will talk about chart functions, good and bad practices of using charts, and how to create and configure our own chartsamong other topics. Report designer uses JFreeChart to create charts. By explaining each type of chart (Bar chart, Line chart, Pie chart, Area chart, Barline chart, Ring chart, Bubble chart, Scatter-plot chart, XY chart, XY line chart, XY area chart, Waterfall chart, Radar chart, XY area line chart) , we will be able to understand which chart to use for a particular need, and how to configure the chart so that its look and feel is what we want. We will also see how charts allow us to show data from different perspectives and add value to our reports.

Radar chart

Chapter 10 – Adding Subreports
This chapter is dedicated to Subreports. We will see what Subreports are and how they work and learn about their specific features. We will also talk about the relation that exists between the main Report and its Subreports. We will be able to add information to our Reports in the form of tables, charts, and so on, whether this information is connected to our data set or not. This gives us an idea of the potential that Subreports provide.

Chapter 11 – Publishing and Running Reports in Pentaho BA Server
In this chapter we talk about Pentaho, the fastest growing, most popular, and most heavily invested in Open Source Business Intelligence (OSBI) suite in recent years. We analyze its principal characteristics and the principal projects included in the suite. With Pentaho Business Analytics Business Analytics Server, we have full BI analytic power at our fingertips through a GNU General Public License (GPL). We will see how Pentaho Report Designer (PRD) and Pentaho BA Server interact, and how we can use them to run our reports from Pentaho User Console (PUC). At the end of this chapter, we will have Pentaho BA Server running and will be able to access our reports from a web browser. Pentaho can run on any compatible application servers with JEE architectures such as JBOSS, Tomcat, WebSphere, WebLogic, Oracle AS…

Chapter 12 – Making a Difference – Reports with Hyperlinks and Sparklines
In this chapter we will see how to create and configure Hyperlinks. Hyperlinks give us great flexibility in designing our reports, as they let us drill down. Using Hyperlinks, we can create a network of reports and also re-use reports. Here we will also see a very interesting object that we can add to our reports, which will present specific information graphically: the sparkline.

Hyperlinkp5

Chapter 13 –  Environment Variables, Stylesheets, and Crosstabs
This chapter is dedicated to environment variables, stylesheets, and crosstabs. We will treat each of them in detail, defining what they are about and how to implement them in Pentaho Report Designer (PRD). Using Environment Variables allows us to interact with the BA Server that is executing our reports. That is, we can obtain the username of the user who is currently logged in and his or her roles, among other information. With Style Sheets, we can configure our report so that its look and feel varies according to certain CSS Rules. These CSS Rules are easy to configure, save, and import. Finally, we will take a look at one of PRD’s new jewels, Crosstabs. Using Crosstabs, we can show OLAP-style analytic information in our reports.

Chapter 14 – PRD Reports Embedded in Web Applications
This chapter attempts at taking not only one step forward with PRD, but many. Once this chapter is finished and the concepts presented here are understood, we will be able to create a web portal that allows users to access PRD reports, send parameters to them, and select the final presentation format. We will also be able to perform every configuration needed to have the portal working as well as have it packaged so that we can transport it and deploy it in a different environment with little modification.

Types of Parameters in Pentaho Report Designer


Pentaho Report Designer contains nine new pre built parameters. Below is a quick overview of the nine parameters. Each overview includes:

  • Screenshot and description
  • How to setup your parameter query
  • How to setup your report query to accept the parameter

Every example uses the Steel Wheels sample data:

DROP DOWN

Drop down screenshot

  • Similar to a select box in HTML
  • Displays one active option at a time unless you click on the arrow to display all options
  • Can only select one option at a time


Parameter Query

This query will display a unique list of Product Lines as options inside a drop down.

-- Name: enterProductLine
SELECT DISTINCT `products`. PRODUCTLINE
FROM   `products`

Report Query


This query will display all information for each product by the selected Product Line option from the drop down. As only one option can be selected at a time it is recommended to use a = (equal) in your WHERE clause.

-- Name: stockByProductLine
SELECT *
FROM   `products`
WHERE  `products`.PRODUCTLINE = ${enterProductLine}

Single Value List
Single value screenshot

  • Similar to a list box in HTML
  • Displays more than one option at once (including the active option)
  • To see more than one option at once change the Visible Items count under the parameter’s options window
  • Can only select one option at a time

Parameter Query

This query will display a unique list of Product Lines as options inside a single value list.

-- Name: enterProductLine
SELECT DISTINCT `products`. PRODUCTLINE
FROM   `products`

Report Query

This query will display all information for each product by the selected Product Line option from the single value list. As only one option can be selected it is recommended to use a = (equal) in your WHERE clause.

-- Name: stockByProductLine
SELECT *
FROM   `products`
WHERE  `products`.PRODUCTLINE = ${enterProductLine}

Multi Value List
Multi value screenshot

  • Similar to a multi list box in HTML
  • Displays more than one option at once (including active options)
  • To see more than one option at once change the Visible Items count under the parameter’s options window
  • Can select more than one option at a time

Parameter Query

This query will display a unique list of Product Lines as options inside a multi value list.

-- Name: enterProductLine
SELECT DISTINCT `products`. PRODUCTLINE
FROM   `products`

Report Query

This query will display all information for each product by the selected Product Line options in the multi value list. As multiple options can be selected at a time make sure you use an IN() condition in your WHERE clause.

-- Name: stockByProductLine
SELECT *
FROM   `products`
WHERE  `products`.PRODUCTLINE IN (${enterProductLine})

Radio Button
Radio button screenshot

  • Similar to radio buttons in HTML
  • Displays more than one option at once (including the active option)
  • Can only select one option at a time

Parameter Query

This query will display a unique list of Product Lines options as radio buttons.

-- Name: enterProductLine
SELECT DISTINCT `products`. PRODUCTLINE
FROM   `products`

Report Query

This query will display all information for each product by the checked Product Line radio button. As only one option can be selected at a time it is recommended to use a = (equal) in your WHERE clause.

-- Name: stockByProductLine
SELECT *
FROM   `products`
WHERE  `products`.PRODUCTLINE = ${enterProductLine}

Check Box
Check box screenshot

  • Similar to a check box in HTML
  • Displays more than one option at once (including active options)
  • Can select more than one option at a time

Parameter Query

This query will display a unique list of Product Lines options as check boxes.

-- Name: enterProductLine
SELECT DISTINCT `products`. PRODUCTLINE
FROM   `products`

Report Query

This query will display all information for each product by the checked Product Line check boxes. As multiple options can be selected at a time make sure you use an IN() condition in your WHERE clause.

-- Name: stockByProductLine
SELECT *
FROM   `products`
WHERE  `products`.PRODUCTLINE IN (${enterProductLine})

Single Selection Button
Single selection button screenshot

  • Similar to a buttons in HTML
  • Displays more than one option at once (including the active option)
  • Can only select one option at a time

Parameter Query

This query will display a unique list of Product Lines as single selection buttons.

-- Name: enterProductLine
SELECT DISTINCT `products`. PRODUCTLINE
FROM   `products`

Report Query

This query will display all information for each product by the selected Product Line button. As only one option can be selected at a time it is recommended to use a = (equal) in your WHERE clause.

-- Name: stockByProductLine
SELECT *
FROM   `products`
WHERE  `products`.PRODUCTLINE = ${enterProductLine}

Multi Selection Button
Multi selection button screenshot

  • Similar to a button in HTML
  • Displays more more than one option at once (including active options)
  • Can select more than one option at a time

Parameter Query

This query will display a unique list of Product Lines options as multi selection buttons.

-- Name: enterProductLine
SELECT DISTINCT `products`. PRODUCTLINE
FROM   `products`

Report Query

This query will display all information for each product by the selected Product Line buttons. As multiple options can be selected at a time make sure you use an IN() condition in your WHERE clause.

-- Name: stockByProductLine
SELECT *
FROM   `products`
WHERE  `products`.PRODUCTLINE IN (${enterProductLine})

Text Box
Textbox screenshot

  • Similar to a text boxes in HTML
  • Free text only
  • Is case sensitive and must be exactly the same as the option

Parameter Query

This query will display a unique list of Product Lines which the contents of the text box will match.

-- Name: enterProductLine
SELECT DISTINCT `products`. PRODUCTLINE
FROM   `products`

Report Query

This query will display all information for each product by the contents of the text box matching a Product Line option. As only one option can be selected at a time make sure you use a = (equal) in your WHERE clause.

-- Name: stockByProductLine
SELECT *
FROM   `products`
WHERE  `products`.PRODUCTLINE = ${enterProductLine}

Date Picker
Date picker screenshot

  • Similar to standard JavaScript date pickers
  • No way of formatting the date picker output, for example displays date, time and timezone

Parameter Options

As the date picker is providing the user values to input you do not need to specify a parameter query so your parameter options should look similar to the screenshot below:

Date picker options

Parameter Query

No parameter query is needed as the date picker is generating the parameter value.

Report Query

This query will display all information for orders which are greater than the specified Order Date from the date pickers value. As only one option can be selected at a time make sure you use a = (equal), >, >=, <= or < condition in your WHERE clause.

-- Name: stockByProductLine
SELECT *
FROM   `orders`
WHERE  `orders`.ORDERDATE > ${enterOrderDate}

Hopefully these descriptions  help you with parameters available in Pentaho Report Designer