Hello, I am building a report to information on Qty needed for WO. Everything looks good when i join inventoryitem and Insitestatus.
i wanted to get material uasage from AMMTran. the moment i join that all my totals begins to increase to a high figure that is impossible.
Best answer by arpine08
Hello
You can use the following approach to prevent a data increase:
Instead of joining INSiteStatus and AMMTran directly—which causes a "many-to-many" relationship that multiplies rows and increases totals—create two separate Aggregate GIs first:
GI_INSiteStatusByItem
Source: INSiteStatus
Grouping: Group by InventoryID
Results-> SUM(QtyOnHand)
GI_AMMTranByItem
Source: AMMTran
Grouping: Group by InventoryID
Results-> SUM(Qty)
Then, in your Main GI:JPS, join these pre-aggregated GIs to the InventoryItem table:
InventoryItem
LEFT JOIN GI_INSiteStatusByItem on InventoryID
LEFT JOIN GI_AMMTranByItem on InventoryID
By using this method, each joined data source returns exactly one row per InventoryID.
Here are GI’s screenshots:
GI_INSiteStatusByItem:
GI_AMMTranByItem:
GI:JPS
+1Hello
You can use the following approach to prevent a data increase:
Instead of joining INSiteStatus and AMMTran directly—which causes a "many-to-many" relationship that multiplies rows and increases totals—create two separate Aggregate GIs first:
GI_INSiteStatusByItem
Source: INSiteStatus
Grouping: Group by InventoryID
Results-> SUM(QtyOnHand)
GI_AMMTranByItem
Source: AMMTran
Grouping: Group by InventoryID
Results-> SUM(Qty)
Then, in your Main GI:JPS, join these pre-aggregated GIs to the InventoryItem table:
InventoryItem
LEFT JOIN GI_INSiteStatusByItem on InventoryID
LEFT JOIN GI_AMMTranByItem on InventoryID
By using this method, each joined data source returns exactly one row per InventoryID.
Here are GI’s screenshots:
GI_INSiteStatusByItem:
GI_AMMTranByItem:
GI:JPS
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
