Print — Count

Count as Column

The other form of count puts the total in a separate column on the report. The difference between the two forms is the reserved word: on.

There are actually 3 reserved words that use the on syntax:

On the first two, name precedes the reserved word because field1 actually prints. With count on, name never prints — rather the count of field1 prints.

When name precedes the count word, the result prints at the bottom of the report.

  • name count — counts the occurrences of name and prints at the end of the report
  • count on name — counts the occurrences of name and prints in a separate column.

In both cases the count will be the count of the unique values of name.

However, count on name by itself would print only one value in the column, since every record has a name.

QUIZ-054

The result would be like this:

QUIZ-042

It would be better to use the first form, the end count, rather than waste a whole column for the count.

So, count on is used to print counts of subsidiary or intermediate values. We modify the Quiz pad to separate the names by type:

QUIZ-057

  •  count on name on type — count the names for each type.
  • mask “#,### — formats the column
  • label “Count” — column heading.
  • skip — prints a blank line after the count whenever the count prints.

Now the “Count” column has some values:

QUIZ-048

There are 10 Application types and 1 Clause type records on this page.

Note also:

  1. the sort puts the records in type order so the records with the same type will be together.
  2. There is no limit to the number of filter levels: count on field1 on field2 on field3 on field4 on
  3. The first on field is the field we are counting.
  4. The rest of the on fields tells the report when to print. Whenever field2 or field3 or field4 changes in value, the count of field1 will print, and the count will reset to zero for the next group.

Here is another example:

QUIZ-049

soshp is the shipment table. sosli is the line  item table for the shipments.

  • count on sosli.no on shp.no label “Line Cnt”
    • counts the number of line items (sosli.no is a unique record number assigned to each line item)
    • for each shipment (shp.no is the unique shipment number).
    • Whenever the shp.no changes value, the count of sosli.no will print.
    • label “Line Cnt” — column label
  • count on shp.no on date month year total skip
    • counts the number of shipments for each month and year.
    • The date field is the date of the shipment.
    • The month and year reserved words filter the date to include the month and year — ignoring the day.
    • The result is a count of shipments for each month.
    • total — prints a total count of the shipments.
    • skip — prints a blank line after the count whenever it prints.

The result at the end of November looks like this:

QUIZ-053

The total on the last page at the end of December looks like this:

QUIZ-052

The topic of In-line expressions is for another article, but the count target can also be an inline expression:

QUIZ-058

[sosli.no where type= “I”] 

  • filters the line item count to include only those lines that are Inventory items.
  • The in-line expression is enclosed with [ and ].
  • The where reserved word introduces the condition (or conditions).
  • type is a field in the sosli table. (It could be a value in another table).
  • The operator is =  (lots of possibilities here).
  • Finally, the value of the type is the literal “I”.

The last page of the result is like this:

QUIZ-061

Remember that count on counts the unique occurrences of the target field. It doesn’t matter where in the sequence of of records it might reappear — if it shows up a second time, it will not be counted.

In our shipment, line item example, we had a unique number for each of the line items (sosli.no). That may not always be the case. In addition to fields and inline expressions, you can use the concatenation or combining of fields to make up a unique value.

Instead of:

  • count on sosli.no on shp.linno label “Line Cnt”

we can make up a unique target this way:

  • count on { shp.no “/” sosli.linno } on shp.no label “Line Cnt”
    • the squiggly brackets { and } enclose fields combined into a single value.
    • { shp.no “/” sosli.linno } — if the shipment number (shp.no) was 12345 and the line number (sosli.linno)  was 15, the result value would be 12345/15 which is unique enough in anyone’s book. There can’t be another shipment number record 12345 with a line #15, so it will be counted once. We added the separator, “/” to split up the two numbers. It could be any character or series of characters.  Without the separator there might be an apparent duplicate:  1234515 (shipment 123451 with line 5; or 1234 with linno 515; or 12345 with line 15).
    • on shp.no — the first on set the target of whatto count. The second and following ‘on’ arguments sets when to print. In this case, the count will print for each shipment.

So, the count on target can be a field, an expression or a concatenation.

  Page 1 — count
  Page 2 — end count
  Page 3 — count as column