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) and there the query designer (not BEx Query Designer).
Click on ‘key figures’ in the column. On the right-hand side under ‘display’ choose ‘Can be changed using variables’.
Create a new variable from the change button:
This is how the variable would look like – note that the variable is based on the technical characteristic ‘1MEMBER’:
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.