Report Designer (Classic)

The Pentaho Report Designer creates complete reports, which are stored in a .report file. This file can be self-sufficient: it can hold information on layout, database connections and queries. For basic reporting, this might be enough, and you won't need the rest of the Pentaho platform.

For more advanced reporting, you will need to export the report to a form that can be used by the Pentaho server. This can be done by clicking File > Publish.... The output format is a .xml file. This file only stores visual layout information and references to table entries, but the database connections and queries are left up to the Pentaho server.

When you publish, you have the option of making a .xaction file as well. This file is used by the Pentaho server to run the report. It contains the actions to run on the report, including the database connections, queries, prompts and many other elements that cannot be expressed in the Report Designer on its own. If you have an existing .xaction file, you can use that instead. In that case, don't save a new .xaction file. You will need to tell your existing .xaction file to use the .xml file (see 'Call a report from an xaction file', below).

When the Pentaho server executes the .xaction file, it passes on a query to the report for display. The report .xml file does no processing on its own.

Below are some quick howtos so that you may quickly become productive with Report Designer.

General design tips

• use padding to space out text — this is much neater than manually leaving spaces between elements
• Make the fields in the different bands the same heights - that way when you export to excel all of the data for a single row will be in the same row in the XLS file
• use Subbands to link together related elements
• use a No Data Band so that something is displayed when there is no data
• you may have multiple data sources, but the first one must be named 'default'
• "grouping" bar charts. If you have more than one bar in one chart, e.g. actual and budget bars per month and you like bars are closer in each month. What you can do is add empty items in Values columns and Series Names. Shortage of the solution is the legend looks funny with empty items. However, we can design a legend pretty straightforward.

Positioning elements

Create guide lines by clicking on the rulers on the top and left of the report canvas. You can more finely tune the position of a guide line by right clicking its handle on the ruler and selecting Properties. Elements will snap to the lines as you drag them near. This is a good way to keep elements in-line.

Fonts and typefaces

