Strange behaviour in group footers in reports

brucem

Hello again ;-))

I have an annoying problem that is causing me grief to do with totalling etc in reports.

My report footer totals three data item fields to produce a total for sales amount and purchase amount.

one fields source expression is
ItemTotILE1Profit+ItemTotILEShipInv1Profit+ItemTotILECreditsProfit

the other is
-ItemLedgerEntry1.Amount-ILEShipInv1.Amount-ILECredits.Amount

I want then to show this as a percentage

if i create a field which has a source

(ItemTotILE1Profit+ItemTotILEShipInv1Profit+ItemTotILECreditsProfit)/(-ItemLedgerEntry1.Amount-ILEShipInv1.Amount-ILECredits.Amount))*100

I get a division by zero error even though all the field totals are NON ZERO.

I would guess that it is something to do with how navision is deciding to total which fields.

I have tried creating variables and running the calc is code, but still the error. I have also tried using control1/control2 but this does work.

the only thing else i should perhaps mention is that the system is using the createtotals function on these fields for report totals.

any help gratefully recieved.

Bruce

mfabian

Do you really want to tell us that NF reports a "Division by Zero" error if you calculate Control1/Control2 and Control2 is not Zero?

No way!!! There MUST be a situation where control2 is zero. Probably in one of the footer sections. I would definitely avoid putting a division in a source-expression of a form. Instead create a function like

Function Divide (a,b : Decimal) : Decimal;
If b = 0 then
exit (0)
else
exit (a/b)

And use this function as source-expression.

Marcus

---

Marcus Fabian
m.fabian@thenet.ch
+41 79 439 78 72

brucem

Marcus,

thanks for the reply !

I'm not saying that Navision is reporting an error when dividing one control by another when both are not zero ( at least not as such)

All i can say is that the report without the calculation in the footer produces the error, even though the preview shows the totals i want to divide to ALL be non zero.

Now like i said previously this may have something to do with the order that Navision is calculating the totals.

If Navision is calculating the value for the % expression before it has totaled the sub values, then I agree that one if not both values are zero, hence the error.

If Navision is calculating the % after the subtotals then there should be no error.

If out of interest you would like to see the report let me know and i'll send you a copy.

Regards

Bruce

brucem

Hello Again !!

Ref this report, I have been investigating this a bit more and I now know what is causing ot, but not why!!

Although the calculation to get the totals is taking place in the group footer the group footer itself is being visited on what would appear to be every iteration of the data item itself. This might be by design, but it is what is causing the problem.

I can demonstrate this by popping up a message box in the post section of the group footer containing the values i want to divide. In a report where i expect to see 5 values, i appear to be getting a say 10 or twelve Zero values followed one correct answer.

Why this occurs i am not sure, but i would have thought that the group footer would only have been visited at the end of each group.

The base for the report is

Item
Item Ledger Entry
Item Ledger Entry
Item Ledger Entry
Item Ledger Entry
Item Ledger Entry
Item Ledger Entry


The Item table is grouped on Item No and this is where the group totalling is occuring.

I have worked around it by using the check for zero values function, but i am still confused as to why the grouping should be happenig / not happening ??

Bruce

mfabian

The order how Navision (and any other programming language except apl) resolves functions like yours is clearly defined:
- Brackets are resolved first
- Dot-Operations (multiplication , division) comes next
- dash-operations (substraction, addition) comes last

Your example in the original post is not clear in the point whether the multiplication with 100 at the end would multiply the previous result or the divident. In fact this statement would cause a syntax error anyway as the numbers of open and closed brackets are not equal.

But this cannot be the problem. The important point is the value of (ItemLedgerEntry1.Amount-ILEShipInv1.Amount-ILECredits.Amount). If this value is Zero you're crashed, otherwise not.

About the Group footers: You are right with your complaints. Navision's behaviour is not very logical in this point. You have to determine the ShowOutput yourselves checking the TOTALSCAUSEDBY value.
I recommend you let the navision wizard create a similar report with groupings and look at the code generated.

Marcus

---

Marcus Fabian
m.fabian@thenet.ch
+41 79 439 78 72

[This message has been edited by fabian (edited 02-02-2001).]