Running totals for invoice cost per purchase order 1

[koolskins]

CR 9.2

I am attempting to run a report that shows total commitment dollars per purchase order with total paid invoices and total unpaid invoices against each purchase order. The total po commitment is actually a sum of the individual line items that make up the po. I have a running total on the commitment value and am not having a problem with that column. My problem is attempting to get the correct invoice values for the paid and unpaid invoices recorded against each purchase order number. The table/columns involved are:

purchase order:
rp700.rp_id purchase order ID
rp700.comm_cost po line item commitment

invoices:
ap620.rp_id ap purchase order ID
ap_620.net_amt ap invoice net value
ap_620.status ap invoice paid or unpaid


What is correct syntax in the running total construct to get the correct running total for only PAID invoices for each purchase order? I've tried summing the net_amt and evaluating when the group rp_id changes and resetting when the rp_id changes, but this doesn't work when there are many invoices to the same po and it also won't give me only the paid or unpaid invoices...

Any help much appreciated...

Randy

 

[lbass]

I'm unclear what "ap invoice net value" is. If it is the amount of the particular invoice, then your running total should be a sum of {ap_620.net_amt}, evaluate based on a formula:

{ap_620.status} = "Paid"

Reset on change of group (PurchaseID), assuming you have grouped on this field. This will give you the total for paid invoices per purchase order. For the unpaid invoices, change the status to "Unpaid" in the evaluation formula.

-LB

 

[koolskins]

Thanks, this is getting me closer. The report has a grouping of VENDOR and then PO NUMBER. My running total for the PO NUMBER group is giving high numbers for a given purchase order when more than one invoice is involved for a particular po. I am resetting the RT on the PO NUMBER and evaluating on the formula {table.ap_status}="PAID". Any thoughts on what is making the RT giving wrong totals?

 

[lbass]

I'm guessing that the multiple records in the Purchase Order table are causing duplication of records from the Invoices table. You might try changing the resets to "on change of {rp700.comm_cost}," although it would be better if you had a separate ID for line item cost in case the values of two separate line items were the same. I'm guessing your detail section might look like:

PO ID PO LI Comm AP Inv Net AP Status
1 300 25 Paid
1 50 Paid
1 475 Unpaid
1 250 25 Paid
1 50 Paid
1 475 Unpaid

The easiest way to troubleshoot running totals is to lay out the fields involved in the running total in the details section and then copy the running total into the details section so you can determine what records it is counting. If you are still having trouble, please try this and then report back with a sample of the results of this exercise and we can go from there.

-LB

 

[John43]

Just a question, but have you tried subreports?
It may help in tracking down some of the differences
you are currently having, and may even provide a solution.

 

[koolskins]

Thanks John43, I've played with subreports a bit but have two problems when I use them. They are very slow as they do a separate select on every detail record and the volume of data I'm parsing is fairly large and second, my end-user is going to want to export this data to excel and subreport data doesn't seem to work with this....

lbass' last suggestion really helped and I did shrink my data select down to one vendor and looked at my detail rows that I had been suppressing. Sure enough, the purchase order line item select was summing multiple rows and these rows were duplicating the ap values. I don't really understand this as my table joins went from the PO master table, for which there is only one record per po directly to my ap table and then a join between the PO master table and the po line item table. I would have thought that the po line items would not have been included in any way to my ap data???? Anyway, I suppressed dups and that solved that problem.

One additional question though. I've used the three formula technique to get the ap data, but the following sum forumla is only returning one invoice value per po number, can anyone tell me why?


whileprintingrecords;
global numbervar ap_p_net;
if {ap_mast_620.ap_status} = "P" then
global numbervar ap_p_net := ap_p_net+sum({ap_mast_620.net_amt},{ap_mast_620.rp_id});


Thanks for the help guys, hope everyone is having a great holiday season.

Randy

 

[lbass]

Where are you placing this formula, and what are you trying to accomplish? This looks like a group level formula.

-LB

 

[IdoMillet]

You don't specify how you are joining but it sounds like if an Invoice has 3 line items, the values from that Invoice would get "replicated" 3 times.

