Skip to Content

This is the first article in a series, exploring the various methodologies for “application building” in Analysis Office.  In the initial installments, we will be working solely with worksheet formulas and Visual Basic for Applications (VBA) macros.  Later, we’ll explore building our own add-ins to further enhance Analysis, using Microsoft’s Visual Studio Tools for Office (VSTO).

Customers often come to us with asking for features or changes in the way Analysis Office works.  With ideas that benefit many of our customers, we add them to the product development backlog. With ideas that apply to a specific customer or ideas that if in the standard product would violate standard “best practices” for security, usability, etc they cannot be added to the backlog.

Some of these ideas that cannot be added to the backlog are still excellent ideas and the customer who proposed them would benefit if they saw the light of day.  Fortunately, Analysis Office exists within the Microsoft Office environment; which means that all of the various methods of extending and automating office are also valid for extending and automating office Analysis.  Analysis also brings its own palette of formula and macro commands to the table, making it even more powerful.

The feature that we’ll look at in this installment was inspired by a recent customer visit.  Make the data source metadata available when offline.  This means that the Information tab of the Design Panel empty of anything interesting until a data source has been refreshed.  If a user has a large workbook, with many data sources, she may want to have a peek at this information without waiting for the whole workbook to refresh.  So let’s give her this ability!

What we need (AKA Requirements and Specifications)

  • We need some sort of nonvolatile way of storing the contents of the Information Tab for offline use.  For the sake of simplicity here, we’ll use hidden worksheets to hold this information.  Whenever the user wants to peek at it, she can simply unhide the relevant sheet.
  • This hidden sheet needs a naming convention so that the user can quickly find the sheet that she wants to look at.  We’ll use <DataSourceAlias>_<QueryName>.  The data source alias is the “script and formula name”, usually something along the lines of DS_1, DS_2, etc.  The query name here is the text name, not the technical name.  With these two bits of information, the user should easily be able to find her hidden sheet.
  • We’ll fill this sheet with the information from the Information tab of the Design Panel.
  • As an added bonus, we’ll show the variables and filters that are applied to the data source.

DocumantDataSource()

We are going to call our macro DocumantDataSource.  We’ll make it a standard subroutine with no parameters.  This will allow use us to use it later as we see fit.  For example, the user could assign a button or hotkey combination to Excel to trigger this macro manually.  It could also be called from the CallbackAfterRedisplay macro (if there is one present) to automatically generate the data whenever the data source is refreshed.  We’ll start with a blank subroutine and declare all of the variables that we’ll need.

Sub DocumentDataSource()

‘ DocumentDataSource Macro

    Dim wrkSheet As Worksheet

    Dim activeRange As Range

    Dim sheetName As String

    Dim datasourceName As String

    Dim dataSourceVariables As Variant

    Dim dataSourceFilters As Variant

    Dim dslabel(1 To 11) As String

    Dim dsIinfo(1 To 11) As String

    Dim nNth As Integer

    Dim currRow As Integer

End Sub

Now let’s fill it in!

If we have selected a crosstab or chart, then…

With the following lines of code, we’ll ensure that the current focus cell is within a crosstab.  We do this by making a call to the Analysis VBA API command, SAPGetCellInfo to check whether we have a selected data source by asking for the formula alias of the current focus cell.  If the selection is not a crosstab or chart, then asking for it’s formula alias will return an error.  The rest of the macro will live inside the if statement, ensuring that it only executes if we have indeed selected a data source.

    dataSourceAlias = Application.Run(“SAPGetCellInfo”, Selection, “DATASOURCE”)

    If IsError(dataSourceAlias) = False Then

    End If

Create the New Hidden Sheets

In the next 9 lines of code (not counting comments), we’ll do a couple of chores. 

  1. We’ll ask the for the name of the data source and concatenate that with the formula alias, to determine the sheet name.
  2. We delete any sheet by that name, if one exists.  We turn off the display of alerts and turn it back on afterwards.  If we don’t do this, Excel will show the user a popup, asking whether she really wants to delete the sheet or not. 
  3. We create a new sheet by that name. 

