A script-enabled browser is required for this page to function properly.

<formula> XML tag

The <formula> tag defines a formula column in the data model of the report definition. A formula column uses a PL/SQL function to perform an operation, typically a complex calculation of some kind. If you are performing a common calculation (for example, sum, percent of total, or standard deviation), then you can use the <summary> tag, which requires no PL/SQL.

Syntax

<formula
  name="column_name"
  source="plsql_function_name"
  dataType="number | character | date"
  width="number"
/>

Parameters /Options

Description

name

Is the name of the formula column

source

Is the name of a PL/SQL function defined within the <programUnits> tag that performs the desired operation for the formula.

dataType

Is the type of data that is generated by the formula. For example, if the formula performs a mathematical operation, then the result is a number. The possible values for dataType are number, character, and date.

width

Is the number of characters wide of the result of the formula.

Example

The following example shows a segment of an XML report definition that defines a data model with a formula column in it. The defaulting algorithm places the column in the appropriate group based on where you place its associated fields in the <layout> section.

<data>
  <dataSource name="Q_1">
    <select>
      SELECT ALL VIDEO_CATEGORY_BY_QTR.QUARTER, 
      VIDEO_CATEGORY_BY_QTR.SALES_REGION,
      VIDEO_CATEGORY_BY_QTR.STATE, VIDEO_CATEGORY_BY_QTR.CITY, 
      VIDEO_CATEGORY_BY_QTR.PRODUCT_CATEGORY, 
      VIDEO_CATEGORY_BY_QTR.TOTAL_SALES,
      VIDEO_CATEGORY_BY_QTR.TOTAL_COST,VIDEO_CATEGORY_BY_QTR.TOTAL_PROFIT
      FROM SCOTT.VIDEO_CATEGORY_BY_QTR
      WHERE VIDEO_CATEGORY_BY_QTR.SALES_REGION='West'
    </select>
  </dataSource>
  <dataSource name="Q_2">
    <select>
      SELECT ALL VIDEO_CATEGORY_BY_QTR.QUARTER, VIDEO_CATEGORY_BY_QTR.CITY, 
      VIDEO_CATEGORY_BY_QTR.PRODUCT_CATEGORY, 
      VIDEO_CATEGORY_BY_QTR.TOTAL_PROFIT,
      VIDEO_CATEGORY_BY_QTR.TOTAL_SALES,
      VIDEO_CATEGORY_BY_QTR.TOTAL_COST
      FROM SCOTT.VIDEO_CATEGORY_BY_QTR
      WHERE VIDEO_CATEGORY_BY_QTR.SALES_REGION='West'
    </select> 
  </dataSource>
  <formula name="Profit_Margin" 
    source="FormulaProfitMargin" 
    datatype="number" width="9"/>
</data>
<programUnits> 
  <function name="FormulaProfitMargin"> 
    <![CDATA[ 
      FUNCTION FormulaProfitMargin RETURN number IS 
      BEGIN 
        return ((:TOTAL_PROFIT1 / 
                 (:TOTAL_SALES1 - 
                 (0.07 * :TOTAL_SALES1)))
                  * 100); 
      END; 
    ]]> 
  </function> 
</programUnits>

See also

About XML in reports

Oracle Reports XML tags