Skip to Content

Filter by property in Data Manager using VBA - BPC 10.1

Hi experts,

I've read in other topic (https://archive.sap.com/discussions/thread/3312072) that it is not possible to call a data manager through VBA passing a filter for a specific dimension.

Because that post is a little out of date, I'd like to know whether this constraint still remains in BPC 10.1.

For instance, I'd like to call a data manager that contains dimension country to be selected and only show those countries in specific area (area being a property in dimension country) and not the full list of countries

Regards,

Bruno

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Sep 20, 2017 at 07:37 PM

    Still no way to filter data in the SELECTINPUT prompt in DM advanced script.

    Alternative - use VBA:

    1. Get list of required members (using special hidden report or looping all members of dimension):

    https://blogs.sap.com/2014/06/04/bpc-nw-10-vba-to-get-dimension-members-list-and-properties/

    2. Use listbox in VBA form to allow user to select from the list mentioned above. TreeView control can be also used (more complicated).

    3. Launch DM using VBA passing parameters to DM package:

    https://blogs.sap.com/2017/06/16/simple-vba-function-to-pass-parameters-to-dm-packages/

    If something is not clear - please ask additional questions!

    Add comment
    10|10000 characters needed characters exceeded

    • P.S.

      "Get list of required members (using special hidden report or looping all members of dimension):" - I have added a new blog:

      https://blogs.sap.com/2017/09/21/bpc-nw-10-vba-function-to-get-dimension-members-list-by-property-value/

      Or you can simply use OpenFilteredMemberSelector API function for steps 1 and 2:

      Public Sub SelectMembers()
          Dim strMembers As String
          Dim strMem() As String
          Dim lngTemp As Long
          Dim lngTemp1 As Long
          
          strMembers = epm.OpenFilteredMemberSelector(epm.GetActiveConnection(ThisWorkbook.Worksheets("Sheet1")), "SOMEDIMNAME", "", "SOMEPROPERTYNAME=SOMEPROPERTYVALUE", True)
          strMembers = Left(strMembers, Len(strMembers) - 1) 'remove last ";"
          strMem = Split(strMembers, ";") 'string to array of strings
          For lngTemp = 0 To UBound(strMem)
              lngTemp1 = InStrRev(strMem(lngTemp), "[")
              strMem(lngTemp) = Mid(strMem(lngTemp), lngTemp1 + 1, Len(strMem(lngTemp)) - lngTemp1 - 1)
          Next lngTemp
          strMembers = Join(strMem, ",")
          MsgBox strMembers
      End Sub

      The resulting comma separated list of members strMembers can be used in ExeceuteDM call:

      strAnsw(0) = "%SELECTION%P|DIMENSION:AUDIT_TRAIL|AT_BPC_INPUT|DIMENSION:CATEGORY|FH2|DIMENSION:COMPANY_CODE|" & strMembers & "|DIMENSION:COORDER|IO_NONE|DIMENSION:COST_CENTER|CC_NONE|DIMENSION:CURRENCY|LC|DIMENSION:FUNCTIONAL_AREA|FA_NONE|DIMENSION:PROFIT_CENTER|PC_NONE|DIMENSION:P_ACCOUNT||DIMENSION:TIME|2013.02,2013.03,2013.04|DIMENSION:TRADING_PARTNER|TP_NONE
      ExeceuteDM strAnsw,...