Using a Subtotal in another Formula

[blueboyz]

CR 2008, Pervasive SQL on Windows 7
I have an inventory report that displays the inventory items by vendor.
I created two groups: Group#1 – Vendor, Group#2-Item
I need to get the Inventory Contribution % per Item and per Vendor.
Inventory Contribution % per Item =
({@OnHand$$per Item}/{@Total ReportOnHand$$}) * 100

{@OnHand$$perItem} is placed in GroupFooter#2-Item and the formula is:
{@QOH} * (GetPeachLastCostv2 (Filename, {LineItem.ItemRecordNumber} ))

{@QOH} is placed in GroupFooter#2 and the formula is a special function:
GetPeachQtyOnHandv2 (Filename, {LineItem.ItemRecordNumber} )

The InventoryContribution% per Item is not correct.
An item that has $930 as the OnHand$$perItem, shows 0% when it should show 4.12%
(930/22647 * 100 where 22647 is the OnHand$$perReport)

Further down the report, an item that has $111 as the OnHand$$perItem, shows .51% when it should show .49%
(111/22647 * 100 = .49%)

Here is an example of how my report should look:

Vendor/Item QOH OnHand$ Inv Contrib %
ABC Group Header#1
AVRY 50 250 7.69% Group Footer#2
EQWT 100 1000 30.77% Group Footer#2
NURS 200 400 12.31% Group Footer#2
TOTAL ABC 350 1650 50.77% Group Footer#1

DEF Group Header#1
SOIL 10 100 3.08% Group Footer#2
EQLW 5 500 15.38% Group Footer#2
TOOL 100 1000 30.77% Group Footer#2
TOTAL DEF 115 1600 49.23% Group Footer#1

Total 465 3250 Report Footer

There seems to be a problem in obtaining the OnHand$$perReport amount.
Any help is greatly appreciated.

 

[Madawc]

Try creating a few diagnostic detail lines, to find out what's happening to the values you are doing your calculations with.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[blueboyz]

I did find a problem in the formula for calculating the inventory contribution %.

The formula is:
EvaluateAfter ({@Display ReportOnHandAmount});
If {@On Hand $$} <> 0 and {@Display ReportOnHandAmount} <> 0
then ({@On Hand $$}/{@Display ReportOnHandAmount}) *100;

It is pulling the running total amount for {@Display ReportOnHandAmount}.

Do you know how I can pull the report total for the OnHandAmount?

 

[Madawc]

Try creating a summary total rather than a running total. The use of Crystal's automated totals is outlined at FAQ767-6524, including the strengths and weaknesses of the different types.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[blueboyz]

I have to create a manual running total (Reset, Accumulate, Display) because a summary total would include inventory items that may appear twice or more in the detail section. The total for the inventory item on hand amount should only include the item once.

For example, if item AVRY on July 31, 2011 has 10 units in stock, then the on hand amount should be 10 * $50 (cost) = $500. If AVRY was sold on invoice #1 with 5 units and invoice #2 with 5 units, the summary total adds the 10 Units in stock twice because it appears on the detail line twice.

 

[Madawc]

Sorry, I see no way on that basis. Can anyone else help?

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP