Filter by property in Data Manager using VBA - BPC 10.1

[Former Member]

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

[former_member186338 · Accepted solution]

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!

[former_member186338 · Accepted solution]

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

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