Complex Running Totals

[MeGGaN]

I'm modifying this report which has become very complex. My user has made me modify it lots of times since he always finds some calculation wrong.

It worked fine until he told me he rather prefered it grouped by article id (which was on a details section) instead of order number, which was Group#1.

I'm talking here about orders that create 1 or more shippers (depending on the existence). Sometimes, shippers aren't shipped complete because existence wasn't enough to complete the order. (I know this might sound familiar for many of you). I don't know the exact words in english but my report is trying to find all quantities of articles that were not shipped because:

1. There was no existence
2. Existence wasn't enough to complete picking list.

I have several columns and some are calculated based on the existent ones:

The layout is like this:

Qty Ordered/Qty on Shipper/Qty on Invoice/Qty on Shipper vs Qty Ordered/Qty on Invoice vs Qty Shipped

A tipical scenario for article X would be (on that order):

10 / 7 / 5 / 3 / 2

This means that a client ordered 10 units, a shipper was made for 7 units but the invoice turned out only for 5. So, 3 units were not on shippers and 2 units (that were on the shipper) did not made it to an invoice (ran out of existence).

Now, my report has to be grouped by Buyer, then by article and then by order and by shipper. So the buyers can see why they are running out of existence.

It has to be done this way so anyone (or me) can drill down on an article and see the order on which it was, and then, see the shippers generated by that order on which the article was.

I linked the tables on SQL server views so I could avoid retained orders, etc. and I have a view on which I have all the keys: OrderNbr, ShipperID, InvtID...

I started to have this problem when I did summaries: If I have an order that caused me a backorder, I might have two (or more) shippers for that order. When I print on my report, some records (based on the example) would look like:

article: X
order: 45 <- has 2 shippers
shipper: 89
details: 10 / 7 / 5 / 3 / 2
shipper: 98
details: 10 / 5 / 4 / 5 / 1
order: (summaries wrong) 20 / 12 / 8 / 3

order: 33 <- has 1 shipper
shipper: 89
details: 8 / 8 / 8 / 0 / 0
order: (summaries ok)
article (summaries)


summaries on case of first order should be like: 10 / - / 9 / - / -

I placed scores (-) because I'm all mixed up, stucked and I'm not sure how to deal with them anymore so they won't duplicate the quantities and be real so buyers can make decisions with the results.

Any ideas or supporting thoughts are welcome and thank you for taking the time to read all this stuff.

Magda

 

[lbass]

It looks like the field {Qty_Ordered} should just be copied into the Group (Orders) footer, and not summed. Your example indicates that the next two fields ({Qty_on_Shipper} and {Qty_on_Invoice}) are varying based on shipper, which suggests they might be working correctly.
Using your example above, it looks like the summary you want would be:

10/12/9/-2/3

So, {Qty_Ordered} should be copied into the Group (OrderID) footer, {Qty_on_Shippers} should be summed, {Qty_on_Invoice} should be summed, and the other two calculations should be made at the group (OrderID) footer level:

{@QtyShippedvsOrdered}:

{Qty_Ordered} - sum({Qty_on_Shippers},{table.OrderID})

{@QtyInvoicedvsShipped}:

Sum({Qty_on_Shippers},{table.OrderID})-Sum({Qty_on_Invoice},{table.OrderID})

If the summaries still are not correct after trying this, please provide a complete sample of actual output and what the results should be.

-LB

 

[MeGGaN]

I never thought about moving fields to other groups. That would help a lot.

However, by the time I'm reading this, I made conditional running totals using formulas. They seem to work fine but if my user complains again about any calculation, I think I would try moving fields to group level. (I think that if I have a chance, I would do it anyway just to see if results match).

Thank you very much for taking the time to answer this, it isd a great help and if my report is wrong again, now I have another start point to check it again.

Magda

 

[fsilva79]

I have a simillar problem.

I create a report of several invoices of 1 customer, example:
Customer# 123
->Invoice # 1, 2, 3 ...
-> each invoice has it's own sold item # ...

What I need is to Calculate the Sub_total = Sum(Item_price * Qty)
then I need the TAX Amount ... TAX_Amount = @SubTotal * 0.075

when I do this, it brings me the TAX AMOUNT of ALL the invoices, And I just need the TAX Amount of EACH invoice.

Then I need the Total for each invoice.

Please helpme out, I've been dealling with this issue for 2 days and can't solve it, please help urgent.

Thanks in advance.

Paco

 

[MeGGaN]

Are you using running totals or summaries?

When you use running totals you can set an option that makes summaries reset based on the groups.

I have a question, did you placed your formulas on details section?

 

[fsilva79]

Sorry for being a new in Crystal ...
I'm not using neither of them cause I siimply don't know how to do it.

I've tried with formula editor, and have happed what I justo posted, and with sumaries I don't know how to Calculate the tax, seems like if I use sumaries there is no way out to calculate TAX amount.

 

[MeGGaN]

ahhh I think I know why you are getting the summary acumulated. when you do Sub_total = Sum(Item_price * Qty)
you are doing like a manual total, and you get the sum of all. if you only do Sub_total = Item_price * Qty the formula will evaluate for each line on details section (by ex. for every article).

If your fields are numbers you can rigth click the formula and select the option: insert summary (or something like that). that creates summaries for group footers sections.

Check out this url about running totals, i found it very useful:

http://support.crystaldecisions.com/communityCS/TechnicalPapers/scr_running_total.zip.asp

Magda

 

[fsilva79]

thanks for your advice.

You really helpme.

I finally solve it by ADDING another group where I get the sub_total of the previous group as you mentioned, and then I create a formula for the TAX amount, ant I make the formula to call the sub_total I've just created.

So it was just a metter of grouping correctly and using the correct formulas.

Thanks once again.

Sincerly
Paco

 

[MeGGaN]

Glad I could help

Magda