Creating Custom Attributes and Measures

Custom attributes and measures are new columns derived from other attributes or measures in the data model.

For example, a common use of a custom attribute is to concatenate two attributes. Use single quotes and spaces to add space between the values. For example:

[ContactTitle]+' '+[ContactName]

For example, a common use for custom measures is to calculate a third measure from two base measures. For example, to calculate revenue from quantity and unit price:

[Quantity]*[Unit_Price]

Custom Attributes

You create a custom attribute by applying a formula to one or more base attributes from the same hierarchy level. These custom formulas execute at the data tier (pre-aggregation) and the custom attributes become available in the subject areas of Designer and Visualizer. You can also use custom attributes in report filters. Another common use of a custom attribute is to extract a specific date part from a date attribute, for example:

DATEPART(YEAR,[Order_Date])

You can pass prompt filters and values to custom formulas. See Passing Prompt Filters and Values to Expressions and Custom Formulas.

Important: Custom attributes are not supported for Live Access sources.

To create a custom attribute

1. From Admin 2.0, select Space Management.
   
   
2. Select Custom Attributes & Measures.
   
   
3. Select the Custom Attributes tab.
4. Select the + Create New Custom Attribute icon.
   
5. Enter the following information for the custom attribute:
   
   
   • Name: Enter the Name for the new custom attribute.
   • Dimension: Select the dimension to determine the attributes that can be contained in the formula.
   • Auditable: Enabling this allows the attribute to be included in the Audit Trail when queried. For more information on the Audit Trail, see Audit Trail.
   • Formula Field: Enter the formula for the new custom attribute. For more information, see Formula Reference for Custom Attributes and Measures.
   • Use Columns: Select the columns to be used in the Formula field. The available columns are determined by the selected Dimension.
     
6. Click Save. The Custom Attributes tab lists the new custom attribute.
   
7. To modify or delete a custom attribute, select the custom attribute and click either Edit or Delete icon.

Custom Measures

Custom measures are measures that you define in the data model by applying a formula to one or more base measures from the same data grain.

Important: Custom measures are not supported for Live Access sources.

The syntax for custom measure formulas is similar to BQL but not the same. The Custom Measures dialog adds the dimension and aggregation syntax behind the scenes. See Formula Grammar for Custom Attributes and Measures.

Another use of a custom measure is to find the difference between two dates. The dates must be measures, not attributes. For example, to calculate the difference between the order and shipped date:

DATEDIFF(DAY,[OrderDate],[ShippedDate])

Custom formulas execute at the data tier (pre-aggregation) and the custom measures become available in the subject areas of Designer and Visualizer. See Pre-Aggregation vs. Post-Aggregation Calculations.

You can pass prompt filters and values to custom formulas. See Passing Prompt Filters and Values to Expressions and Custom Formulas for information.

Create a custom measure

1. From Admin 2.0, select Space Management.
   
   
2. Select Custom Attributes & Measures.
   
   
3. Select the Custom Measures tab.
4. Select the + Create New Custom Measure icon.
   
5. Enter the following information for the custom measure:
   
   
   • Name: Enter the name for the new custom measure.
   • Grain: Select a grain to use as the source of the metric.
   • Aggregation Rule: The aggregation rule determines how the custom metric is aggregated at higher levels. For example, SUM determines that the values of the custom metric are added together at levels above the grain.
   • Formula field: Enter the formula for the new custom metric. For more information, see Formula Grammar for Custom Attributes and Measures.
     
   • Use Columns: Select the columns to be used in the Formula field. The available columns are determined by the selected Dimension.
     
6. Click Save. The Custom Measures tab lists the new custom measure.
7. To modify or delete a custom measures, select the custom measure and click either Edit or Delete. icon.