|
|
Subtotal by
|
|
|
Subtotal by
|
|
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 |
|
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."
|
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.
|
FMP Tips Contents
|
 |
