How to use OpenFilteredMemberSelector API and multiple selection

[Former Member]

Hi all,

I do have 2 queries.

1)

I am  trying to populate member based on some condition. Like if user passes some parameter based on that condition only Id's I have to populate in column.

For example If filtering criteria is Level property for time dimension, If user pass MONTH then I want to populate only those members in specified column having level property equal to MONTH. Like EPMDimensionPropertyValues but for this function I am not able to pass any filtering criteria.

I want to create dropdown list where I can do multiple selection.

I have gone through below link but it is populating all the members.

http://scn.sap.com/docs/DOC-55622

2)

How to use  OpenFilteredMemberSelector this API. What is the use?  Below is code I have written for this API. The code is working but when I select member where it is reflecting?

I am looking for multiple selection so I can't use context and also can't use EPMSelectMultiMemberID since i can't pass any criteria.

Dim epm As New FPMXLClient.EPMAddInAutomation

Sub select()

'epm.OpenFilteredMemberSelector "connection_name", "TIME", "[TIME].[PARENTH1].[2016.Q1]", "LEVEL=QUARTER;calc=Y", True

End Sub

I am looking for multiple selection from drop down list. 

Regards,

Sushant

[former_member186338 · Accepted solution]

"I want to create dropdown list where I can do multiple selection." - dropdown with multiple selection??? Absolutely strange idea... Dropdown is for SINGLE selection...

"In template I have to populate only those members having write access" - CALC property is not related to security...

Please explain your requirements once again!

Vadim

[Former Member · Accepted solution]

Hi Vadim,

Time dimension is not secure I have project dimension as secure dimension. I found VBA code for multiple selection in drop down list. Yes u are correct drop down list is used for single selection.

Below is code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Oldvalue As String

Dim Newvalue As String

On Error GoTo Exitsub

If Target.Address = "$N$10" Then

    If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then

    GoTo Exitsub

    Else: If Target.Value = "" Then GoTo Exitsub Else

        Application.EnableEvents = False

        Newvalue = Target.Value

        Application.Undo

        Oldvalue = Target.Value

        If Oldvalue = "" Then

            Target.Value = Newvalue

        Else

            Target.Value = Oldvalue & ", " & Newvalue

        End If

    End If

End If

Exitsub:

Application.EnableEvents = True

End Sub

I have searched lot so I thought I have to go with drop down list.

I can't go with page axis and EPMSelectMultiMemberID  epm function as it will populate all members as per my understanding.

I do not understand that API how to use. I have gone through VBA code that u have developed but it brings all data.

[Former Member · Accepted solution]

HI Vadim,

Users are having access of all member but for few members having write access and remaining read access. So Input template i have to give selection of project. Only those project having Write access and it should have multiple selection for project.

[former_member186338 · Accepted solution]

First - I do not recommend to use the proposed code, it will create a lot of issues if user will try to change something already selected

You can use:

1. Without VBA: =EPMSelectMultiMemberID and local member to generate comma separated list like:

2. With VBA:

Dim epm As New FPMXLClient.EPMAddInAutomation

Public Sub test()

Dim strMem As String

strMem = epm.OpenFilteredMemberSelector("INPER - SIM", "TITLES", "", "PLANBE=BE1000", True)

ThisWorkbook.Worksheets("Sheet1").Range("B2") = strMem

End Sub

Read more:

To get a comma separated list of member ID's you have to Split strMem in strMemArr() and then perform extraction of ID in the loop as in the code provided in the link.

Vadim

[akos_beres · Accepted solution]

Sushant,

I don't know of a way to filter dimensions based on security authorization in an EPM report. I think that's what Vadim was saying. If you need to do something like that you will need to add another property and use it to get what you want.

Akos

BTW ... if the selection doesn't have to be dynamic and you want to show a handful of members just include ID=XYZ in the filter.

[former_member186338 · Accepted solution]

You will be unable to filter members with write access (using EPM formulas or VBA)...

Vadim

[former_member186338 · Accepted solution]

Property will not help for multiple users with different access profiles!

[Former Member · Accepted solution]

Hi Vadim,

Thank you for your help.

One more question.

Can I populate the members based on any property (Hard coded) by using code that you have mentioned in below link?

As the code is populating all members instead of that specific members based on condition can we populate? If yes can you share VBA code for that.

Thank you Akos beres for taking interest and for your prompt reply.

[former_member186338 · Accepted solution]

There is no way to get only members with some property value! You can get all members in some array, read in the loop property value for each member and in case property value match condition - copy current member to the new array.

Vadim

[akos_beres]

Sushant,

Maybe I'm not understanding what you would like to do but I feel the solutions are fairly simple

1, Use Axisoverride or Dimension override ... here is quick video on how to connect it with selections ... you can also concatenate members

EPM Add-in Academy - Enterprise Performance Management - SCN Wiki

2, The script works like the EPMSelectmember formula ... either use that on the Excel sheet or need to pass the selection values into a cell. Frankly I'm not sure why one would use VBA for that.

Hope this helps

Akos

[Former Member]

Hi Akos,

Thanks for your prompt reply.

To bring these member in my report i will use override function but drop down list is dynamic as per my requirement.

I have given example of time, but actually requirement is users having access of all members of secure dimension out of that some of member having write access and for few read access. In template I have to populate only those members having write access. So based on some property i can populate those members in one column and create dropdown for multiple selection. So I have gone through VBA code that is developed by Vadim but it is populating all master data of dimension I need to populate only ID based on parameter(Cell values).