EVDRE Memberset Selector in Treeview

In SAP BPC 7.x EVDRE you may want to filter members according to both attribute values and/or member selections. You can find these complex selection examples in "Usage and Considerations of EVDRE" document like "SELF,DEP and ACCTYPE="INC",ID=Account:SalesKorea". For some of end users it may be complicated to hardcode this selection string into EVDRE Dimension Memberset field.

For one of our clients we decided to leverage MS Excel VBA functionality to achieve easy selection of dimension members in an EVCVW functionality fashion. Of course you can argue we may achieve this functionality with defining hierarchies in dimension but I personally do not like to have many many hierarchies, and prefer flat dimension structure.

Lets start with a EVDRE(1x1) in "Sheet1" which has material dimension in row expansion. As you can see Memberset is by default SELF,DEP.

In Sheet2 prepare a EVDRE for only ROW expansion listing Material dimension and place properties which you want to construct hierarchy based on these properties. In BPC MS you can also use EVLST function but since in NW version you do not have this functionality it is better to use EVDRE.

In Excel VBA Editor start with inserting a UserForm.

You can use default controls like combobox, listbox, checkboxes but if you want to use Treeview you have to add Treeview control from "Additional Controls..."

From "Additional Controls" list you can add Microsoft TreeView Control, version 6.0

Place Treeview and two command buttons into your UserForm.

First command button will construct treeview from a selected region in Sheet2.  VBA coding will be similar to the below one:

Dim nodItem As Node
Dim rangeaddress As Range
Dim level1 As Range
Dim level1name, level1key, level1text As String
Dim level2 As Range
Dim level2name, level2key, level2text As String
Dim dimmember As Range
Dim dimmemberkey, dimmembertext As String
Dim TreeView As TreeView
Set TreeView = UserForm1.TreeView1
TreeView.CheckBoxes = True
TreeView.Nodes.Clear
Set nodItem = TreeView.Nodes.Add(, , "ROOT", "ALL")
nodItem.Expanded = True
level1name = "GRUPICI"
level2name = "YAYINEVITEXT"
Set rangeaddress = Worksheets("Sheet2").Range("B138").Cells
For Each c In Worksheets("Sheet2").Range(rangeaddress.Value).Cells
Set level1 = c.Offset(0, 1)
level1key = level1name & "=""" & level1.Value & ""","
level1text = level1.Value
Set level2 = c.Offset(0, 2)
level2key = level2name & "=""" & level2.Value & ""","
level2text = level2.Value
Set dimmember = c.Offset(0, 3)
dimmemberkey = "ID=""" & c.Value & ""","
dimmembertext = dimmember.Value
On Error Resume Next
TreeView.Nodes.Add "ROOT", tvwChild, level1key, level1text
TreeView.Nodes.Add level1key, tvwChild, level2key, level2text
TreeView.Nodes.Add level2key, tvwChild, dimmemberkey, dimmembertext
Next

Second Command Button enables user to write selected nodes into a cell seperated with commas. Code for second button will be like this:

Dim filterdestination As Range
Set filterdestination = Worksheets("Sheet1").Range("A2").Cells
Dim TreeView As TreeView
Set TreeView = UserForm1.TreeView1
Dim FilterString As String
For Each tnode In TreeView.Nodes
  If Not tnode Is tnode.Root Then
    If tnode.Checked = True Then
     FilterString = FilterString & tnode.Key
    End If
  End If
Next
If Len(FilterString) > 0 Then
  FilterString = Left(FilterString, Len(FilterString) - 1)
  filterdestination.Value = FilterString
  Unload UserForm1
Else
  MsgBox ("Please Select Material")
End If

Now our UserForm is ready for users to call, so place a Shape object in Sheet1 and assign a macro to this shape.

When you click on shape object you will get a hierarchical representation of Material dimension like this

After you select dimension members and attribute nodes and click on commandbutton2 as you can see comma seperated memberset is written to destination cell specified in code.

Now you can pass these selected values to EVDRE Memberset field. In case of no selection you may want to use EVCVW option, so using an IF formula for checking empty selection will be beneficial.

SAP BPC allows us to use flexibility of Excel, in this example I have tried to show a simple solution. I hope developers may add some simple but handy functionalities in coming service packs.

You may want to examine a simple macro example without EVDRE's, download here

Best Regards