Astute readers may notice that there is no VBA command in the Analysis API called SAPGetSourceInfo, but that there is a worksheet formula by that name.  This is correct!  AO worksheet formula commands may also be called with the from Application.Run, just like macro commands.

        ‘Determine the data source alias, text name and the name of the properties sheet

        datasourceName = Application.Run(“SAPGetSourceInfo”, dataSourceAlias, “DataSourceName”)

        sheetName = dataSourceAlias & “_” & datasourceName

        ‘Delete the existing sheet if there is one.

        ‘   We turn off the display of alerts when we do this, because we don’t want the user bothered by a dialog

        ‘   We turn it back on after deleting the sheet

        On Error Resume Next

        Application.DisplayAlerts = False

        Application.ActiveWorkbook.Sheets(sheetName).Delete

        Application.DisplayAlerts = True

        ‘Create the new worksheet

        Set wrkSheet = Application.ActiveWorkbook.Sheets.Add

        wrkSheet.Name = sheetName

        wrkSheet.Visible = xlSheetHidden

Get the metadata from Analysis

Next up, we’ll prepare the information fields from the Information tab.  We’ll pack this information into lists, so that we can write them in a single loop.  Again, we are using Application.Run to call an AO worksheet formula; in this case, SAPGetSourceInfo.

        dslabel(1) = “Data Source Name”

        dslabel(2) = “Key Date”

        dslabel(3) = “Last Data Update”

        dslabel(4) = “Query Technical Name”

        dslabel(5) = “Info Provider Technical Name”

        dslabel(6) = “Info Provider Name”

        dslabel(7) = “Query Created By”

        dslabel(8) = “Query Last Changed By”

        dslabel(9) = “Query Last Changed At”

        dslabel(10) = “System”

        dslabel(11) = “Logon User”

        ‘Collect the data fields

        dsIinfo(1) = Application.Run(“SAPGetSourceInfo”, dataSourceAlias, “DataSourceName”)

        dsIinfo(2) = Application.Run(“SAPGetSourceInfo”, dataSourceAlias, “KeyDate”)

        dsIinfo(3) = Application.Run(“SAPGetSourceInfo”, dataSourceAlias, “LastDataUpdate”)

        dsIinfo(4) = Application.Run(“SAPGetSourceInfo”, dataSourceAlias, “QueryTechName”)

        dsIinfo(5) = Application.Run(“SAPGetSourceInfo”, dataSourceAlias, “InfoProviderTechName”)

        dsIinfo(6) = Application.Run(“SAPGetSourceInfo”, dataSourceAlias, “InfoProviderName”)

        dsIinfo(7) = Application.Run(“SAPGetSourceInfo”, dataSourceAlias, “QueryCreatedBy”)

        dsIinfo(8) = Application.Run(“SAPGetSourceInfo”, dataSourceAlias, “QueryLastChangedBy”)

        dsIinfo(9) = Application.Run(“SAPGetSourceInfo”, dataSourceAlias, “QueryLastChangedAt”)

        dsIinfo(10) = Application.Run(“SAPGetSourceInfo”, dataSourceAlias, “System”)

        dsIinfo(11) = Application.Run(“SAPGetSourceInfo”, dataSourceAlias, “LogonUser”)

Next up, we actually populate these values into the hidden worksheet

        ‘Write the non variable/filter bits.  The label in Col A and the Value in Col B

        ‘We are writing the values, not the formulas

        For nextRow = 1 To 11

            wrkSheet.Cells(nextRow, 1).Value2 = dslabel(nextRow)

            wrkSheet.Cells(nextRow, 2).Value2 = dsIinfo(nextRow)

        Next nextRow

Variables and Filters

The variables and effective filters are handled a bit differently than most of the other properties.  They are a bit trickier, because SAPListOfVariables and SAPListOfEffectiveFilters both deliver “lists of lists” as variants.  We handle them by giving them their own worksheet blocks, with one line per variable or filtered dimension.  The block has three columns: 

  1. The first tells us whether it is a variable or filter section, in cross-join style (no repeating of the same info on following lines). 
  2. The second column tells us the variable name or dimension name. 
  3. The third is the variable/filter value.

