In a NC query, you may get unexpected stock values displayed if you drilldown a characteristic such as movement type(0MOVETYPE, 0STOR_LOC or 0STOCKTYPE). At first glance it looks wrong, but actually the values are correct according to the non-cumulatiove logic. It probaly isn't what you expect from business point of view. See also note 589024 and the Simple Example where this topic is dicussed from a technical point of view.
Let's take a look at the following example.
We have a NC cube GL_NC, it consists NC key figure: 0TOTALSTCK
- 0TOTALSTCK is calculated from key figures:
- 0RECTOTSTCK: the stock quantity that are received. The received quantity is always loaded into cube with movemnet type (0MOVETYPE) = 101 (let's assumen only one movement type 101 is for receiving to simply the scenario)
- 0ISSTOTSTCK: the stock quantity that are issued. The issued quantity is always loaded into cube with movement type = 601.
0TOTALSTCK = initial stock + sum(0RECTOTSTCK) - sum(0ISSTOTSTCK). For further details see note 1548125 'Interesting facts about Inventory Cubes'.
Cube GL_NC has initial stock 0.
Now we have the following data loaded into cube:
Therefore, the stock result is:
Here we can see:
- On 24.07.2012, there is a stock received (moved in as movement type 101) of 50 ST. Therefore total stock (0TOTALSTCK) is 50.
- On 26.07.2012, there is a stock issued (moved out as movement type 601) of 30 ST. Therefore total stock changes to 20 ST (0 + 50 - 30).
- On 28.07. 2012, there is a tock received (moved in as movement type 101) of 100 ST. Therefore total stock changes to 120 ST (0 + 50 - 30 + 100).
When Movement Type is Drilled Down
After Movement Type is drilled down, the column of 0TOTALSTCK value is hard to understand.
On the red underlined cells, you will see on 28.07.2012 the stock is 150 ST for movement 101. However the actual stock in the factory warehouse should still be 120 as we explained previously.The same situation happens on cell of 26.07.2012 with movement 601. Now the 0TOTALSTCK value looks strange, but such strange value is correct according to OLAP engine logic.
In above red underlined cells, there are addtional filter on 0MOVETYPE from the drill down characteristic. What system does is to calculate 0TOTALSTCK with the filter of 0MOVETYPE. Therefore, in cell 150ST OLAP can only get received quantities (0TOTALSTCK=0 +100ST + 50 ST - 0) because all issued quantity are with 0MOVETYPE=601 and get excluded from the calculation.
Similarly, in cell -30ST, OLAP can only get issueed quatities (0TOTALSTCK = 0 + 0 - 30 ST) because all received quantities are with 0MOVETYPE=101 and thus get excluded.
How to Get Normal Stock even with Movement Type Drilled Down?
In principal, if you put local filters on the NC key figure (stock key figure) this may make the stock result hard to understand. In some situations, actually what you want is to drill down a characteristic to see the detailed values of other normal key figures (such as inflow or outflow of the stock), but you prefer the stock value keeps the same as without drilldown.
To make 0TOTALSTCK show meaningful stock value even with 0MOVETYPE drilled down, you can use Constant Selection.
You can create a selection (or Restricted Key Figure) of 0TOTALSTCK with a constant selection on 0MOVETYPE as following:
Here, no filter is defined on 0MOVETYPE. This means 0MOVETYPE = * for this key figure. After set 0MOVETYPE as constant selection, the local filter on 0MOVETYPE that is introduced by drilldown will be ignored. This key figure will always have filter 0MOVETYPE = *.
In this way, the overall stock value will be displayed in query result although 0MOVETYPE is drilled down. See below:
CAUTION: Uncheck the flag KIDSEL in the properties of the BEx queries to force the processing of the hidden structure element.
NC query will get similar unexpected stock value if you drill down with storage location (0STOR_LOC) and stock type (0STOCKTYPE) characteristics. The cause and solution is similar as explained above. For more detailed information, please also read:
Note 589024: Reports in BW with storage location and stock type chars.