Solved

# MINUS BALANCE

Posted on 2013-05-11
290 Views
Hi All,

I have CR report with Qty below :

1. PO Qty
2. DO Qty (Order for Warehouse to Deliver Item)
3. SURAT JALAN (SJ) (Delivery Item)
4. SISA means Balance.
DO Balance means QTYPO - QtyDO
SJ Balance means QtyPO - QtySJ

One Item, can have several DO, and one DO can have several SURAT JALAN
There comes the problem, in the screen shot DO is 600, and SJ have twice delivery, which is 200 and 400. And both of them came from 600 qty of the same DO. Thus the DO Balance is minus.

How could I solve this problem ?

Thank you.
0
Question by:emi_sastra
• 16
• 11
• 4
• +1

LVL 77

Expert Comment

ID: 39159205
You do this sort of thing using Groups in the report.
I assume you have ID numbers that identify each PO, DO and SJ.
You would then insert a group using the PO ID, and insert  a second group using the DO ID

You then use Insert >Summary to add a Sum of qtySj to each group level.
You can also Insert>Summary to add the Sum of QtyDO to the PO group.

You can then create formula fields ...in the PO Group Header or Footer you can create a formula which is
QTYPO  - <the sum of QtyDo you added above>

and in the DO group header/footer you can create a formula field to calculate the other balance..
QtyDO - <Sum of SJ you added above>

When you are creating the Balance formula fields, you will see that each of the summary values appears in the field list and you can select them just as any other field to create the expression you need.

You might need to reposition some of the fields between group headers and footers and you can suppress any sections that you dot want to see.
0

LVL 1

Author Comment

ID: 39159327

Should also group for DO ?

Thank you.
DO-Balance-Minus.PNG
0

LVL 100

Expert Comment

ID: 39159450
You need to group if the DO can have several related records.  In this case I think the answer is yes, group on DO also.

mlmcc
0

LVL 1

Author Comment

ID: 39159470
How if I don't want to make it as group, since it will show at tree view at the left side of the report ?

What should I do ?

Thank you.
0

LVL 100

Expert Comment

ID: 39159478
You can sort by it but you won't have the group header/footer to use for the totals

mlmcc
0

LVL 1

Author Comment

ID: 39159482
Hi mlmcc,

Yes, I have sorted it.
But the problem is the running total has minus result, since the DO Qty has 2 details.

Thank you.
0

LVL 100

Expert Comment

ID: 39160346
How are you doing the subtraction?

mlmcc
0

LVL 1

Author Comment

