We help IT Professionals succeed at work.
Get Started

Calculations Using Running Totals in Crystal Reports

Carla Romere
Carla Romere asked
Last Modified: 2012-05-09
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?
Watch Question
This problem has been solved!
Unlock 2 Answers and 7 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE