Call Report from other Model - Custom Menu BPC 10.1

[Former Member]

Hi Experts,

I want to create a custom menu for the users where they can connect to reports from any connection.

To do so, I am using function "OpenSpecificDocument" and trying to declare a connection as last parameter as mentionned in SAP documentation :

"OpenSpecificDocument(documentNameAs String, TeamId As String, subFolderRequested As String, subModule As String,[connectionString As String])"

Unfortunately I don't understand the format exepcted in the last parameter and don't manage to make it work.

I tried via EPM function :

=EPMExecuteAPI("OpenSpecificDocument";"description in cell";"REPORTS\myreport.xlsx";_EPM_[% SolutionName%]_[%server%:%port%]_[%environment%]_[%model%]")

or via VBA :

Sub OpenDoc()
Dim EPM As New FPMXLClient.EPMAddInAutomation

Set EPM = New EPMAddInAutomation Application.Run"EPMExecuteAPI", "OpenSpecificDocument", "Texte","REPORTS\Test11.xlsx", "", "", "",";"_EPM_[% SolutionName%]_[%server%:%port%]_[%environment%]_[%model%]"

EndSub

None of these solutions is working. Do you know what should be filled in the %SolutionName% variable?

Any well will be appreciated.

Best Regards,

Axel

[former_member186338 · Accepted solution]

Hi Axel,

In VBA please execute something like:

Dim EPM As New FPMXLClient.EPMAddInAutomation

Dim strConnStr as String

strConnStr = EPM.GetActiveConnection(ThisWorkbook.ActiveSheet)

MsgBox strConnStr

Please read help:

42.4.4.7 GetActiveConnection

Vadim

[Former Member · Accepted solution]

Hi Vadim,

Thank you for your help. I could determine my connection syntax.

One weird thing is it does show "_EPM_*" but "_FPM_*" plus it adds a "_[false]" statement at the end.

%SolutionName% should be standard as it shows "BPCNW10"

So I include this connection within my code (with "FPM" then "EPM" and with or without the "_[false]"), it shows the following error :

EPM - General Error

%server% %environment% %model%

The variables being what I put in the VBA code.

I searched on SCN but could not find a post with the same error message.

Do you have any idea where this comes from?

Axel

[former_member186338 · Accepted solution]

Sorry Axel,

I do not understand your issue...

If I launch the following code:

Public Sub test()

Dim EPM As New FPMXLClient.EPMAddInAutomation

Dim strConnStr As String

strConnStr = EPM.GetActiveConnection(ThisWorkbook.ActiveSheet)

Debug.Print strConnStr

End Sub

In the Immediate window I will see strConnStr as:

_FPM_BPCNW10_[http://finbpcdev2:55000/sap/bpc/]_[SIM]_[INFILE]_[false]

Where:

[http://finbpcdev2:55000/sap/bpc/] - Server URL

[SIM] - Environment

[INFILE] - Model

You can use this string in the other functions...

Vadim

[former_member186338 · Accepted solution]

Just to confirm:

Code:

Public Sub test()

Dim EPM As New FPMXLClient.EPMAddInAutomation

Dim strConnStr As String

Dim strDimListArr() As String

strConnStr = EPM.GetActiveConnection(ThisWorkbook.ActiveSheet)

strDimListArr = EPM.GetDimensionList(strConnStr) 'Connection string required!

Debug.Print Join(strDimListArr, ";")

End Sub

Result:

BE;CORPDIR;INACCT;PERIODS;PLANTYPE;TITLES;MEASURES - correct list of dimensions!

Vadim

[Former Member · Accepted solution]

Hi Vadim,

Sorry if I was not clear but this is what I have done.

I execute :

Sub OpenDoc()

Dim EPM As New FPMXLClient.EPMAddInAutomation

Set EPM = New EPMAddInAutomation

Application.Run "EPMExecuteAPI", "OpenSpecificDocument", "Texte", "REPORTS\myreport.xlsx", "", "", "", "_FPM_BPCNW10_[myserverurlandport]_[myenv]_[mymodel]"

End Sub

And I get the message attached.

Axel

[former_member186338 · Accepted solution]

Ups, but the error message shows that you really used not real names:

_FPM_BPCNW10_[myserverurlandport]_[myenv]_[mymodel]

instead of something real as in my sample:

_FPM_BPCNW10_[http://finbpcdev2:55000/sap/bpc/]_[SIM]_[INFILE]_[false]

Vadim

P.S. Test my code sample with EPM.GetDimensionList(strConnStr)... it's simple and will show that connection string is fine. Then continue with OpenSpecificDocument...

[Former Member · Accepted solution]

Vadim,

I am sure of the purpose of this post but tried to execute it anyway.

Unfortunately nothing happens. I have no error message, nothing shows.

I simply copied/pasted your code without any modification.

Axel

[former_member186338 · Accepted solution]

Sorry Axel,

You need to have some VBA knowledge...

"I simply copied/pasted your code without any modification." - Do you now how to see results of Debug.Print in the immediate window??

In general, I am not sure that you are using correct parameters for OpenSpecificDocument

Test this working code assuming the following:

1. Open... -> Open Server Root Folder...

2. Let's open the file TT.xlsx

3. Use the following code:

Public Sub test()

Dim EPM As New FPMXLClient.EPMAddInAutomation

Dim strConnStr As String

Dim strDimListArr() As String

strConnStr = EPM.GetActiveConnection(ThisWorkbook.ActiveSheet)

EPM.OpenSpecificDocument "TT.xlsx", "", "REPORTS\WIZARD\", "", strConnStr

End Sub

Look on OpenSpecificDocument parameters!!!

It works fine!

Vadim

[former_member186338 · Accepted solution]

P.S. "I am "not" sure of the purpose of this post but tried to execute it anyway." - the purpose is simple: to demonstrate that connection string format is fine using simple function GetDimensionList with only single parameter!

[Former Member · Accepted solution]

Vadim,

Sorry my screenshot is misleading. I modified it to hide connection values but I did modify the code as you mentionned in your example with the real values.

[former_member186338 · Accepted solution]

Look here:

It's a working code!

[Former Member · Accepted solution]

Vadim,

Unfortunately my knowledge in VBA is very poor.

But still I managed to make my code work with the help of yours.

I had left the quotes around "strConnStr"

I deleted it and my the code is now working fine.

Thank you very for your help.

Best Regards,

Axel

[former_member186338 · Accepted solution]

Ups - what do you mean by "I had left the quotes around "strConnStr""??

strConnStr is a string variable defined here:

Dim strConnStr As String

And assigned:

strConnStr = EPM.GetActiveConnection(ThisWorkbook.ActiveSheet)

And used as the last parameter of OpenSpecificDocument

But you can construct it yourself!

Vadim

[Former Member · Accepted solution]

Just to complete your answer, as my objective was to open a report from another example, here is what I have done based on your example. This macro runs well and connects by default, the report called with the connection specified.

Sub OpenDoc()

Dim EPM As New FPMXLClient.EPMAddInAutomation

Dim strConnStr As String

Set EPM = New EPMAddInAutomation

strConnStr = "_FPM_BPCNW10_[http://finbpcdev2:55000/sap/bpc/]_[SIM]_[INFILE]_[false]"

EPM.OpenSpecificDocument "myreport.xlsx", "", "REPORTS\", "", strConnStr

End Sub

Nevertheless I have another question :

Is it possible to realize the same action double clicking on a cell?

I tried this : =EPMExecuteAPI("OpenSpecificDocument";"Description of the cell";"REPORTS\myreport, ,_FPM_BPCNW10_[http://finbpcdev2:55000/sap/bpc/]_[SIM]_[INFILE]_[false]")

But it is not working.

Does anyone know how I can get the same result as the macro directly with EPMExecuteAPI function in a cell?

Axel

[former_member186338 · Accepted solution]

You have to use correct syntax (described in help)! API parameters have to be passed one by one as Excel function parameters.

In my sample it will be:

=EPMExecuteAPI("OpenSpecificDocument";"OpenDocTT";"TT.xlsx";""; "REPORTS\WIZARD\";"";"_FPM_BPCNW10_[http://finbpcdev2:55000/sap/bpc/]_[SIM]_[INFILE]_[false]")

Working!

Vadim

[Former Member · Accepted solution]

Vadim,

I am trying to access the report from another model in the same environment. I used the same exact syntax you mentioned above but am getting the error saying "unable to contact the server, there is no end point listening at the server name."

=EPMExecuteAPI("OpenSpecificDocument","OpenDocTEST","TEST.xlsx","","REPORTS\WIZARD\","","_FPM_BPCNW10_[http://dlasccdbd401.r1-core.r1.aig.net:8001/sap/bpc/_[SIM]_[INFILE]_[false]")

This is working when I have report in some team folder(not Company) and I mention that team name as second parameter after the report name.

Do we always need to mention the team folder . I have all my reports and input forms in Company folder only. How shall I access them.Please let me know if I missing anything in the above formula.

I tried using the VBA code also but that is also working only if I mention the team folder. Please advise

Best Regards,

Saila

[former_member186338 · Accepted solution]

Sorry, but "_[SIM]_[INFILE]_[false]")" - this is from my sample, names of my environment and model...

Vadim

[Former Member · Accepted solution]

I just took your example but used the correct server connection. Here is my entire syntax.

=EPMExecuteAPI("OpenSpecificDocument","TestReport","TestRPT.xltm","",
"REPORTS\","","_FPM_BPCNW10_[http://dlasccdbd401.r1-core.r1.aig.net:8001/sap/bpc/]_[GLOBALENV]_[GLOBALMODEL2]_[false]")

Here GlobalModel2 is where TestRPT.xltm report is saved under company reports folder. I logged in GlobalModel1 and trying to use this formula to open that report but getting error.

Hope I made it clear now.

Regards,

Saila

[former_member186338 · Accepted solution]

Just tested once more:

I am connected to the model ADVSALES in the environment SIM.

I want to open file in the model INFILE in the same environment SIM, located in:

Using the formula:

=EPMExecuteAPI("OpenSpecificDocument";"OpenDocTT";"TT.xlsx";""; "REPORTS\";"";"_FPM_BPCNW10_[http://finbpcdev2:55000/sap/bpc/]_[SIM]_[INFILE]_[false]")

The file is opened, no issues.

Vadim

[Former Member · Accepted solution]

Thank you Vadim,

It's working for me now. I had to correct my file name.

Best Regards,

Sailaja

[former_member409271 · Accepted solution]

Hi Vadim

I am trying to do the same as you described above, only difference is that I am on the Microsoft version of BPC 10.1

Using your code a get an EPM - General error with my server name,Environment name and Model appearing.

Is the code different for the Microsoft version? I assume it would differ at the section of code that states:  "_FPM_BPCNW10"

Any help in this regard will be appreciated.

Regards

Aadil

[former_member409271 · Accepted solution]

Hi Vadim

I replaced "_FPM_BPCNW10" with "_FPM_BPCMS10" , it seems to have worked now.

Regards

Aadil