I'm using formula A inside formula C, but why is formula A returning 0

[srogers]

Using Crystal 7
I sort my report by project.
In my report I have a formula for TotalNetRevenue and one for Costs. I also have a formula that subtracts those two and I call it GrossMargin. To get the right number I have to Insert, subtotal, (by project) and use that formula. They are working fine and are located in the project group footer.

The problem:
I created another formula (to calculate gross margin percent):

If {@TotalNetRevenue} > 0
Then
({@GrossMargin} / {@TotalNetRevenue}) * 100
Else
0

TotalNetRevenue does not return as zero for the project I'm looking at but in this formula it does. If I replace Else 0 with Else 9 I get a 9. (If I do an Insert, subtotal I get a five digit number, but its not what I get when I do the calculation manually).

I tried summing the TotalNetRevenue like this:

If Sum({@TotalNetRevenue}) > 0
Then
({@GrossMargin} / {@TotalNetRevenue}) * 100
Else
0

But when I try to run the report I get this error:
A summary has been specified on a non-recurring field

Any ideas? (hope this wasn't too confusing)
Thanks,
Sharon

 

[Andie]

You may want to try "WhilePrintingRecords" at the start of your last formula (gross margin percent). This will force the formula to be calculated while the report is printing the records, rather than while it is reading the database.

 

[kenhamady]

We may need to see the other formulas. Post them if you need further help. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[FoxG]

Hi Sharon,

try using the EvaluateAfter function for all formulae used within the second formula

ie. Evaluateafter({@TotalNetRevenue});
Evaluateafter({@GrossMargin});
.
. Your code
.

Geoff

 

[srogers]

Thanks Andie - I tried the WhilePrintingRecords; (shown commented out below) and received this error message "A summary has been specified on a non-recurring field."

Thanks Geoff - Here's how I changed it after your suggestion:

If EvaluateAfter({@TotalNetRevenueBilled}) <> 0
Then
(EvaluateAfter({@GrossMarginBilled}) / EvaluateAfter({@TotalNetRevenueBilled})) * 100
Else
0

But it resulted in a zero. (afterward I changed Else 0 to Else 9 and it resulted in a 9)

Here are the formulas (with their complete names - I left the word "billed" off last time):

GrossMarginPercent:

//WhilePrintingRecords;
If {@TotalNetRevenueBilled} <> 0
Then
({@GrossMarginBilled} / {@TotalNetRevenueBilled}) * 100
Else
0

GrossMargin:

{@TotalNetRevenueBilled} - {@TotalDirectCostsBilled}

TotalNetRevenueBilled:

{@TotalRevenueBeforeDiscBilled} + {@WriteOffsAndDiscountsBilled}

TotalDirectCostsBilled:

{@DirectEmployeeCostTotalBilled} + {@DirRegCostContractorBilled} + 
{@DirProductCostBilled} + {@DirCostExpensesEmpPaidBilled} + {@DirCostExpensesLUMTpaidBilled}

I tried a cross-tab with my GrossMarginPercent formula (right now I'll try anything) and got some results I didn't understand. The cross tab looked like this:

Project 1
4.40 118.80
11.33 340.00
16.00 992.00
39.05 117.14
and several other rows down to
100.00 300.00

The numbers in the 4.40 column are GrossMarginPercent and the numbers in the 118.80 column are Sum of GrossMarginPercent. This cross-tab is in a group footer section of my sort by Project group. I really only wanted one percent per project and none of the ones shown is correct.
For Project 1 here's the results I get for the formulas above:
GrossMarginPercent: 0.00 (I should be getting 34%)
(sum of GrossMarginPercent: 6,624.39)
Sum of GrossMargin: 63,995.00
Sum of TotalNetRevenueBilled: 185,400.00
Sum of TotalDirectCostsBilled: 121,405.00

Thanks so much for looking at this with me.
Sharon

 

[kenhamady]

Sharon,

The layers of formulas make it hard to troubleshoot without looking at the actual report, a level of support that I can only provide to my customers.

But rereading your first post, it sounds like you are trying to get a percentage on each Group Footer, but you arn't using Group summary values, you are using the Detail values. Put the fields used in the formula on the GF and see if they are the values you expect. I think you need to be using your subtotal in the formula. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[FoxG]

Hi Sharon,

The EvaluateAfter was supposed to be on a line of its own at the start of the relevent formulas that use them.

I'm suprised that the formula you generated saved OK.

Geoff

 

[srogers]

oops - I tried it without looking it up in Help for the right syntax. sorry.

Now I added these lines to the beginning of the formula:
EvaluateAfter({@TotalNetRevenueBilled});
EvaluateAfter({@GrossMarginBilled});
and left the rest of the formula alone.

I still got zero, but maybe I need to do this on more formulas.

I have something to try at least. I'll keep working on it.

Thanks so much for your time.
Sharon

 

[srogers]

Thanks for everyone's help and ideas.

I finally got it.

if sum({@TotalNetRevenueBilled}) <> 0 
then 
 Sum({@GrossMarginBilled}, {@ProjectNameAndID}) / sum({@TotalNetRevenueBilled}) * 100
else
0