Adding amount from formula in Grp Footer#2 - Display in Grp Footer#1 1

[blueboyz]

I am using Peachtree Quantum 2011 and Crystal Reports v2008. Peachtree uses Pervasive SQL as its database engine.

I designed a report to show the items sold. The report is grouped by vendors that supply the item(s). The report also displays the current quantity on hand for the item. If the item is sold more than once in a given period the report lists the current quantity on hand again. The current quantity on hand is the result of a special function and will only result in the current quantity on hand for the last day of the current accounting period. Therefore, I put the current quantity on hand function in the Group Footer #2 {LineItem.ItemID} and suppressed the detail section.

Vendor Item ID QOH Units Sold On Hand $
A 123 10 25 $100
A 456 20 10 $200
A 789 50 5 $250
Total for A 80 40 $550
(The Total for A is showing 50 for the total QOH instead of 80.)

I need to add the current quantity on hand in each Group Footer #2 and place the sum in the Group Footer #1 {LineItem.VendorRecordNumber}.

The report is displaying the wrong amount in Group Footer #1 no matter what I do. Right now, it is displaying the last value in the Group Footer #2.

My formula for the current quantity on hand is:
{@QOH} = GetPeachQtyOnHandv2 (Filename, {LineItem.ItemRecordNumber} )
The formula is placed in the Group Footer #2.

The formula for the current quantity on hand for all the items under a vendor is:
{@QOH per Vendor} =
WhilePrintingRecords;
NumberVar QOHperVendor := ({@QOH});
It is placed in the Group Footer #1.

I am happy to attach the report file to an email if anyone needs it.

How can I get the correct quantity on hand for each vendor?

Thank you for your assistance.

 

[MCuthill]

blueboyz,

I think you just need running total formula setup to achieve this.

{@Reset_QOHperVendor} -- place this in the Vendor Header

WhilePrintingRecords;
NumberVar QOHperVendor:=0;

{@Accumulate_QOHperVendor} -- place in details

WhilePrintingRecords;
QOHperVendor:=QOHperVendor+{@QOH};

{@QOH per Vendor} -- place in Group Footer
Change to as follows:

WhilePrintingRecords;
NumberVar QOHperVendor;

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[blueboyz]

Thank you, Mike! Your posts helped lead me in the right direction.
Here's what I did:
{@Reset VendorQOH} (placed in the GroupHeader#1 - Vendors)
WhilePrintingRecords;
NumberVar VendorQOH := 0;

{@Accumulate VendorQOH} (placed in the GroupFooter#2 - Items)
WhilePrintingRecords;
NumberVar VendorQOH := VendorQOH + {@QOH};

{@Display VendorQOH} (placed in the GroupFooter#1 - Vendors)
WhilePrintingRecords;
NumberVar VendorQOH;

There is also a formula {@QOH} in the GroupFooter#2 - Items.

{@QOH}
GetPeachQtyOnHandv2 (Filename, {LineItem.ItemRecordNumber} )

This is the special function for retrieving the current quantity of hand of an item for the last day of the current accounting period.

I couldn't place {@Accumulate VendorQOH} in the details because the inventory item can apppear more than once, but the current quantity on hand for the item is always the same. The accumulation would not be correct since it would add the QOH for item 123 of 10 if it appeared 3 times in the details section and display 30 when it should be 10.


Thank you, thank you, thank you.
I've been working on this report too long and sometimes it just takes a fresh set of eyes on the problem.

 

[MCuthill]

You are most welcome BlueBoyz, glad I could help!

Methinks I must have misinterpretted the structure of the report, but luckily these type of formulas are quite dynamic and can be quite easily modified for different applications. I have long since lost count of how many times I have used the Reset-Accumulate-Display set of formulas.

Have a great weekend!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."