Bookshelf Home | Contents | Index | PDF     

Oracle® Business Intelligence Answers, Delivers, and Interactive Dashboards User Guide > Working with Oracle BI Views in Oracle BI Answers >

Editing the Formula of a Column

This section describes how to edit the formula of a column, and contains the following topics:

To manually edit the formula of a column

  1. In the Table View (or in the Columns area on the Criteria tab), click the Edit Formula button for the column whose formula you want to edit.
  2. In the Edit Formula dialog box, display the Column Formula tab.
  3. Type the formula into the Column Formula area.
  4. Click OK when you are done.

To add operators and characters to the Column Formula area using the button bar:

  1. In the Table View (or in the Columns area on the Criteria tab), click the Edit Formula button for the column whose formula you want to edit.
  2. In the Edit Formula dialog box, display the Column Formula tab.
  3. Click the first eight buttons in the button bar to insert the following operators/characters into the text area:
    • Plus - (the '+' button inserts the '+' character)
    • Minus - (the '-' button inserts the '-' character)
    • Multiply - (the 'x' button inserts the '*' character)
    • Divide - (the '/' button inserts the '/' character)
    • Percentage - (the '%' button inserts the '/100' characters)
    • Open parenthesis - (the '(' button inserts the '(' character)
    • Close parenthesis - (the ')' button inserts the ')' character)
    • Concatenate - (the '||' button inserts the '||' character))

To add functions to the Column Formula area using the button bar:

  1. In the Table View (or in the Columns area on the Criteria tab), click the Edit Formula button for the column whose formula you want to edit.
  2. In the Edit Formula dialog box, display the Column Formula tab.
  3. Click the Function button to display the Insert Function dialog box, where you can select functions from a tree of function groups.
  4. Expand a function group to show a list of functions within that group.
  5. Click on a function name to show the syntax of the selected function and its description in the lower part of the dialog.
  6. Click the OK button, to dismiss the secondary dialog and insert the selected function's expression into the Column Formula area.

    If some text is selected in the Column Formula area when you click the Function button, the selected text is incorporated into the inserted function formula.

    If no text is selected in the column formula when you click the Function button, the function's expression is inserted and the first argument (denoted by tokens expr or expr1) is automatically selected.

To add column expressions to the Column Formula area using the button bar:

  1. In the Table View (or in the Columns area on the Criteria tab), click the Edit Formula button for the column whose formula you want to edit.
  2. In the Edit Formula dialog box, display the Column Formula tab.
  3. Click the Column button to select a column name from a menu of the available column names that are already selected into the criteria of the current report.

    When you select a column name from the menu, the column's expression is inserted into the Column Formula area.

