Pentaho Interactive Reports
The intuitive, drag-and-drop, browser-based design environment for Interactive Reports enables you to quickly add elements to your report and format them any way you like. You do not need any special expertise to use Interactive Reports. You can also display Interactive Reports in a dashboard in the User Console.
Here is a list of the main Interactive Reports features.
Drag-and-drop report design
Font selection
Column resizing
Column sorting
Ability to rename column headers
Copy and paste functionality
Unlimited undo and redo functionality
Ability to output reports as HTML, PDF, CSV, Excel Workbook, or Excel 93-2003 Workbook files
Ability to display reports in a dashboard
Before you can create Interactive Reports, you must have access to a data source. The data source for Interactive Reports is based on the Pentaho relational data model, also known as the metadata data model. This model enables the Pentaho system to present data in reports in meaningful business terms instead of in abstract terms. For example, a table referred to as CUST_TBLE or ORDR_TBLE in the data model can be presented in your report as Customers and Orders.
In this topic
Get started
By going to the Browse Files perspective in the User Console, you can also view an editable version of the Vendor Sales report. Switch to the Browse Files perspective in the User Console, then follow these steps.
In the Folders pane, click to expand the Public folder, then click to highlight the Steel Wheels folder.
In the Files pane, click Vendor Sales. Then click Edit in File Actions.
The Opened perspective appears with the Interactive Reports and toolbars active.
Create New Interactive Report Panel
1
Opened view
Displays quick access buttons across the top to create and save new Analyzer reports, Interactive Reports, and Dashboard Designer reports. Opened reports and files show as a series of tabs across the page.
2
Data, Formatting, and General panels
Use the Data panel to drag information into a column or a row on the report. Your report display changes as you drag items onto the Report Canvas. Use Find to search for a specific field.
Delete a level or measure from your report by dragging it from the Layout panel to the trash can that appears in the lower right corner of the Report panel.
The Formatting panel allows you to change the font size and type on the opened report.
The General panel allows you to set preferences, select a paper size for printing, and select from a variety of templates for your report.
3
Report canvas
Shows a dynamic view of your Interactive report as you work to build it. The look of your report changes constantly as you work with Data, Formatting, and General panels to refine it.
4
Interactive toolbar and filters
Use the toolbar functions to undo or redo actions, hide lists of fields, add or hide filters, disable the auto-refresh function, adjust settings, change the view of your report, and limit the number of rows queried. Use the filters panel to display a list of filters applied to the active report, or edit or delete filters.
Create an Interactive Report
These steps guide you through making an Interactive Report.
From User Console Home, click Create New, then Interactive Report.
Create New Interactive Report Choose a data source for the report from the Select Data Source dialog box. Click OK.
Interactive Report Select Data Source dialog box If no data source is listed, contact your administrator.
To add your first column, click and drag a field from the Data tab in the left panel.
Drag the field over the report canvas on the right until a vertical line appears, then drop the field in place. This field becomes a column in your report.
Interactive Report Create Column Continue to drag and drop fields on to the report canvas until you have all of the columns of your report in place.
Interactive Report Add Columns You can rearrange the order of the columns by clicking on the headers and dragging the columns to the right or left of their current location. When a green line appears, you can drop the column there. You can also resize your columns by selecting the bar to the right or left of the column header and dragging it until your columns are the correct size.
Rename your report by double-clicking Untitled in the Report Canvas, then typing a name in the field that appears.
Rename a report Click Save As on the toolbar.
When the Save As dialog box appears, type a file name for your report, choose a location to save it in, and click Save.
Your new interactive report is created and saved in the location of your choice.
Edit an Interactive Report
Use these steps to edit your Interactive Report.
Log in to the User Console and click Browse Files to locate the folder containing your report.
Right-click the report name in the Files pane and choose Edit from the Folder Actions pane.
Alternatively, double-click the report name to open it, then click Edit in the User Console toolbar.
Edit Your Interactive Report Edit your report as needed.
Save your report.
Customize an Interactive Report
Use these options to fine-tune an interactive report.
You can change columns, headers, formats, page setup, and templates.
Reorder and resize columns
Drag a column header to a new location.
Or, select the column header menu. Then select Move > Left or Right.
Resize a column by dragging the header divider left or right.
Change a column header name
Double-click the column header.
Type the new name. Then press Enter.
Design report headers and footers
Double-click the upper or lower report corners.
Use the dialog box to update the header or footer:
Replace any existing text.
Insert a date and time stamp.
Select Date. Then press Enter.
Optional: Add custom text, like
Today: $(report.date).
Insert a page count.
Select #/Pages. Then press Enter.
Freeze the top row in HTML output
Select Sticky HTML headers rows on the General tab.
This freezes the header row for HTML (Single Page) output.
Format column values
Select a column.
Select the Formatting tab.
Pick the format for the column values.
Some formats come from the data source metadata. You can override them.
Copy and paste formatting
Use Copy formatting and Paste formatting on the Formatting tab.
This applies the same format to other columns.
To remove your changes, select the column. Then select Remove Formatting.
Customize page format and orientation
Interactive Reports uses Letter (8.5 x 11 inches) and Portrait by default.
Use Page Setup to change the page size or orientation.
Change page format
Select the General tab. Then select Page Setup.
In Page Setup, select a page format.
Optional: Update the margins.
Select OK.
Change page orientation
Select the General tab. Then select Page Setup.
In Page Setup, select an orientation icon.
Select OK.
Use a different report template
Select a different template from the General tab.
Select the General tab.
Use the arrows to browse templates.
Double-click a template to select it.
When prompted, select Yes or No to keep formatting.
The report updates to match the new template.
Add logos
You can add logos to interactive reports by replacing the default logo file used by report templates.
This requires access to the Pentaho Server file system. If you do not have access, contact your administrator or Pentaho Support.
Before you start
Prepare a logo image in PNG format.
Back up the existing
Logo.pngandclassic-engine.propertiesfiles.Plan for a server restart if you change alignment settings.
File locations vary by installation and OS. The examples below use <server_installation_folder> as a placeholder.
Add a custom logo to an interactive report template
In the User Console, go to Home.
Select Create New > Interactive Report.
In Select Data Source, select a data source.
Select OK.
Select the General tab.
Select Select to open the template list.
Select a template that shows the red Change Me! logo.
Templates are stored here:
Open the tenant image folder:
Rename the existing
Logo.pngto keep a backup.Example:
Logo-orig.pngRename your custom logo file to
Logo.png.Refresh the template.
Confirm the new logo renders correctly.
Change logo alignment
You can set the logo alignment to left, right, or center.
Open this folder:
Open
classic-engine.propertiesin a text editor.Find this property:
org.pentaho.reporting.engine.classic.core.environment.pirTemplateLogoAlignmentSet the value to
left,right, orcenter.The default value is
right.Restart the server.
Multi-tenancy support for logos
Each tenant can have its own Logo.png. The tenantId value is stored in the session.
The default tenant is /pentaho/tenant0. The default logo is stored here:
For multiple tenants, create one folder per tenant ID. Store that tenant’s logo in the folder as Logo.png.
Read and export in view-only mode
After you save your Interactive Reports, browse for it in your Pentaho repository. You can quickly identify Interactive Reports by their icon. When the report appears, notice that the toolbar that allows you to perform certain actions on the report.
Hover your mouse over the icons in the toolbar to see which actions you can take. These actions include:
Exporting a report as a PDF, HTML, CSV, Excel Workbook, or Excel 93 - 2003 Workbook file to be saved locally.
You can also export a report in Edit mode.
Adding filters for viewing report data.
Rearranging the report layout, such as moving, sorting, and removing columns from the report.
You cannot save any changes to your report when you are in View-only mode. However, you can click Edit in the User Console toolbar to return to edit mode and save your changes.
Build large reports
If you are building Interactive Reports with a large amount of data, you might find that it is quicker to build the report if the data is not constantly refreshing or returning a large number of rows. You can manually refresh the report to view your results at any point in the process.
When you are done designing the report, run the full data set by scheduling the report. See Schedule Reports.
You can limit how often you query the data while building your report to help reduce the amount of time required to refresh the report. Use the options in the Query Settings dialog box to adjust refresh times while you are building your report.
In the Data tab, click the Settings icon in the upper-right corner to open the Query Settings dialog box. Make changes as needed and close the dialog box when you are done.
Turn off auto-refresh
Design your report layout first, including calculations and filtering, without querying the database. Clear the Auto-Refresh option in the Query Settings dialog box.
Turn off auto-refresh mode to reduce the number of queries sent to the data source. Use it when the data source responds slowly.
When your layout is ready, turn Auto-Refresh back on. Data retrieval runs once and your report displays results.
Distinct values
By default, Interactive Reports queries data with the SQL SELECT DISTINCT statement when no GROUP BY is used. SELECT DISTINCT returns only distinct values. It can require an extensive sorting operation in the database.
To stop returning distinct values, clear Select Distinct in the Query Settings dialog box. This setting is saved with your report.
If you are an administrator, right-click the toolbar and select View SQL. Check whether DISTINCT is included.
To clear Select Distinct by default for new reports, ask your system administrator. See the Administer Pentaho Data Integration and Analytics document.
Row limits and query timeouts
Limit the number of rows displayed while designing your report. Set Row Limit options in the Interactive Toolbar.
Limit how long a query runs. Select Query Timeout in the Query Settings dialog box and specify seconds.
Row limits and timeouts help avoid out-of-memory errors. They also reduce load on the database server.
If you exceed the system maximum row limit, a help message appears.
To change the system maximum row limit, ask your system administrator. See the Administer Pentaho Data Integration and Analytics document.
Use calculated fields
You can create calculated fields from fields in the data source. You can also build calculated fields from other calculated fields.
When you create a calculated field, the field appears in the Calculated Fields list. The function determines the values.
You can add calculated fields to columns or groups. This helps you build richer reports.
Generic functions like now() or 2+5 cannot be added to an empty layout. Add at least one column or group from a data source field first.
Calculated fields do not support these options:
Filter
Prompt
Sort
Aggregation
Create a calculated field
Configure the calculated field
Set the values you need:
Display Name: Name shown in the report.
Category: Function category.
Select Fields: Fields you can insert into the formula.
Functions: Double-click a function to insert it into Formula.
For common formula functions, see the Pentaho Report Designer documentation.
Formula: The expression used to calculate values.
Data Format: Data type for the field.
Numeric
Date
Other
Description: Description of the selected function.
Return Type: The function return type.
Example:
SUM(2, 3)returns an integer type.Example:
CONCATENATE("A", "B")returns a string type.
Advanced topics
Use these topics to extend what you can do in Interactive Reports.
Group and filter data
Group data to keep related items together.
Add filters to show only the data you want.
Group data
Group a report by one or more fields. Common groupings include region, product line, or both.
Use any of these methods:
Drag a column header above the report headers. Drop it when the green line appears.
Drag a field from the Data tab in the left pane. Drop it in the space above the report headers.
Create nested groups. Drag a second field under the first group.
After grouping, sort by clicking the arrows next to each group name. You can also create advanced filters to refine results.
When you’re done, click Save or Save As. Choose a location for the report.
Add filters
Filters restrict the data in a report. They help you focus on specific values.
Example: A report shows sales by product line. A filter on Quarter can restrict results to a single quarter. Add a region filter, such as Europe, to narrow results further.
To add filters:
On the toolbar, click the plus sign (+) next to No Filters.
A filter workspace appears above the report.
From Available Fields, drag a field into the filter workspace.
The filter dialog box opens.
Select one or more values in the left list.
Click the arrow to move them into the right pane.
Click OK.
Repeat for each field you want to filter on.
Save your report.
The report updates to show only the selected values. Use Undo or Reset to revert changes.
Filter on date fields
Use date constraints to filter on a specific date.
Open the Filters panel.
Click Filters, or click (+) next to No Filters.
Drag a date field into the Filters area.
The filter dialog box opens.
Choose a date constraint.
Click the arrow to open the date picker.
To create a date range, add two filters. Use one for the start date and one for the end date.
Pick a date.
Click OK.
The report updates as soon as you add the filter. The filter appears in the Filter box. An icon also appears next to the field in the Data tab.
To change the filter, click Edit. To remove it, click Delete.
Flag a filter as a dashboard parameter
Flag a filter as a dashboard parameter. This exposes the parameter in Dashboard Designer.
Create a filter.
In Parameter Name, enter the parameter name.
Click OK.
Save your report.
The parameter name appears on the Parameters tab in Dashboard Designer. You can edit parameters while working in Report Designer.
Create advanced filters
Create filters on multiple groups of fields. You can also filter on a single group.
Filters on multiple groups are called advanced filters. Drag fields into the Filter Panel.
Place items into multiple groups by moving them up or down. Create a hierarchy by indenting fields.
Apply a summary function
Assign a function that performs summary calculations. Apply it to numeric values in columns, groups, or the entire report.
Click the down arrow next to a report column containing numeric values.
Select Summary from the menu, then choose the summary type:
Summary TypeDescriptionNone
No summary function assigned.
Average
Calculates the average value in a given column.
Count
Counts the items in a group or report, but does not require a numeric value.
Count Distinct
Counts the distinct occurrences of a certain value in a column; does not require a numeric value.
Maximum
Identifies the highest or largest value in a column.
Minimum
Identifies the lowest or smallest value in a column.
Sum
Calculates a total sum of the group or report (group level, and running total in the report footer).
Save the report.
Apply an aggregate function
Assign an aggregate function to columns that contain numeric and non-numeric values in your report. Aggregate functions return a single value calculated from the values in a column.
Click the down arrow next to a report column that contains numeric values.
Select Aggregation from the menu, then choose the aggregation type:
Function NameDescriptionNone
No aggregate function assigned
Average
Calculates the average value in a given column
Count
Counts the items in a column; does not require a numeric value
Count Distinct
Counts the distinct occurrences of a certain value in a column; does not require a numeric value
Maximum
Identifies the highest or largest value in a column
Minimum
Identifies the lowest or smallest value in a column
Sum
Calculates a running total sum of the specified column
Save the report.
Last updated
Was this helpful?