Skip to Content
0
May 09, 2017 at 06:45 AM

Problem in getting correct result in cross tab

110 Views Last edit Jun 08, 2018 at 04:41 PM 2 rev

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.