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.

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Donnie Burhan

    Hi Gerd,

    Wouldn’t you need to change the query every year if using this approach?

    Is it not possible to use Text Variable for each key figure instead, so it can be like:

    Selection Month -2

    Selection Month -1

    Selection Month

    Selection Month +1

    Selection Month +2

    I think this should be more flexible to design.

    Though I find it interesting that now there’s a functionality to filter key figures by using a variable.

     

    Could you explain how this code works?

    Because I see that for example, for l_cur_month = 4, then the variable output will be 4_2017.

    How did this translate into Amount Apr 2017?

    (0) 
    1. Gerd Schoeffl Post author

      Hi Donnie,

      Well sure – the example I have used is simplified to make it easier to understand the idea behind the new feature. What I would do differently in a real-live project is:

      • use variables in the columns – a variable for year, maybe even for period (depending on the fact which characteristic you use), maybe for additional characteristics like 0InfoProv, 0Version etc. (if additional actual data for past month needs to be seen)
      • use text variables
      • do not use a fixed year in the key of the structure elements, rather use a naming convention like ‘01_cur_year’, ‘01_next_year’, …
      • change the ABAP coding: pick the start month from the system data or from some (admin) parameter, polish the coding, … Also, here the idea was to get it to run and relatively easy to understand.

      So how does the system find the correct key for a dedicated structure element? As visible in the third screen shot every structure element must have a key. In my naming convention the structure elements are called ‘01_2017’, ‘02_2017’, …

      Now in the coding example the variable l_cur_month is a two digit numeric field. Thus, after the statement ‘l_cur_month = 4’ the variable l_cur_month contains the value ‘04’ and thus the generated key is ‘04_2017’ which is the key for ‘Amount April 2017’. Again, this might be a place where a little polishing would make the ABAP coding more sophisticated.

      Best regards, Gerd

      (0) 

Leave a Reply