on 12-29-2011 4:35 AM
Hello BExperts,
What format/structure does the SAPBEXsetVariables API function expect for its input range? I have been struggling for several days now to figure this out. The description of the API function is below.
Function SAPBEXsetVariables(varValues As Range) As Integer
Set variable Values for User to provide Pop-Up Filter selections (Filter dialogs will be disabled if properly filled)
The code I have written attempts to open a workbook, set the values of two variables that it normally would prompt the user for, and refreshes the workbook.
Sub TestMacro()
Dim varValues As Range
Set varValues = Worksheets("Test").Range("A6:F7")
Run "SAPBEXreadWorkbook", "W4FSAZJXZ341W13I569KJ28RX"
Run "SAPBEXsetVariables", varValues
Run "SAPBEXrefresh", True
End Sub
The workbook opens and refreshes just fine, but always prompts me for the variable values. The workbook only contains one query. Searching the Internet reveals that several others have had this problem but I cannot find a solution.
It appears that with BI 3.x one could simply overwrite variables values in the hidden "SABBEXqueries" sheet. But it seems that sheet no longer exists for BI 7. Function SAPBEXdebugPrint does not seem to be available for BI 7 either so I cannot see the C_T_VARIABLES structure that some recommend looking at.
The last post in the following thread appears to reveal the structure of the range that SAPBEXsetVariables expects, but did not work for me. BEx still asks me for the values of the two variables.
http://forums.sdn.sap.com/thread.jspa?threadID=276850&tstart=0
Any help would be appreciated!
Edited by: alfredough on Dec 29, 2011 5:48 AM
Hi Alfredough, I'm afraid I'm in pretty much the same situation as yourself (although possibly slightly further down the line). The reason I add that possibility is because I'm pretty certain that SAPBEXsetVariables has been removed in 7.x.
It looks like there are some cludges which can be done to work through this (possibly), but the entire comparison of bexanalyzer.xla to sapbex.xla seems to involve features being removed, rather than added.
Here's the thread which seems to give a cludge, which I'm having trouble getting working: http://forums.sdn.sap.com/thread.jspa?threadID=1306490
There's also some work which someone's done here around script objects: http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/25185
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks for your response. I ended up figuring out a work-around, just been meaning to come back here to post it.
I created a new sheet in my workbook named Button. In this sheet I created a range that was four rows by three columns in size. An example is below. I only needed to set two variables.
VAR_NAME 0 TECHNICAL_NAME
VAR_VALUE_EXT 0 CHARACTERISTIC_VARIABLE_VALUE
VAR_NAME_1 0 TECHNICAL_NAME2
VAR_VALUE_EXT_1 0 CHARACTERISTIC_VARIABLE_VALUE2
The text in the first column is used by BEx, so leave that as is. I'm assuming for additional variables and values you continue to increment the extensions, for example _2, _3, etc. I'm not sure what the second column is used by BEx for, but 0 worked for me so I stuck with it. The third column contains the variables' technical names and the characteristic values I want to set them to.
Next I created a button. For its command type I selected: workbook-specific command and selected the data provider the variables pertained to. For workbook-specific command I selected Process Variables.
I set the button's command range to my Excel range above. In the static parameters for the button:
SUBCMD 0 VAR_SUBMIT
CMD 0 PROCESS_VARIABLES
DATA_PROVIDER 0 DP_#
Where DP_# is the data provider I selected when specifying the button's command type. For some reason it is incredibly difficult to set these static parameters. They will keep getting overwritten. You have to keep going back and setting them again. Stick with it and eventually it'll let you get all of this in there.
I then wrote a macro in the workbook.
Sub ClickMacro()
Sheets("Button").BUTTON_#_Click
End Sub
BUTTON_# is the name of the button. You can view the button's name by clicking on it in design mode.
I then wrote VBA code in another workbook, which copies values into the range I gave an example for above.
Dim workbookName As String
Run "SAPBEXreadWorkbook", "WORKBOOKID"
workbookName = ActiveWorkbook.Name
Run "'" & workbookName & "'!ClickMacro"
Run "SAPBEXrefresh", True
Workbooks(workbookName).SaveCopyAs fileName:=(fileName)
WORKBOOKID is the workbook ID of the workbook that contains the sheet and ClickMacro that I defined above.
fileName is the path and file where I save the workbook
Hope this helps someone. It took me a long time to figure out how to do this.
Hi Alfred,
Thank you for providing the details.
I could create a button following the below procedure.
1. Open Bex Analyzer
2. Navigate to Bex Analyzer (menu) -> Design Toolbar -> Insert Button. This will switch on the Design Mode.
3. Click on the button, select "Workbook-Specific Command" and click on next (Data provider need not be worried about at this point as it can be changed later also).
4. Select "Process Variables" and click on Finish.
5. One more window with an heading "Properties of Button" will be shown with fields like "Name of Button", "Range", "Button Text", "Command Range" and few other.
6. On the right side of this window, list of Static Parameters can be created.
7. As per your example, i created below parameters
CMD 0 PROCESS_VARIABLES
DATA_PROVIDER 0 DP_1
and created a range having below details and provided this range in "Command Range" field (in button properties)
VAR_NAME 0 #NAME1#
VAR_VALUE_EXT 0 #VALUE#
VAR_NAME_1 0 #NAME2#
VAR_VALUE_EXT_1 0 #VALUE2#
8. This DID NOT WORK for me. Actually nothing was happening. After some investigation, i figured out that i need to have one command like below
CMD | 1 | SHOW_VARIABLE_SCREEN |
Without this neither the variable screen was shown nor it read the parameter values from the excel range provided. Once this was done, the input screen was shown and with the values that i provided in my excel range.
Here are my questions. Pls provide your valuable inputs.
1. Why am i forced to have a command for "SHOW_VARIABLE_SCREEN"? Is this mandatory? problem with this is that i will always be shown a input screen which i don't want (in fact the very reason for this exercise is not to show the parameter screen but to supply values to it from the back end). Is there some setting that i need to do to get rid of this?
2. The setup can read values for only 2 parameters. i mean when i have below entries, the program was not reading the 3rd parameter values (of course i updated the "command range").
VAR_NAME_2 0 #NAME3#
VAR_VALUE_EXT_2 0 #VALUE3#
Thank you. Looking for a sooner response.
Lohith
Hi Alfred,
I figured out the mistake that i have done. Looks like the order of the commands is very important.
In my case, i had something like below
CMD 0 PROCESS_VARIABLES
DATA_PROVIDER 0 DP_1
SUBCMD 0 VAR_SUBMIT
I should not have the PROCESS_VARIABLES as my first command. Instead should have DP_1 or the SUBCMD as first argument. So below order worked for me.
DATA_PROVIDER 0 DP_1
SUBCMD 0 VAR_SUBMIT
CMD | 0 | PROCESS_VARIABLES |
But my second question is still unanswered. Can you through some light on it?
Lohith
Hi Lohith and all interested in,
I definitely resolved the problem with preparing the range for SAPBEXSetVariables function.
My BW spreadsheeds are now perfectly automated. If someone wants to know the know-how, just pls e-mail me on tomasz.szymacha.66@gmail.com
Cheers
Tomek
Hi, i have 7 sheets with 7 queries and 7 dp (data provider)..
Id like to refresh all queries with the new variables values setted calling the "process variables" button only one time, ¿it is posible?.
Actually i have to set the dp variable (example dp_1) and when i click the button it refresh all workbook but only uses the new values for the first sheet that contains dp_1.
The rest of sheets are refreshed with values of initial window prompt variables.
If i call 7 times the button for every dp it goes correct, but the time of execution is very long.
Id like simulate the execution when i fill the window prompt variables.
Could you help me please?.
User | Count |
---|---|
84 | |
10 | |
9 | |
8 | |
6 | |
6 | |
6 | |
5 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.