Part II: Analysis Services Reporting in Excel
In Part I, I discussed how to set the worksheet up, now it’s time to look at some code. Building a report consists of three logical operations: determining the correct MDX that will yield the desired results, fetching the data, and displaying the data. In this post I’ll focus on the first task – determining the correct MDX. Before I jump into that task, I’d like to show the main procedure that glues these three tasks together along with some module level information.
LISTING 1:
Option Explicit
'/ Module variable
Dim msMDX As String
'/ Connection String - modify data source property appropriately
Private Const CONN = "Data Source=;Initial Catalog=foodmart 2000;" & _
"Provider=msolap;Execution Location=3;" & _
"Default Isolation Mode=1;"
Public Enum OPERATION
REFRESH_REPORT
DEFAULT_REPORT
PREVIOUS_REPORT
REBUILD_REPORT_WS
REBUILD_REPORT_CODE
End Enum
Public Sub DefaultReport()
GetReport DEFAULT_REPORT
End Sub
Public Sub PreviousReport()
GetReport PREVIOUS_REPORT
End Sub
Private Sub GetReport(opType As OPERATION)
Dim cst As ADOMD.Cellset
Dim sMDX As String
Dim ws As Worksheet
Dim nMonth As Integer
On Error GoTo ErrHandler
Set ws = ThisWorkbook.Worksheets("Report")
'/ Build the MDX statement
Select Case opType
Case REFRESH_REPORT
sMDX = ws.Range("CurrentMDX")
Case DEFAULT_REPORT
sMDX = BuildMDX(ws, True)
Case REBUILD_REPORT_WS
sMDX = BuildMDX(ws, False)
Case PREVIOUS_REPORT
sMDX = ws.Range("PriorMDX")
Case REBUILD_REPORT_CODE
sMDX = msMDX
End Select
SaveMDX ws, sMDX
'/ Get the data
Set cst = GetCellset(sMDX)
If cst.State = 0 Then Exit Sub
'/ Display the results
ws.Range(ws.Range("TopLeft").Offset(1, 0), _
ws.Range("R65536")).ClearContents
DisplayCellset cst, ws, ws.Range("TopLeft").Offset(1, 0)
ExitPoint:
Set ws = Nothing
Set cst = Nothing
Exit Sub
ErrHandler:
If Err.Number <> 18 Then
Beep
MsgBox "The Following Error has occurred:" & vbCrLf & _
Err.Description, vbCritical, " Error!"
End If
Resume ExitPoint
End Sub
In Part I I put two drawing objects on the worksheet called “Default Report” and “Previous Report”. These “buttons” are hooked up to the DefaultReport and PreviousReport procedures respectively. As you can see, all they do is call the GetReport procedure with the correct Operation. In Part I, I only showed off three types of Operations: DEFAULT_REPORT, PREVIOUS_REPORT, and REBUILD_REPORT_CODE (I didn’t mention this explicitly, but this type of operation occurs when you drill down on an item). You could add a simple procedure to call GetReport specifying the REFRESH_REPORT operation. All this does is build the report using the MDX stored in the CurrentMDX named range. For this example, I didn’t implement the REBUILD_REPORT_WS operation so specifying this operation results in the same functionality as the DEFAULT_REPORT operation. The idea of REBUILD_REPORT_WS is to figure out what the MDX should be based on the items on the worksheet. This allows the user to build some “free-form” reports. For example, they can just enter the stores they want to see sales numbers for (without drilling to the correct spot in the hierarchy) and the procedure figures out the correct MDX.
For the purposes of this example, I simplified the BuildMDX function and its related functions. Here is an elementary implementation.
LISTING 2: Building the MDX
Private Function BuildMDX(ws As Worksheet, bDefault As Boolean) As String
BuildMDX = "SELECT " & _
ColumnMDX() & _
RowMDX(ws, bDefault) & _
"FROM [Sales] " & _
SlicerMDX
End Function
Private Function ColumnMDX() As String
ColumnMDX = "{[Time].[1997].[Q1], [Time].[1997].[Q2]} on columns, "
End Function
Private Function RowMDX(ws As Worksheet, bDefault As Boolean) As String
Dim sMDX As String
Dim nRow As Integer
Dim sCurrent As String
Dim sPrevious As String
Dim sDefaultMDX As String
sDefaultMDX = _
"NON EMPTY { (Descendants([Store].[USA], 1, SELF_AND_BEFORE)) }"
If bDefault Then
sMDX = sDefaultMDX & " ON ROWS " & vbCrLf
Else
sMDX = "NON EMPTY { "
nRow = ws.Range("TopLeft").Row + 1
sPrevious = ""
If IsEmpty(ws.Cells(nRow, 1)) Then
sMDX = "NON EMPTY " & sDefaultMDX & " ON ROWS " & vbCrLf
Else
Do Until IsEmpty(ws.Cells(nRow, 1))
sCurrent = Trim(ws.Cells(nRow, 1).Value)
If sCurrent <> sPrevious Then
sMDX = sMDX & _
"([Store].[" & Trim(ws.Cells(nRow, 1).Value) & "]),"
End If
sPrevious = sCurrent
nRow = nRow + 1
Loop
sMDX = Left(sMDX, Len(sMDX) - 1)
sMDX = sMDX & "} ON ROWS" & vbCrLf
End If
End If
RowMDX = sMDX
End Function
Private Function SlicerMDX() As String
SlicerMDX = "WHERE([Measures].[Store Sales])"
End Function
I could have simplified this some more by just folding everything into one or two procedures. For example, ColumnMDX and SlicerMDX simply return a static string – why break those out into separate functions? The reason I left them as separate functions is because normally you’ll want to build these dynamically as well. It would be a trivial exercise to modify these functions so that the user could select which measure they want to see (SlicerMDX) or to offer the ability to navigate across the time periods (ColumnMDX).
The RowMDX function is the only procedure that does any real work here. Most of the procedure handles the case where the MDX needs to be determined based on the items on the worksheet. As you can see, this implementation is fairly primitive – all it does is loop until the first empty cell is found, adding the value of each cell as a member name item in the row specification.
This is probably a good time to address a potential security concern. Is this code subject to a SQL Injection attack? The simple answer is yes. It would be possible to break this code by crafting a special snippet of MDX and placing it in one of the worksheet cells. On the other hand, MDX and Analysis Services is a bit different than SQL and a database. Most cubes I come across are write-only – you cannot modify the values or add new values to the cube. If you do have a cube with writeback enabled, the potential risk is much higher. Also, note that you cannot use the CommandText and Parameters.AddWithValue method that is used with SQL statements in ADO. The ICommandWithParameters interface is not supported by the MSOLAP provider. That said, I’d strongly recommend that you check any values you read off the spreadsheet before using them in your MDX. Maybe I’ll post on this subject in an upcoming post.
Though handling the drill up/down could probably be considered part of determining the correct MDX, I save this piece for a future post. Next up in this series: fetching the data and displaying the results.