AFTER_REFRESH

[Former Member]

Hi,

We have written Macros (using Visual Basic) in a few input schedules in order to build adequate controls. One requirement was that planning should not be allowed in a few rows & columns. The macro automatically runs when we open the input schedule.

When we refresh the data, the macro stops working. We want the macro to work at all times...We used a few custom VBA Functions (i.e. AFTER_REFRESH) and wrote a VB code to run the above macro after every refresh. However the same does not work..

Please advise.

Regards,

Shibu

[Former Member]

Yes, can anyone from SAP tell us what even fires on a double-click event? Can we intercept it? Thanks.

[former_member186498]

Hi,

try adding the code even in AFTER_EXPAND and AFTER_CHANGECVW.

Kind regards

Roberto

[Former Member]

I added the two custom VBA functions..However it is still not meeting our requirement. I mean the function is called when we refresh the workbook or change the current view. But it does not work in the following situation -

Node 1 -

Member A

Member B

Node 2 -

Member C

Member D

We have set memberset as SELF,DEP. Now we need to double click on Node 1 to plan values for Member A and B. We want certain VBA code to be executed when the user double clicks on Node 1 / Node 2...

Kindly advise....

[former_member186498]

Hi,

can you add please the VBA code inside the events?

Kind regards

Roberto

[Former Member]

We are not sure which event is triggered when we double click a particular node.....We have used AFTER_REFRESH function (but are not sure if this is the right event). We feel that the system refreshes data when we double click on Node 1 / Node 2.

The sample VBA code is -

Function AFTER_REFRESH(Argument As String)

MsgBox "Hello world - AFTER REFRESH"

AFTER_REFRESH = True

End Function

[former_member186498]

Hi,

sorry but I don't understand, at the beginning you wrote "The macro automatically runs when we open the input schedule" and than "When we refresh the data, the macro stops working".

Now you wrote that the issue is on the double click on the node that you think it will refresh the data.

But double click on a Worksheet in VBA triggers only the before_doubleclick event. Why you think that the data should be refreshed after the double click? Have you added some code in this event?

Can you explain better what are you trying to do? Some like a drill through via VBA?

In the first issue you wrote "One requirement was that planning should not be allowed in a few rows & columns."

Is this inherent to the problem? If so, you can explain how you managed this requirement?

If you explain better the situation and add the VBA code maybe we can try to help you.

Kind regards

Roberto

Edited by: Roberto Vidotti on Jan 3, 2012 2:56 PM

[Former Member]

Hi Roberto,

Thank you for your response. Sorry for all the confusion.

Yes the issues are interlinked with each other. I will try to explain the issue completely u2013

1) We are creating excel input templates for Planning. The memberset option used for our planning headers is SELF,DEP. Hence we need to double click on nodes to see the child members and enter the plan values. The requirement is to build a few controls in these input templates (e.g. the user should not be allowed to input values in a few cells i.e. in case a few criteria is met). For meeting this requirement we created an excel macro (using VB code). This macro prevents data entry in the cells based on certain criteria. This macro is meeting our requirement.

2) The above macro is loaded when we open the Input Template. While testing the above macro, we observed that it stopped working when we performed a few operations in the input template (e.g. when we refresh data, expand a particular node, expand the worksheet, change current view, etc).

3) To resolve the above issue, we used custom VBA functions provided by SAP BPC. We used functions - AFTER_REFRESH, AFTER_CHANGECVW, AFTER_SEND, AFTER_EXPAND and called the above macro in each of the above functions. This helped us to resolve the issue of the macro being disabled during data refresh, data send, workbook refresh, change current view. E.g. when we refresh data, the macro stops working and is called again (because of the VB coding in AFTER_REFRESH function).

E.g. of VB Code -

Function AFTER_REFRESH (Argument As String)

MsgBox Argument

Application.run (Original Macro mentioned in point 1)

AFTER_REFRESH = True

End Function

So the original macro is called again after refresh, data send, expand worksheet, change current view.

4) Now the issue is the macro stops working when we expand a particular node. E.g.

Say the member hierarchy is as follows u2013

Node 1 u2013

Member A

Member B

Node 2 u2013

Member C

Member D

When we double click on Node 1, the macro stops working. We want the macro to continue working even after the user expands a particular node.

We were under the assumption that when we expand a particular node, the system automatically refreshes data. Hence we were expecting AFTER_REFRESH function to be called when we expand a nodeu2026 However this is not the case.

We wish to know, the event that is triggered when a user expands a node. In case we get that event, we can call the macro once this event is triggered.

Kindly let me know if you require any further inputs on this.

Regards,

Shibu

[former_member186498]

Hi Shibu,

thanks for the explanation, some considerations:

1) if you put SELF,DEP in the expansion and Members A and B are directly dependent from Node 1, when you "expand all" the worksheet you should see

Node 1 u2013

Member A

Member B

and you don't need to double click to see them, if you don't see the children, that it's because they aren't direct children, than you must use SELF, BAS on this dimension expansion.

2) please note that the macro code is triggered by an event f.e open_workbook, after_refresh, the macro will be executed but after the execution of the code it's not active anymore, it's will activate only when another event call this macro again.

The drill down (double click) call an "expand all" so it should trigger the AFTER_EXPAND function.

Kind regards

Roberto

[Former Member]

Dear Roberto,

The planning headers are as follows u2013

Node 1 u2013

Member A

Member B

Node 2 u2013

Member C

Member D

Nodes 1 and 2 belong to TOTAL NODE. TOTAL NODE is set in the current view. Since we have set the memberset as SELF,DEP the screen initially looks as below u2013

Node 1

Node 2

When we double click on Node 1, the system expands the same as u2013

Node 1 u2013

Member A

Member B

Node 2

The above happens as we have set the Drilldown Option (in workbook options) as u201CExpand by inserting new rowsu201D. The current view does not change when we double click on Node 1. Nor does the sheet expand. Hence none of these functions are called when we double click node 1.

We wish to know what event is triggered in the above scenario.

Regards,

Shibu

P.S. We checked the drilldown option of u201CExpand by overwriting rowsu201D. Here the current view is changed when we double click on node 1. Here functions AFTER_EXPAND and AFTER_CHANGECVW are called. But we are not comfortable with this option and are looking for solution in the above scenario.

[former_member186498]

Hi Shibu,

i used in my test the "expand by overwriting rows" and with this options it use the after_expand.

You're write with the other option with the double_click it execute the query without expand or refresh.

I don't know if you can intercept some event with this option, but for now you can use the first option to drill down and insert values on the "leaf" and with the right-click click on "go back" to navigate through the other nodes, or write ALL in the expansion instead of SELF,DEP and you will see immediately all the hierarchy.

If i discover some events for the u201CExpand by inserting new rowsu201D option I will write it.

Kind regards

Roberto

Edited by: Roberto Vidotti on Jan 4, 2012 12:48 PM

[Former Member]

Thank you very much!!

[Former Member]

Hello Shibu,

I have same issue. I want to run a macro when I double-click on a node in my report. Did you resolve this issue? Thanks..