Skip to Content
0

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

Sep 06, 2017 at 02:12 PM

90

avatar image
Former Member

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

5 Answers

Vadim Kalinin Sep 06, 2017 at 02:29 PM
0

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.

Share
10 |10000 characters needed characters left characters exceeded
Vadim Kalinin Sep 06, 2017 at 02:49 PM
0

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


epmver.png (80.0 kB)
Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Sep 07, 2017 at 02:10 PM
0
    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





Show 1 Share
10 |10000 characters needed characters left 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...

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

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

Show 1 Share
10 |10000 characters needed characters left 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)
0
avatar image
Former Member Sep 08, 2017 at 09:10 AM
0

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


excel.png (26.9 kB)
Show 1 Share
10 |10000 characters needed characters left characters exceeded

I am using Excel 2016 32-bit:

ex2016.png (115.0 kB)
0