Migrating Advanced BEx Analyzer Workbooks – What VBA is Supported?
Introduction
As part of our migration strategy, we need to decide what to do with our workbooks. As BEx Analyzer has been around the longest of most of our BI Tools, this is generally the most widely deployed in our current customer base. Therefore, over the years, users may have become very savvy with these workbooks and may have ventured into using Visual Basic within these applications. This blog shows what BW 3.x functions in VBA are part of the public API that was published. It also discusses what the corresponding function is in SAP NetWeaver 2004s BI.
How do we compare?
Within SAP NetWeaver 2004s BEx Analyzer, a design toolbar has been added that allows us to use many elements and features we did not have previously. Therefore, in general, the use of Visual Basic is generally going to less in the new environment. However, we are talking about what migrates, so let us take a look. Figure 1 shows what functions existed in BW 3.x and their corresponding functions in SAP NetWeaver 2004s BI.
NOTE: Not all functions that existed in BW 3.x exist in SAP NetWeaver 2004s BI. In general, this is because these functions generally would not be needed due to being replaced by new objects available in the new design toolbar. However, this may cause some problems for migration.
Figure 1 – Comparison of Functions
|
BW 3.x
|
SAP NetWeaver 2004s BI
|
Comments
|
|
SAPBEXgetWorkbookID
|
SAPBEXgetWorkbookID
|
|
|
SAPBEXreadWorkbook
|
SAPBEXreadWorkbook
|
|
|
SAPBEXsaveWorkbook
|
SAPBEXsaveWorkbook
|
|
|
SAPBEXgetErrorText
|
SAPBEXgetErrorText
|
|
|
SAPBEXsetFilterValue
|
SAPBEXsetFilterValue
|
|
|
SAPBEXgetFilterValue
|
SAPBEXgetFilterValue
|
|
|
SAPBExcopyFilterValue
|
||
|
SAPBEXsetDrillState
|
SAPBEXsetDrillState
|
|
|
SAPBEXgetDrillState
|
SAPBEXgetDrillState
|
|
|
SAPBEXrefresh
|
SAPBEXrefresh
|
In the 2004s Analyzer, ALL the items in the workbook can be refreshed, but refreshing a query individually is not supported
|
|
SAPBEXpauseOn
|
BEx.Synchronize = true
|
|
|
SAPBEXpauseOff
|
BEx.Synchronize = false
|
|
|
SAPBEXfireCommand
|
||
|
SAPBEXcheckContext
|
||
|
SAPBEXgetFilterValue
|
||
|
SAPBEXshowTextElements
|
||
|
SAPBEXembedQuery
|
||
|
SAPBEXgetResultRangeByID
|
SAPBEXgetResultRangeByID
|
|
|
SAPBEXjumpInitial
|
||
|
SAPBEXjump
|
||
|
SAPBEXgetResultRange_ResultArea
|
||
|
SAPBEXgetResultRange
|
||
|
SAPBEXtoolbar
|
What to be careful of?
We see the functions that are the same between BW 3.x and SAP NetWeaver 2004s BI. In general, these things will migrate ok. The functions that have changed and that do not exist will generally require manual work for migration.
Are there other functions in SAP NetWeaver 2004s BI?
Of Course! There are lots of new VBA functions available in the public xBEXapi module. In addition, the interface to call connection handling, BEx Precalculation, and other common functions are also in a public interface. Keep in mind that you should NOT use VBA as a first step. This is only after you’ve explored the new native capabilities in the BEx Design Toolbar.
Where do I find details and examples?
Easy. The best place to go for documentation is the online help. For a list of the functions for the BW 3.x runtime, see the online help here: Integration with Visual Basic for Applications. For SAP NetWeaver 2004s BI, you don’t even need to go to the online help. The functions are documented directly within the modules. Open the Visual Basic Editor with the SAP NetWeaver 2004s BEx Analyzer and go to the xBExAPI module to see all the functions you can run! They are commented right there…
Summary
This is just one part of your migration picture. For a full view see: Rolling out the New SAP NetWeaver 2004s BI Frontend Tools
Is refreshing a query individually going to be supported in later Support Packs?
This becomes a problem for current workbook using multi query that needs to be refreshed individually.
Please advise!
Thankyou,
-Sini
Am I wrong?
Alex
thanks
https://websmp106.sap-ag.de/~sapidb/011000358700004483762006E
This is now planned for 2008.
Cheers,
Prakash
Thanks,
Laurie
Great series of blogs, Prakash.
I have two questions:
1) When I try to migrate a workbook (it has about 7 queries spread over a few tabs and a tab-mainpage with formula taking values from the 7 queries' cells), it errors out saying "A critical program error occurred. The program has to close...." and then it shuts down Analyzer. I get the same error when I play around the Analyzer long enough. Have you encountered this before?
2) On a separate case, I was trying out the new functionality, and used the drop-down for a master data attribute. The idea was the query will filter according to the value I select from the drop-down... however, if I pick value Name ="ABC" for instance, it will show result set of Name = "XYZ". Any idea why this is happening? (Also tested the radio-button and check-boxes, they work just fine.)
Extra info:
SAP GUI 710 (from compilation 4 CD) no other patches applied. Running Office 2003, made sure the Macro security is set accordingly.
Thanks!
Cheers,
Fionna
I'm currently migrating a workbook that has VBA enabled.
One of the functions that were used previously was SAPBEXpauseOn(). Since this function no longer exists, and it should be used BEx.Syncronize = True, when I replace one for another, the systems states the error "Object Required- 404"
Any Ideas? How can I use the older SAPBEXpauseOn or any substitute?
Thank You,
Diogo Ferreira
Does anyone have an idea why when we create a brand new macro "Macro1" with BEx functions called, for example, setdrilldown, and we ran the macro it executes normally, but when we put this code in the macro "SAPBExOnRefresh" it executes the code normally but doesn't release the application? Excel keeps the time icon, like it was doing something but does not free the application, we have to manually kill the excel?
we need to invoke a macro after refreshing a workbook, which we called within the "SAPBExonrefresh" in BEx 3.5.
We converted the queries in the workbook to formulas and wrote the macro name in the workbook settings "invoke macro on refresh", but it is not invoked.
Thanks in advance for your help,
Rainer Sigg
Try with the following parameter in your macro definition :
"Sub your_macro (ParamArray varname())…"
Best regards,
Hugues.
thanks a lot for your help - it works 🙂
Best regards,
Rainer
We have recently upgraded the workbook from 3.x to 7.0 version.
When I try to change the sub definition from Public Sub SAPBEXonRefresh(queryID As String, resultArea As Range) to Public Sub SAPBEXonRefresh(queryID() As String, resultArea () As Range) Then the VB Code is giving me an error Out of memory error.
Can you put forward your suggestions on this
Thanks in Advance
PV
At the moment I'm struggling with a problem and just can not find the solution anywhere at this moment, so I hope perhaps you have any idea how to solve this. I'm responsible for automated regression testing within a project, and I automated with (amongst others) VBA in Excel using the BEx Analyzer 3.X the complete expanding/drilling down of the rows of a query result. Within the BEx Analyzer 3.X, while looping all the data rows, on each row I executed the following VBA code, with the result that the specific row was expandend/drilled down:
retVal = Run("sapbex.xla!SAPBEXcheckContext", "HDEX", myCell)
If retVal = 0 Then
'OLAP context checked OK
retVal = Run("sapbex.xla!SAPBEXfireCommand", "HDEX", myCell)
Else .....
So I used the SAPBEXfire command for expanding the specific row. Now however, we want to use the new BEx Analyzer 7, so I have to migrate all my testactions. The only problem I have at the moment is that the SAPBEXfire command is not available in the new BEx Analyzer. And I just can not find or think of any workaround. Do you have perhaps any idea what command I could use for expanding/drilling down a row in Excel? Is there any alternative for the "Run("sapbex.xla!SAPBEXfireCommand", "HDEX", myCell)" command. Any help much appreciated.
i am on netweaver 2004s sp14 + excel 2003
there is definitely no comment or documentation in any of the api functions in bexanalyzer.xla!
..besides the hint in some functions: 'Not supported in 7.0'..
there is as well no documentation in the online help!
next thing is: where can you get the local query id (which you need for some functions) from? .. it is not anymore under "properties" ..there you can only get the technical name.
third an most annoying issue:
the drill functions and filter functions don't work anymore although they are not marked with 'not suppoerted in 7.0'
they always stop with a message of the debugger ..something like :
'runtime error 1004. cannot assign the intersect property of the application object' in the code line where the paddin method is called (e.g. SAPBEXgetDrillState = pAddin.SAPBEXgetDrillState(ActiveWorkbook.Name, currentState, atCell)
).
is there any solution?
thanks in advance
Runtime error 1004, complaining about intersection when calling SAPBEXgetFilterValue or other routines.
Office 2003, Service Pack 3.
Any suggestion?
Is there a possibility to access the Report Report Interface (RRI) from VBA code in SAP NetWeaver 2004s BI? Which function should I use instead of SAPBEXjump?
The question:
how can I programmaticaly setting the value of a variable. That's without poping up the variable dialog box (with SHOW_VARIABLE_SCREEN for example) on BI 7.0 SP14 (BEx Analyser front-end).
As I read on SDN this was possible with the API function SAPBEXsetVariables. Unfortunaly this function seems to be deprecaded.
Now my business scenario (not really needed to answer the question but any design advice is welcomed!):
I'm designing a IP application for our controling department. This involve 2 realtime infocube which both belong to my aggregation level.
Some of the planing function need both infocubes but the most just need one of them (the planing infocube). The idea is (with vba code) checking according to the function called whatever the both infocube are in the filter or not. Evently setting the variable containing the InfoCube correctly.
Thanks in advance for your answers,
Eric
Cheers!
/RB
i am really irritated with the new "Advanced BEx Analyzer Workbooks". There is no documentation for the new/changed functions, the essential questions
concerning the problems on the forum (and on this page) get no answer from SAP.
It seems that SAP knows, that this area in 2004s is not (yet?) properly implemented and tries to keep silent.
>>Are there other functions in SAP NetWeaver 2004s BI?
>>Of Course! There are lots of new VBA functions >>available in the public xBEXapi module.
can you name this new functions, i can only see in this module that many functions that are really needed are not supported in 7.0
>> For SAP NetWeaver 2004s BI, you don't even need >>to go to the online help. The functions are >>documented directly within the modules.
i have not found any online help for 2004s VBA integration. If i tell my customer, he don't need any documentation, he can just analyse the comments in the code, i will be fired on the next day. On the other side, i have found no proper documentation in this code ...
Regards,
Kirill
I found some sample coding to get started with the new BEx API for Excel here:
https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/8065f00c-59bc-2a10-31ab-9c538126d775
Some generic sample coding to access the results of a certain query through its data provider I adopted from that document:
Dim bex1 As Object
Dim bexitems As Object
Dim bexitem As Object
Set bex1 = Application.Run("BExAnalyzer.xla!GetBEx")
Set bexitems = bex1.Items
For Each bexitem In bexitems
' Further Coding ...
Debug.Print bexitem.Name
Next bexitem
If I stop with the VBA debugger, I can see in the debug window what is the real type of bexitem although it is declared as "Object": BExItemGrid
In debug view I cannot access any sub-objects or variables of bexitem (probably because the variable "bexitem" is declared as object instead of as "BExItemGrid"). But the coding in the document uses e.g. "bexitem.Name" or "bexitem.DataProvider".
So the question is: how did the author get the information what the structure of BExItemGrid-Class is? Just give me a hint on how to find that out. It will probably be enough just to add a reference to the library containing all that classes to my project, so I can declare the variable as BExItemGrid instead of generic "Object".
Thank you very much.
Regards,
Andreas
have you any new information about the functions and their usage?
Best regards,
Alexander
I´m still missing any kind of documentation for the VBA possibilities! SAP Help, SDN as well as the BEx-Addin coding does not really help.
Where can we all get the information about the object modell of the BEx? Which function accepts wihch parameters? Where are some examples?
Obviously some people DO know things about it since some code fragments are in the air...
I´d like to be one of it, since I really do need to program some things for our customers...
Regards
Denis
I've not worked on BEx topics in quite some time now. I'll let the relevant colleagues at SAP know that they should look at adding examples. Cheers,
Prakash
with analyzer 7.0 i can't display variables values in workbook text item.
I have some variables used for restricted key figure.
With analyzer 3.x i can see variables values in the variables area of text elements.
But with analyzer 7.0 i can't.
Why?
thanks in advance
Alberto
I also facing the same issue as you. May I know have you find out any solution to display the text elements as in Analyzer 3.x?
Thank you and really appreciate your help.
Best Regards,
Smiley
Too many limitations which are critical and requirement - which worked in 3X.
When we could expect a solution for RRI between workbooks? In BI 3.x we used the function SAPBEX.XLA!SAPBEXjump (VBA) but it was not implemented in BExAnalyzer.xla in BI 7.0. You explained in your blog some functions were not created because they maybe were replaced with objects in the new design toolbar. I undertand this is not the case for function SAPBEXjump. If there is any solution right now, please let me know.
Thanks in advance
Jairo
We wanted to have a query inserted in a workbook by simple 1-click command as it was done in 3.X toolbar.
We are aware that it is possible via design mode etc. However, this is very cumbersome and more over for end users this is too many steps to insert one query in workbook.
I attempted to automate this process by manual recording of a vba macro, however vba macro steps are not recorded for BEx 7,0 Toolbar functions or Workbook design mode functions.
I attempted to Insert button and configure the button to do this, however this is not possible either.
Is that way or method to have a new query inserted in a workbook in 1 click method?
Thanks
Uday