Multiple member selection for expansion in Report/Input schedule in SAP BPC 10

[Former Member]

Business Case:

In a business scenario, End users would want to select multiple products or cost
centers to plan in an input schedule which are not a part of single grouping or hierarchy.

This requirement can be implemented using a VBA macro or without using VBA macro.

Let us see the pros and cons of both these options,

Without using VBA Macro	Using VBA Macro
Less maintenance of report/input schedule since the page key selection pop-up member selection is a standard functionality.	Maintenance may be difficult since we need a hidden range or sheet for expansion of dimension members to display in a custom combo-box for selection.
Time taken to refresh report is less since it is a standard member selector pop-up.	Time taken to refresh report would be more since all members of a particular dimension has to refresh in a hidden sheet.
There is a limit of 30 members which are allowed to be selected (Page key axis selection limit).	There is no limit in the number of members that can be selected.

Here we will see in detail for designing a report/input schedule without using VBA Macro option,

 

In a report/input schedule, if the user wanted to select members of a dimension which are random and

there is no single member selection or base member selection for report expansion.
Then we can use the following method to create a report without using visual basic code (VBA).

 

In this scenario, Entity dimension members will be randomly selected by user,

 

Step 1: Create a dummy report (000) with Entity dimension in page axis.

 

 

Step 2: Select Entity dimension in page axis dimensions and check the option “Allow Multiple Selection”.

 

This option will allow user to select multiple random members.

 

In the dummy report, we can hide the expansion range leaving only the page key selection to be visible.

 

 

Step 3: Create the second report(001) with Entity dimension in row and Time dimension in column expansion.

 

Now EPM Dimension override formula would be written for expansion for second report (Dimension: Entity).

 

We will refer to the selection made by user in the previous step - page axis of the dummy report (000).

 

=EPMDimensionOverride("001","ENTITY",E10)

 

E10 cell contains entity list which will be used for expansion using dimension override formula.

 

Final report would appear like below screenshot, it allows user to select multiple random member of a

dimension in a report and expand with that selection.

 



 

Limitation: There is a limit of 30 members which are allowed to be selected in page key axis.

 

Using VBA: Another way to achieve this requirement is to create an expansion which has entire list of

members in a hidden sheet and use a combobox (ActiveX Controls) to allow user to select

the members in the report.