Report Writer > Browses > Browse on Data Dictionary Columns

Browse on Data Dictionary Columns

You use this program to create and maintain the Report Writer data dictionary column definitions.

The standard SYSPRO Tables and Columns are imported using the Data Dictionary Import program, which is a prerequisite to using the Report Writer.

A maximum of 200 columns per table are allowed in SYSPRO.

Each individual item of information defined within a table is called a column, which is assigned a 18 character column name. The column name defines where the information is stored in a particular table.

For example, the stock description is held within the Inventory Master table.

You use the Data Dictionary Column Listing program to print a list of data dictionary columns.

Change Table

Before you can maintain the columns in a table, you need to indicate the table in which the column(s) must be maintained.

This screen is displayed when you access the Browse on Data Dictionary Columns program from the menu. When you access the Browse on Data Dictionary Columns from within the Browse on Data Dictionary Tables program, the table currently highlighted in the Browse on Data Dictionary Tables listview is used by default.

Once you have selected and accepted a table, you can use the Change Table option from the Change menu to select a different table.

Field Value
Table Indicate the table for which you want to maintain columns.
OK

Select this to accept the table name you entered.

The existing columns for the table you selected are displayed in the listview.
Cancel Select this to exit the program or to return to the listview if you selected the Change Table option from the Change menu to select a different table.

Toolbar and menu

Field Description
Change  
Change table Select this to indicate a different table for which you want to maintain the columns.
Linked Tables

Data Dictionary Column Maintenance

This screen is displayed when you select the Add or Change option.

[Note]

  • If the table to which you are adding the column resides on a SQL server, you must also add the column to the appropriate table on the SQL server system. i.e. the Browse on Data Dictionary Columns program only adds the column to the Report Writer table. Refer to Report Writer Introduction for additional information.

  • For standard SYSPRO columns, you can only change the Description and Access fields.
Field Description
Column

This field is used to name the column in the data dictionary.

Once this name has been created it cannot be changed.

Standard column names should be used across the tables, as this helps with linking. e.g. StockCode.

If your company defines one or more key columns as numeric it is advisable to add a new column to the data dictionary defining the column as numeric, especially if you use these key columns as run time prompts.
Description

This field is used to describe the column in the data dictionary.

You can change this description at a later stage.
Access

Indicate the level of access permitted. The lowest permission held against the table/column is used as the default.

The lower access of the table and column permissions is used to determine the actual access allowed. Therefore, although you can define Read/Write access against the column here, if the level of access against the table is defined as Read only; then you will only have Read only access to the column.
Read only Select this to define this column as read only. No data can be written to the column. Data can only be read from the column.
Read/Write Select this to define the column as read and write. Data may be read from as well as written to the column
None Select this to define no access to this column. i.e. The column cannot be accessed by the Report Writer.
Sequence

The Column sequence number is a three digit numeric number in the data dictionary that is used to maintain a list of all the columns by sequence number for each table.

It can be used when either browsing on the columns, or when listing them in the Data Dictionary Columns Listing program.

The standard data dictionary generates a sequence number from 1 upwards, reflecting the position within the table. You can therefore, list the fields in the same sequence that they are defined in the record layouts.

If you add a user defined column that is related to a standard column, then give this column the same sequence number as the standard column. This enables you to find the position easily.

For example: In the Inventory Master table, the Product Class column has a sequence number of 20 and an edit pattern of 4. The first 2 characters of the product class depicts the type, for instance BH is for Bicycle Helmets. You now want further details within the Bicycle Helmets, for instance the size and the color. To do this, add a new column called Product Group with an edit pattern of 2, then give this column the same sequence number of 20. Now, when browsing or listing the columns, the Product Group will appear together with the Product Class.
Edit pattern

This is displayed for verification purposes only and cannot be changed. It displays what the field will look like when it is printed,which assists you when aligning the headings on a report.

If the column is a Numeric type, then you must define the edit pattern to be used. An additional edit pattern option is displayed once the column type is set to numeric enabling you to enter edit pattern to be used when printing the field.
Type Depending on the column type you select, different fields become available for input (e.g. Length, decimals, format, etc). Refer to for additional information (Column Type Interpretations)
Alpha Select this to define the column as alphanumeric.
Numeric Select this to define the column as numeric.
Date Select this to define the column as a date.
Position This indicates the position in the line where the first character is located. The first character in a column is in position 1.
Length

This indicates the length of the column. i.e. The number of characters that the column occupies within a line.

This is dependent on the number of decimal places and the selected format.

This is not available for Date type columns.
Suppress leading zeros

Select this if you do not want to print leading zeros in the column. This option is only available for alphanumeric type columns.

Example Customer Code 0001234 is printed as 1234.
G/L company id in shared table

This option is only available for alphanumeric column types.

Some General Ledger tables, such as GenMaster, store a company id as the first column and against this column is a behind-the-scenes flag indicating that this must be the current company. The Report Writer uses the Company column flagged as mentioned above to ensure that only rows for the current company id are accessed.

By default this option is selected, which causes the Report Writer to only select records for the current company when the General Ledger is shared.

If this option is deselected, then the Report Writer selects all records in the shared General Ledger files (unless there is some conditional logic limiting the Companies selected in the report). This means you can produce a report that lists all the ledger codes in a consolidated GenMaster table.
Decimals This indicates the number of decimals to be used for the number where the Type is defined a Numeric.
SYSPRO format number Select this to store the column in the SYSPRO Format Number.

This option indicates that numbers have an implied decimal point and that negative numbers do not store the sign as a separate character, but instead changes the last digit according to a table. i.e. The decimal point and sign do not take up any characters.
Edit pattern

This indicates the edit pattern to be used when printing the field and is only enabled for Numeric Types.