ID: 39160461
-How are you doing the subtraction?
{@POQty}-{#TotalQtyDO}

Where #TotalQtyDO is running total.

Thank you.
0

LVL 100

Expert Comment

ID: 39161424
What is @POQty?

mlmcc
0

LVL 34

Expert Comment

ID: 39164079
In your screenshot, there is a -600 in the SISA/DO column.  There is a 0 in that column on the line above that.  Is that 0 correct?  I think maybe it should be 400 (the same as the SISA/SJ column).

If the line above should be 400, instead of 0, it looks like maybe the field used by your #TotalQtyDO running total is 600 in both of those last two lines (Maybe because they were part of the same shipment or something?), so the total of 600 for those two lines gets added to the running total twice (once for each line).

If that's correct, I'm not sure how to fix it, unless there's another field that you can use for your running total.  The SJ/Qty column seems like an obvious choice, but it's probably not that simple.

James
0

LVL 1

Author Comment

ID: 39164166
Hi mlmcc,

-What is @POQty?
{QtyPo}

Thank you.
0

LVL 1

Author Comment

ID: 39164180
Hi James,

-In your screenshot, there is a -600 in the SISA/DO column.  There is a 0 in that column on the line above that.  Is that 0 correct?  I think maybe it should be 400 (the same as the SISA/SJ column).
Yes.

- If the line above should be 400, instead of 0, it looks like maybe the field used by your #TotalQtyDO running total is 600 in both of those last two lines (Maybe because they were part of the same shipment or something?), so the total of 600 for those two lines gets added to the running total twice (once for each line).
Yes, because it is POQty - PODo

How to solve this problem ?

Thank you.
0

LVL 1

Author Comment

ID: 39164197
Hi All,

May be we could create a formula to set the QtyDO to 0, when it is the same with the above data. Like suppressed if duplicated.

Thank you.
0

LVL 34

Expert Comment

ID: 39164304
Should that line above be 0 or 400?  I'm not sure which one you were saying "Yes" to.  :-)

If the 0 on the line above is correct and the only problem is that the last line has -600, should that column ever be negative?  If not, maybe you could simply change the formula from

{@POQty}-{#TotalQtyDO}

to

if {@POQty}-{#TotalQtyDO} > 0 then
{@POQty}-{#TotalQtyDO}
else
0

James
0

LVL 1

Author Comment

ID: 39164396
Yes, the new formula should work.
But I think this is not a good way to do it.
How if the QtyDO might be around  10%  < QtyPO > 10 %.

Thank you.
0

LVL 34

Expert Comment

ID: 39167490
Sorry, but I'm not sure what you mean by that.

James
0

LVL 1

Author Comment

ID: 39172701
Some company has policy that vendor my send item with 10% tolerance.
Thus if Qty PO is 100, vendor is allowed to send 90 or 110.

Thank you.
0

LVL 34

Expert Comment

ID: 39174252
I don't fully understand your data or report structure, which is why my last suggestion was really just a brute force approach to try to avoid seeing negative results.  :-)

So, I am not at all sure if this is what you're looking for, but you can give it a try.

if {@POQty}-{#TotalQtyDO} > 0 or
Abs ({@POQty}-{#TotalQtyDO}) <= ({@POQty} / 10) then
{@POQty}-{#TotalQtyDO}
else
0

That says if the difference is positive, or if the difference (positive or negative) is <= 10% of @POQty, then show the difference.  Otherwise, show 0.

James
0

LVL 1

Author Comment

ID: 39174526
Hi James,

Could we create a formula to hold DONo, say its name is @LastDONo ?

Then If {DoNo} <> @LastDoNo
@LastDONo := {DoNo }
{@POQty}-{#TotalQtyDO}
ELSE
@POQtyDOBalance

Something like that.

Thank you.
0

LVL 34

Expert Comment

ID: 39175255
You can't assign a value to a formula, so you can't use something like
{@LastDONo} := {DoNo}

You could save the value from the field in a variable, but you may not need one.  You can use the Previous function to get the value for a field from the previous record.  If you're going to use Previous, I would also use OnFirstRecord, because I'm not sure what Previous gives you when you're on the first record and there is no previous record.  So, it might be something like this:

If OnFirstRecord or {DoNo} <> Previous ({DoNo}) then
{@POQty}-{#TotalQtyDO}
ELSE
{@POQtyDOBalance}

James
0

LVL 1

Author Comment

ID: 39176028
Hi James,

I've tried it.

The formula can not refer to itself, either directly or indirectly.

Thank you.
0

LVL 34

Expert Comment

ID: 39176410

ELSE
@POQtyDOBalance

I just accepted that as what you wanted, but if what you're working on is the @POQtyDOBalance formula, then obviously that won't work (and wouldn't make any sense).

Assuming that what you're looking at is the @POQtyDOBalance formula, then what do you really want after the Else?

James
0

LVL 1

Author Comment

ID: 39176448
-  Assuming that what you're looking at is the @POQtyDOBalance formula, then what do you really want after the Else?
I mean to show the last balance of @POQtyDOBalance , suppose it is 0 then just show zero.

Now, since it has error then I omit the else :

If OnFirstRecord or {DoNo} <> Previous ({DoNo}) then
{@POQty}-{#TotalQtyDO}

Ant the result = 2.145, same as QtyPO.

The other problem is the sum of QtyDO at group footer. The value is exceed 600 as we know it.

Thank you.
DO-Balance-Minus-01.PNG
0

LVL 34

Expert Comment

ID: 39176978
I'm sorry, but I really don't know what you're looking for at this point.

You said "show the last balance of @POQtyDOBalance", but does "last" mean the final value for that group, or the value from the previous record, or something else?

And you said that with the new version of the formula, the result is 2,145, but is that right or wrong?

It might help if you could post another screenshot, but highlight the numbers that are wrong, and say what they should be, and why.

It would probably also help if you could post the report (.RPT file), so that I could see the report structure and formulas for myself.

James
0

LVL 1

Author Comment

ID: 39176999
- You said "show the last balance of @POQtyDOBalance", but does "last" mean the final value for that group, or the value from the previous record, or something else?
I mean the final value for that group which is @POQtyDOBalance.

- And you said that with the new version of the formula, the result is 2,145, but is that right or wrong?
Wrong, should be 0, same as above record.

-It might help if you could post another screenshot, but highlight the numbers that are wrong, and say what they should be, and why.
I do now. Please have a look at it.

- It would probably also help if you could post the report (.RPT file), so that I could see the report structure and formulas for myself.

Thank you.
DO-Balance-Minus-01.PNG
rptLaporanRekapPODanPengirimanv2.rpt
0

LVL 34

Accepted Solution

James0628 earned 500 total points
ID: 39177038
I have a couple of other ideas, but I have thought of something that may fix both problems.

Edit the #TotalQtyDO running total.  In the Evaluate section, select the "On change of field" option, and select the NoDO field.

That might clear up the problem that you were having with the @POQtyDOBalance formula.  It was -600 at the end because you're subtracting #TotalQtyDO, and #TotalQtyDO includes 600 twice, because there are two detail records for the same NoDO.  If you change the running total so that it only includes the value from each NoDO once, that may fix @POQtyDOBalance too.

If you fix the running total, you may not need the if-else in @POQtyDOBalance anymore.  After you change the running total, I would change @POQtyDOBalance back to the way that it used to be (like below), and see what you get.

{@POQty}-{#TotalQtyDO}

For the QtyDO total that shows 2745, that is also because you've got two detail records for the same NoDO, with the same quantity (600), so it gets added twice.  If you make the change to #TotalQtyDO that I described above, it should give you the correct total.  Try replacing the sum on QtyDO in the group footer with #TotalQtyDO.

James
0

LVL 1

Author Comment

ID: 39177828
- Edit the #TotalQtyDO running total.  In the Evaluate section, select the "On change of field" option, and select the NoDO field.

I can not select NoDO field, since it is not a group.
We can just type in textbox beside on change field.

Thank you.
0

LVL 34

Expert Comment

ID: 39179516
It doesn't have to be a group.  Make sure that you're selecting "On change of _field_", not "On change of group".  I had absolutely no problem making that change in the report that you posted.

If you can have more than one NoDO for a single ProdName, then the report should also be sorted by NoDO.  I didn't think of that before.  Just add a sort (not a group) on NoDO.  The records will be sorted by NoDO within the ProdName group.

There is an assumption here that you won't have the same NoDO for different ProdName values.  If the same NoDo could appear in more than one ProdName group, the running total may need to be changed, so that it resets the total at the correct times.

James
0

LVL 1

Author Comment

ID: 39179784
I am sorry, that textbox is uneditable, should use arrow to feed it from dataset.
It works now for the balance, but not for the sum total.

Thank you.
DO-Balance-Minus-02.PNG
0

LVL 34

Expert Comment

ID: 39179821
I'm not sure what you're trying to edit.  Just delete the sum from group footer 4 and put #TotalQtyDO there instead.

James
0

LVL 1

Author Closing Comment

ID: 39179848
-I'm not sure what you're trying to edit.  Just delete the sum from group footer 4 and put #TotalQtyDO there instead.
I  mean "On change of _field"

- Just delete the sum from group footer 4 and put #TotalQtyDO there instead.
Great, it works now.

Thank you very much for your help.
0

LVL 34

Expert Comment

ID: 39182229
Ah.  I see.  I've never tried to edit that field in a running total, since selecting something from the list is easier.  So I didn't realize that you couldn't edit it.  I thought you were trying to edit a field on the report.

I'm glad you were finally able to get it working.  You're welcome.

James
0

## Featured Post

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customerâ€™s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. â€¦
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 biâ€¦
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax â€” just include tâ€¦
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidtâ€¦