> Report Writer Introduction

Report Writer Introduction

The Report Writer module enables you to produce ad hoc reports in the format you require.

The menu driven design provides access to all system data with no programming skills required.

Reports can be sent to the printer, screen preview or an ASCII file. Data can be read and written back to the database facilitating mass updates. Data from 3rd party sources can be used in reports. Data can also be exported to third party software such as spreadsheets, word processors, databases, etc.

The Report Writer does not have any logic to physically create SQL tables (i.e. the table definition or the columns and their data types). The Report Writer cannot write any new rows to an existing SQL server table. It can only be used to update existing rows in a SQL table.

Implementation

The Report Writer module uses its own data dictionary and not the standard data dictionary. For this reason, before you can use the SYSPRO Report Writer, you must use the Data Dictionary Import Wizard program to create the Report Writer Data Dictionary from the standard dictionary supplied with SYSPRO. This applies irrespective of whether you are using a C-ISAM or a SQL Server-based system.

The standard data dictionary contains all the table names, columns and table linkages required for your reports. The import program uses files in the Base/dd directory to create the data dictionary. Importing the Standard Data Dictionary does not have any effect on your data files.

Even if you have a previous version of the Report Writer installed, we recommend that you re-import the standard data dictionary to ensure that you have the most up-to-date definition of tables and columns.

It is advisable to re-import the data dictionary every time a new version of the software is installed.

Once you have created the Report Writer data dictionary, you can optionally use:

  • the Browse on Data Dictionary Tables program to amend the Report Writer data dictionary tables. You should never amend the standard data dictionary.

  • the Data Dictionary Table Listing program to print the details of data dictionary tables.

  • the Browse on Data Dictionary Columns program to create and maintain the Report Writer data dictionary column definitions.

  • the Browse on Data Dictionary Linkages program to define how tables are logically linked. By linking a table, the columns available in the linked table can be used when creating a report.

SQL vs C-ISAM

The majority of functions and features available in the Report Writer are equally available regardless of whether you are using a SQL Server or C-ISAM-based system.

There is limited support for mixing data in a single report from these two disparate systems.

This section summarizes the main differences between functionality when using SQL Server and C-ISAM based systems.

Limited features when using the Report Writer on SQL Server data

When defining and running reports in a SQL Server environment, you need to be aware of the following:

Sorting on user-variables - limited access to system variables

Generally you can only sort on a user-variable that references system variables if the system variables are $CompanyDate or $Date.

Sorting is prohibited if you defined a user-variable that references any other system variables.

Sorting on user-variables - limited access to other user-variables

Generally you can sort on user-variables that perform calculations unless those calculations reference other calculated user-variables (i.e. you cannot sort on a user-variable that references other calculated user-variables.)

You can sort on user-variables that reference other run-time prompt user-variables.

Date Arithmetic - hint to avoid overflow errors

When you define a user-variable that is calculated as the number of days between two dates (by subtracting one date from another) it is important that you define the user-variable with enough digits. A minimum of 5 digits is recommended to avoid a SQL statement overflow error.

Defining user columns and accessing them in a report

There are two primary steps to configure user columns in a report on a SQL Server based system. Once these steps have been completed for each user column you can access the column in the report in exactly the same way as for standard SYSPRO columns.

  1. Define the column in the SQL table against the database The new user-defined column can be added by a system administrator to store user-defined data or alternatively it can be a user-defined column that is defined as a substring of an existing column. For example it could be the first 4 characters of the StockCode column in the InvMaster table.

  2. Define the column in the Report Writer using the Browse on Data Dictionary Columns program. The name of the physical column in the table must be added to the data dictionary against the 'Column:' prompt and the Type must be defined as alphanumeric, numeric or date as appropriate. The Length must be defined for Char columns, the Length and Decimals for Numeric columns. Datetime columns are automatically handled appropriately.

    [Note]

    When you define a numeric column such as decimal (14,2) then you define Length as 14 and 2 decimals with the '[x] SYSPRO Format number' option selected. All other fields are documentary.

It is recommended that user-defined columns are named appropriately to avoid possible use by SYSPRO programs in the future. For example, you could start the Column name with 'Usr'.

SYSPRO supports column names up to 18 characters in length.

It is recommended that your columns are limited to char, decimal and datetime SQL Server datatypes as these are the types used by the standard SYSPRO system.

In addition it is important that you define user-defined columns as nullable (NULL) or supply a suitable default to avoid problems when SYSPRO programs insert data into the row.

  • 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.

Defining user tables and accessing them in a report