To add variables to the Column Formula area using the button bar:

  1. In the Table View (or in the Columns area on the Criteria tab), click the Edit Formula button for the column whose formula you want to edit.
  2. In the Edit Formula dialog box, display the Column Formula tab.
  3. Click the Variable button to display a menu that enables you to select a Session, Repository or Presentation variable.

    When you select a variable from the menu, the Insert Variable dialog box is displayed.

  4. Type the name of the variable (and optional default value if applicable).
  5. Click OK to dismiss the dialog.

    The appropriate formula expression for the variable is created and inserted into the Column Formula area. The example below shows the formula for a Session variable named blah.

    "Sales Measures".Dollars + VALUEOF(NQ_SESSION.blah)

    NOTE:  Repository and session variables must be included as arguments of the VALUEOF function.

  • Static repository variables must be referred to by name.

    For example, to use the value of static repository variables named "prime_begin" and "prime_end", the following example shows the correct syntax:

    CASE WHEN "Hour" >= VALUEOF("prime_begin") AND "Hour" < VALUEOF("prime_end") THEN 'Prime Time' WHEN ... ELSE...END

  • Dynamic repository variables must be referred to by their fully qualified name.

    If you are using a dynamic repository variable, the names of the initialization block and the repository variable must be enclosed in double quotes ( " ), separated by a period, and contained within parentheses. For example, to use the value of a dynamic repository variable named REGION contained in a initialization block named Region Security, the following example shows the correct syntax:

    SalesSubjectArea.Customer.Region = VALUEOF("Region Security"."REGION")

  • Session variables must be preceded by NQ_SESSION and separated by a period, and both the NQ_SESSION portion and the session variable name must be contained within parentheses.

    If the variable name contains a space, the name must be enclosed in double quotes ( " ). For example, to use the value of a session variable named REGION, the following example shows the correct syntax:

    "SalesSubjectArea"."Customer"."Region" = VALUEOF(NQ_SESSION.REGION)

  • Presentation variables are referred to using their name, and optionally by a default value.

    The appropriate formula expression for the variable is created and inserted into the Column Formula area. The example below shows the formula for a Presentation variable named blah with a default value of 10.

    "Sales Measures".Dollars + @{blah}{10}

For more information about using variables, see the chapter about working with requests in Oracle Business Intelligence Answers, Delivers, and Interactive Dashboards User Guide.

To add filters to the Column Formula area using the button bar:

  1. In the Table View (or in the Columns area on the Criteria tab), click the Edit Formula button for the column whose formula you want to edit.
  2. In the Edit Formula dialog box, display the Column Formula tab.
  3. Click the Filter button to display the Insert Filter dialog containing a complex filter editor.

    The filter is initially blank, but you populate the filter by clicking a column in the selection pane to add a column filter and create a filter expression.

  4. Click a column name in the selection pane to display the Create/Edit Filter dialog.

    For more information about using this dialog, see Using Column Filters in an Oracle BI Request.

  5. Click OK to dismiss the Create/Edit Filter dialog when you finish creating the filter expression.

    The resulting filter expression is added to the complex filter definition in the Insert Filter dialog.

    The filter expression will be in the form:

    FILTER(<expression> USING <filter_expression>)

    Where:

      • <expression> is an expression that contains at least one measure. For example, the expression "sales + 1" is allowed if "sales" is a measure. The expression "productid" is not allowed if "productid" is a scalar attribute.
      • <filter_expression> is a boolean expression (evaluates to TRUE or FALSE) and does not contain any measures. Also, this expression may not contain any nested queries.

        NOTE:  If you selected some text in the Column Formula area before clicking the Filter button, it is incorporated in the inserted filter expression, replacing the expression token. For example, if you had selected "Sales Measures".Dollars before clicking the Filter button. A complex filter expression might replace the filter_expressions token as follows:
        FILTER("Sales Measures".Dollars USING ((Periods."Year" = '1999') AND ((Markets.District = 'CINCINNATI DISTRICT") OR (Markets.District = 'DENVER DISTRICT')))).

  1. Click on other column names in the selection pane to define more expressions to build up a complex filter statement involving multiple expressions.

    Use the AND/OR link to specify the relationship between multiple expressions.

  2. Click OK to dismiss the Insert Filter dialog

    This creates an appropriate filter expression and inserts it into the Column Formula area of the Edit Column Formula dialog.

  3. Click OK to dismiss the Edit Column Formula dialog.

    Filter expressions are displayed in the column name in the Criteria tab.

To add case statements to the column formula area using the Bins tab

  1. In the Table View (or in the Columns area on the Criteria tab), click the Edit Formula button for the column whose formula you want to edit.
  2. In the Edit Formula dialog box, display the Bins tab.

    Use the Bins tab to easily build up a formula in the Column Formula area using CASE statements.

    NOTE:  The Bins tab is not affected by the button bar. However, if you create a CASE statement using the Bins tab, the button bar will be hidden when you click the Column Formula tab. The button bar is only displayed if you clear all bins.

  3. Click the Add Bin button to display the Create/Edit Filter dialog.

    Use the Create/Edit Filter dialog to create a filter expression to display as a CASE statement in the Column Formula area of the Edit Column Formula dialog.

    For more information about using this dialog, see Using Column Filters in an Oracle BI Request.

  4. Click OK to dismiss the Create/Edit Filter dialog and display new or updated filter expressions in the Bins tab.
  5. Click the Column Formula tab to view filter expressions as CASE statements.
  6. Click OK when you are done.
Related Topics

Overview of Oracle BI Answers

Performing Common Tasks When Working with Oracle BI Views

  
Oracle® Business Intelligence Answers, Delivers, and Interactive Dashboards User Guide Copyright © 2007, Oracle. All rights reserved.