Working with rows where the number cell is blank

Archived. Content merged into "Set Analyzer report options".

This page is archived. See the current guidance in Set Analyzer report options.

Blank cells are values in your report that are null or empty. You can control how Analyzer displays blank values. You can also control when Analyzer shows rows or columns with blanks.

Report Options dialog box

Set blank measure display

Analyzer reports break down number fields (like Sales) by text fields (like Product Name). If a product did not sell, the report may show 0, a blank, or a dash (-).

Blank and zero behave differently in calculations. For example, averages include zeros but exclude blanks.

To change how blank measures display:

• In the Blank Cells section, set Blank measures display as.

Show rows or columns when measures are blank

Analyzer hides rows and columns when all measures in the row or column are blank. This default gives the best performance.

If you have calculated measures that return values without base measure data, you can show more rows or columns. For example, YTD Sales for a Month can exist even when monthly sales are blank.

To change when Analyzer shows rows or columns:

• Select an option in Show rows or columns with.

The options are:

• Measure data

  Shows combinations with measure data defined by relationships in the database. This option is used most often.

• Measure and calculated measure data

  Shows Measure data combinations plus additional combinations created by MDX calculations. Use this option with Measure data.

• Show all even blank measures

  Shows all attribute combinations as a Cartesian join. Use this option to verify dimension combinations without fact data. For example, show Product Line and Sales Territory combinations with no sales.

If you use Measure and calculated measure data or Show all even blank measures, Analyzer computes cross-joins in memory. Processing increases significantly. Changing these options prompts a performance warning.

Blank cell options and performance warning

This property can be enabled or disabled by an administrator. See the Administer Pentaho Data Integration and Analytics document for details.

What happens when there is no number field on the report?

The following rules apply:

• If there is only one text field (for example, Product Line) and no number field, Analyzer displays all values. For example, it shows product lines even when they have no data.

• If there is more than one text field (for example, Product Line and Region) and no number field, Analyzer may hide some values. This can happen when you show rows or columns with measure and/or calculated measure data. Do not draw conclusions in this state. Add a number field instead.

These rules reduce confusion when expected values do not appear. Also consider usability and performance when showing every combination.