HypGetOption

Describes the Oracle Smart View for Office VBA function, HypGetOption.

Cloud data provider types: Oracle Essbase, Planning, Planning Modules, Financial Consolidation and Close, Tax Reporting

On-premises data provider types: Oracle Essbase, Oracle Hyperion Planning, Oracle Hyperion Financial Management

Description

HypGetOption() retrieves Smart View options that are both global (default) and sheet specific so that you do not need separate VBA commands for the two option types.

See also HypGetGlobalOption and HypGetSheetOption.

Syntax

HypGetOption (vtItem,vtRet,vtSheetName)

ByVal vtItem As Variant

ByRef vtRet As Variant

ByVal vtSheetName As Variant

Parameters

vtItem: The index or constant that refers to a specific option. See Table 13-3 for descriptions of the options. Also, a list of available options is shown in smartview.bas under "Enumeration of options index to be used for HypGetOption/HypSetOption."

vtRet: The output variable

vtSheetName: The sheet name of a sheet level option. If no sheet name is provided, then the option is set as default.

Return Value

Returns 0 if successful; otherwise, returns the appropriate error code.

Examples

Public Declare Function HypGetOption Lib "HsAddin" (ByVal vtItem As Variant, ByRef vtRet As Variant, ByVal vtSheetName As Variant) As Long

Sub Example_HypGetOption()

sts = HypGetOption(HSV_ZOOMIN, Var, "Sheet2") 'get zoom in option for sheet2
sts = HypGetOption(1, Var, "") 'get default zoom in option

End Sub

Sub getFormatOptions()

HypGetOption HSV_USE_EXCEL_INDENTATION, Var, "Sheet1"
HypGetOption HSV_NEGATIVE_NUMBERFORMAT, Var, ""
HypGetOption HSV_USE_CUSTOM_NUMBERFORMAT, Var, ""
HypGetOption HSV_CUSTOM_NUMBERFORMAT, Var, ""
HypGetOption HSV_SHOW_DISTINCT_PAGE_MEMBERNAME, Var, ""
HypGetOption HSV_FREEZE_ROWCOL_HEADERS, Var, ""

End Sub

Sub setNegativeNum()

sts = HypSetOption(HSV_NEGATIVE_NUMBERFORMAT, 2, "")

End Sub
Sub setCustomformat()

HypSetOption HSV_USE_CUSTOM_NUMBERFORMAT, True, ""
HypSetOption HSV_CUSTOM_NUMBERFORMAT, "#,##0_);[Blue](#,##0)", ""

End Sub

Table 13-3 Option Constants for HypGetOption and HypSetOption

Option Constant Data Type Comment
HSV_ZOOMIN 1 Number Sets zoom in level:

  • 0 = Next level

  • 1 = All levels

  • 2 = Bottom level

  • 3 = Sibling level

  • 4 = Same level

  • 5 = Same generation

  • 6 = Formulas
HSV_INCLUDE_SELECTION 2 Boolean Selects the Include Selections check box
HSV_WITHIN_SELECTEDGROUP 3 Boolean Selects the Within Selected Group check box
HSV_REMOVE_UNSELECTEDGROUP 4 Boolean Selects the Remove Unselected Groups check box
HSV_INDENTATION 5 Number Selects an Indentation option

  • 0 = No indentation

  • 1 = Indent sub items

  • 2 = Indent totals
HSV_SUPPRESSROWS_MISSING 6 Boolean Suppresses rows that contain no data or are missing data
HSV_SUPPRESSROWS_ZEROS 7 Boolean Suppresses rows that contain only zeroes
HSV_SUPPRESSROWS_UNDERSCORE 8 Boolean Suppresses rows that contain underscore characters in member names
HSV_SUPPRESSROWS_NOACCESS 9 Boolean Suppress rows that contain data that the user does not have the security access to view
HSV_SUPPRESSROWS_REPEATEDMEMBERS 10 Boolean Suppresses rows that contain repeated member names, regardless of grid orientation.
HSV_SUPPRESSROWS_INVALID 11 Boolean Suppresses rows that contain only invalid values
HSV_ANCESTOR_POSITION 12 Number Specifies an ancestor position in hierarchies:

  • 0 = Top

  • 1 = Bottom
HSV_MISSING_LABEL 13 Text Displays #Missing, #Numeric Zero, or the text of your choice in data cells that contain missing data.
HSV_NOACCESS_LABEL 14 Text Displays #NoAccess, #Numeric Zero, or the text of your choice in data cells that the user does not have permission to view.
HSV_CELL_STATUS 15 Number As an alternative to displaying actual data, displays the calculation or process status of the cells:

  • 0 = Data

  • 1 = Calculation Status

  • 2 = Process Management
HSV_MEMBER_DISPLAY 16 Number Specifies how to display member names in cells:

  • 0 = Name Only

  • 1 = Name and Description

  • 2 = Description only
