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:
- 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.
- Open up the developer tab and click the Visual Basic button.
- 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
