In this blog post I will be sharing a part of my own experience while planning in unified stories using scripting, before we start, I would like to clearly state that the findings and ideas you will be reading were used to workaround limitations imposed by dropdown lists in 2 specific planning use cases I personally encountered.
In both scenarios the customers preferred to use the app scripting capabilities to display a popup including dropdowns for the data action parameter values over using the data action trigger itself, so dropdowns were a must, the scenario was:
Click on a specific row from inside a table.
Press a button.
Display a popup including filled dropdown lists that initialize on default values captured from the row details wherever applicable, the planner is then able to change those default values to any other value available in one or more dropdown list.
Use Case 1:
Planning on dimensions that have a huge number of members, this issue can be encountered on many dimensions, such as Cost Element, Cost Object, WBS Elements, etc. In this use case the dimension had around 3000 members.
This is when the findings below where observed. As the number of members in the getMembers() API call went up beyond the default value, time to display the popup also increased, until it caused an out of memory error to all the tenant open tabs inside the browser.
Number of members
Time until the popup appears
~38 Seconds then out of memory error
So basically, there was no way to supply the dimension with all the 3000 members that must be available for the users to plan on, having it as an input text area would also be a terrible idea as there wouldn't be a robust way to have a value help/suggestions list, or some method to validate the entry.
Use Case 2:
Filtering out only open WBS Elements, despite there being a property that flags the closed ones, the main problem was that since this is a planning scenario, we could not simply filter them out while importing the masterdata, the reason why is because if we assume that:
WBS Element "A" was active in June 2023, while loading the masterdata, it is not going to be filtered out as the WBS Status property value is "unassigned".
Now let us assume that same WBS Element "A" had the Status property value set as "Closed" and no longer "unassigned", what will happen in the dimension during that load? Nothing, because the filter is set on the Status to be "unassgined", hence it will exclude the "Closed" record it should have received, the masterdata on SAC for that dimension will not be updated with "Closed", so that WBS Element remains active in the model itself, which is wrong. We need a way to somehow filter them out on the story level.
Input Control + Masterdata Model!
Input controls received a new set of script APIs in Q2 2023, these allow us to set/getActiveSelectedMembers to an input control, however these APIs currently support active members only, if you attempt to display unbooked values, the getActiveSelectedMembers() will return undefined, this is a limitation that is mentioned in the tooltip description of the API. This will be later addressed by using a dummy masterdata model, forcing all the plannable members to be be active (have posted values).
Planning against only posted values in a planning model, in this case we do not need anything else, we assume that the input control was used only due to the performance degradations experienced as we increase the number of members loaded to a dropdown, or in case the dropdown causes an out of memory error. Setup should be:
Add an input control to the page, select the intended dimension, set it up as desired, in most cases, you would want it to have single selection.
Disable cascading effect.
Adjust Linked Analysis to point at nothing, if this is done properly the cube icon in the input control should appear as a warning.
In the widget context menu>Show/Hide>Warning/Error Icon as well as Header Icon, this is to hide the warning that this widget is not affecting anything, which is in our case ideal as this widget's only usage is retrieving the member keys, which we need to pass to a data action parameter.
Finally set/getActiveSelectedMembers() as desired, a typical call to a data action would look like this: DataAction_P_COSTELMT.setParameterValue("04COSTLEMT",InputControl_COSTELMT.getInputControlDataSource().getActiveSelectedMembers().id);
Planning against unbooked values, such as a mirrored dimension, for example Sender Cost Object and Receiver Cost Object, where all receivers are initially loaded as "unassigned".
In this scenario we need to:
Create the planning dimension as a public dimension, load its masterdata directly from the source, in the following example, I loaded the masterdata of the public dimension ZE_COSTELMT from the BW InfoObject 0COSTELMT, the InfoObject will be available for consumption on SAC in case the flag "Usable as InfoProvider" is ticked. Load the dimension members along with all its properties (attributes).
Create a model that consists only of the planning dimension, of course Date and Version will be there by default, limit the date granularity to "Year", create one measure, assign it to the default key figure "Number of Records". You should end up with a model having a number of rows identical to the unique number of dimension members.
The dummy model can exist only once per tenant, then reused in any unified story/application containing the intended dimension as a planning dimension, regardless of the real planning model in scope, this would be by calling the model in the story or app, adding the dimension input control based on it, then follow the same steps of the Basic Usage section above.
Masterdata Model Structure
Remember, all we need from this input control is capturing the keys of all applicable members and passing them to data/multi actions of any other model as parameter inputs, the input control is ideal for this as it goes not fire a getResponse(Backend call) unless it is open, it buffers additional members as you scroll down or specifically search for a certain member, this is ideal for performance in the 2 use cases this blog post is about.
Filtering out Input Control Values based on Properties:
This section addresses Use Case 2. So far, there is no best practice on how to filter values before populating them to a dropdown list, there is more than one idea, none of them seemed to be performance efficient.
Let us assume we need the Cost Element input control to return all the cost elements but the ones belonging to controlling area "0001", in other words we need to exclude "0001", I was able to fulfill a similar requirement using 2 input controls, one based on the Controlling Area, this one will be excluding "0001", using its cascading effect, it will filter out those values on the main Cost Element input control, the Controlling Area input control can be set as hidden during runtime.
The ideal setup here would be that the input control is based on a dedicated masterdata model, as Linked Analysis on the property filter input control only should be allowed on all widgets, the main reason why we need it to be set to all, is that we are unable to only select other input controls after the "Only selected widgets" option is chosen, hence having a dedicated model is considered as an appropriate workaround in this scenario as the input controls from the same source will only be impacting each other. Cascading effect also needs to be enabled on both input controls.
Property filter setup
Input Controls vs Dropdown lists
Needed using addItem() in a for loop
Number of members
Unlimited (Buffer as you scroll or search)
Limit lies in the getMembers() API used to supply it with values, it will cause an out of memory error after 1000 members
Much faster after the values are supplied
Value Help & Suggestions
Only happen if the input control is either expanded, opened, or further scrolled down
Will anyway happen in order to load the list of values
Filtering member using properties
Can be easily done provided that there is a masterdata model
Hard to be fulfilled
Out of the box
Hard to be fulfilled
Clean your data, check if the end users will really need to plan on such a huge number of members.
Use data/multi action triggers, choosing among all available members is available out of the box.
When in doubt, consult the experts! Ask your Account Executive/Customer Success Partner/Technical Quality Manager about the Premium Hub - CoE Analytics team, we have a variety of enablement, and support services that can help you stabilize, expand, and get the most out of SAP Analytics Cloud.