Data Table Range Properties

false

Controls whether a user is allowed to add rows using the Add Row tool or insert rows using the Insert Row tool. It also controls whether an empty row is automatically added and maintained as the last row in the sheet range.

The Add Row tool is a standard tool identified by the key AddDataTableRows. The tool is enabled only when AllowAddRow is True and when either of the following conditions is met:

1) Multiple DataTableRange’s are represented on the sheet and the active cell is within the sheet range of a DataTableRange that allows adding rows, or 2) This DataTableRange is the only range represented on the active sheet.

When multiple rows are selected within the range, the number of rows added is equivalent to the number of rows selected.

The Insert Row tool is a standard tool identified by the key InsertDataTableRows. The tool is enabled only when AllowAddRow is True and the active cell is within the range. The tool inserts rows above active row. The number of rows inserted is equivalent to the number of rows selected.

The setting also controls whether new rows can be saved using the Save tool.

The Save tool is a standard tool identified by the key SaveDataSetRanges. The tool is enabled only when AllowAddRow, AllowDeleteRow, or AllowModifyRow is True for any DataTableRange defined for the SQLPassthroughDataSetRange.

false

Controls whether a user is allowed to delete rows using the Delete Row tool.

The Delete Row tool is a standard tool identified by the key DeleteDataTableRows. The tool is enabled only when AllowDeleteRow is True and the active cell is within the range. The tool deletes all the selected rows from the range.

The setting also controls whether deleted rows can be saved using the Save tool.

The Save tool is a standard tool identified by the key SaveDataSetRanges. The tool is enabled only when AllowAddRow, AllowDeleteRow, or AllowModifyRow is True for any DataTableRange defined for the SQLPassthroughDataSetRange.

false

Controls whether modified rows can be saved using the Save tool.

The Save tool is a standard tool identified by the key SaveDataSetRanges. The tool is enabled only when AllowAddRow, AllowDeleteRow, or AllowModifyRow is True for any DataTableRange defined for the SQLPassthroughDataSetRange.

When AllowModifyRow is False, the user is not prevented from editing cell values. To prevent a user from editing values in a row within a DataTableRange’s sheet range, the cells that cannot be edited should be locked and the sheet protected.

When the view is built or closed, the SaveDataSetRanges tool detects whether any rows have been added, deleted, or modified. If so, the user is prompted whether to continue.

false

Controls whether auto-filtering within the data range or data block is automatically enabled.

AutoFilteringEnabled is not applicable when the DataTableRange is configured to group rows.

The following limitations apply to auto-filtering: — As in Excel, auto-filtering is not supported on a protected sheet. — Auto-filtering is not currently supported for Essbase Excel views.

The name of an optional sheet range that contains the data range and additional columns that contain formulas that refer to cells within the data range. The DataBlockSheetRangeName must be the name of sheet-level defined range.

The data block range is used to define formulas within cells on a data row that refer to cells within the data range on the same row. When the range is built and when rows are added, inserted, or deleted, the columns within the data block are maintained along with the data range.

When grouping is defined, the data block range should not be used, since the range specified as the GroupTemplateSheetRangeName provides the same functionality.

The name of the sheet range where the data is copied. The DataSheetRangeName must be the name of sheet-level defined range.

If the sheet range contains a single column, it is expanded to the number of columns in the data table. If the range contains multiple columns, the equivalent number of DataTable columns is copied to the range, which allows for the DataTable to contain additional columns that are not copied to the sheet. These columns can be used for sorting and grouping, and can also include the primary key column(s).

If the range is a single row, it is expanded to the number of rows in the data table.

The number of rows that are required in the sheet range is determined by the SetDataFlags’ InsertCells and NoColumnName options. Refer to the SetDataFlags setting for additional information.

The name of the DataTable associated with the range.

For a SQLPassthroughDataSetRange (or SQLPassthroughDataSet universal range): A SQLPassthroughDataSet defines one or more queries. Each query specifies the name of the DataTable in the DataSet that will contain the results of the query. The DataTableName associates a DataTable with a sheet range. When the SQLPassthroughDataSet is built, the DataTable rows are copied to the sheet range.

