AX2287

Defining sheet views for a sheet

You can define one or more sheet views for a sheet, in order to:

  • Set sizing for columns and rows in a sheet
  • Hide specific columns and rows in a sheet
  • Set the active cell for a sheet

A sheet can have a single view just for formatting purposes, or it can have multiple views that emphasize different information. For example, a sheet might have one view that displays data by quarters, and another view that displays data by months. Or one view that shows only high-level summary data, and another view that exposes the detailed data.

Sheet views are defined by creating the following tags in the sheet:

  • A View tag to define the name of the sheet view, and to designate the location of the view control row and control column. You can also optionally specify default row and column sizing for the sheet view.

  • Hide tags to flag certain columns and rows to be hidden when the sheet view is applied. These tags are placed in the view control row and control column.

  • Set tags to set sizing for specific columns and rows when the sheet view is applied. These tags are placed in the view control row and control column.

  • Page break tags to set page breaks when the sheet view is used in conjunction with a Print tag. These tags are placed in the view control row and control column.
Sheet view tag summary
Tag Type Tag Syntax

Primary tag

[View; ViewName; DefaultColWidth; DefaultRowHeight; ActiveCell]

Hide row / column tags

[Hiderow;Pagebreak]

[Hidecolumn;Pagebreak]

Set row / column tags

[Setrow;Height;Pagebreak]

[Setcolumn;Width;Pagebreak]

Pagebreak row / column tag

[Pagebreak]

Defining the primary View tag in a sheet

To define a sheet view, place the following tag in any cell within the first 500 rows of the sheet:

[View; ViewName; DefaultColWidth; DefaultRowHeight; ActiveCell]

The View tag uses the following parameters:

Item Description

ViewName

The name of the sheet view. The sheet view name is the name that users will select to apply this particular view. The name can also be used to apply a default sheet view when the file is opened.

This name must be unique across all views defined in the sheet, including column views and sheet views.

DefaultColWidth

Optional. Specifies the default column width for the sheet view. The width specified here will be used for all columns except hidden columns, or columns where the width is set using individual SetColumn tags.

You can type a number for the width, or use the keyword Autofit.

DefaultRowHeight

Optional. Specifies the default row height for the sheet view. The height specified here will be used for all rows except hidden rows, or rows where the height is set using individual SetRow tags.

You can type a number for the height, or use the keyword Autofit.

ActiveCell

Optional. Specifies a cell location for the cursor when the sheet view is applied.

The active cell is only used when the view is applied as an Initial Dynamic View or when a user explicitly applies the view using Change View. It is not reapplied after system processes such as running Axiom queries or inserting calc methods.

NOTE: If the view is configured to be applied as the default view when the file is opened, the active cell defined for the view takes priority over the active cell defined for the sheet (on the Control Sheet).

The row where the View tag is placed becomes the view control row, and the column where the view tag is placed becomes the view control column. Multiple sheet views can share a control row or a control column if appropriate.

Examples

[View;Budget Summary]

This defines a sheet view named Budget Summary, with no default column or row sizing.

[View;Budget Detail;Autofit;16]

This defines a sheet view named Budget Detail, with the default column width set to autofit, and the default row height set to 16.

[View;Budget Summary;;;F25]

This example is the same as the first example, except that the cursor will be placed at cell F25 when the sheet view is applied. Note that omitted parameters must be delimited with "empty" semicolons.

NOTES:  

  • View tags are not ignored for purposes of the autofit settings. This means that the column containing the View tag may be quite wide due to the length of the View tag itself. Once you have finished setting up the view, you may want to set the cell containing the View tag to a small font size, such as 1pt, so that the tag does not affect autofit.

  • The primary View tag must be located in the first 500 rows of the sheet.

  • View tags can be placed within a formula, as long as the starting bracket and identifying tag are present as a whole within the formula. For more information, see Using formulas with Axiom feature tags.

Hiding columns or rows

If you want a column or row to be hidden when a sheet view is applied, you must mark the column or row with a Hide tag.

To mark a row as hidden, place the following tag in the control column for the sheet view:

[Hiderow]

