I have a GI that outputs QtyAvail for each of our two warehouses on separate rows from the INLocationStatus table. I know to get the total QTY I can group the report by item.
In this same GI however, I need to also output the total QtyAvail from both warehouses in the following format:
Item - Warehouse - QtyAvail - Total QtyAvail
ABC - WHS1 - 10 - 50
ABC - WHS2 - 40 - 50
Is this possible?
Best answer by vincea11
I was able to solve for this! I created a GI with the InventoryID’s grouped and outputted the QtyAvail from each location with a sum to get the total QTY available. Then, in my primary report, I joined this table to the InventoryItem table by InventoryID and am able to output the total QtyAvail on any GI.
+11Hi
Have you used "Total Aggregate Function" in GI?
Best Regards,
NNT
Hello NNT, thank you for your reply but unfortunately that does not work for our needs.
Our report is a listing of multiple stock items and I need to calculate on a per-line level the warehouse qty against the total qty like this:
Item - Warehouse - QtyAvail - Total QtyAvail - % of Total
ABC - WHS1 - 10 - 50 - 20%
ABC - WHS2 - 40 - 50 - 80%
ABC2 - WHS1 - 20 - 50 - 40%
ABC2 - WHS2 - 30 - 50 - 60%
Check out the INItemCost table - I’ve used INItemCost.QtyOnHand a lot when I want to get a total qty on hand without too much complexity in groupings and joins
I’m not aware of a table that has a total for QtyAvail across all sites - depending on how many warehouses you might have, I think your best approach is just to write a simple sum formula to get the Qty available at each
I was able to solve for this! I created a GI with the InventoryID’s grouped and outputted the QtyAvail from each location with a sum to get the total QTY available. Then, in my primary report, I joined this table to the InventoryItem table by InventoryID and am able to output the total QtyAvail on any GI.
Thank you for sharing your solution with the community
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
