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.
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.
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.
When defining and running reports in a SQL Server environment, you need to be aware of the following:
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.
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.
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.
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.
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.
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.
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.
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.
Create your user table and it's columns in the SYSPRO database where the rest of your SYSPRO data resides.
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'. |
Typically populate this table with your data as applicable.
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.
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.
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. |
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.
You use the Browse on Data Dictionary Linkages program to configure any tables that logically link to or from your user tables.
When defining and running reports in a C-ISAM environment, you need to be aware of the following:
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.
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 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:
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.
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.
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:
Read the Inventory master file from stock code 'X' to the end of the file. The WHERE clause would be: WHERE StockCode >= 'X'
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.
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 |
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.
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 |
This section describes the use of Key fields in the Report Writer.
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.
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.
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'.