Skip to main content

Pentaho+ documentation has moved!

The new product documentation portal is here. Check it out now at docs.hitachivantara.com

 

Hitachi Vantara Lumada and Pentaho Documentation

Use calculated measures in Analyzer reports

Parent article

In Pentaho Analyzer, 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 their Analyzer report.

As part of inline modeling, you can now save your calculated measure to the data model while creating or editing your report in Analyzer. Previous to Pentaho 6.0, if you wanted to add a particularly useful 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 other users can benefit immediately from your work.

To add calculated measures to the model or to edit calculated measures saved to the data model, 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.

Creating a calculated measure in a report

To create a calculated measure within your report, create or open an existing report in Pentaho Analyzer. Be sure to select a base measure for which you would like to create a calculated measure and then add it to the Layout panel.
NoteTo learn how to create a calculated measure in the data source, see Adding a Calculated Measure to the Data Source.

Procedure

  1. Click the Down Arrow next to a base measure in the Layout panel, select User Defined Measure from the menu, and then select Create Calculated Measure.

    The Create Calculated Measure dialog box appears.Select this check box to save your calculated measure to the data source.
  2. In the Display Name field, enter a name for your calculated measure.

  3. In the Format field, specify how you want the results of your measure to appear in your report. Optionally, edit 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.
  4. 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.
  5. Select the Calculate subtotals using measure formula check box to use this calculated measure when adding up subtotals in your report.

  6. (Optional) Select the Apply to data source check box to add the calculated measure to the data model.

    When you click OK to save this calculated measure, your calculated measure will also be saved to the data model. Once you save the report, the measure will now be available for other users to add to their reports.
  7. Click OK to apply your calculated measure to your report, or click Cancel to close the dialog box without applying your changes.

    When you save your report, your calculated measure will also be saved with your report.

Next steps

  • If you have not saved the report yet, you can click the Undo button to remove the calculated measure, even if you selected to apply it to the data source.
  • You can use hidden fields to create calculated measures. When you select the Show Hidden Fields option in the View menu for the Available Fields list, measures set as 'hidden' are available for selection in the Create Calculated Measure dialog box. To view hidden measures, you need the Manage Data Source permission. See Hide and Unhide Available Fields for additional details.

Editing a calculated measure in the report

If you have created a calculated measure, but have not added it to the data source, you can edit that calculated measure in the Layout panel or in the report.

Procedure

  1. Click the Down Arrow next to a calculated measure in the Layout panel, select User Defined Measure from the menu, and then select Calculated Measure Properties. Optionally, you can open this dialog box in the report by double-clicking the calculated measure, or right-clicking it to select User Defined Measure Calculated Measure Properties.

    The Properties dialog box appears for that calculated measure.Select the calculated measure to display the drop-down menu.
  2. You can view and/or edit the following fields.

    NoteSome of the fields only display for users who are assigned the Manage Data Sources permission.
    FieldDescription
    Display NameThe 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 type in the field to enter a custom format.

    The Format field only displays when the value for the field is a number or a date. If you do not specify a format, the default value of the first base measure is used as the format.

    See Format Field Options for more information on selecting the appropriate format for your report.

    Decimal Places(Optional) Specify the number of decimal places for the results.
    TypeThe type of field, measure, such as calculated measure. This field is always read-only.
    MDX FormulaIn 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.
    Calculate subtotals using measure formula(Optional) Select this check box to use this calculated measure when adding up subtotals in your report. Clear this check box to not use this formula when calculating subtotals.
    Apply to data source(Optional) Select this check box to add the calculated measure to the data model. When you click OK to save this calculated measure, your calculated measure will also be saved to the data model. Once you save the report, the measure will now be available for other users to add to their reports.
  3. Click OK to apply your calculated measure to your report. When you save your report, your calculated measure will also be saved with your report. Click Cancel to close the dialog box without applying your changes.

Next steps

  • If you have not saved the report yet, you can click the Undo button to remove the calculated measure, even if you selected to apply it to the data source.
  • You can use hidden fields to create calculated measures. When you select the Show Hidden Fields option in the View menu for the Available Fields list, measures set as 'hidden' are available for selection in the Create Calculated Measure dialog box. To view hidden measures, you need the Manage Data Source permission. See Hide and Unhide Fields for additional details.

Open the New Calculated Measure dialog box

You can create calculated measures from several locations within Pentaho Analyzer.

From the . . .Do this:
Layout panelClick the Down Arrow next to a base measure, select User Defined Measure from the shortcut menu, and then select Create Calculated Measure.
Report panelIf the measure has been added to the report, right-click the measure in the report, select User Defined Measure from the shortcut menu, and then select Calculated Measure Properties.
Available Fields panelSee Adding a Calculated Measure to the Data Source.

Tips for using calculated measures

Keep the following guidelines in mind when working with calculated measures in Pentaho Analyzer.

  • When you add a calculated measure to the data source, it is available for anyone using that data source in Analyzer. This calculated measure appears and functions like any other base measure. If you do not add your calculated measure to a data source, it will only appear in the one report where is was added.
  • If you create a calculated measure just for your report, but later decide that you want other users to have access to it, then you can add the calculated measure to the data source using one of the following methods:

    From the . . .Do this:
    Layout panelClick the Down Arrow next to the calculated measure, and then select Add to Data Source.
    Report panelRight-click the calculated measure, and then select Add to Data Source.
    In the Properties dialog boxSelect the Apply to data source check box.
  • If you create a calculated measure and add it to the data source, but have not yet saved the report, you can use the Undo button to undo those actions, which will remove the calculated measure. Conversely, you can use the Redo button to reapply those actions.
    NoteWhen you click Undo, actions are removed in the reverse order in which they were applied, so any actions performed after adding the calculated measure will be undone first.
  • If you add a calculated measure to the data source, you can view and edit its properties from the Available Fields list. See Updating Calculated Measure Properties to learn how.