about-data-model-columns

Data Base columns

Database columns represent a column that is selected by the query and contains the data values for a report. For each column that you select in your query, Reports Builder automatically creates a column in the data model of your report.

 Formula Columns

            Formula columns to create computed columns. It can be written using PL/SQL syntax. Formula columns names are generally preceded by “CF_” to distinguish them from data columns. A formula column performs a user-defined computation on the data of one or more column(s), including placeholder columns.

For example, : ITEMTOT *.07 is a formula that performs a computation on one column, while :SAL + :COMM performs a computation using two columns in a record. You create formulas in PL/SQL using the PL/SQL Editor.

Note: Formula columns should not be used to set values for parameters.

Creating or editing a formula column

To create or edit a formula column:

1.      In the Data Model view, single-click the Formula Column in the tool palette, then:

·         To create a column within a group, click in the group at the position you want the column placed in the hierarchy.

·         To create a report-level column, click in an open area of the canvas region.

2.      Double-click the formula column object to display the Property Inspector.

3.      Under the Placeholder/Formula node, double click the PL/SQL Formula property field.

In the PL/SQL Editor, define the PL/SQL for the formula for example SAL * 0.07.

Summary Columns

            Summary columns perform a computation on another column’s data. These are used for calculating summary information like SUM,AVERAGE,COUNT,MINIMUM,MAXIMUM,%TOTAL. This column uses a set or predefined Oracle aggregate functions that can be applied to data or formula columns. Summary columns names are generally preceded by “CS_” to distinguish them from data columns.

Creating a summary column:

 To create a summary column (for totals or subtotals) using the Data Wizard:

Ø       In the Data Model view, click the query that contains the column you want to total.

Ø       Choose Tools?> Data Wizard.

Ø       On the Totals page, follow the wizard to add the desired summary to your report.

 Note: For group reports, the Report Wizard and Data Wizard create n summary fields in the data model for each summary column you define: one at each group level above the column being summarized, and one at the report level.   For example, if a report is grouped by division, and further grouped by department, then a summary column defined for a salary total would create fields for the sum of salaries for each division and each department group (group-level summaries), and the sum of all salaries (report-level summary).

To create a summary column (for totals or subtotals) using the tool palette:

Ø       In the Data Model view, single-click  in the tool palette, then:

o         To create a column within a group, click in the group at the position you want the column placed in the hierarchy.

o         To create a report-level column, click in an open area of the canvas region.

Ø       Double-click the summary column object to display the Property Inspector.

Ø       Under the Summary node:

o         Set the Function property by choosing the type of summary you want from the list.

o         Set the Source property to the column you want to summarize.

o         Set the Reset At property to the group at which the summary column value resets.

Place Holders

            A placeholder is a column for which we set the data type and value in PL/SQL that we define. Placeholder columns are useful when you want to selectively set the value of a column (E.g.: each time the nth record is fetched, or each time a record containing a specific value is fetched, and so on). We can set the value of a placeholder column in the following places:

§          the Before Report Trigger, if the placeholder is a report-level column

§          a report-level formula column, if the placeholder is a report-level column

§          a formula in the placeholder’s group or a group below it (the value is set once for each record of the group)

Creating or editing a placeholder column

To create or edit a placeholder column:

1.      In the Data Model view, single-click the Placeholder Column tool in the tool palette.

·         To create a column within a group, click in the group at the position you want the column placed in the hierarchy.

·         To create a report-level column, click in an open area of the canvas region.

2.      Double-click the placeholder column object.

3.      In the Property Inspector, set the desired properties for the placeholder column.

4.      Set the value of a placeholder column in:

·         the Before Report trigger, if the placeholder is a report-level column

·         a report-level formula column, if the placeholder is a report-level column

·         a formula in the placeholder’s group or a group below it (the value is set once for each record of the group)