Skip to Content
avatar image
Former Member

Error in EPM Add-in when double click to select filter with multiple choice

Hi experts,

I'm facing an issue in a BPC 10.1 workbook, with a report that has a multiple selection filter. When we double click to change the filter, with multiple choice an vba error occurrs and the filter doesn't change.

This error appears because a VBA macro is triggered on double click, and it returns the error (file attached error.png).

Facts:

This was working fin in the Office 2010 version and early in version 2013 was working also. It changed it's behaviour since January this year.

It appears that some automatic update was made to office or EPM, and now it is behaving differently.

Versions:

  • EPM 10.0 SP 27 Patch 2 .NET4
  • MS Office Professional plus 2013

Attachments:

on-double-click-window-multiple-filter.png

macro-vba-error.png

Can you please help us with this matter?

Thanks in advance.

Kind regards,

Raquel Oliveira

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Sep 06, 2017 at 02:29 PM

    Sorry, but the issue is not clear!

    1. Explain the required logic! "multiple selection filter" - ???? Provide step by step user actions and expected results!

    2. Never attach images using "Insert File"

    3. Post code using "Insert Code" - it's not possible to copy code from screenshot! Provide full code.

    Add comment
    10|10000 characters needed characters exceeded

  • Sep 06, 2017 at 02:49 PM

    Just to test the following code is working absolutely fine on my computer:

    Option Explicit
    
    Dim epm As New FPMXLClient.EPMAddInAutomation
    
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim strDimMembers As String
    Dim strConn As String
    
    If Target.Address(True, True, xlA1, False) = "$A$1" Then
        Cancel = True
        strConn = epm.GetActiveConnection(ActiveSheet)
        strDimMembers = epm.OpenMemberSelector(strConn, "CATEGORY", "")
        MsgBox strDimMembers
    End If
        
    End Sub

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Sep 07, 2017 at 02:10 PM
        Option Explicit
    
    
        Dim epm As New FPMXLClient.EPMAddInAutomation
    
    
        Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
    
        Dim strCellDoubleClickAdr As String
    
    
        Dim strCellMemberListAdr As String
    
    
        Dim wshCurrent As Worksheet
    
    
        Dim strConnName As String
    
    
        Dim strDimName As String
    
    
        Dim strDimMembers As String
    
    
        Dim strDimMembersArr() As String
    
    
        Dim strFinalDimMembersArr() As String
    
    
        Dim strFinalDimMembersWDescArr() As String
    
    
        Dim strDimMembersWDesc As String
    
    
        Dim lngMaxMemberNum As Long
    
    
        Dim lngTemp As Long
    
    
        strCellDoubleClickAdr = "$E$9"
    
    
        If Target.Address(True, True, xlA1, False) = strCellDoubleClickAdr Then
    
    
            Cancel = True
    
    
            Set wshCurrent = ThisWorkbook.ActiveSheet
    
    
            strCellMemberListAdr = "$A$12"
    
    
            strConnName = epm.GetActiveConnection(ActiveSheet)
    
    
            strDimName = "SOC_DIV_PARCEIRA"
    
    
    
    
            strDimMembers = epm.OpenMemberSelector(strConnName, strDimName, "")
     
    
    
            strDimMembers = Left(strDimMembers, Len(strDimMembers) - 1)
    
    
            strDimMembersArr = Split(strDimMembers, ";")
    
    
            lngMaxMemberNum = UBound(strDimMembersArr)
    
    
            ReDim strFinalDimMembersArr(0 To lngMaxMemberNum)
    
    
            ReDim strFinalDimMembersWDescArr(0 To lngMaxMemberNum)
          
    
    
            For lngTemp = 0 To lngMaxMemberNum
    
    
            strFinalDimMembersArr(lngTemp) = Mid(strDimMembersArr(lngTemp), InStrRev(strDimMembersArr(lngTemp), "[") + 1, Len(strDimMembersArr(lngTemp)) - InStrRev(strDimMembersArr(lngTemp), "[") - 1)
    
    
            strFinalDimMembersWDescArr(lngTemp) = strFinalDimMembersArr(lngTemp) & " - " & epm.GetMemberCaption(strConnName, strDimMembersArr(lngTemp))
    
    
            Next lngTemp
    
    
            strDimMembers = Join(strFinalDimMembersArr, ",")
    
    
            strDimMembersWDesc = Join(strFinalDimMembersWDescArr, ",")
    
    
            Target.Value = strDimMembersWDesc
    
    
            wshCurrent.Range(strCellMemberListAdr).Value = strDimMembers
    
    
        End If
       
        End Sub
    
    
    
    
    
    Add comment
    10|10000 characters needed characters exceeded

    • And what???

      Code is presented in unreadable form with empty lines between each line of code!

      strCellMemberListAdr

      is empty! The rest of the code looks OK.

      Start with simple code and try to reproduce the issue...

  • avatar image
    Former Member
    Sep 07, 2017 at 02:16 PM

    Dear Vadim,

    Thank you for answer. I have provided the full code as requested.

    Regarding the questions you asked, please let me explain better

    The goal of this VBA is to allow the user to select more than one member of the dimension, in the report filter.

    We have implemented a logic that does the following:

    1 - The user opens the report and double clicks the filter

    2 - The VBA code is executed and opens a window where the user can select multiple dimension members

    3 - After the selection, the VBA code writes the selection into excel cell $A$12

    4 - We apply a "dimension members override" function to the report, to expand based on the selected members that are listed in cell $A$12

    This allows the report to expand per multiple selection members.

    The problem is that somewhere in time, this logic stopped working in our key-users computer, because after the members are selected and after click "ok", the macro gives the attached error.

    Have I made myself clearer?

    How should I attach the images?

    Thank you :)

    Raquel Oliveira

    Add comment
    10|10000 characters needed characters exceeded

    • I was unable to reproduce the error... you have to perform more investigation... May be conflict with antivirus software or some other strange things!

      "How should I attach the images?" Easy:

      imgadd.png (18.7 kB)
  • avatar image
    Former Member
    Sep 08, 2017 at 09:10 AM

    Thank you Vadim.

    We are having trouble also in simulating this error because it only give us with a certain excel version.

    Could you please share the Excel version that you have?

    Thank you.

    Raquel Oliveira

    Add comment
    10|10000 characters needed characters exceeded