We use the currRow variable to track where we are in the worksheet, so that we don’t overwrite anything.

        ‘Let’s proceed with handling the variables and filters

        currRow = 11

        ‘Variables

        nNth = 0

        dataSourceVariables = Application.Run(“SAPListOfVariables”, dataSourceAlias)

        For Each dataSourceVariable In dataSourceVariables

            nNth = nNth + 1

            currRow = currRow + 1

            ‘In the first row of variables, we’ll write the appropriate signifier in col 1

            If nNth < 2 Then

                wrkSheet.Cells(currRow, 1).Value2 = “Variables”

            End If

            wrkSheet.Cells(currRow, 2).Value2 = dataSourceVariable(nNth, 1)

            wrkSheet.Cells(currRow, 3).Value2 = dataSourceVariable(nNth, 2)

        Next

        ‘Filters

        nNth = 0

        dataSourceFilters = Application.Run(“SAPListOfEffectiveFilters”, dataSourceAlias)

        For Each dataSourceVariable In dataSourceFilters

            nNth = nNth + 1

            currRow = currRow + 1

            ‘In the first row of filters, we’ll write the appropriate signifier in col 1

            If nNth < 2 Then

                wrkSheet.Cells(currRow, 1).Value2 = “Effective Filters”

            End If

            wrkSheet.Cells(currRow, 2).Value2 = dataSourceFilters(nNth, 1)

            wrkSheet.Cells(currRow, 3).Value2 = dataSourceFilters(nNth, 2)

        Next

Re-size the Columns for Readability

        ‘resize the columns

        wrkSheet.Columns(“C:C”).EntireColumn.AutoFit

        wrkSheet.Columns(“B:B”).EntireColumn.AutoFit

        wrkSheet.Columns(“A:A”).EntireColumn.AutoFit

And Voila!  We now have a macro for storing the data source metadata offline.

Blog1.png

To report this post you need to login first.