The number preceding the decimal point represents the number of integers, and the number after the decimal point represents the number of decimal places. If a negative sign is entered, it means values under zero will be printed.

The maximum size of a numeric field is 15 digits before the decimal and a maximum of 6 digits after the decimal (15.6). This number can be positive or negative.
Blank when zero

Select this to prevent the field from being printed if it has a zero value.

When the file contains 0.0000 for a line, it will print nothing under the column.
Format

This indicates the format in which you want the date displayed and is only enabled for Date type columns.

All dates are now stored as CCYYMMDD in the files, but can displayed as required. The format chosen determines the number of characters required on the line.
Output format

This indicates the format to be used when printing a date column.

The same format codes are available throughout the Report Writer.

The dates are held in an internal format and presented according to the format entered here. The editing formats use a / (forward slash) to edit the dates.
Blank when zero Select this to prevent the field from being printed if it has a zero value.
Show Table Information Select this to view additional information about the table for which you are maintaining columns.
Save

Select this to save the information you entered.

[Note]

If the table to which you are adding the column resides on a SQL server, you must also add the column to the appropriate table on the SQL server system. i.e. the Browse on Data Dictionary Columns program only adds the column to the Report Writer table.
Close Select this to ignore any changes you made and to return to the listview.

Current Table Information

This screen is displayed when you select the Show Table Information option.

The following information is included for the table:

Field Description
Table

This indicates the name of the table.

The standard SYSPRO table base names are preceded by an abbreviation of the SYSPRO module.
Description This indicates the description of the table and is used for identification purposes.
Record length This indicates the number of characters required for each line within the table.
Key length This indicates the number of characters occupied by the key column within the table.

Column Type Interpretations

The following table indicates how the Report Writer interprets SQL column types:

SQL Column Type SYSPRO Report Writer
Char Alphanumeric
Varchar Alphanumeric
Unichar Alphanumeric
Univchar Alphanumeric
Tinyint Numeric
Smallint Numeric
Int Numeric
Real Numeric
Float Numeric
Date Date
DateTime This stores both Date and Time data. You cannot change this data types to Read/Write in the report writer data dictionary and you cannot add user defined columns for this data type.
VarChar(MAX) This applies to SQL only tables in the SYSPRO data dictionary. Internally the record layouts for this field is defined as an eight character ALPHA field, which would be used to store a pointer to the data in memory.

The Report Writer can only access the first 255 characters of a VarChar(MAX) column. You cannot change this data type to Read/Write in the report writer data dictionary and you cannot add user defined columns for this data type.

VarBinary(MAX) This applies to SQL only tables in the SYSPRO data dictionary. The Report Writer cannot access any data in a VarBinary(MAX) column.

Listview columns

Column Description
Column name This indicates the name of the column in the data dictionary.
Description This indicates the text used to describe the column in the data dictionary.
Sequence This indicates the three digit numeric number in the data dictionary that is used to maintain a list of all the columns by sequence number for each table.
Data type This indicates the type of data that can be entered/stored in the column.
Edit This indicates what the field will look like when it is printed.
Position This indicates the position in the line where the first character of the column is located.
Access

This indicates the level of access permitted for the column.

  • Read only - No data can be written to the column. Data can only be read from the column.
  • Read/Write - Data may be read from as well as written to the column.
  • None - The column cannot be accessed by the Report Writer.
User A 'Yes' in this column indicates that this is a user defined column.

Add a report writer data dictionary column

  1. From the Browse on Data Dictionary Columns program, enter the name of the table to which you want to add a column.

  2. Select OK to accept the table name you entered.

    The columns already defined against this table are displayed in a listview.

  3. From the listview pane, select Add from the Edit menu.

    Alternatively select the Add icon from the toolbar.

  4. Enter the information for the new column.

  5. Select the Save function to save the column information you entered.

    [Note]

    If the table to which you are adding the column resides on a SQL server, you must also add the column to the appropriate table on the SQL server system. i.e. The Browse on Data Dictionary Columns program only adds the column to the Report Writer table. Refer to Report Writer Introduction for additional information.

  6. Select Close to return to the listview.

Change a report writer data dictionary column

  1. From the Browse on Data Dictionary Columns program, enter the name of the table for which you want to maintain a column.

  2. Select OK to accept the table name you entered.

    The columns already defined against this table are displayed in a listview.

  3. From the listview pane, highlight the column you want to change.

  4. Select the Change option from the Edit menu.

    Alternatively select the Change icon from the toolbar.

  5. Enter the new information for the column.

  6. Select the Save function to save the changes to the defined column.

  7. Select Close to return to the listview.

Delete a report writer data dictionary column

  1. From the Browse on Data Dictionary Linkages program, enter the table from which you want to delete the column(s).

  2. Select OK to accept the table name you entered.

    The columns already defined against this table are displayed in a listview.

  3. From the listview pane, highlight the column you want to delete.

  4. Select the Delete option from the Edit menu.

    Alternatively select the Delete icon from the toolbar.

  5. From the Confirm Deletion screen, select OK to delete the selected column or Cancel to return to the listview without deleting the column.

    [Note]

    This step is only required if the Confirm Deletions command is selected from the Options menu.

    If the Confirm Deletions command is deselected, then the highlighted column is deleted immediately.

Change the listview sequence of columns

  • From the listview pane of the Browse on Data Dictionary Columns program, select the Sequence menu.

    Select the Column option to view the columns in column name order.

    Select the Column Description option to view the columns according their descriptions.

    Select Sequence Number to view the columns in their sequence number order.

    Once you have selected a sequence, the listview is re-ordered according to the selection you made.

Change the table

  1. From the listview pane of the Browse on Data Dictionary Columns program, select Change from the toolbar.

  2. Select Change table.

  3. Enter the table name you require.

  4. Select OK to accept the source table name you entered.