Skip to Content
0

Filter by property in Data Manager using VBA - BPC 10.1

Sep 20, 2017 at 07:24 PM

104

avatar image
Bruno Marcos

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
Answers Answers and Comments
10 |10000 characters needed characters left characters exceeded
▼ Viewable by all users
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Sort by:
Best Answer
avatar image
Vadim Kalinin Answered Sep 20, 2017 at 07:37 PM
0

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!

Show 1 Share
10 |10000 characters needed characters left characters exceeded
▼ Viewable by all users
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,...
0
Comment