List BEx Analyzer 7.X variables and generate the associated PROCESS_VARIABLES commands

[franois_gyngysi]

Under BW 3.5, we had developed some Visual Basic macro to set the values of BEx variables for queries embedded in Excel workbooks. This was achieved by writing directly in the BEx repository (sheet SAPBEXqueries). However, this doesn't work anymore with BW 7.X. We haven't managed to use the new BEx Visual Basic API to achieve the same result but found an interesting solution in Lars Hermanns' answer at How to set variables values via VBA..

In order to automate the process, we wrote some Visual Basic code to extract the list of variables / values from the BEx repository (XML) and to generate the PROCESS_VARIABLES commands.

1) List the BEx variables / values by InfoProvider

The procedure is the following:

• Launch the BEx Analyzer and open a BEx query with variable(s)


• Save the workbook on your local drive (this is required to save the variables' values in the BEx XML repository)


• Go to the Visual Basic editor (menu Tools > Macro > Visual Basic Editor or press Alt+F11)


• Insert a new module (menu Insert > Module)


• Copy / Paste the code below in the new module. This code has been tested in Excel 2003 and Excel 2007.

'Modifications



'01-AUG-2013

'- added support for hierarchy node variables (but not hierarchy variable)

'- added single quote in front of variable values when generating commands to keep formatting of external value



'20-OCT-2014

'- added support for Multiple Single Values variable



'21-OCT-2014

'- added support for Formula variable



Option Explicit



Dim w As Worksheet

Dim lRow As Long



'

'Create a new worksheet with the list of BEx variables by dataprovider

'Doesn't handle hierarchy variable

'

Sub generateCommand()

    Dim objXML As MSXML2.DOMDocument

    Dim strXML As String

    Dim oNodeList As IXMLDOMSelection

    Dim curNode As IXMLDOMNode

    Dim oList As IXMLDOMSelection

    Dim varNode As IXMLDOMNode

    Dim n As Long, n2 As Long, n3 As Long





    'Get the BExAnalyzer repository (XML)

    Set objXML = New MSXML2.DOMDocument



    'In Excel 2007, Scripts property doesn't exist anymore. We use CustomXMLParts instead

    If Val(Application.Version) >= 12 Then

        strXML = ActiveWorkbook.CustomXMLParts(4).XML

    Else

        strXML = Worksheets("BExRepositorySheet").Scripts(1)

    End If



    If Not objXML.loadXML(strXML) Then

        Err.Raise objXML.parseError.errorCode, , objXML.parseError.Reason

        Exit Sub

    End If



    'create a new worksheet to store result

    Call initLog



    'List of dataproviders

    Set oNodeList = objXML.selectNodes("//T_DATAPROVIDER/RSR_SX_DATAPROVIDER")



    'Process each dataprovider

    For n = 0 To oNodeList.Length - 1

        Set curNode = oNodeList.Item(n)



        'Name of dataprovider

        Call writeLog("DATA_PROVIDER", n, curNode.selectNodes("NAME").Item(0).nodeTypedValue)



        'BEx commands for variables

        Call writeLog("CMD", n, "PROCESS_VARIABLES")

        Call writeLog("SUBCMD", n, "VAR_SUBMIT")



        'Variables

        Set oList = curNode.selectNodes("REQUEST/VAR/RRX_VAR")



        'Process each variable entry

        For n2 = 0 To oList.Length - 1

            Set varNode = oList.Item(n2)



            Select Case varNode.selectNodes("OPT").Item(0).nodeTypedValue

            Case ""

            Case "EQ", "GE", "GT", "LE", "LT", "CP"



                'if hierarchy node variable (can only be single value or list of single values. Cannot exclude value)

                If varNode.selectNodes("VARTYP").Item(0).nodeTypedValue = "2" Then

                    Call writeLog("VAR_NAME_" & n2 + 1, n, varNode.selectNodes("VNAM").Item(0).nodeTypedValue)

                    Call writeLog("VAR_VALUE_EXT_" & n2 + 1, n, "'" & varNode.selectNodes("LOW_EXT").Item(0).nodeTypedValue)

                    Call writeLog("VAR_NODE_IOBJNM_" & n2 + 1, n, "0HIER_NODE")

                Else

                    Call writeLog("VAR_NAME_" & n2 + 1, n, varNode.selectNodes("VNAM").Item(0).nodeTypedValue)

                    Call writeLog("VAR_OPERATOR_" & n2 + 1, n, varNode.selectNodes("OPT").Item(0).nodeTypedValue)

                    Call writeLog("VAR_SIGN_" & n2 + 1, n, varNode.selectNodes("SIGN").Item(0).nodeTypedValue)



                    'if individual variable (P = Single Value ; M = Multiple Single Value ; F = Formula)

                    Select Case varNode.selectNodes("VPARSEL").Item(0).nodeTypedValue

                    Case "P", "M", "F"

                        Call writeLog("VAR_VALUE_EXT_" & n2 + 1, n, "'" & varNode.selectNodes("LOW_EXT").Item(0).nodeTypedValue)



                    Case Else

                        Call writeLog("VAR_VALUE_LOW_EXT_" & n2 + 1, n, "'" & varNode.selectNodes("LOW_EXT").Item(0).nodeTypedValue)



                    End Select

                End If



            Case "BT"

                Call writeLog("VAR_NAME_" & n2 + 1, n, varNode.selectNodes("VNAM").Item(0).nodeTypedValue)

                Call writeLog("VAR_OPERATOR_" & n2 + 1, n, varNode.selectNodes("OPT").Item(0).nodeTypedValue)

                Call writeLog("VAR_SIGN_" & n2 + 1, n, varNode.selectNodes("SIGN").Item(0).nodeTypedValue)

                Call writeLog("VAR_VALUE_LOW_EXT_" & n2 + 1, n, "'" & varNode.selectNodes("LOW_EXT").Item(0).nodeTypedValue)

                Call writeLog("VAR_VALUE_HIGH_EXT_" & n2 + 1, n, "'" & varNode.selectNodes("HIGH_EXT").Item(0).nodeTypedValue)



            Case Else

                Call writeLog("Unknown operator: ", varNode.selectNodes("OPT").Item(0).nodeTypedValue, "")



            End Select

        Next

    Next



    'Housekeeping

    Set oList = Nothing

    Set varNode = Nothing

    Set oNodeList = Nothing

    Set curNode = Nothing

    Set objXML = Nothing



    Call closeLog



End Sub



'

'List the variables by dataprovider in the immediate windows

'

Sub parseXML()

    Dim objXML As MSXML2.DOMDocument

    Dim strXML As String

    Dim oNodeList As IXMLDOMSelection

    Dim curNode As IXMLDOMNode

    Dim oList As IXMLDOMSelection

    Dim varNode As IXMLDOMNode

    Dim n As Long, n2 As Long, n3 As Long

    Dim s As String



	

    'Get XML BEx repository

    Set objXML = New MSXML2.DOMDocument



    'In Excel 2007, Scripts property doesn't exist anymore. We use CustomXMLParts instead

    If Val(Application.Version) >= 12 Then

        strXML = ActiveWorkbook.CustomXMLParts(4).XML

    Else

        strXML = Worksheets("BExRepositorySheet").Scripts(1)

    End If



    If Not objXML.loadXML(strXML) Then

        Err.Raise objXML.parseError.errorCode, , objXML.parseError.Reason

    End If



    'List of InfoProviders

    Set oNodeList = objXML.selectNodes("//T_DATAPROVIDER/RSR_SX_DATAPROVIDER")



    'Process each InfoProvider

    For n = 0 To oNodeList.Length - 1

        Set curNode = oNodeList.Item(n)



        'Name of InfoProvider

        Debug.Print "DATA PROVIDER " & n & ":" & curNode.selectNodes("NAME").Item(0).nodeTypedValue



        'Variables

        Set oList = curNode.selectNodes("REQUEST/VAR/RRX_VAR")



        Debug.Print "VARIABLES:"



        For n2 = 0 To oList.Length - 1

            Set varNode = oList.Item(n2)



            s = ""

            For n3 = 0 To varNode.childNodes.Length - 1

                s = s & varNode.childNodes.Item(n3).baseName & "=" & varNode.childNodes.Item(n3).nodeTypedValue & ";"

            Next



            Debug.Print s

        Next



        Debug.Print "*************************************************"

    Next



    'Housekeeping

    Set oList = Nothing

    Set varNode = Nothing

    Set oNodeList = Nothing

    Set curNode = Nothing

    Set objXML = Nothing



End Sub





Private Sub initLog()

    Set w = ActiveWorkbook.Sheets.Add

    lRow = 1

End Sub





Private Sub writeLog(s1 As Variant, s2 As Variant, s3 As Variant)

    w.Cells(lRow, 1) = s1

    w.Cells(lRow, 2) = s2

    w.Cells(lRow, 3) = s3

    lRow = lRow + 1

End Sub





Private Sub closeLog()

    Set w = Nothing

End Sub

 

Please note that the code is provided as-is and the author assumes no responsibility for issues which arise resulting from its use. Feel free to tweak the code if needed.

• Declare a reference to the Microsoft XML library (menu Tools > References... and choose Microsoft XML, vX e.g. Microsoft XML, v6.0)


• If not displayed, open the Immediate Windows (menu View > Immediate Window)


• Run the macro parseXML (menu Run > Run Sub/UserForm)


• The list of variables / values by InfoProvider is displayed in the Immediate Window

DATA PROVIDER 0:DATA_PROVIDER_1 VARIABLES: VNAM=0I_CALMO;VARTYP=1;VPARSEL=I;SIGN=I;OPT=BT;LOW=201105;LOW_EXT=05.2011;HIGH=201106;HIGH_EXT=06.2011;LOWTXT=;LOWTXT_LEN=0;HIGHTXT=;HIGHTXT_LEN=0;HRYTXT=;HRYTXT_LEN=0;VTXT=Calendar Year/Month;VTXT_LEN=20;HELP_AVAIL=;MSG_NR=;IOBJNM=0CALMONTH;LOW_CMP=201105; *************************************************

2) Generate BEx commands

