WIXLIB

Pentaho Report Designer User Guide

This document supports Pentaho Business Analytics Suite 4.8 GA and Pentaho Data Integration 4.4 GA,documentation revision October 31, 2012.This document is copyright 2012 Pentaho Corporation. No part may be reprinted without written permission fromPentaho Corporation. All trademarks are the property of their respective owners.Help and Support ResourcesIf you have questions that are not covered in this guide, or if you would like to report errors in the documentation,please contact your Pentaho technical support representative.Support-related questions should be submitted through the Pentaho Customer Support Portal athttp://support.pentaho.com.For information about how to purchase support or enable an additional named support contact, please contact yoursales representative, or send an email to sales@pentaho.com.For information about instructor-led training on the topics covered in this guide, visithttp://www.pentaho.com/training.Limits of Liability and Disclaimer of WarrantyThe author(s) of this document have used their best efforts in preparing the content and the programs containedin it. These efforts include the development, research, and testing of the theories and programs to determine theireffectiveness. The author and publisher make no warranty of any kind, express or implied, with regard to theseprograms 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 connectionwith, or arising out of, the furnishing, performance, or use of the programs, associated instructions, and/or claims.TrademarksPentaho (TM) and the Pentaho logo are registered trademarks of Pentaho Corporation. All other trademarks are theproperty of their respective owners. Trademarked names may appear throughout this document. Rather than listthe names and entities that own the trademarks or insert a trademark symbol with each mention of the trademarkedname, Pentaho states that it is using the names for editorial purposes only and to the benefit of the trademarkowner, with no intention of infringing upon that trademark.Company InformationPentaho CorporationCitadel International, Suite 3405950 Hazeltine National DriveOrlando, FL 32822Phone: 1 407 812-OPEN (6736)Fax: 1 407 517-4575http://www.pentaho.comE-mail: communityconnection@pentaho.comSales Inquiries: sales@pentaho.comDocumentation Suggestions: documentation@pentaho.comSign-up for our newsletter: http://community.pentaho.com/newsletter/

TOC 3ContentsIntroduction. 6How to Start Report Designer.7Starting Report Designer on Windows.7Starting Report Designer on Linux.7Starting Report Designer on OS X.7Report Designer Configuration Directories. 8Pentaho Reporting Configuration Files.8Workflow. 9Navigating Report Designer.10The Welcome Screen. 10The Report Designer Main Toolbar. 10Report Designer's Tabbed Views. 11The Report Workspace.11The Structure Pane.12The Data Pane.13Function Reference.15The Style Pane. 18Style Properties Reference. 19The Attributes Pane. 25Element Attributes Reference. 26The Palette. 32Data Sources and Queries. 35Supported Data Sources. 35Adding Data Sources.35Adding a JDBC Driver.35Adding a JDBC Data Source. 36Adding a Metadata Data Source.38Adding a Pentaho Data Integration Data Source.38Adding an OLAP Data Source. 39Adding an OLAP (Advanced) Data Source.39Adding an XML Data Source. 40Adding a Table Data Source.40Adding Advanced Data Sources. 41Adding a JNDI Data Source.42Creating Queries.42Hadoop Hive-Specific SQL Limitations. 42Creating Queries With SQL Query Designer. 43Creating Queries With Metadata Query Editor. 43Dynamic Query Scripting. 44Creating Sub-queries With SQL Query Designer. 45Adding Report Elements.46Report Layout Types. 46Adding Standard Design Elements.46Aligning Elements. 47Adding Bands. 47Creating Sub-Reports. 48Referring to Report Elements by Name or Column Position. 48Creating a Table of Contents.48Creating an Index. 50Creating Charts.52Choosing the Right Chart Type. 52Creating a JFreeChart Element. 52Creating a Sparkline Chart. 53Sparkline. 53

TOC 4Applying Formatting to Report Elements. 54Standard Element Formatting.54Creating Hyperlinks on Visualizations. 54Creating a Link to a Report on a Chart. 55Paste Formatting. 56Morphing an Element.56Implementing Row Banding.56Performing Calculations.58Using the Formula Editor. 58Common Formulas. 58Summarizing Data in Groups.61Output Parameterization.63Simple SQL Output Parameterization.63Advanced SQL Output Parameterization.64Simple Metadata Output Parameterization.65Simple OLAP Output Parameterization. 65Permanently Overriding the Auto-Submit Option.66Integration With the Pentaho BA Server.68Publishing to the BA Server.68Editing an Interactive Report (.prpti) in Report Designer. 68Hiding Reports. 69Linking Reports.69Linking in Tabs.69Localizing a Report. 70Using Externalized Message Bundles. 70Creating Report Design Wizard and Interactive Reporting Templates. 71Report Design Wizard Template Design Guidelines. 71Interactive Reporting Template Design Guidelines. 72Dynamic Element Positioning in Templates. 72Template Properties. 73Deploying a Template to Report Design Wizard.74Deploying a Template to Interactive Reporting.74Setting the Default Interactive Reporting Template. 75Troubleshooting. basedQueriesCreatedwithPreviousVersions76 ofReportDesignerReport Elements With Dynamic Heights Overlap Other Elements. 76Columns Unexpectedly Merge When Exporting to Excel. 76Tutorials. 77Creating a Report Using Report Designer. 77Designing Your Report.79Refining Your Report. 80Adding a Chart to Your Report.84Adding Parameters to Your Report.86Publishing Your Report. 89Chart Types. 91Bar. 91Data Collectors. 92Chart Properties.94Line. 103Data Collectors. 103Chart Properties.105Area. 114Data Collectors. 114Chart Properties.116Pie.124Data Collectors. 125Chart Properties.125Multi-Pie.132Data Collectors. 132

