Skip to Content
avatar image
Former Member

Problem in getting correct result in cross tab

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • avatar image
    Former Member
    May 09, 2017 at 06:52 AM

    Sorry the data didn't come up correctly.

    My data is as below and the Cross Tab that I want is just below that

    Add comment
    10|10000 characters needed characters exceeded

  • May 09, 2017 at 08:07 AM

    Hi Sreejith,

    1. Use the Customer field as the first row of the crosstab.

    2. Use the Order Num as the second row.

    3. Use the Prod Group as the Column.

    4. Use the ShipQty as the summarized field.

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Abhilash;

      Thank you for the response. I don't want the ship qty as the summarized field. I want the pending qty.I had created a formula to get the pending qty. But that gives an error when an order is shipped multiple times like Order Qty is 100. First shipment 25 second shipment 35 so in actual 40 is balance. But when I see my Cross Tab report I get 80 as the result. That is what the problem is

  • avatar image
    Former Member
    May 09, 2017 at 11:24 AM

    Hi;

    I am very much in need for a solution to this problem. If any body could help me out to sort out the issue.

    Regards

    Sreejith

    Add comment
    10|10000 characters needed characters exceeded