Working with Analyzer fields (archive)

Archived. Content merged into "Pentaho Analyzer".

An Analyzer report is a collection of fields and filters. It renders those fields in a chosen report format.

Think of a report like a spreadsheet file. Unlike a spreadsheet, Analyzer queries live data when you open it. It also queries live data when you change it.

Reports are stored in a repository. You can open the same report from any computer.

When you create a report, start by selecting a data source. The data source controls which fields you can use. For example, an Orders data source exposes order-related fields.

Where data comes from

Analyzer uses OLAP and multidimensional query expressions (MDX). It retrieves data from relational databases (RDBMS).

Analyzer commonly queries a data warehouse. A warehouse consolidates data from multiple source systems. It optimizes that data for reporting and analysis.

Pentaho Data Integration (PDI) is often used to build warehouses. It also populates data models for reporting. PDI loads from databases, apps, spreadsheets, and public data.

Your administrator usually builds and maintains data models.

Fields in Analyzer

Examples include Sales Revenue, Profit Margin, Product Name, Region Name, and Fiscal Year. Fields define what your report shows.

Types of fields

Analyzer exposes three field types. They map to how data is stored and aggregated.

• Levels

  • Text-based dimensions.

  • Example: Product Name.

  • Level values are often called members.

• Time period fields

  • A specialized level type used in most reports.

  • Examples: Fiscal Year, Order Month.

• Measures

  • Numeric metrics.

  • Examples: Sales Revenue, Profit Margin.

Fields are color-coded in the report and the Layout panel:

• Levels and time periods use a yellow background.

• Measures use a blue background.

Field hierarchies

Some level fields belong to a hierarchy. Hierarchies define drill paths.

Examples:

• Product Line → Product Name

• Year → Quarter → Month → Week → Day

Hierarchies help in two ways.

They make drilling faster:

• Right-click a level name, then select Also Show.

• Right-click a value, then select a drill option.

  • Example: Keep Only 2007 And Show Quarters.

• If multiple hierarchy levels are in the report, you can drill up or down.

  • Example: Drill up to Year or Drill Down to Month.

They also reduce filter value lists:

• If you filter Product Line = Snow Sports, then Product Name values narrow.

Fields from the same hierarchy must be on the same axis. Analyzer enforces this when you arrange fields.

View details about a field

You can inspect a field from the Layout panel or the report. You can also inspect it from the Available Fields list.

View the definition of a field (“Tell me about”)

Use Tell me about to see how a field was defined.

1. In the Layout panel or the report, right-click the field name.

2. Click Tell me about.

3. Review the details.

You typically see:

• Display Name

  • The name shown in Available Fields and the report.

  • If you renamed it in the report, you also see the original name.

• Type

  • Level, time, or measure.

• Description

  • If provided in the data model.

• MDX

  • The underlying MDX expression.

• Member Properties

  • If the field has member properties (for example, Customer(6)).

View and edit field properties (“Properties”)

Most users can view field properties. Only users with Manage Data Sources can edit some properties.

For permission setup, see Manage Users and Roles in the User Console.

1. In Available Fields, right-click a field.

2. Click Properties.

3. View or edit the available properties.

Common properties:

• Display Name

  • Editable with Manage Data Sources.

  • Report-only renames do not change the list display name.

  • See Rename a field.

• Aggregation (measures only)

  • Determines how values roll up.

  • Common options include SUM, AVERAGE, COUNT, and COUNT_DISTINCT.

• Format

  • Controls number and date formatting.

  • See Format field options.

• Description, Type, MDX

  • Read-only.

• Member Properties

  • Read-only list of member properties, if present.

View a level with member properties in a report

If a dimension shows a number in parentheses, it has member properties. Example: Customer (6).

1. In Available Fields, locate a dimension with a number in parentheses.

2. In the report, right-click that dimension header.

3. Click Show Properties.

4. Select or clear properties, then click OK.

Editing measure properties

Changing measure properties updates the data source. It can affect all users of that source.

You need Manage Data Sources to change measure properties.

Work with the Available Fields list

You can organize, search, and add fields from Available Fields.

Change how fields are grouped and sorted

1. In Available Fields, click View.

2. Select a sort option:

   • By Category (default). Set by an administrator.

   • By Type. Measures first, then levels.

   • A to Z. Alphabetical, no grouping.

   • By Schema. Uses cube schema grouping.

Find a field

In Available Fields, type part of the field name in Find.

