Roll Your own Analysis Office Features (Part I) – Generating Data Source Documentation via Macro

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.