HSV_INVALID_LABEL 17 Text Displays #Invalid, #Numeric Zero, or the text of your choice in data cells that contain invalid data.
HSV_SUBMITZERO 18 Boolean If you specified #NumericZero for the HSV_MISSING_LABEL, HSV_NOACCESS_LABEL, or SV_INVALID_LABEL options, allows you to submit zeroes to the database.
HSV_MOVEESSBASEMEMBERFORMULAONZOOM 19 Boolean When set to True, moves member formulas on zoom in and zoom out.

When enabled, this behavior can impact performance during zoom in and zoom out. Therefore, the default setting is False.

This option becomes irrelevant when the grid contains any data or non-data formulas, or a zoom-in is performed in a free-form grid, in which case, the member formula will move by default.
HSV_PRESERVE_ESSBASECOMMENT_UNKNOWNMEMBERS 20 Boolean Preserves Essbase comments. If set to false, an "unknown member" error message from Essbase is displayed.
HSV_PRESERVE_FORMULA_COMMENT 21 Boolean Preserves formulas and comments on the grid during queries.
HSV_22 22   Reserved for future use
HSV_FORMULA_FILL 23 Boolean Propagates formulas associated with member cells to the members retrieved as a result of zooming in.

If HSV_PRESERVE_FORMULA_COMMENT and HSV_EXCEL_FORMATTING are both enabled, propagates cell formatting to the members retrieved as a result of zooming in.

Applies to formulas in both member and data cells.

HSV_PRESERVE_FORMULA_ONPOVCHANGE 24 Boolean Specific to form sheets. Preserves formulas in cells when user refreshes or makes changes to the POV. When set to False, any formulas in the sheet are lost.
HSV_EXCEL_FORMATTING 30 Boolean Selects the Excel formatting check box
HSV_RETAIN_NUMERIC_FORMATTING 31 Boolean When the user drills down in dimensions, uses the scale specified in HSV_SCALE and/or number of decimal places from HSV_DECIMALPLACES for data.
HSV_THOUSAND_SEPARATOR 32 Boolean Uses a comma or other thousands separator in numerical data. Do not use # or $ as the thousands separator in Excel International Options.
HSV_NAVIGATE_WITHOUTDATA 33 Boolean Enables the speeding up of operations such as Pivot, Zoom, Keep Only, and Remove Only by preventing the calculation of source data while you are navigating. When you are ready to retrieve data, disable Navigate without Data.
HSV_ENABLE_FORMATSTRING 34 Boolean Essbase-specific.

Essbase provides a format string to be associated with different data types.

Once enabled, shows user specific text instead of numbers.
HSV_ENHANCED_COMMENT_HANDLING 35 Boolean Enables review and correction of comments and member names in ad hoc grids that contain comments.
HSV_ADJUSTCOLUMNWIDTH 36 Boolean Adjusts column widths to fit cell contents automatically.
HSV_DECIMALPLACES 37 Number Specifies the number of decimal places to display.
HSV_SCALE 38 Number Specifies the scaling of numeric data, which is displayed based on the scale selected.
HSV_MOVEFORMATS_ON_ADHOC 39 Boolean Copies parent cell formatting to zoomed in cells and retains this formatting even if the cell location changes after an operation.
HSV_DISPLAY_INVALIDDATA 40 Boolean Displays invalid data.
HSV_SUPPRESSCOLUMNS_MISSING 41 Boolean Suppresses columns that contain cells for which no data exists in the database (no data is not the same as zero. Zero is a data value.)
HSV_SUPPRESSCOLUMNS_ZEROS 42 Boolean Suppresses columns that contain only zeroes.
HSV_SUPPRESSCOLUMNS_NOACCESS 43 Boolean Suppresses columns that contain data that the user does not have the security access to view.
HSV_SUPPRESS_MISSINGBLOCKS 44 Boolean Suppresses blocks of cells for which no data exists in the database.
HSV_REPEATMEMBERS_IN_FORMS 45 Boolean Facilitates the readability of forms by allowing member names to appear on each row of data.
HSV_USE_EXCEL_INDENTATION 46 Boolean Preserves native Excel formatting used for indentation in ad hoc grids.

Works in conjunction with HSV_INDENTATION. When HSV_INDENTATION is set to 1 (Subitems) or 2 (Totals), the blank spaces used for indentation may create issues for some users. By enabling HSV_USE_EXCEL_INDENTATION, the indentation for subtotals and totals is handled correctly.

Works with Oracle Fusion Cloud Enterprise Performance Management 21.02+ Standard mode applications only.

HSV_SHOW_DISTINCT_PAGE_MEMBERNAME 47 Boolean Displays distinct page member names or fully qualified names, instead of descriptions, in the Page list for forms.
HSV_FREEZE_ROWCOL_HEADERS 48 Boolean Freezes row and column headers in all forms and ad hoc grids dynamically, irrespective of their size and layout.

Once enabled, if this is option is then disabled, the freeze from the rows and columns is removed, so that users can freeze them manually as required.
HSV_NEGATIVE_NUMBERFORMAT 49 Number

Specifies the format in which negative numbers must be displayed. Valid values are:

