Function to get Total of filtered field

[jimlongo]

I have a Transaction table. It has the fields "TransactionType" and "TransactionAmount". The field TransactionType contains a Value List.

JM

FM

AM

Is it possible to get the SUM of just the amounts with the AM type?

Thanks,

jim

[bcooney]

There are several approaches to this:

1. Create a summary field, Total of TransactionAmount. Find all transactions of type "AM" and the summary field (since its result reflects the found set) will show your total.

2. Put the summary field and the TransactionType field in a sub-summary part by Transaction Type. Sort by Transaction type and each sub-summary section will display a total for that type.

3. Create a filtered selfjoin relationship by transaction type.

[jimlongo]

There are several approaches to this:

1. Create a summary field, Total of TransactionAmount. Find all transactions of type "AM" and the summary field (since it's result reflects the found set) will show your total.

2. Put the summary field and the TransactionType field in a sub-summary part by Transaction Type. Sort by Transaction type and each sub-summary section will display a total for that type.

I think I need to explain further. I already have a Summary Field, Total of Transaction Amount - a Grand Total if you will.

Now on the same layout I want to break down the grand total into its subsets of AM, JM, FM

My fuzzy logic tells me it should be

Sum of TransactionAmounts where TransactionType=AM

but I guess things aren't always that simple :

3. Create a filtered selfjoin relationship by transaction type.

I'll look at this and see where it goes.

Thank you.

Edited November 24, 2010 by Guest
added some wishful thinking

[bcooney]

"Now on the same layout I want to break down the grand total into its subsets of AM, JM, FM"

That's exactly what a subsummary report is. By placing a summary field in a subsummary part, you'll get totals by the break field.

[Vaughan]

I already have a Summary Field, Total of Transaction Amount - a Grand Total if you will.

This summary field will display a grand total ONLY if it is in a leading or trailing GRAND summary part. As Barbara pointed out, if it is in any other sub-summary part it will display the sub-total for that break.

[jimlongo]

Thanks, I was just reading the documentation on how SubSummary reports work when you responded.

Marvellous.

[bcooney]

Keep in mind that subsummary report totals reflect the found set. For example, if you find first for a date range, your totals will change (and that's useful).

[jimlongo]

Keep in mind that subsummary report totals reflect the found set. For example, if you find first for a date range, your totals will change (and that's useful).

Yes I would want that.

[jimlongo]

Okay one more if I could indulge you . . .

I got the subSummary and a Trailing Grand Summary and it looks like this

Can you give me a clue how I would lay it out to do this, so that the SubSummaries are further divided. This is just a graphic edit.

[comment]

What differentiates between the two groups?

[Vaughan]

To add to Comment's post: you'll need to have another field that allows the two groups to be differentiated, and a sub-summary based on that field to create the grouping in the report.

[jimlongo]

Thanks for all the help and clues everyone.