Skip to Content
avatar image
Bruno Marcos

Filter by property in Data Manager using VBA - BPC 10.1

Sep 20, 2017 at 07:24 PM | 135 Views

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

SAP Business Planning and Consolidation, version for SAP NetWeaver
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    avatar image
    Vadim Kalinin
    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

    • Vadim Kalinin
      Sep 21, 2017 at 08:12 AM

      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,...