0—Use negative sign (default)

1—Use parentheses to show negative number

2—Use Red color

3—Use parentheses and red color
HSV_USE_CUSTOM_NUMBERFORMAT 50 Boolean Enables custom number format.
HSV_CUSTOM_NUMBERFORMAT 51 Text A custom number format string. User is responsible for setting the proper format per Excel standards.
HSV_DOUBLECLICK_FOR_ADHOC 101 Boolean Specifies that double-clicking retrieves the default grid in a blank worksheet and thereafter zooms in or out on the cell contents.
HSV_UNDO_ENABLE 102 Boolean Enables and disables Undo.

Specify the number undo operations allowed with the HSV_NUMBER_OF_UNDO_ACTION parameter.
HSV_103 103   Reserved for future use.
HSV_LOGMESSAGE_DISPLAY 104 Number Specifies message display level setting:

  • 0 = Information

  • 1 = Warnings

  • 2 = Errors

  • 3 = None

  • 4 = Extended info
HSV_ROUTE_LOGMESSAGE_TO_FILE 105 Boolean Enables and disables the Route Messages to File check box.
HSV_CLEAR_LOG_ON_NEXTLAUNCH 106 Boolean Clears the log file starting with the next log message generation, which will be seen after Excel is closed.
HSV_REDUCE_EXCEL_FILESIZE 107 Boolean Should always be enabled except in the following cases, when it should not be used:

  • You send an Excel workbook to users on Smart View releases earlier than 9.3.1.6 or to users on Microsoft Office regardless of Smart View release. In these workbooks:

    • Grids that contain functions must be refreshed before data can be displayed.

    • In ad hoc mode, POV settings are lost; the behavior is similar to that of a fresh ad hoc grid.

  • You open a workbook sent from users on Smart View release earlier than 9.3.1.6 or on Microsoft Office regardless of Smart View release

HSV_ENABLE_RIBBON_CONTEXT 108 Boolean Displays the active data provider ribbon automatically after you use a button on the Smart View ribbon.
HSV_DISPLAY_HOMEPANEL_ONSTARTUP 109 Boolean Enables and disables the Display on Startup check box on the Smart View Home panel.

When enabled, shows the Smart View Home Panel when the Panel icon is selected in the Smart View ribbon.

When disabled, the last opened panel is shown.
HSV_SHOW_COMMENTDIALOG_ON_REFRESH 110 Boolean When enabled, if the grid has comments, the comment editor is displayed to users upon refresh.

When disabled, users can launch the comment editor from the Smart View ribbon.
HSV_NUMBER_OF_UNDO_ACTION 111 Number The number of Undo and Redo actions permitted on an operation (0 through 100).

Works in conjunction with the HSV_UNDO_ENABLE parameter.
HSV_NUMBER_OF_MRU_ITEMS 112 Number The number, 15 or fewer, of your most recently used connections to be displayed on Smart View Home and the Open menu on the Smart View ribbon.
HSV_ROUTE_LOGMESSAGE_FILE_LOCATION 113 Text Saves log messages in a file.
HSV_DISABLE_SMARTVIEW_IN_OUTLOOK 114 Boolean Disables Smart View in Outlook if you do not want to use Smart View task lists in Outlook.
HSV_DISPLAY_SMARTVIEW_SHORTCUT_MENU_ONLY 115 Boolean Displays only Smart View menu items on shortcut menus. Otherwise, shortcut menus display both Excel and Smart View items.
HSV_DISPLAY_DRILL_THROUGH_REPORT_TOOLTIP 116 Boolean Displays by default lists of available drill-through reports for cells whenever you mouse over them.
HSV_SHOW_PROGRESSINFORMATION 117 Boolean Specifies that the Smart View Progress status bar will appear when an operation begins after the number of seconds defined in HSV_PROGRESSINFO_TIMEDELAY.
HSV_PROGRESSINFO_TIMEDELAY 118 Number The time, in seconds, after which the Smart View Progress status bar appears when an operation begins.
HSV_ENABLE_PROFILING 119 Boolean Creates extended Info log entries and most function calls. Creates XML files for each Office application with active Smart View. Intended for debugging. Severely impacts performance.
HSV_REFRESH_SELECTED_DEPENDENT_FUNCTIONS 121 Boolean Executes dependent functions on the same sheet before executing the selected functions.
HSV_IMPROVE_METADATASTORAGE 122 Boolean Allows for more efficient storage of internal data structures.

This option pertains to interoperability between different versions of Smart View.

When this option is set to True, Smart View maintains two copies of metadata for compatibility purpose, which may result in slower overall performance.

If all users in your organization are on Smart View 9.3.1.6 or higher, then this option should always be set to True.

Set this option to False in the following situations:

  • You send an Excel workbook to users on Smart View releases earlier than 9.3.1.6, or to users on Microsoft Office 2002 and earlier, regardless of Smart View release

  • You open a workbook sent from users on Smart View releases earlier than 9.3.1.6, or from users on Microsoft Office 2002 and earlier, regardless of Smart View release