PDA

View Full Version : sort or filter summary calculations in a report


ABC123

Kari Kohlhaas
01-19-2005, 09:58 AM
We are a non-profit organization which uses a5v5 for our membership database. We have a set which contains 2 tables - one which stores each member's idno, name, address, etc, and another table (linked thru idno) which has 1 to many links of their contributions.

EXAMPLE: TABLE 1 0001 Smith Bob 10 maple ave

Table 2 0001 11/01/2004 auction $200.00
06/01/2004 flower sale $100.00
04/20/2004 Derby Day $500.00
02/15/2004 Membership $100.00
04/20/2003 Derby Day $500.00
01/01/2003 Membership $60.00

We are trying to generate a report which list only members whose total annual contribution is over $2500 (for example). I can use the reporting software's summary calculation to get the total of each individual's gifts over the year, but have been unable to perform any SORT or FILTER operations on summary calculations in the report. How can I get this information?

I have been writing it to a file and then using EXCEL to sort by total contributions. This is inconvenient. Any ideas?
Kari

Stan Mathews
01-19-2005, 10:13 AM
Those calculations are not available for sorting or filtering. The calculation for the total of the contributions of the last member in the parent table is not performed until his/her records are encountered.

The best avenue is probably to design a summary operation, creating a new table, and base the report on the summary table. It will then be a simple matter to filter on a value in a field in the (single) summary table.

Bill Warner
01-19-2005, 10:33 AM
Here's a way to do it (I just tried it successfully):

Create a group in the report, and then a calc field (TTL):

IF(TOTAL(AMT,GRP-"JOBNO)"100,TOTAL(AMT,GRP-"JOBNO),0)

In this example, AMT is the amount field, and JOBNO is the linking field to the parent. This calc will only show non-zero amounts for those members whose total contributions are over $100.

Next, place the field on the report in the JOBNO group, and set the format to "blank if zero". Then set the property of that group (region) to shrink contents.

With only this field on the report, you will only see the amounts over $100. You'll probably need other fields, so you can set them as calc fields to show a value if CALC-"TTL is " 0, and "" or 0 if not.