There are several steps to configure user tables to be accessed by a report on a SQL Server based system. Once these steps have been completed for each user table you can access the column from these tables in the report in exactly the same way as for standard SYSPRO columns.

  1. Create your user table and it's columns in the SYSPRO database where the rest of your SYSPRO data resides.

    [Note]

    SYSPRO supports table and column names up to 18 characters in length. It is recommended that your columns are limited to char, decimal and datetime SQL Server datatypes as these are the types used by the standard SYSPRO system. It is recommended that user-defined tables are named appropriately to avoid possible use by SYSPRO programs in the future. For example, you could start the Table name with 'Usr'.

  2. Typically populate this table with your data as applicable.

  3. Configure this table using the Browse on Data Dictionary Tables program. Typically you define the table name with the same name as the physical table defined in SQL Server. It is recommended that you assign an appropriate table description.

  4. Configure each column using the Browse on Data Dictionary Columns program. The name of the physical column in the table must be added to the data dictionary against the 'Column:' prompt and the Type must be defined as alphanumeric, numeric or date as appropriate. The Length must be defined for char columns, the Length and Decimals for numeric columns and datetime columns are automatically handled appropriately.

    [Note]

    When you define a numeric column such as decimal(14,2) this would be defined as Length 14 and 2 decimals with the '[x] SYSPRO Format number' option selected. All other fields are documentary.

  5. Once you have added the column definitions you should re-edit the user table definition and define the primary key. This generally consists of a list of columns that define the unique key to the table.

  6. You use the Browse on Data Dictionary Linkages program to configure any tables that logically link to or from your user tables.

Limited Report Writer features when using the Report Writer on C-ISAM data

When defining and running reports in a C-ISAM environment, you need to be aware of the following:

Sequencing - effect on performance

When you configure a report sequence the Report Writer uses the most appropriate C-ISAM index to read the data. The fastest method of reading data is to select a report sequence that matches the primary key of the primary table (and when you are using 1-many linked tables, the primary keys of these tables).

Alternatively, you can select the columns that make up one of the alternate indexes of the primary table. This also gives very good performance.

If no report sequence is defined, then reports run on a C-ISAM based system also use the primary key of the primary and linked tables for optimum performance.

If any other combination of columns is selected as the report sequence, then the Report Writer first sorts your data before generating the report. In many cases this will still give a good level of performance, but when large amounts of data are being sorted, then this can have a detrimental effect on the report generation time. In this case, consider using a different report sequence.

Sequencing - no data updates available

When you configure a report sequence that requires a sort to be generated before the report is generated (see above), you will not be allowed to update any data using the report.

Start Logic - configured for C-ISAM and not required on SQL Server

C-ISAM and SQL Server systems store and access data differently.

This section explains how the Report Writer can take advantage of 'Start logic' on C-ISAM based systems and does not require 'Start logic' on SQL Server based systems.

ISAM conditional Logic

ISAM files are processed by reading the files sequentially via a key (either the primary key or an alternate key). The Report Writer generally has to process the entire file to find information unless the initial part of the key is known.

The following examples illustrate this point:

  1. Read the Inventory master file from stock code 'X' to the end of the file.

    In this example, the report writer can logically 'Start' at stock code 'X' and read the rest of the items. Depending on the alphabetic distribution of the stock code this could save a considerable time rather than having to read all the items and only processing the items that start 'X' and onwards.

  2. Read the Inventory master file and look for items where the mass is > 100

    In this example, the Report Writer has to read ALL the items in the Inventory Master file and check each one for the required condition (Mass > 100). There is no other way of finding all items where the Mass is greater than 100 in a C-ISAM file.

    C-ISAM based systems can benefit enormously by appropriate use of the Start logic. If you do not use Start logic and only use the include logic, then the ISAM based system will still return the correct results except that every record is processed in the file and therefore the report may be noticeably slower than when using Start logic. Refer to Start Logic for information on using Start logic.

SQL Server conditional logic

When the Report Writer is running on a SQL Server based company, it simple issues a statement that includes an appropriate WHERE phrase.

Using the two previous examples: , the WHERE clause would be:

  1. Read the Inventory master file from stock code 'X' to the end of the file. The WHERE clause would be: WHERE StockCode >= 'X'

  2. Read the Inventory master file and look for items where the mass is > 100 The WHERE clause would be: WHERE Mass > 100

    The SQL Server optimizer engine determines how best to execute this statement - including determining if there is an index on the columns being accessed. Therefore when the Report Writer is reporting on data in a SQL Server system there is nothing else to be done for best performance.

The Start logic is not necessary for a SQL Server based system to get the best level of performance.

Limited Report Writer features when using both SQL and C-ISAM data

This section provides useful information that you should be aware of when defining and running reports that access data from both SQL Server tables and C-ISAM files in the same report.

Limits

The following table defines the limits for the Report Writer:

Description Limit
Maximum ISAM record length 4000
Maximum ISAM key length 200
Maximum alphanumeric field length 255
Maximum numeric field size 15.6-
Maximum columns per table 200
Maximum column conditions per report 200
Maximum linked tables per report 12
Maximum 1-Many linked tables per report 2
Maximum columns per report 200
Maximum columns per heading section 100
Maximum columns per group heading section 100
Maximum columns per detail section 100
Maximum columns per sub-total section 100
Maximum columns per total section 100
Maximum number of user variables 100
Maximum number of unique strings per report 100
Maximum number of unique numeric constants per report 100
Maximum report conditions 30
Maximum level of brackets in conditions 10
Maximum columns per sequence 10
Maximum sequences per report 10
Maximum sub-total levels and groups 5
Maximum user variables conditions per variable 3
Maximum lines in the page heading 10
Maximum number of run-time prompts per report 10
Maximum fields per user variable 12

