Is that possible to automatically update report connection when Data connect changed in EPM report

[former_member772554]

Hello experts,

It always need to change the report connection manually even the data connection has already been changed, Is that possible to automatically update report connection when Data connection changed in EPM report?

(Data Connection is changed from A to B, but Report connection keeps A)

[former_member186338 · Accepted solution]

Just checked the code

Public Sub ChangeCon()
Dim epm As New FPMXLClient.EPMAddInAutomation
Dim strConn As String
Dim strServer As String
Dim strEnvironment As String
Dim strModel As String

strServer = "http://XXXXXXXXXXX:8080/sap/bpc/"
strEnvironment = "XXXXXX"
strModel = "Consolidation" '"PLANNING_PER"


strConn = "_FPM_BPCNW10_[" & strServer & "]_[" & strEnvironment & _
        "]_[" & strModel & "]"
epm.ChangeReportConnection ThisWorkbook.ActiveSheet, "000", strConn

End Sub

Working fine, no errors

[former_member772554 · Accepted solution]

Thanks so much!!! Vadim...that works as I expected!

[former_member186338]

To ensure that proper connection is selected you can create a VBA code to:

1. Disconnect

2. Connect to particular server+environment+model (using server username and password)

I have created Excel Add-In to quickly switch between multiple connections. When I am using this VBA tool my report and data connections are always in sync.

[former_member772554]

Hello Vadim,

Thanks for your reply!

Could you share your this VBA tool and code...thanks so much

[former_member186338]

hailongjin

No, I can't share my tool (it's a complex development)

connect code:

    strConn = "_FPM_BPCNW10_[" & strServer & "]_[" & strEnvironment & _
        "]_[" & strModel & "]"
    epm.Logoff
    epm.Connect strConn, strUser, strPassword

[former_member772554]

Hello Vadim,

I find a API "ChangeReportConnection" and write below VBA to change the report conn.

However it always failed with error, and told me a syntax error with bpc.ChangeReportConnection(ws,"000",StrConn)

Dim bpc As New EPMAddInAutomation

Sub refresh_Click()

Dim StrConn As String
Dim ws As Worksheet

Set ws = ActiveSheet
StrConn = "_FPM_BPCNW10_[http://192.168.7.159/sap/bpc/]_[SUNACBPC]_[PLANNING]"

bpc.ChangeReportConnection(ws,"000",StrConn)

End Sub

Please help to check and give me some advice on how to revise this. thanks.

[former_member186338]

hailongjin

Sorry, but why do you want to use ChangeReportConnection?

look at my code: disconnect and connect!

[former_member772554]

Vadim,

As I intend to make this as a standard button in the report that every end user can use to switch the report Model conn if they want without logoff and logon, furthermore I cannot pre-define Username & Password for each user...

[former_member186338]

hailongjin

"every end user can use to switch the report Model conn" - strange idea! What is the business case?

Do you have identical models in production environment?

Why user need to switch report connection?

P.S. By the way, there is also:

SetActiveDataManagerConnection(string connectionString)

[former_member772554]

vadim.kalinin

Yeah...these two models are identical with storing different category data.

So I want to make a easy way for user to switch.

[former_member186338]

hailongjin

"two models are identical with storing different category data" - design issue! Strange approach.

[N1kh1l]

hailongjin Hello,

Please make sure Your report/Input form is not set to store connection and Environment details. Goto Edit Report > Options and make sure these below options are checked. Once the report is saved with these options set, next time it should automatically switch the connection.

Please Upvote and Accept if the answer helps you.

Regards

Nikhil

[former_member772554]

Dear Nikhil,

Thanks for your reply!

I have a try and update report options as you mentioned...but not work. I still need to switch report connection from A to B manually.

Please take a look if anything incorrect I maintained below.

[N1kh1l]

hailongjin,

I tested it in my landscape with below steps.

1. in My Dev system , In Report Editor Under options, I made sure that Do Not Store Connection and Do not store Environment in connection are checked.
2. I cleared and refreshed my metadata cache for the report from More as shown below.

   

3. I save my report on server as well as local desktop.

4. I log off from my Dev system and log on to QA system and open the report from my desktop. Report is connected to QA system.

Please try these steps and see if it works for you.

Regards

Nikhil