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.
- We'll ask the for the name of the data source and concatenate that with the formula alias, to determine the sheet name.
- 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.
- 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:
- 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).
- The second column tells us the variable name or dimension name.
- 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.
- SAP Managed Tags:
33 Comments
