cancel
Showing results for 
Search instead for 
Did you mean: 

Automatically Add Epm olap member through data validation.

Former Member
0 Kudos

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.

Accepted Solutions (1)

Accepted Solutions (1)

former_member186338
Active Contributor
0 Kudos

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
0 Kudos

Hi Vadim,

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

Mayur

former_member186338
Active Contributor
0 Kudos

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
Active Contributor
0 Kudos

P.S. And please close already answered discussions!

Former Member
0 Kudos

Hi Vadim,

Thanks for your help it's solved my problem.

Mayur.

former_member186338
Active Contributor
0 Kudos

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

Answers (1)

Answers (1)

former_member186338
Active Contributor
0 Kudos

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