Solved

MINUS BALANCE

Posted on 2013-05-11
32
290 Views
Last Modified: 2013-05-20
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
Comment
Question by:emi_sastra
  • 16
  • 11
  • 4
  • +1
32 Comments
 
LVL 77

Expert Comment

by:peter57r
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

by:emi_sastra
ID: 39159327
Please see the screen shot.

Should also group for DO ?

Thank you.
DO-Balance-Minus.PNG
0
 
LVL 100

Expert Comment

by:mlmcc
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

by:emi_sastra
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

by:mlmcc
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

by:emi_sastra
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

by:mlmcc
ID: 39160346
How are you doing the subtraction?

mlmcc
0
 
LVL 1

Author Comment

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

Where #TotalQtyDO is running total.

Thank you.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39161424
What is @POQty?

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
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

by:emi_sastra
ID: 39164166
Hi mlmcc,

-What is @POQty?
{QtyPo}
At ItemCode Group Header.

Thank you.
0
 
LVL 1

Author Comment

by:emi_sastra
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

by:emi_sastra
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.

How about that ?

Thank you.
0
 
LVL 34

Expert Comment

by:James0628
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

by:emi_sastra
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

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

 James
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 1

Author Comment

by:emi_sastra
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

by:James0628
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

by:emi_sastra
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

by:James0628
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

by:emi_sastra
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

by:James0628
ID: 39176410
I was kind of wondering about that.  You had

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

by:emi_sastra
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

by:James0628
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

by:emi_sastra
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.
Please have a look.

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

Accepted Solution

by:
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

by:emi_sastra
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

by:James0628
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

by:emi_sastra
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

by:James0628
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

by:emi_sastra
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

by:James0628
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now