Using Workbook to meet requirements of special calculations in Report.

[Former Member]

Hi All,

I have some questions on using Workbooks to format queries.

I have a BeX Query that has certain special layout requirements and special calculations for Result rows for which I need to use excel macros.

I have used a Workbook to achieve this.

Will the workbook reflect changes everytime I make some changes to the query structure?

Will the data be refreshed each time the workbook is opened?

How can the workbook be hosted on the Web in a 3.x environment ?

What are the possible ways client can access and launch the workbook?

Is there any better approach to do this other than using Workbooks?

Please let me know your thoughts on this.

Regards,

Amrita

[Former Member]

Thanks Bob,

I will go ahead with this implementaion.

Regards,

Amrita

[Former Member]

Amrita,

Based on our experience with running workbooks from the SAP Enterprise Portal, the answers to your questions follow:

 Will the workbook reflect changes everytime I make some changes to the query structure?

The workbook will not automatically be updated to reflect changes to the query. After doing such changes, you have to open the workbook, go to the Query Designer from there, then click on the Execute button (green checkmark) to load the new changes into the workbook. Of course, after doing this, you will have to resave the workbook.

>

Will the data be refreshed each time the workbook is opened?

You can determine whether or not the workbook is refreshed each time by setting the Properties before saving the workbook.

>

How can the workbook be hosted on the Web in a 3.x environment ?

You can call a workbook from the Portal by setting Transaction Code = "RRMXP", Application Parameters = "wbid=<workbook id>", and Process First Screen = "Yes" (to suppress pop-up screen with workbook ID before running workbook). When the user clicks on the link in the Portal, a new Excel session will be opened (outside the Portal!) and the workbook will run.

>

What are the possible ways client can access and launch the workbook?

The above process is the best we could come up with.

>

Is there any better approach to do this other than using Workbooks?

I don't know of any, but would be interested if anyone else has any suggestions.

Hope this helps...

Bob

[Former Member]

Thanks for the detailed answer Bob.

I will be writing a lot of VBA code in the Macros for this workbook. The code is to perform certain calculations using the data generated by the Report.

I am wondering how far that will impact the query performance.

Please provide your inputs if you have worked on anything similar.

Regards,

Amrita

[Former Member]

The VBA macros will run AFTER the query finishes running, so they should not "impact the query performance" as such. Whatever time it takes to run the macros will just be added to the overall run-time.

Hope this helps...

Bob