Measures

Measures are a set of functions. Examples include a SUM of the actual gross revenue in USD, or a COUNT of the number of building permits worth over $500,000.

The designer defines the aggregation function (SUM, COUNT, and so on) for custom measures, so end users don't need to do so themselves when they create an analysis. You can apply these functions on fields of type Date, Numeric, or Currency.

Measures available to a particular type of field may differ depending on the field type. After you define the measures for the required fields and publish the custom subject area, you can select these fields and the applied measures when creating your report in the Oracle BI Composer. You can only specify aggregate formulas to apply to a measure when creating a custom subject area. It is not possible to edit a measure in an already published custom subject area.

Here are some measures you can apply to fields of type Numeric, Currency, or Date.

• For Numeric and Currency fields, a measure can be:

  • All

    Note: All is not a measure, but an option in the UI that selects all of the measures.

  • Sum: Calculates the sum of the values.

  • Average: Calculates the mean value.

  • Count: Calculates the number of rows that are not null.

  • Count Distinct: Calculates the number of rows that are not null. Each distinct occurrence of a row is counted only once.

    Note: Although Count Distinct is usually used in cases requiring a count on a foreign key (because a count of distinct rows is what's wanted), it is not required. If your requirements allow multiple instances of the same foreign key value to be counted multiple times, you can use Count rather than Count Distinct.

  • Maximum: Calculates the highest numeric value.

  • Minimum: Calculates the lowest numeric value.

  • First: Selects the first occurrence of the item.

  • Last: Selects the last occurrence of the item.

  • Median: Calculates the middle value.

  • Standard Deviation: Calculates the standard deviation to show the level of variation from the average.

  • Standard Deviation Population: Calculates the standard deviation using the formula for population variance and standard deviation.

• For Date fields, a measure can be:

  • All

  • Maximum

  • Minimum

You can select measures based on your reporting needs. For example, you can use measures to view product sales per store, state, or country. Or, to view the number of support tickets opened or closed per day, week, or month, and so on.

If you do not define a measure, then one will be automatically created for the subject area when you submit the custom subject area for publication.