Oracle® Hyperion Smart View for Office, Fusion Edition

HypCopyMetaData() performs Export Metadata.

HypCopyMetaData (vtSourceSheetName, vtDestinationSheetName)

ByVal vtSourceSheetName As Variant

ByVal vtDestinationSheetName As Variant

vtSourceSheetName: Name of the source sheet where the Custom Properties should be copied from. (Required)

vtDestinationSheetName: Name of the destination sheet where the Custom Properties should be copied to. (Required)

Returns SS_OK if the function suceeded; otherwise, the appropriate error code.

Public Declare Function HypCopyMetaData Lib "HsAddin.dll" (ByVal vtSourceSheetName As Variant, ByVal vtDestinationSheetName As Variant) As Long

Sub Sample_HypCopyMetaData()
		Dim LRet As Long
		LRet = HypCopyMetaData (“Sheet1”, “Sheet1(2)”)
End Sub	

HypIsCellWritable() checks to see whether a cell is writable.

HypIsCellWritable (vtSheetName [in], vtCellRange [out])

ByVal vtSheetName As Variant

ByVal vtCellRange As Variant

vtSheetName: Name of the worksheet. If this argument is NULL or empty, the default sheet is used.

vtCellRange: Range of the cell (one cell only) whose writability must be checked.

VARIANT_TRUE if the cell is writable, otherwise VARIANT_FALSE.

Public Declare Function HypIsCellWritable Lib "HsAddin.dll" (ByVal vtSheetName As Variant, ByVal vtCellRange As Variant) As Boolean

Sub TestIsCellWritable()

			Dim oRet As Boolean
			Dim oSheetName As String
			Dim oSheetDisp As Worksheet
	
			oSheetName = "Sheet1"
			Set oSheetDisp = Worksheets(oSheetName$)
			oRet = HypIsCellWritable (Sheet1, oSheetDisp.Range("G2"))

End Sub

HypIsSmartViewContentPresent() checks to see whether the sheet contains Smart View content.

HypIsSmartViewContentPresent(vtSheetName [in], pContentType [out])

ByVal vtSheetName As Variant

ByRef vtTypeOfContentsInSheet

vtSheetName: Name of the worksheet. If this argument is NULL or empty, the default sheet is used.

pContentType: Function returns appropriate type of content on the sheet. Possible values are in the enum as defined below.

Enum TYPE_OF_CONTENTS_IN_SHEET
    EMPTY_SHEET
    ADHOC_SHEET
    FORM_SHEET
    INTERACTIVE_REPORT_SHEET
End Enum

VARIANT_TRUE if the worksheet contains Oracle Hyperion Smart View for Office, Fusion Edition content, otherwise VARIANT_FALSE.

Public Declare Function HypIsSmartViewContentPresent Lib "HsAddin.dll" (ByVal vtSheetName As Variant, _
ByRef vtTypeOfContentsInSheet As TYPE_OF_CONTENTS_IN_SHEET) As Boolean

Sub TestIsSVCContentOnSheet()

	Dim oRet As Boolean
	Dim oContentType As TYPE_OF_CONTENTS_IN_SHEET
	Dim oSheetName As String
	Dim oSheetDisp As Worksheet

	oSheetName = "Sheet1"
	Set oSheetDisp = Worksheets(oSheetName$)
	oRet = HypIsSmartViewContentPresent (Sheet1, oContentType)

End Sub

HypGetDimMbrsForDataCell() retrieves the entire set of dimension members for a data cell.

HypGetDimMbrsForDataCell (vtSheetName [in], vtCellRange [in], vtServerName [out], vtAppName [out], vtCubeName [out], vtFormName [out], vtDimensionNames [out], vtMemberNames [out])

ByVal vtSheetName As Variant

ByVal vtCellRange As Variant

ByRef vtServerName As Variant

ByRef vtAppName As Variant

ByRef vtCubeName As Variant

ByRef vtFormName As Variant

ByRef vtDimensionNames As Variant

ByRef vtMemberNames As Variant

vtSheetName: Name of the worksheet. If this argument is NULL or empty, the default sheet is used.

vtCellRange: Range of the cell (one cell only) whose writability must be checked.

pvtServerName: Name of the server the associated connection on the sheet is connected to

pvtApplicationName: Name of the application the associated connection on the sheet is connected to

pvtCubeName: Name of the cube /database (Plan Type in Planning) the associated connection on the sheet is connected to

pvtFormName: Name of the form the associated connection on the sheet is connected to (in ad hoc grids, this is returned as empty string)

pvtDimensionNames: Array of dimension names

pvtMemberNames: Array of member names

Returns SS_OK if the function suceeded; otherwise, the appropriate error code.

