Add Rows to Planning Data with more Control than "Add Member"

[bastianbrindley]

Introduction:

The purpose of this blog is to showcase the possibility of adding rows to the planning data set with a mixture of SAC Analytics Designer scripting APIs and Data Actions.

The specific use case is when new combinations of members should be posted during the planning cycle in a self-service approach by the users.

Setup:

A designated combination of dimension members does not exist yet in the planning version and surrounding parameters need to be considered in the process.

Problem:

With the built-in functionality of "Add Member" in a table grid, the outer dimensions (which are not currently in the table) are handled in the background and which members are chosen is not apparent to the user - the outcome of this can lead to irritations and wrong data in the aftermath.
If a Data Action is used directly, then the user needs to input members for every dimension which is tedious and easily leads to wrong settings.
Furthermore, if there are more complex scenarios in the planning concept (e.g. hidden key figure sets or artificially filled dimensions like compounded IDs), this function is not applicable at all.

Solution:

Let´s assume there are 10 dimensions in the model. A couple of them are driving (e.g. Company Code, Cost Center or Sales Rep depending on the subject area) and for these, the members of the new row are clear to the user or even defined through authorization.
The other dimensions are to be prefilled with the most common members that are available in the planning set - they can be changed by the users to other values, also completely unbooked ones.

After the selection of dimension members, the user also needs to choose measure values (if there are multiple measures to be filled with the same values or circular measures exist, like quantity and price per piece, this is where this approach "outperforms" the default "Add Member" the most).

Once this is done, the user can click a button and in the background a Data Action which creates the rows is started. Any required quality checks, e.g. on the measure values or if the selected combination already exists, can be added to the button click.

Afterwards, the same can be done with other member combinations or the user can return to the "normal" planning table and the new records will be available.

Implementation:

The driving dimensions can be covered with single selection input controls in the initial view of the interface (be it a page in the main story or a separate story just for the row creation purpose).
Then a button can be used to open a popup for the detailed member selection.

For each dimension a hidden table is created which contains just this dimension and the relevant measure to identify relevance, e.g. Sales or Profit. The table is sorted by this measure descending or even the Top Rank function is used. The table is just filtered by the driving dimensions from the step before. Hence, the first line of the table will contain the most used member per dimension, hereby providing good default values.

On the button onClick() these members are retrieved as follows:

var dim1 = T_Dim1.getDataSource().getResultSet(undefined,undefined,1);

The members for all dimensions are forwarded to a main table:

T_Main.getDatasource().setDimensionFilter("Dimension1", {id: dim1[0] ["Dimension1"].id, description:dim1[0] ["Dimension1"].description});

 

In a popup the derived members can be shown with Filter Lines based on the main table (Note: Input Controls would currently not work as the setSelectedMembers API does not work if unbooked members are allowed in the Input Control).

The user could change those members now as desired.
The measure values can be inputted through Input Fields.

Once the user is finished with configuring the selections for the new row, a button can be clicked to create the combination.

Any additional logic, like quality checks can be applied here and finally the currently selected members per dimension need to be retrieved:

V_New_Dim1 = cast(Type.SingleFilterValue,T_Main.getDatasource().getDimensionFilters("Dimension1")[0]).value;

And then handed over to a Data Action:

DA_Create.setParameterValue("Dimension1",V_New_Dim1);

This Data Action is very simple and contains just one Advanced Formula Step with filters on all the dimension members, whilst propagating the measure values entered by the user.

 

Conclusion

With this setup the creation of new member combinations into the planning version can be handled very easily and gives the developer a lot of control during this process.
It is also convenient for the users and was well received in my projects.

Let me know if You have any questions or something is unclear, I´ll be happy to enhance the post.