Conditional group SUM in Crystal Reports

Question

I've been doing some accounting reports and have been summing up my different currencies using a formula

IE

CanadianCommissionFormula

if {myData;1.CurrencyType} = "CDN" then
    {myData;1.Commission} 
else
    0

CanadianCommissionSum

SUM({@CanadianCommissionFormula})

Then I'd just display the CanadianCommissionSum at the bottom of the report and things were great.

I've just come across the requirement to do this, but grouped by Sales Rep. I tried using my previous formula, but this sums for the whole report. Is there an easy way to sum like this, based on which group it's in?

Solution

You probably figured this out a year ago, but just in case, try this:

1. Change your CanadianCommissionSum formula to

   SUM({@CanadianCommissionFormula},{SalesRep})

2. Put this formula in your SalesRep's Group Footer section.

This should now display properly.

Comments

[Nathan Koop]

that, unfortunately gives me the sum for all the sales reps

[Nathan Koop]

how do I get it to reset on the change of the group?

[OMG Ponies]

Then you aren't grouping by sales rep, or the sales rep info isn't what you think it is.

[OMG Ponies]

You can setup running totals through functions, or use the Running Total feature in the same widget where you list the functions/etc.

[Nathan Koop]

When I look at the Group Expert I see the field SalesRep in the "Group By" portion. Then, using the formulas I've got listed above, I add the formula from your answer in the GroupFooterSection1 area, it then displays the total SUM for all Canadian Commissions, not the individual rep.

[OMG Ponies]

So on the report, you see a group header above the details section, and the corresponding group footer underneath - the formula is in the group footer section...

[OMG Ponies]

Is any other data relating to sales rep coming out properly in the grouping?

[Nathan Koop]

Yep, the group header is above the details section and there is a group footer, everything else displays great.

[Nathan Koop]

I don't want to waste too much of your time, I thought of another way to resolve this issue. I can add two more columns in my SQL query and a couple of CASE statements. IE CASE currency WHEN "CDN" THEN commissionAmount ELSE 0 END AS CdnCommission. Then I can sum up this in the group.

[Arushi Rajput]

When you set up the new running total field you will see a "Reset" section which is where you set it to "On change of group" and then select your group. This will reset the field back to zero every time that group changes.

[OMG Ponies]

By all means, do as much as you can in the SQL. See if your db supports WITH ROLLUP on the GROUP BY clause - the output might help even more.

[Hammad Khan]

this helped me a little but I have a question here if you can answer stackoverflow.com/questions/7987228/crystal-report-group