On the BEx Toolbar, under the Settings icon are two commands you need to use:
1. New Workbook on embed ... is based on permanent template;
2. Permanent workbook template.
So, you save your "receiver" query in a workbook (if there are multiple queries in the receiver workbook, your RRI receiver must be the LAST query embedded). Then, you define that receiver query as your permanent template.
This sounds bad, doesn't it? But, it is not as bad as it sounds. All you need to do is to set this workbook dynamically, make the jump, then reset the New workbook on embed setting. Here is code I used:
Sub jump2ODS()
Dim myCell As Range, resultRng As Range
Dim ws As Worksheet, myRange As Range
Set myCell = ActiveCell
Set ws = ActiveSheet
If Not BExIsLoaded Then Exit Sub
'ensure that user selected a cell within the query results table!
On Error Resume Next
Set resultRng = Range(ws.CodeName & "_results")
If resultRng Is Nothing Then
MsgBox "Unable to locate query results on " & ws.Name & ".", vbCritical, _
"Unable to jump to ODS query."
Exit Sub
End If
Set myRange = Application.Intersect(myCell, resultRng)
If myRange Is Nothing Then
MsgBox "Please select a cell in the query results table.", vbCritical, _
"Unable to jump to ODS query."
Exit Sub
End If
'set New Workbook on embed ... is based on Permanent Template
Run "sapbex.xla!templatePermanent"
'define the jump workbook as the Permanent Template
I am trying to solve the same issues making a workbook jump to another workbook.
I am confused by your following comments: "All you need to do is to set this workbook dynamically, make the jump, then reset the New workbook on embed setting...."
Hi Dan, sorry I did not see your question sooner. Was out all of last week.
In that passage, I was referring to setting the "Permanent template on embed ...". I meant to say that "Permanent" can be very temporary (i.e., "dynamic"). From the wording of your question, I suspect that THIS is not the problem you are having. I imagine that you are getting stuck somewhere earlier than this. So, try to explain in more detail.
I assume that you have your two queries already created. And, that both work separately. I further assume that you have successfully "joined" the two queries using RRI. So, you are able to drill-through from query 1 to query 2. But, that each time that you do the drill-through, query 2 embeds in either a blank workbook or in a standard template; but, not in the workbook you want it to. If I am wrong about any of these assumptions, let me know.
Before doing anything in VBA, let's get this to work manually.
Take the workbook that has query 2 embedded and formatted the way you want it and save it to the BW server (I will call it "workbook 2"). For now, you can save it to your favorites if you like.
THIS IS IMPORTANT: if there is more than one query in workbook 2, then the jump-to query (query 2) MUST be the LAST query embedded (if you are familiar with the hidden query sheet SAPBEXqueries, this means that the local query ID will be listed LAST in column F).
On the Business Explorer toolbar, go to the Settings icon. Here, you must do 2 things:
1. Set "New workbook on embed" = "is based on permanent template"
2. Use "Permanent workbook template ...", this will bring up what looks like an Open Workbook dialog. Navigate to workbook 2 and select it.
Now try your drill-through. When you jump from query 1, query 2 will be processed on the server. When query 2 is finished running it will embed in workbook 2, replacing the query 2 that was there when you saved the workbook.
You can now go back to the Business Explorer toolbar and change your "New workbook on embed", if you wish.
Success? If so, do you see the problem with extending this to a wide user base? The problem is that each user will have to do exactly the same thing that you just did. The code that I posted in August automates the changes in the "New workbook on embed" settings so that users do not have to know about these settings.
What the code does is:
+ changes the "New workbook on embed" = "permanent template"
+ uses the server workbook id to tell BW which workbook is the "permanent template"
+ executes the RRI jump
+ resets the "New workbook on embed" back to (in my case) "is selected from list"
This is what I meant by "set this workbook dynamically".
Hope this helps. If not sufficient, post again. I will try to do a better job of keeping up with this real-time.
You can try : In the SAP Easy Access Menu of the BW system, choose SAP Menu -> Business Explorer -> Query -> Jump Target. You reach the Sender/Receiver Assignment Maintenance screen.
I already conected between the quaries useing transaction RSBBS (=the same that you suggested) but i cant find any way to connect between two workbooks.
I can save a template workbook insted but it will influence the presentation of all the other quaries as well and i dont want that to happend.
I know this thread is quite old, but I havent found a solution for my particular problem in newer ones as well.
I want to connect between workbooks like described above.
The "SAPBEXreadWorkbook" works fine, but... when I want to jump to and fro or when I press the button twice, the workbook is opened again. But I want it not to be opened again, but just activated.
Can you please tell me where exactly i have to insert this code in my workbook in Bex Analyzer.Because i am not using command button..it will be more helpful.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.