EPM Formula to change Context Member Value

[Former Member]

dear BPC Gurus

Currently to change context value, either I've to go to the Context Toolbar and choose the values, or, I use the formula EPMContextMember, double click it and then select the value.

Is there a way that I can put the desired value in cell A1, and then, a EPM formula would point to cell A1, and upon refresh, the Context Member Value = Value in cell A1?

[Former Member]

Hi,

Let  me correct my VBA code.

Dim epm As New FPMXLClient.EPMAddInAutomation

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim KeyCells As Range

    Dim conn As String

    Set KeyCells = Range("A1")

   CONNE = epm.GetActiveConnection(ActiveSheet)

If Target.Address = "$A$1" Then

epm.SetContextMember CONNE, "VERSION", Range("A1")

epm.RefreshActiveSheet

End If

End Sub

Vadim you are correct. Edmund Low Please explain your requirement in detail.

[Former Member]

Hi,

Yes you can use override functionality as mentioned by Rahul.

Another work around for you is use VBA code. A small VBA code will solve your problem nothing you have to do just change the value in Cell "A1" report will automatically gets refresh.

Note: VERSION is dimension name

Please follow instruction.

1) Open VBA editor(Alt+F11)

2) Select the sheet where your changing the value.(Sheet1)

3)Paste below code in sheet1.

Dim epm As New FPMXLClient.EPMAddInAutomation

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim KeyCells As Range

    Dim conn As String

    Set KeyCells = Range("A1")

    CONNE = epm.GetActiveConnection(ActiveSheet)

    If Not Application.Intersect(KeyCells, Range(Target.Address)) _

           Is Nothing Then

'set context member

epm.SetContextMember CONNE, "VERSION", Range("A1")

        End If

        epm.RefreshActiveSheet

End Sub

Note: you have to put the above code in the specific sheet where u are going to change value.

Hope this will solve your problem.

[former_member186338]

The main question is - what for?

[former_member186338]

Please explain the scenario - what do you want to achieve! Why do you need to change context? why not to change axis member?

[former_member225135]

Hi,

You can use EpmDimensionOverride functionality

Regards,

Rahul