About summary column
Example
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.
