Skip to Content

BPC 10 - Data AccessProfile settings vs member selection

Hi All,

I have Data Access Profiles set up with 1 security dimension ("Cost Centre").

Users have access to selection of "Cost Centre" members, with some being "Read" and some being "Write". I think that's very typical and standard set up.

What I am struggling now is Input Templates. What I want to achieve is to restrict member selection via selection window (I use EPMContextMember formula) to Write members only. At the moment users see all members both Read and Write.

I had a go with filters (formula and member filter) using Owner hierarchy but that's no good because:

  1. another hierarchy to maintain
  2. I'm struggling when Owner >1.I can use "Like" in Member Filter but can't have Users applying filters themselves. I can fix filter to formula but (from what I know) it only works with Owner "is" not "contain"

Is there a way to do it?

Thanks!

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    May 26, 2017 at 03:10 PM

    Sorry, but it's not possible... There is no way to effectively restrict users from selecting read only members.

    Add comment
    10|10000 characters needed characters exceeded

  • May 30, 2017 at 09:55 AM

    Right! I got it working. The workaround is long and nasty but it does work.

    Steps:

    1. BPC Admin
      1. Update Users "Data Access Profiles" with Write/Read members
      2. Update Dimension Owner hierarchy with Write members only
    2. Excel Input Template:
      1. Create BPC table showing given dimensions in a column (in my case “Cost Centre”) . The table will show ALL members (Read & Write) the user has access to.
      2. Create additional column with EPMMemberProperty for [Owner]. This will tell us which members are assigned to the user as “Write”
    3. So far so good. Not it gets little bit tricky as I had to write VBA to get everything working:

    Private Sub Workbook_Open()

    'Getting UserName value in a cell to be used with Filter
    Dim BPCUsername As String
    BPCUsername = Environ("Username")
    Worksheets([sheet with table]).Range([specify cell]).Value = BPCUsername

    'refreshing workbook to show correct (ones with access to ) members for the user
    BPCA.RefreshActiveWorkBook

    ' filtering dimension table using BPC Username (=Owner) value
    Worksheets([sheet with BPC table).Range([range of cells for your members]).AutoFilter Field:=5, Criteria1:="=*" & BPCUsername & "*", Operator:=xlAnd

    ' copy and paste as values the list of members. This information will be used later in dynamic dropdown list
    Sheets([sheet with BPC table).Select
    Range([range of cells for your members]).Select
    Selection.Copy
    Sheets([new blank sheet]).Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Sheets([main sheet where you want your use to be]).Select

    End Sub

    No the last thing to do is to create dynamic dropdown list:

    1. Define new range in Name Manager. I called it “Read_Members” and used formula = ‘[new blank sheet]'!$A$1:OFFSET(‘[new blank sheet]'!$A$1,COUNTA('[new blank sheet]'!$A$1:$A$500)-1,0)
      I used range for 500 rows but obviously its not fixed. Use as many as you need.
      * I've had to copy member values to new sheets, because when Range was linked to the actual table it was picking up all members (unfiltered = Read & Write) and we only want Write.
    2. On the main sheet we need to create dropdown list with our Write members. To do that pick cell -> Data Validation -> List and Source: =Read_Members
      Now after all that, we have a dropdown list with Read members only which users can chose from.
      Last thing to do is to link the value to the actual Input Table. I did it by simply linking EPMOlamMemberO formula to dropdown list cell value .

    The only challenge I have left is: Environ("Username") returns the "owner of the PC" value not the current BPC Username. In other words, when I have other users logged in to BPC on my station (logged in via Xls or BPC website, makes no difference), the value is still my username. It wont make any difference for my process (for me 1 PC = 1 user) but it'd be nice to get the value correct!

    If anyone has an idea, please share!

    Add comment
    10|10000 characters needed characters exceeded