Syntax

The basic syntax of a summarize statement is:

• print num1 summarize on field1 on field2  (etc.)
• print date1 summarize on field3
• print text1 summarize on field4
• chart num2 summarize on distinct field5

Whenever any one of the on fields change:

• the accumulated total of the numeric fields (num1, num2) prints.
• the value of the date or text fields (date1, text1) prints.

The field (num1, date1, text1) can be a dictionary or in-line expression:

• print express1 summarize on field7   (express1 is a dictionary expression)
• print [ num3 where field6 = “I” ] summarize on field8   (in-line expression)

ON modifiers

There are several reserved word modifiers that are available for the on arguments:

• on distinct field1 — The SQL query returns a value for every field for every row. Sometimes that means that a parent value will be repeated for every child record. If you are summarizing on the parent field, the on distinct ensures that the parent field is only counted ones.
  • For example: a shipment (parent) has line items (child). If there are 10 line items on a shipment, the shipment amount field will return 10 times, once for each line item. We use summarize to skip the duplicates:
    • print shipment amount summarize on distinct shipment number total
  • NOTE: Unless you are using one of the other ‘on’ modifiers (first, last, high, low or lowpos), it is easier to use repeat on, where the duplicates are handled automatically. Both methods support totals and break subtotals. The major difference between them is that repeat on prints on the first row where a change is detected while summarize on prints on the last row.
    • print shipment amount repeat on shipment number total
  • Use on distinct with chart values (the ‘Y’ axis on a bar chart) when there is a parent – child issue. Chart values are accumulated independently from the report fields. You can chart values that are not printed on the report, but are contained in the conditions of the where clause (under find at the top of the Quiz pad). Since the report can be printed in any order, the chart values may not be accumulated in that same order. The on distinct argument ensures that the chart values of a parent are counted once. The syntax is the same.
• on first — this returns the first occurrence of the field.
  • print field1 summarize on field2 on first
• on last — returns the last occurrence of the field.
  • print field1 summarize on field2 on last
• on high — returns the highest value of the field.
  • print field1 summarize on field2 on high
• on low — return the lowest value of the field.
  • print field1 summarize on field2 on low
• on lowpos — returns the lowest positive value of the field.
  • print field1 summarize on field2 on lowpos

The ‘on’ arguments can be in any order.

   Page 1 — summarize
   Page 2 — summarize syntax
   Page 3 — numeric summarize
   Page 4 — date summarize
   Page 5 — expression summarize
   Page 6 — text summarize

Next – numeric summarize >