Skip to main content
Pentaho Documentation

Working with Measures in Analyzer

Overview

In Analyzer, you can work with measures in several ways to accomplish tasks, such as renaming a measure, creating a calculated measure, and editing a calculated measure. When updating the details of a measure or adding a calculated measure, you are making changes to the data model from within Analyzer. When you save these changes, they are now a part of the data source for other users to access. We call these types of changes to the data source inline modeling since these changes are reflected immediately in the data source you are working with. This approach allows you to make model changes easily in Analyzer which benefits other users immediately.

To make these types of changes, 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.

Updating Measure Properties

You can update the properties on base measures and calculated measures within Analyzer. For example, you may want to rename 'QTY' to 'Quantity,' change the aggregation method from a sum to an average, or adjust the MDX formula on a calculated measure. When you make and save such a change to a measure, you are making a change to the data source which will affect all users who are creating reports based on that data source.

You can only update the properties for base measures and for calculated measures which were added to the data source in Analyzer. You cannot edit the properties of a calculated measure created in PDI and published to Analyzer. You can only edit these calculated measures in PDI. See Using the Annotate Stream Step for SDR for more information.

To Update Base Measure Properties

These instructions assume you are creating or editing a report in Analyzer.

  1. In the Available fields list on the left, click the name of the base measure you want to edit, and then click the down arrow  to the right of the measure name. A shortcut menu appears.
  2. Click Measure Properties. The Measure Properties dialog box appears.                                                                                                                                                        
  3. You can edit the following fields.
    Field Description
    Display Name The name of the measure as it displays in the model.
    Aggregation Select an aggregation type using the drop-down list. Options include:
    • SUM
    • AVERAGE
    • COUNT
    • COUNT_DISTINCT
    • MINIMUM
    • MAXIMUM
    Note that COUNT and COUNT-DISTINCT are only applicable for non-numeric fields.
    Format Choose how this measure should be formatted, such as currency, general number, or percentage. Use the drop-down arrow to select a format from a system-defined list, or select the 'Text' icon next to the field to enter a custom format.
  4. When finished editing, click OK to save and apply your changes, or Cancel to close the dialog box without saving your changes to the measure.

To Update Calculate Measure Properties

These instructions assume you are creating or editing a report in Analyzer. Note that calculated measures display with the function symbol ('fx') in the Available Fields list.

  1. In the Available fields list on the left, click the name of the calculated measure you want to edit, and then click the down arrow  to the right of the measure name. A shortcut menu appears.
  2. Click Calculated Measure Properties. The Calculated Measure Properties dialog box appears.
  3. You can edit the following fields.
    Field Description
    Name The name of the measure as it displays in the model.
    Format Choose how this measure should be formatted, such as currency, general number, or percentage. Use the drop-down arrow to select a format from a system-defined list, or select the 'Text' icon next to the field to enter a custom format. If you do not specify a format, the default value of the first base measure is used as the format.
    Decimal Optionally, specify the number of decimal places for the results.
    Formula In the right panel, enter the formula for your calculated measure. You can write the MDX statement, or you can use the list on the left to drag measures into the right panel. You can also use the symbol buttons below to help create your statement, or just use your keyboard to write the expression.
    Create subtotals using measure formula Select this check box to use this calculated measure when adding up subtotals in your report.
  4. When finished editing, click OK to save your changes to the calculated measure to the data source, or Cancel to close the dialog box without saving your changes.

Adding a Calculated Measure to the Data Source

Calculated measures are user-defined measures based on a user's customizations to base measures in the data model. By applying a formula, in this case an MDX statement, to a selected base measure, the user creates a calculated measure to use in her Analyzer report.

Since Pentaho 6.0, you can save your calculated measure to the data model while creating or editing your report in Analyzer. Previously, if you wanted to add a calculated measure to the model for others to use in their reports, you had to submit a request to IT and wait until it was available. Now you can save the calculated measure to the model from within Analyzer so you can use that new measure without reloading your report. In addition, after you save your report, other users can  immediately benefit from your calculated measure.

If you want to create a calculated measure to only use in a particular report, see Creating a Calculated Measure in a Report.

To Add a New Calculated Measure to the Data Source

When you create a calculated measure using a measure in the Available Fields list in Analyzer, it is added to the data source when you save your report. To create a calculated measure within Analyzer, create or open an existing report in Analyzer.

  1. In the Available Fields list, click a measure to select it and then click the down arrow next to it.                                                                                             
  2. From the shortcut menu which appears, select Create Calculated Measure. The New Calculated Measure dialog box appears.
  3. In the Name field, enter a name for your calculated measure.
  4. In the Format field, specify how you want the results of your measure to appear in your report. Optionally, specify the number of decimal places for the results. If you do not specify a format, the default value of the first base measure is used as the format.
  5. In the right panel, enter the formula for your calculated measure. You can write the MDX statement, or you can use the list on the left to drag measures into the right panel. You can also use the symbol buttons below to help create your statement, or just use your keyboard to write the expression.
  6. Select the Calculate subtotals using measure formula check box to use this calculated measure when adding up subtotals in your report.
  7. Click OK to save this calculated measure. When you save your report, your calculated measure will also be saved to the data model. Once you save the report, the measure will be available for future reports which use this data source. Optionally, click Cancel to close the dialog box without saving your changes.

To edit a calculated measure you have created and saved in Analyzer, see Updating Calculated Measure Properties.