The solution is to group within PO by Invoice. In the running total, set the evaluate option to once per each Invoice. This would ensure that the ap_620.net_amt gets evaluated only once from each invoice.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[koolskins]

lb...I'm trying to simply get the total amount paid for each purchase order. I have multiple invoices against each purchase order, but not multiple line items against each invoice (as Ido pointed out) but the above formula is only returning one invoice value for the given PO. The report right now has 4 groups:

Vendor
PO number
PO_ID number
AP PO_ID number

The Vendor and the PO number groupings are for drill down capability and to ultimately provide all purchase orders and amounts paid at the vendor level. The PO_ID and AP PO_ID are internal id's assigned to each record that the system uses to match records. Both of these id's point to the purchase order itself. So if my purchase order record has an id of 1000, then any invoice with a PO_ID of 1000 is for that purchase order. I could use the PO NUMBER instead, but my system has additional data elements such as contract and project numbers which also come into play. However, the ID numbers transcind those layers so the joins and selects are easier.

I have a reset formula to declare and set the variable "ap_net" to 0. I have a sum formula noted before that should sum all invoices for a given AP PO_ID and then a print formula to print that sum.

I have tried putting those formulas in all combinations of the group headers and footers and just cannot get them in the right places to accurately sum all invoices payments for a given purchase order. I either get one invoice only per purchase order, or apparently all invoices against everything.

I've also tried this with running totals in many different combinations, but can't get that to work either. The only additional logic for that, as noted in my sum formula, is I'm looking for two different groupings of invoices with a PAID or OPEN status, so in a RT, that would have to be part of the evaluate formula.

 

[lbass]

The following reset formula belongs in the AP PO_ID group header (You don't need to specify "global", since global is the default):

whileprintingrecords;
numbervar ap_p_net := 0;

The accumulating formula belongs in your detail section, and should look like:

whileprintingrecords;
numbervar ap_p_net;
if {ap_mast_620.ap_status} = "P" then
numbervar ap_p_net := ap_p_net+ {ap_mast_620.net_amt};

Your display formula should be placed in the group (AP PO_ID) footer and should look like:

whileprintingrecords;
numbervar ap_p_net;

You didn't really need to use the three-formula method, since the running total expert would have been simpler. If your data (which you still haven't shown us) is repeating so that you have duplicate records within each AP PO_ID group, you would need to change the formulas to accommodate this. If this is the case, please provide sample data (a visual layout of results for at least two AP PO_ID numbers) AND also tell us whether you have some invoice ID field which identifies specific invoice numbers so that you can distinguish between duplicate invoice amounts.

-LB

 

[koolskins]

okay, here are the table and data layouts:


PO_LINE_ITEM_TABLE
rp_id (unique po id)
rl_id (unique po line item id)
commited_amount
received_amount

rp_id rl_id committed received
898465 900200 1000.00 500.00
898465 900201 2000.00 2000.00
898500 900450 500.00 250.00
898600 900600 1000.00 500.00

AP_INVOICE_TABLE
rp_id (ties invoice to purchase order)
iv_id (unique invoice id)
status (either P or O for Paid or Open)
net_amount

rp_id iv_id stat net_amount
898465 200150 P 250.00
898465 200151 P 250.00
898465 200152 O 2000.00
898600 200500 P 500.00
898600 200501 P 250.00
898600 200502 O 250.00

The report should show:

rp_id committed received paid open

898465 3000.00 2500.00 500.00 2000.00
898500 500.00 250.00 0.00 0.00
898600 1000.00 500.00 750.00 250.00

I've started this report over with a very simple join and data select. I've joined these two tables with a left outer join to the ap table as not all purchase orders will have an ap invoice against it. I have grouped the PO table rp_id, then the PO table rl_id, and then the AP table rp_id.

I have potential multiple po line items in the purchasing table and potential multiple invoices against a purchase order. I need for each po, the sums of the committed and received amounts calculated from the individual line items of the purchase order and the sum of the net amounts for each purchase order by PAID and OPEN status.

