Sum of currency field dependent on value of string field 2

[starbase59]

This thread is similar to "Trying to sum by type" posted by frankone on 6/6. I have a string field, Fund_type, and a currency field, Amount. What I want is where fund_type in (equity, hybrid), then add the values in Amount.

Tried:
if {tblMonthlyRsmSummary.FundType} in["Equity", "Hybrid"] then
sum({tblMonthlyRsmSummary.Amount})

This adds all fund_types together, not just equity and hybrid.

Thanks for any solutions.

 

[Madawc]

Try a running total - this lets you sum using a formula, the sort of formula you wrote. The only drawback is that running totals 'run' with the detail line and are only complete in the group footer / report footer.

This should work for any version of Crystal, but it's best to give your version, along with connections when this might be relevant.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[BobSuruncle]

Running totals can be very useful, but should be avoided if possible.
As you stated, Madawc, they can only be used effectively in the Group Footer (or Report Footer).
This is because they are second pass objects.
Because of this evaluation time, they will slow down the displaying of pages (especially annoying if you're viewing successful instances)

Starbase59, your formula should be changed as follows:

if {tblMonthlyRsmSummary.FundType} in["Equity", "Hybrid"] then {tblMonthlyRsmSummary.Amount}

Place this formula field into the detail section (hide it if you wish).
Then, right-click on it a select Insert Summary, and choose Sum as the summarization type.


Bob Suruncle

 

[starbase59]

Example:
Equity Amount
Pacific $3000
Mtn $4000
Central $5000

Hybrid
Pacific $10000
Mtn $ 8000
Central $ 7000

I want a new total for each Pacific, Mtn and Central with equity and hybrid added together, by region.
Pacific would = $13,000, Mtn = $12,000 and Central =$12,000

Running totals add region totals together which I don't want.
Thank you.

 

[lbass]

You might consider using a crosstab, where you use the type field as the column, region as the row, and amount as the summary. This way you can see summaries by type and by region.

-LB