Essbase Excel Add-Ins VBA (Case Study)

You can automate Excel Essbase Add-Ins with simple VBScript.  Automating the Excel Essbase add-ins may open up possibility of creating excel based report directly, without Financial Reporting needed.

I had once created a report on Financial Reporting (FR) where it failed the job gracefully.  The report was very detailed and needed a lot of combination.  The report also shows text input (something which will be retrieved as a number index by Excel Essbase add-ins).  If I opened that report in FR the report simply crashes the service.  The report was too much for FR to handle. For your info, the Report Service had been tuned to use 2 GB of memory, and it still crashes.  So we held a meeting with the client, and we found out that the client actually wants to open it in Excel for analyzing purposes (Exporting the needed FR report to Excel).  So we propose an idea of creating an Excel based report using Excel Essbase Add-Ins, an Oracle ODBC and VBA.  Here's how:

1. Install the Oracle Client (We need ODBC) and the Excel Essbase Add-Ins (I assumed you knew how to install those items).  In my case, my client uses Oracle 9i, so I installed the Oracle ODBC Driver called Oracle in OraHome92.  I installed it from the default Oracle Client Installation Package and you need to set up the ODBC.
2. Open up the developer tab and click the Visual Basic button.
3. Create a Module, and copy the insides of %ARBORPATH%\9.3.1\bin\essxlvba.txt into the module.

• To Connect to essbase use this command:

x = EssVConnect(targetWorksheet, "admin", "password", "serverName", "appName", "dbName")

• To Disconnect to essbase use this command:

x = EssVDisconnect(targetWorksheet)

• To Retrieve data use this command:

x = EssVRetrieve(targetWorksheet, range(rang), 1)

• To Get Sheet Option use this command:

x = EssVGetSheetOption("Staging", 13)

• To Set Sheet Option use this command:

x = EssVSetSheetOption(Worksheet, ItemId, Status)

• To Zoom In use this command

x = EssVZoomIn("Staging", Null, range(rang), 3, False)

Here is my code :

Dim targetWorksheet As String

Sub DisConnEss(ByVal targetWorksheet As String)

x = EssVDisconnect(targetWorksheet)
End Sub

Sub ConnEss(ByVal targetWorksheet As String)
x = EssVConnect(targetWorksheet, "admin", "password", "server", "appName", "dbName")
End Sub

Sub RetData(ByVal rang As String)
x = EssVRetrieve(targetWorksheet, range(rang), 1)
If x = 0 Then
   MsgBox ("Retrieve successful.")
Else
   MsgBox ("Retrieve failed.")
End If
End Sub

Private Sub Duplicate(ByVal FromSheet As String, ByVal ToSheet As String)
    Sheets(FromSheet).Select
    Sheets(FromSheet).Copy after:=ActiveSheet
    ActiveSheet.Name = ToSheet
End Sub

Sub GenerateResult()
    ConnEss "Staging"
   
    Sheets(targetWorksheet).Activate
    Sheets(targetWorksheet).range("A4").Select
   
    ActiveCell.FormulaR1C1 = cmbDivision.Text
   
    If WorksheetExists("Staging") Then
        DeleteSheet ("Staging")
    End If
   
    Duplicate targetWorksheet, "Staging"
       
    Dim IncludeSelectionOpt As Boolean
    IncludeSelectionOpt = EssVGetSheetOption("Staging", 2)
    If IncludeSelectionOpt = True Then
        SetSheetOption "Staging", 2, False
    End If
   
    Dim AliasOpt As Boolean
    AliasOpt = EssVGetSheetOption("Staging", 13)
    If AliasOpt = False Then
        SetSheetOption "Staging", 13, True
    End If
   
    Dim RepeatOpt As Boolean
    RepeatOpt = EssVGetSheetOption("Staging", 25)
    If RepeatOpt = False Then
        SetSheetOption "Staging", 25, True
    End If
       
    Dim IndentOpt As Integer
    IndentOpt = EssVGetSheetOption("Staging", 5)
    EssVSetSheetOption "Staging", 5, 1
   
    ZoomData ("A4")
    ZoomData ("B4")
    ZoomData ("C4")
   
    EssVSetSheetOption "Staging", 5, IndentOpt
   
    If RepeatOpt = False Then
        SetSheetOption "Staging", 25, RepeatOpt
    End If
   
    If AliasOpt = False Then
        SetSheetOption "Staging", 13, AliasOpt
    End If
   
    If IncludeSelectionOpt = True Then
        SetSheetOption "Staging", 2, IncludeSelectionOpt
    End If
     
    DisConnEss "Staging"
   
    GetOracleCellText
   
    If WorksheetExists("Result") Then
        DeleteSheet ("Result")
    End If
    Duplicate "Staging", "Result"
   
    HouseKeeping

    Reporting
End Sub

Private Sub cmdGoDivision_Click()
    Sheets("Staging (Template)").Visible = True
  
    targetWorksheet = "Staging (Template)"
    GenerateResult
     
     If WorksheetExists("Staging") Then
        DeleteSheet ("Staging")
    End If
   
    Sheets("Staging (Template)").Visible = False

End Sub

