I am trying to create a dashboard which among other things will give a KPI on the amount and value of orders processed between two dates. Everything appears to be working fine until I encounter part shipment of orders, ie; one sales order with two or more pack ids. For some reason, the BAQ adds another line with the balance of the order value on each pack id. There is only one line on each pack id and invoice. I have tried getting the value straight from the InvcHead and also using a subquery but I cannot get it to work. Hopefully, someone can spot what I am doing wrong.
Thank you
Adrian.
your rows are duplicating. Since you have everything going to Order head, if anything has more than one row, you will be multiplying by that many rows. If you have multiple lines, I’m assuming you will have more than one row for the JobProd table, and you clearly have more than one row for the ShipDtl. So if each of those has two, and they aren’t joined, you will get 4 rows.
It could be other ones too, but you’ll have to join those tables to remove the duplicates.
Hi Brandon,
Thanks for helping me out. The frustrating thing is that more by luck than judgement I guess, the dashboard is working as it should except when it comes to part shipments. I am trying to make a calculated field so if there is a value in the InvcAmt it uses that, if not use the OrderHedAmt. No luck as yet but I will keep trying
I have finally got it working. I expect many people have the same problems, where a dashboard starts of as quite a small easily managed component, but then people start to ask “Can it also show me this/” and “Can you just add this?”. After a while it becomes a very difficult to manage monster!!
Thanks for the help guys.
