Skip to Content

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.

image

Lets start with a EVDRE(1×1) 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

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

  1. Thomas Unterner
    Thanks for sharing this.
    I totally agree with you, the number of parallel hierarchies must be kept as low as possible. That is why your solution is very interesting. I like your approach to leverage MS Excel VBA functionality to achieve this.

    I would just add an expand and refresh after the user hits the Copy selected button, so that the template refreshes automatically:

    Private Sub CommandButton2_Click()
    Dim filterdestination As Range
    Set filterdestination = Worksheets(“Sheet1”).Range(“B2”).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
      Application.Run “Mnu_eTools_ExpandAndRefresh”
    Else
      MsgBox (“Please Select [dimname]”)
    End If
     
    End Sub

    Best regards,
    Thomas

    (0) 

Leave a Reply