DEFINE COLUMN command

Creates and adds one or more columns to an existing view.

Syntax

DEFINE COLUMN view_name field_name <AS display_name> <POSITION n> <WIDTH characters> <PIC format> <SORT|SORT D> <KEY> <PAGE> <NODUPS> <NOZEROS> <LINE n>

Parameters

Name Description
view_name

The view to add the column to.

field_name

The field to create the column for.

To use a field from a related table, specify the field name as table_name.field_name.
AS display_name

optional

The display name (alternate column title) for the field in the view. If you want the display name to be the same as the field name do not use AS.

Specify display_name as a quoted string. Use a semi-colon (;) between words if you want a line break in the column title.
POSITION n

optional

The position of the column in the view numerically from left to right:

  • if omitted, the column is placed as the rightmost column at the time that the column is added
  • if a position number is missing, column positions are adjusted so that the columns are positioned sequentially
  • if a position number is already in use, the new column is placed to the left of the column already using the position number
WIDTH characters

optional

The display width of the field in characters.

The specified value controls the display width of the field in Analytics views and reports. The display width never alters data, however it can hide data if it is shorter than the field length.

If you omit WIDTH, the display width is set to the character width specified for the field in the table layout.

Note

The characters specified by WIDTH are fixed-width characters. Every character is allotted the same amount of space, regardless of the width of the actual character.

By default, views in Analytics use a proportional width font that does not correspond with fixed-width character spacing.

If you want a one-to-one correspondence between the WIDTH value and the characters in the view, you can change the Proportional Font setting in the Options dialog box to a fixed-width font such as Courier New.
PIC format

optional

Note

Applies to numeric or datetime fields only.

  • numeric fields the display format of numeric values in Analytics views and reports
  • datetime fields the physical format of datetime values in the source data (order of date and time characters, separators, and so on)

    Note

    For datetime fields, format must exactly match the physical format in the source data. For example, if the source data is 12/31/2014, you must enter the format as "MM/DD/YYYY".

format must be enclosed in quotation marks.

SORT | SORT D

optional

Sorts the column:

  • ascending order SORT
  • descending order SORT D

KEY

optional

The column is designated as a break field in reports. Reports are subtotaled and subdivided when the value of the column changes. The following restrictions apply to break fields:

  • must be a character field or expression
  • if a break field is set in the view, it must be the leftmost column
  • the last column in the view cannot be a break field
  • if you have more than one break field, all of the columns to the left of any additional break field must also be break fields

PAGE

optional

Inserts a page break each time the value in the break field changes.

NODUPS

optional

Substitutes blank values for repeated values in the field.

For example, if the customer name is listed for each invoice record, the report is easier to read if it shows only the first instance of each customer name.

NOZEROS

optional

Substitutes blank values for zero values in the field.

For example, if a report includes a large number of zero values in a field, the report is easier to read if it only displays non-zero values.

LINE n

optional

 The number of lines in the column. If no value is specified, the column defaults to a single line. The value of n must be between 2 and 60.

Examples

Defining a view with six columns

With the AR table open, you define a view called AR_Report, and define six columns. The columns are displayed in the listed order:

OPEN Ar
DEFINE VIEW AR_Report OK
DEFINE COLUMN AR_Report No AS "Customer Number" WIDTH 7 KEY
DEFINE COLUMN AR_Report Date AS "Invoice Date" WIDTH 10
DEFINE COLUMN AR_Report Due AS "Due Date" WIDTH 10
DEFINE COLUMN AR_Report Reference AS "Reference Number" WIDTH 6
DEFINE COLUMN AR_Report Type AS "Transaction Type" WIDTH 5
DEFINE COLUMN AR_Report Amount AS "Transaction Amount" WIDTH 12 PIC "-9999999999.99"