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.

/wp-content/uploads/2013/09/1_276888.jpg

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.

/wp-content/uploads/2013/09/2_276890.jpg

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

/wp-content/uploads/2013/09/3_276891.jpg

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).

/wp-content/uploads/2013/09/4_276892.jpg

=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.

/wp-content/uploads/2013/09/5_276893.jpg

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.

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply