Passing variables to a BEx query dynamically

[former_member215195]

Hi All,

We have a requirement to pass values for variables dynamically to a Bex 7x query because we do not want to prompt any variable screen to the user. I tried few steps as suggested in but still have some questions unanswered. So request your thoughts on the same.

Below procedure has been followed.

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 an 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 when the button was pressed.

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 user will always be shown a input screen which we 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 there is an additional variable, the program is not reading the 3rd parameter values (of course i updated the "command range").

VAR_NAME                     0         #NAME1#              

VAR_VALUE_EXT            0          #VALUE#

VAR_NAME_1                 0          #NAME2#

VAR_VALUE_EXT_1         0          #VALUE2#

VAR_NAME_2                 0          #NAME3#

VAR_VALUE_EXT_2         0          #VALUE3#

Thank you. Looking for a sooner response.

Lohith

[former_member215195 · Accepted solution]

Hi All,

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

So, i don't need to have "SHOW_VARIABLE_SCREEN" command as i don't want to show the screen.

But my second question is still unanswered. Can you through some light on it?

2. The setup can read values for only  2 parameters. i mean when there is an additional variable, the program is not reading the 3rd parameter values (of course i updated the "command range").

VAR_NAME                     0         #NAME1#              

VAR_VALUE_EXT            0          #VALUE#

VAR_NAME_1                 0          #NAME2#

VAR_VALUE_EXT_1         0          #VALUE2#

VAR_NAME_2                 0          #NAME3#

VAR_VALUE_EXT_2         0          #VALUE3#

Lohith

[former_member215195 · Accepted solution]

Hi All,

Got the solution for the other problem too:-)

If the structure of BExRepositorysheet is observed,

(refer to the blog post 'List BEx Analyzer 7.X variables and generate the associated PROCESS_VARIABLES commands' in SCN for how to read the content of BExRepository sheet to an excel/XML) there are many other fields associated with each variable/parameter (like VNAM, OPT. LOW_EXT etc). Each of these variables will have to be interpreted as VAR_NAME_1, VAR_OPERATOR_1, VAR_VALUE_LOW_EXT_1 while reading it by a command.

     In my case i was trying to read a non-mandatory text variable using VAR_NAME_3, VAR_VALUE_LOW_EXT_3 but the values were not getting read. Reason is that i should use another field "VAR_OPERATOR_3" and the values will be read then.

Hope this helps.

Lohith

[former_member215195 · Accepted solution]

Hi All,

I am in a situation wherein i need to refresh only one query in the workbook using this command variables. "PROCESS_VARIABLES" seems to be refreshing all the queries every time. I do have a command for DATA_PROVIDER which will point to the query needed to be executed individually but all the queries get executed still. Is there way i can indicate somewhere to execute only the query from needed DATA_PROVIDER?

Note: I have checked the "Allow Refresh on Individual Queries ..."check box under "Bex analyzer -> Global Settings".

Pls provide your valuable input.

Lohith

[former_member215195 · Accepted solution]

Hi All,

Looks like there is no solution for this problem (BEx is refreshing all the queries embedded in the workbook even though a single query is tried to be refreshed using command button) than running the queries in separate workbooks and merging the same. More details are available at the end of the conversation of the link:

Lohith

[Former Member · Accepted solution]

I found this morning the link you're mentioning, and now I was able to create a button without any "static parameters" in there, and the command range has all the steps and data. And I can call the macro "Button_xx_Click() from a separate workbook.

Many thanks to all the people that share their ideas, I was totally lost with this.

Adrián

[former_member215195 · Accepted solution]

Hi All,

Am glad that, i could refresh a query individually without refreshing all the queries embedded in the work book using command variables. Here are the steps followed.

Created a command button (BEx Analyzer ->Design Toolbar -> Insert Button ->workbook-specific command-> process variables) with below commands:

DATA_PROVIDER 0 DP_1

CMD                    0 PROCESS_VARIABLES

SUBCMD             0 VAR_SUBMIT

and "command Range" with A1:C100

Test setup:

Embedded a query in 2 separate sheets (using "Analysis grid").

Created the command variable as explained above in Sheet2.

So sheet1's query can only be refreshed with "Refresh" or "Change Variable Values" option. But sheet2's query can be refresh with both above options along with clicking the command button.

When the command button is clicked, i made sure that i provide a different value to the variable. Once the query is refreshed, i observed that only the sheet2's query got changed but not the sheet1's. This confirmed this functionality.

Conclusion:

Command button does refresh a particular query (indicated by data provider) and doesn't refresh all the queries embedded in the workbook.

Lohith

Lohith

[former_member812821 · Accepted solution]

Hello everybody and please, excuse my bad english.

I get fill variables correctly, but i want that when i click the button the new values be used for all queries of my workbooks but although i can see that it refresh all queries only use the new values on data_provider given.

If i dont set the data_provider it simply use old values setted and ignores the new.

So, is there any way to execute all queries of my books with the new values only pressing the "process variables button"?

Kind regars and thanks.