Cumulative Sub-total
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22568
Printed Date: 28 Apr 2024 at 12:53am
Topic: Cumulative Sub-total
Posted By: Pete G
Subject: Cumulative Sub-total
Date Posted: 04 Apr 2018 at 9:26am
Hi All;
I have a report (Priority List) with two sub-reports. One showcases scheduled delivery information along with order quantity and stock on hand values. Some entries can have multiple scheduled delivery dates with different order quantities for each release.
I've created two formula fields in that sub-report; The first simply sums the order quantities and subtracts it from the stock on hand amount and is working fine. The other SHOULD be subtracting the order amount from stock on hand for each entry. The problem I'm having, I guess, is that line one works fine. Line two, however, is still subtracting from the original SOH amount, rather than the amount less the first line.
I have considered having multiple invoice balance fields and then using if/else logic to populate them, or somehow adding a counter loop and using that to update the amounts cumulatively, but am unsure how to proceed.
Any ideas would be greatly appreciated!
|
Replies:
Posted By: kevlray
Date Posted: 06 Apr 2018 at 5:18am
Question? Is the sub-report in a different section than where the calculation is being done (he other SHOULD be subtracting the order amount from stock on hand for each entry.). Values from a sub-report are not available (thus looking at a previous value) until after the section the sub-report is in.
|
Posted By: Pete G
Date Posted: 06 Apr 2018 at 6:24am
The running total (subtraction) is in the detail section. The total of all outstanding orders being subtracted from all stock on hand is in the report footer.
I don't think the issue is with the placement, so much as with the fact that the SOH (Stock On Hand) isn't changing in the table it's being pulled from, so the 2nd line is subtracting from the original total regardless of what the first line does.
|
Posted By: kevlray
Date Posted: 09 Apr 2018 at 4:36am
|
I am not sure what you are trying to achieve since the value from the sub-report would change for each detail record. A few more details on how the report is structured could help.
|
Posted By: Pete G
Date Posted: 09 Apr 2018 at 5:22am
That's the problem; each record can have 0 to multiple entries based upon whether or not there are delivery dates scheduled.
The sub-report is subtracting the order quantity for the specific ship date from the stock on hand amount for that item code in one formula field. That one is only subtracting the individual order amount from the total stock on hand for each line within that record. For instance this is what displays now;
SOH ORD_QUAN INV_BAL
10000 100 9900
10000 1000 9000
10000 1500 8500
this is what I'm looking for;
SOH ORD_QUAN INV_BAL
10000 100 9900
10000 1000 8900
10000 1500 7400
The sub-report is a modification request and the main report is based on JOB_NO. In a real life scenario, we could have one job that encompasses 30 shipments in a year and the existing report would just give a negative inventory balance. The mod is trying to show at which shipment we would go into the negative, so we know when to re-order.
Please let me know if you need more detail or if that wasn't clear enough.
|
|