Hyperion Workspace Management Reporting

Financial Report(Grid, chart, POV, prompt, OLAP)
- Dynamic report (*.des)
- Snapshot reports (*.rpt)
- Books (*.kbk)
- Snapshot Books (*.kbt)
- Batch files (*.bch)

Web Analysis (.apt) (Essbase, RLDB, Drill through)
- Spreadsheet
- Chart
- Pinboard
- SQL spreadsheet
- Freeform grid

Interactive Report(.bqy)

Production Report(.sqr)

Excel Spreadsheet Addin report (can be imported into workspace, add user permission)

Job Scheduling for Financial Report:
- Scheduling batch for financial report on recurring time based
- Command Line Scheduler for financial report batch
(You can use an external scheduler to schedule batch jobs in EPM Workspace
You automate the process of launching batches using an external scheduler or launching batches after an external event occurs, such as the completion of a consolidation.)


Interactive Report(.bqy) and .Production Report(.sqr)
- Time based event
- External Trigger Event
Associate job with the events

Method to trigger the External Event:
- Java method: call ExternalTriggerEvent class, triggerEXE() method
- PL/SQL method: updated Hyperion.v8_trigger table with new date time

Java - Run Essbase Report Script

/*
RunReport.java
*/
package com.essbase.samples.japi;
import com.essbase.api.base.*;
import com.essbase.api.session.*;
import com.essbase.api.datasource.*;
//import com.essbase.api.dataquery.*;
import com.essbase.api.domain.*;
//import com.essbase.api.metadata.*;
/**RunReport Example does the following: Signs on to essbase domain,
Runs a report and Signs Off.
In order for this sample to work in your environment, make sure to
change the s_* variables to suit your environment.
*/
public class RunReport {
// NOTE: Change the following variables to suit your setup.
private static String s_userName = "admin";
private static String s_password = "password";
private static String s_olapSvrName = "bobpc";
/* Possible values for s_provider:
"Embedded" or "http://localhost:13080/aps/JAPI" */
private static String s_provider = "Embedded"; // Default
private static final int FAILURE_CODE = 1;
public static void main(String[] args) {
int statusCode = 0;
IEssbase ess = null;
IEssOlapServer olapSvr = null;
try {
acceptArgs(args);
// Create JAPI instance.
ess = IEssbase.Home.create(IEssbase.JAPI_VERSION);
// Sign On to the Provider
IEssDomain dom
= ess.signOn(s_userName, s_password, false, null, s_provider);
// Open connection with OLAP server and get the cube.
olapSvr = (IEssOlapServer)dom.getOlapServer(s_olapSvrName);
olapSvr.connect();
IEssCube cube = olapSvr.getApplication("Sample").getCube("Basic");
executeReport(cube);
System.out.println("\nReport Execution Sample Completed.");
} catch (EssException x) {
System.out.println("Error: " + x.getMessage());
statusCode = FAILURE_CODE;
} finally {
// Close OLAP server connection and sign off from the domain.
try {
if (olapSvr != null && olapSvr.isConnected() == true)
olapSvr.disconnect();
} catch (EssException x) {
System.err.println("Error: " + x.getMessage());
}
try {
if (ess != null && ess.isSignedOn() == true)
ess.signOff();
} catch (EssException x) {
System.err.println("Error: " + x.getMessage());
}
}
// Set status to failure only if exception occurs and do abnormal termination
// otherwise, it will by default terminate normally
if (statusCode == FAILURE_CODE) System.exit(FAILURE_CODE);
}
static void executeReport(IEssCube cube) throws EssException {
try {
// Execute a report by passing the specification string.
String repSpec = "{TABDELIMIT}" +
"{SUPALL COLHEADING NAMESON BLOCKHEADERS PAGEHEAD INDENTGEN 2 DECIMALS VARIABLE}" +
"
"
"{INCZEROROWS}" +
"{MISSINGTEXT \"n/a\"}
"
"{ OUTALTNAMES }" +
"
System.out.println("Executing a report by passing the specification string....");
System.out.println("\n\nReport Output for spec: "+repSpec+"\n----------------"+
"------------------------------------------------------------");
String output = cube.report(repSpec, true, false);
System.out.println(output);
// Execute a report by passing file name in the server.
String reportFile = "Top";
System.out.println("\nExecuting a report by passing file name in the server....");
System.out.println("\n\nReport Output for file: " + reportFile +
"\n-----------------------------");
output = cube.report(true, false, reportFile, false);
System.out.println(output);
// Copy a report file from server to JAPI client, and execute the
// report by passing this client file name.
reportFile = System.getProperty("java.io.tmpdir")+"Bottom.rep";
cube.copyOlapFileObjectFromServer(IEssOlapFileObject.TYPE_REPORT,
"Bottom", reportFile, false);
System.out.println("\n\nReport Output for file: " + reportFile +
"\n-----------------------------");
output = cube.report(true, false, reportFile, true);
System.out.println(output);
// Execute a report and get the output as an iterator. This is
// useful when report returns large resultset.
reportFile = "Asym";
System.out.println("\nExecuting a report to get output as an iterator....");
System.out.println("\n\nReport Output for file: " + reportFile +
"\n-----------------------------");
IEssSequentialIterator iterator = cube.report(reportFile, true, false,
true, false);
while ((iterator.isEndOfData() == false) &&
((output = iterator.getNextString()) != null))
System.out.println(output);
} catch (EssException x) {
System.err.println("Error: " + x.getMessage());
}
}
static void acceptArgs(String[] args) throws EssException {
if (args.length >= 4) {
s_userName = args[0];
s_password = args[1];
s_olapSvrName = args[2];
s_provider = args[3]; //PROVIDER
} else if (args.length != 0) {
System.err.println("ERROR: Incorrect Usage of this sample.");
System.err.println(
"Usage: java " + RunReport.class.getName() + " ");
System.exit(1); // Simply end
}
}
}

