Example 2: counting male and female employees

Example 1 demonstrates the use of the Sum and Count functions. Sum and Count are two examples of a class of functions called aggregate functions.

An aggregate function is a function that operates on a range of values in a column. The aggregate functions are:

Avg	Large	Mode	Sum
Count	Last	Percent	Var
CumulativePercent	Max	Small	VarP
CumulativeSum	Median	StDev
First	Min	StDevP

About crosstab functions Although the crosstab functions (CrosstabAvg, CrosstabAvgDec, CrosstabCount, CrosstabMax, CrosstabMaxDec, CrosstabMin, CrosstabMinDec, CrosstabSum, and CrosstabSumDec) behave like aggregate functions, they are not included on the list because they are for crosstabs only and are designed to work in the crosstab matrix.

A few restrictions apply to the use of aggregate functions. You cannot use an aggregate function:

In a filter
In a validation rule
As an argument for another aggregate function

This example demonstrates the use of the Sum aggregate function.

What you want to do

Using the employee table in the EAS Demo DB as the data source, you create a report using at least the Emp_id and the Sex columns. You want the report to display the number of male employees and female employees in the company.

How to do it

In the summary band in the workspace, add two computed fields to the report that use the Sum and If functions:

Sum(If(sex = "M", 1, 0))

counts the number of males in your company;

Sum(If(sex = "F", 1, 0))

counts the number of females in your company.

By clicking the Page computed field button, you can also add a Page computed field in the footer band to display the page number and total pages at the bottom of each page of the report.

What you get

Here is what the design of the report looks like.

The sample’s header band displays Employee I D and Sex. The Detail band has emp _ i d and radio buttons for Male and Female. The Summary band displays Number of males and under it the expression Sum ( If ( sex = " M ", 1 , 0 ) ) and Number of females and under it the expression Sum ( If ( sex = " F ", 1 , 0 ) ). The Footer displays ’ Page " + page ( ) + ’ of ’ + page Count ( ).

Here is the last page of the report, with the total number of males and females in the company displayed.

The sample Data Window object displays three employee i d numbers. Next to each are two radio buttons selected for either Male or Female. At the end of the employee data, the text Number of males is displayed, and next to it, Number of females. Under the text are totals for males and females. The footer displays the text Page 3 of 3.

If you want more information

What if you decide that you also want to know the number of males and females in each department in the company?

To display the males and females in each department:

Select Design>Data Source from the menu bar so that you can edit the data source.
Select Design>Select tables from the menu bar and open the Department table in the Select painter workspace, which currently displays the Employee table with the Emp_id and Sex columns selected.
Select the department_dept_name column to add it to your data source.
Select Rows>Create Group from the menu bar to create a group and group by department name.
In the trailer group band, add two additional computed fields:
```
Sum(If(sex = "M", 1, 0) for group 1)
```
counts the number of males in each department;
```
Sum(If(sex = "F", 1, 0) for group 1)
```
counts the number of females in each department.

Here is what the design of the grouped report looks like.

The sample displays a Header band containing the text Employee I D and Sex, a band for Header group department _ dept _ name that contains department dept name, a Detail band with emp _ i d and radio buttons for Male and Female, and a band for trailer group department _ dept _ name with the text Number of Males and Number of females and the expressions Sum ( If ( sex = " M ", 1 , 0, ) ) and . Sum ( If ( sex = " F " , 1 , 0 ) ). A Summary band displays the text Total number of males and Total number of females. Under the text are the expressions Sum ( If ( sex = " M ", 1, 0 ) for group 1 ) and Sum ( If ( sex = " F ", 1, 0 ) for group 1). A Footer band displays the expression ’ Page ’ + page ( ) + ’ of ’ + page Count ( ).

Here is the last page of the report with the number of males and females in the shipping department displayed, followed by the total number of males and females in the company.

The sample Data Window object displays the title Shipping followed by a list of employee i d numbers. Next to each number are two radio buttons selected for either Male or Female. At the end of the employee data, the text Number of males is displayed, and next to it, Number of females. Under the text are totals for males and females. At the bottom of the object, the text Total number of males is displayed, and next to it, Total number of females. Under the text are figures for the total number of males and females.