The procedure is the following:

• In the Visual Basic Editor, run the macro generateCommand (menu Run > Run Sub/UserForm)


• The list of BEx commands is generated in a new worksheet

• Go to BEx Design mode and add a button


• Add a reference to the generated commands

NOTES:

1. You may have to adjust manually some variables' values in the generated worksheet (e.g. add single quotes in front of dates, adjust dot or comma wrongly interpreted as decimal separator by Excel)


2. The macro doesn't handle all types of variables e.g. doesn't handle hierarchy variables

If you want to be able to access the macro without having to copy / paste the code each time, you can create an Excel Add-In. To do so:

• Create a new Excel workbook


• Copy / paste the above VBA code in a VBA module


• Add a reference to Microsoft XML


• Save the workbook as an Excel Add-In (menu File > Save As... and choose XLA file type). Do not save the Add-In in the default AddIns directory because BEx Analyzer seems to ignore these Add-In (use another directory instead e.g. My Documents)


• Close the file


• Install the Add-In (Tools > Add-Ins... and choose Browse... to select the XLA file).

Note that by default Excel Add-Ins are NOT loaded when you launch BEx Analyzer. See SAP note 1329403 - BExAnalyzer.exe does not load addins compared to Excel.exe to fix the issue.

Afterword

Some additional information based on the comments made below:

1) If the workbook is compressed, you’ll have an error message when the macro tries to parse the XML code. To solve the issue, click on the Workbook Settings icon in the BEx Design Toolbox toolbar (also available by choosing Design Toolbar > Workbook Settings in the BEx Analyzer menu) and make sure Use Compression When Saving Workbook is unchecked.



2) The Visual Basic macro assumes that your Excel file is in XLSX format if you’re using Excel 2007 or above. If the file is saved in XLS format under Excel 2007 or above, the macro won’t work.