Trouble with summing a formula

[SSSFisgard]

Hi guys,

I'm working with Crystal Reports XIR2, and a SQL Server database.

What I'm having isssues with, is summerizing a formula. I've done it in the past with no problems but I'm having a hell of a time doing it now.

What I have is 2 formulas {@yield1} & {@yeild2}. What I need to find out is how many records of {@yield1} are greater than {@yield2}.

What I have done so far is created an if then else statement thats says if {@yield1} > {@yeild2} then 1 and then try and sum on that formula. Thats where I get "This field cannot be summarized".

Any idea how I can get around this.

Thanks

 

[lbass]

Can't help unless you show the content of each formula, including the content of any nested formulas.

-LB

 

[SSSFisgard]

K, I'll list the content of the formulas below

Overall Yield : {@annualized earnings}/{Mortgages.Principal} * 100
Portfolio Yield : Sum ({@annualized earnings})/Sum ({Mortgages.Principal})*100

Nested formulas are:
Annualized Earnings : {@interest per year} + {@net fee annualized}
Interest Per Year : {Mortgages.Principal}*{@EAR}/100
Net Fee Annualized : ({@Net Fee}/{Mortgages.Term}) * 12
EAR : ((((({Mortgages.InterestRate}/100)/{@compounded per year})+1)^{@compounded per year}) - 1)*100
Net Fee : {Mortgages.AdminFee} - {Mortgages.FinderFee}

I need to know how many times Portfolio Yield is larger than Overall Yield.

I hope this is all you need.

 

[lbass]

You didn't show the content of {@compounded per year}, but I can see that your Portfolio yield formula is one reason you cannot summarize, since it already contains summaries. Are you certain that this is the comparison you want to make--a row value for overall yield with a summary of portfolio value? If so, then use a variable to the count, like this:

whileprintingrecords;
numbervar cnt;
if {@portfolioyield} > {@overallyield} then
cnt := cnt + 1;

Then display the result in the report footer:
whileprintingrecords;
numbervar cnt;

If you are evaluating at some group level, then the last formula belongs in the group footer, with a reset in the group header:

whileprintingrecords;
numbervar cnt;
if not inrepeatedgroupheader then
cnt := 0;

-LB

 

[SSSFisgard]

Thanks lbass,

that works great. Can I throw one more at you. Now I need to find out the total dollar volume of those exceeding the yield. Can I do a running total?

exp: mortgage 1 - $ 75000
mortgage 2 - $ 80000
mortgage 3 - $ 50000

result: $205000

hopefully i explained myself

 

[lbass]

Add another variable to your detail level formula like this:

whileprintingrecords;
numbervar cnt;
currencyvar amt;
if {@portfolioyield} > {@overallyield} then (
cnt := cnt + 1;
amt := amt + {table.mortgageamt}
);

Then in the report footer use:

whileprintingrecords;
currencyvar amt;

This assumes taht the mortgageamt field is a currency datatype. If it is a number, change currencyvar to numbervar.

-LB