cancel
Showing results for 
Search instead for 
Did you mean: 

Row/Column member selector run from VBA

Former Member
0 Kudos

Hello Gurus,

Is there easy way to open Row/Column axis member selector using VBA?

I need to create custom button in a report which will allow user to select members in TIME dimension which in my report is in column axis.

I already tried solutions involving recording macro or using EPMDimensionOverride.

Each solution either failed or didn't meet requirements so I guess the easiest way would be to write VBA code which will behave exactly as if user clicked on dimension in time axis as presented on below screenshot.

Any help on how to do this or what functions/macros to use will be highly appreciated.

Many thanks!

Maciej

Accepted Solutions (1)

Accepted Solutions (1)

former_member186338
Active Contributor
0 Kudos

Hi Maciej,

You have to use the code like:

Dim epm As New FPMXLClient.EPMAddInAutomation

Public Sub SelMem()

Dim strTimeMembers As String

Dim strConnName As String

strConnName = "INFILE - SIM"

strTimeMembers = epm.OpenMemberSelector(strConnName, "PERIODS", "")

MsgBox strTimeMembers

End Sub

The Member Selector dialog will be:

In strTimeMembers you will receive the list of selected members:

You can parse this string to array of members and use it later for EPMDimensionOverride formula or VBA code...

Vadim

Former Member
0 Kudos

Thank you Vadim!

With your guidance I managed to meet the requirement.

Regards,

Maciej

Answers (1)

Answers (1)

Former Member
0 Kudos

Gurus,

I have one more concern regarding this topic.

I have implemented the solution as Vadim suggested and it works perfectly fine on my computer with EPM SP15.

However we have found out that on a computer with EPM SP18 installed function EPMDimensionOverride didn't work. After downgrade to EPM SP15 it started working nicely.

Are there any know issues regarding function EPMDimensionOverride and EPM SP18?

Regards,

Maciej

former_member186338
Active Contributor
0 Kudos

Ups,

Just tested on SP 18 Patch 2 Net 4 - EPMDimensionOverride works fine.


Excel 2010 32 bit


Vadim

former_member186338
Active Contributor
0 Kudos

Ups,

Test with full member name like: [INACCT].[PARENTH1].[2020202020704]

Not working!

You can remove in VBA the full name part!

Vadim

Former Member
0 Kudos

That's it. In my case I was using full member name like in the example above.

Isn't this a bug since " epm.OpenMemberSelector(strConnName, "PERIODS", "")" returns string which consists of members in full name?

Anyways, thanks! I will have to keep in mind not to upgrade customer's workstations to EPM SP18 unless I change in VBA members into short form.

Regards,

Maciej

former_member186338
Active Contributor
0 Kudos

Simply use the following code:

Dim epm As New FPMXLClient.EPMAddInAutomation

Public Sub SelMem()

Dim strConnName As String

Dim strTimeMembers As String

Dim strTimeMembersArr() As String

Dim lngTemp As Long

strConnName = "INFILE - SIM"

strTimeMembers = epm.OpenMemberSelector(strConnName, "PERIODS", "")

strTimeMembers = Left(strTimeMembers, Len(strTimeMembers) - 1)

strTimeMembersArr = Split(strTimeMembers, ";")

For lngTemp = 0 To UBound(strTimeMembersArr)

    strTimeMembersArr(lngTemp) = Mid(strTimeMembersArr(lngTemp), InStrRev(strTimeMembersArr(lngTemp), _

        "[") + 1, Len(strTimeMembersArr(lngTemp)) - InStrRev(strTimeMembersArr(lngTemp), "[") - 1)

Next lngTemp

strTimeMembers = Join(strTimeMembersArr, ",")

MsgBox strTimeMembers

End Sub

Vadim