Sub Reporting()
    Sheets("Result").Columns(4).EntireColumn.Delete

    Sheets("Result").Rows(1).EntireRow.Delete
    Sheets("Result").Rows(1).EntireRow.Delete
   
    Sheets("Result").Columns(1).Select
    ActiveCell.EntireColumn.Insert
   
    Dim i As Integer
    Dim CostCenter As String
    Dim length As Integer
    i = 2
    Do
        Sheets("Result").range("B" & i).Select
        If (ActiveCell.Text <> "#Missing" And ActiveCell.Text <> "") Then
            length = Len(ActiveCell.FormulaR1C1)
           
            CostCenter = Left(ActiveCell.FormulaR1C1, 4)

            Sheets("Result").range("A" & i).Select
            ActiveCell.FormulaR1C1 = CostCenter
           
            Sheets("Result").range("B" & i).Select
            ActiveCell.FormulaR1C1 = Mid(ActiveCell.FormulaR1C1, 6, length - 4)
        End If
        i = i + 1
        Sheets("Result").range("B" & i).Select
    Loop While (ActiveCell.Text = "#Missing" Or ActiveCell.Text <> "")

    Sheets("Result").range("A1").Select
    ActiveCell.FormulaR1C1 = "Cost Center"
   
    Sheets("Result").range("B1").Select
    ActiveCell.FormulaR1C1 = "Cost Center Desc"
       
    Sheets("Result").range("C1").Select
    ActiveCell.FormulaR1C1 = "Grade"

    Sheets("Result").range("D1").Select
    ActiveCell.FormulaR1C1 = "Position Title"
   
   
End Sub

Sub HouseKeeping()
    Dim i As Integer
    Dim posTitle As String
   
    i = 4
    Do
        Sheets("Result").range("D" & i).Select
        If (ActiveCell.Text <> "#Missing" And ActiveCell.Text <> "") Then
            posTitle = ActiveCell.FormulaR1C1
            Sheets("Result").range("C" & i).Select
            ActiveCell.FormulaR1C1 = ActiveCell.FormulaR1C1 & " - " & posTitle
        End If
        i = i + 1
        Sheets("Result").range("D" & i).Select
    Loop While (ActiveCell.Text = "#Missing" Or ActiveCell.Text <> "")
End Sub

Private Sub SetSheetOption(ByVal Worksheet As String, ByVal ItemId As Integer, ByVal Status As Boolean)

        x = EssVSetSheetOption(Worksheet, ItemId, Status)
        If x > 0 Then
            MsgBox ("Error on Server")
        ElseIf x < 0 Then
            MsgBox ("Local Error")
        End If
End Sub

Sub DeleteSheet(strSheetName As String)
' deletes a sheet named strSheetName in the active workbook
    application.DisplayAlerts = False
    Sheets(strSheetName).Delete
    application.DisplayAlerts = True
End Sub

Sub ZoomData(ByVal rang As String)
x = EssVZoomIn("Staging", Null, range(rang), 3, False)
'If x = 0 Then
   'MsgBox ("Zoom successful.")
'Else
   'MsgBox ("Zoom failed.")
'End If
End Sub

Sub Zoom(ByVal Column As String)
Dim i As Integer
Dim rang As String
i = 4
rang = Column & i
Do
    Sheets("Staging").range(rang).Select
    If (ActiveCell.Text <> "#Missing" And ActiveCell.Text <> "") Then
        ZoomData (rang)
    End If
    i = i + 1
    rang = Column & i
Loop While (ActiveCell.Text <> "#Missing" And ActiveCell.Text <> "")
End Sub

Sub GetOracleCellText()

Dim Conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String
Dim currentText As String
Dim targetWorksheet As String

Dim connString As String

connString = "Driver={Oracle in OraHome92};Dbq=HYPERION;Uid=USERID;Pwd=PASSWORD;"
Conn.Open connString
Sheets("Staging").Activate

Dim i As Integer
i = 4

Sheets("Staging").range("D" & i).Select
Do
   
    If ActiveCell.Text <> "#Missing" And ActiveCell.Text <> "" Then
        strSQL = "SELECT VALUE FROM HSP_TEXT_CELL_VALUE WHERE TEXT_ID = '" & ActiveCell.Text & "'"
        rs.Open strSQL, Conn, adOpenForwardOnly, adLockOptimistic
       
       
        If rs.EOF Then
            ActiveCell.Text = ""
        Else
            ActiveCell.FormulaR1C1 = rs![Value]
        End If
        rs.Close
    Else
        'ActiveCell.Text = ""
    End If
    i = i + 1
    Sheets("Staging").range("D" & i).Select
Loop While (ActiveCell.Text = "#Missing" Or ActiveCell.Text <> "")

End Sub





5222: Unable to view report. You do not have access to the following members on the POV (Hyperion Workspace Error)

Error message says: "5222: Unable to view report.  You do not have access to the following members on the POV: Version" (or any other kind of dimension members).  User can't view report in Hyperion Workspace.  Reports have at least one dimension set as a user POV.

A possible solution is to check whether Preview is set to ON in workspace's Preferences for each respected user.

If it still can't be opened, the problem may lay on the assigned access in the Planning's dimension.  Here I selected the "Version" dimension as directed on my error message.  (You have to open Planning - Advanced Mode, then in the Administration menu select Dimensions, then open up the respected Dimension Member).

Click on a member then click Assign Access.  In the following pop up page assign a user to the member and their respected Access Right.