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            apex_exec.t_column_name,
  p_value_column          IN            apex_exec.t_column_name,
  p_overall_label         IN            t_label                   DEFAULT NULL,
  p_overall_value_column  IN            apex_exec.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;