Formula-Functions for simplified parametrization in Pentaho Reporting Lemonade

Creating parametrized reports or combining several of these reports via drill-down-links is not necessarily the most enjoyable of all activities when it comes to writing reports.

A drill-down report is simply a report, that has a click-able link somewhere. The link itself then points to another report and also contains all the parameters to actually see that report run.

Within the Pentaho Platform, there are two URLs that are responsible for showing a report:

1. For XAction driven reports: Call the XAction handler with a suitable XAction and all the parameters

http://localhost:8080/pentaho/ViewAction?solution=samples&path=getting-started&action=HelloWorld.xaction



2. For PRPT-Reports: Call the Reporting-Plugin’s content handler with the report file and the parameters.

http://localhost:8080/pentaho/content/reporting/reportviewer/report.html?solution=samples&path=getting-started&name=HelloWorld.prpt

URLs are usually added via an style-expression on the links::url style-property with a formula similar to this one:

="http://www.google.com/search?q=" & URLENCODE([field])

This works reasonable well for Strings and Numbers. Complex types like Arrays or Date objects, however, need to be specially formatted. The new PARAMETERTEXT function provides a easy option to get raw data objects into the right format for parametrization in drill-down reports.

="http://localhost:8080/pentaho/content/reporting/reportviewer/report.html?solution=samples&path=getting-started&name=HelloWorld.prpt¶meter=" & URLENCODE(PARAMETERTEXT([field]))

To format values inside a formula via a format-string, we now offer the MESSAGE function and extended the TEXT function to allow more control when creating strings.

The MESSAGE function uses a java.text.MessageFormat to format values into text.

=MESSAGE("{0,number} chicken crossed the road on {1,date,short} to {2}"; ( 100 * 50 + 10); DATEVALUE(20, 5, 2005); "to visit grandma")

The TEXT function simply converts a value into text.  The optional second parameter specifies a number or date format to convert the value.

=TEXT(NOW(); "dd-MM-yyyy")

This entry was posted in Parameter on by .
Thomas

About Thomas

After working as all-hands guy and lead developer on Pentaho Reporting for over an decade, I have learned a thing or two about report generation, layouting and general BI practices. I have witnessed the remarkable growth of Pentaho Reporting from a small niche product to a enterprise class Business Intelligence product. This blog documents my own perspective on Pentaho Reporting's development process and our our steps towards upcoming releases.