Limitations when using a mixture of ISAM and SQL tables in the same report

  • If you intend to use a combination of ISAM and SQL tables in the same report, you must ensure that the primary table and any 1-Many linked tables are SQL Server-based.

  • You can include 1-1 linked ISAM tables in the report.

  • The Primary table cannot be an ISAM table if you are using mixed SQL and ISAM tables.

Permissions (table and column)

Each table and column can be assigned one of the following permissions:

  • None - No Access

  • Read

  • Read/Write

The default should normally be set to Read only.

You use the Browse on Data Dictionary Tables Data Dictionary Tables program to define table access.

You use the Browse on Data Dictionary Columns Data Dictionary Columns program to define table access.

The lower access setting of the table and column permission is used to determine the actual access allowed. This is shown in the table below:

Table Access Column Access Actual Column Access
None None None
None Read None
None Read/Write None
Read None None
Read Read Read
Read Read/Write Read
Read/Write None None
Read/Write Read Read
Read/Write Read/Write Read/Write

Report Writer Keys

This section describes the use of Key fields in the Report Writer.

Background

Reports developed for SYSPRO 6.1 and prior versions typically would treat key fields as 'alphanumeric' and use the full length of the key field. For example in SYSPRO 6.1 the Customer key was defined as 7 alphanumeric characters.

In SYSPRO 6.1 when a key field such as the Customer code was defined as numeric, it would be shown with the full 7 numeric digits (e.g. '0001234'). In addition the operator would be required to define conditions in the Report Writer using a 7 character alphanumeric user variable or a string defined as 7 characters - e.g. '0000001'.

SYSPRO 7 stores many key fields in significantly larger field sizes. For example the Customer key is now 15 characters. To avoid an operator having to work with 15 numeric digits when the key is defined as numeric, we allow the definition of a 'presentation length' against each key field (Company Setup - Keys).

The presentation length refers to how the data is displayed in SYSPRO and is what the system assumes to be the maximum length of the field.

When migrating a SYSPRO 6.1 company to SYSPRO 7, the presentation length is set to the same value as the original key size in SYSPRO 6.1. For example the default presentation length for the Customer key is 7 characters in SYSPRO 7 after migrating a company from SYSPRO 6.1.

In SYSPRO 7, for alphanumeric keys this just means that the first 7 characters will typically be used/prompted - however for numeric keys the 'right-most' 7 characters will be used.

Irrespective of the presentation length specified, the information is stored in the database in its full length. For example Customer key '0001234' in SYSPRO 6.1 is stored as '000000000001234' in SYSPRO 7. This is the full 15 digit numeric customer key - in effect eight zeros were prefixed during the migration to SYSPRO 7.

Keys in the current version of SYSPRO

The properties defined for Key fields in the Company Setup program (Keys) determine how the fields are printed in Report Writer reports.

The presentation length refers to how the data is displayed in SYSPRO and is what the system assumes to be the maximum length of the field. Irrespective of the presentation length specified, the information is stored in the database in its full length.

[Note]

It is assumed that you have imported (or re-imported) the latest Data dictionary (Data Dictionary Import).

The following rules apply to Key fields:

  • When adding a new key column to a report, the edit length defaults to the presentation length (i.e. if the presentation length against the customer key is 7 characters, then the edit length will default to 7 characters).

  • When printing the report (or generating an extract file or XML report) the edit length will be used. If the key is defined as numeric, then the last ‘x’ characters of the key value are printed.

    For example, assume that in the Company Setup program (Keys) the Customer key is defined as numeric and the customer code is '000000000001234'.

    • If the edit length is defined as 7 with no leading zero suppression, then the field will be shown as '0001234'

    • If the edit length is 7 with leading zero suppression enabled, then the field will be shown as '1234'

  • In all cases, if a key is defined as alphanumeric (or the key contains alpha characters) then the left-most ‘x’ characters will be shown (where 'x' is the edit length as defined in the report). Leading zero suppression does not apply.

  • When you import the data dictionary using the Data Dictionary Import program, the datatype against each field is transferred to each column in the Report Writer data dictionary.

    • The following datatypes will show the correct default presentation length when adding a field to the report: Asset, BuyingGroup, AssetCapex, Customer, InvoiceAr, DeliveryNote, DispatchNote, Grn, GtrRefernce, InspReference, Job, Lot, PaymentNumber; PurchaseOrder, Quote, RmaNumber, Requisition, SalesOrder, Serial, StockCode, Supplier, PaymentNumberAr, CollectorNumber.

    • Also note that the datatype is not always the same as the column name.

      For example in the ArCustomer table there are the following columns:

      • Column: Customer - Dataype:Customer

      • Column: MasterAccount - Dataytpe: Customer

      For both of these columns, the 'Customer' key type field definitions will be used as they both have a datatype defined as 'Customer'.