HypGetOption
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, Oracle Hyperion Financial Management
Description
HypGetOption() retrieves Oracle Smart View for Office 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 a valid sheet name is not provided, then the default option is used.
Return Value
Returns 0 if successful; otherwise, returns the appropriate error code.
Example
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
Table 13-3 Option Constants for HypGetOption and HypSetOption
| Option | Constant | Data Type | Comment |
|---|---|---|---|
| HSV_ZOOMIN | 1 | Number | Sets zoom in level:
|
| 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
|
| 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:
|
| 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:
|
| HSV_MEMBER_DISPLAY | 16 | Number | Specifies how to display member names in cells:
|
| 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_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:
|
| 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:
|
| 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:
|