# Subtotal by pay period and account

### Chong-ho (Alex) Yu, Ph.D., MCSE, CNE

Problem:

I have a timecard database. In the payroll different employees are paid by different accounts. I would like to see the sumtotal of each account within a pay period on the Web. The search result should look like the following table:

 date name rate hours total account subtotal by account 1.9.98 Alex Yu \$ 7.75 40 \$ 310 AB11011 \$ 940 1.9.98 Bill Clinton \$ 8.00 40 \$ 320 AB11011 \$ 940 1.9.98 Al Gore \$ 7.75 40 \$ 310 AB11011 \$ 940 1.9.98 Bill Gates \$ 6.00 20 \$ 120 CD22000 \$ 312 1.9.98 Steve Job \$ 9.60 20 \$ 192 CD22000 \$ 312

Solution:

First, create a calculation field named "date_account", which concatenates the date and the account. In the formula you must cast the variable type of "date" from date to text by using the function "DateToText."

 ``` DateToText(date)&account```

Next, create a summary variable named "sumtotal." Choose "total" as the summary option and the total pay as the summing variable (see below). This field just adds up all pay amount but does not give you a subtotal by group.

To get the subtotal of account by date, create another calculation field entitled "sum_by_date_account". Then extract the subtotal from "sumtotal" by using the function GetSummary with the subgroup "date_account."

 ` GetSummary(sumtotal, date_account)`

However, the subtotal will not take effect unless the data are sorted by "date_account." You can pre-determine the sort order in "search.html" by adding the following line into the webpage. Now the search result could present the result as what you expect.

 ``` ```