ADD_AGGREGATE Procedure

This procedure adds an aggregate to the aggregate collection. Aggregate collections can be passed to the EXPORT calls in order to add an aggregate row. This procedure can be used in combination with control breaks or standalone for overall aggregates. If an empty aggregate collection (or no aggregate collection) is passed, no aggregate rows render in the export.

This procedure requires an aggregate column. Value is the current aggregate total (for control breaks) or the overall total.

Syntax

PROCEDURE ADD_AGGREGATE(
    p_aggregates            IN OUT NOCOPY   t_aggregates,
    p_label                 IN              t_label,
    p_format_mask           IN              VARCHAR2                            DEFAULT NULL,
    p_display_column        IN              wwv_flow_exec_api.t_column_name,
    p_value_column          IN              wwv_flow_exec_api.t_column_name,
    p_overall_label         IN              t_label                             DEFAULT NULL,
    p_overall_value_column  IN              wwv_flow_exec_api.t_column_name     DEFAULT NULL );

Parameters

Parameter	Description
`p_aggregates`	Aggregate collection.
`p_label`	Aggregate label.
`p_format_mask`	Format mask to apply on the aggegate value.
`p_display_column`	Name of the column where to display the aggregate.
`p_value_column`	Name of the column which contains the value of the aggregate.
`p_overall_label`	Overall label.
`p_overall_value_column`	Name of the column which contains the value of the overall aggregate.

Examples

DECLARE
    l_aggregates     apex_data_export.t_aggregates;
    l_columns        apex_data_export.t_columns;
    l_context        apex_exec.t_context;
    l_export         apex_data_export.t_export;
BEGIN
    apex_data_export.add_aggregate(
        p_aggregates              => l_aggregates,
        p_label                   => 'Sum',
        p_format_mask             => 'FML999G999G999G999G990D00',
        p_display_column          => 'SAL',
        p_value_column            => 'AGGREGATE1',
        p_overall_label           => 'Total sum',
        p_overall_value_column    => 'OVERALL1' );
        
    apex_data_export.add_column( p_columns => l_columns, p_name => 'DEPTNO', p_is_column_break => true );
    apex_data_export.add_column( p_columns => l_columns, p_name => 'EMPNO');
    apex_data_export.add_column( p_columns => l_columns, p_name => 'ENAME');
    apex_data_export.add_column( p_columns => l_columns, p_name => 'SAL');

    l_context := apex_exec.open_query_context(
        p_location    => apex_exec.c_location_local_db,
        p_sql_query   => 'select deptno,
                                    empno, 
                                    ename, 
                                    sal,
                                    sum( sal)  over ( partition by deptno ) as AGGREGATE1,
                                    sum( sal)  over ( ) as OVERALL1
                            FROM emp
                            order by deptno' );

    l_export := apex_data_export.export (
                        p_context      => l_context,
                        p_format       => apex_data_export.c_format_pdf,
                        p_columns      => l_columns,
                        p_aggregates   => l_aggregates );

    apex_exec.close( l_context );

    apex_data_export.download( p_export => l_export );

EXCEPTION
    WHEN others THEN
        apex_exec.close( l_context );
        raise;
END;