Summary Column properties

About summary column
A summary column performs a computation on another column's data.  Using the Report Wizard or Data Wizard, you can create the following summaries:  sum, average, count, minimum, maximum, % total.  You can also create a summary column manually in the Data Model view, and use the Property Palette to create the following additional summaries:  first, last, standard deviation, variance.

Break Order
Description The Break Order property is the order in which to display the column's values.  This property applies only to columns that identify distinct values of user-created groups (i.e., break groups).  The order of column values in a default group is determined by the ORDER BY clause of the query for SQL queries and by the sort column for Express queries.  For column values in user-created groups, however, you must use Break Order to specify how to order the break column's values.
Values
None
Ascending
Descending
Applies to columns
Required/Optional Required, if Set Break Order is set to ‘Yes’.
Default Ascending for database and sort columns. None for database columns representing dimensions in Oracle Express queries with break groups.


Function
Description The Function property is the computation to be performed on the values of the column specified in Source.  To create running summaries, you place the column in the appropriate group and use Function in conjunction with Reset At.
Values
Average        Calculates the average of the column's values within the reset group.

Count            Counts the number of records within the reset group.

First              Prints the column's first value fetched for the reset group.

Last              Prints the column's last value fetched for the reset group.

Maximum       Calculates the column's highest value within the reset group.

Minimum       Calculates the column's lowest value within the reset group.

% of Total     Calculates the column's percent of the total within the reset group.

Std. Deviation Calculates the column's positive square root of the variance for the reset group.

Sum              Calculates the total of the column's values within the reset group.
Variance        Sums the squares of each column value's distance from the mean
Value of the reset group and divides the total by the number of values minus 1.
Applies to summary columns
Required/Optional required, if column is a summary column.
Default blank


Reset At
Description  The Reset At property is the group at which the summary column value resets to zero (if Function is Count), null (if Function is not Count), or Value If Null (if the column has one).  Reset At has a list of values containing valid reset groups.  Reset At determines if the summary is a running summary or a periodic (e.g., group-level) summary.
Value
Page             Is used for page-level summaries.  The summary is reset between pages.

Report           Is used for summaries that apply to the entire report, such as grand totals.  The summary is reset after the report (i.e., it is never reset).

<Names>       Is a valid group name.  The summary is reset after each record of the group.

Applies to     summary columns
Required/Optional required, for columns of Type Summary.
Usage Notes
  • The reset group of a summary column may be its group or any group above its group, including Report and Page.
  • To ensure that page summaries behave in a predictable way, make sure each record fits on a single page, or if that is not feasible, force each new record to print on a new page.


Example
Suppose that you want to create a group report like the one below:

DEPT   NAME            SAL      RESETSUM    RUNSUM
----------------------------------------------------------
10      SMITH           1000   1000             1000
          JONES           1000   2000             2000
          KING             1000   3000             3000

20      JOHNSON       1500   1500             4500
          WARD           1000   2500             5500

The parent group, G_DEPT, contains the DEPTNO column.  The child group, G_EMP, contains ENAME and SAL.  To get the sum of salaries for each department (RESETSUM), you create a summary column in G_EMP with the following settings

Function        Reset At
Sum              G_DEPT
In this case, Reset At indicates that the summary should be set to null after each department.

To get the running sum of salaries (RUNSUM), you create a summary column in G_EMP with the following settings:

Function        Reset At
Sum    Report
In this case, Reset At indicates that the summary should not be reset to null but should accumulate throughout the report.


Value If Null
Description The Value if Null property is a value to be substituted for any null values of the column.  For example, if you enter X in this field for a character type column, then an X will be displayed for null values fetched for the column.  If left blank, no substitution will be done for null values.
Values Enter any valid value that conforms to the column's Data type.  Value if Null cannot exceed 1K in length.
Applies to columns
Required/Optional optional
Default blank
Usage Notes
Entering a string for Value if Null on a break column causes Report Builder to prefetch all of the rows in the break column's group.  This could degrade performance when you run the report.  To avoid pre fetching, use the NVL function in your query rather than entering a string for Value if Null.