BPC 10 on Open: check ContextMember?

[Former Member]

Hi everybody,

we're on BPC 10 for MS and I want to have a template, where a check is running on open (or before the first refresh), if the member in the Context of a certain dimension has HLEVEL 4 or not. And if the member has HLEVEL <> 4, then the user should be forced to select another member with HLEVEL 4, before a refresh is triggered.

Unfortunately, I have no idea how this can be achieved, anybody got a possible solution?

Thanks, Philipp

[Shrikant_Jadhav]

Hi Philipp,

You can achieve this with the help of macro - BEFORE_REFRESH function.

Populate the HLEVEL in one cell. 

Write a vb code in IF-ELSE block.

You can show desired message in pop window.

Regards,

Shrikant

[Former Member]

Hi,

thanks for the input. Unfortunately, that doesn't quite work. I tried implementing it like this in the Before Refresh Event:

If Worksheets("BottomUp").Range("B11").Value <> "4" Then

'B11 contains the HLEVEL of the context member with

'=EPMMemberProperty(;EPMContextMember (;"OrgStruktur");"HLEVEL")

Dim selectd As String

    selectd = client.OpenFilteredMemberSelector(client.GetActiveConnection(ActiveSheet), "OrgStruktur", client.GetContextMember(client.GetActiveConnection(ActiveSheet), "OrgStruktur"), "HLEVEL=4")
   
    If InStr(1, selectd, ";") > 1 Then

        client.SetContextMember client.GetActiveConnection(ActiveSheet), "OrgStruktur", Left(selectd, InStr(1, selectd, ";") - 1)

    Else

        client.SetContextMember client.GetActiveConnection(ActiveSheet), "OrgStruktur", selectd

    End If
End If

This triggers the event I want, but it triggers it twice (don't know why): when the member has HLEVEL <>4, the member selector pops up (filtered to members with HLEVEL 4), but when I select one member and click OK, the member selector pops up a second time, forcing me to choose again. Strangely, after confirming my choice the second time, it works.

I don't really know why this happens, but I think it's got to do something with reading the HLEVEL from a cell. So I'd be looking for a way to get the HLEVEL of the context member in VBA, maybe that would do the trick...

Or did I implement the idea wrong?

Thanks, Philipp