Essbase Excel Report

The report developing with Excel is very simple. The way is:
1. Design an interactive interface on Excel, that include: listing box, drop down box, text box, label, button, radio option, check box,etc. For example, the region listing box, the product category listing box, the process(data retrieve) button, the submit or update button.
2. Add proper VBA code and call VBA MACRO functions on these control boxes. Macro such as: ESSConnect(), ESSMenu(), ESSRetrieve(),etc
3. Then we can run the on demand interactive Excel report to make special query on the Essbase data, and navigate on different sheets.
4. The VBA code related to these control boxes can be saved as a VBA function library, so that the code can be reused on other Excel report

The next is a simple but standard sample:

Declare Function EssVConnect Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal username As Variant, ByVal password As Variant, ByVal server As Variant, ByVal application As Variant, ByVal database As Variant) As Long


Declare Function EssVGetMemberInfo Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal mbrName As Variant, ByVal action As Variant, ByVal aliases As Variant) As Variant

Declare Function EssVDisconnect Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant) As Long



Declare Function EssVFreeMemberInfo Lib "ESSEXCLN.XLL" (ByRef memInfo As Variant) As Long

Const EssBottomLevel = 3

Const EssChildLevel = 1

Const username = "admin"

Const password = "password"

Const ServerName = "bobpc"

Const AppllicationName = "Sample"

Const EssbaseDataBase = "Basic"

Sub Disconnect()

sFileName = ThisWorkbook.Name

For Each wsSheet In Worksheets

X = EssVDisconnect(sFileName & wsSheet.Name)

Next wsSheet

If X = 0 Then

answer = MsgBox("Disconnected!", 64, "Connection Dialog")

End If



End Sub

Sub lstRegion_Change()

v_ListIndex = Sheet1.Shapes(2).ControlFormat.ListIndex

v_ListText = Sheet1.Shapes(2).ControlFormat.List(v_ListIndex)

Call GetBottom(v_ListText)

End Sub

Sub UnselectListBox(ByVal ListBoxNam As Variant)



End Sub

Sub Refresh()

Call GetChild("Market")

Call GetBottom("Market")

End Sub

Sub Process()



End Sub

Sub Submit()



End Sub

Sub Export()

With Sheet1

Set cb = .Shapes.AddFormControl(xlCheckBox, 10, 10, 100, 10)

cb.ControlFormat.LinkedCell = "A1"

End With



End Sub

Sub Quit()



End Sub

Sub Actual()

