Working with Analyzer fields
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.
Fields in Analyzer
Examples of fields include 'Sales Revenue', 'Profit Margin' ,'Product Name', 'Region Name', and 'Fiscal Year'. Fields are what define the content of your report.
Types of fields in Analyzer
The following types of fields are available:
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.
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 panel. 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:
- When you click on a level field on the report, such as Fiscal Quarter, and then click Also Show from the context menu, all these fields will be available for selection if the field is part of a hierarchy.
- When you 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.
- When you click on a level field value, such as 2007, and both the Year and Quarter are in the report, the context menu displays the Drill up to Year option. When you click the 2007-Q1 value, the context menu displays the Drill Down to Month option.
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.
View the definition of a field
In the Layout panel or the report, right-click the field name.
Click Tell me about from the menu.The About dialog box displays for the field.
View the following information about the field:
Field property Description 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 panel and the Report pane unless you have renamed the field. Renaming a field within the Layout panel or the report pane will not affect the display name of the field in the Available Fields list.
Type 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 appears. Check or clear the member property boxes to add or remove them from the report.
Viewing and editing field properties
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.
View and edit field properties
In the Available Fields list, right-click the field name you want to view or edit.
From the menu that displays, click Properties.The Properties dialog box displays for the field.
View or edit the following information about the field:
Field property Description Display Name
The name of the field as it appears in the Available Fields list. If you renamed this field in the Layout panel 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 panel and the Report pane unless you have renamed the field. Renaming a field within the Layout panel 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. Type 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.
View a level with member properties in a report
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.
Rename a field
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.
(Optional) 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.
Working with the Available Fields list
You can work with the Available Fields list in several ways. You can organize the list with sorting options, find a field using the Find box, and add fields to the list.
View the List of Available fields
Click the View button at the top of the pane.
Select one of the following sort options for organizing the list of fields:
By Category (default)
This grouping is set by an administrator.
Lets you see the list where all measure fields come first, followed by level fields.
A to Z
Alphabetical order with no grouping.
This displays the grouping as defined by the administrator in the cube’s underlying schema.
Add a field to a report
You can add fields from the Available Fields list.
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.
Editing units of measure in a report
To add or edit a unit of measure, perform the following steps:
In your report, right-click the column header of the measure you want to modify, such as Quantity or Sales.
From the menu that appears, select Edit. The Edit Column dialog box opens.
In the Format field, select Currency or General Number.
In the Units field, select the unit of measure. .The Units field is only available when the format is set to Currency or General Number
Click OK to save your changes.
Move fields in a report
- In a table report, the easiest method is to simply drag the field to a new location.
- In chart mode, do the following:
Open the Layout panel.
Select and drag fields within and between the three different drop areas.NoteYou can only move a field within zones of the same type: blue for measures and yellow for levels/time periods.
Remove Fields in a Report
Select the name of the field you want to remove (a trash can appears) and drag it to the lower-right corner of the report or into the Available Fields list.
(Optional): Right-click on the name of the field you want to remove, and then select Remove from Report from the menu.
Hide and unhide fields
You can select to hide or show fields in the list of Available Fields for a report. Hiding fields is helpful when you want a clear view of only those fields you are interested in for your report.
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.
|$ #,##0||$ 12, 345|
|$ #,##0.00||$ 12, 345.09|
|$ -#,##0.00||$ -12, 345.09|
|$ (#,##0.00)||$ (12, 345.09)|
|$ #,##0.00;(#,##0.00)||$ 12, 345.09|
|0 %||1234509 %|
|0.00 %||1234509.00 %|
|MMMMM d, yyyy||April 1, 2016|
|M/d/yy h:mm AM/PM||4/1/2016 8:09 PM|
|M/d/yy h:mm||4/1/2016 20:09|
|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 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. Note that this limit can be increased by your 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.|