centers to plan in an input schedule which are not a part of single grouping or hierarchy.
|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).
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.