For a DataSet universal range that uses a connector, the DataTableName is required. Depending on the DataSet source connector, the value may either be arbitrary or specific to the requirements of the connector.

true

Controls whether an empty row is automatically added and retained as the last row in the data sheet range.

RetainEmptyLastDataSheetRangeRow is typically set to True when users are allowed to add rows to the data range.

true

Controls whether a single row is retained in the data sheet range when the DataTable contains no rows.

Set RetainSingleDataSheetRangeRow to True when column formulas are defined that refer to cells within the last row of data range.

RowDataErrorColumnLocation.FirstColumn

Indicates the column(s) used to display a row’s data errors.

When a user saves changes, any row data errors returned by the database are displayed in an Excel comment for each row that contains an error. The cell(s) that contains the Excel comment is controlled by the RowDataErrorColumn.

None - No data errors are displayed. FirstVisibleColumn - Displays the data errors in a row’s first visible cell in the DataSheetRangeName. ColumnToLeftOfSheetRange - Displays the data errors in the cell to the left of row’s first cell in the DataSheetRangeName. AllColumns - Displays the data errors in each of the row’s cells in the DataSheetRangeName.

DataTableRangeSetDataFlags.Default

The SetDataFlags options can be enabled and disabled to control how the data is copied to the sheet range.

Default — All the options are disabled by default.

InsertCells —  To pre-format the range and to have formulas that refer to columns in the range, enable InsertCells. When enabled, cells are inserted or deleted to accommodate the data. The sheet range must contain exactly two rows for data, unless the NoColumnHeaders option is disabled, which requires an additional row. When the DataTable contains no rows, a single data row is retained in the range to allow for formulas. If the DataTable contains multiple rows, any formulas that refer to the second row of the original range are changed to refer to the last inserted row. When disabled, copies the data to the sheet without inserting cells.

NoColumnHeaders When enabled, uses the first row of the range as the first data row. When disabled, uses the first row of the range for the column headers.

AllText When enabled, all values are stored and formatted as text, including numeric and boolean types, which are converted to text. When disabled, an attempt is made to parse the values to determine if numeric or boolean, and automatically uses the best cell type and formatting based on the data type.

Sets or returns a range name to allow spell-checking for.

ExcelOutlineSummaryRow.BelowDetailRows

Controls the location of the +/- (expand/collapse) icon relative to the detail rows.

When Excel outlining is enabled for any DataTableRange that is configured to group and/or sort rows, the ExcelOutlineSummaryRowsLocation setting indicates whether the +/- (expand/collapse) icon within each Excel group of rows is located above or below the detail rows.

AboveDetailRows - The +/- (expand/collapse) icon appears above the detail rows.

BelowDetailRows - The +/- (expand/collapse) icon appears below the detail rows.

The RowSortAndGroupByInfo’s ExcelOutlineHeaderRowCount and ExcelOutlineFooterRowCount settings control the number of rows that remain visible above and below the detail rows, respectively, when the Excel group is collapsed.

The defined range name or range reference that marks where the first occurrence of the outermost grouping is copied to. The GroupStartCell setting is only applicable when grouping or sorting is configured.

Configures grouping and sorting of the DataTable rows.

Multiple levels of grouping can be defined. Each group definition in the RowSortAndGroupByInfoList represents a level of grouping. The list is ordered from the outermost to innermost level where a group definition is the parent of the next group (or subgroup) definition in the list. When the DataTable rows are grouped at runtime, a single occurrence of a parent group can contain multiple occurrences of the child group. No limit is imposed on the number of levels of group definitions that can be defined.

Grouping Rows Each level of grouping can be based on either 1) the value of one or more columns, or 2) conditional filter expressions

Sorting Rows Sorting based on one or more columns can be defined for the innermost group level.

Excel Outlining Excel outlining can optionally be enabled for each level of grouping. Excel grouping supports an outline of up to a maximum of 8 levels.

The DataTableRange name, which is used to uniquely identify the instance in the DataTable Range Editor.