BPC10 ExpandActiveWorkBook API seemed Unreliable. Any fixes?

[Former Member]

Dear BPC Experts,

We have recently upgraded from BPC7.5 to BPC10. We are using SP17 right now.

We have invested several years in developing EVDRE reports (many of them) in the BPC7 environment and hence we are trying to make use of many of the EVDRE templates in BPC10.

In BPC10, you have to go to an EVDRE sheet before the "Expand" menu become available (what a pain!), I have written an Excel macro that made use of the EPM API to "Expand Active Workbook" by clicking a button. Here is the code:

'//////////////

Public EPMApi as New FPMXLClient.EPMAddInAutomation

Sub ExpandActiveWorkbook ()

     EPMApi.ExpandActiveWorkbook

End Sub

'///////////

The code seems to work most of time... However, in a few occasions (seems pretty random!), I noted that one or two sheets did not get expanded or refreshed (or may have gotten refreshed but data was not downloaded). This is not good as I have >40 sheets in a file! Imagine the tasks of having to check every sheet just to make sure data is downloaded properly after expanding or refreshing? Note in BPC7.5, the macros (based on MNU_ETOOLS_xxxx)  worked great (never had any issues).   

I wonder if anyone has encountered similar issues (using VBA code or just Expand Workbook from the EPM menu bar)?

In addition, I wonder if anyone has a fix to make ExpandActiveWorkbook run reliably (any options to set? different expand/refresh commands, etc.).

Thank you very much for your insights and help in advance!

Ken

[former_member182709 · Accepted solution]

Ken,

Instead of using

EPMApi as New FPMXLClient.EPMAddInAutomation

Can you try to use the way below to declare the EPMApi.

Dim EPMApi As Object

Set EPMApi =
Application.COMAddIns("FPMXLClient.Connect").Object

This is a official way to declare the EPM objects.

Charlie

[Former Member · Accepted solution]

Hi Ken -

Do you have a lot of old EV functions in this template?  You may want to try converting them to the corresponding EPM functions.  We noted that even when using the EVDRE in 10.0, the templates / reports function much better when replacing the EV functions with EPM. 

Also, you may want to try adding another line of code right after the expansion to refresh the active workbook.

Thanks,

Rich

[Former Member · Accepted solution]

Thank you very much, Richard for the suggestions!

I tried replacing two EV functions and I noted very little difference in the results.

The two EV functions I tried:

     EVSND ==> EPMSaveData

     EVCGT ==> EMPCOMMENTFULLCONTEXT

I got virtually the same results (speed of response, etc.).

As a matter of fact, I believe that SAP has written the two EPM function and "copied" the two to become EVSND and EVCGT (for compatibility with BPC7.5 reports) ... The reason that let me to this belief is that in EVSND (BPC7.5), I used to be able to send a data point or a value calculated within the EVSND formula. However, now in BPC10, the data point that I want to send HAS to be residing within a cell (no more in function calculation)! The same behavior in EPMSaveData function.

If anyone has insights into the differences (if any) in EPM functions vs. EV functions in BPC10, would appreciate if you could share.   

Many thanks for looking and for sharing your knowledge and solutions,

Ken

[former_member182709 · Accepted solution]

Ken,

Ev function is 7.5 function. Even though it's officially supported by EPM Add-in, EPM function works better than Ev function as EPM function is native function supported by EPM Add-in.

Charlie