Add a field to a report

You can add fields using several methods:

• Drag a field into the report.

• Drag a field into a drop zone in the Layout panel.

• Right-click a field, then click Add to Report.

• Double-click a field.

Rename a field

Renaming affects only the current report. It does not change the data model.

1. In the report, right-click the field you want to rename.

2. Click Edit or Column Name and Format.

3. In Name, enter the new name.

4. Optional: set Plural Name used within this report.

5. Click OK.

To remove a rename, open the edit dialog. Clear the name fields.

Move fields in a report

Sometimes you need to rearrange fields.

• In a table report, drag the field to a new location.

• In chart mode:

1. Open the Layout panel.

2. Drag fields within and between drop zones.

Move measures only within measure zones. Move levels only within level zones.

Remove fields in a report

Do one of the following:

1. Drag the field name to the trash area.

2. Right-click the field name, then click Remove from Report.

Hide and unhide fields

Hidden fields do not appear in Available Fields. They are not available for report design.

You need Manage Data Sources to hide fields. For permission setup, see Manage Users and Roles in the User Console.

The field count at the top of Available Fields depends on permissions:

• With Manage Data Sources, you see all fields.

  • This includes hidden fields and model-added measures.

• Without it, you see only visible fields.

Hide a field

1. In Available Fields, hover over a field.

2. Click the down arrow.

3. Click Set Hidden.

If the field is used in reports, it is removed from those reports. Use Undo to restore it before saving. After saving, you must unhide the field to use it again.

Hidden fields can also affect drill-through. See Set Analyzer report options.

View hidden fields

1. In Available Fields, click the down arrow next to View.

2. Click Show Hidden Fields.

Hidden fields appear in gray.

To hide them again, clear Show Hidden Fields.

Hidden measures can be used in calculated measure formulas. You still need Manage Data Sources to view them.

Unhide a field

1. Enable Show Hidden Fields.

2. Hover over the gray field, then click the down arrow.

3. Clear Set Hidden.

Format and units of measure

Editing units of measure in a report

Use units when you work with large numbers.

1. In the report, right-click a measure header.

2. Click Edit.

3. In Format, select Currency or General Number.

4. In Units, select the unit of measure.

5. Click OK.

Format field options

The Format property supports numeric and date formats. It applies to numbers and calendar dates.

For more details on format strings, see MDX format definitions.

Common numeric formats:

Format String	Result
0	12345
0.00	12345.09
#,##0	12, 345
#,###.00	12, 345.09
-#,###.00	-12, 345.09
(#,###.00)	(12, 345.09)
$ #,##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 %
#E+#	1E+4
0.00E+00	1.23E+04
##0.0E+0	1.2E+4

Common date and time formats:

Format String	Example
M/d	4/1
M/d/yy	4/1/16
MM/dd/yy	04/01/16
d-MMM	1-Apr
d-MMM-yy	1-Apr-16
MMM-yy	Apr-16
MMMMM-yy	April-16
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
M/d/yyyy	4/1/2016
d-MMM-yyyy	1-Apr-2016
h:mm	20:09
h:mm AM/PM	8:09 PM
h:mm:ss	20:09:06
h:mm:ss AM/PM	8:09:06 PM
\[h]:mm:ss	\[20]:09:06

Managing fields in large reports

Large reports can be truncated. This is most common with fields that have many values.

Truncated table reports behave differently:

• The status bar shows displayed rows and columns versus total.

• Analyzer cuts cells until the total is at most 2000.

  • Your administrator can raise this limit.

• Rows are cut first, down to at least 10 rows.

• Columns are cut after rows.

• Subtotals and grand totals do not display.

• A message at the end of the report indicates truncation.

Charts also have plot point limits. The limit depends on the chart type. You can change it in Chart Options.

Troubleshooting: Your report does not display data

In some cases, a report returns no data. These are common causes and fixes.

What you did	What happened	Likely reason	Example	Solution
You added or modified a filter.	The report returned blank.	The filter is too restrictive.	The filter includes year 1997, but you have revenue only for 2005.	Loosen filters, or show rows/columns where the number cell is blank.
You added a new number field.	The report returned blank.	The field has no values.	You added Quota, but no quota data exists.	Ask your administrator to load data or hide the field.
You added a new text field. You have no number fields.	The report returned blank.	Analyzer sometimes needs a measure to correlate level fields.	You added Account Name and Order Status.	Add a number field.