- 1 Behaviour Options
- 2 Appearance Options
- 3 Interaction Options
- 4 Lockdown Options
- 5 Writeback Options
- 6 MDX Options
- 7 Default Grid Options
- 8 Performance Tips for Large Grids
- 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. E.g. when a grid becomes taller cells will only be inserted across the width of the data.|
|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.
- Remove empty columns/rows
- Exclude columns/rows with no values from the result set.
- Enable Visual Totals
- New in Version 7.6 - see Visual Totals for more information
- Incude Headers in sets
- Header selections are written into tuples when the MDX is generated. Turning this off means that context from the rows/columns selections will not be overwritten by the headers.
- Replace 0 with Null
- Allows zeros to be removed from reports using the Exclude Empty options
- Feed hierarchy member results
- Use the MDX function Generate to allow dynamic ranking and filtering (note: this option can be slower). For example: if Country and Product are on Rows, with this option enabled a grid with ranking on both hierarchies will show the Top 10 Countries and within each of those countries the Top 10 Products. With this disabled you would get the Top 10 Countries and the Top 10 Products, calculated separately.
- Use NonEmpty on crossjoins
- Use the NonEmpty MDX function when joining hierarchies
- Exclude calculated members
- By default XLCubed will show calculated members when drilling members to display their children. This option means those calculations are no longer shown.
- Use Default Member for empty selections
- If a range contains no members then XLCubed will use the default member instead. If you turn this off it will use an empty set instead. This can be useful if you are combining multipe sets and some may be empty.
- Allow Excel style advanced sets
- See Excel Style Advanced Selections.
- Where clause style
- When aggregating in a header hierarchy, XLCubed can generate MDX that:
|With members||extracts each aggregate into a With Member|
|Sets||leaves the aggregates in-lined as a set|
|Subselect||uses an in-line subselect statement for the aggregates, gives visual totals (Totals are for selected members only).|
The first two options should give the same results, but performance can vary depending on your cube.
- Use Existing in With Members
- Adds an Existing function on aggregated sets in headers if the same dimension is also used on columns or rows. This can improve performance in many cases, but for some cube calculations may return unexpected results. You should check that the grid results are correct when turning on this option.
- Drill Mode
- Changes which members are shown when members are drilled
|Standard||Drilled members are expanded in all positions.|
|Drill and Replace||Drilled members and their children are shown. Drill the parent member to drill back up.|
|Asymmetric||Drilled members are expanded only in the location drilled. This applies if you have more than one dimension on rows, when the members can be repeated. This feature was introduced in version 7.2.|
For further information go to: Drill Modes
- Member Text
- Controls how the grid handles user-entered member selections, e.g. when driving from a range
|Validate||Member text is checked against the database to ensure it is valid.|
|Unique Name||Member text is assumed to be valid unique name and passed directly into the query.|
|Captions||Member text is assumed to be a valid caption and combined with the hierarchy name before being passed into the query. e.g. "Bikes" becomes "[Product].[Product Categories].[Bikes]"|
|Keys||Member text is assumed to be a valid member key and combined with the hierarchy name before being passed into the query.e.g. "1" becomes "[Product].[Product Categories].&"|
- 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.
- Refresh when driving cells change
- If the Grid is driven from any Excel ranges, then causes the Grid to update itself whenever any of these changes.
- The title of the Grid, displayed in dialogs and menus whenever the Grid is referred to.
- The following placeholders can also be used to display information regarding the cube connection:
- Display Grid title
- Display the Grid title in the worksheet.
- Replace nulls with
- Instead of an empty cell, XLCubed inserts the desired value.
- 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:
- 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|
- Group member properties with the same name
- Any member properties with the same caption in the cube will be merged together into one column
- Show Comments
- Report users can view and enter comments
- 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.
- Size of indentation
- Allows user to set size of indentation when drilling
In this section you can choose not to display sections of the grid. This allow you to place grids in specific places without needing to hide rows/columns which is useful if they move when drilled.
|All areas except "Row Members" hidden on the second grid.|
This feature was introduced in version 7.2.
- 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.
- Hide Column Labels
- Hide labels for any hierarchies displayed on the columns axis
- Hide Row Labels
- Hide labels for any hierarchies displayed on the rows axis
- Hide Column Members
- Hide members for any hierarchies displayed on the columns axis
- Hide Row Members
- Hide members for any hierarchies displayed on the rows axis
Track grid's active cell
The current cell in the Grid can be used as input for other Grids and formulae. See Active Cell Grid Selections for more information.
XLCubed can create named ranges based on Grid elements that can then be used by other Excel objects, such as formulae and charts. See Named Grid Ranges for more information.
- 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.
- 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.
See Writeback for more detail about writeback in XLCubed.
- 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 Grids
- Apply formatting
- Merge repeating cells
- Resize Columns
- Hide draggable hierarchy labels
After making these changes to all the grids use XLCubed -> Connections -> Refresh connections to clear any remaining hierarchy labels.