Using Fields in Analyzer Reports
An Analyzer report is a collection of fields and filters that is displayed in a specific report format. You can think of a report as a file (like a spreadsheet file), except that when you open a report or make a change, the report connects to your database so it displays the latest data. Reports are stored in a repository, so you can access reports from any computer.
When you create a new Analyzer report from the ground up, you select a data source first. The data source determines which fields will be available when you build your report. For example, if you choose orders as your analysis area, you most likely will see all fields related to orders.
Where Does the Data Come From?
Pentaho Analyzer leverages OLAP technology and multi-dimensional query expressions (MDX) to dynamically retrieve data from relational databases (RDBMS). Analyzer is most often used to query data in an organization’s data warehouse, which generally consolidates data from multiple source systems into a common place for information analysis and reporting.
Pentaho Data Integration (PDI) is a popular tool used for building and populating data warehouses and usable data models. PDI can load data from applications, databases, and spreadsheets within your company, as well as from external and public data sources. Tools like PDI are typically managed by your system administrator.
Types of Fields
Examples of fields include 'Sales Revenue', 'Profit Margin' ,'Product Name', 'Region Name', and 'Fiscal Year'. Fields are what define the content of your report. The following types of fields are available:
Levels. Fields, such as Names, Types, and Categories are most often text-based. For example, if you were working for an athletic equipment vendor, you would use 'Product Name' Level in your reports. In this level, you might have 'Snow Sports' and 'Cycling' as possible values for the Product Name field. These individual values are often referred to as members of that level.
Time Period Fields: While Time fields are technically Level fields as well, Time fields are critical to nearly all reports and are often regarded as their own category of fields. Time period fields such as 'Fiscal Year' and 'Order Month' are commonly used in reports. Possible values for those fields could be '2004' and 'Jan-2006', respectively.
Measures: Measure fields are numeric and most often represent business metrics. These types of fields are designed for mathematical activities such as summing, dividing, and creating averages. 'Sales Revenue' and 'Profit Margin' are examples of measures.
In Analyzer reports, fields are color-coded by type in both the report and the Layout pane. The colors are assigned as follows:
- Levels including Time Period fields are defaulted with a yellow background.
- Measures are defaulted with a blue background.
You can create a report without any knowledge of field types, but knowing how field types work can sometimes help you understand how different charts display data and how filters work together.
About Field Hierarchies
Some level fields (time periods, names, types, categories, etc.) belong to field hierarchies. Here are two examples of field hierarchies:
- Product Line >>Product Name
- Year >>Quarter >>Month >> Week >> Day
The field hierarchies help you in two primary ways. First, it provides a quick and easy way to drill into more details on a report:
- Click on a level field on the report, such as 'Fiscal Quarter', and then click Also Show from the context menu. If the field is part of a hierarchy, all these fields will be available for selection.
- Click on a level field value on the report, such as the year '2007'. The context menu displays the option Keep Only 2007 And Show Quarters.
Second, when creating a filter, field hierarchies narrow down the list of available values. For example, if you have a filter Product Line='Snow Sports', then the list of possible choices when you filter Product Names are limited to the products that are part of the Snow Sports product line.
Additionally, field hierarchies sometimes control how fields are placed on the report. For example, fields from the same field hierarchy need to be placed on the same axis (row/column) and the report will automatically enforce this rule as you move and arrange your fields.
Viewing the Definition of a Field
You can view the definition of a level or measure field from the Layout panel and the report in Analyzer. The definition includes those attributes which defined the field when the data model was built.
- In the Layout Panel or the report, right-click the field name, then click Tell me about... from the menu. The About dialog box displays for the field. You can view the following information about the field:
|Display Name|| |
The name of the field as it appears in the Available Fields list and your report. If you renamed this field in the report, a notification with the original name will display below.
If you are assigned the 'Manage Data Sources' permission, you can edit the name for this field. The edited name will display in the Available Fields list, as well as in the Layout pane and the Report pane unless you have renamed the field. Renaming a field within the Layout pane or the report pane will not affect the display name of the field in the Available Fields list.
The type of field, such as level, time, or measure.
|Description||The description of the field, if any.|
|MDX||The formula for the level or field as an MDX statement.|
|Member Properties|| |
If a field has a number in parenthesis next to it in the Available fields list, such as 'Customer(6)', that means that the dimension has member properties associated with it. When you open the About dialog box, you will also see a list of the Member Properties in addition to the other details about the field.
If you open the field layout, you can see your dimensions in either the Row Labels or Col Headers fields, depending on how you have them oriented. To constrain a dimension by controlling its member properties, right-click on a dimension in the row label or column header fields, then select Show Properties from the context menu. A sub-menu with all available member properties will appear. Check or un-check the member property boxes to add or remove them from the report.
Viewing and Editing the Properties of a Field
You can view the properties of a level or measure field from the Available Fields list in Analyzer. The properties include those attributes which defined the field when the data model was built.
While most users can view the properties of a field in Analyzer, select users can edit a few of these properties, depending on the type of the field, such as level, time period, or measure. To edit these properties, you must be assigned the 'Manage Data Sources' operation permission in Users and Roles. For more information on setting and maintaining permissions and roles for users, see Use Pentaho Security.
- In the Available Fields list, right-click the field name, then click Properties from the menu. The Properties dialog box displays for the field. You can view or edit the following information about the field:
|Display Name|| |
The name of the field as it appears in the Available Fields list. If you renamed this field in the Layout pane or the report pane, a notification with the new name will display below.
If you are assigned the 'Manage Data Sources' permission, you can edit the name of this field. The edited name will display in the Available Fields list, as well as in the Layout pane and the Report pane unless you have renamed the field. Renaming a field within the Layout pane or the report pane will not affect the display name of the field in the Available Fields list.
|Aggregation (for measures only)||The aggregation type is how the measure combines the data. Use the drop-down list to select an aggregation type from a system-defined list. Options include: |
Choose how this level or measure should be formatted, such as currency, general number, percentage, or date. Use the drop-down arrow to select a format from a system-defined list, or type in the field to enter a custom format. Note that the Format field only displays when the value for the field is a number or a date.
See Format Field Options for more information on selecting the appropriate format for your report.
|Description||The description of the field, if any. This field is always read-only.|
The type of field, such as level, time, or measure. This field is always read-only.
|MDX||The formula for the level or field as an MDX statement. This field is always read-only.|
|Member Properties|| |
If a field has a number in parenthesis next to it in the Available fields list, such as 'Customer(6)', then the dimension has member properties associated with it. When you open the Properties dialog box, you will also see a list of the member properties in addition to other details about the field. This field is always read-only.
Viewing a Level with Member Properties in a Report
When a number in parentheses appears next to a dimension in a list of Available fields, that dimension is associated with specific member properties. You can use these properties to constrain dimensions.
- Locate a dimension in the Available fields list which includes a number in parentheses, such as 'Customer (6)' and 'Product (3)'.
- Locate the corresponding dimension on your report. Right-click the row or column header for that dimension, then click Show Properties. A menu displays the member properties you can choose to appear in the report.
- Select or clear the member property you want in the report, then click OK.
Editing Measure Properties
When you update the properties on measures in Analyzer, including calculated measures, you are making a change to the data source which will affect all users who are creating reports based on that data source. Such changes require users to be assigned the 'Manage Data Sources' operation permission in Users and Roles. For more detailed information on viewing and editing properties for both base measures and calculated measure, see Updating Measure Properties.
Renaming a Field
To rename a field within a report, do the following:
- Right-click the field you want to rename in the report.
- Select Edit or Column Name and Format from the menu to open the Edit dialog box for that field.
- Enter the new name in the Name field. Note that you can also view the original name of the field in this dialog box.
- Optionally, enter the plural version of the new name (if applicable) in the Plural Name used within this report field. (Plural versions of a field name are useful because the Pentaho interface often uses field names in menus and dialog boxes. If you enter a plural version of the new field name, it automatically will be used in situations where the plural form is grammatically correct.)
- Click the OK button to save the new field name.
Renaming the field only effects the contents of the report. A user can view the original name of the field by viewing the definition of the field.
To remove a modified (renamed) field name, open the Edit dialog box and delete the existing entries using the Backspace key.
Viewing the List of Available Fields
You may organize the list in four ways:
- By Category (default). This grouping is set by an administrator.
- By Type. Lets you see the list where all measure fields come first, followed by level fields.
- A->Z. Alphabetical order with no grouping.
- By Schema. This displays the grouping as defined by the administrator in the cube’s underlying schema.
To change the organization, click the View button at the top of the pane.
Finding a Field
To find a field, on the Available fields list, type the first characters of the field name in the Find box.
Adding a Field to a Report
From the Available fields list, you can add fields to a report using the following methods.
- Select a field, and drag it into the Report pane. A visual indicator (black line) lets you place the field where you want it.
- Select a field and drag it to a drop area in the Layout Panel. Note the visual indicator when you drag a field over a valid drop area.
- Right-click a field and select Add to Report.
- Double-click a field.
Moving Fields in a Report
Sometimes you need to re-arrange the fields which are already in your report. In a table report, the easiest method is to simply drag the field to a new location. In chart mode, open the Layout pane.
Option 1: For table reports, drag the field header to the new location. Note that measure fields (blue) will always be placed on the right side.
Option 2: To move fields using the Layout pane, do the following:
- Select and drag fields within and between the three different drop areas. You can only move a field within zones of the same type: blue for measures and yellow for levels/time periods.
Removing a Field
Complete one of the following actions to remove a field from a report:
Option 1: Drag the field to the lower-right corner (a trash can will appear) or into the Available Fields area.
Option 2: Right-click on the name of the field you want to remove, and then select Remove from Report from the menu.
Format Field Options
The Format field for Properties allows users to select values based on numerals and calendar dates. Below is a list of supported numeric and date formats you can select for the field or measure.
For more detailed information about numeric and date format strings, view this article about MDX and format definitions.
Numeric fields using '12345.09' as the value:
$ 12, 345
|$ #,##0.00||$ 12, 345.09|
|$ -12, 345.09|
$ (12, 345.09)
|$ #,##0.00;(#,##0.00)|| |
$ 12, 345.09
|0.00 %||1234509.00 %|
Date fields using 'April 1, 2016, 8:09:06 PM' as the value:
|MMMMM d, yyyy||April 1, 2016|
|M/d/yy h:mm AM/PM||4/1/2016 8:09 PM|
|h:mm AM/PM||8:09 PM|
|h:mm:ss AM/PM||8:09:06 PM|
Managing Fields in Large Reports
You can add fields that have an arbitrary number of values, but large reports will be truncated. Truncated table reports differ from full reports in the following three ways:
- The Report Status Bar displays the number of rows/columns shown versus the number of rows/columns in the full report. Cells will be cut until the number of cells is less than or equal to 2000. (This limit can be increased by the administrator.) Rows are cut first, down to a minimum of 10 rows, followed by columns. This technique ensures that you still generate a useful sample of the row values despite the truncation.
- Subtotals and Grand Totals do not display in truncated reports.
- A message at the end of the report informs you of the truncation. Note that the data in the cells does not change because of the truncation.
For charts, there is a maximum value of plot points which can be displayed on any axis. This limit is different depending on the type of chart and based on the amount of data which can reasonably fit on a screen. You can change this limit in Chart Options.
Troubleshooting: Your Report Does Not Display Data
In some situations, your report might not display any data. The table below outlines the most likely scenarios and their solutions.
|What you did||What happened||Likely Reason||Example||Solution|
|You added or modified a filter||The report returned blank.||The filter(s) you added are too restrictive.||Your filter only includes the year '1997' but you have sales revenue only for '2005'.||Change your filters or change the report options to show rows or columns where the number cell is blank.|
|You added a new number field.||The report returned blank.||There are no values for the number field(s) that in the report.||You added the "Quota" field but you have not yet loaded any Quota data into Pentaho.||Contact your administrator to: 1) get data loaded into this field OR 2) hide this field.|
|You added a new text field. You have no number fields on the report.||The report returned blank.||You have two or more text fields on the report but in some cases Pentaho Analyzer needs a number field to tie it all together.||You have Account Name and Order Status on the report||Add a number field.|