Question

Crystal Report formula to get costs

  • 15 July 2021
  • 2 replies
  • 78 views
A
  • Anonymous
  • 0 replies

I am trying to create a new version of a customer pack slip in E10, so that when we ship FOC items overseas, we have a document for customs purposes that shows the cost value. I have got all the details on the the report and am having trouble with the following formula, that will pull the cost depending on If statements,.

If ({shipdtl.OurJobShipQty} > 0) then ((({JobAsmbl.TLALaborCost} + {JobAsmbl.TLABurdenCost} + {JobAsmbl.TLAMaterialCost} + {JobAsmbl.TLASubcontractCost} + {JobAsmbl.TLABurdenCost}) / {JobHead.ProdQty}) * {shipdtl.OurJobShipQty})
else
If ({shipdtl.OurInventoryShipQty} > 0) and ({Part.CostMethod} = "A" and {PartCost.CostID} = "1") then (({PartCost.AvgLaborCost} + {PartCost.AvgBurdenCost} + {PartCost.AvgMaterialCost} + {PartCost.AvgSubContCost} + {PartCost.AvgMtlBurCost}) * {shipdtl.OurInventoryShipQty})
else
If ({shipdtl.OurInventoryShipQty} > 0) and ({Part.CostMethod} = "S" and {PartCost.CostID} = "1") then (({PartCost.StdLaborCost} + {PartCost.StdBurdenCost} + {PartCost.StdMaterialCost} + {PartCost.StdSubContCost} + {PartCost.StdMtlBurCost})*{shipdtl.OurInventoryShipQty})
else 9999.99

 

This formula works ok if we are shipping direct from a job, but fails if shipping from inventory.

If I rem out the first If and Else, it will run for an inventory part with on problem.

If I remove this field from the report it wil load data no matter if it is a shipment form a job or inventory, so that tells me there is a problem with this formula, but for the life of me I can’t see it, so am hoping that someone can take a look and tell me where I’m going wrong.

Thanks in advance for any help.

2 replies

G
Userlevel 3

In order to see where the issue could be, I would break the statement into 2 different fields and display both.

So the first IF statement would output to a field called CostJobShip.

The IF/ELSE statement for an inventory item would output to a field called CostInvShip.

Also display the ShipDtl.OurJobShipQty and the ShipDtl.OurInventoryShipQty with the appropriate Cost field calculated from the appropriate IF statement.

This will help identify the cost calculation is correct.  You may want to display the JobHead.ProdQty field to verify you are not dividing by a 0 value.

If it works by segregating the IF/ELSE statements, you can always suppress the field you don’t want to display in the Field Attributes, if the value is equal to or less than zero.

Glenn Owers Sr. Business Analyst Safran Electronics & Defense Canada Peterborough, ON Canada Version: 2021.2..23
C

I have had problems in the past with too many if statements in the same calculation. You may want to try using a Switch statement and see if that fixes your issue. 

Reply


Terms & Conditions