In my previous post we discussed how one can build a dynamic zigzag calculation in Essbase. In this post I will show how dynamically calculated values can be used in stored member calculations.
Let's modify the cube by adding a new dimension Year with two members 2017 and 2018.
After saving the outline all the data will go into 2017 member. Make sure that Year member is marked as "Dynamic Calculation" otherwise the data will end up in the Year member.
There is no data in 2018 which is expected. Assuming that the inventory was tracked properly, the "Inventory on hand" in January of 2018 should be equal to the "Closing Inventory" in December of 2017. The "Inventory on hand" is a stored member so we need to copy values there.
Let's add a MDX script to Zigzag.demo cube.
"([Closing Inventory])" TO "([Inventory on hand], Jan, )"
Now we have data in 2018. I believe the same effect could be achieved using standard BSO calc scripts, but it probably will require some tricks to generate empty blocks before you can FIX on it.
When will this feature be available for on-prem?