Table Properties

Behaviour Options

Excel

Resize columns/rows after refresh

Automatically adjust the columns and rows to fit the data.

Insert/delete columns/rows when Grid size changes

Determines the behaviour when members are added or removed.

No Shift	XLCubed overwrites neighbouring cells.
Shift Range	XLCubed inserts the required number of cells only.
Shift Entire	XLCubed inserts and deletes entire columns or rows to accommodate the new data.

Fill formulae next to Grid

Detect and fill down/across any formulae directly adjacent to the Grid as members are added or removed.

Data

Remove empty columns/rows

Exclude columns/rows with no values from the result set.

Feed hierarchy member results

Use the MDX function Generate to allow dynamic ranking and filtering (note: this option can be slower).

Use NonEmpty on crossjoins

Use the NonEmptyCrossJoin MDX function (see this MSDN article for more information).

Refresh

Display error on invalid members

Displays a warning if invalid selections are found on any hierarchy.

Refresh Grid on open

Causes the Grid to update itself when the workbook is opened.

Appearance Options

General

Title

The title of the Grid, displayed in dialogs and menus whenever the Grid is referred to.

Display Grid title

Display the Grid title in the worksheet.

Replace nulls with

Instead of an empty cell, XLCubed inserts the desired value.

Formatting

Apply formatting

Override formats on the Grid, or let you manage them manually.

Format Grid without borders

Exclude borders from the formatting applied to the Grid.

Apply Cube formatting

Retrieve and apply formats that are defined in the cube.

Merge repeating cells

When crossjoining, merge the repeated cells for the outer hierarchies:

With merged repeating cells

With separate repeating cells

Show member properties in separate columns

If any member properties are selected, display each in its own column, or in the same cell as the member caption:

	Show member properties in separate columns
	Property - Member
	Member - Property
	Property

Hide header hierarchies

Show only the row and column hierarchies and members. The header hierarchies can still be seen in the Edit Grid dialog.

Hide draggable hierarchy labels

Prevent the hierarchy labels being rendered. This option speeds up Grid updates.

Hide hierarchy label warning icon

Prevent display of a warning icon when advanced selections are active on a hierarchy.

Drilling

Show drill indicator

If a member is drillable, a symbol is displayed before the member's caption:

+	Member can be drilled down.
-	Member can be drilled up.
	Member cannot be drilled

Indent members when drilling

The indentation of the members in a Grid reflects what level they are on. The size of indentation can also be adjusted.

Lockdown Options

Password protect Grid properties

Before showing the Grid Properties dialog, a password is required.

Apply restrictions in Excel

Enforce the following permissions in Excel. If off, the restrictions are only applied when published to XLCubed Web Edition.

Permissions

Show Grid menus

Show the right-click Grid menu. Note: The Grid properties dialog is always available unless specifically excluded below.

Allow hierarchy navigation

Show the Hierarchy Editor for any hierarchy.

Can change filter/column/row members

Show the Hierarchy Editor for the hierarchies on the given axis.

Can drill columns/rows

Allow drilling on members in the given axis.

Set All to No

Disallows all the above permissions.

Hiding Menu Items

These options allow you to hide specific operations from the Grid's right-click menu.

Writeback Options

See Writeback for more detail about writeback in XLCubed.

MDX Options

Connection

Set the connection for the Grid

Auto-generate MDX (default)

XLCubed controls the MDX for the Grid, given the selected options, hierarchies and members.

Manual MDX

You may type your own MDX in the edit field. Using XL3Parm( Address ) allows you to substitute parts of the MDX with cells on the worksheet.

Get MDX from Excel range

The entire MDX statement for the Grid is taken from a cell on the worksheet.

Default Grid Options

The defaults for new Grids can be set by using the Save as Defaults button. To set a Grid back to the saved defaults, use the Load from Defaults button.

Performance tips for large tables

If you have very large tables, the following options will speed up data refreshes:

Turn off

• Resize Columns
• Apply Formatting
• Merge repeating cells