4 Replies Latest reply: Dec 27, 2012 7:08 AM by Jose Tos RSS

    Need help with pivot table / set analysis in dimension

    davy michiels



      I am having some problem with displaying data in 1 table. I think pivot table is the best option.


      I want to have as a result a table with products, stock and orders not yet delivered. Selection will be made on warehouse and product(s).

      Problem 1: I want to see also the products that do not have any stock or orders and I want to see 1 line for each order (with order ref, and so on).

      Problem 2: Stock lies in warehouses (with names of only 2 characters) and orders are placed in same warehouses (but with full name)


      I have solved problem 2 by adding a variable (vWarehouse) that states if warehouse (2 characters) "WH" is selected, variable gets value "WareHouse". Then this Variable is used in set analysis to display only values for this WareHouse.

      Like for balance quantity to be deliverd on the order I use :

      sum({$< WareHouse={'$(vWarehouse)'}, Status={NotDelivered}>} BALANCEQTY)

      So far this works


      Problem 1 however I can not get passed:


      I want to display all products (regardless stock or order balance qty) and if there are orders with balance, all orders with balance. But only one "cell" (or row) for each article.

      Please see added file for what I mean.

      I can get to same layout but then products with stock 0 are not displayed. Or same but can only get 1 order to appear per product. Or 1 row for each order. So I am kind of stuck here.


      Anybody got idea how to solve this?