BPC 10 - Data AccessProfile settings vs member selection

[former_member381420]

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!

[former_member186338 · Accepted solution]

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

[former_member381420 · Accepted solution]

Ok thanks Vadim!

[former_member381420]

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!

[former_member186338]

To get BPC username you can use cell with:

=EPMUser()

[former_member381420]

oh... I knew there must be an easier way! Thanks a lot!

[former_member186338]

There are a lot of other things that can be improved in your code....

For example you have a report with 3 columns:

ID OWNER DummyDATAColumn

Using API functions: GetDataTopLeftCell(Worksheet,ReportID) and GetDataBottomRightCell(Worksheet,ReportID)

You can calculate the range of first 2 columns.

Then you can read the range into array:

Dim varIDOWNERArr() as Variant

varIDOWNERArr = range(...)

Then loop this array to check for LIKE in the second column.

And don't use Copy Paste in VBA, use array assignment to range!

[former_member381420]

You Sir are a genius. A big thank you one more time!