'MsgBox (Sheet1.Shapes.Item(3).Name)



End Sub

Sub Budget()

'MsgBox (Sheet1.Shapes(3).ControlFormat.LinkedCell

End Sub



Sub Conn()

sFileName = ThisWorkbook.Name

For Each wsSheet In Worksheets

X = EssVConnect(sFileName & wsSheet.Name, username, password, ServerName, AppllicationName, EssbaseDataBase)

Next wsSheet

If X = 0 Then

' answer = MsgBox("Connected!", 64, "Connection Dialog")

Else

answer = MsgBox("not Connected!", 64, "Connection Dialog")

End If

End Sub

Public Sub Tester()

Dim rng As Range

Dim LastRow As Long



LastRow = Cells(Rows.Count, "A").End(xlUp).Row



Set rng = Range(LastRow - 1)

rng.Select



End Sub

Sub SelectFirstToLastInColumn()

' Select From the First NonBlank to the Last Nonblank in the Column

Cells(1, 1).Select

Set TopCell = Cells(1, ActiveCell.Column)

Set BottomCell = Cells(Rows.Count, ActiveCell.Column)

If IsEmpty(TopCell) Then Set TopCell = TopCell.End(xlDown)

If IsEmpty(BottomCell) Then Set BottomCell = BottomCell.End(xlUp)

If TopCell.Row = Rows.Count And BottomCell.Row = 1 Then

ActiveCell.Select

Else

Range(TopCell, BottomCell).Select

End If

End Sub





Sub GetChild(ByVal mbrName As Variant)

Call Conn





Dim vt As Variant

Dim cbItems As Variant

Dim i As Integer

Dim pMember As String



vt = EssVGetMemberInfo(ThisWorkbook.Name & "Sheet1", mbrName, EssChildLevel, False)

If IsArray(vt) Then

cbItems = UBound(vt) + 1



For i = 0 To UBound(vt)

Sheet1.Cells(i + 1, 1) = vt(i)

Next

Else

MsgBox ("Return Value = " + Str(vt))

End If



' Select From the First NonBlank to the Last Nonblank in the Column



Cells(1, 1).Select

Set TopCell = Cells(1, ActiveCell.Column)

Set BottomCell = Cells(Rows.Count, ActiveCell.Column)

If IsEmpty(TopCell) Then Set TopCell = TopCell.End(xlDown)

If IsEmpty(BottomCell) Then Set BottomCell = BottomCell.End(xlUp)

If TopCell.Row = Rows.Count And BottomCell.Row = 1 Then

ActiveCell.Select

Else

Range(TopCell, BottomCell).Select

End If



Sheet1.Shapes(2).ControlFormat.ListFillRange = Range(TopCell, BottomCell).Address



'MsgBox (Worksheets(1).Shapes(2).Name)

X = EssVFreeMemberInfo(vt)



End Sub

Sub lstCity_Clear()

For i = 1 To 20

Sheet1.Cells(i, 2) = Null

Next

End Sub

Sub GetBottom(ByVal mbrName As Variant)



Call lstCity_Clear

Dim vt As Variant

Dim cbItems As Variant

Dim i As Integer

Dim pMember As String



vt = EssVGetMemberInfo(ThisWorkbook.Name & "Sheet1", mbrName, EssBottomLevel, False)



If IsArray(vt) Then

cbItems = UBound(vt) + 1

For i = 0 To UBound(vt)

Sheet1.Cells(i + 1, 2) = vt(i)



Next

Else

MsgBox ("Return Value = " + Str(vt))

End If

Cells(1, 2).Select

Set TopCell = Cells(1, ActiveCell.Column)

Set BottomCell = Cells(Rows.Count, ActiveCell.Column)

If IsEmpty(TopCell) Then Set TopCell = TopCell.End(xlDown)

If IsEmpty(BottomCell) Then Set BottomCell = BottomCell.End(xlUp)

If TopCell.Row = Rows.Count And BottomCell.Row = 1 Then

ActiveCell.Select

Else

Range(TopCell, BottomCell).Select

End If


Sheet1.Shapes(3).ControlFormat.ListFillRange = Range(TopCell, BottomCell).Address

X = EssVFreeMemberInfo(vt)

End Sub