I am having a data as below. The data contains the orders received and the invoices done accordingly. In the below example Customer A001 Order 100 Line 1 there are different shipments.
Customer Order Num OrderLine ProdGroup OrderQty OurShipQty UnitPrice A001 100 1 Group 1 100 25 15 A001 100 2 Group 2 50 15 10 A001 100 1 Group 1 100 35 15 A001 103 1 Group 3 75 0 20 A002 101 1 Group 1 20 0 50 A003 102 1 Group 2 10 0 1 A003 102 2 Group 3 50 25 2
I want my result as below in a cross tab report which should show me the pending qty and balance amount of sale per customer per order.
Group 1 Group 2 Group 3 Total A001 100 40 35 600 350 950 103 75 1500 1500 A002 101 20 20 1000 1000 A003 102 10 25 10 50 60
Inorder to get the pending qty I gave the below formula
if isnull({ourshipqty}) then
{orderqty} else
Maximum ({orderqty}) - Sum ({ourshipqty}, {@OrderNumOrderLine})
The problem that is taking place is if in the case of customer A001 Order Number 100 Line 1 Under the head Group 1 I get the Pending Qty as 80. Its adding up the same pending qty for each line of shipment. The same story for the balance amount
The Cross Tab is placed in the REPORT FOOTER.
Please let me know how I could get the correct report. I also noticed one thing that if a variable is given in the formula then I cant use that in the cross tab.