HypExecuteCalcScriptEx
Describes the Oracle Smart View for Office VBA function, HypExecuteCalcScriptEx.
Cloud data provider types: Oracle Analytics Cloud - Essbase, Planning, Planning Modules, Financial Consolidation and Close, Tax Reporting
On-premises data provider types: Oracle Essbase, Oracle Hyperion Planning
Description
HypExecuteCalcScriptEx() executes the selected business rule.
Syntax
HypExecuteCalcScriptEx(vtSheetName, vtCubeName, vtBRName, vtBRType, vtbBRHasPrompts, vtbBRNeedPageInfo, vtRTPNames(), vtRTPValues(), vtbShowRTPDlg, vtbRuleOnForm, vtbBRRanSuccessfully, vtCubeName, vtBRName, vtBRType, vtbBRHasPrompts, vtbBRNeedPageInfo, vtbBRHidePrompts, vtRTPNamesUsed, vtRTPValuesUsed )
ByVal vtSheetName As Variant
ByVal vtCubeName As Variant
ByVal vtBRName As Variant
ByVal vtBRType As Variant
ByVal vtbBRHasPrompts As Variant
ByVal vtbBRNeedPageInfo As Variant
ByRef vtRTPNames() As Variant
ByRef vtRTPValues() As Variant
ByVal vtbShowRTPDlg As Variant
ByVal vtbRuleOnForm As Variant
ByRef vtbBRRanSuccessfuly As Variant
ByRef vtCubeName As Variant
ByRef vtBRName As Variant
ByRef vtBRType As Variant
ByRef vtbBRHasPrompts As Variant
ByRef vtbBRNeedPageInfo As Variant
ByRef vtbBRHidePrompts As Variant
ByRef vtRTPNamesUsed As Variant
ByRef vtRTPValuesUsed As Variant
Parameters
vtSheetName: Input parameter; the name of worksheet on which to run the function. If vtSheetName is Null
or Empty
, the active worksheet is used.
vtCubeName: Input parameter; the cube name (plan types in Oracle Hyperion Planning) associated with the business rule.
vtBRName: Input parameter; the name of the business rule to be run.
vtBRType: Input parameter; the type of business rule to be run. Valid values are "graphical", "ecs", "sequence", and "native".
vtbBRHasPrompts: Input parameter; the Boolean that indicates whether the business rule has RTPs.
vtbNeedPageInfo: Input parameter; the Boolean that indicates whether the business rule requires Page Information to be run (this information is either from HypListCalcScriptsEx or from a prior run of HypExecuteCalcScriptEx).
vtRTPNames: Input parameter; the array of RTP names associated with the business rule.
vtRTPValues: Input parameter; the array of RTP values corresponding to the RTP names.
vtbShowBRDlg: Input parameter; the Boolean that indicates whether to display the Business Rules dialog to let users select the business rule (True) or to execute the business rule automatically (False). If set to True, all input parameters related to the business rule are ignored. Recommendation: Set to True when running the business rule for the first time, and thereafter set to false to automate the execution of the same business rule.
vtbRuleOnForm: Input parameter; the Boolean that indicates whether the business rule is to be associated to the form open on active sheet.
vtbBRRanSuccessfully: Output parameter; the Boolean value that indicates whether the last business rule ran successfully.
vtCubeName: Output parameter; the cube name (plan types in Oracle Hyperion Planning) associated with the last run business rule
vtBRName: Output parameter; the name of the last run business rule.
vtBRType: Output parameter; the type of the last run business rule.
vtbBRHasPrompts: Output parameter; the Boolean that indicates whether the last run business rule has RTPs.
vtbBRNeedPageInfo: Output parameter; the Boolean that indicates whether the last run business rule requires Page information to be run.
vtbBRHidePrompts: Output parameter; the Boolean that indicates whether the last run business rule has hidden RTPs.
vtRTPNames: Output parameter; the array of RTP names used to run last run business rule.
vtRTPValues: Output parameter; the array of RTP values associated with RTP names used to run last run business rule.
Return Value
Returns 0 if successful; otherwise, the appropriate error code.
Example
Public Declare Function HypExecuteCalcScriptEx Lib "HsAddin" (ByVal vtSheetName As Variant,ByVal vtCubeName As Variant,ByVal vtBRName As Variant, ByVal vtBRType As Variant, ByVal vtbBRHasPrompts As Variant, ByVal vtbBRNeedPageInfo As Variant,ByRef vtRTPNames() As Variant,ByRef vtRTPValues() As Variant, ByVal vtbShowRTPDlg As Variant, ByVal vtbRuleOnForm As Variant, ByRef vtBRRanSuccessfully As Variant,ByRef vtCubeName As Variant,ByRef vtBRName As Variant, ByRef vtBRType As Variant, ByRef vtbBRHasPrompts As Variant, ByRef vtbBRNeedPageInfo As Variant, ByRef vtbBRHidePrompts As Variant, ByRef vtRTPNamesUsed As Variant, ByRef vtRTPValuesUsed As Variant) As Long
Sub Example_HypExecuteCalcScriptEx()
Dim oRet As Long
Dim oSheetName As StringDim oSheet As Worksheet
Dim vtCubeNames As Variant
Dim vtBRNames As Variant
Dim vtBRTypes As Variant
Dim vtBRHasPrompts As Variant
Dim vtBRNeedsPageInfo As Variant
Dim vtBRHidePrompts As Variant
Dim sAllCalcs As String
Dim sCalcName As String
Dim bNeedPageInfo As Variant
Dim vtInRTPNames() As Variant
Dim vtInRTPValues() As Variant
Dim vtOutRTPNames As Variant
Dim vtOutRTPValues As Variant
Dim vtbBRRanSuccessfully As Variant
Dim vtbBRRanSuccessfully2 As Variant
Dim vtOutCubeName As Variant
Dim vtOutBRName As Variant
Dim vtOutBRType As Variant
Dim bBRHasPrompts As Variant
Dim bBRNeedPageInfo As Variant
Dim bBRHidePrompts As Variant
Dim bShowDlg As Variant
Dim bRuleOnForm As Variant
'Set oSheet = ActiveSheet
'oSheetName = oSheet.Name
oSheetName = "Sheet3"
oRet = HypListCalcScriptsEx (oSheetName, False, vtCubeNames, vtBRNames, vtBRTypes, vtBRHasPrompts, vtBRNeedsPageInfo, vtBRHidePrompts)
If (oRet = 0) Then
If IsArray(vtBRNames) Then
lNumMbrs = (UBound(vtBRNames) - LBound(vtBRNames) + 1)
End If
sPrintMsg = "Number of Calc Scripts = " & lNumMbrs
MsgBox (sPrintMsg)
'Start Executing the Calc Script
bShowDlg = True
bRuleOnForm = False
iScript = 1
oRet = HypExecuteCalcScriptEx (oSheetName, vtCubeNames(iScript), vtBRNames(iScript), vtBRTypes(iScript), vtBRHasPrompts(iScript), vtBRNeedsPageInfo(iScript), vtInRTPNames, vtInRTPValues, bShowDlg, bRuleOnForm, vtbBRRanSuccessfully, vtOutCubeName, vtOutBRName, vtOutBRType,bBRHasPrompts, bBRNeedPageInfo, bBRHidePrompts, vtOutRTPNames, vtOutRTPValues)
If (oRet = 0) Then
MsgBox ("Last BR ran successfully - " & vtbBRRanSuccessfully)
If (vtbBRRanSuccessfully = True) Then
bShowDlg = False
bRuleOnForm = False
If IsArray(vtOutRTPNames) And IsArray(vtOutRTPValues) Then
lNumRTPNames = (UBound(vtOutRTPNames) - LBound(vtOutRTPNames) + 1)
lNumRTPVals = (UBound(vtOutRTPValues) - LBound(vtOutRTPValues) + 1)
End If
If (lNumRTPNames > 0) Then
ReDim vtInRTPNames(lNumRTPNames - 1) As Variant
ReDim vtInRTPValues(lNumRTPNames - 1) As Variant
For iRTPs = 0 To lNumRTPNames - 1
sBRName = vtOutRTPNames(iRTPs)
sBRVal = vtOutRTPValues(iRTPs)
vtInRTPNames(iRTPs) = sBRName
vtInRTPValues(iRTPs) = sBRVal
Next iRTPs
End If
oRet = HypExecuteCalcScriptEx (oSheetName, vtOutCubeName, vtOutBRName, vtOutBRType, bBRHasPrompts, bBRNeedPageInfo, vtInRTPNames, vtInRTPValues, bShowDlg, bRuleOnForm, vtbBRRanSuccessfully2, vtOutCubeName, vtOutBRName, vtOutBRType, bBRHasPrompts, bBRNeedPageInfo, bBRHidePrompts, vtOutRTPNames, vtOutRTPValues)
MsgBox ("Automated BR ran successfully - " & vtbBRRanSuccessfully2)
End If
Else
sPrintMsg = "Error - " & oRet
MsgBox (sPrintMsg)
End If
Else
sPrintMsg = "Error - " & oRet
MsgBox (sPrintMsg)
End If
End Sub
Usage
You can use HypExecuteCalcScriptEx in four modes, depending on whether HypListCalcScriptsEx is called before HypExecuteCalcScriptEx.
Not Calling HypListCalcScriptsEx Before HypExecuteCalcScriptEx
If you do not call HypListCalcScriptsEx before HypExecuteCalcScriptEx, then the first time you call HypListCalcScriptsEx you should set vtbShowBRDlg to True for the first usage and to False thereafter.
-
Mode 1: When vtbShowBRDlg is True:
-
Input Arguments: vtSheetName, vtCubeName, vtbRuleOnForm are used. vtBRName, vtBRType, vtbBRHasPrompts, vtbNeedPageInfo, ppRTPNames, ppRTPValues are ignored.
-
Behavior: The Business Rules dialog box displays all possible rules depending upon the vtbRuleOnForm value. When the user, runs the selected business rule and exits the Business Rules dialog box, the details of that business rule are filled in the out arguments and returned to the caller.
-
Output arguments: All out arguments are filled and returned to the caller so that they can be used in subsequent calls.
-
-
Mode 2: When vtbShowBRDlg argument is False:
-
Input arguments: All input arguments are used.
-
Behavior: The Business Rules dialog box is not displayed. The business rule is run automatically, and the appropriate status is returned to the caller.
-
Output arguments: All output arguments are left unmodified, because nothing needs to be passed on to the caller, who already has all the information to run this particular business rule.
-
Calling HypListCalcScriptsEx Before HypExecuteCalcScriptEx
If you do call HypListCalcScriptsEx before HypExecuteCalcScriptEx, then when HypListCalcScriptsEx is called, users get information about all business rules and runtime prompts, if any.
If a user runs a business rule that has no RTP, HypExecuteCalcScriptEx can be called with vtbShowBRDlg argument as False and provides all other information as the input arguments.
If a user runs a business rule that has an RTP, HypExecuteCalcScriptEx must be called with vtbShowBRDlg as True so that the business rule and its RTPs can be displayed and the user can select the RTP values to run the business rule. (In Oracle Hyperion Planning and EPM Cloud, the RTP flag may be True for a business rule when there are no RTPs to be displayed.)
-
Mode 3: If the cube name, business rule name and business rule type are passed as empty in HypExecuteCalcScriptEx, the Business Rules dialog box is displayed and all business rules are shown, depending upon vtbRuleOnForm argument. All else is the same as mode 1.
-
Mode 4: If the cube name, business rule name and business rule type are passed with filled values in HypExecuteCalcScriptEx, the Business Rules dialog box is displayed and only the passed business rule (business rule name for the provided cube name) is displayed along with its RTPs. All else is the same as mode 1.