Data provider types: Essbase, Financial Management, Planning, Hyperion Enterprise
HypGetOption() retrieves Smart View options that are both default and sheet specific so you do not need separate VBA commands for the two types of options.
See also HypGetGlobalOption.
HypGetOption (vtItem,vtRet,vtSheetName)
ByVal vtItem As Variant
ByRef vtRet As Variant
ByVal vtSheetName As Variant
vtItem: The index or constant that refers to a specific option. See Table 6, Option Constants for HypGetOption and HypSetOption 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.
Returns 0 if successful; otherwise, returns the appropriate error code.
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 6. Option Constants for HypGetOption and HypSetOption
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_19 | 19 | Reserved for future use | |
HSV_20 | 20 | Reserved for future use | |
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_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_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. |