Edit Column Formula dialog: Column Formula tab

Use this tab of the "Edit Column Formula dialog" to:

Specify custom text for headings for the column's folder and for the column.
Specify the aggregation rule for the column totals for an attribute or measure columns or for a KPI-related formula.
Edit the formula for an attribute or measure column. A column formula specifies what the column values represent, for example, "Sales Measures"."Dollars". By editing this formula, you can present analysis results in a variety of ways.
Create formulas that calculate KPI actual values, target values, and thresholds.

For more information, see:

This dialog is also displayed as:

Edit Formula dialog

Components

Folder Heading

Not available for KPIs.

Use this field to enter the text for the heading for the column's folder. To enable this field, select the Custom Headings box.

Column Heading

Not available for KPIs.

Use this field to customize the heading to reflect the data in the column. To enable this field, select the Custom Headings box. For example, to change the column heading to "Dollars Increased by 10%", select the Custom Headings box and then, in the Column Heading field enter Dollars Increased by 10%.

Custom Headings

Not available for KPIs.

Use this box to enable the Folder Heading and Column Heading fields so that you can customize the headings to suit your needs. For example, if you change the formula of a column, you might want to change the column heading to something that is more representative of the column.

Aggregation Rule (Totals Row)

Use this box to select the aggregation rule for the:

Column totals for attribute or measure columns. For example, if you want column totals to show the minimum value of the rows in the result set, then select Min. If you want them to show the sum of all the values in the result set, then select Sum, which is the default.
KPI-related formula

For a list of the valid values, see "Aggregation Rules and Functions".

Available

Displays the primary subject area and any related subject areas that have been added to the "Subject Areas pane". Use this area to select columns to use in formulas. To insert a column in a formula, select the column and click the Add Column button (the arrow button between the Available and Column Formula areas). Note that when you insert a column, if you have something highlighted in the Column Formula area, it is replaced.

Column Formula

Use this area to perform these tasks:

Create or edit the formula for an attribute or measure column.
Create the formula to calculate a KPI actual value, target value, or range.

When you have text selected in the Column Formula box, it is replaced by values that you select elsewhere, such as in the Available list or by inserting functions or filters. The selected text is also displayed in the Insert Function and Insert Filter dialogs when you invoke them.

You can enter the formula manually or use these components:

f(. . .) — Use this button to display the "Insert Function dialog", where you select a function from a tree of function groups. For example, you can build a formula based on a SQL function, such as RANK("Sales Measures"."Dollars").

For information about SQL functions, see Appendix D, "Logical SQL Reference."
Filter. . . — Use this button to display the "Insert Filter dialog", where you can add a SQL filter. For example, you can build a formula that uses the SQL FILTER function to filter the data, such as FILTER("Sales Measures"."Dollars" USING ("Markets"."Region" = 'EASTERN REGION').
Column > — Use this button to select a column from a list of the available attribute and measure columns that are included in the analysis criteria to use in the formula. This is helpful for example, if you have cleared the formula area and want to get back to where you started.
Variable > — Use this button to select the type of variable (Session, Repository, or Presentation) to add. The "Insert Variable dialog" is displayed, where you specify the name of the variable.

For example, you can build a formula using a presentation variable, such as "Sales Measures".Dollars + @{PercentIncrease}{1.50}, where PercentIncrease is the name of the presentation variable and 1.50 is its default value.
+ — Use this button to insert the addition operator to add values.
- — Use this button to insert the subtraction operator to subtract values.
x — Use this button to insert the multiplication operator to multiply values.
/ — Use this button to insert the division operator to divide values.
% — Use this button to insert the percent sign to divide values by 100.
( — Use this button to insert the left parenthesis to enclose data in brackets.
) — Use this button to insert the right parenthesis to enclose data in brackets.
||— Use this button to insert the concatenation operator.

Treat as an attribute column

Available only for measure columns and only when the dialog is invoked from the Criteria tab. The default is that the box is not selected. Use this box to specify that a measure column be treated as an attribute column. This setting forces a Group By of the column values when the measure column is positioned outside the Measures area to an edge of the view This setting is not applied to a measure column in the Columns and Measures area of a table or the Measures area for a pivot table or graph.

This box is required to facilitate functionality in this release that specifies to not show all the detail when a measure column is moved to an edge but rather to aggregate the measure column to the grain of the edge. For example, aggregating dollar values to the Region grain.

Suppose that you have an analysis that contains the Region, Units, and Dollars columns. Using the Bins tab of the Edit Column Formula dialog for Units, you create one bin for values greater than or equal to 10 million and you specify that bin name as X. All other values use the bin name of Y. These bins create a CASE statement in the formula for this column.

When you display that analysis in a table on the Results tab, you see values for X and Y in the Units column. If you display that analysis in a pivot table with the Units column on the Measures edge, you also see values for X and Y. If you move the Units column to another edge, such as the row edge of a pivot table, then you see only values for X. This is because the values of the column were aggregated for each region and none are now less than 10 million.

To resolve this problem and have views display data as in previous releases (prior to 11g), you select the Treat as an attribute column check box to specify that the measure column be handled as an attribute column, which prevents the aggregation of the values.

All analyses are upgraded from Release 10g with all measure columns having the Treat as an attribute column check box selected, to ensure that the measure columns are treated as attribute columns, as they were in Release 10g.

Notes

To manually enter a variable in an expression in the Formula area, follow the guidelines for referencing variables in expressions that are described in Table 2-2.