



Products	Support	Download	Contact

Selecting on Record Counts

return to contents or index

Abstract

Describes how to select summary information based on accumulated amounts.

Q&A:

Question:

I need to create a report that selects only those customers that have more than one service contract with us. How can I select only those customers with two or more records in our SERVICE file?

Answer:

Prior to XENTIS v4.4, it was necessary to first create a new temporary file and then subsequently run XENTIS/Report. Starting with version v4.4 of XENTIS is the ability to select on subtotals. Now you can do this report very easily -- and in a single pass.

In our sample report we have two files; an ACCOUNT_MASTER file and a SERVICE_CONTROL file. The SERVICE_CONTROL file has one or more entries for a given client account based on the number of separate service contracts they have.

The easiest way to 'count' items is to create a calculation (assume the calculation is called COUNT) that is defined as a literal value of 1. The expression itself is entered as: COUNT = 1. When COUNT is specified as a field to print on your report, each 'detail' (each line on the report) is assigned a value of 1. This technique allows you to count the number of 'details' in the report. When you accumulate COUNT and add sort and break fields, you can then display a count of logical groupings of 'details'. In this report, if you sort and break by ACCOUNT_NO, you can count the numberof service contracts for each customer.

To activate the Select on Subtotals feature you must respond with an "S" at the "Do you wish to have breaks" prompt. When this feature is used, each break field will prompt the user for a series of select-on-subtotal expressions. All expressions must evaluate to TRUE for the group of records to be selected. Each expression must be of the form:

<Accumulated-field> <Operator> <Numeric-literal>

<Accumulated Field> must be a field specified in the report that is accumulated, but not using running totals or alternate accumulation types such as /AVE, /MIN, etc.
<Operator> must be a XENTIS comparison operator such as GE, LE, CN, etc.
<Numeric-literal> must be a valid XENTIS numeric literal.

When you respond "S" at the "Do you wish to have breaks" prompt, you will first see the regular prompts of the break section. When prompted for the Select on subtotal expression, simply enter COUNT GT 1. An extract from the .XCF command file would look like this:

! ******* Calculation Section *******
Y                                       ! Yes we have calculations
COUNT = 1                               ! Calculation expression 1 
NONE                                    ! No more calculations::
! ******* Break Logic Specifications *******
S                                       ! Break logic wanted
A:COMPNAME                              ! Break field name 1
NONE                                    ! Break name 1       
!                                       ! Skip lines before break
N                                       ! Skip to top of page
!                                       ! No counter to reset
1                                       ! Skip lines after break
COUNT GT 1                              ! Select-on-Subtotal expression 1
NONE                                    ! No more expressions
NONE                                    ! No more break logic

Note: If select-on-subtotal expressions are entered for multiple break levels, XENTIS will compute the subtotals and evaluate the select-on-subtotal expression for the inner most break levels first.