Using VBA Macros with Merged Variables Query Prompt

[former_member245941]

Hello,

I am trying to change a query prompt entry in an Analysis Workbook through VBA Macros but there seems to be an issue with the "merge Variables" option in conjunction with the SAPSetVariable function.

If you encountered similar problems, please let me know how you aproached the issue or what obvious error you see in the function calls.

In our workbooks without merged variables the usage of the SAPSetVariable function code as in the examle below works flawlessly and gives a returncode 1 for the succesfull execution.

[...]

l_result = Application.Run("SAPSetVariable", "Z_ZSDCH9876_MSEO_XYZ", "1234", "key", "DS_1")

[...]

In a workbook with merged variables and severel Data Sources the call of SAPSetVariable seems to fail.

Lets pretend the workbook contains the Data Sources"DS_1", "DS_2" and "DS_3". Each of them using the BW Query Variable with the technical name of "Z_SDCH9876_MSEO_XYZ" and the descriptive name of "customer".

None of the below example lines of code changes the query prompt. Each of them returns a l_result of 0, which indicates a failure of the SAPSetVariable Function, instead of the returncode of 1 for succesfull execution.

[...]

l_result = Application.Run("SAPSetVariable", "Z_ZSDCH9876_MSEO_XYZ", "1234", "key", "DS_1")

l_result = Application.Run("SAPSetVariable", "Z_ZSDCH9876_MSEO_XYZ", "1234", "key")

l_result = Application.Run("SAPSetVariable", "customer", "1234", "key", "DS_1")

l_result = Application.Run("SAPSetVariable", "customer", "1234", "key")

[...]

Calling Application.Run("SAPGetProperty", "LastError", "Text") yields an empty string, regardless of the variant used to call SAPSetVariable

In contrast you can without problem uncheck the "Merge Variables" Option in the workbook and call the SAPSetVariable function for the individual Datasources.

[...]

l_result = Application.Run("SAPSetVariable", "Z_ZSDCH9876_MSEO_XYZ", "1234", "key", "DS_1")

l_result = Application.Run("SAPSetVariable", "Z_ZSDCH9876_MSEO_XYZ", "1234", "key", "DS_2")

l_result = Application.Run("SAPSetVariable", "Z_ZSDCH9876_MSEO_XYZ", "1234", "key", "DS_3")

[...]

This creates a bit of a pedicament. For the users the merged variables are a clear advantage, but in this case they barr us from including additional VBA based functionalities the users know and appreciate from other workbooks.

Kind regards,

Werner

[former_member245941 · Accepted solution]

Hello,

revisiting the issue i discovered a solution for the problem myself. Switching from "Text" or "Key" to "INPUT_STRING" in the call of SAPSetVariable did the trick (in combination with omitting the parameter for the datasource / formula alias).

e. g.

[...]

l_result = Application.Run("SAPSetVariable", "Z_ZSDCH9876_MSEO_XYZ", "1234", "INPUT_STRING")

[...]

Please do not ask me why this works and the "Text" or "Key" Options do not. It could even be specific to our case and the queries used.

As i am quite happy about finding a solution, i post this in reply to my own question to share the knowledge. Perhaps it will help, should you run into this problem too.

Regards,

Werner

[Former Member]

Hi,

Have you see the following note in the documentation? Not sure if it is related.

• Formula Alias  Enter the formula alias for the data source. You can set the alias when configuring the data source on the Components tab in the design panel. 

  You have to define this parameter if the variables in the workbook are not merged. If the variables in the workbook are merged, you can define this parameter but you do not have to define it.

[former_member245941]

Hello,

Thank you for your reply. I know of this note, in the german version of the documentation. It has the corresponding section. The section is the reason why we tried the function call with and without the "Formula Alias" Parameter. If the variables in the workbook are merged, we get a return code = 0 for all of the four calling examples.

As you can see in the example code, we even tried to call the function with the Query Variables description as well as with the technical Name. Although, we never tried to call with the description in the dimension parameter in other cases. Using the technical Name works just fine in our other workbooks.

I would guess, that calling with the description in the dimension parameter does not work at all. Regardless of merging variables or not, but we never tested that throughoutly.

Kind regards,