Skip to Content
0
Oct 25, 2013 at 11:18 AM

Using VBA Macros with Merged Variables Query Prompt

428 Views

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