Public Declare Function HypGetDimMbrsForDataCell Lib "HsAddin.dll" (ByVal vtSheetName As Variant, ByVal vtCellRange As Variant, _ ByRef vtServerName As Variant, ByRef vtAppName As Variant, _ ByRef vtCubeName As Variant, ByRef vtFormName As Variant, _ ByRef vtDimensionNames As Variant, ByRef vtMemberNames As Variant) As Long

Sub TestGetDimMbrsForDataCell()

Dim oRet As Long
Dim oSheetName As String
Dim oSheetDisp As Worksheet
Dim vtDimNames As Variant
Dim vtMbrNames As Variant
Dim vtServerName As Variant
Dim vtAppName As Variant
Dim vtCubeName As Variant
Dim vtFormName As Variant
Dim lNumDims As Long
Dim lNumMbrs As Long
Dim sPrintMsg As String

oSheetName = "Sheet1"
Set oSheetDisp = Worksheets(oSheetName$)
oRet = HypGetDimMbrsForDataCell("", oSheetDisp.Range("B2"), vtServerName, vtAppName, vtCubeName, vtFormName, vtDimNames, vtMbrNames)

If (oRet = SS_OK) Then
    If IsArray(vtDimNames) Then
        lNumDims = UBound(vtDimNames) - LBound(vtDimNames) + 1
    End If

    If IsArray(vtMbrNames) Then
        lNumMbrs = UBound(vtMbrNames) - LBound(vtMbrNames) + 1
    End If

    sPrintMsg = "Number of Dimensions = " & lNumDims & "  Number of Members = " & lNumMbrs & " Cube Name - " & vtCubeName
    MsgBox (sPrintMsg)
End If

End Sub

HypCaptureFormatting() applies grid formatting to cells created by zooming in.

HypCaptureFormatting (vtSheetName [in], vtRange [in])

ByVal vtSheetName As Variant

ByVal vtSelectionRange As Variant

vtSheetName: Name of the worksheet. If this argument is NULL or empty, the default sheet is used.

vtRange: Range of the cell(s) for which formatting needs to be captured. (Multiple ranges are supported)

Returns SS_OK if the function suceeded; otherwise, the appropriate error code.

Public Declare Function HypCaptureFormatting Lib "HsAddin.dll" (ByVal vtSheetName As Variant, ByVal vtSelectionRange As Variant) As Long

Sub TestCaptureFormatting()

		Dim oRet As Long
		Dim oSheetName As String
		Dim oSheetDisp As Worksheet

		oSheetName = "Sheet1"
		Set oSheetDisp = Worksheets(oSheetName$)
		oRet = HypCaptureFormatting ("", oSheetDisp.Range("B2"))

		MsgBox (oRet)

End Sub

HypRemoveCapturedFormats() removes captured formats.

HypRemoveCapturedFormats (vtSheetName [in], vtRemoveAllCapturedFormats [in],vtSelectionRange [in])

ByVal vtSheetName As Variant

ByVal vtbRemoveAllCapturedFormats As Variant

ByVal vtSelectionRange As Variant

vtSheetName: Name of the worksheet. If this argument is NULL or empty, the default sheet is used.

vtbRemoveAllCapturedFormats: Boolean to indicate whether all captured formats on the grid should be deleted. (If this parameter is true, the next parameter value is not used, so users can pass NULL for vtSelectionRange.)

vtSelectionRange: Range of the cell(s) for which formatting needs to be captured. (Multiple ranges are supported)

Returns SS_OK if the function suceeded; otherwise, the appropriate error code.

Public Declare Function HypRemoveCapturedFormats Lib "HsAddin.dll" (ByVal vtSheetName As Variant, ByVal vtbRemoveAllCapturedFormats As Variant, ByVal vtSelectionRange As Variant) As Long

Sub TestRemoveCaptureFormatting()

		Dim oRet As Long
		Dim oSheetName As String
		Dim oSheetDisp As Worksheet

		oSheetName = "Sheet1"
		Set oSheetDisp = Worksheets(oSheetName$)
		'oRet = HypRemoveCapturedFormats("", False, oSheetDisp.Range("B2"))
		oRet = HypRemoveCapturedFormats("", True, Null)
		MsgBox (oRet)

End Sub

HypListCalcScriptsEx() lists all business rules.

HypListCalcScriptsEx (vtSheetName [in], vtbRuleOnForm [in], pvtArCubeNames [out], pvtArBRNames [out], pvtArBRTypes [out], pvtArBRHasPrompts [out], pvtArBRNeedPageInfo [out], pvtArBRHidePrompts [out])

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

