This document is copyright © 2009 Pentaho Corporation. No part may be reprinted without written permissionfrom Pentaho Corporation. All trademarks are the property of their respective owners.
About This Document
If you have questions that are not covered in this guide, or if you find errors in the instructions or language,please contact the Pentaho Technical Publications team at documentation@pentaho.com. The Publicationsteam cannot help you resolve technical issues with products.
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 contactyour sales 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 Warranty
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 todetermine their effectiveness. The author and publisher make no warranty of any kind, express or implied, withregard 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 connectionwith, or arising out of, the furnishing, performance, or use of the programs, associated instructions, and/orclaims.
Trademarks
Pentaho (TM) and the Pentaho logo are registered trademarks of Pentaho Corporation. All other trademarksare the property of their respective owners. Trademarked names may appear throughout this document. Ratherthan list the names and entities that own the trademarks or insert a trademark symbol with each mention of thetrademarked name, Pentaho states that it is using the names for editorial purposes only and to the benefit ofthe trademark owner, with no intention of infringing upon that trademark.
Company Information
Pentaho Corporation
Citadel International, Suite 3405950 Hazeltine National DriveOrlando, FL 32822
Phone: +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/Contents
Introduction ................................................................................................................................................ 2First Steps ................................................................................................................................................. 3
How to Log Into the Pentaho User Console ....................................................................................... 3Navigating the Pentaho User Console ............................................................................................... 3Using My Workspace ........................................................................................................................ 4Working With Ad Hoc Reports .................................................................................................................. 6
Creating An Ad Hoc Report ................................................................................................................ 6Editing an Existing Report .................................................................................................................. 7Deleting a Report ................................................................................................................................ 8Running a Report ................................................................................................................................ 8Creating An Analysis View ........................................................................................................................ 9Displaying Data in a Dashboard ............................................................................................................. 10
Creating a New Dashboard .............................................................................................................. 10Working with the Chart Designer .................................................................................................... 11Adding Data to Your Chart ............................................................................................................. 13Working with Pie Charts ................................................................................................................. 17Working with Dial Charts ................................................................................................................ 18Correcting Scaling Issues ............................................................................................................... 19Rotating Chart Axis Labels ............................................................................................................. 19Editing a Chart ................................................................................................................................ 19Adding a Data Table to a Dashboard ............................................................................................. 20Updating the Data Table Display .................................................................................................... 21Displaying a Report Designer (.prpt) File in the Dashboard ........................................................... 22Displaying an .xaction File in the Dashboard ................................................................................. 23Displaying a Web site in a Dashboard ........................................................................................... 24Working with Parameters .................................................................................................................. 25Assigning Parameters in the Query Editor ..................................................................................... 26Working with Filters .......................................................................................................................... 26Creating a Static List of Options .................................................................................................... 27Creating an SQL List ...................................................................................................................... 27Editing Filters .................................................................................................................................. 28Saving Your Dashboard .................................................................................................................... 28Editing Your Dashboard .................................................................................................................... 29Scheduling Reports ................................................................................................................................. 30
Scheduling a Report ......................................................................................................................... 30Quartz Cron Attributes .................................................................................................................... 30Deactivating Schedules ..................................................................................................................... 31Appendix: List of Pentaho BI Suite Enterprise Edition Guides ................................................................ 32
i
Introduction
The Pentaho BI Platform is a Web-based framework that leverages elements of Pentaho Analysisand Pentaho Reporting to provide easy-to-use tools for creating and sharing analysis views, ad hocreports, and dashboards. It is the heart of the BI Suite, which also includes powerful software toprepare your data and create detailed reports.
The BI Platform Enterprise Edition includes two graphical user interfaces: The Pentaho User
Console, which helps you discover patterns, trends, and other hidden information about your data;and the Pentaho Enterprise Console, which gives sysadmins, IT managers, CIOs, and databaseadministrators fine-grained control over every aspect of BI Platform configuration, management, andsecurity.
The purpose of this guide is to teach business users how to use the Pentaho User Console. Theinstructions below assume that the BI Platform has been properly configured for users, roles, anddata sources through the Pentaho Enterprise Console.
Pentaho User Console 3.5 Guide 2
First Steps
Your system administrator, IT manager, or supervisor should have provided you with a Web addressand login credentials for the Pentaho User Console. If not, now is the time to inquire about them.
How to Log Into the Pentaho User Console
Follow these steps to log into the Pentaho User Console:
1.Open a Web browser and type in the address of the Pentaho server.You'll see an introductory screen with a Login button in the middle.2.Click Login.
A login-related pop-up appears.
3.Type your user name and password in the appropriate fields and click Login.
You are now logged into the Pentaho User Console, and ready to start creating and running reportsand analysis views.
Navigating the Pentaho User Console
The Pentaho User Console was designed to be intuitive to users accustomed to standard filemanagers like Windows Explorer, and Web browsers like Firefox.
The first thing you will see when logging in is the quick launch, shown below:
Pentaho User Console 3.5 Guide 3
Note: If you do not have a Pentaho Dashboards Enterprise Edition entitlement, the New
Dashboard button will instead be Manage Content, which will open a dialog that offers quickavailability to the edit, share, and schedule functions for existing content.
Click one of the three icons in the center of the screen to create a new ad hoc report, analysis view,or dashboard.
The button bar near the top of the page also contains icons for starting new ad hoc reports, analysisviews, and dashboards, along with a button to print the current report or analysis view, and to opena previously saved solution.
The menu above the button bar performs these same functions, plus administrative actions if youare logged in as an administrator, and also offers access to My Workspace and external links tohelp and support resources.
The three buttons on the quick launch screen will appear when you log into the Pentaho UserConsole for the first time, and when you close all tabs in the solution browser.
You can change views between My Workspace and the solution browser at any time by clicking therightmost icons in the top button bar, or through the View menu.
Using My Workspace
Pentaho User Console 3.5 Guide 4
My Workspace is a graphical overview of the reports and analysis views you've created andscheduled. Click the triangle on the left side of each category to see the elements it contains.Reports that you've created or have access to will have options to the right of each report listing tosuspend or run them, and schedules have options to suspend, run, or delete them.Waiting
This category lists all private schedules that will run in the future. Clicking Cancel will delete theschedule. If you want to suspend a schedule, you should use the Suspend function of the MySchedules list instead.Complete
Each report that has been run via a schedule will show up in this list. Every time the scheduleexecutes, it overwrites that report's previous entry in the Complete list.My Schedules
This category contains a comprehensive list of your private schedules, including those that are notcurrently waiting to run. This will be identical to your Waiting list unless you have suspended any ofyour schedules.All Schedules
This is a combined list of public and private schedules under your control. It is only viewable byadministrator-level users.Public Schedules
This is a list of all public schedules set by the administrator from the Pentaho Enterprise Console.You can run a public schedule manually, but you cannot change its interval through the PentahoUser Console.
Pentaho User Console 3.5 Guide 5
Working With Ad Hoc Reports
The procedures below explain everything you need to know about working with ad hoc reports in thePentaho User Console.
Note: The ordering of all of the list elements in the ad hoc reporting interface is determined by thedata source's metadata, which is created and modified through Pentaho Metadata Editor.
Creating An Ad Hoc Report
Pentaho Report Designer is the most comprehensive report design tool in the Pentaho BI SuiteEnterprise Edition, but for preliminary report exploration or to generate a report quickly, nothingbeats the ad hoc report wizard built into the platform. Ad hoc reporting helps users quickly identifyand group interesting and relevant data, apply constraints to it, and generate a report. The resultingreports are viewable through the built-in preview feature; through a Pentaho Reporting-based Webapplication; or as a standalone PDF, XLS, CSV, or HTML file. To create an ad hoc report, followthese steps:
1.In the File menu, select the New sub-menu, then click Report.
The ad hoc reporting wizard appears in a new solution browser tab.
2.In the first step of the wizard, select a Data Source. The Details table will populate withavailable tables and columns.Data Sources are defined by your administrator.
3.In the Apply a Template field, select a predefined report template. A thumbnail preview of thetemplate will appear in the Template Details field.A template specifies a variety of properties in the report that affect its appearance, like fontsize and background colors for various report elements.4.Click Next.
5.In the Available Items list, select the business columns that you want to see in your report,and click and drag them to the Groups, Details, or Filters lists on the right. You must have atleast one business column in the Details list for a report to be valid.You can select multiple items by holding down the Ctrl key. If you want to delete an item, clickthe X in the upper right corner of the field the item is in; if you need to move an item up ordown in the list or to other groups, click the up or down arrows at the top of the Groups list.In the Preview as drop-down box, select a preview format, then click Go. When the previewappears in a new browser window or tab, verify that it contains the data you selected and thatit's presented appropriately.
Click Next when you're ready to start refining your report.
Click the Level 1 item in the Groups list. The General options list will appear on the right.Modify the level options to your preference.
6.
7.8.9.
By default, the Show Group Summary checkbox is selected; uncheck it if you do not wantto see group summary information. The default name for the group summary uses the groupname as a variable; you can change this if you like. The options in the Formatting listsdetermine where in the report the information in the selected level will appear.10.Repeat the above two procedures for other levels that you configured earlier.
11.Click the first item in the Details field. Three new options lists will appear on the right.Pentaho User Console 3.5 Guide
6
12.In the Formatting field, select the numerical formatting and alignment options appropriate for
your situation.
13.In the Calculation list, select the function you want to use to refine the element you selected.
For instance if you selected a Business Column that contains product prices, clicking on Sumwould add all of the prices together.
14.Click Add a Constraint. The field will expand to show the name of the element, a conditional
drop-down box, a text field, and a checkbox.
15.Select a conditional symbol from the drop-down box, then type in a value in the text box.This specifies the exact constraint you are applying to the element, meaning that you aredecreasing the amount of detail by adding a higher level of specificity.
16.Add further constraints as necessary by clicking Add a Constraint again. Remove a constraint
by clicking its checkbox and then clicking the X at the top of the list.
If there is more than one constraint in the list, you will have the option of adding conditionaloperators for the constraints via a drop-down box to the left of each item in the list, meaningyou can specify AND or OR conditionals on each constraint.
In the Sort Columns field, click Add to specify how each element in the Details list should besorted, then select the sort order from the drop-down box.Repeat the above steps for any other items in the Details list.
Click the first item in the Filters list. The Constraints list will appear on the right.
Add constraints as necessary, per the above instructions for adding constraints to items in theFilters list.
17.18.19.20.
For string-type Business Columns, you will have the option of searching the database tablesthat apply to the column. When this option is available, it is accessible via a magnifying glassicon to the right of the text box.
21.Repeat the above instructions for additional filters.22.Click Next to move on to the next screen.
23.To set the header, footer, description, paper type, and page orientation, change the values
accordingly.PDF is the only output type that has a concept of a page, so the Page portion of the Headerand Footer sections only applies to PDFs.
24.Click either the Save or Save As button to save your report, or select Save from the File
menu. In the ensuing file dialog, navigate to the location you want to save the report to, type ina filename for the report, and click the checkboxes next to the file formats you want to save in.
You can continue to modify your report after it's been saved; just click the Save button toupdate the saved report after you've made changes.
Note: The Save dialog does not list output types; instead, it pulls the currently selected
preview output type. By default that is HTML, so that will also be the default format to save as.If you want to save as a different output type, select it from the Preview drop-down list beforesaving.
You now have a complete ad hoc report in the format or formats you specified.
Editing an Existing Report
After you've created and saved a report, you can go back to it later and change its properties.
Pentaho User Console 3.5 Guide 7
1.In the upper left pane, navigate to the location of your report. It should appear in the lower leftlist when you've selected the proper directory.
2.In the lower left pane, right-click the report you want to edit, then click Edit in the popup menu.The ad hoc report wizard will open in a new tab.
Deleting a Report
After you've created and saved a report, you can delete it using the Explorer-like Pentaho UserConsole file browser.
1.In the left pane, navigate to the location of your report. It should appear in the lower left listwhen you've selected the proper directory.
2.Right-click the report you want to eliminate, then click Delete in the popup menu.
The selected report will be removed from your computer. Any associated artifacts will not be
removed, so some manual cleanup may be required if you want to remove more than just the report.
Running a Report
After you've created and saved a report, you can execute it manually at any time.
1.In the left pane, navigate to the location of your report. It should appear in the lower left listwhen you've selected the proper directory.
2.Right-click the report you want to run, then click Run in the context menu.
You can alternatively double-click on a report to run it.
3.In the Run sub-menu, select one of the following options: New Tab, New Browser Window,or Background.The options will open the report in a new tab, a new browser window (which may open in anew browser tab, depending on which Web browser you are using and how it is configured),or it will run in the background and inform you when the report is ready to view. The popupdialogue that informs you of the report's completion will ask you if and where you would like todisplay it.
The selected report will be executed, and display according to the option you selected.
Pentaho User Console 3.5 Guide 8
Creating An Analysis View
To create a new analysis view, follow this process.1.In the File menu, select New Analysis View....
This is one of several ways to create a new analysis view; all methods lead to the same screenin the Pentaho User Console.
2.Select the appropriate schema and cube from the drop-down lists.3.Click OK to continue.
A fresh analysis view will open in a new tab.
4.Click the + icons next to each column you want to drill down into.
5.To change the filter, column, and row data, click the Open OLAP Navigator button, on the leftend of the toolbar.The OLAP Navigator will appear above your analysis view.
6.To add a dynamic chart, click the Chart button toward the right side of the toolbar.
A new chart will appear below your analysis view. It will change dynamically according to thedrill-down you select.
7.When you're finished exploring your data through an analysis view, you can selecting Savefrom the File menu, or by clicking the disk icon in the left side of the toolbar.You now have an analysis view, which enables you to drill down to the minutest of details storedin your database. Using this analysis view, you can create an interactive dashboard, or share yourfindings with other Pentaho User Console users.
Pentaho User Console 3.5 Guide 9
Displaying Data in a Dashboard
Dashboard Designer enables you to create dashboards that provide users with immediate access tobusiness-related information they need. Using Dashboard Designer you can customize dashboardsto address the needs of different types of users. For example, your sales staff may want see currentleads, while your support staff may want to see a list of open issues. You can create dashboardpages that address the needs of both user groups.
Creating a dashboard is as simple as selecting a layout, theme, and the content you want to display.This content can include reports, charts, data tables, URLs, and even interactive analysis views.Dashboard Designer also provides you with the ability to add dynamic filter controls making it easyfor dashboard consumers to filter the entire contents of their dashboard using a simple pick listThe Dashboard Designer allows you to display the following content types in your dashboard:•Chart — When selected, opens the Chart Designer that allows you to create bar, line, area,pie, and dial charts for display in your dashboard.
•Data Table — When selected, allows you to display database-related content in tabularformat.
•File — When selected, opens a browser window that allows you to locate a file (.xactionor .prpt) that contains the content, usually a report or chart, you want to display in yourdashboard.
•URL — When selected, opens a dialog box that allows you to enter the URL of the Web siteyou want to display in your dashboard.
To display the content types in the Pentaho User Console, click the dashboard.
(Insert) in one of the panels in
If you are displaying a chart or data table you must first connect to the data source that containsdata you want displayed.
Creating a New Dashboard
You must be logged on to the Pentaho User Console. Follow the instructions below to create a newdashboard:
1.In the Pentaho User Console quick launch bar, click New Dashboard. Alternatively, youcan click File > New > Dashboard or click (New Dashboard) in the tool bar. The NewDashboard page appears.
2.In the edit pane (lower portion of the page), click Properties, and enter a title for yourdashboard page in the Page Title text box.The name you entered appears on the top left corner of the dashboard. This name helps youidentify the page if you want to edit, copy, or delete it later.3.Click Templates to select a dashboard layout.
A blank dashboard with the layout you selected appears.4.Click Theme to select a theme for your dashboard.The theme you selected is applied to your dashboard.
Pentaho User Console 3.5 Guide 10
5.Under Objects, select a panel (Untitled 1, 2, and so on) from the list.6.Under Title, type a label for the panel and click Apply.The label you typed appears on the top left corner of the frame.7.Repeat step 6 for each panel in your dashboard.
Now that you have established the look-and-feel of you dashboard, you are ready to startdefining the type of data you want displayed in the panels of your dashboard.
Working with the Chart Designer
The Chart Designer allows you to create bar, pie, line, dial, and area charts that can be added to adashboard. Below are the general steps associated with creating a chart:1.2.3.4.5.6.7.8.
Select a data source.Build a query.
Set the data definitions: values, series, category.Select a chart type and theme.
Enter labels for the chart title, and x,y axes.If applicable, adjust scaling and label rotation.Place your chart in the dashboard.Save your dashboard.
If you are new to charting, here are some minimal guidelines that may help you determine what typeof chart is best suited for the data you want to present in your dashboard:Bar Charts
If you want to compare items during a specific time period, consider using a bar chart. Key wordsto think about when creating a bar chart are compare or rank. For example if you want to compareitems sold to show which one made the most profit, you might create a bar chart that ranks the
products from the lowest to highest profit. The bar's length determines its ranking; the label identifiesthe item. Bar chart data can be presented horizontally or vertically depending on your requirements.
Pie Charts
If you are comparing parts of a whole, consider using a pie chart. Key words associated with chartsinclude, portion, share, and percentage. If for example, you want to demonstrate the proportion ofthe company's budget spent on health insurance, use a pie chart. To make the chart easier to readlimit the number of slices to five. Pie charts can also be exploded, which means certain slices arepulled away from the remainder of the chart for emphasis.
Pentaho User Console 3.5 Guide 11
Line Charts
Line charts are useful for showing changes over time. Key words associated with data that is bestsuited for a line chart are trend, growth, and decline. If, for example, you want to show how productsales have changed over five years, use a line chart. The slope of the line helps users quicklyidentify the direction of the trend.
Dial Charts
Dial charts are often associated with Key Performance Indicators (KPIs). Dial charts are circularand contain a scale, a needle, and one or more a dial sectors. The dial sector is used to identify aspecified area on a dial chart using a particular color. For example, you could have a dial plottinginventory with a minimum dial value of 10000 and a maximum dial value of 50000. There could bea red dial sector for the region between 2000 and 4000 indicating that if the needle is in this area,there is a danger of a supply inventory shortage.
Area Charts
Pentaho User Console 3.5 Guide 12
Area charts can be used to show a comparison of the same thing during different points in time.
Area charts are not designed to provide exact data but they do give users visual clues of the relativesizes of the items they are representing.
Adding Data to Your Chart
Before you can start creating a chart you must select a data source that contains the data you wantto use. You must then define the data that will be displayed in the chart.
Note: If you are unsure what chart type is best for displaying your data, see Choosing the CorrectChart Type on page 11 for more information.Follow the instructions below to add data to your chart:1.In the Dashboard, choose a dashboard panel and click
(Insert) and select Chart.
The Select a Data Source dialog box appears.
2.Select the data source from the list of available choices. The data source contains the contentyou want to display in your chart.The Query Editor appears.
3.The Query Editor allows you to retrieve dynamic data from a database for display in a chart.Defining your query is the first step in ensuring that the correct data is selected. In the QueryEditor, click (+) next to a category name to display its associated table columns.
4.Click the small yellow arrow to place the column name under Selected Columns. In the
example below, the Customernumber column has been selected and moved under SelectedColumns. Notice that the column names appear under Selected Columns. Continue addingcolumns as needed.
Pentaho User Console 3.5 Guide 13
5.Now add the Conditions; these are your constraints that filter what you are selecting. You canadd multiple conditions. In the example below, the search is limited to customer numbers.
Under Combine, you can select your constraint (and, or, and not, or not) from the drop-downlist. Under Comparisons you can click the drop-down list to display options for comparisons,=, <, >, and so on; (for example, where the customer number is equal to 144 or 145). You canalso select an aggregation type from the drop-down list for table columns that contain numericdata. The table below contains a definition for each aggregate type:
Aggregate TypeSUMCOUNTAVGMINDescriptionSums a column's valuesCounts a column's valuesAverages a column's valuesSelects the minimum column valuePentaho User Console 3.5 Guide 14
Aggregate TypeMAXDescriptionSelects the maximum column valueNote: For information about adding parameters in the Query Editor, see AssigningParameters in the MQL Query Editor. on page 26
Note: Click Preview at any time to view the data associated with your query.
6.Add the columns that you want to Order By. The ordering of the selected data is accomplishedby one or more columns in a table. For example, you can sort the data by customer name andaddress.
7.Click OK in the Query Editor when you are done.
The Chart Designer appears.
8.Under Data, click the drop-down arrow to display and select the table columns.
Data DefinitionValuesDescriptionThe value is always numeric. The valuedetermines the height of columns in a barchart and the height of lines in a line chart. Inarea charts, the y-axis values determine theheights of the points.Series show up as the individual columns on abar chart and as individual lines in a line chart.Area charts display each series as a point.Categories are displayed as bars or groups ofbars on the y-axis (vertical axis). In line charts,categories are usually associated with timeperiods. In area charts, the x-axis displays thecategory labels.SeriesCategoryPentaho User Console 3.5 Guide 15
A preview of the chart appears in a box in the upper-right corner of the Chart Designer as youselect your options.
9.Under Chart Type, click on a chart type to select it.
Note: By default, charts display in \"animated\" Flash mode. You can turn animation off bydisabling the Animated check box in the Chart Designer. Animated charts bring focus to
important aspects of your data. For example, animation allows you to bring out a data point if itreaches a critical value, such as high or low sales numbers.
If you selected a pie or dial chart, see Working with Pie Charts on page 17 or Workingwith Dial Charts on page 18 , respectively.10.Under Theme, select a theme from the list.
The theme is applied to your chart.
11.Enter the labels for the Chart Title, X Axis Title (horizontal axis), and Y Axis Title (vertical
axis).
Entries are displayed in the chart preview.
Pentaho User Console 3.5 Guide 16
12.Click Apply to see the chart preview.
13.Click OK display your chart in the dashboard panel.
Working with Pie Charts
A pie chart gives dashboard consumers an immediate visual clue of the relative sizes of the sharesof a whole. Categories are represented by individual slices. The size of the slice in a pie chart isdetermined by the value.
You can animate a pie chart if you want its pieces to be exploded, which means that the individualslices of the pie can be pulled away from the rest of the pie.
Pentaho User Console 3.5 Guide 17
To animate a pie, enable Animated check box in the Chart Designer.
Working with Dial Charts
For dial charts to display correctly, you must enter values for your range and the chart title. Inthe example below, the dial chart preview is displaying ranges associated with sales. Noticethat each dial sector is represented by a color, red, yellow, or green. The needle is positioned inthe 220796.48 range, indicating that total orders, while not stellar, are not near the danger zoneindicated by the red sector in the dial.
Changing Dial Sector Colors
You can change the color of a dial sector by clicking the small down arrow in the color boxes
associated with Range. Select a color from the palette so that you can preview it on your dial chart.Click Apply to preview your dial chart. Click OK to place your dial chart into the dashboard.
Pentaho User Console 3.5 Guide 18
Correcting Scaling Issues
Use the scaling feature in instances where numeric values in a chart are so long that they affect thedisplay.
1.Preview your chart in the Chart Designer.
2.Under Scale, click to display the drop-down list of scaling options. In the example below, thescaling option used is \"1000.\" Notice the change in the display of numeric values when scalingis applied. Users of the chart can see actual values when they hover over the bars in the chart.
3.To display the scaling value on the chart, enter it in the X or Y Axis Title field; for example, \"x1000.\"Note: The scaling feature is available for all chart types except pie.
Rotating Chart Axis Labels
If your chart axis labels become unreadable because they are too long, Label Rotation may correctthe problem.
1.In the Chart Designer, display your chart.
2.Under Label Rotation, select Diagonal or Vertical and click Apply.The labels display with the rotation you set.
3.Click OK to display the chart in the dashboard panel.
Editing a Chart
Follow the instructions below to edit a chart.
1.In the Dashboard Designer, select the panel that is displaying the chart you want to edit.2.
In the upper right corner of the panel click (Edit)The Chart Designer appears.3.Click Edit Query.The Query Editor opens.
Pentaho User Console 3.5 Guide 19
4.Edit the query as needed and click OK.
The Chart Designer appears.
5.Select the appropriate data definitions to build the chart.
6.If applicable, change the chart type and theme and click Apply to see a preview of the editedchart.
7.Click OK to display the chart in the dashboard.
Adding a Data Table to a Dashboard
The Data Table feature allows you to display a tabular representation of a database query in adashboard. It also allows you and consumers of the dashboard to manipulate the data in the datatable, while in the dashboard. For example, users can resize, sort, and change the order of columns.Follow the instructions below to add a data table to your dashboard.1.Select a panel in the Dashboard Designer.2.Click (Insert) and select Data Table.
The Select a Data Source dialog box appears.
3.Select a data source from the list of available data sources and click OK.Note: The data sources in the list are defined by an administrator.
The Query Editor opens.
4.Begin building your query. Click (+) next to the category name to display its associated tablecolumns. When the column names appear, click to select the column that contains the datayou want displayed in your data table.
5.Click the small yellow arrow to place the column name under Selected Columns.
6.Now add the Conditions; these are your constraints that filter what you are selecting. You canadd multiple conditions.Under Combine, you can select your constraint (and, or, and not, or not) from the drop-downlist. Under Comparisons you can click the drop-down list to display options for comparisons,=, <, >, and so on; (for example, where the customer number is equal to 144 or 145). You canalso select an aggregation type from the drop-down list for table columns that contain numericdata. The table below contains a definition for each aggregate type:
Aggregate TypeSUMCOUNTAVGMINMAXDescriptionSums a column's valuesCounts a column's valuesAverages a column's valuesSelects the minimum column valueSelects the maximum column valueNote: Click Preview at any time to view the data associated with your query.
7.Add the columns that you want to Order By. The ordering of the selected data is accomplishedby one or more columns in a table. For example, you can sort the data by customer name andaddress.
8.Click OK in the Query Editor when you are done.
Pentaho User Console 3.5 Guide 20
The Data Table appears in the dashboard panel.
Updating the Data Table Display
You can edit the data table display directly in the dashboard panel.Sorting Column Data
To change the sort order data under a column, click the drop-down arrow in the header and chooseSort Ascending or Sort Descending. You can also hide one or more columns by disabling theappropriate check boxes next to the column names.
Adjusting Column Width
You can adjust the width of a column by clicking the right border of the column header and draggingit to the right or left. Release the mouse button when you are done.Moving Columns
To move the placement of a column, click and drag the column it to the appropriate location in theData Table as shown in the example below:
Paginating
The pagination feature allows you to page through a large number of records.
Pentaho User Console 3.5 Guide 21
Displaying a Report Designer (.prpt) File in the Dashboard
Follow the instructions below to display a report created in Report Designer and in a dashboard.1.Select a panel in the Dashboard Designer.2.Click (Insert) and select File.
A browser window opens.
3.Locate the appropriate report file. Report Designer files have a .prpt extension.4.Click Select to place the report inside the dashboard panel.
Pagination control arrows at the top of a report allows you to scroll through long reports. Noticethat the report file name, NewReport.prpt, appears under Content: in the dashboard edit panein the sample below. This sample report contains parameters, (Additional Title-Text, OutputType), that do not have default values. You can enter values manually and link them to a
dashboard filter in the text boxes under Source. When the report renders again, the parametervalue(s) you entered are included in the report.
Pentaho User Console 3.5 Guide 22
Important: If you select a Report Designer file to place in a dashboard, but do not supplyvalues for required parameters, the report will show up blank.
Displaying an .xaction File in the Dashboard
Follow the instructions below to display the contents of an .xaction file in a dashboard.
Note: Generally, .xaction files are provided to you by an administrator. An .xaction file may containa report, a chart, or other type of content.
1.Select a panel in the Dashboard Designer.2.Click (Insert) and select File.
A browser window opens.
3.Locate the appropriate .xaction file.
4.Click Select to place the contents of the file inside the dashboard panel.
Notice that the file name, Sales Year/Year, appears under Content: in the dashboard editpane of the sample below. This sample chart contains parameters, (screen_res, Region), anddefault values ( large , NA).
Pentaho User Console 3.5 Guide 23
Displaying a Web site in a Dashboard
Follow the instructions below to display contents of a Web site in a dashboard panel.1.Select a panel in the Dashboard Designer.2.Click (Insert) and select URL.
The Enter Web site dialog box appears.
3.Enter the Web site URL in the text box and click OK.
In the example below, a search parameter has been added to the URL.
When the parameter displays in the edit pane of the dashboard, you can enter a value for theparameter in the text box next to the parameter name.
Pentaho User Console 3.5 Guide 24
4.
If applicable, click (Edit) to make changes.5.Save your dashboard when you are done.
Working with Parameters
If you are placing an .xaction or .prpt inside a dashboard panel, it is possible that the author ofthe .xaction or report, defined meaningful parameters for the content. If previously defined, theparameters and their associated default values, appear under Parameters in the edit pane of thedashboard. In the example below, when the chart initially rendered, it displayed a parameter called,\"chart_type\" with a default value called, \"bar.\" A user can change the value of the parameter to seethe content rendered as a pie, line, or area chart.
Parameter names are \"hard-coded,\" which means they cannot be changed. Neither can you changethe number of parameters associated with an .xaction or .prpt file. When you create a chart using
Pentaho User Console 3.5 Guide 25
the Chart Designer, embed a URL into a dashboard, or create a data table, you can change both thename and value of a parameter.
Assigning Parameters in the Query Editor
If you have set filter controls/parameters in your dashboard, you can edit them in the Query Editor.The Query Editor recognizes when you have set filter controls and displays them in a drop-down list.For example, suppose you have a filter control called \"REGION,\" you can set the default value foryour constraint to \"North.\" When the chart renders, it displays data for the North region specifically.If you haven't set filter controls in the dashboard, you can add them manually in the Query Editor.In the Value field type the name of the filter control inside curly braces, as in {Name_your_filter}.In the example below, the designer created a filter control called, {letter}; the default value for thefilter control is, \"S.\" When the chart is rendered it displays data associated with customers whoselast names start with the letter \"S.\"
Suppose the designer chooses not to display parameters and wants to limit the data to namesending with \"S,\" exclusively? In this instance, he or she would not include the curly braces aroundthe letter \"S,\" and the Default value is disabled.
Working with Filters
Filters allow you to display a subset of data based on the dashboard user's point of view. Forexample, the dashboard user's point of view of Region may be \"East;\" his or her point of view ofTime may be, \"Fourth Quarter.\" Pentaho Dashboards allows you to designate a point of view basedon the filters in your source data. Follow the instructions below to add filters to your dashboard:1.In the dashboard page, under Objects, select Filters.
The Filter Editor appears on the right. No filters are listed if this is the first time you areassigning filters.
2.To display a filter toolbar to users of the dashboard, enable Show Filter Toolbar.A placeholder for the filter toolbar appears at the top of the dashboard.3.Click
(Add) to start adding filters.
The Filter Control dialog box appears.
4.In the Filter Control dialog box, type a display name for the control label. For example,\"Region.\"
5.Enable Display Name as Control Label if you want users to see the display name in the filtertoolbar.
6.Enter an ID associated with the control label. The ID is an internal name that is not displayed.
Pentaho User Console 3.5 Guide 26
7.If you plan to provide hard-coded names and values for your dashboard users, see Creating aStatic List of Filters on page 27 . If you plan to provide a dynamic list of filters based on aSQL query, see Creating a SQL List of Filters on page 27 .
Creating a Static List of Options
When you create a static list, you define a static list of options to present to the dashboard user.Each option consists of a display name that appears in a drop-down list in the filter toolbar as shownin the sample on the right.
Follow the instructions below to create a static list:
1.In the Filter Control dialog box, under Data Origin, select Static List.2.
Click (Add).
The List Value dialog box appears.
3.In the List Value dialog box, enter the label and a value for the first option in the list. The labelis the option that is visible to the user; the value is an internal name.4.When you are done adding options, click OK.
The label is displayed to the dashboard user. In the sample screen capture above, \"East\" isthe first option on the list.
Creating an SQL List
When you create an SQL List, you are using an SQL query to dynamically retrieve a list of displaynames and corresponding values directly from a relational database, as shown in the sample screencaptures below:
Pentaho User Console 3.5 Guide 27
Note: You must have administrative permission to create SQL-based filters.Follow the instructions below to create an SQL List:
1.In the Filter Control dialog box, under Data Type, select SQL List.
2.Select the data source that contains the content you need to set options from the drop-downlist.
3.Type the SQL query in the area provided for you.
4.Click Test to ensure that your SQL query is displaying the correct values.5.Adjust your SQL query as needed and click OK.
The list of values appear in the filter toolbar in the dashboard.
Editing Filters
You can edit filters as needed. Follow the instructions below to edit filters:
1.In the dashboard page, select an existing dashboard.
2.Under Objects, click Filters to display the filters associated with the dashboard you selected.3.Select the filter you want to edit.
The Filter Control dialog box appears.
4.Make your changes as needed and click OK to update the filter-related options in thedashboard.
Saving Your Dashboard
Follow the instructions below to save your dashboard:1.
In the Pentaho User Console toolbar, click (Save) to open the Save dialog box.2.In the File Name text box, type a file name for your dashboard.
3.Enter the path to the location where you want to save your dashboard. Alternatively, use theup/down arrows or click Browse to locate the solution (content files) directory in which you willsave your dashboard.
Pentaho User Console 3.5 Guide
28
4.Click Save. See Editing Your Dashboard on page 29 .
Editing Your Dashboard
Follow the instructions below to edit an existing dashboard:
1.In the lower left corner of the Pentaho User Console, (under Files), select an existingdashboard file.The dashboard panels and their related content appear.2.Click
(Edit) in the upper-right corner of the dashboard panel you want to edit.
Note: Some content such as .xaction and .prpt files cannot be edited directly in the
dashboard. In such instances, (Edit) is disabled.3.Make your changes as needed.4.
In the toolbar, click (Save As) to open the Save As dialog box.
5.In the File Name text box, type a file name for your dashboard. Alternatively, use the up/downarrows or click Browse to locate the solution (content files) directory in which you will saveyour dashboard.6.Click Save.
The name of your dashboard file appears in the list under Files in the lower left portion of thePentaho User Console.
Pentaho User Console 3.5 Guide 29
Scheduling Reports
The Pentaho User Console has the ability to run reports at specified intervals, even if user input isrequired to properly execute the report. The sections that follow explain how to set up and configureprivate schedules.
Scheduling a Report
You can configure the Pentaho BI Server to run reports at regular intervals, or on certain dates andtimes. Follow the instructions below to create a private schedule.
1.In the left pane, navigate to the location of your report. It should appear in the lower left listwhen you've selected the proper directory.
2.Right-click the report you want to schedule, then click Schedule in the context menu.3.
The New Schedule window appears.
Type in a name to identify the schedule by in the Name field, and a group name (for groups ofschedules) in the Group field. Optionally you can also enter a description of the schedule inthe Description field.
Select an interval from the Recurrence drop-down box. If you would like to enter a custominterval, select Cron and consult the Quartz reference on page 30 to learn the Quartzsyntax.
Adjust the interval settings accordingly; these settings should be self-explanatory.Click OK to complete the schedule.
4.
5.6.
The report is scheduled and will be executed at the interval you specified, and will now be listed inMy Workspace.
Quartz Cron Attributes
The Quartz cron engine supports a seven-attribute time declaration with many possible values. Thenumber format is the same for every expression, even if the values are different -- it must be listedas seconds, minutes, hours, day of month, month, day of week, then the year. A space separateseach attribute.
Possible values for each attribute should be mostly obvious -- 0 to 59 for seconds and minutes, 0to 23 for hours, 1 to 31 for days, 1 to 12 for months, 1 to 7 for day of week, and a four-digit year.Alternatively you can use three-letter values for the day of week (MON, TUE, WED, THU, FRI, SAT,SUN), and three-letter values for the month (JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP,OCT, NOV, DEC).
An asterisk (*) indicates all values, so an asterisk in the minute field would mean that the reportruns once every minute. You can specify a range of values with the - (dash) operator, and you canspecify multiple individual values with a comma. If you need to excuse a value in the day of monthand day of week field from a cron job, you can use the question mark (?) character to indicate thatthis value doesn't matter. If you need to split values, you can do so with the slash (/) character --this operator literally means \"every,\" so */15 would mean \"Every 15.\" In the day of month field,you can use the # character to indicate a certain instance of a day of the month, for instance thesecond Friday of the month would be 6#2. Lastly, you can use a capital L in the day of month andday of week field to indicate \"Last,\" as in the last day of the week. A capital C in either of thesefields means \"Calendar,\" and combined with a number means that the report should execute a theinterval indicated by the C number according to the loaded calendar. A capital W in the day of month
Pentaho User Console 3.5 Guide 30
attribute means \"Weekday,\" which only encompasses Monday through Friday. Most of these valuescan be combined to create unusual cron schedules.
AttributeSecondsMinutesHoursDay of monthMonthDay of weekYear, - * /, - * /, - * /, - * ? / L W C, - * /, - * ? / L C #, - * /Conditionals and OperatorsExecute a report at 10:15 AM on every last friday of every month during the years 2008, 2009, and2010:
0 15 10 ? * 6L 2008-2010
Deactivating Schedules
Once you've scheduled a report, you can easily erase it without deleting the report.
1.Click the My Workspace button, which is the second one in from the right side of the toolbar.2.Click the triangle next to the Waiting section to see a list of scheduled reports.3.Click Cancel next to the schedules you'd like to remove.
The previously scheduled report will no longer run at the specified interval.
Pentaho User Console 3.5 Guide 31
Appendix: List of Pentaho BI Suite Enterprise EditionGuides
As an Enterprise Edition customer, you have access to an array of professional documentation inthe Pentaho Knowledge Base , including a collection of tech tips and articles, and the followingcomprehensive guides for users and administrators:
Document NameCustomizing the Pentaho User ConsolePurposeA short but informative developer's guide on how tocustomize the theme and graphics in the PentahoUser ConsoleAn installer's step-by-step guide to deploying theBI Suite with the preconfigured Tomcat applicationserver and a MySQL solution databasePentaho BI Suite citrus Standard Installation GuidePentaho BI Platform citrus Manual Deployment GuideAn installer's step-by-step guide to building anddeploying the BI Platform and Pentaho EnterpriseConsole for all supported solution repositorydatabases and application serversPentaho BI Suite citrus Mac Installation GuidePentaho BI Suite citrus Archive-Based InstallationGuideAn installer's step-by-step guide to installing the BISuite on a MacAn installer's step-by-step guide to installing the BISuite along with a preconfigured Tomcat applicationserver on Windows, Solaris, or Linux by using small,pre-configured ZIP or TAR archivesA comprehensive guide to the Pentaho User Console,including the Dashboard Designer pluginA sysadmin and database administrator's guide toadvanced BI Platform configuration, including securityintegration, product maintenance, and performancetuningA task-based user guide for Report DesignerAn administrator's step-by-step guide to upgradinga production system from the BI Suite version 2.x tocitrusPentaho User Console citrus GuidePentaho BI Suite citrus Administrator's GuidePentaho Report Designer citrus User GuideUpgrading to the Pentaho BI Suite Enterprise EditioncitrusPentaho User Console 3.5 Guide 32
因篇幅问题不能全部显示，请点此查看更多更全内容