Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am working on ' Inventory & Production' application for a client. There is a requirement to show details for order vs receipt in a pivot table. In this case two tables are there, one for Header and other one for line detail.
In header table, customer raise a PO for placing a order for required qty as a bulk order and in line table he receives qty on multiple times against supply.
For Ex;
Hear Table
----------------
Po No PO Date Order Qty Vendor Item
------------------------------------------------------------------------------------------------------
PO10245 01/05/2014 500 52104 ACB5044
PO12548 05/05/2014 700 84501 ACV5042
And When customer receives supply against order, entries in Line Table is as follows:
Line Table
-------------
Po No Supply Date Rec Qty Vendor Item GRN No
-----------------------------------------------------------------------------------------------------------------------------------
PO10245 08/05/2014 110 52104 ACB5044 GRN10104
PO10245 18/05/2014 99 52104 ACB5044 GRN10143
PO10245 21/05/2014 111 52104 ACB5044 GRN11108
PO12548 15/05/2014 300 84501 ACV5042 GRN11110
PO12548 20/05/2014 100 84501 ACV5042 GRN11111
-------------------------------------------------------------------------------------------------------------------------------------
And output Required in following format in Pivot Table:
Vendor Po No Item GrNNo Order Qty Received Qty
------------------------------------------------------------------------------------------------------------------------------------
Total 500 320
------------------------------------------------------------------------------------------------------------------------------------
52104 PO10245 ACB5044 GRN10104 500 110
GRN10143 500 99
GRN11108 500 111
----------------------------------------------------------------------------------------------------------------------------------
Total 700 400
----------------------------------------------------------------------------------------------------------------------------------
84501 PO12548 ACV5042 GRN11110 700 300
GRN11111 700 100
----------------------------------------------------------------------------------------------------------------------------------
Please suggest me a solution so that I can fulfill client requirement.
Awaiting for your suggestions.(High Priority Requirement)
Thanks in Advance
Hi Aman,
find the attachment, hope this will help you
-- Rajendra
PFA.
PFA
Dear Pradip,
Thanks for providing valuable suggestion for same. But I think it will not work properly because if there will be another PO with Same Order Qty then sum(distinct Order Qty) will show wrong output.
If possible please provide more better and feasible solution.
Thanks Again
Please find updated app. Hope it meets your target.
Hi,
Because Order Qty of PO is a Measure, so will it better to keep this on Link table in Data Model.
If we can join these two tables and can achieve goal then it will much better for us.
Hi,
Because Order Qty of PO is a Measure, so will it better to keep this on Link table in Data Model.
If we can join these two tables and can achieve goal then it will much better for us.
Try the bellow expressions along with the dimensions which you have taken
Order quantity: Sum([Order Qty])
Receivedquantity: Sum([Rec Qty])
nope