Print reports for Oracle APEX and Apache FOP


8. The Group by example

The database entry departmentEmployeesGrouping.xre  shows how to use the "group by" feature. The example takes as input a list of employees and groups them by status (in the test file there are 2 status, active and inactive).

For grouping 2 areas are required,

one group header and one group detail:

  • The group header must contain the value use for grouping in the "XML group by" property. In this example the status element. You must also set the value for "XML Node", these are the nodes you will be grouping, in this case persons.

    In the group header you can output the value of the element you are grouping by (in this case status).

Group detail areas have the following restrictions:

  • the value for the XML Node property will be inherited from the header.
  • the XML Group by property may not be used.

Note nested groups are not supported.

Group footers

The example departmentEmployeesGrouping_footer.xre shows how to create group footers. Group footers are used to show total fields of the values in the group for each value being grouped. In the example the group footer is used to show the total number of employees for each status.

Group footers have to be implemented as follow:
  • create a new area placed after the group detail area. In the example the footer area is the "detail 2" area.

  • The footer area must have as "super area" the "group header". In our example the super area of "detail 2" is "detail 1" which is the group header.
  • If you want to count or sum the fields of the current group you use an xpath expression like this one:

    count(/departments/department/person[status = current()/status])

    this means, count all persons (/departments/department/person) whose status has the same value as the current's group status. Note we are grouping by status so when this is executed for the "active" employees status, it will count all employees whose status is "active".

    Let's assume each person has a child element called "salary", we could sum all salaries of the employees in the current status like this:

    sum(/departments/department/person[status = current()/status]/salary)

    this means select the salary of all employee whose status is the same status as the current group and then sum all selected salaries.