TOC 5Chart Properties.134Bar Line Combination. 140Data Collectors. 140Chart Properties.142Ring. 153Data Collectors. 154Chart Properties.154Bubble.161Data Collectors. 161Chart Properties.162Scatter Plot. 171Data Collectors. 171Chart Properties.174XY Bar. 182Data Collectors. 183Chart Properties.185XY Line. 194Data Collectors. 194Chart Properties.197XY Area. 205Data Collectors. 206Chart Properties.208XY Extended Line (XY Step, XY StepArea, XY Difference). 217Data Collectors. 219Chart Properties.221Waterfall.230Data Collectors. 230Chart Properties.232Radar. 240Data Collectors. 241Chart Properties.243

Introduction 6IntroductionPentaho Report Designer is a sophisticated report creation tool that you can use standalone, or as part of the largerPentaho Business Analytics distribution. It enables professionals to create highly detailed, "pixel-perfect" reports basedon adequately prepared data from virtually any data source.Report Designer is one of several ways to create reports with Pentaho software. Through the BI Server's Web-basedPentaho User Console you can also use the Interactive Reporting interface, or you can integrate the Pentaho Reportingengine (on which Report Designer is built) into your own software. If you're an XML guru, you could also use DesignStudio to create a report by hand via an action sequence.This user guide covers all of the major Report Designer features and functions, from adding a data source to workingwith conditional formatting and formulas. You can read it cover-to-cover to attain a reasonably comprehensive ReportDesigner education, or you can use it strictly as a reference to consult when you run into an operational challenge.

How to Start Report Designer 7How to Start Report DesignerHow you start Report Designer depends on which platform you are using Windows, Linux, or OS X.Starting Report Designer on WindowsIf you used the Pentaho Business Analytics installer available to you through your subscription, you will have a Startmenu category for all of your Pentaho applications. To run Report Designer, click the Report Designer item in thePentaho Business Analytics subdirectory in the Pentaho application folder. Alternatively, you can run the ner.exe from Windows Explorer or the command prompt.Starting Report Designer on LinuxThe Business Analytics installer does not create program entries in the K menu or Applications menu in Linux desktopenvironments, 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 aterminal window.Starting Report Designer on OS XThe Mac installation procedure does not create program entries in the dock, so you will have to start Report Designerby opening your Applications folder, then the report-designer sub-folder, then running report-designer.app.

Report Designer Configuration Directories 8Report Designer Configuration DirectoriesUpon first launch, Report Designer creates a .pentaho directory in the current user's home directory, and populates itwith the following subdirectories:DirectoryPurposecachesContains cached fonts, which speeds up report renderingclassic-engineA cache directory that contains low-level options saved by the PentahoReporting enginereport-designerContains both the default Pentaho-supplied report samples and content, anduser preferences for the Report Designer interfacereport-design-wizardContains the default Pentaho-supplied Report Design Wizard templatessimple-jndiHolds a single properties file that contains JNDI connection information. Bydefault it has connection details for the Pentaho-supplied HSQLDB sampledatabasePentaho Reporting Configuration FilesThe 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 youare following guidelines from Pentaho documentation or are assisted by a Pentaho support or consulting ontains options for the Report Designer client tool. Itdoes not change any report urces/classicengine.propertiesContains global report rendering options for reportsgenerated locally from Report Designer. Some of theseoptions can be overridden in individual assicengine.propertiesContains global report rendering options for publishedreports that are generated on the BA Server. Some ofthese options can be overridden in individual reports.

Workflow 9WorkflowTo 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 queryArrange data elements in the Report Designer workspaceApply formatting and add extra graphical elementsCreate formulas or calculated fields using data retrieved from your queryPublish the report, either to the Pentaho BA Server, or locally as a PDF or other supported file formatYour report will consist mostly of data retrieved from a database query that you will create through Report DesignWizard, SQL Query Designer, MQL Query Builder, or by hand. Once you have a dataset, you are able to furtherconstrain it to show specific details, and then move on to report layout and design.