To mark a column as hidden, place the following tag in the control row for the sheet view:

[Hidecolumn]

If a column or row is marked to be hidden, this overrides any default width or height set in the View tag, and the column or row is hidden using standard Excel functionality.

NOTES:  

  • Hide tags can be placed within formulas if desired. For example, you can use formulas to dynamically determine whether a certain row or column should be hidden.
  • [Hidecol] is also recognized as a valid tag.
  • You can optionally append the keyword Pagebreak to the Hide tag in order to set a page break when printing. For example: [Hiderow;Pagebreak]. See Setting page breaks for use with Print views.

Setting individual column width or row height

If you want to set the height of an individual column or the width of an individual row when a sheet view is applied, you must mark the column or row with a Set tag. If an individual column or row is marked with a Set tag, this overrides any default width or height set in the View tag.

To set the height of a row, place the following tag in the control column for the sheet view:

[Setrow;Height]

To set the width of a column, place the following tag in the control row for the sheet view:

[Setcolumn;Width]

To define the height or the width in the tag, specify a number or use the keyword Autofit.

NOTES:  

  • The Set tag can be placed within a formula if desired. For example, you can use formulas to dynamically determine the height or width, or whether the size should be set at all.
  • Set tags are not ignored for purposes of autofit settings. You may want to set the cells containing Set tags to a small font, such as 1pt, so that the tags do not affect autofit.
  • You can optionally append the keyword Pagebreak to the Set tag in order to set a page break when printing. For example: [Setcolumn;15;Pagebreak]. See Setting page breaks for use with Print views.
  • [Setcol] is also recognized as a valid tag.

Setting page breaks for use with Print views

If the sheet view will be used with a Print view, then you can specify page breaks on certain rows and columns. These tags are only honored when the sheet is printed using a Print tag that references the sheet View tag. The page break tags are ignored when simply applying the view to the sheet. For more information on using Print tags, see Setting up print views for Axiom files.

To set a page break on a particular row or column, place the Pagebreak tag in the View control column or row by itself, or as an optional parameter to a Hide or Set tag. For example:

[Pagebreak]

[Hiderow;Pagebreak]

[Setcolumn;15;Pagebreak]

Page breaks are inserted before the column or row that is flagged with a Pagebreak tag. This means that the next page will start with the flagged row or column (unless the Pagebreak tag is within a Hide tag, in which case the next page starts with the next visible column or row).

Sheet view behavior

When a file is opened, no sheet views are active by default. This means that no sheet view settings will be applied, and all columns and rows will be visible (unless they are hidden by other means). If you want to automatically activate a sheet view when the file is opened, you can use the Initial Dynamic View setting on the Control Sheet. For more information, see Setting the default views for a sheet.

When a sheet view is made active—whether by default on file open, or by using the Change View command—the following occurs:

  • All rows and columns that are marked with Hide tags in the selected view are hidden.

  • All other rows and columns are shown, including rows and columns that have been manually hidden using Excel functionality.

    Exception: If a column or row is currently hidden due to an active column view or row view, that column or row remains hidden. When sheet views overlap with column and row views, the Hide tags in the sheet view are always honored, but otherwise the column and row view settings remain active.

  • Row heights and column widths are set based on the default settings in the selected View tag and in any individual Set tags. If no width or height is defined in the sheet view for a column or row, then its width or height is left as is.

  • The cursor is placed in the active cell for the view, if one has been defined for the view.

  • The Freeze Panes settings defined on the Control Sheet (if applicable) are reapplied.

  • Spreadsheet groupings in the sheet are expanded.

When a sheet view is made active, the name of the sheet view is written to the Current Dynamic View cell on the Control Sheet. This field is system-maintained and is used to keep track of the current sheet view so that it can be automatically reapplied after certain file processes (such as after running an Axiom query, or inserting a calc method). If the sheet is not already configured on the Control Sheet, activating a sheet view will cause it to be.

Only one sheet view can be active at any one time. If a sheet view is currently active and then a user selects a different sheet view from the Change View menu, the settings for the existing view are now ignored and the settings for the new view are applied.

See also