HypGetOption

Data provider types: Essbase, Financial Management, Planning, Hyperion Enterprise

Description

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.

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

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 6. Option Constants for HypGetOption and HypSetOption

 ConstantData TypeComment
HSV_ZOOMIN1NumberSets 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_SELECTION2BooleanSelects the Include Selections check box
HSV_WITHIN_SELECTEDGROUP3BooleanSelects the Within Selected Group check box
HSV_REMOVE_UNSELECTEDGROUP4BooleanSelects the Remove Unselected Groups check box
HSV_INDENTATION5NumberSelects an Indentation option
  • 0 = No indentation

  • 1 = Indent sub items

  • 2 = Indent totals

HSV_SUPPRESSROWS_MISSING6BooleanSuppresses rows that contain no data or are missing data
HSV_SUPPRESSROWS_ZEROS7BooleanSuppresses rows that contain only zeroes
HSV_SUPPRESSROWS_UNDERSCORE8BooleanSuppresses rows that contain underscore characters in member names
HSV_SUPPRESSROWS_NOACCESS9BooleanSuppress rows that contain data that the user does not have the security access to view
HSV_SUPPRESSROWS_REPEATEDMEMBERS10BooleanSuppresses rows that contain repeated member names, regardless of grid orientation.
HSV_SUPPRESSROWS_INVALID11BooleanSuppresses rows that contain only invalid values
HSV_ANCESTOR_POSITION12NumberSpecifies an ancestor position in hierarchies:
  • 0 = Top

  • 1 = Bottom

HSV_MISSING_LABEL13TextDisplays #Missing, #Numeric Zero, or the text of your choice in data cells that contain missing data.
HSV_NOACCESS_LABEL14TextDisplays #NoAccess, #Numeric Zero, or the text of your choice in data cells that the user does not have permission to view.
HSV_CELL_STATUS15NumberAs 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_DISPLAY16NumberSpecifies how to display member names in cells:
  • 0 = Name Only

  • 1 = Name and Description

  • 2 = Description only

HSV_INVALID_LABEL17TextDisplays #Invalid, #Numeric Zero, or the text of your choice in data cells that contain invalid data.
HSV_SUBMITZERO18BooleanIf 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_1919 Reserved for future use
HSV_2020 Reserved for future use
HSV_PRESERVE_FORMULA_COMMENT21BooleanPreserves formulas and comments on the grid during queries.
HSV_2222 Reserved for future use
HSV_FORMULA_FILL23BooleanPropagates 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_FORMATTING30BooleanSelects the Excel formatting check box
HSV_RETAIN_NUMERIC_FORMATTING31BooleanWhen 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_SEPARATOR32BooleanUses a comma or other thousands separator in numerical data. Do not use # or $ as the thousands separator in Excel International Options.
HSV_NAVIGATE_WITHOUTDATA33BooleanEnables 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_FORMATSTRING34BooleanEssbase-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_HANDLING35BooleanEnables review and correction of comments and member names in ad hoc grids that contain comments.
HSV_ADJUSTCOLUMNWIDTH36BooleanAdjusts column widths to fit cell contents automatically.
HSV_DECIMALPLACES37NumberSpecifies the number of decimal places to display.
HSV_SCALE38NumberSpecifies the scaling of numeric data, which is displayed based on the scale selected.
HSV_MOVEFORMATS_ON_ADHOC39BooleanCopies parent cell formatting to zoomed in cells and retains this formatting even if the cell location changes after an operation.
HSV_DISPLAY_INVALIDDATA40BooleanDisplays invalid data.
HSV_SUPPRESSCOLUMNS_MISSING41BooleanSuppresses 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_ZEROS42BooleanSuppresses columns that contain only zeroes.
HSV_SUPPRESSCOLUMNS_NOACCESS43BooleanSuppresses columns that contain data that the user does not have the security access to view.
HSV_SUPPRESS_MISSINGBLOCKS44BooleanSuppresses blocks of cells for which no data exists in the database.
HSV_DOUBLECLICK_FOR_ADHOC101BooleanSpecifies that double-clicking retrieves the default grid in a blank worksheet and thereafter zooms in or out on the cell contents.
HSV_UNDO_ENABLE102BooleanEnables and disables Undo.

Specify the number undo operations allowed with the HSV_NUMBER_OF_UNDO_ACTION parameter.

HSV_103103 Reserved for future use.
HSV_LOGMESSAGE_DISPLAY104NumberSpecifies message display level setting:
  • 0 = Information

  • 1 = Warnings

  • 2 = Errors

  • 3 = None

  • 4 = Extended info

HSV_ROUTE_LOGMESSAGE_TO_FILE105BooleanEnables and disables the Route Messages to File check box.
HSV_CLEAR_LOG_ON_NEXTLAUNCH106BooleanClears the log file starting with the next log message generation, which will be seen after Excel is closed.
HSV_REDUCE_EXCEL_FILESIZE 107BooleanShould 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_CONTEXT108BooleanDisplays the active data provider ribbon automatically after you use a button on the Smart View ribbon.
HSV_DISPLAY_HOMEPANEL_ONSTARTUP109BooleanEnables 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_REFRESH110BooleanWhen 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_ACTION111NumberThe 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_ITEMS112NumberThe 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_LOCATION113TextSaves log messages in a file.
HSV_DISABLE_SMARTVIEW_IN_OUTLOOK114BooleanDisables Smart View in Outlook if you do not want to use Smart View task lists in Outlook.
HSV_DISPLAY_SMARTVIEW_SHORTCUT_MENU_ONLY115BooleanDisplays only Smart View menu items on shortcut menus. Otherwise, shortcut menus display both Excel and Smart View items.
HSV_DISPLAY_DRILL_THROUGH_REPORT_TOOLTIP116BooleanDisplays by default lists of available drill-through reports for cells whenever you mouse over them.
HSV_SHOW_PROGRESSINFORMATION117BooleanSpecifies 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_TIMEDELAY118NumberThe time, in seconds, after which the Smart View Progress status bar appears when an operation begins.
HSV_ENABLE_PROFILING119BooleanCreates 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.