Report Designer User Guide
User Manual:
Open the PDF directly: View PDF 
.
Page Count: 247
Design Print-Quality Reports
This document supports Pentaho Business Analytics Suite 5.0 GA and Pentaho Data Integration 5.0 GA,
documentation revision August 28, 2013, copyright © 2013 Pentaho Corporation. No part may be reprinted without
written permission from Pentaho Corporation. All trademarks are the property of their respective owners.
Help and Support Resources
If you do not find answers to your quesions here, please contact your Pentaho technical support representative.
Support-related questions should be submitted through the Pentaho Customer Support Portal at
http://support.pentaho.com.
For information about how to purchase support or enable an additional named support contact, please contact your
sales representative, or send an email to sales@pentaho.com.
For information about instructor-led training, visit
http://www.pentaho.com/training.
Liability Limits and Warranty Disclaimer
The author(s) of this document have used their best efforts in preparing the content and the programs contained
in it. These efforts include the development, research, and testing of the theories and programs to determine their
effectiveness. The author and publisher make no warranty of any kind, express or implied, with regard to these
programs or the documentation contained in this book.
The author(s) and Pentaho shall not be liable in the event of incidental or consequential damages in connection
with, or arising out of, the furnishing, performance, or use of the programs, associated instructions, and/or claims.
Trademarks
Pentaho (TM) and the Pentaho logo are registered trademarks of Pentaho Corporation. All other trademarks are the
property of their respective owners. Trademarked names may appear throughout this document. Rather than list
the names and entities that own the trademarks or insert a trademark symbol with each mention of the trademarked
name, Pentaho states that it is using the names for editorial purposes only and to the benefit of the trademark
owner, with no intention of infringing upon that trademark.
Third-Party Open Source Software
For a listing of open source software used by each Pentaho component, navigate to the folder that contains the
Pentaho component. Within that folder, locate a folder named licenses. The licenses folder contains HTML.files that
list the names of open source software, their licenses, and required attributions.
Contact Us
Global Headquarters Pentaho Corporation
Citadel International, Suite 340
5950 Hazeltine National Drive
Orlando, FL 32822
Phone: +1 407 812-OPEN (6736)
Fax: +1 407 517-4575
http://www.pentaho.com
Sales Inquiries: sales@pentaho.com
| TOC | 3
Contents
Introduction................................................................................................................................ 6
How to Start Report Designer....................................................................................................7
Starting Report Designer on Windows..........................................................................................................7
Starting Report Designer on Linux................................................................................................................7
Starting Report Designer on OS X................................................................................................................7
Report Designer Configuration Directories................................................................................ 8
Pentaho Reporting Configuration Files.........................................................................................................8
Workflow.................................................................................................................................... 9
Navigating Report Designer.....................................................................................................10
The Welcome Screen................................................................................................................................. 10
The Report Designer Main Toolbar............................................................................................................ 10
Report Designer's Tabbed Views............................................................................................................... 11
The Report Workspace...............................................................................................................................11
The Structure Pane.....................................................................................................................................12
The Data Pane............................................................................................................................................13
Function Reference..........................................................................................................................14
The Style Pane........................................................................................................................................... 18
Style Properties Reference.............................................................................................................. 19
The Attributes Pane.................................................................................................................................... 25
Element Attributes Reference.......................................................................................................... 26
The Palette................................................................................................................................................. 32
Data Sources and Queries...................................................................................................... 35
Supported Data Sources............................................................................................................................ 35
Adding Data Sources..................................................................................................................................35
Adding a JDBC Data Source........................................................................................................... 35
Adding a Metadata Data Source......................................................................................................37
Adding a Pentaho Data Integration Data Source.............................................................................37
Adding an OLAP Data Source......................................................................................................... 38
Adding an OLAP (Advanced) Data Source......................................................................................38
Adding an XML Data Source........................................................................................................... 39
Adding a Table Data Source............................................................................................................39
Adding Advanced Data Sources...................................................................................................... 40
Adding a JNDI Data Source.............................................................................................................41
Adding a MongoDB Data Source.....................................................................................................41
Creating Queries.........................................................................................................................................43
Hadoop Hive-Specific SQL Limitations............................................................................................ 43
Creating Queries With SQL Query Designer................................................................................... 43
Creating Queries With Metadata Query Editor................................................................................ 44
Dynamic Query Scripting................................................................................................................. 45
Creating Sub-queries With SQL Query Designer............................................................................ 45
Adding Report Elements..........................................................................................................46
Report Layout Types.................................................................................................................................. 46
Adding Standard Design Elements.............................................................................................................46
Aligning Elements....................................................................................................................................... 47
Adding Bands............................................................................................................................................. 47
Creating Sub-Reports................................................................................................................................. 48
Referring to Report Elements by Name or Column Position...................................................................... 48
Creating a Table of Contents......................................................................................................................48
Creating an Index....................................................................................................................................... 49
Creating Charts........................................................................................................................51
Choosing the Right Chart Type.................................................................................................................. 51
Creating a JFreeChart Element.................................................................................................................. 51
Creating a Sparkline Chart......................................................................................................................... 52
Sparkline.......................................................................................................................................... 52
| TOC | 4
Applying Formatting to Report Elements................................................................................. 53
Standard Element Formatting.....................................................................................................................53
Creating Hyperlinks on Visualizations........................................................................................................ 53
Creating a Link to a Report on a Chart............................................................................................ 54
Paste Formatting........................................................................................................................................ 55
Morphing an Element..................................................................................................................................55
Implementing Row Banding........................................................................................................................55
Performing Calculations...........................................................................................................57
Using the Formula Editor............................................................................................................................ 57
Common Formulas.......................................................................................................................... 57
Summarizing Data in Groups......................................................................................................................60
Output Parameterization..........................................................................................................62
Simple SQL Output Parameterization.........................................................................................................62
Advanced SQL Output Parameterization....................................................................................................63
Simple Metadata Output Parameterization.................................................................................................64
Simple OLAP Output Parameterization...................................................................................................... 64
Permanently Overriding the Auto-Submit Option........................................................................................65
Integration With the Pentaho BA Server..................................................................................67
Publishing to the BA Server........................................................................................................................67
Editing an Interactive Report...................................................................................................................... 67
Hiding Reports............................................................................................................................................ 68
Linking Reports...........................................................................................................................................68
Linking in Tabs.................................................................................................................................68
Localizing a Report.................................................................................................................. 69
Using Externalized Message Bundles........................................................................................................ 69
Creating Report Design Wizard and Interactive Reporting Templates.................................... 70
Report Design Wizard Template Design Guidelines.................................................................................. 70
Interactive Reporting Template Design Guidelines.................................................................................... 71
Dynamic Element Positioning in Templates............................................................................................... 71
Template Properties................................................................................................................................... 72
Deploying a Template to Report Design Wizard.........................................................................................73
Deploying a Template to Interactive Reporting...........................................................................................73
Setting the Default Interactive Reporting Template.................................................................................... 74
Troubleshooting....................................................................................................................... 75
EnablingMulti-ValuedreportParametersforMetadata-basedQueriesCreatedwithPreviousVersions
75 ofReportDesigner
Report Elements With Dynamic Heights Overlap Other Elements............................................................. 75
Columns Unexpectedly Merge When Exporting to Excel........................................................................... 75
Tutorials................................................................................................................................... 76
Creating a Report Using Report Designer.................................................................................................. 76
Designing Your Report.....................................................................................................................78
Refining Your Report....................................................................................................................... 79
Adding a Chart to Your Report.........................................................................................................84
Adding Parameters to Your Report..................................................................................................86
Publishing Your Report.................................................................................................................... 88
Chart Types............................................................................................................................. 90
Bar.............................................................................................................................................................. 90
Data Collectors................................................................................................................................ 91
Chart Properties...............................................................................................................................93
Line........................................................................................................................................................... 102
Data Collectors.............................................................................................................................. 102
Chart Properties.............................................................................................................................104
Area.......................................................................................................................................................... 113
Data Collectors.............................................................................................................................. 113
Chart Properties.............................................................................................................................115
Pie.............................................................................................................................................................123
Data Collectors.............................................................................................................................. 124
Chart Properties.............................................................................................................................124
Multi-Pie....................................................................................................................................................131
Data Collectors.............................................................................................................................. 131
| TOC | 5
Chart Properties.............................................................................................................................133
Bar Line Combination............................................................................................................................... 139
Data Collectors.............................................................................................................................. 139
Chart Properties.............................................................................................................................141
Ring.......................................................................................................................................................... 152
Data Collectors.............................................................................................................................. 153
Chart Properties.............................................................................................................................153
Bubble.......................................................................................................................................................160
Data Collectors.............................................................................................................................. 160
Chart Properties.............................................................................................................................161
Scatter Plot............................................................................................................................................... 170
Data Collectors.............................................................................................................................. 170
Chart Properties.............................................................................................................................173
XY Bar...................................................................................................................................................... 181
Data Collectors.............................................................................................................................. 182
Chart Properties.............................................................................................................................184
XY Line..................................................................................................................................................... 193
Data Collectors.............................................................................................................................. 193
Chart Properties.............................................................................................................................196
XY Area.................................................................................................................................................... 204
Data Collectors.............................................................................................................................. 205
Chart Properties.............................................................................................................................207
XY Extended Line (XY Step, XY StepArea, XY Difference)..................................................................... 216
Data Collectors.............................................................................................................................. 218
Chart Properties.............................................................................................................................220
Waterfall....................................................................................................................................................229
Data Collectors.............................................................................................................................. 229
Chart Properties.............................................................................................................................231
Radar........................................................................................................................................................ 239
Data Collectors.............................................................................................................................. 240
Chart Properties.............................................................................................................................242
| Introduction | 6
Introduction
Pentaho Report Designer is a sophisticated report creation tool that you can use standalone, or as part of the larger
Pentaho Business Analytics distribution. It enables professionals to create highly detailed, print-quality reports based on
adequately prepared data from virtually any data source.
Report Designer is one of several ways to create reports with Pentaho software. Through the BA Server's Webbased Pentaho User Console, you can also use the Interactive Reporting interface, or you can integrate the Pentaho
Reporting engine (on which Report Designer is built) into your own software.
This section covers all of the major Report Designer features and functions, from adding a data source to working
with conditional formatting and formulas. You can read it cover-to-cover to attain a reasonably comprehensive Report
Designer education, or you can use it strictly as a reference to consult when you run into an operational challenge.
| How to Start Report Designer | 7
How to Start Report Designer
How you start Report Designer depends on which platform you are using Windows, Linux, or OS X.
Starting Report Designer on Windows
If you used the Pentaho Business Analytics installer available to you through your subscription, you will have a Start
menu category for all of your Pentaho applications. To run Report Designer, click the Report Designer item in the
Pentaho Business Analytics subdirectory in the Pentaho application folder. Alternatively, you can run the \pentaho
\design-tools\report-designer\report-designer.exe from Windows Explorer or the command prompt.
Starting Report Designer on Linux
The Business Analytics installer does not create program entries in the K menu or Applications menu in Linux desktop
environments, so you will have to start Report Designer by navigating to the /pentaho/design-tools/reportdesigner/ directory and running the report-designer.sh script. You can do this from your file manager, or from a
terminal window.
Starting Report Designer on OS X
The Mac installation procedure does not create program entries in the dock, so you will have to start Report Designer
by opening your Applications folder, then the report-designer sub-folder, then running report-designer.app.
| Report Designer Configuration Directories | 8
Report Designer Configuration Directories
Upon first launch, Report Designer creates a .pentaho directory in the current user's home directory, and populates it
with the following subdirectories:
Directory
Purpose
caches
Contains cached fonts, which speeds up report rendering
classic-engine
A cache directory that contains low-level options saved by the Pentaho
Reporting engine
report-designer
Contains both the default Pentaho-supplied report samples and content, and
user preferences for the Report Designer interface
report-design-wizard
Contains the default Pentaho-supplied Report Design Wizard templates
simple-jndi
Holds a single properties file that contains JNDI connection information. By
default it has connection details for the Pentaho-supplied HSQLDB sample
database
Pentaho Reporting Configuration Files
The following files contain various configuration options for Pentaho Reporting. The options are not particularly selfexplanatory and their value limits are not obvious; therefore, you shouldn't change any options in these files unless you
are following guidelines from Pentaho documentation or are assisted by a Pentaho support or consulting representative.
File
Purpose
/pentaho/design-tools/report-designer/resources/reportdesigner.properties
Contains options for the Report Designer client tool. It
does not change any report options.
/pentaho/design-tools/report-designer/resources/classicengine.properties
Contains global report rendering options for reports
generated locally from Report Designer. Some of these
options can be overridden in individual reports.
/tomcat/webapps/pentaho/WEB-INF/classes/classicengine.properties
Contains global report rendering options for published
reports that are generated on the BA Server. Some of
these options can be overridden in individual reports.
| Workflow | 9
Workflow
To create a report in Report Designer, follow this process.
1.
2.
3.
4.
5.
6.
Connect to a data source (database, usually, though you can also pull data from a flat file)
Constrain the data with a query
Arrange data elements in the Report Designer workspace
Apply formatting and add extra graphical elements
Create formulas or calculated fields using data retrieved from your query
Publish the report, either to the Pentaho BA Server, or locally as a PDF or other supported file format
Your report will consist mostly of data retrieved from a database query that you will create through Report Design
Wizard, SQL Query Designer, MQL Query Builder, or by hand. Once you have a dataset, you are able to further
constrain it to show specific details, and then move on to report layout and design.
| Navigating Report Designer | 10
Navigating Report Designer
If you have little or no experience with Report Designer, then you will need to learn how to navigate the user interface
before you can move on to more complex tasks. The content in this section provides a comprehensive yet brief
introduction to all of Report Designer's user interface components.
The Welcome Screen
The Welcome screen's primary purpose is to provide new users a quick, four-step process that walks you through
creating a new report through the Report Design Wizard. This is the default view when you start Report Designer, but if
you close it, you can make it reappear at any time by going to the Help menu and selecting Welcome.
In addition to the new report creation buttons, the Welcome screen also shows a list of sample reports. You might find
these useful if you're looking for inspiration, or if you can't figure out how to use a certain Report Designer feature. In
order to display the samples, you must have the Pentaho sample data HSQLDB database installed and running.
If you do not want to see the Welcome screen at start up, you can uncheck the Show at startup option in the lower
right corner of the window.
The Report Designer Main Toolbar
The toolbar at the top of the Report Designer window is for file, data, publishing, and cut-and-paste operations. The
toolbar makes some of the most frequently used features more accessible to users who have not yet learned keyboard
shortcuts for them. There are no unique data, publishing, or file operations in the toolbar; every icon represents a
feature that is also available through one of the panes or menus in Report Designer.
To discover what each icon does, mouse over it to see a tooltip description.
Defining Preferences
To edit preferences associated with date and time format, look-and-feel, browsers, networks, external tools and
locations go to Edit -> Preferences. Enable Display the index columns in the Report Designer's field selectors...
to refer to data fields by name or column position. For more information, see Referring to Report Elements by Name or
Column Position
| Navigating Report Designer | 11
Report Designer's Tabbed Views
Each report and subreport is opened in its own tab in Report Designer, much like in modern Web browsers and text
editors. The currently selected report's tab will always be highlighted in blue, as shown in the graphic below. Click the X
in the corner of a tab to close the open report it represents, or right-click the tab to see a context menu that offers more
advanced close operations.
The button bar below the tab area offers font and preview options. The eye icon switches to preview mode, which
shows you approximately how the report, as currently arranged, will display when published. When you are in preview
mode, the eye turns into a pencil icon; click it to return to design mode.
The rest of the functions in this bar are standard font controls found in most text editors and word processors. The list of
font types is pulled from your Java Runtime Environment's fonts directory and from the TrueType fonts registered with
your operating system or desktop environment.
The Report Workspace
The workspace is dominated by the layout bands, which define each individual portion of the report. The currently
selected band's label will always be highlighted in gray, as shown with the page header band in the graphic below.
The top band is the page header, which represents the top of each report page. On the first page of a multi-page report,
the page header is at the absolute top, above the report header.
The next band is the report header, which contains report elements just below the page header, but only on the first
page of the report. The report header only appears once per report; it is not repeated on subsequent pages in the same
report.
The details band is next, and it contains middle-of-the-page report elements. This is where most of your report data
should go, and ordinarily represents the largest portion of your report pages.
Next is report footer, which appears at the bottom of the last page of the report, just above the page footer. Like the
report header, it only appears once per report.
| Navigating Report Designer | 12
The last band is the page footer, which appears at the absolute bottom of every page in a report.
You can also create groups for various report elements, with each group having its own header and footer bands in the
workspace.
All of the report bands can be resized by dragging their resize handles, or by moving report elements down past the
bottom border. For this reason, report elements cannot be dragged from one band to another; they must be cut from the
first band and pasted into the second.
If you'd like to change the size of the layout bands to give yourself more area to work in without changing the
dimensions of the published report, you can click and drag the percentage number in the upper left corner of the
workspace. By default it says 100%, but if you click and drag it diagonally toward the upper left or lower right corners,
the view will zoom in or out. If you want to reset the view to 100%, double-click the upper left corner where the
percentage shows.
The Structure Pane
The Structure tab shares a pane with the Data tab in the upper right section of Report Designer. The Structure pane
shows the exact hierarchy of every element included in a report. If you add an element to the workspace, it will show up
in the Structure pane; when selected there, all of its fine-grained details can be viewed and modified through the Style
and Attributes panes in the bottom right section of the screen. In addition to the standard drag-and-drop method using
the palette and the workspace, you can also add an element to a report by right-clicking on any of the report sections in
the Structure list, then selecting Add Element from the context menu. You can delete any element in the list by clicking
on it, then pressing the Delete key, or by right-clicking it and selecting Delete from the context menu.
Master Report or sub-report
This is the top-level category under which all other report bands are listed. For standalone or master reports, this will be
Master Report; for subreports, it will be sub-report.
Page Header
All of the elements shown in the page header band will be listed in this category.
Report Header
All of the elements shown in the report header band will be listed in this category.
Groups
If you've created any groups for your report elements, they will show up here. You can add or delete a group by rightclicking the Groups heading, then selecting the appropriate action from the context menu. You can also delete a group
by selecting it, then pressing the Delete key. Your Details band is considered a group, and is explained below.
Details
All items you place in the Details band will appear in the Details heading under the Group section. There are also
Details-specific Header and Body bands which are not displayed in the workspace by default, but are available in the
Structure pane to add to. You can add these extra bands to your workspace by selecting each of them in the Structure
pane, then clicking the checkbox next to hide-on-canvas in the Attributes pane.
No Data
In the event that your query does not return any data, whatever content you put into the No Data band will appear in
your report. You can add a no data band to your workspace by selecting No Data Band in the Structure pane, then
clicking the checkbox next to hide-on-canvas in the Attributes pane.
Report Footer
All of the elements shown in the report footer band will be listed in this category.
| Navigating Report Designer | 13
Page Footer
All of the elements shown in the page footer band will be listed in this category.
Watermark
You can add a watermark to your report by clicking Watermark in the Structure pane, then either right-clicking it and
adding an element directly through the Structure pane, or by clicking the checkbox next to hide-on-canvas in the
Attributes pane and dragging an element to the new Watermark band.
The Data Pane
The Data pane enables you to add data sources and view the individual queries, functions, and parameters in each
report. The three buttons at the top of the pane will add a new data source, function, or parameter when clicked,
respectively.
Data Sets
All of the data sources and queries you have defined for the current report will be listed here. If you want to add a new
data source, click the leftmost icon (the yellow cylinder) and select the data source type from the ensuing drop-down
menu. To add a new query to an established data source, right-click the data source and then select Edit DataSource
from the context menu. To delete a data source, select it, then press the Delete key, or right-click it and select Delete
from the context menu.
Functions
All of the mathematical functions and conditional elements that you add to a report will be listed in this category. Click
the fx button in the upper left corner of the pane to add a new function. You can delete a function by clicking it, then
pressing the Delete key, or by right-clicking it and selecting Delete from the context menu.
Parameters
If your query is properly formed, you can add a parameter to your report, which enables report readers to customize
the content of the output. To add a new parameter, click the rightmost icon in the upper left corner of the pane. You can
delete parameters by selecting the parameter you want to eliminate and pressing the Delete key, or by right-clicking the
parameter and selecting Delete from the context menu.
| Navigating Report Designer | 14
Environment Variables
If you are publishing your report to the Pentaho BA Server, you can use certain BA Server environment variables in
your report:
Variable
Purpose
hostColonPort
The hostname and port number for the BA Server service
pentahoBaseURL
The complete URL to the BA Server, as set in the Base
URL property.
roles
Returns a comma-separated list of roles that the BA
Server user who is currently running this report belongs to
roles-array
Returns a Java array of strings containing the roles that
the BA Server user who is currently running this report
belongs to
serverBaseURL
The URL to the BA Server, minus the BA Server
application context name (the default context is /pentaho/)
solutionRoot
The path to the top-level Pentaho solution directory. The
default location is /home/pentaho/pentaho/server/
biserver-ee/pentaho-solutions/ on Linux and
Solaris, and C:\pentaho\server\biserver-ee
\pentaho-solutions\ on Windows.
username
Returns the BA Server username of the person currently
running the published report
Function Reference
Every function available in Report Designer is defined below and categorized according to the group it belongs to.
| Navigating Report Designer | 15
Common Functions
The Common category contains functions that handle page numbering, and a generic OpenFormula feature that you
can use to create your own custom function.
Function Name
Purpose
Open Formula
Enables you to create your own custom OpenFormula
function using the built-in Formula Editor. This function will
run according to its placement in the report. If you need
a custom function to run before all other report actions,
use the Open Formula function in the Advanced category
instead.
Page
Counts the number of pages rendered thus far in a report.
Total Page Count
Lists the total number of pages in the rendered report.
Page of Pages
Prints the current page number in comparison to the total
number of pages in the rendered report.
Report Functions
The Report category contains functions that modify the layout of the rendered report.
Function Name
Purpose
Is Export Type
Tests whether the given export type has been selected for
this report.
Row Banding
Alternates the background color of each item band in a
group.
Hide Repeating
Hides equal values in a group. Only the first changed
value is printed.
Hide Page Header & Footer
Hides the page header and footer bands when the output
type is not pageable.
Show Page Footer
Only shows the page footer on the last page rendered in
the report.
Summary Functions
The Summary category contains mathematical functions that count, add, and divide report data in groups.
Function Name
Purpose
Sum
Calculates the sum of the selected numeric column. This
produces a global total.
Count
Counts the total number of items contained in a group.
If no group is specified, all items in the entire report are
counted.
Count by Page
Counts the total number of items contained in a group on
one rendered page. If no group is specified, all items on
the entire page are counted.
Group Count
Counts the total number of items in the selected groups. If
no group is specified, all items in all groups are counted.
Minimum
Identifies the lowest or smallest value in a group.
Maximum
Identifies the highest or largest value in a group.
Sum Quotient
Performs simple division on the sum totals from two
columns and returns a numeric value.
| Navigating Report Designer | 16
Function Name
Purpose
Sum Quotient Percent
Performs simple division on the sum totals from two
columns and returns a percentage value.
Calculation
Stores the result of a calculation. This function can be
used to convert a group of Running functions into a single
total Summary function.
Count For Page
Counts items on a page according to the specified criteria.
This value is reset to zero when a new page is reached.
Sum For Page
Adds all of the specified items on one page and produces
a total. This value is reset to zero when a new page is
reached.
Running Functions
The Running category contains mathematical functions that deal with running totals, as opposed to global or summary
totals.
Function Name
Purpose
Sum
Calculates a running total sum of the specified column.
Count
Counts the items in a group or report.
Group Count
Counts the number of groups in a report.
Count Distinct
Counts the distinct occurrences of a certain value in a
column.
Average
Calculates the average value in a given column.
Minimum
Identifies the lowest or smallest value in a column.
Maximum
Identifies the highest or largest value in a column.
Percent of Total
Calculates the percentage value of a numeric column. The
total sum is divided by the number of items counted.
Advanced Functions
The Advanced category contains functions that deal with developer-centric actions.
Function Name
Purpose
Message Format
Formats text according to the Java Message Format
specification.
Resource Message Format
Formats text from a resource bundle according to the
Java Message Format specification.
Lookup
Maps a string from one column to another string. The
possible mappings are given as (key, text) pairs. If the
string from the column is null or matches none of the
defined keys, a fallback value is returned.
Indirect Lookup
Returns a value from a mapped field. The field's value is
used as a key to the field-mapping. The expression maps
the value to a new column name and returns the value
read from this column.
Resource Bundle Lookup
Performs a resource-bundle lookup using the value from
the defined field as a key in the resource bundle. This
expression behaves like a resource field.
| Navigating Report Designer | 17
Function Name
Open Formula
Purpose
Enables you to create your own custom OpenFormula
function using the built-in Formula Editor. This function will
run before any other action in the report.
Chart Data Functions
The Chart Data category contains functions that create datasets for JFreeChart elements.
Function Name
Purpose
CategorySet Data Collector
See CategorySet on page 91
Pie DataSet Collector
See PieSet
Pivoting CategorySet Data Collector
See PivotCategorySet on page 92
TimeSeries Collector
See TimeSeries
XY-Series Collector
See XYSeries
XYZ-Series Collector
See XYZSeries
Image Functions
The Image category contains functions .
Function Name
Purpose
Area Chart
See Area on page 113
Bar Chart
See Bar on page 90
Bar Line Chart
See Bar Line Combination on page 139
BarCode
A simple barcode chart available through the Report
Designer palette
Bubble Chart
See Bubble on page 160
Extended XY Line Chart
See XY Extended Line (XY Step, XY StepArea, XY
Difference) on page 216
Line Chart
See Line on page 102
Multi Pie Chart
See Multi-Pie on page 131
Pie Chart
See Pie on page 123
Radar Chart
See Radar on page 239
Ring Chart
See Ring on page 152
Scatter Plot Chart
See Scatter Plot on page 170
Sparkline
Creates a Sparkline chart element. See Creating a
Sparkline Chart on page 52 for more details.
Survey Scale
A sliding scale chart element.
Waterfall Chart
See Waterfall on page 229
XY Area Chart
See XY Area on page 204
XY Bar Chart
See XY Bar on page 181
XY Line Chart
See XY Line on page 193
Script Functions
The Script category contains functions that enable you to directly type in code from a supported scripting language.
| Navigating Report Designer | 18
•
•
•
•
Bean-Scripting Framework (BSF)
Bean-Scripting Host (BSH)
JavaScript
Single Value Query
The only unique object Pentaho offers in Report Designer for a scripting language is getValue for the Bean-Scripting
Framework, which retrieves the current record or row, as shown below:
Object getValue()
{
Object value = dataRow.get("RegionVariance");
if (value instanceof Number == false)
{
return Boolean.FALSE;
}
Number number = (Number) value;
if (number.doubleValue() < 0)
{
return Boolean.TRUE;
}
return Boolean.FALSE;
}
Deprecated Functions
The Deprecated category contains functions that had to be included in this version of Report Designer to provide
backwards compatibility for files created with older Report Designer versions. You should never use any of these
functions in new reports; there are no Deprecated functions that are not more sensibly implemented in other functions in
other categories.
The Style Pane
The Style pane displays all of the visual and positional style options for any given item in the Structure pane. Click on
any Structure element, and the composition of the Style pane will adjust to show all of the available style properties,
listed by group.
Note: You cannot edit any Style or Attributes options for any selected report elements in the workspace while
the Data tab has focus. Click the Structure tab to see the Style and Attributes panes for selected elements.
| Navigating Report Designer | 19
Style Properties Reference
Every report element can be customized according to the below-listed parameters. To access style properties, click the
Style tab in the lower right pane.
Font Styles
Font styles control the font and font properties pertaining to the text of the selected element.
Property Name
Data Type
Purpose
family
Selection
The name of the font or font family
font-size
Integer
The size of the font, in points (1/72 of
an inch)
bold
Boolean
A flag indicating whether a bold-type
face should be used
italics
Boolean
A flag indicating whether a italic or
oblique type face should be used
underline
Boolean
A flag indicating whether the text
should be underlined
strikethrough
Boolean
A flag indicating whether the text
should be rendered striken through
smooth
Selection
A flag indicating whether text-aliasing
should be activated
embed
Boolean
A flag indicating whether the font
information should be embedded into
the target document
Text Styles
Font styles control the font and font properties pertaining to the text of the selected element.
| Navigating Report Designer | 20
Note: For elements that control colors, possible values are standard HTML color names (red, blue, green,
black, etc.) or hexadecimal color values (#000000, #FFFFFF, #CCFF00, etc.)
Property Name
Data Type
Purpose
h-align
Selection
Horizontally aligns the selected
content within this element
v-align
Selection
The size of the font, in points (1/72 of
an inch)
v-align-in-band
Selection
A extended text-alignment that allows
fine control on how inline-text is
aligned within a line
text-wrap
Boolean
A flag indicating whether text will
automatically wrap at the end of the
line
text-color
Selection
The text (foreground) color.
bg-color
Selection
The element's background color.
line-height
Integer
Defines the height of a single text line.
Is always greater or equal to the font
size
overflow-text
String
A text quote that is printed if the given
text does not fully fit into the element
bound
trim
Boolean
A flag indicating whether leading and
trailing white spaces will be removed
trim-whitespace
Selection
Controls how the renderer treats white
spaces
bg-ext
String
A extended foreground paint property.
Expert option
encoding
Boolean
Specifies the target text-encoding
for the given field, in case the output
supports per-field encodings
Text Spacing Styles
Text spacing styles control the amount of space between letters and words in a textual element.
Property Name
Data Type
Purpose
character
Integer
The minimum space between two
letters
word
Integer
Defines additional spacing between
words
preferred-character
Integer
The preferred space between two
letters
max-character
Integer
The maximum space between two
letters
Padding Styles
Padding styles control the space around the selected element.
| Navigating Report Designer | 21
Property Name
Data Type
Purpose
top
Decimal
Defines the padding on the top edge
of the element
bottom
Decimal
Defines the padding on the bottom
edge of the element
left
Decimal
Defines the padding on the left edge
of the element
right
Decimal
Defines the padding on the right edge
of the element
Object Styles
Object styles control the appearance of shape elements.
Property Name
Data Type
Purpose
fill
Boolean
A flag indicating whether the given
shape should be filled.
fill-color
Selection
Provides a way to define alternative
fill-colors. If undefined, the foreground
color is used.
draw-outline
Boolean
Defines whether the shape-outline
should be drawn in the foreground
color.
stroke
Selection
Defines the stroke (pen type and
width) that should be used to render a
shape.
anti-alias
Boolean
Defines whether drawable content
should be rendered with anti-aliasing
enabled.
aspect-ratio
Boolean
A flag indicating whether the scaling
should preserve the aspect ratio.
scale
Boolean
A flag indicating whether the content
printed in the element should be
scaled to fit the element's boundaries.
Size & Position Styles
Size & Position styles control the size and position of the selected element.
Property Name
Data Type
Purpose
height
Decimal
The element's preferred height. If
defined, this overrides all other height
definitions including the dynamicheight flag
width
Decimal
The preferred width. If defined, this
width overrides all other layout rules
x
Decimal
The X-Coordinate where the element
should be placed
y
Decimal
The Y-Coordinate where the element
should be placed
visible
Boolean
A flag indicating whether the element
will be printed
| Navigating Report Designer | 22
Property Name
Data Type
Purpose
invisible-consumes-space
Boolean
A flag indicating whether an element
set to visible maintains its space
dynamic-height
Boolean
A flag indicating whether the field
should expand its height based on the
content it contains
min-height
Decimal
The element's minimum height
max-height
Decimal
The element's maximum height
min-width
Decimal
The element's minimum width
max-width
Decimal
The element's maximum width
x-overflow
Boolean
Defines whether content is allowed to
overflow the element's layouted box
to the right
y-overflow
Boolean
Defines whether content is allowed to
overflow the element's layouted box
to the bottom
fixed-position
String
(Group elements only) Shifts the band
to a fixed position on the page
layout
String
(Band elements only) The layout
strategy for elements in bands.
See Report Layout Types on page
46 for more information on layout
options.
box-sizing
Selection
Specifies the border-model to use
Links Styles
Links styles control the properties of any HTML links created from any report element.
Property Name
Data Type
Purpose
pdf-bookmark
String
(Band elements only) Adds bookmark
to pdf outputs
html-anchor
String
The name of an anchor (link-target)
embedded in the html outputs
url
String
The destination URL
url-tool-tip
String
Hyperlink title that is displayed as
tooltip
url-window-title
String
The window where the link should be
opened
Excel Styles
Excel styles control XLS output options.
Property Name
Data Type
Purpose
sheet-name
String
The title of the sheet/table generated
in table-exports
format-override
String
A override setting that provides a
Excel-specific cell-formats
| Navigating Report Designer | 23
Property Name
Data Type
Purpose
formula-override
String
A override setting that provides a
formula that should be printed in the
generated Excel-cell instead of the
original content
wrap-text
Boolean
A override setting that defines
whether Excel-Cells should have textwrapping enabled
Sparkline Styles
Sparkline styles control the colors of the various sparkline chart types.
Property Name
Data Type
Purpose
low-color
Selection
Defines the color of the lower value
on a pie sparkline
medium-color
Selection
Defines the color for midrange values
on a pie sparkline
high-color
Selection
Defines the color of the higher value
on pie and bar sparklines
last-color
Selection
Defines the color of the last value in a
series on a bar sparkline, and the line
color on a line sparkline
Page Behavior Styles
Page behavior styles control page display and rendering properties of the selected element when publishing to a pageaware file format.
Property Name
Data Type
Purpose
display-on-first-page
Boolean
(Band elements only) If true, only
displays this band on the first page
display-on-last-page
Boolean
(Band elements only) If true, only
displays this band on the last page
repeat-header
Boolean
(Header and footer elements only) If
true, repeats this header or footer on
every printed page
page-break-after
Boolean
If true, a page break will occur before
this element
page-break-before
Boolean
If true, a page break will occur after
this element
sticky
Boolean
If true, imports page-header/footer
and the repeated group-header/
footer from the master report into sub
reports
avoid-page-break
Boolean
If true, cancels a predefined (through
a formula or function) page break
orphan
Integer
Defines the minimum number of
elements or lines at end of the page
before a pagebreak can occur within
the band or paragraph
widows
Integer
Defines the minimum number of
elements or lines at the beginning
| Navigating Report Designer | 24
Property Name
Data Type
Purpose
of the page before a pagebreak can
occur within the band or paragraph
Border Styles
Border styles control the color, texture, and size of the border around the selected element.
Note: For elements that control colors, possible values are standard HTML color names (red, blue, green,
black, etc.) or hexadecimal color values (#000000, #FFFFFF, #CCFF00, etc.)
Property Name
Data Type
Purpose
top-size
Integer
Specifies the size (in pixels) of the top
border segment
top-style
Selection
Specifies the line style of the top
border segment
top-color
Selection
Specifies the color of the top border
segment
top-left-round-height
Integer
Specifies the degree of vertical
roundness (in pixels) of the top left
border corner
top-left-round-width
Integer
Specifies the degree of horizontal
roundness (in pixels) of the top left
border corner
top-right-round-height
Integer
Specifies the degree of vertical
roundness (in pixels) of the top right
border corner
top-right-round-width
Integer
Specifies the degree of horizontal
roundness (in pixels) of the top right
border corner
bottom-size
Integer
Specifies the size (in pixels) of the
bottom border segment
bottom-style
Selection
Specifies the line style of the bottom
border segment
bottom-color
Selection
Specifies the color of the bottom
border segment
bottom-left-round-height
Integer
Specifies the degree of vertical
roundness (in pixels) of the bottom left
border segment
bottom-left-round-width
Integer
Specifies the degree of horizontal
roundness (in pixels) of the bottom left
border segment
bottom-right-round-height
Integer
Specifies the degree of vertical
roundness (in pixels) of the bottom
right border segment
bottom-right-round-width
Integer
Specifies the degree of horizontal
roundness (in pixels) of the bottom
right border segment
left-color
Selection
Specifies the color of the left border
segment
| Navigating Report Designer | 25
Property Name
Data Type
Purpose
left-size
Integer
Specifies the size (in pixels) of the left
border segment
left-style
Selection
Specifies the line style of the left
border segment
right-color
Selection
Specifies the color of the right border
segment
right-size
Integer
Specifies the size (in pixels) of the
right border segment
right-style
Selection
Specifies the line style of the right
border segment
break-color
Selection
Specifies the color of the border
segment that closes an element that
is cut short by a page break
break-style
Selection
Specifies the color of the border
segment that closes an element that
is cut short by a page break
break-size
Integer
Specifies the color of the border
segment that closes an element that
is cut short by a page break
The Attributes Pane
The Attributes pane displays all of the low-level properties, and input and output options for any given item in the
Structure pane. Click on any Structure element, and the composition of the Attributes pane will adjust to show all of the
possible ways its can be customized.
Note: You cannot edit any Style or Attributes options for any selected report elements in the workspace while
the Data tab has focus. Click the Structure tab to see the Style and Attributes panes for selected elements.
| Navigating Report Designer | 26
Element Attributes Reference
Below is a complete reference for all of the items in the Attributes pane in Report Designer, organized alphabetically by
property name. Not all attributes will apply to every report element.
Barcode
The below attributes belong to the barcode property:
Attribute Name
Purpose
Possible Values
bar-height
Sets the height of the bar code.
Integer of any value.
bar-width
Sets the width of the bar code.
Integer of any value.
checksum
Shows the checksum value.
Boolean; default is false.
type
Sets the type of bar code.
String; default is code128 (see
Barbecue project documentation for
details)
show-text
Displays text under the bar code.
Boolean; default is false.
Common
The below attributes belong to the common property:
Attribute Name
Purpose
Possible Values
type
Defines the type of element.
Predefined and locked as an element
type.
field-name
Defines the existing field to use.
String; there is no default, you must
define a value manually.
value
Defines a hard coded value instead of String or integer; no default value.
a field.
group-fields
Defines the field to group by.
String or integer; no default value.
resource-value
A resource string found within the
resource bundle.
String; no default value.
resource-identifier
Defines the file that contains resource String; no default. This is a properties
strings.
file, including the path.
name
The name you want to assign to this
element.
String; no default value.
output-format
Sets preferred output type for the
report.
String; no default value. Possible
values are: html, pdf, xls, rtf, csv, xml.
lock-output-format
Locks the output type specified in the
output-format attribute so that no
other output type can be chosen.
Boolean; default is false.
auto-submit
If selected, your parameterized
reports will automatically update
based on the current selection in
your parameter lists. If un-checked,
parameter selections will not
automatically change the report data;
report users will have to select a
new parameter value and then click
View Report to update the report.
To change this globally in reports
published to the BA Server, see
Boolean; default is false.
| Navigating Report Designer | 27
Attribute Name
Purpose
Possible Values
Permanently Overriding the AutoSubmit Option on page 65.
format
Sets the Java format string.
String; there is no default. Must be in
the Java number or date format.
if-null
If the defined field or value returns
null, show the value defined here.
Boolean; default value is false.
message-null-value
If the message returns null, then
return the value defined here.
String; no default value.
arc-height
Defines the arc (corner) height of a
rectangle.
Integer; default value is 0.
arc-width
Defines the arc (corner) width of a
rectangle.
Integer; default value is 0.
data-cache
Determines whether parameter
Boolean; default is True (cache is
result sets are cached, which would
turned on).
reduce the amount of reload time
when switching parameters in a
rendered report. This option can
be further configured through the
org.pentaho.reporting.engine.classic.
core.cache.InMemoryCache.CachableRowLimit
and
org.pentaho.reporting.engine.classic.
core.cache.InMemoryCache.MaxEntries
engine settings.
Table of Contents
The below attributes belong to the table-of-contents property:
Attribute Name
Purpose
Possible Values
group-fields
Defines both the depth of the datacollection and the fields from which to
read the group-value-X values.
If the group-field given in the array
is empty, the field value will be read
from the current relational group
and in the details-processing, the
value will be null. If the group-fields
list is empty, an automatic mode
is activated that collects all groups
extracting the group-value from the
relational group.
title-field
Defines a field in the master-report
that will be read for a valid item-title.
Any column field or function
title-formula
Defines a formula that is evaluated
when a new item has been collected.
The formula will only be evaluated if
the title-field is not set.
formula
collect-details
Defines, whether detail items should
be included in the data-collection.
Boolean; default is false.
Note: This attribute
consumes a significant
amount of system memory.
Do not use this attribute on
reports that are over a million
rows.
| Navigating Report Designer | 28
Attribute Name
index-separator
Purpose
Possible Values
Defines the separator text that is used String; default is comma ",".
between the index-elements.
Index
The below attributes belong to the index property:
Attribute Name
Purpose
Possible Values
data-field
Defines the field to be used as the
item-data or item-key.
Any column field or function
data-formula
Defines an open formula to be used
as the item-data or item-key.
Formula
Important: Make sure that
data-field is not defined, if this
attribute is used.
index-separator
Defines the separator text that is used String; default is comma, (,).
between page numbers in the itempages field in the index sub report. It
defaults to ",".
condensed-style
Defines whether or not a dash (-)
is used between continuous page
numbers; for example, 4,5,6,7 display
as 4-7.
Boolean; default is false.
Excel
The below attributes belong to the excel property:
Attribute Name
Purpose
Possible Values
formula
Converts an entered value or formula
into a Excel numeric value.
String; no default value.
page-header-text
Appends text into Excel page header.
String; no default value.
page-footer-text
Appends text into Excel page footer.
String; no default value.
HTML
The below attributes belong to the html property:
Attribute Name
Purpose
Possible Values
append-header
Inserts the HTML entered here into
the of the HTML output.
String; no default value.
append-body
Inserts the HTML entered here into
the of the HTML output.
String; no default value.
append-body-footer
Inserts the HTML entered here into
the