Data source types: Essbase, Planning
HypExecuteCalcScriptEx(vtSheetName [in], vtCubeName [in], vtBRName [in], vtBRType [in], vtbBRHasPrompts [in], vtbBRNeedPageInfo [in], vtRTPNames() [in], vtRTPValues() [in], vtbShowRTPDlg [in], vtbRuleOnForm [in], vtbBRRanSuccessfuly [out], vtCubeName [out], vtBRName [out], vtBRType [out], vtbBRHasPrompts [out], vtbBRNeedPageInfo [out], vtbBRHidePrompts [out], vtRTPNamesUsed [out], vtRTPValuesUsed [out])
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
vtSheetName: For future use. Currently the active sheet is used.
vtCubeName: Cube Name (Plan type incase of Planning) Business Rule is associated with
vtBRName: Business Rule Name of the BR to be run
vtBRType: Business Rule Type for the BR to be run
vtbBRHasPromps: Boolean indicating if the Business Rule has RTPs
vtbNeedPageInfo: Boolean indicating if the Business Rule needs Page Info to be run (Get this info either from HypListCalcScriptsEx or from prior run of HypExecuteCalcScriptEx)
ppRTPNames: Array of RTP Names associated with the Business Rule
ppRTPValues: Array of RTP Values corresponding to the RTP Names
vtbShowBRDlg:Boolean indicating whether to show the Business Rule dialog box and let the user select the Business Rule to run or of automating execution of BR. If this flag is true, all the input parameters related to the BR are ignored. Recommendation: This flag should be true when running the BR for the first time and then using the output paramters to automate the execution of the same BR from second time onwards. In this case, this flag should be false second time
vtbRuleOnForm: Boolean indicating if the Business Rule is associated to the form opened on active sheet
pvtbBRRanSuccessfully: Return boolean value indicating if the last Business Rule ran successfully
pvtCubeNameUsed: Cube name (Plan Types incase of Planning) associated with the last run Business Rule
pvtBRNameUsed: Business Rule Name of the last run Business Rule
pvtBRTypeUsed: Business Rule type of the last run Business Rule
pvtbBRHasPrompts: Boolean indicating if the last run Business Rule has RTPs
pvtbBRNeedPageInfo: Boolean indicating if the last run Business Rule requires Page information
pvtbBRHidePrompts: Boolean indicating if the last run Business Rule has hidden RTPs
pvtRTPNamesUsed: Array of RTP Names used to run last run Business Rule
pvtRTPValuesUsed: Array of RTP Values associated with RTP names used to run last run Business Rule
Declare Function HypListCalcScriptsEx Lib "HsAddin" (ByVal vtSheetName As Variant, ByVal vtbRuleOnForm As Variant, ByRef vtCubeNames As Variant, ByRef vtBRNames As Variant, ByRef vtBRTypes As Variant, ByRef vtBRHasPrompts As Variant, ByRef vtBRNeedsPageInfo As Variant, ByRef vtBRHidePrompts As Variant) As Long
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 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) As Long
Sub TestListAndExecuteCalcScriptsEx()
Dim oRet As Long
Dim oSheetName As String
Dim 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
You can use HypExecuteCalcScriptEx in four modes, depending on whether HypListCalcScriptsEx is called before HypExecuteCalcScriptEx.
If you do NOT call HypListCalcScriptsEx before HypExecuteCalcScriptEx, then the first time you call HypListCalcScriptsEx you should set the vtbShowBRDlg argument to true for the first usage and to false thereafter.
When vtbShowBRDlg argument is true (mode 1):
In arguments: vtSheetName, vtCubeName, vtbRuleOnForm are used. vtBRName, vtBRType, vtbBRHasPrompts, vtbNeedPageInfo, ppRTPNames, ppRTPValues are ignored.
Behavior: The Business Rule dialog box displays all possible rules depending upon the vtbRuleOnForm value. When the user selects, runs and exits the Business Rule dialog box, the details of that Business Rule are filled in the out arguments and returned to the caller.
Out arguments: All out arguments are filled and returned to the caller so that they can be used in subsequent calls.
When vtbShowBRDlg argument is false (mode 2):
In arguments: All in arguments are used.
Behavior: The business rule is run without displaying the Business Rule dialog box ,and the appropriate status is returned to the caller.
Out arguments: All out arguments are left unmodified as nothing needs to be passed on to the caller, who already has all the information to run this particular business rule.
If you DO call HypListCalcScriptsEx before HypExecuteCalcScriptEx, then when HypListCalcScriptsEx is called, users get information about all business rules and runtime prompts (RTP), 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 in 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. (Note: inPlanning, the RTP flag may be true for a business rule when there are no RTPs to be displayed.)
If the cube name, business rule name and business rule type are passed as empty in HypExecuteCalcScriptEx (mode 3), the Business Rule dialog box is displayed and all business rules are shown depending upon vtbRuleOnForm argument. All else is the same as mode 1.
If the cube name, business rule name and business rule type are passed with filled values in HypExecuteCalcScriptEx (mode 4), the Business Rule 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.