Generate Combinations Function in SAC Planning

In customer projects input queries are often restricted to only posted values to ensure optimal performance for the end-users. As business requirements are usually quite similar from year to year, this supports the end-user to easily plan their values in a well-arranged input form without thinking about validity of shown combinations. However, sometimes new business requirements occur, for example new materials need to be planned.

This blog shows an end-user friendly and well performing custom-developed solution in SAP BW using BI-IP functionality which can be embedded in almost every front-end where you have BW Access and can execute BI-IP planning sequences, such as SAC Planning with BPC Live Connection, but also in older frontends such as BEx Web Templates or Lumira.

To allow this, there are different possibilities:

  1. 1. Master data input form or upload cockpit
  2. 2. New line functionality
  3. 3. Generate combinations function

For master data input queries it is important to restrict as much as possible to ensure a good performance for the end-user. Additionally, it could be helpful to add some checks and validations, for example via characteristic relationship or data slice to make sure that the user can only enter valid combinations. This is also important from the performance perspective.

If an upload cockpit is used, it would be easy to create new combinations, even via mass upload, but the user creating the upload file needs to have a basic technical understanding of the values to be uploaded, like the characteristic keys and the structure of the upload file to be provided.

In Bex web templates, later in Lumira, AO and also in SAC it is possible to add new lines to your input query by selecting the characteristic values via F4-help for all characteristics in the drilldown. My experience was that this only works if the queries are well restricted, because for info objects with lots of master data records, the F4-help might not even come back. Additionally, the end-user needs to have a basic idea of what the characteristic key of the combinations to be uploaded is like, to find the values that should be added. Moreover there is a technical restriction: If you use queries with two structures, for example one for the versions in the columns and another one for the key figures to be planned in the rows, this functionality is not even available.

As a result of these points, I tried to find a way how to allow the end-user to easily create new combinations in their only posted values input form. My provided solution here is a so called “Generate combinations function”.

To show how it works, I have added this function to the sample story above – I created an easy Sales Volume planning based on posted values. Via dropdown box selection the user can select a new material to be added to the input query below to allow planning  quantities for it in the future.

After pressing the Add new combination button, the new material appears in the input form by creating a zero record in period 12.

Some technical facts about how this works

  • Our story is based on SAC with BPC Live Connection
  • The input query was created in our BW and restricted to only posted values, while zero suppression was not active
  • The function itself was created as an ABAP BI-IP planning function in our BW system – it works a bit like the standard generate combinations function from SAP, but I added some special logic
  • The dropdown box is filled from the material dimension of the input form

To make sure that the dimension material gives back more entries than the posted values only, but instead only those relevant for the end-user, I added a fixed filter for all materials starting with CK20*. If your selection is more complex, you can also add an exit variable in the filter to collect those characteristic values relevant for your business case.

More details about the Planning function

The function has three parameters:

  1. Parameter STRING can be used to implement specific logic
    1. Parameter KYFSEL can be used to mark relevant key figures of your aggregation level
    1. Parameter VALUE can be used to set another value than 0 for the new record, i.e. 1 or 0,01 – this works only if KYFSEL selection is filled as well

Let us have a more detailed look on the implementation of the init_execution, execute and finish_execution method.

Method Init_execution

  • Gets all restrictions from the filter and selects corresponding master data
  • Restrictions via navigational attributes is also taken care of
  • Out of the filter selection, first of all the unique key is defined – this contains all characteristics which are filtered to single value only
  • Additionally, a helper table is filled, which contains characteristics with more than one value

Method Execute

  • As a result of unique key and helper table, all possible combinations are created – the value for the new record depends on the maintenance of the parameter VALUE – if nothing was defined, a zero record will be created
  • Combinations which exist already in c_th_data, will be skipped during processing, so that existing transaction data will not be changed or overwritten

Method Finish_execution

  • If error messages occurr during processing of the function, they will be given back executing this method

Last but not least some more hints for the creation of the corresponding filter:

  • I prefer to use the same aggregation level for the function as it was already used for the input form. This makes sure that derivations, checks and data slices will be done the same way for function and input query
  • Make sure that you restrict EVERY! characteristic of your aggregation level in the filter of this function – for everything which is not restricted, ALL existing master data will be used creating the new records
  • Regarding periods/ months I usually restrict to period 12 as at least for the customers I was working so far, period 12 was always open for input, while previous months could be locked via data slices when they turned from plan month into an actual month
  • For all characteristics which can not be restricted to a single value initially, input variables should be created, which will then be filled according to the dropdown selection – the handover needs to be done then in the scripting/ binding part of the frontend of your choice

Here the example for my function where I handed over the SalesOrg selection of the user from the datasource as well as the material selection from the dropdown to the input variables of my function.

Summary

Summing up the generate combinations function is an end-user friendly and well performing custom-developed solution in SAP BW using BI-IP functionality which can be embedded in almost every front-end where you have BW Access and can execute BI-IP planning sequences, such as SAC Planning with BPC Live Connection, but also in older frontends such as BEx Web Templates or Lumira.