Automatically Add Epm olap member through data validation.

[Former Member]

Hi All,

I have a requirement as follows:-

We have an Input Schedule for recording data related to Head counts to be hired in HCM model. Earlier we insert new hires by the standard functionality of EPM i.e. EPM-> Insert Members. Now our requirement is to add or insert the new ones by providing them a dropdown list where user will pick or select an employee and that will be automatically add in the Input Schedule. Next time if user wants to add another employee it will select an employee from the drop down list and the selected one will be automatically added in the next row.

Regards

Mayur.

[former_member186338 · Accepted solution]

Sample:

Procedure AddMem is attached to Add button:

Dim epm As New FPMXLClient.EPMAddInAutomation

Public Sub AddMem()

epm.AddMemberToRowAxis ThisWorkbook.Worksheets("Sheet1"), "000", "TITLES:" & ThisWorkbook.Worksheets("Sheet1").Range("B1").Value, 1

epm.RefreshActiveReport

End Sub

For sure it's better to test if the new member already exists in the report (using GetRowAxisMembers).

Vadim

[Former Member · Accepted solution]

Hi Vadim,

Thanks for your prompt reply,I just want to know one thing, is it possible without VBA application?

Mayur

[former_member186338 · Accepted solution]

No, this is absolutely not possible! You want to select some member in the combobox and then - perform action - add this selected member to the row axis! Actions require macro...

Vadim

[former_member186338 · Accepted solution]

P.S. And please close already answered discussions!

[Former Member · Accepted solution]

Hi Vadim,

Thanks for your help it's solved my problem.

Mayur.

[former_member186338 · Accepted solution]

Better code that will test if member already exist:

Public Sub AddMem()

Dim wshCurrent As Worksheet

Dim strMemArr() As String

Dim strMemToAdd As String

Dim strMemID As String

Dim strDimName As String

Dim lngTemp As Long

Dim lngTemp1 As Long

Set wshCurrent = ThisWorkbook.Worksheets("Sheet1")

strMemToAdd = wshCurrent.Range("B1").Value

strDimName = "TITLES"

strMemArr = epm.GetRowAxisMembers(wshCurrent, "000")

For lngTemp = 0 To UBound(strMemArr)

    lngTemp1 = InStrRev(strMemArr(lngTemp), "[") + 1

    strMemID = Mid(strMemArr(lngTemp), lngTemp1, Len(strMemArr(lngTemp)) - lngTemp1)

    If strMemID = strMemToAdd Then GoTo AlreadyExist

Next lngTemp

epm.AddMemberToRowAxis wshCurrent, "000", "TITLES:" & strMemToAdd, 1

epm.RefreshActiveReport

AlreadyExist:

End Sub

Vadim

[former_member186338]

Hi Mayur,

The only way is to use VBA. Near combobox with the list of employees you will have a button.

The macro launched by this button will:

1. Using API AddMemberToRowAxis - add member.

2. Refresh the report.

Vadim