We help IT Professionals succeed at work.

Calculations Using Running Totals in Crystal Reports

Carla Romere
Carla Romere asked
on
This project has gotten thrown in my lap today and they want it finished by end of business today. I didn't create this report, but am trying to create a new field to display on it.

This report is grouped as follows:
Group 1 - basically a product type sort field
  Group 2 - Order Age (# Days since estimated Shipdate ascending)
    Group 3 - Customer Name
      Group 4 - Order Number
         Group 5 - Part Code
            Group 6 - Unit of Measure

Differenet customers can order the same parts. Currently we are displaying the current inventory on hand for each part ordered (same part on different customers). What they would like to see is a running total of the inventory that would be LEFT after shipping each order from oldest to newest.

If I only display one part code I am able to accomplish this by using a running total on the qty ordered per part code, multiplying that times a negative one and subtracting that from the current inventory on hand. Then comes the rub - if I include all part codes, I can't seem to get the running total to work correctly. Currently this is my running total: Sum order quantity, evaluate on change of field order number (part codes are only included once on any order) and to reset never. But, that's not limiting the running total by part code.

Sample of what I want to display:

Customer / Order / Part           Order Qty       Current Inv     Remaining Inv
1                123       345                 3                       84                    81
1                124       789                 4                      10                      6
2                125       345                 5                       81                    76
2                126       789                 1                       6                      5

Which is exactly what I get if I limit the open orders to only one part code. But when I put in multiple part codes, it doesn't work. So I tried setting the running total up be creating a combo field which includes the order/part number, which works until I add multiple part numbers. I need to figure out when to reset the count to only consider the part code itself.

When I throw in a second part code this is what I get currently:

Customer / Order / Part           Order Qty       Current Inv     Remaining Inv
1                123       345                 3                       84                    81 -> this row is correct
1                124       789                 4                      10                      3 -> this should be a 6
2                125       345                 5                       84                    72 -> this should be 81-5=76
2                126       789                 1                       10                    -3 -> this should be 5

I need help figuring out how to get the running total I need with the way they have this grouped...any ideas?
Comment
Watch Question

Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
There is no easy way to do this and get the report you want.

The easy way is to sort/group based on the part number rather than customer.
You then get

Customer / Order / Part           Order Qty       Current Inv     Remaining Inv
1                123       345                 3                       84                    81
2                125       345                 5                       84                    72
1                124       789                 4                      10                      3
2                126       789                 1                       10                    -3

The only other way I can think to do this is with arrays.

How many parts?
What is the part number range?

mlmcc
mlmcc
Carla RomereDirector of Information Technology

Author

Commented:
Well, I can't change the groupings because this is how they want to see it. Part number range is 1000000 through 1999999. The total number of part codes is probably around 250 per report. I've not worked with arrays in Crystal Reports, but am not opposed to learning how - just need to have this finished in the next 4 hours...
Carla RomereDirector of Information Technology

Author

Commented:
Is there a way to create a formula that basically does this:

[current inventory] - sum([order qty]) where [est shipdate] < [shipdate on the current record]
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
You could use a SQL Expression to return the total ordered before a date

This question has a pdf on SQL Expressions
http://www.experts-exchange.com/Database/Reporting_/Crystal_Reports/Q_23858441.html?cid=748#a27624600

mlmcc
To answer your question about the formula:

[current inventory] - sum([order qty]) where [est shipdate] < [shipdate on the current record]

 The answer is presumably "no, you can't do that".  Technically, it would depend on what level of Sum you were trying to use, where you were trying to use that formula, etc.  Presumably you're looking for a total for a Part Code, and unless your report is grouped so that all of the records with the same Part Code are grouped together, Sum will not give you a total that includes all of the records with that Part Code.

 Unless you can create a query on your server that produces those counts for you (so that they're included in the data that the report gets and it doesn't have to calculate them), or maybe use a SQL expression as mlmcc suggested, then arrays seem like your best bet.

 James
Carla RomereDirector of Information Technology

Author

Commented:
Well that's exactly what I ended up doing. I combined the two views the original creator of the report was using into one view and did all my calculations on the sql side. That worked like a charm.
Glad you were able to work it out.  This is one of those things that's just difficult to do in CR.

 James