Modify the behavior options of a grid.

Excel

OptionDescription
Resize columns/rows after refreshAutomatically adjust the columns and rows to fit the data.
Insert/delete columns/rows when Grid size changes

Determines the behavior when members are added or removed.

  • No Shift – Anaplan XL overwrites neighboring cells.
  • Shift Range – Anaplan XL 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 – Anaplan XL inserts and deletes entire columns or rows to accommodate the new data.
Fill formulae next to GridDetect and fill down/across any formulae directly adjacent to the Grid as members are added or removed.

Example of the shift behaviors:

Query options

OptionDescription
Remove empty columns/rowsExclude columns/rows with no values from the result set.
Enable Visual TotalsSee Visual Totals for more information
Incude Headers in setsHeader 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 NullAllows zeros to be removed from reports using the Exclude Empty options
Feed hierarchy member resultsUse the MDX function Generate to enable 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 crossjoinsUse the NonEmpty MDX function when joining hierarchies
Exclude calculated membersBy default, Anaplan XL 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 selectionsIf a range contains no members, then Anaplan XL will use the default member instead. If you turn this off, it'll use an empty set instead. This can be useful if you are combining multiple sets and some may be empty.
Allow Excel style advanced setsSee Excel Style Advanced Selections.
Where clause style

When aggregating in a header hierarchy, Anaplan XL 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 MembersAdds 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.
Use Subselect for column/row selectionsChanges the query to use a subselect clause.
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. 

For further information, see Drill Modes.

Member Text

Controls how the grid handles user-entered member selections, for example, when driving from a range.

  • Validate - Member text is checked against the database to ensure it's valid.
  • Unique Name - Member text is assumed to be a 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. For example, 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. For example, 1 becomes [Product].[Product Categories].&[1]

Refresh

OptionDescription
Refresh Grid on openCauses the Grid to update itself when the workbook is opened.
Refresh when driving cells changeIf the Grid is driven from any Excel ranges, then this causes the Grid to update itself whenever any of these changes.
Refresh when slicer/links changeIf the grid is driven from any slicers/links then causes the grid to update itself whenever any of these changes.
Display error on invalid membersDisplays a warning if invalid selections are found on any hierarchy.
Warn when driving cells out of syncDisplays a warning if the cells driving the grid have changed and the refresh is turned off.