CREATING COMPUTED COLUMNS

Computed columns calculate values based either on PL/SQL expressions or on data provided through database columns. There are two categories of computed columns that can add to a report:

Formula Columns:

Formula columns compute their values using PL/SQL expressions. Formulas could operate on multiple values per record.

Summaries Columns:

Summary  columns  compute  their  values  using  build  in  functions  of  Oracle  Report. The Summaries operate on one value over multiple records.

Create a Formula by using the Formula Column Tool that is:

1.    A formula column could be created in the data model editor. Then Click on View...Data Model to open the data model editor. The Data model editor should displays a query object and a group object which encloses a number of columns.

2.    Raise the height of the group to make place for the formulae column. Choose the formula column tool in the palette and place it after the last field in the group radiant.

A new column initially named as CF_1 is created as display given below. Because a new column within the radiant group is created, it will be shown as frequently as the other columns in the same group.

3.    Set the subsequent properties for the formulae column.

4.    Click on the property PL/SQL formulae property. The program Unit editor is invoked as display given below.

5.    Enter the complete formula for the sale value for each product:

function radiant return Number is
begin
return: avg_product_rate *: sum_quantity;
end;

The colons appear before sum_quantity and avg_product_rate since it functions as a bind variable reference; that is the values of sum_quantity are substituted into the formula at runtime.

6.    Click on Compile.  the  status  line  reports, Successfully Compiled if  the  function  is  correctly  typed  in or else the status line reports Compiled with Errors and the Program Unit editor points out the errors in the Compilation Messages field.

7.    Click  on  Close  to  close  the  Program  Unit  editor  after that  close  the  property  sheet. cf_sales_value is currently listed as a column belonging to   radiant. The column name appears in italic, involving in which it is a user-created column.

8.    Right click on the data model editor and select Report Wizard to invoke the report wizard.

9.    The newly created field is shown in the available field’s list item as display.

10.    Select this field as display given below.

11.    Select Label tab and modify the label of the formula field to Sales    Value.

Create a Report Summary by using Summary Column Tool:

To create a summary that computes the total sale that is. Sum of sales value for each product:

1.    Invoke the Report wizard by clicking on the Tools...Report Wizard. Then choose the Totals tab. The list of available fields for that summary information can be specified is displayed as shown in below diagram.

2.    Select CF_sales_value from the list and click on icon. Therefore denotes that it is the sum of CF_sales_value. The finished totals tab screen in the report wizard will be displayed as shown in given below.

3.    Click on finish.

4.    Open the Data model editor by clicking on View.. Data Model. The summary column with a default name will be displayed as shown in the below diagram.