When a po has multiple line items, it is multiplying the ap values returned and when there are multiple invoices against a purchase order it is duplicating the po values. I've tried running totals and the three formula techiques, but can't seem to get the right combination down in either case, although when working with just one of the tables, I don't have any problem at all with either technique. I know the join is my problem but can't seem to figure out how to overcome it.

Randy

 

[lbass]

Using the running total editor, here is how you would set up the running totals for each:

1-{#Committed}:
Select {PO.commamt}, sum, evaluate on change of group (PO RL_ID), reset on change of Group (PO RP_ID).

2-{#Received}:
Select {PO.received}, sum, evaluate on change of group (PO RL_ID), reset on change of Group (PO RP_ID).

3-For "Paid" and "Open", do the following:

Create a formula {@netamt}:

if isnull({AP.Net_Amt}) then 0 else {AP.Net_Amt}

{#Paid}:
Select {@netamt}, sum, evaluate based on a formula:

{AP.status} = "P"

Reset on change of {PO.RL_ID}.

{#Open}:
Select {@netamt}, sum, evaluate based on a formula:

{AP.status} = "O"

Reset on change of {PO.RL_ID}.

Then create two formulas so you can display 0's for null running totals:

{@paid}:

if isnull({#paid}) then 0 else {#paid}

{@open:

if isnull({#open}) then 0 else {#open}

Substitute the correct field names, and place {PO.RP_ID}, {#committed}, {#received}, {@paid}, and {@open} in the Group (RP_ID) Footer.

This assumes that your data is displaying like the following:

RP_ID RL_ID Comm Rec IV_ID Stat NetAmt
898465 900200 1000 500 200150 P 250
898465 900200 1000 500 200151 P 250
898465 900200 1000 500 200152 O 2000
898465 900201 2000 2000 200150 P 250
898465 900201 2000 2000 200151 P 250
898465 900201 2000 2000 200152 O 2000
898500 900450 500 250 null null null
898600 900600 1000 500 200500 P 500
898600 900600 1000 500 200501 P 250
898600 900600 1000 500 200502 O 250

-LB

 

[koolskins]

That did it! Thanks a million for your help and patience.

Randy

 

[koolskins]

Okay, I spoke too soon. lbass' recommendations worked fine for the summary values at the purchase order level. However, the report's primary grouping is by vendor and I need group totals and grand totals for these columns also.

I thought the running total syntax for the vendor group would have just been resetting by the vendor id but that is not working. It's only adding the last po line for any given vendor. What should the correct RT or forumla be to get vendor level totals and grand totals for the A/P data?

Randy

 

[lbass]

{#Committed} and {#Received} should be fine if you create two of the same running totals and just change the reset to "on change of Group (Vendor)", with placement in the Group (Vendor) Footer. For the grand totals for these two, create the same running totals and change the reset to "Never."

For the other two formulas, you now need the three-formula method:

{@reset} to be placed in the Group (RP_ID) Header:
Whileprintingrecords;
numbervar paid := 0;
numbervar open := 0;

{@paidopen} to be placed in the Group (RP_ID) Footer:
whileprintingrecords;
numbervar paid := paid + {@paid};
numbervar open := open + {@open};
numbervar grtotpaid := grtotpaid + {@paid};
numbervar grtotopen := grtotopen + {@open};

You need four display formulas (the display formula is the "third formula" of the three-formula method):

Place these two in the Group (Vendor) footer:

//{@displpaid}:
whileprintingrecords;
numbervar paid;

//{@displopen}:
whileprintingrecords;
numbervar open;

Place these in the report footer:

//{@displgrtotpaid}:
whileprintingrecords;
numbervar grtotpaid;

//{@displgrtotopen}:
whileprintingrecords;
numbervar grtotopen;

-LB

 

[koolskins]

lb, works like a charm. The only problem was the reset formula, I believe you meant to put that in the vendor group header. Anyway, it appears to be working fine with that slight alteration. Thanks again for your help.

Randy

 

[lbass]

Sorry, yes, I should have said the vendor group header...Glad it worked.

-LB