Skip to Content

It is quite common that the API of Analysis Office returns arrays containing the requested information.

Example: The workbook contains two Data Sources DS_1 and DS_2. The API call to read the list of Data Sources ( Application.Run(“SAPListOf”, “DATASOURCES”) ) will return the following information: 

DS_1 Description of DS_1
DS_2 Description of DS_2

 

A typical question that arises: Suppose I am only interessted in the first column ( in the example above: containing the data source alias) of the result set. How can I easily extract it? And how can I expose the content to the user?

Well lets answer it step by step by going through the sample coding:

After retreiving the list of DataSources from the API it checks for an empty result (IsArray()-function). Then it copies the first column (Index-function), transposes it into a row and converts it into a string containg the Data Source Alias, seperated by “,” (in our example, the result is “DS_1″,”DS_2”). Based on a named range as target, it creates the cell validation/drop down.

VBA – Source Code Sample

Option Explicit

Sub AO_UpdateDSList()

  ‘ get list of data sources
  Dim DataSources As Variant
  DataSources = Application.Run(“SapListOf”, “DATASOURCES”)
  
  ‘ extract data source alias (first column of result) and converts it to a string with comma seperated values
  Dim DDValues As Variant
  Dim DSAlias As Variant
  If IsArray(DataSources) Then
    DSAlias = Application.WorksheetFunction.Index(DataSources, 0, 1)
    DDValues = Join(Application.WorksheetFunction.Transpose(DSAlias), “,”)
  Else
    DDValues = DataSources
    If DDValues = “” Then DDValues = “No data sources found!”
  End If
  
  ‘ create/adjust the data validation for the named range “AOListOfDataSource”
  With Range(“AOListOfDataSource”).Cells.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=DDValues
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = “List of Data Source Alias”
        .ErrorTitle = “Invalid Data Source”
        .InputMessage = “Select a data source from the list of values”
        .ErrorMessage = “The entered data source doesn’t exist. Please enter a valid value”
        .ShowInput = True
        .ShowError = True
 End With
 
 ‘ clear current cell content
 Range(“AOListOfDataSource”).Value = “”
 
End Sub

 

Once you have defined the named range and created and executed the VBA subroutine successfully, the result should look similar to the screenshot (Hint: don’t forget to set the focus on the cell):

image

 

Outlook: Although the example is based on the list of data sources it can be used for other array based results as well, e.g. ListOfDimensions.

To report this post you need to login first.

1 Comment

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

  1. Olaf Fischer Post author
    I have been asked how this could be used for a list of members (“F4-Help”). Well it is pretty straight forward:

    1. Adjust the sample code by using the API function SAPListOfMembers(“DS_1″;”FILTER”;”0PRODUCT”)
    2. Adjust the .index statement to pick up the second column to get the member texts.

    That’s it.

    Hint: if you need the member key – the API function SAPGetMember(“DS_1″;”0PRODUCT=LAPTOP”;”KEY”) converts the text to key.
    =SAPGetMember(“DS_1″;H16;”KEY”)

    (0) 

Leave a Reply