vtSheetName: Name of the worksheet. If this argument is NULL or empty, the default sheet is used.

vtbRuleOnForm: Boolean to indicate whether the user wants to list business rules associated only with the form opened on the sheet. If this argument is false, all the business rules associated with the application will be returned.

pvtArCubeNames: Array of cube names (Plan Types in Planning) associated with the Business rules

pvtArBRNames: Array of Business Rule Names

pvtArBRTypes: Array of Business Rule Types

pvtArBRHasPrompts: – Array of booleans indicating whether the Business Rule has Run Time Prompts

pvtArBRNeedPageInfo: Array of booleans indicating whether the Business Rule needs Page Information on the sheet to be run

pvtArBRHidePrompts: Array of booleans indicating whether the RTPs for this Business Rule are hidden

Returns SS_OK if the function suceeded; otherwise, the appropriate error code.

HypExecuteCalcScriptEx() executes the selected business rule.

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: Name of the worksheet. If this argument is NULL or empty, the default 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

Returns SS_OK if the function suceeded; otherwise, the appropriate error code.

Declare Function HypListCalcScriptsEx Lib "HsAddin.dll" (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.dll" (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.

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.)

HypGetCellRangeForMbrCombination() retrieves the cell range for the selected combination of members.

HypGetCellRangeForMbrCombination (vtSheetName [in], ppvtDimNames [in], ppvtMbrNames [in], pvtCellIntersectionRange [out])

By Val vtSheetName As Variant

ByRef vtDimNames As Variant

ByRef vtMbrNames As Variant

ByRef vtCellIntersectionRange As Variant

vtSheetName: Name of the worksheet. If this argument is NULL or empty, the default sheet is used.

ppvtDimNames: Array of dimension names

ppvtMbrNames: Array of member names corresponding to the dimensions (in the same order)

pvtCellIntersectionRange: Range of the cell(s) on the grid

Returns SS_OK if the function suceeded; otherwise, the appropriate error code.

Public Declare Function HypGetCellRangeForMbrCombination Lib "HsAddin.dll" (ByVal vtSheetName As Variant, ByRef vtDimNames() As Variant, ByRef vtMbrNames() As Variant, ByRef vtCellIntersectionRange As Variant) As Long

Sub GetCellRangeForMbrCombination()

		Dim oRet As Long
		Dim oSheetName As String
		Dim oSheetDisp As Worksheet
		Dim vtDimNames(3) As Variant
		Dim vtMbrNames(3) As Variant
		Dim vtReturnCellRange As Variant
		Dim oRange As Range
	
		'oSheetName = "Sheet1"
		'Set oSheetDisp = Worksheets(oSheetName$)

		vtDimNames(0) = "Measures"
		vtDimNames(1) = "Market"
		vtDimNames(2) = "Year"
		vtDimNames(3) = "Product"
		'vtDimNames(4) = ""

		vtMbrNames(0) = "Sales"
		vtMbrNames(1) = "New York"
		vtMbrNames(2) = "Year"
		vtMbrNames(3) = " Product"
		'vtMbrNames(4) = ""

oRet = HypGetCellRangeForMbrCombination ("", vtDimNames, vtMbrNames, vtReturnCellRange)

If (oRet = 0) Then
    Set oRange = vtReturnCellRange
End If

HypIsDataModified() checks to see whether any data cells have been modified but not yet submitted.

HypIsDataModified (vtSheetName [in])

By Val vtSheetName As Variant

vtSheetName: Name of the worksheet. If this argument is NULL or empty, the default sheet is used.

VARIANT_TRUE if the sheet contains any data cells that have been updated and not yet submitted, otherwise VARIANT_FALSE.

Public Declare Function HypIsDataModified Lib "HsAddin.dll" (ByVal vtSheetName As Variant) As Boolean

Sub TestIsSheetDirty()

		Dim oRet As Boolean

		oRet = HypIsDataModified("")
		MsgBox (oRet)

End Sub

HypIsFreeForm() checks to see whether the worksheet is in free form.

HypIsFreeForm (vtSheetName [in])

By Val vtSheetName As Variant

vtSheetName: Name of the worksheet. If this argument is NULL or empty, the default sheet is used.

VARIANT_TRUE if the cell is in free form state, i.e., either member cells or comment cells have been modified and the sheet has not been refreshed, otherwise VARIANT_FALSE.

Public Declare Function HypIsFreeForm Lib "HsAddin.dll" (ByVal vtSheetName As Variant) As BooleanSub TestIsSheetFreeForm()

Sub HypIsFreeForm() 
		Dim oRet As Boolean

		oRet = HypIsFreeForm("")
		MsgBox (oRet)

End Sub