For best results when designing reports, select fonts or font families that are available on the computer generating the report. For example, report elements in "Arial" will display as such if the report is called in HTML format on a Windows system. Other systems that don't have this typeface installed will interpret it differently. PDF versions are generated on the server, so if the server doesn't have that font installed (e.g. if the server isn't running Windows) then the report might not look like what you would expect.

The default "Dialog" is a decent default, being translated well on different platforms.

Other typefaces you might want to try include:

• the default Java fonts
• Liberation
• Libertine

Call a report from an xaction file

When you export a report from the Report Designer, you can have an accompanying xaction file created alongside. If you want to do it manually, follow these steps:

1. Open the xaction file in the Design Studio
2. In the Define Process tab, right-click in Process Actions, then click Add > Report > Pentaho Report
3. 'Pentaho Report' will now show in Process Actions. Select it to edit.
4. In Report Specification, point to the XML report file.
5. In Report Data, type/select the output result (defined in previous process actions). Often this will be <query_result>
6. In Output Report Name, type an appropriate name.
7. In Process Actions, drag that name down to the Process Outputs section.

Link to an image

1. save your image as <PentahoDir>/jboss/server/default/deploy/<stylename>.war/<image>
2. access it as http://localhost:8080/<stylename>/<image>

Note: Overlapping images may not display in the final report. Report Designer might not warn you of this.

Link to a dynamic URL

Elements in the Report Designer can be turned into dynamic hyperlinks, based on input from the xaction file.

1. Make sure that the xaction file is passing the required information to be inserted into the URLs.
2. In the Report Designer, right-click the element and choose 'Create Hyperlink...'
3. Press the '[...]' button next to Fields
4. Add the fields of data that you need (these are passed from the xaction). Pay attention to their numbering (order them as required).
5. Click OK to go back to the Create Hyperlink box. Paste the URL you need into the URL field.
6. Make the static parts in the URL dynamic by replacing them with wildcards. The wildcards correspond with the Fields you just set up. They look like {0}, {1}, {2} and so on.

Using message fields

Message Fields let you mix dynamic and static text, sort of like a combined Label and Text Field. Here's an example:

The date is $(report.date, date, d MMMM yyyy)

This will show in the report as:

The date is 6 November 2008

The first part ("The date is") is static. The variable afterwards has three sections:

1. The field to use. Here we are using the internal function 'report.date' to give the current date, but you could also use a field from the database query.
2. The data type. This data is of type 'date'.
3. The format in which to display the data.

References

Pentaho wiki

Add page numbering

1. In the Structure pane, select Report Functions
2. In the Properties pane, select Available Functions > Misc > PageOfPagesFunction
3. Click the 'Add Function to Report' button below
4. You should now see PageOfPagesFunction under Report Functions in the Structure pane. Name it 'page#'
5. Place a text field in the footer and name it 'page#' (the same name as the function)

Add the current date

Here are a few ways in which you can add the current date to a report.

Method 1

See http://forums.pentaho.org/showthread.php?t=27175 for more information.

To make the report show the date and time of generation:

1. In the Structure pane, right-click Data Sets and choose Add Properties Data Set
2. Add a new property with Name 'report.date' and Type 'date'. The Value should reflect the current date.
3. Add a text field with Field Name 'report.date'

Method 2

1. Add a Message Field to the report.
2. Set the Format String, e.g. $(report.date, date, yyyy-MM-dd)

Method 3

1. Add a date field to the report
2. Set the Format as appropriate (e.g. dd/MM/yy)
3. In Formula, click the [...] button to get a list of choices (e.g. for today's date, put "=TODAY();" as the formula)

Method 4

You can use SQL date and time functions, such as CURRENT_DATE and NOW(). Here's a list of the functions available in MySQL 5.1. Because these are functions and not actual dates, remember that these are incompatible with the {PREPARE} statement in action sequences.

Select a single value in a query

Method 1

The best way to do this is to use the SingleValueQueryFunction, available in Report Designer 1.7 and above. It launches a parameterised query using a defined data source and returns a single value.

Here are some resources to assist:

• How many subreports is too many?
• Can someone give an example of singleValueQueryFunction please?
• Class SingleValueQueryFunction

Method 2

In the Design Studio, create an xaction and add a query that delivers a single result. Give it an appropriate Result Set Name. Then create a JavaScript process action. Add the result set you just created as a Script Input. Here's a sample piece of Javascript code that you can use:

var Result_Set_new;
Result_Set_new='{Result_Set}';

Add a Pentaho Report process action. In the Report Parameters, add the variable you just created in the JavaScript (Result_Set_new in this example). This will allow that variable to be exposed to the report.

In Report Designer, right-click Data Sources to add a Properties data source. Give the new property the same name as the JavaScript variable and set the appropriate data type. Then you can refer to this value in the report template just as you would any other database field.

Quoting numbers from a query

Report Designer 1.6 has a bug that automatically adds commas in numbers as thousands separators. To work around this, convert the numbers in the SQL query to a text string.

e.g. change

select taskid

select cast(taskid as text) as taskidp

Hide an element based on the existence of a value

You might not want to display an element if it has no value.

1. In the Structure pane, select Report Functions, and add Misc > HideNullValuesFunction to the report
2. Select the newly-added function to change its Properties.
3. Click the [...] button next to Element, and pick the element to apply this function to.
4. Click the [...] button next to Field, and pick a field. The Element will be hidden if the value in this field is null, has zero length or is the number zero.
5. Give the function an appropriate name.

Style expressions

Style expressions allow you to alter the look or behaviour of an element.

• Style Expressions 101
• The complete reference to StyleKeys and Style-Expressions

Change the digits format of y axes in bar line chart.

The reason we need change the digits format of y axes is when the highest number of y axes is less then 1. Pentaho report will show the scales with a very long odd digits, e.g. 0.7 is 0.7000000000001. I guess this is a bug of the report designer, because our input figures has only one digit after dot. Furthermore, it only happens only in bar line chart, but only in bar chart or line chart.

Solution is

• set the y1 format as 0.0 for the property called Bar Ticks label Format.
• Similarly, for the y2 axis of the line chart, set the format as 0.0 for Line Ticks label format

Creating Links With Target "_BLANK"

We can use style expressions to add links to fields. Click on the field that you want to add link to and add style expressions under 'Preferences'

• 'href-target' - allows to define where the link should be pointing to. Use parameters to dynamically define links. For example, to use the value of the field picture_id in the link, use the following syntax:

report: SUBSTITUTE("http://images.google.com/images?q={0}"; "{0}"; URLENCODE([picture_id]))

• 'href-html-window' allows to define where the link should be opened. To open the link in a new window set its value to

  ="_BLANK"

Formatting numbers - how to display negatives in brakets

To add format to your number fields, use the "Format" option. Formats can be defined following these examples:

#,###.##

#,##0.##

#,##0.00

An # means that zeros will not be displayed (e.g. 0.5 will become .5). "0" will force displaying zeros (e.g. 5 will become 5.00)

Sometimes, e.g. in accounting, it is a requirement to display negative numbers in brakets: So -10 will be displayed as (10). To achieve this, define the format as following:

#,###.##;(#,###.##)

#,##0.##;(#,##0.##)

#,##0.00;(#,##0.00)

Other how-tos

These have not been written by BizCubed.

• Dynamically Generating a Cross Tab Report (Part 1 | Part 2)