33 Comments

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

  1. John Coudriet

    Good article David . We would like to create a default workbook and place the prompts or effective filters in the same worksheet or tab with the result set or crosstab but I couldn’t find out to reposition the crosstab depending on how many effective filters are present in VB code. Is it possible to reposition the crosstab in vb code and if it is could samp[le code be provided?

     

    thanks

    (0) 
    1. David Stocker Post author

      I have to experiment with this.  After discussing it around the A Office dev team, there is no consensus.  If you select the named range and move it, it is supposed to work, but we catch the paste event.  What I’m not certain of is whether the event is triggered when moving a named range in VBA. 

      (0) 
      1. Dirk Mayrock

        Hi John, David,

         

        in the past I tried to have 2 crosstab underneath its other and tried to move the lower one always 5 rows below the end of the first one.

         

        I tried to hang my code into the Callback after redisplay but it did not work. the movement of the secend crosstab again raised a redisplay. AO cold not run this and gave error “13 – A callback is running”.

         

        So I guess its the same here.

         

        The only workaround that I found is…

        Let the crosstab start in row 50 for example and then hide the rows between the last effective filter and row 50. This assumes that you will not build more than 50 efective filters, increase as desired.

         

        Only not so nice looking thing is that if the row numbers are displayed the user sees the “missing” area…. its just a workaround.

         

        Dirk

        (0) 
          1. Dirk Mayrock

            Hi John,

             

            if you don’t get it run on your own get back to me. I can then try to find the document on my drive, I thin I saved a copy of it locally.

             

            Dirk

            (0) 
        1. David Stocker Post author

          VBA is single threaded, so we can’t spawn any asynchronous threads that wait for the after redisplay callback and then move the second crosstab; so it looks like your workaround is  the only game in town.

           

          That is unless you want to write a VSTO add-in to do it. 

           

          Wait!  If you turn off the refresh before moving the second crosstab and then turn it back on at the very end of the redisplay callback, it might work; if you are lucky and Excel completes the macro before triggering the new refresh.

           

          Cheers,

          Dave

          (0) 
          1. Dirk Mayrock

            Hi David,

             

            but without rendering the 1st crosstab you cant calculate the new postition of the second one… so I guess there is now way actually.

             

            Dirk

            (0) 
            1. David Stocker Post author

              Well, the after redisplay callback in AO comes after rendering (as opposed to its BEx predecessor, which fired before rendering).  But still, you are depending on how Excel times and orchestrates its VBA macro calls, vs event callbacks; and if it works, it’s just blind luck.

               

              Even if it worked, I’d not trust that it is reliable in a production environment.

               

              Cheers,

              Dave

              (0) 
    1. Alagappan Ramasamy

      Hi All,

       

         Fiscal year displaying ‘V6/2014’ in cross tab table but same report effective filter showing ‘Z1/2014’. But V6/2014 is the correct one how to change in my effective filter value into ‘V6/2014’ . Kindly help me very urgent

       

      Thanks & Regards,

       

      Alagappan R

      (0) 
  2. Martin Kreitlein

    Hi David. It is a great idea to start this series. Thanks a lot.

     

    As you can imagine, I already tried to use your coding but I’m a little bit disappointed that the three timestamp fields used, are not displaying the correct date/time.

    E.g.: 414.425.190.162.037 should reflect:  17.06.2013 12:27:23

    … but the excel format DD.MM.YYYY hh:mm:ss will result in #####################

    Could you please update your coding with the proper format for the timestamps?

    Thanks a lot, Martin

    (0) 
    1. Dirk Mayrock

      Hi Martin,

       

      I changed the definition of the array dsIinfo to type variant
      Dim dsIinfo(1 To 11) As Variant

       

      When you go to the hidden sheet you can apply the needed Format like “Short Date” to the 3 mentioned fields.

       

      Hope that helps.

       

      Dirk

      (0) 
      1. Martin Kreitlein

        Hi Dirk,

        good idea.
        So, I enhanced the coding in this way now… and it works fine 🙂

         

                For nextRow = 1 To 11
                    wrkSheet.Cells(nextRow, 1).Value2 = dslabel(nextRow)
                    wrkSheet.Cells(nextRow, 2).Value2 = dsIinfo(nextRow)

         

        ‘New 16.7.2013 – Begin
                    If nextRow = 2 Or nextRow = 3 Or nextRow = 9 Then
                       Cells(nextRow, 2).NumberFormat = “m/d/yyyy h:mm”
                    End If
        ‘New 16.7.2013 – End

         

                Next nextRow

        (0) 
    2. Alagappan Ramasamy

      Hi All,

       

         Fiscal year displaying ‘V6/2014’ in cross tab table but same report effective filter showing ‘Z1/2014’. But V6/2014 is the correct one how to change in my effective filter value into ‘V6/2014’ . Kindly help me very urgent

       

      Thanks & Regards,

       

      Alagappan R

      (0) 
  3. Andrew Roebuck

    Very simialr to an approach I took that collates information about DataSources, Crosstabs, Variables, Dimensions, Open workbooks, hidden sheets, loaded add-ins etc. Very useful for debugging and support. Looking forward to more like this.

    (0) 
  4. John manlo

    Hello David,

     

    In my requirement, A query in workbook should refresh using Macro by clicking a button, Do you have any code for that..?

     

    Thanks

    PSN

    (0) 
    1. Martin Kreitlein

      Hi PSN,
      you could use this coding:

       

      Sub ButtonClick()

      Dim lResult As Long
      Dim VlastError As Variant

       

      ‘ Refresh necessary?
      lResult = Application.Run(“SAPGetProperty”, “IsDataSourceActive”, “DS_1”)
       
      If lResult = 0 Then
      ‘ Refresh
        lResult = Application.Run(“SAPExecuteCommand”, “Refresh”, “DS_1”)
        VlastError = Application.Run(“SAPGetProperty”, “LastError”)
         If VlastError(1) <> 0 Then
      ‘ Error message
            lResult = Application.Run(“SAPAddMessage”, “Error: ” & VlastError(1) & ” ” & VlastError(2), “ERROR”)
         End If
      End If

      End Sub

       

      Regards, Martin

      (0) 
      1. Bheem Reddy

        Hi Martin,

         

        Thanks for the  above code.

         

        I  have an issue with SAP FBL3N t-code where im trying to run VBA throughh excel and extracting a data from  SAP. The sap is taking a little longer time to extract the data and give the out put in the screen. i have to wait til the data generates if i use wait function i will  have to make vba for 10 mins , sometimes if the data is extracted earlier in sap it will stay till it completes 10 mins. i wanted vba to wait  exactly till the actual run time.

         

        Can you suggest any code for that.

         

        appreciate your co-operation

         

        thanks in advance

        (0) 
  5. Vasu Mullapudi

    Hi @David

     

    Great post.

     

    Is there a work around to execute Worksheet SAP functions in VBA?

     

    Am trying to dynamically change one of the Variables in VBA using a button. SAPSetVariable works fine for setting the variable but I have to get the current value of the variable from worksheet.

     

    Wondering if there is a way to use SAPGetVariable directly in VBA without referencing worksheet cells.

     

    Appreciate your help.

     

    Thanks,
    Vasu

    (0) 
    1. Martin Kreitlein

      Hi Vasu.

      I’m not sure if David is checking this Blog regularly?!

      However, so far I as well did not find any other solution to “get” a variable than via the formula  =SAPListOfVariables(“DS_x”;”KEY”;”PROMPTS”) and taking the value from the respective cell.

      Regards, Martin

      (0) 
      1. Martin Kreitlein

        Hi Victor,

        great idea! But tell me one secret…

         

        How do you determine if the variablesArray contains only 1 filled variable, e.g.:

        variablesArray(1) = Name of variable

        variablesArray(2) = value –> which I need

         

        or more variables:

        variablesArray(1, 1) = Name of first variable

        variablesArray(1, 2) = value –> which I need

        variablesArray(2, 1) = Name of second variable

        variablesArray(2, 2) = value –> which is not relevant

         

        I tried it with ReDim “variablesArray()” but this deletes the Contents 🙁

         

        Thanks, Martin

        (0) 
        1. Victor Gabriel Saiz Castillo

          Hi Martin,

           

          not sure if I follow correctly.

          You can use additionally the parameter “ALL_FILLED”, to get only the list variables in the array which have been answered.

          You can also loop through the array (e.g. variablesArray(x, 1)) to count how many variables are in there.

           

          Best regards,

           

          Victor

          (0) 
          1. Martin Kreitlein

            Hi Victor,

            when I’m debugging my code I can see the result of “variablesArray” is different, depending if only one variable is filled, or more than one.

             

            That means, if only one variable is filled, the result is a one-dimensional Array.

            If more variables are filled it is a two-dimensional Array (see examples above).

             

            So, how do I know how to access it correctly?

            variablesArray(x, 1) will not be working with only 1 variable filled, since my value is located in variablesArray(2).

             

            Regards, Martin

            (0) 
  6. Kumar Ko

    Hi David

     

    It is a nice blog to educate us about Analysis/VBA related notes.

     

    Our requirement is : User shd be able to select parent-child hierarchy nodes/leafs on the prompt screen.

     

    What we did:

    Due to hidden sheet, we have created a small button with name “Select Hierarchy” in the work book & used

    lResult = Application.Run(“SAPCallMemberSelector”, “DS_5”, “FILTER”, “CHILD”, ActiveCell, “MULTIPLE”)

    to select parent-child hierarchy nodes/leafs & have sent these selections back to HANA with little code as below:

     

    Call Application.Run(“SAPSetRefreshBehaviour”, “Off”)

    Call Application.Run(“SAPExecuteCommand”, “PauseVariableSubmit”, “On”)

    Call Application.Run(“SAPSetVariable”, “ZVAR_NODE”, lResultNODE, “INPUT_STRING”, “DS_4”)

    Call Application.Run(“SAPSetVariable”, “ZVAR_LEAF”, lResultLEAF, “INPUT_STRING”, “DS_4”)

    Call Application.Run(“SAPExecuteCommand”, “PauseVariableSubmit”, “Off”)

    Call Application.Run(“SAPSetRefreshBehaviour”, “On”)

     

     

    EVERY THING IS GOOD.

     

    But how can we add this small button “Select Hierarchy” we have on workbook to the prompt.

    So that users can select parent-child hierarchy nodes/leafs on the prompt window itself.

     

     

    What we believe is unless we see the hidden sheet macros, we can not do any customization to the prompt related code.

     

    Can you pls suggest how to unhide it.This sheet has property : xlSheetVeryHidden & can not be opened. If it is with “xlSheetHidden” – it wud have been visible just by clicking options.

     

    Will be waiting for your expert suggestions.

     

    Venkat

    (0) 
  7. Barnaby Richards

    I’m trying to figure out ho to obtain SAPGetSourceInfo without having Analysis running.

     

    The requirement is based on the need to broadcast reports from BIP with meta-data available for users who do not have Analysis installed.

    (0) 

Leave a Reply