EssSetSheetOption

Description

Sets individual Essbase spreadsheet options.

Syntax

EssSetSheetOption([workbookName]sheetName, item, option)

Where [workbookName]sheetName is the text name of the workbook and worksheet where option is set.

item is a number that specifies the setting you want. The item arguments, description, and expected data types are shown in Table 34.

Table 34. Item Return Values and Types

ItemReturn ValueType

1

Specify Drill Level setting

Number

2

Enable Include Selection setting

Boolean

3

Enable Within Selection Group setting

Boolean

4

Enable Remove Unselected Groups setting

Boolean

5

Specify Indent setting

Number

6

Enable Suppress Missing setting

Boolean

7

Enable Suppress Zeros setting

Boolean

8

Enable Suppress Underscores setting

Boolean

9

Specify Alias for Missing label setting

Text

10

Enable Update mode setting (block storage databases only)

Boolean

11

Enable Retain on Retrieval formula preservation setting

Boolean

12

Enable Adjust Columns setting

Boolean

13

Enable Alias Names setting

Boolean

14

Specify Alias Name Table setting

Text

15

Enable Template Retrieve mode

Boolean

16

Enable Free Form/Version 2.x mode

Boolean

17

Enable Auto Sort Rows setting

Boolean

18

Enable Use Styles

Boolean

19

Specify No Access Alias setting

Text

21

Enable Retain on Keep Only and Remove Only formula preservation setting

Boolean

22

Enable Retain on Zooms formula preservation setting

Boolean

23

Enable Formula Fill

Boolean

24

Enable Member Names and Alias setting

Boolean

25

Enable Repeat Member Labels setting

Boolean

26

Enable Sheet Options for Query Designer setting

Boolean

27

Enable Latest Time Period

Boolean

28

Specify Latest Time Period

Text

29

Enable Hybrid Analysis

Boolean

30

Enables metadata sampling when performing a Zoom In operation

Boolean

32

Indicates the percentage of the members to retrieve when metadata sampling is enabled

Number

33

Enables display of the qualified name of the duplicate (non-unique) member in the Excel comment box

Boolean

34

Enables display of the qualified name of the duplicate member in the Excel spreadsheet

Boolean

Note:

Items 20 and 31 are not used.

option is the value set for the item. When specifying option, use the type listed in Table 34 next to the item you are setting.

Example

=EssSetSheetOption ("[Sample.xls]Sheet1",10,TRUE)

puts the worksheet in Update mode.

Notes: