Use calculated measures in Analyzer reports

In Pentaho Analyzer, calculated measures are user-defined measures. They use formulas, usually MDX, over base measures.

You can save a calculated measure to the data model from Analyzer. This makes it available to other users immediately.

Before you begin

To add or edit calculated measures in the data model, you need the Manage Data Sources permission. For details about roles and permissions, see Use Pentaho Security.

Open the Create Calculated Measure dialog box

You can open the dialog from these locations:

• Layout panel: Click the down arrow next to a base measure. Then select User Defined Measure > Create Calculated Measure.

• Report panel: If the measure is in the report, right-click it. Then select User Defined Measure > Calculated Measure Properties.

• Available Fields panel: See Add a calculated measure to the data source.

Create a calculated measure in a report

To create a calculated measure in a report, create or open a report in Analyzer. Then add the base measure to the Layout panel.

To create a calculated measure directly in the data source, see Add a calculated measure to the data source.

1. Click the down arrow next to a base measure in the Layout panel. Then select User Defined Measure > 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 results to display in the report.

   If you do not specify a format, Analyzer uses the format from the first base measure.

4. In the right panel, enter the formula for your calculated measure.

   You can type the MDX statement or drag measures into the formula editor.

5. Select Calculate subtotals using measure formula to use the calculated measure for subtotals.

6. (Optional) Select Apply to data source to save the calculated measure to the data model.

   When you save the report, the measure becomes available to other users of the data source.

7. Click OK to apply your calculated measure to your report. Click Cancel to close the dialog box without applying changes.

When you save your report, the calculated measure is saved with the report.

• If you have not saved the report yet, you can click Undo to remove the calculated measure, even if you applied it to the data source.

• You can use hidden fields to create calculated measures. Turn on Show Hidden Fields in the View menu for Available Fields. To view hidden measures, you need the Manage Data Sources permission. For details, see Hide and unhide fields.

Edit a calculated measure in a report

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

1. Click the down arrow next to the calculated measure in the Layout panel. Then select User Defined Measure > Calculated Measure Properties.

   You can also open this dialog from the report. Double-click the calculated measure, or right-click it and select User Defined Measure > Calculated Measure Properties.

   The Properties dialog box appears.

   

   Select the calculated measure to display the drop-down menu.
2. View or edit the fields.

   Some fields only display if you have the Manage Data Sources permission.

Field	Description
Display 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. Select a format from the list or enter a custom format. The Format field shows only when the value is a number or a date. If you do not specify a format, Analyzer uses the format from the first base measure. For details, see Format field options.
Decimal Places	(Optional) Specify the number of decimal places for the results.
Type	The type of field, measure, such as calculated measure. This field is always read-only.
MDX 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.
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.

1. Click OK to apply your calculated measure to your report.

   When you save your report, your calculated measure is saved with the report.

   Click Cancel to close the dialog box without applying your changes.

• If you have not saved the report yet, you can click Undo to remove the calculated measure, even if you applied it to the data source.

• You can use hidden fields to create calculated measures. Turn on Show Hidden Fields in the View menu for Available Fields. To view hidden measures, you need the Manage Data Sources permission. For details, see Hide and unhide fields.

Tips for using calculated measures

Keep these guidelines in mind when working with calculated measures:

• If you add a calculated measure to the data source, it is available to anyone using that data source in Analyzer.

• If you do not add the calculated measure to the data source, it only appears in the report where you created it.

• If you first created a measure for your report, you can later add it to the data source:

  • Layout panel: Click the down arrow next to the calculated measure. Then select Add to Data Source.

  • Report panel: Right-click the calculated measure. Then select Add to Data Source.

  • Properties dialog box: Select Apply to data source. (See Edit a calculated measure in a report.)

• If you added a calculated measure to the data source but have not saved the report, you can click Undo to remove it.

  When you click Undo, Analyzer removes actions in reverse order.

• If you add a calculated measure to the data source, you can view and edit its properties from Available Fields. See Working with Analyzer measures.