BPC 10.1 Embedded – how the new exit for displaying structure members can be used for setting up a rolling forecast
In our blog BPC 10.1 Embedded: How to efficiently realize a rolling forecast scenario we have described how a rolling forecast can be achieved using a BPC Embedded scenario. We have described that there is one option where you create all possibly necessary structure elements (columns) in your query and then use the display variable to display only those columns that are necessary at the given time. The single values in the variable must be selected manually. Obviously, this procedure is quite cumbersome and error prone especially when the query contains many structure elements.
With note 2407049 (also have a look at note 2133546 as prerequisite) we introduce the option to use an (non-input enabled) exit variable instead. Thus, the correct structure elements can be chosen automatically by the system. The end user does not have to care about setting the variable and cannot choose the wrong columns.
To set up the query you must use the new BW modeling tool (Eclipse based).
First you should create a new variable for your visibility filter. Use the context menu on the name of your BW system, choose ‘New’ and then ‘Variable’ (if ‘Variable’ is not visible directly go to ‘New->BW Object->Variable’). Create a variable based on the technical characteristic ‘1MEMBER’. Note that the variable type MUST be ‘Customer Exit’. If you want you can make the variable input enabled.
This is an example how your variable could look like:
Now open your query in the BW Modeling Tools (not BEx Query Designer).
Click on ‘key figures’ in the column. On the right-hand side under ‘Display’ choose ‘Can be changed using variables’.
Click the change button on the right and either enter the name of your variable directly or use the browse button:
Now for each of the structure elements you should enter a technical name (in a normal query this is not necessary):
We want to follow our example of a rolling forecast. This is the list of the structure elements we create:
Now create the standard BW variable exit. If a certain column should be visible, then the exit must return the key of this column. If for example, we are have to plan starting from April 2017 the exit would return the keys for the structure elements for April 2017, May 2017,…, March 2018, Q2 2018, Q3 2018, and Q4 2018.
You could either hard code the logic we have just described or you can make the coding more generic as we have done in our example:
When executing the query the structure is automatically adapted so without user interaction we get to correct setup for the rolling forecast.
When you display the design panel you can see that there is a filter on the measures in the columns. Click on measure and you will see which structure elements are displayed by the exit variable and which columns are disabled.