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

We recommend that even if you have a previous version of the Report Writer installed you still 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 following programs:

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

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

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

  • the 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 that are 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 R/W 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 Report Writer is limited to numeric columns with 12 integers and 6 decimals.

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 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 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 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 12.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 Data Dictionary Tables program to define table access.

You use the 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