Skip to Content

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”

           

                ‘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

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

image

  • Go to BEx Design mode and add a button
  • Add a reference to the generated commands

image

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.

/wp-content/uploads/2011/06/wbkcompression_159168.jpg

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.

To report this post you need to login first.

100 Comments

You must be Logged on to comment or reply to a post.

  1. Ptown Bro

    Thanks for writing this. Very good information and it makes a lot of sense, but I’m not able to make it work for me. The following line in your generateCommand() sub procedure is not generating the right results for me:

    strXML = Worksheets(“BExRepositorySheet”).Scripts(1)

    The strXML is set equal to the script text of the script object.  This script text is returning  a bunch of text that can’t be read (garbage) that looks encrypted. For example its returning:

    ScriptText = H4sIAAAAAAAEAO29B2AcSZYlJi9tynt/SvVK1+B0oQiAYBMk
    2JBAEOzBiM3mkuwdaUcjKasqgcplVmVdZhZAzO2dvPfee++999577733ujud
    Tif33/8/XGZkAWz2zkrayZ4hgKrIHz9+fB8/Ih7/Hu8WZXqZ101RLT/7……..

    Since it’s not returning what I assume should be XML text, the following line breaks:

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

    Any idea why?

    (0) 
    1. François Gyöngyösi Post author

      Hello,

      I don’t know why it doesn’t work on your PC. I have tried the whole procedure on my PC and it works fine (I use Win XP and Excel 2003). By the way, I have modified the code to make it work under Excel 2007 (the Scripts property doesn’t exist anymore). Hope you’ll find a solution to your problem.

      Regards.

      François.

      (0) 
      1. Ptown Bro

        Hmm.. Maybe the issue is that I’m using Windows 7 and Excel 2007.  I’ll try the new code you inserted for Excel 2007 and try that. Taking a closer look at your code, it appears that all you did was change both the generateCommand() and parseXML() sub procedures to 1) check to see which version was being used and then 2) if it’s Excel 2007 or greater, use the CustomXMLParts object instead of the Scripts object.

        I assume you need add a library or object variable reference to the CustomXMLParts object somehow to access it’s methods and properties.

        (0) 
      2. Ptown Bro

        Okay. I’ve tried this both in Windows 7 / Excel 2007 and Windows XP / Excel 2003 and it doesn’t work for me.

        In Windows 7 / Excel 2007, the CustomXMLParts object returns XML text, but it is NOT the text related to the dataprovider or paramters. Also, there isn’t 4 elements in the CustomXMLParts object there are only 3. In other words, CustomXMLParts(4) in your above code is out of range. If you do a CustomXMLParts().count there are only 3 items return. Each of these items have XML text in them, but none return the expected text.

        In Windows XP / Excel 2003, this still returns the garbage / encrtyped like text like I mentioned above. Even when viewing the back-end of the sheet, in MS Script Editor you see the garbage / encrtyped text.

        Any suggestions?

        (0) 
        1. François Gyöngyösi Post author

          I had another look but cannot find anything. For information, I use BI AddOn 7.X (based on 7.20) Support Package 6 Revision 1812. You may try Excel forums to see if there is anything related to encrypted script (I have modified security settings including encryption but I had no issue viewing the script).

          (0) 
  2. Dan Hong

    HI Fancois,

    Within your ParseXML sub, you set the document node equal to “//T_DATAPROVIDER/RSR_SX_DATAPROVIDER” but this text is no where to be found within the XML extraction.  Can you help clarify the node of this document?  Thanks.

    ‘List of InfoProviders

        ‘List of ‘List of InfoProviders

        Set oNodeList = objXML.selectNodes(“//T_DATAPROVIDER/RSR_SX_DATAPROVIDER”)

    “)

    (0) 
      1. Dan Hong

        HI Francois, I’ve taking on a new approach to this automation I’m attempting.  Do you happen to know the VBA command that would refresh my queries (in Workbook) on open?  This would have to bye pass the variable menu prompt.  I hope you can help.  Thanks again..

        (0) 
        1. François Gyöngyösi Post author

          I think there is a workbook property to tell BEx Analyzer to refresh queries on opening but I haven’t used it. Otherwise, you can add the following code in your workbook (in a new module):

          Sub Auto_Open()

               Run “BexAnalyzer.XLA!SAPBEXrefresh”, True

          End Sub

          Hope this helps.
          François.

          (0) 
          1. Dan Hong

            HI Francois,  The refresh command works, but it refreshes with old variable.  I’ve an SAP Exit variable for current date.  Do you know of any command line that would refresh using the current date?  Thanks again…

            (0) 
            1. François Gyöngyösi Post author

              The only solution I found to refresh variables is to use the technique described in the above blog… Didn’t manage to set variable values through the BEx API.

              (0) 
                1. Dan Hong

                  Hi Francios,

                  Do you know how to set automatic connection to BW via Bex Analyzer 7.2?

                  The procedure below you provided works great but it require user intervention when the system attempts connect.  How can I bypass the SAP Logon prompt and just directly connect to BW?

                  Sub Auto_Open()

                       Run “BexAnalyzer.XLA!SAPBEXrefresh”, True

                  End Sub

                  Thanks,

                  -Dan

                  (0) 
                    1. Dan Hong

                      Hi Francois,  in the end it was our system configuration that will not allow silent login.  Thank you very much for your helps.

                      (0) 
      2. Lohith Hattera

        Hi Francois,

        Thank you very much for providing the details.

        I am sorry but am visiting a 2 years old post as i still am having issues with the path “//T_DATAPROVIDER/RSR_SX_DATAPROVIDER” @

        Set oNodeList = objXML.SelectNodes(“//T_DATAPROVIDER/RSR_SX_DATAPROVIDER”). In one of your comments, you had suggested to check the string value of “strXML” which would store the values of “ActiveWorkbook.CustomXMLParts(4).XML”.

             For my environment (Win7, MS Excel 2007, SAP GUI 730, Bex 7.x sp 4 – patch 1) this is pointing to the below address:

        “<?xml version=”1.0″ encoding=”utf-16″?><Application xmlns=”http://www.sap.com/ip/bi/bexanalyzer/excel/application“>H4sIAAAAAAAEAO29B2AcSZYlJi9tynt/SvVK1+B0oQiAYBMk2JBAEOzBiM3mkuwdaUcjKasqgcplVmVdZhZAzO2dvPfee++999577733ujudTif33/8/XGZkAWz2zkrayZ4hgKr.

        Looks like this path/variable is not referring to the local instance. And hence i don’t see any data in the repository sheet. Can you pls suggest.

        Thank you,

        Lohith

        (0) 
        1. François Gyöngyösi Post author

          The XML code seems compressed. You need to disable compression:

          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.


          The Afterword section at the end of the blog contains a screen capture of the dialog box involved.

          (0) 
          1. Lohith Hattera

            Thank you again. I cud uncheck the compress checkbox.

            Now the path of strxml shown is

            “”<?xml version=”1.0″ encoding=”utf-16″?><Application xmlns=”http://www.sap.com/ip/bi/bexanalyzer/excel/application“><PAGE xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:xsd=”http://www.w3.org/2001/XMLSchema“><PAGENO>0</PAGENO><VERSION>1</”

            I feel that the problem may not be with this as i don’t get any error at this point. I feel the problem is at “Set oNodeList = objXML.SelectNodes(“//T_DATAPROVIDER/RSR_SX_DATAPROVIDER”)”.

            What exactly does this “/T_DATAPROVIDER/RSR_SX_DATAPROVIDER”” path indicate?

            Pls suggest.

            Thank you,

            Lohith

            (0) 
            1. François Gyöngyösi Post author

              This is used to select the node of the XML document that contains the infoproviders. If you want to dump the XML document in a text file, just type in the following commands in the VBA Immediate Window (press return after each line to execute the command):

              open “c:\test.xml” for output as #1

              print #1, ActiveWorkbook.CustomXMLParts(4).XML

              close #1

              Hope it helps.

              (0) 
              1. Lohith Hattera

                Hi Francois,

                Great! Yes, it indeed helped me to see and understand the contents of XML file. I could see the parameter values and other details.

                     I think the key was to save the workbook LOCALLY after enabling

                menu Tools > References… and choose Microsoft XML, vX e.g. Microsoft XML, v6.0). Without doing this, the command “ActiveWorkbook.CustomXMLParts(4).XML” would not get the right reference i suppose.

                     After doing this, generateCommand wrote all the parameter/variables details (technical name, value(s), operators etc) in a newly created sheet. I will have to customize this code to make this content look like SAPBexQueries sheet’s data so that i do not need to make any changes to the existing VBA code used in 3.x.

                     Few of our reports are very complex containing upto 3-4 queries. I definitely will have challenges going forward:-)

                     As of now, I would like to thank you very much for all your immediate responses. Looking forward to have few more positive discussions.

                Have a nice day.

                Lohith

                (0) 
                1. Lohith Hattera

                  Hi Francois,

                       As per my previous post i could read the parameter values from the XML file and could customize according to my project need. Later these parameter values will be shown to the user along with some other data (few charts and tabular o/p etc).

                       I have a question related to reading the parameter values from XML.

                       Scenario is that my workbook is saved as an excel file in an application server instead as a BW workbook. This excel workbook will be accessible by a link. User can refresh the report upon opening.

                       As per my learning (mentioned in previous post) to read the latest parameter values from the XML, i always need to SAVE the workbook first. But in this case, since i have the report located in a server, i cannot allow user to overwrite the original workbook in the server.

                       This is getting me to a limitation wherein i need to force the user always to store the workbook locally so that i can save the workbook using “thisworkbook.save” in VBA to read the latest parameter values from XML. Otherwise, the XML file will always contain unsaved/old parameter values which will lead to a confusion.

                       Is there a solution for this issue? Is there any other way i can read the latest values from XML without saving the workbook?

                  Pls suggest.

                  Lohith

                  (0) 
                2. Ryan Lee

                  Hi Lohith,

                  I do have exact same problem you used to have. path of strxml was showing exactly as you would have after you disabled compression.

                  I tried to save it LOCALLY and run again but they seem to have same.

                  One thing to note is that I’m running on Windows server, I’m thinking saving locally in C: drive is still located in server causing the problem.

                  Would you have any other alternative solution to this?

                  When I run ParseXML, there are no variables generated.

                  Please share any suggestions! 🙂

                  Thanks,

                  Ryan

                  (0) 
  3. David Deal

    This seems like exactly what I need to do.  The problem I am having is with this line:

    strXML = ActiveWorkbook.CustomXMLParts(4).XML

    My ActiveWorkbook object only has 3 CustomXMLParts.  (confirmed by using ActiveWorkbook.CustomXMLParts.Count)

    (I also tried viewing the XML of parts 1, 2, and 3 and none appear to store variable values.)

    I can see the BExRepositorySheet in the VBA Editor, and I can unhide it using VBA.  It’s empty except for the number 7 in cell A1.  I can only assume the variables are actually stored in the XML for that sheet.  When I click the Change Variable Values button on the BEx Analyzer toolbar, the variables I want to change are there and they have the last values I used.

    Could this be related from your instructions?

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

    Obviously I’ve done that but it seems like what’s happening is the variables’ values aren’t being saved in the BEx XML repository.  (BExRepositorySheet)  I tried saving the file on my desktop and in the root of C:.  Although that doesn’t explain where BEx is getting the values, they’re definitely stored in the workbook somewhere.

    I’m not ready to give up on this yet, does anyone have any ideas?  🙂

    I tried renaming the file from .XLSX to .ZIP and viewing all of the XML that way.  I opened every .XML document and I don’t see the variables there either.  If anyone who has this working really wants to help me out, can you rename one of your files to .ZIP and see if you can see which .XML file has the variables stored?  (if any)

    (0) 
    1. Sebastian Sieber

      Hi all,

      got the same problem like David.

      “strXML = ActiveWorkbook.CustomXMLParts(4).XML” doesnt exist.

      I’m using Win7 & Office2010 & BW 7.x.

      Anyone got a solution?

      Thanks, Sebastian

      (0) 
      1. François Gyöngyösi Post author

        I have not tried under Office 2010. You may try the following:

        * Launch BEx Analyzer

        * Open a BEx query

        * Go to the Visual Basic Editor (Alt+F11)

        * If needed, display the Immediate Window (menu View > Immediate Window)

        * Type in the following command in the Immediate Window and press Enter:

        ? ActiveWorkbook.CustomXMLParts(4).XML

        Do you get an error ?

        Regards.

        François.

        (0) 
        1. Sebastian Sieber

          I get an error (translated from German):

          Runtime Error ‘9’:

          Index out of allowed range.

          Running the command with (1) (2) (3) gives the following results:

          (1)

          <cp:coreProperties xmlns:dc=”http://purl.org/dc/elements/1.1/” xmlns:cp=”http://schemas.openxmlformats.org/package/2006/metadata/core-properties“><dc:creator>mruettge</dc:creator><cp:keywords/><dc:description/><dc:subject/><dc:title/><cp:category/><cp:contentStatus/></cp:coreProperties>

          (2)

          <Properties xmlns=”http://schemas.openxmlformats.org/officeDocument/2006/extended-properties“><Company>T-Systems International GmbH</Company><Manager/></Properties>

          (3)

          <CoverPageProperties xmlns=”http://schemas.microsoft.com/office/2006/coverPageProps“><PublishDate/><Abstract/><CompanyAddress/><CompanyPhone/><CompanyFax/><CompanyEmail/></CoverPageProperties>

          Thanks for your help!

          Sebastian

          (0) 
        2. Sebastian Sieber

          Update:

          Saved the Workbook as xlsx (standard is xls)

          now i get the encrypted problem.

          ______________________________________

          Ptown Bro Solution is remove the check mark from WorkBook setting “Use Compression When saving WorkBook”

          ——————————————————————-

          But I don’t find that Option.

          Can You please tell me where it is?

          Thanks!

          Sebastian

          (0) 
  4. Whitney Nielsen

    I added the code, and created the button, and everything works great, except all of my reports now have the filter formatting in cells C14:D16.  Every time I add a new Data Provider in any workbook with this code, this filter formatting appears and I cannot make it go away.  We’re in the testing process of upgrading from 3.5 to 7.0 so I’m still not very familiar with 7.0.  Do you have any suggestions of how to remove this?

    (0) 
    1. François Gyöngyösi Post author

      You could go into BEx Design Mode and see if you have a “Filter” component in cell C14:D16. You can then remove it. As to why it appears… The only thing I can think of is your default workbook template. Click on Global Settings in the BEx toolbar, choose the Default Workbook tab and check your current default workbook (maybe it contains the filter area). Click on Use SAP Standard and see if you have the same issue. Good luck with the upgrade.

      (0) 
      1. Whitney Nielsen

        Thanks for your quick response. It no longer appears. Resetting my default workbook must have done the trick.  It was already set to the SAP Standard, so when I re-did everything that filter formatting was removed. I must have had a funky workbook before.  Thanks for your help!

        (0) 
  5. Whitney Nielsen

    Francois,

    Above it says that it will not work with hierarchy variables.  I’m feeding in different cost center nodes with a loop, so essentially you click the button and the report runs 400 times each time with a different node. I had everything working in 3.5 and now I’m trying to rebuild in 7.0.  Is there a way that I can do this but with a hierarchy variable?

    Thanks,

    Whitney

    (0) 
    1. François Gyöngyösi Post author

      Whitney,

      I added support for hierarchy node variable (but not for hierarchy variable). Hope it helps.

      Note that each time a macro writes a value in an Excel cell (with BEx active), an event is triggered and BEx fires up to see if it’s got something to do. This slows everything down. To prevent this, add the following code at the beginning of your macro (Excel won’t generate events):

      Dim lEnableEvents As Boolean

      lEnableEvents = Application.EnableEvents

      Application.EnableEvents = False

      And the following code at the end of your macro:

      Application.EnableEvents = lEnableEvents

      Have fun!
      François.

      (0) 
      1. Whitney Nielsen

        Thanks so much! The new code was what I was looking for, in 3.5 I had code to add in the 0HIER_NODE portion, but I wasn’t sure how to incorporate that in 7.0.  We’ve noticed that the event triggered where BEx looks to see if it has to do anything seems to happen regardless if we’re using a macro or not.  This has really effected performance since we upgraded a few months ago.  If you have any suggestions on how to resolve this at a system level please send me a direct message with the information. I’d really appreciate it!

        (0) 
  6. Sheila Brown

    Hello, Francois!

    I hope you are still following this post. I was able to follow your instructions to the letter without incident, until I got to “Run the macro  ParseXML”. When I do this, nothing happens at all.

    I am using Windows 7, Office 2010 and BEx Analyzer 7.30. I tried saving the query workbook to my local drive as .xlsx, but when I did that, I got the message that the file had to be macro enabled, which is a .xlsm file. I saved it anyway as .xlsx, but then none of the macros would work, not even the ones I created. However, when I saved it as .xlsm, nothing happens when I run the macro.

    I have also unchecked the “Use Compression When Saving Workbook” as well.

    Any ideas what is happening? Could it be that the variables are not being saved to the BExRepositorySheet for some reason? Does the BExAnalyzer.xla add-in need to be active when running the macro?

    Did I place the code in the proper place? My Project – VBAProject screen looks like this:

    + VBAProject (PERSONAL.XLSB)

    –  VBAProject (WORKING-WD() Out of Balance Bal Sht DC) (This is my workbook)

         – Microsoft Excel Objects

              Sheet 1 (BExRepositorySheet)

              Sheet 2 (Table)

              Sheet 3 (Graph)

              ThisWorkbook

         – Modules

              Module1 (This is where I put your code)

    I would really appreciate your assistance or anyone else’s! I am sooooo close to getting tis to work.

    Thanks!

    Sheila B.

    (0) 
    1. Sheila Brown

      I just wanted everyone to know that I was able to resolve my problem on my own! It appears that, when using Excel 2010, you have to save the workbook as an .xlsm (macro-enabled) workbook, not an .xlsx. Also, make sure that you have the “Provide Results Offline” box checked on the Data Provider screen of the BEx query in order to for variables to save to the BExRespositorySheet.

      Sheila B.

      (0) 
  7. Denis Lessard

    I seem to be missing something:

    I ran your script on BEx 7.X revision 1887 (Excel 2003 on Window XP) to get the exact variables content.  Worked great !

    I created the BEx button as shown… 

    I changed the value of the variables in the Excel range to be able to validate.

    But VBA (Excel) expects to have a macro called BUTTON_2_Click. (BUTTON_2 is my actual button number).  So the call to the button returns an error 438.

    Since it is not created by default with the BEx Button creation,  I tried to create it with nothing or with calls to SPABEXrefresh but the variables values are not changed when I display the change variable values menu.

    What am I missing?

    What should I put in the Click macro ?

    (0) 
    1. François Gyöngyösi Post author

      If you follow the above procedure and insert a BEx Button, you’ll end up with VBA code similar to this:

      Public Sub BUTTON_33_Click()

        Dim BEx1 As Object

        Set BEx1 = Application.Run(“BExAnalyzer.xla!GetBEx”)

        Call BEx1.RaiseButtonClick(Parent.Name, “BUTTON_33”)

      End Sub

      This VBA code is located in the Excel sheet where you inserted the button. Let’s say this sheet is Sheet4 (be careful, you need to refer to the “internal” name of the sheet as shown in the VBA editor). To call the above code, type in:

      Call Sheet4.BUTTON_33_Click

      Note that as you type in the above code in the VBA editor, BUTTON_33_Click should appear as a method of Sheet4 as shown below:

      /wp-content/uploads/2014/03/click_420184.png

      Regards.

      François.

      (0) 
      1. Denis Lessard

        Thank you,

        I don’t know why but the code was not created.  Or maybe I deleted it by mistake…

        Anyway, everything works well now

        Merci

        Denis

        (0) 
  8. Whitney Nielsen

    I’m back!  I’ve added the code to another new workbook, and no matter what I do – the prompt screen always comes up the first time that I run the report. It does not give any messages, it appears like the values are not being fed into the variables. However – the reports update with the values that I have in the Command Area.  I have CMD PROCESS_VARIABLES and SUBCMD VAR_SUBMIT as my commands in the button.  I compare it to my other packets that use this code and I cannot see any differences.  Do you have any other suggestions on what to check?

    (0) 
      1. Whitney Nielsen

        Within the same file, I’m having issues with it processing the variable that has customer exits, it indicates that it can not determine the value. Does something need to be done differently in the command lines for variables with customer exits?

        (0) 
        1. François Gyöngyösi Post author

          I tried a query with variables with customer exits and it worked fine. If you still have the issue, could you provide the definition of the variable i.e. its properties (is it accessible, etc.).

          (0) 
          1. Whitney Nielsen

            It is still an issue.  The variable causing issues is a time variable, fiscal year period.  we have some abap variables built off of it to calculate month to date, quarter to date, year to date on exit. However even though I’m feeding the one date into the prompt screen, it isn’t able to calcuate the month to date, quarter to date, year to date within the queries.

            (0) 
              1. Lohith Hattera

                Hi Francois,

                     Can you please help on how to refresh individual queries manually?

                Command Run(“BExAnalyzer.xla!MenuRefreshVariables”) seem to be running all the queries together. Does this command accept any parameter with which we can mention the query to be executed?

                Pls suggest.

                Lohith

                (0) 
                  1. Lohith Hattera

                    Hi Francois,

                    I’m still exploring the option of refreshing single Bex query. Meanwhile i got another problem.

                    I am trying to run the below command WITHOUT being connected to the BW system

                    x = Run(“BExAnalyzer.xla!MenuRefreshVariables”)

                            In Bex 3x also, similar approach was done and upon execution, BEx used to prompt a window to select the desired BW system and after entering the credentials, variable screen for the first query used to be popped up.

                            But the behavior is different with 7x. In 7x, upon executing the command, the window to select the BW system will be popped up and after entering the credentials, BEx is executing the query with PREVIOUSLY run variable values!

                    Below solution was suggested in one of the threads (http://scn.sap.com/thread/1336977).

                    1. – In BEXInterfaceModule change the code to:

                    Public Function BEx() As Object

                      Set BEx = Application.Run(“BExAnalyzer.xla!GetBEx”, ThisWorkbook)

                    End Function

                    2. – go to TOOLS –> References and check the “SAP BEX Analyser API”

                    Does this work? or do we need to do any other changes?

                    Pls suggest.

                    Lohith

                    (0) 
                    1. François Gyöngyösi Post author

                      I don’t seem to have the problem. I executed the Run(“BExAnalyzer.xla!MenuRefreshVariables”) command and it worked fine. After I logged into SAP BW, I got the variable screen. For information, I use BI AddOn 7.X SP7, Patch 1, Revision 2015.

                      (0) 
                      1. Lohith Hattera

                        Well seems like i am using a slightly older version.

                        BI Addon 7.x (based on 7.30) SP4 patch 1, Revision 1938.

                        Do you see any issues with that?

                        Also, should i do below steps too?

                        1. – In BEXInterfaceModule change the code to:

                        Public Function BEx() As Object

                          Set BEx = Application.Run(“BExAnalyzer.xla!GetBEx”, ThisWorkbook)

                        End Function

                        2. – go to TOOLS –> References and check the “SAP BEX Analyser API”

                        Does this work? or do we need to do any other changes?

                        Lohith

                        (0) 
                        1. François Gyöngyösi Post author

                          Sorry, I don’t know what all these steps do. So I won’t be able to help you on this. One last thought. You may want to make sure that you get the variable screen when you click directly on the Change Variable Values icon without being connected.

                          (0) 
                          1. Lohith Hattera

                            Am sorry…i am not getting it:-(

                            I see that “Change Variable Values” option will be enabled only if we are connected to a BW system… so how do i refresh query without being connected? Is there any command which i can use to make the connection to BW explicitly?

                            Thank you,

                            LohithBex7x_ChangeVarDisabled.png

                            (0) 
                            1. François Gyöngyösi Post author

                              There is a bug in BEx Analyzer. If the option is not available, just create a new workbook and close it. The option should become available (at least that’s what happens with my version of BEx Analyzer).

                              (0) 
                              1. Lohith Hattera

                                “Change Variables” option will always be disabled whenever i open the workbook or any saved excel until i connect to a system. However i found a way to fix it:-) I have added the below code in the “Workbook_Open()” module.

                                Workbooks.Open(FileName:=sSAPFileName, ReadOnly:=True).RunAutoMacros Which:=xlAutoOpen

                                where sSAPFileName = “C:\Program Files (x86)\Common Files\SAP Shared\BW\BExAnalyzer.xla”.

                                So, whenever the workbook is opened, excel adds the BW toolbar (if it was not added already). Later, when i call the Run(“BExAnalyzer.xla!MenuRefreshVariables”) in a personalized module, both server connection and query refresh will happen:-)

                                Do you see any issues with this approach?

                                There is one more concern:-(

                                My requirement is to disable all the Bex toolbar icons when the report/workbook is opened. I don’t want user to do any query refresh manually instead he must always use a VB button provided. I am successful in disabling a particular icon using the below command.

                                Application.CommandBars(“Bex analysis toolbox”).Controls(2).Enabled = False.

                                But, the icons will be activated back after the query is refreshed. Looks like Bex enables all the Bex icons (open, save, refresh, change variables) back so that user can use them. Is there a way to suppress the Bex functionality?

                                Again, as a workaround, am disabling the icons using the above command after the query is refreshed.

                                Thank you,

                                Lohith

                                (0) 
  9. Stephan Ip

    Francois,

    First my kudos to you for such an exceptional article. Thank you for sharing your knowledge with extracting the BEx 7 variables via VBA. We have leveraged your code to develop an Excel add-in for this very purpose.

    However, I want to let you know that, in the generateCommand procedure, there’s a small block of code that needs to modified slightly:

    ‘if individual variable

    If varNode.SelectNodes(“VPARSEL”).Item(0).nodeTypedValue = “P” Then

        Call writeLog(“VAR_VALUE_EXT_” & n2 + 1, N,
            varNode.SelectNodes(“LOW_EXT”).Item(0).nodeTypedValue)

    Else

        Call writeLog(“VAR_VALUE_LOW_EXT_” & n2 + 1, N,
            varNode.SelectNodes(“LOW_EXT”).Item(0).nodeTypedValue)

    End If

    So here in this snippet of code, if the variable is a single value variable (VPARSEL = “P”), the label name is like this: “VAR_VALUE_EXT_[n]”, otherwise the label name has the string “LOW” in it, like this: “VAR_VALUE_LOW_EXT_[n].

    We are experiencing that in sporadic cases, this doesn’t always work. Clicking the BEx command button may occasionally produce an error for some variables, like “Value for variable is invalid”.

    Then what we do for these variables is we have to manually change the label name from “VAR_VALUE_LOW_EXT_[n]” to “VAR_VALUE_EXT_[n]”, or vice versa. And when we click the BEx command button, then it works and there’s no error.

    Again, this problem is sporadic and occasional, but it happens enough that we need to modify the VBA code.

    My questions for you:

    1. Have you experienced this same problem before?

    2. What do think we need to do to modify the logic to handle this?

    Thank you,

    –Stephan Ip

    (0) 
    1. François Gyöngyösi Post author

      Stephan,

      I would need an example to analyse the problem. Could you provide the definition of the variable from the Bex Query Designer as well as the commands that cause the problem.

      Thanks.
      François.

      (0) 
      1. Stephan Ip

        Thank you Francois.

        Here’s a specific example: we have a variable named ZLEDGER2, and the generateCommand procedure writes the various nodes/values to the worksheet as follows:

        VAR_NAME_1 0 ZLEDGER2
        VAR_OPERATOR_1 0 EQ
        VAR_SIGN_1 0 I
        VAR_VALUE_LOW_EXT_1 0 L2

        When the BEx command button is clicked — that runs the Process Variables command as you showed us how to do in your article — we get an error.

        If we manually change the VAR_VALUE_LOW_EXT_1 label name to VAR_VALUE_EXT_1 (removing the “LOW”), and click the BEx command button again, then it works!

        Again note however sometimes we need to do the opposite, and add the “LOW” to the label name manually to make it work.

        In this case, looking at BEx Query Designer at the specific query, the ZLEDGER2 variable is defined as follows:

        General tab

        Description: Ledgers (LL,L2,L3,L4)

        Technical Name: ZLEDGER2

        Global Settings:

           Type of Value: Characteristic Value

           Processing By: Manual Input/Default Value

           Reference Characteristic: [ZLEDGER]

        Replacement Path tab

        Replacement Rule: Replace Variable With []

        Details tab

        Variable Represents: Multiple Single Values

        Variable Is: Mandatory

        Variable is Ready for Input checked

        Personalization: Copy Personalization Data from This Variable: (blank)

        Default Values tab

        Default Values: Ledgers (LL,L2,L3,L4)

        Currency/Unit tab

        Dimension Settings:

           Dimensions: []

           Unit: (blank)

        Advanced tab

        UID: 0YBO67FU7WDYAGPI9F3ZTE7GR

        (0) 
        1. François Gyöngyösi Post author

          Stephan,

          I added support for Multiple Single Values variables (“LOW” shouldn’t be used in this case). The code has been modified i.e. the code below:

          ‘if individual variable

          If varNode.SelectNodes(“VPARSEL”).Item(0).nodeTypedValue = “P” Then

              Call writeLog(“VAR_VALUE_EXT_” & n2 + 1, N,

                  varNode.SelectNodes(“LOW_EXT”).Item(0).nodeTypedValue)

          Else

              Call writeLog(“VAR_VALUE_LOW_EXT_” & n2 + 1, N,

                  varNode.SelectNodes(“LOW_EXT”).Item(0).nodeTypedValue)

          End If

          has been replaced by:

          ‘if individual variable (P = Single Value and M = Multiple Single Value)

          Select Case varNode.selectNodes(“VPARSEL”).Item(0).nodeTypedValue

          Case “P”, “M”

               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

          Regards.

          François.

          (0) 
          1. Stephan Ip

            Thanks again Francois.

            We have noticed in one other case that we need to remove the “LOW” manually from the label to make the BEx command button work, when VPARSEL = “F” (formula variable). However, I don’t know if you can handle this the same way, because a formula variable can be single value, multiple single value, or anything, yes? So it’s possible that a formula variable label can be either VAR_VALUE_EXT_ or VAR_VALUE_LOW_EXT_, right?

            (0) 
            1. François Gyöngyösi Post author

              Stephan,

              I modified the code to have the same logic when VPARSEL=”P”, “M” or “F”. It seems to me that Formula variables available for input always return a single value (otherwise BEx won’t be able to handle the calculation). So, this should hopefully work in all cases.

              Regards.

              François.

              (0) 
                  1. Lohith Hattera

                    Hi Francois,

                    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

                    (0) 
                      1. Lohith Hattera

                        Thank you for your response. I was expecting for a positive answer but i am disappointed:-(

                        So there is no option at all in BEx 7x to refresh a query individually? That’s an issue for me as i need that functionality to make sure that my report which was running properly in 3x will run properly in 7x. Is this something which we need to check with SAP support team? Pls suggest.

                        Lohith

                        (0) 
                        1. François Gyöngyösi Post author

                          Maybe there is a way but we didn’t find it. You’ll find several discussions on SCN on this subject. When we moved from BEx 3.5 to BEx 7, we had in one case to split queries in different workbooks to refresh them individually and then copy the result from one workbook to the other one.

                          (0) 
                          1. Lohith Hattera

                            Hello Francois,

                            Am glad that, i could refresh a query individually without refreshing all the queries embedded in the work book using command variables. Actually this setup was working earlier too, but i thought it wasn’t. 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

                            (0) 
  10. Whitney Nielsen

    Hi Francois – I’m back again.  I’m implementing this in yet another reporting packet.  This new packet has 21 Data Providers, and when I generate the commands, it produces about 500 rows of variables/commands.  I added the button, but when I used the button to execute the refresh, it takes 5-6 times longer to run the workbook.  When I run the workbook normally (without the button) it takes about 50-60 seconds, but with the button, it’s taking almost 6 minutes.

    Any ideas on how to decrease the run time when using the button? Or any idea why using the button would change the run time so dramatically?

    Thanks!

    Whitney

    (0) 
    1. François Gyöngyösi Post author

      Hi Whitney – I managed to reproduce the issue but didn’t find a workaround. I tried to pause the refresh using VBA, split the refresh using several buttons but nothing worked.

      (0) 
        1. Whitney Nielsen

          Francois – My packet has a loop for cost center, which is our authorization variable, and typically I just need to run the 20 DPs for one cost center node at a time, however, I have one time where I need to have it run for several Cost Center nodes at the same time.  Typically – we just select each node and it displays: “1000CCNODEA;1000CCNODEB;1000CCNODEC” etc…. I tried to feed the cost centers like this into automation with the loop, however it isn’t recognizing it as a correct value.  Do you have any ideas how I can run it for multiple nodes?  I can either run for all of the nodes, or select one higher level node, then exclude a separate node.

          (0) 
          1. François Gyöngyösi Post author

            I don’t think you can feed the list of all the cost centres in one go. You probably need to have one entry for each cost centre. Just execute your query for all the cost centres. Execute generateCommand (see VBA code above) and see what you get.

            (0) 
  11. Barry Amirian

    Hi Francois – Thank you very much for providing this valuable information, I was wondering if you were able to find a way to handle the Hierarchy Variables. Thank you.

    (0) 
    1. François Gyöngyösi Post author

      It seems that hierarchy variables are not managed in the same way as other variables. So, the macro won’t be able to generate the code automatically. However, I think you can code directly the hierarchy variable and its value. For example:

      VAR_NAME_3 0 ZHIERVAR
      VAR_VALUE_EXT_3 0 50C_USGAAP

      where ZHIERVAR is your hierarchy variable and 50C_USGAAP is the hierarchy.

      (0) 
  12. Rodolphe LALOUX

    Thanks a lot for this blog !

    Just remark:

    BI Addon 7.x (based on 7.30) SP4 patch 1, Revision 1938 & Windows 8 & Excel 2013

    => If i choose Microsoft XML, v6.0 that does not work but ok with v4.0

    (0) 
  13. Russell Oakes

    All of the above has been absolutely invaluable however recently we have upgraded to SP9 revision 2051… Anything which i previously set up to run automatically still works however i am now unable to parse the XML and return any information for any existing or new workbooks/ queries… Has anyone else come across this problem?

    (0) 
    1. François Gyöngyösi Post author

      Have you checked the Afterword section at the end of the blog i.e. make sure Use Compression When Saving Workbook is unchecked and issue around Excel 2003 vs Excel 2007 file format?

      (0) 
      1. Russell Oakes

        Thanks for the prompt response Francois it seems since the upgrade this option is defaulting to checked as soon as the file is saved… I have got our BW Infosys team looking into it as they’re none the wiser!

        (0) 
  14. Kuldeep Mishra

    Thanks a lot for your development although this is a very old post but that was my last savior however it does not generate variable that need to be changed for example: I save a workbook to my local drive as suggested by you and run your code to generate the variable but it gives me only

    DATA_PROVIDER

    CMD

    SUBCMD.

    (0) 
  15. Ryan Lee

    Hi François Gyöngyösi,

    Thanks for your valuable input, I’m basically new and still struggling with the instruction you provided.

    I happened to only generate the following when I execute parsexml () after referencing Microsoft XML v6.0.

    I use BI Addon 7.X (Based on 7.40), running under windows server 2008.

    I’m weary that excel version is 2013, which might be the issue.

     

    Can you please suggest what the issues are? I’m real desperate to automate bex reports that we’ve just implemented.

    Thanks,

     

     

     

     

    DATA PROVIDER 0:DATA_PROVIDER_1
    VARIABLES: ”
    *************************************************”

    (0) 
    1. Ryan Lee

      Below is the path of strxml that I get when I run the sub module, they don’t seem compressed but the issue the other had exactly same solved by saving it in LOCAL folder.

      I indeed have it saved under C:\ but I’m thinking the fact that they are running under windows server 2008 might cause not to recognize the variables.

      At my work, we only have SAP Bex system implemented in windows server (running Citrix environment), so I guess having to run it at local drive machine (not in server) is not a viable solution.

      I’m been researching online for this and I know people actually got this working whichi is positive but here at this point I’m so desperate.

      Any help would be appreciated!

      Thanks,

      Ryan

      (0) 
      1. François Gyöngyösi Post author

        I also use BI Addon based on 7.40 and the macro works fine. So no issue on this side.

        Not sure about Citrix but if you run BEx inside Citrix then your Excel also runs on the server (as BEx is an Excel addin).If you completely leave Citrix and start again, can you access again the Excel file you saved on C:\?

        The other thing to look at is the definition of the variables used in your query. From BEx Query Designer, could you provide the Global Settings and Details of each variable. See what Stephan Ip provided in the above comment dated October 18, 2014.

        (0) 
        1. Ryan Lee

          Thanks for your prompt response. 🙂

          For your first question, yes I’m able to access the excel file that I saved in C:\  after logging off Citrix environment. The saved file exist in the same folder as is.

          So basically there are two variables options that pop-up in change variables values window.

          It looks like there are a sub-variables under each “Profit Center” and “Fiscal Year” variable called “Select Profit Center:” and “Fiscal/Year Period (Select Option)”.

          Thanks,

          Ryan

           

          (0) 
          1. François Gyöngyösi Post author

            Could you provide additional details on the variable used for Profit Centre. I’d like to know if there is a hierarchy variable. In BEX Query Designer, right-click on Profit Centre in the Filter > Characteristic Restrictions area and select Restrict… and then select Variables from the Show drop-down list. What do you have (sample screen from my system below)?

            (0) 
            1. Ryan Lee

              Is there other way I could get those infos?

              Based on my authority it would not let me choose from the option by right clicking the variable in Bex Query Designer, I’ll have to contact IT to get the authorization.

              Can you not identify by “type of variable” in global setting? It seems the variable is hirarchy type.

              Thanks,

               

              (0) 
              1. François Gyöngyösi Post author

                I wanted to see if you had a variable hierachy but from the screen copies you provided, it looks like the hierarchy is 1000TBGP with 1000 your controlling area (?). Anyway, I created a query with the same variable definitions and the macro works fine. Not sure why it doesn’t work for you. Here is the output I get (I inserted your variable names and values assuming 1000 is your controling area). You may want to try these commands and see if it refreshes your query properly (adjust DATA_PROVIDER if needed).

                DATA_PROVIDER 0 DATA_PROVIDER_1
                CMD 0 PROCESS_VARIABLES
                SUBCMD 0 VAR_SUBMIT
                VAR_NAME_1 0 0I_FY_OP
                VAR_OPERATOR_1 0 BT
                VAR_SIGN_1 0 I
                VAR_VALUE_LOW_EXT_1 0 001.2016
                VAR_VALUE_HIGH_EXT_1 0 012.2017
                VAR_NAME_2 0 ZHV_PRFCHE
                VAR_VALUE_EXT_2 0 100012300
                VAR_NODE_IOBJNM_2 0 0HIER_NODE

                If you have additional hierarchy nodes, just add them in the list. For example, to add hierarchy node 100012350:

                VAR_NAME_3 0 ZHV_PRFCHE
                VAR_VALUE_EXT_3 0 100012350
                VAR_NODE_IOBJNM_3 0 0HIER_NODE
                (0) 
                1. Ryan Lee

                  Hi Francois,

                  I was actually able to get the button working properly…I believe it was the “Profit Center” variable being hierarchy variable that I had to add Object name at the bottom which I did not include it previously.

                   

                  I still do not know why I cannot run XML code, but this definitely helps..

                   

                  I’m curious to see if it also work on filter variables, for example on the filter table on the left of the report if I add them in the list would it change the filter values? (They do not appear in the change variable values window)

                  Thanks so much,

                  Ryan

                  (0) 
                    1. Ryan Lee

                      Hi Francois,

                       

                      After researching for filtering function, I came across with another method that uses the same button function in designer mode in Bex.

                       

                      It is “Data specific command” that gives option to set filter using the button and I ran it succesfully with one of characteristic variables.

                       

                      But for some reason it does not run with hierarchy variables, below you can see I was trying to filter “Cost Center” variable by setting variable node name at the bottom of the line. (It does not work w/o the line either)

                       

                      Just because I was not able to run XML codes, I was wondering if there are any way to find the technical names for the hierarchy variables.

                       

                      Below you can find the settings for the variable “Cost Center (0COSTCENTER)”.

                       

                      Any help would be appreciated!

                      Thanks,

                      Ryan

                      (0) 
                        1. Ryan Lee

                          Hi Francois,

                          Thank for for the comment.

                           

                          If you meant to have “EXT” excluded, this does not seem to do the work.

                           

                          I’m not sure if “Costcenter” is even considered as hierarchy variables, if you see below these two “cost center” and “cost element” variables have different icons to refer to I believe hierarchy variables. Its only them not working with filtering button command which led to me think variable type difference is the issue.

                           

                          I’ll workaround by adding the variable in the change variable window like I have for “profit center”, or change it to characteristic variable.

                           

                          Thanks for the help!

                          Ryan

                           

                           

                           

                          (0) 

Leave a Reply