User’s Guide for Windows
RIS Report Writer
August 1994
DNA115310
All warranties given by Intergraph Corporation about equipment or software are set forth in your purchase contract, and nothing stated in, or implied by, this document or its contents shall be considered or deemed a modification or amendment of such warranties.
The information and the software discussed in this document are subject to change without notice and should not be considered commitments by Intergraph Corporation. Intergraph Corporation assumes no responsibility for any error that may appear in this document.
The software discussed in this document is furnished under a license and may be used or copied only in accordance with the terms of this license.
No responsibility is assumed by Intergraph for the use or reliability of software on equipment that is not supplied by Intergraph or its affiliated companies.
Trademarks
CLIX, Intergraph, and RIS are registered trademarks of Intergraph Corporation. DMRS, I/NFM, and TD1 are trademarks of Intergraph Corporation. All other brands and product names are trademarks of their respective owners.
Copyright
19100 Intergraph Corporation All Rights Reserved
Including software, file formats, and audiovisual displays; may be used pursuant to applicable software license agreement; contains confidential and proprietary information of Intergraph and/or third parties which is protected by copyright and trade secret law and may not be provided or otherwise made available without proper
authorization.
RESTRICTED RIGHTS LEGEND
Use, duplication, or disclosure by the government is subject to restrictions as set forth in subparagraph (c) (1) (ii) of The Rights in Technical Data and Computer Software clause at DFARS 252.227-7013 or subparagraphs (c) (1) and (2) of Commercial Computer Software — Restricted Rights at 48 CFR 52.227-19, as applicable.
Unpublished — rights reserved under the copyright laws of the United States. Intergraph Corporation
Table of Contents
__________________________________________________________________________________________________________________________________________________
Table of Contents
1. Before You Begin ... 1 - 3 1.1 Using On-line Help ... 1 - 5 1.1.1 Parts of the Help Window ... 1 - 5 2. Getting Started ... 2 - 3 2.1 RIS Report Writer Structure and Functions ... 2 - 3 2.2 Changes to RIS Report Writer ... 2 - 4 2.3 Creating a Database ... 2 - 4 2.4 Attaching a RIS Schema ... 2 - 4 2.5 Templates ... 2 - 5 2.5.1 Template Libraries ... 2 - 6 2.6 Join Columns ... 2 - 6 2.6.1 Multi-Schema Joins ... 2 - 7 2.6.2 Outer Joins ... 2 - 7 2.6.3 Edit Relations ... 2 - 8 3. Reports ... 3 - 3 3.1 Report Types ... 3 - 3 3.1.1 Default Reports ... 3 - 3 3.1.2 Blank Reports ... 3 - 5 3.1.2.1 Unrelated Tables Reports ... 3 - 5 3.2 Accessing the Report Builder ... 3 - 6 3.3 Processing Reports ... 3 - 6 4. Edit Script ... 4 - 3 5. Template Librarian ... 5 - 3 Accessing the Template Librarian ... 5 - 3 6. Database Edit ... 6 - 3
6.2 Creating and Using Query Criteria ... 6 - 5 6.2.1 Using Query Functions ... 6 - 5 6.3 Editing Database Values ... 6 - 7 7. SQL Statements ... 7 - 3 8. RIS Report Writer Commands ... 8 - 3 Add Query (Database Edit) ... A - 1 Change All (Database Edit) ... C - 1 Change Row (Database Edit) ... C - 3 Column (Report Builder) ... C - 4 Copy Field ... C - 8 Copy Line ... C - 10 Counter (Report Builder) ... C - 12 Current Row (Database Edit) ... C - 14 Date ... D - 1 Define Structure ... D - 3 Delete All (Database Edit) ... D - 9 Delete Field ... D - 10 Delete Line ... D - 12 Delete Row (Database Edit) ... D - 13 Delete Space ... D - 14 Derived ... D - 15 Dump Template ... D - 17 Edit Code (Database Edit) ... E - 1 Edit Field ... E - 6 Edit Line ... E - 7 Edit Query (Database Edit) ... E - 10 Edit Report Parameters ... E - 11 Exit ... E - 13 Field Format (Report Builder) ... F - 1 Fields On/Off (Database Edit) ... F - 3 Field Operations (Report Builder) ... F - 4 Initialize Query (Database Edit) ... I - 1 Insert Row (Database Edit) ... I - 2 Insert Space (Report Builder) ... I - 3 Key Column (Database Edit) ... K - 1 Line Operations (Report Builder) ... L - 1 Line Script ... L - 2 Load From Library (Report Builder) ... L - 5 Locate Graphics (Database Edit) ... L - 8 Modify Sort ... M - 1 Move Field ... M - 5 Move Line ... M - 7 New Blank (Report Builder) ... N - 1 New Default (Report Builder) ... N - 3
Numeric ... N - 6 Page ... P - 1 Place Field (Report Builder) ... P - 3 Place Line ... P - 5 Process Query (Database Edit) ... P - 8 Process Report ... P - 9 Report (Database Edit) ... R - 1 Review (Database Edit) ... R - 2 Save Report ... S - 1 Text (Report Builder) ... T - 1 Text Size ... T - 3 Time ... T - 4 Undelete Field (Report Builder) ... U - 1 Undelete Line (Report Builder) ... U - 2 Update Active (Database Edit) ... U - 3 Update Row (Database Edit) ... U - 4 User Variable (Report Builder) ... U - 5 Appendix A: RIS Report Writer Environment Variables ... AA - 3 Appendix B: RIS Report Writer Reserved Tables ... BB - 3 B.1 Tables Needed for Raster Graphics ... BB - 3 B.2 Unique Row Identification ... BB - 3 B.2.1 mslink Column ... BB - 4 B.2.2 Key Columns (mscatalog Table) ... BB - 5 B.2.3 Unique Index ... BB - 7 B.3 Coded Columns and mscodelist ... BB - 7 B.4 mscolumns ... BB - 9 Appendix C: Timestamp Columns ... CC - 3 Appendix D: RIS Report Writer Error Messages ... DD - 3 Glossary ... GL - 3 Index ... IN - 3
Before You Begin 1 - 1
Before You Begin
Before You Begin 1 - 3
__________________________________________________________________________________________________________________________________________________
1.
Before You Begin
Document Purpose
This document describes the functions and commands in the Intergraph RIS Report Writer product. This document is intended for database system administrators and users.
Document Prerequisites
This document assumes that you:
Understand SQL database terminology and structure.
Are familiar with the Intergraph Relational Interface System (RIS).
Related Documentation
You can refer to the following documents for additional information: DNA1133 DB Access Administrator’s Guide
DNA1001 DB Access Programmer’s
DNA0032 Relational Interface System (RIS) for Windows NT SQL User’s Guide
Document Conventions
Filenames and directory paths appear in italic typeface. However, the italic typeface is also used for emphasis of new words or important phrases. For example:
c:\windows
Command names, menu names, tools, system prompts and messages, and keys may appear in boldface type. For example:
File menu
— OR — Press Enter
The word mouse refers to the 2-button or 3-button mouse.
The word select means to select a command by pressing the left mouse button over a menu command or by pressing the Alt key and the underlined character
simultaneously.
The word choose means to choose a button or icon by pressing the left mouse button over a Toolbar button, or application icon.
The word reset means to terminate a command initiated with the mouse. Reset by pressing the right mouse button.
The word identify means to define an area or place graphic elements in a graphics file. For PCs, identify with the left mouse button.
The phrase key in generally means to enter data into a field on a dialog box. To advance to the next field, use the Tab key.
Do not use the Enter key to advance to the next field. This key is mostly used as the default key to accept a dialog box instead of pressing the OK button.
System key-ins, keywords, and programming code segments, appear in monospaced type. For example:
main ( )
— OR —
commit
Before You Begin 1 - 5
Words that appear in angle brackets, < >, are identifiers or names that you must supply, or dynamic information that can change for each error message. For example: ERROR: Error opening the file <filename>
Phrases in square brackets, [ ], are optional phrases.
Curly braces contain several options (used in conjunction with a logical OR symbol ( | )) or phrases that can be repeated (used in conjunction with [, ...]). A comma followed by a series of three periods in square brackets ([, ...]) indicates that the last phrase contained within curly braces ({}), or the last item, can be repeated numerous times (separated by commas).
For example: { <column> <data type> } [, ...] means that numerous column names and associated data types can be specified (separated by commas).
The logical or symbol ( | ) separates phrases or keywords within curly braces ({}) that can be used alone but not together.
For example: { user| database} means that either the userkeyword or the
databasekeyword can be specified, but not both. This symbol notes important information.
This symbol cautions about operations that can cause limited damage.
This symbol warns about operations that can cause severe damage.
1.1
Using On-line Help
On-line Help is an on-line reference tool accessible at any time the application is in use. The on-line Help contains a description for each command and tool and step-by-step procedures for common tasks. For example, if you need to perform a certain task, search and display the topic. You can move or resize your application and Help windows so that they are next to each other. This lets you follow the procedures without having to search for the pages in the documentation.
1.1.1
Parts of the Help Window
To view the on-line Help, select Contents from the Help menu. To get more specific information, select one of the major topics or perform a search on a specific topic.
Before You Begin 1 - 7
Use To
Contents Display a listing of the table of contents for the on-line Help file.
Search Locate information about a certain topic that you enter in the Search box.
Back Take you back to the previous Help topics you
have already viewed.
History Display a sequential list of every Help topic you have viewed during your current Windows session.
Find Display a dialog box used to retrieve partial or
full text strings in the help file. Use the
Hints button for information on constructing
your search query.
<< View the previous topic in a series of related topics. The button is dimmed when you reach the first topic in the series.
>> View the next topic in a series of related topics. The button is dimmed when you reach the last topic in the series.
If the graphics in the on-line Help appear distorted, check your graphics driver. If you are using an Intergraph TD1 machine, the S3 1024x768 256 color (Large Font) distorts the graphics slightly. Changing to the (Small Font) version corrects the display. If you are using other drivers, check with your PC manual for information about available graphics drivers.
Getting Started 2 - 1
Getting Started
Getting Started 2 - 3
__________________________________________________________________________________________________________________________________________________
2.
Getting Started
RIS Report Writer is a report writer and database editor that lets you access ANSI/ISO SQL-standard databases supported by the Intergraph Relational Interface System (RIS). RIS Report Writer also runs in conjunction with alphanumeric user data and ASCII data. You can define the input structures for these types of data so that you can design and run reports. You can customize user data reports provided with Intergraph applications. For more information, see these sections:
2.1 RIS Report Writer Structure and Functions 2.2 Changes to RIS Report Writer
2.3 Creating a Database 2.4 Attaching a RIS Schema
2.5 Templates
2.6 Join Columns
2.1
RIS Report Writer Structure and Functions
Two fuctional areas are included in the RIS Report Writer product: Reports and Utilities.
Reports — This function lets you design report templates, which process information
from a relational database to generate report output. You can:
— Interactively place column fields to report on one or more tables in one or more databases.
— Place fields to display text, the date, time, and page number, and to provide numeric information (totals, averages, and so forth).
— Establish sort criteria, print conditions, and reset conditions for lines and fields in the report.
— Use system and user-defined variables to build scripts that provide more flexibility in formatting report output.
Utilities — This function includes Database Edit and SQL Statements.
— Database Edit gives you a graphic window into the attached database. You can edit rows in the database, create query criteria to search for rows with common
properties, or generate reports to review the universe of rows that the query criteria create.
— SQL Statements let you create, store, and recall commonly used Structured Query Language statements.
2.2
Changes to RIS Report Writer
For a complete listing of major changes incorporated since the last release, see the c:\win32app\ingr\risrpt\README.TXT file.
2.3
Creating a Database
Before you use RIS Report Writer, you must create a database with your relational database management system (RDBMS).
Each RDBMS has specific requirements for creating a database. See the RDBMS-specific documents for information.
2.4
Attaching a RIS Schema
RIS Report Writer attaches to a database through the Intergraph Relational Interface System (RIS). RIS is a generic relational database interface that isolates the differences in specific vendors’ relational database management systems (RDBMSs).
Each user of a database must have a RIS schema created for that database. A schema defines a unique database/user combination in the commercial RDBMS. See the Relational Interface System (RIS) for Windows NT SQL User’s Guide for information on creating schemas.
The following methods, listed in order of precedence, attaches a schema to RIS Report Writer.
If no schema is attached when RIS Report Writer is invoked, you can specify a schema in the Schema field on the main dialog. You can key in the schema name or select the list indicator at the end of the field to display a list of available schemas. Select a schema from the list.
You can specify a RIS schema with the -s command line option when you invoke RIS Report Writer from the DOS prompt. For example:
Getting Started 2 - 5
You can set the environment variable DBA_SCHEMA in your propertities under the command line on c: drive or the autoexec.bat file.
See the section RIS Report Writer Environment Variables for information on using RIS Report Writer environment variables.
2.5
Templates
RIS Report Writer stores various types of output as templates. For example, you can save reports created with the Report Builder as report templates in a template library. Templates can be used repeatedly or they can serve as the starting point for new templates.
The following types of templates can be stored in a template library: Applications Bitmap Dialogs Design options Query criteria Palettes Relations Reports Report structures Scripts SQL statements Status Bar Toolbars
You must assign a name for a template when saving it to a template library. Template names must consist of 20 or fewer alphanumeric characters with no embedded spaces. It is recommended that you use the underbar character (_) between words.
2.5.1
Template Libraries
A template library is a data file in which you can store templates. You can create multiple libraries and group templates according to application, use, privilege, or type.
A special command-line process, the Template Librarian (templib), lets you manipulate templates and template libraries. See the section Template Librarian for more information. The Template Librarian is also located on the RIS Report Writer main menu.
Any time you see a Library field on a dialog, you can create a new template library by keying in a template library name that does not exist (full or relative filename) and answering Yes to create it.
To attach a template library, you can specify a library name:
1. Using the -l option when you invoke RIS Report Writer from the command line under properties on c: drive. For example:
risrpt -l <library>
2. In the Library field of the Load From Library dialog.
3. In the DBA_LIB environment variable. (You can also specify a list of template libraries in the DBA_LIB_LIST environment variable.)
2.6
Join Columns
RIS Report Writer lets you relate the data in any two RIS-supported database tables that share a common column. These join columns are not required to have the same name, but they must have common values.
The following figure shows the columns for the manufacturer, parts, and sale tables created by the auto.sql file. Potential join columns are highlighted.
Getting Started 2 - 7
2.6.1
Multi-Schema Joins
You can use multi-schema joins to relate information from more than one schema. Multi-schema joins require that you specify a Multi-schema list, using the -S command-line option or the DBA_SCHEMA_LIST environment variable.
The primary schema in a multi-schema join must have select access to the secondary schema’s tables. Use the RIS GRANT command to grant select access.
2.6.2
Outer Joins
The Join Columns dialog lets you specify whether or not a join should be an outer join (as opposed to a standard join).
If you select Yes, the resulting universe includes all occurrences from the primary table whether or not it has a corresponding row in the secondary (join) table.
If you select No, the resulting universe includes only those occurrences for which the join column values are equal (for example, parts.part_num = sale.part_num).
In general, outer joins process faster than standard joins across schemas. Standard joins process faster if the tables are in the same schema. Query criteria for the secondary table are ignored for outer joins, but used for standard joins.
2.7
Edit Relations
The Edit Relations dialog (available from Schema Edit) lets the Database Administrator graphically depict relations between tables in the database and store this information in a DBA template library. Once a relations template has been created and attached to a dialog/report from the Edit Dialog or Edit Report dialogs, the dialog/report designer can place columns from joined tables without being aware of the complexity of the database. Relations can be simple, as in previous versions with the Join Columns dialog, or complex, consisting of several tables and multiple columns to each table join. DBA can find the shortest available route between tables when performing runtime join operations. If no relations template is attached, or if no path exists between the tables to be joined, the Join
Columns dialog is displayed the first time a join is specified. Existing dialogs/reports will
work as before without change. In fact, the relations template is ignored unless the Join
Columns dialog for the joined column is blank.
DBA 5.0 Edit Relations does not support multi-schema joins or outer joins. However, the
Join Columns dialog can be used to specify simple multi-schema joins and outer joins, as in
past versions.
The following commands are available from the Edit Relations dialog:
Template — The name of the relations template.
Description — The description of the relations template.
Load Relations — Displays the Load from Library dialog for loading an existing
relations template.
Save Relations — Displays the Save Template dialog for saving a new or changed
relations template.
Add Join — Displays the Join Columns dialog for adding new joins.
Delete Join — Deletes a join, by selecting the connecting line between the join
columns.
Move Table — Aligns the table fields as wanted (added joins are placed
Getting Started 2 - 9
Reports 3 - 1
Reports
Reports 3 - 3
__________________________________________________________________________________________________________________________________________________
3.
Reports
RIS Report Writer lets you create, load, modify, and save report templates. You can specify the layout of different types of lines and fields within the report template. When the template is processed, RIS Report Writer reads information from a relational database, ASCII file, binary file, or element list file and formats it to your specifications.
For more information, see these sections: 3.1 Report Types
3.2 Accessing the Report Builder 3.3 Processing Reports
3.1
Report Types
The Report Builder provides several options for creating reports. You can define templates to report on database tables, ASCII files, binary files, or user data in design files. Blank and default reports are available for all four report types. The four report types are:
Database reports — Loads a default template, a blank template, or an existing
template from a template library.
ASCII reports — Creates an ASCII structure template or loads an existing ASCII
report template from a template library. See the section Define Structure.
Binary reports — Creates a binary structure template or loads an existing binary
report template from a template library. See the section Define Structure.
User data reports — Creates a user data structure template or loads an existing user
data report template from a template library. See the section Define Structure.
3.1.1
Default Reports
The New Default (Report Builder) command reads the structure of the attached database or structure and generates a default report template for the selected table. You can select columns to be included in or excluded from the default template when it is loaded.
The following is the default report template for the manufacturer table in the sample database.
In a report template, the column on the left indicates the line type. A
represents alphanumeric data, I represents integer data, F represents floating-point data, D represents derived field, and C represents a coded column field. The number of As, Is, Fs, Ds, or Cs represents the field length for the column in the database.
A default template has the following characteristics: Sort criteria
— One sort key (the first selected column of the table) Top lines
— Title line (the table name) – Date, Time, and Page line
– Column Header lines (underlined column names) Body lines
— Detail line. Printed once for each row in the input universe, it contains the selected columns. You can specify 1 to 2000 characters per line for the default report on the
Edit Report Parameters dialog. If the selected columns overrun the current
characters per line, the line is extended to fit all selected columns.
In addition, the line contains a counter field which counts rows and resets when sort key 1 breaks or changes.
Reports 3 - 5
— Subtotal line. Printed when sort key 1 breaks; it totals, prints, and resets the counter field and totals, prints, and resets any numeric columns included in the report.
Final line
— The system totals and prints the counter field, and totals and prints any numeric columns included in the report in this line.
For more information, see the section New Default (Report Builder).
3.1.2
Blank Reports
The New Blank (Report Builder) command loads a blank report template for a selected table. Unrelated Tables loads a blank template that lets you report on tables that have no relationship.
A blank report contains no lines or fields. You can design a template with the features and functionality needed.
3.1.2.1
Unrelated Tables Reports
To report on tables that do not share join columns, you must create an unrelated tables report. Possible reports on unrelated tables include reports in which preface information comes from one table and body information from another.
You must place one or more Single Table lines for each table on the report template. Fields for a table must be placed on a line associated with that table. Common lines, such as page headings, are placed on All Tables lines. You must set up separate sorts for each table in the report.
For more information, see these sections Join Columns, New Blank (Report Builder), and Modify Sort.
3.2
Accessing the Report Builder
To access the Report Builder, click on the third icon on the Toolbar dialog.
The Report Builder dialog displays on top of the RIS Report Writer main dialog.
RIS Report Writer loads the Report Builder menu bar.
3.3
Processing Reports
You can access the Process Report command from the Reports area of the RIS Report
Writer main dialog. This command lets you process report templates that have been created
Edit Script 4 - 1
Edit Script
Edit Script 4 - 3
__________________________________________________________________________________________________________________________________________________
4.
Edit Script
The Edit Script button on the RIS Report Writer main dialog calls the Edit Script dialog. The Edit Script dialog lets you create or edit scripts. All major functions designed for runtime users can be manipulated by scripts. In addition, System commands can be included within scripts.
Scripting assumes a knowledge of the C programming language. In fact, the structure of a script is in the form of a C function. For descriptions of supported functions, see the DB Access Programmer’s Guide or the c:\win32app\ingr\dba\doc\script.doc file.
Dialog Options
When you select Edit Script, the Edit Script dialog appears.
Name — Specifies the name of the script. The name of the script template must be the
Description — Contains a description of the script (up to 50 characters).
Load from ASCII file — Specifies the name of an ASCII file to load. You can edit the
file and save it as a script.
Save to ASCII file — Specifies the name of an ASCII file to contain the current script.
Scripts saved as ASCII files can be printed.
Debugger — A check box that determines whether the Script Debugger is off or on.
When the toggle is set to On, a Debugger icon appears on the screen. For more information, see the section Script Debugger.
Store Compiled Scripts — A check box that determines whether the compiled version
of the script is stored in the template library. When the toggle is set to On, the compiled script is stored in the template library so that it does not have to be recompiled each time the application is invoked. When the toggle is set to Off, the script is not stored in the template library and is recompiled each time the application is invoked.
Load Script — Calls the Load from Library dialog. You can select a script from the
list of available script templates. For more information, see the section Load From Library (Report Builder).
Check Script — Displays a Script Errors form that displays any detected errors and
the line numbers where the errors were detected. (Usually an error is detected somewhere just after it occurs.) If you select an error line in the Script Errors form, the cursor moves to the point in the script (on the Edit Script dialog) where the error was detected.
Save Script — Calls the Save Template dialog. You can save the current script as a
template in the current template library. For more information, see the section Save Options.
Options — Calls the Script Options dialog, which is described in the following
section.
Text field — Displays the current script. When the Edit Script dialog first appears,
this field is in edit mode. You can key in a script or edit one that has been loaded.
Clear — Clears the text field on the Edit Script dialog. It also clears the delete
buffer.
Delete — Puts the text field in delete mode; any script statement you select is deleted
and stored in a delete buffer. The delete buffer stores deleted lines until you exit delete mode or select Clear. Select Delete again to exit delete mode.
Undelete — Inserts the current delete buffer at the marker (highlighted) line in the
text field. If no marker line exists, the delete buffer is inserted at the bottom of the text.
Edit Script 4 - 5
Select a line to make it a marker line. The text field cannot be in delete or edit mode. (That is, the Delete, Undelete, and Edit buttons are not depressed.)
Edit — This button places the text field in edit mode. You can key in or edit scripts. Use Editor — This button invokes the preferred editor (defined by the EDITOR
environment variable). If EDITOR is not defined, Notepad is the default editor. — The name of the current editor appears in the field to the right of the Use Editor
button. To change editors, select and clear this field, and key in the name of the new editor.
Options Button
When you select the Script Options button, the Script Options dialog appears.
Run Current Script — This button runs the current script in the editor and accepts
any arguments. Some scripts, such as one that requires input from a dialog, cannot run with this command. The Arguments field is used to place any literal arguments that normally appear inside the parentheses of a function call. Separate the literal
arguments with commas, but do not key in the parentheses.
Delete Globals — This button deletes all global variables from memory. This is useful
if you need to change a global type.
Run Initial Script — By default, this button runs the script specified by
DBA_INITIAL_SCRIPT. You can specify another script in the Initial Script field. (Initial scripts can be used to initialize global variables and require no arguments.) This button also deletes all global variables.
Verbose Errors and Warnings — The gadgets in this area let you manipulate
verbose error checking mode. Use of this mode eliminates the need to check status returns in script function calls.
— Edit Settings. This button calls the Verbose Error Settings dialog, which lets you select which function types undergo verbose error checking (standard C, DB Access, I/Forms, I/NFM, RIS, or all). These settings can be saved and loaded as design options. For more information see the section Save Options.
— Off/On. This toggle turns verbose error checking mode off or on.
Script Debugger
The Script Debugger is a troubleshooting tool for scripts. You can use the Script Debugger to:
Set breakpoints to stop a script at specified points in processing. Review the variables associated with a script.
Set values for these variables.
Set verbose error checking mode options. Return to the Edit Script dialog.
Script Debugger Dialog Options
The Script Debugger dialog is invoked from the Edit Script dialog. When you set the
Debugger toggle to On, a Debugger icon appears on the screen. You can move the icon by
pressing and holding the mouse button and moving the mouse.
When any dialogs associated with the Script Debugger are collapsed, a
Debugger icon appears on the screen.
Edit Script 4 - 7
Edit Script — This button returns you to the Edit Script dialog. The last script
function displayed in the Script Debugger text field appears in the Edit Script text field.
Single Step — This button steps to the next line of the script code being debugged.
Any function called is executed.
Step Into — When a script calls a user-defined script function, this button displays the
called function. Execution stops on the first line of the called function. Step Into acts as a single step on button functions and dynamically loaded functions.
Breakpoints — This button calls the Set/Review Breakpoints dialog. For more
information, see the section Set/Review Breakpoints.
Go — This button runs the script to completion or to the next breakpoint.
Continue — This button runs the current function to completion. Execution stops on
the line of script following the point where the function is called.
Variables — This button calls the Review Variables dialog. For more information,
see the section Review Variables.
Abort — This button stops execution of the current script and any other gadget
Visual Script Trace — This toggle turns the Visual Script Trace on and off. When
the toggle is On, the debugger displays functions as they are called, highlighting the line being executed.
Break on Script Gadget — This toggle sets the Break on Script switch. When the
toggle is On and a gadget (or report line, dialog script, or notify script) that has an associated script is activated, the debugger becomes uncollapsed. Script processing is suspended just as if a breakpoint had been set.
Verbose Errors and Warnings — The gadgets in this area let you manipulate
verbose error checking mode. Use of this mode eliminates the need to check status returns in script function calls.
— Edit Settings. This button calls the Verbose Error Settings dialog, which lets you select which function types undergo verbose error checking (standard C, DB Access, I/Forms, I/NFM, RIS, or all). These settings can be saved and loaded as design options (see the section Save Options).
— Off/On. This toggle turns verbose error checking mode off or on.
Breakpoints — This toggle turns all breakpoints on (Enabled) or off (Disabled)
without calling the Set/Review Breakpoints dialog. This does not affect the automatic break on gadget/line scripts.
Name — This field specifies the script function being debugged. Description — This field contains a description of the script function.
Edit Script 4 - 9
Set/Review Breakpoints
When you select Breakpoints from the Script Debugger dialog, the Set/Review
Breakpoints dialog appears.
Break on Function — This field displays the script templates available from the
current library. You can select functions from this list. Script processing stops when a selected function is called.
Clear All Breakpoints — This button clears all functions and line breakpoints. Undo Changes — This button resets all breakpoints to the way they were when the Set/Review Breakpoints dialog was invoked.
Function — This field specifies which function’s line breakpoints appear in the Break on Line field. You can key in a function or select one using the list indicator.
Edit Line Breakpoints — This button starts Add/Remove Line Breakpoints mode.
In this mode, you can select lines displayed in the Script Debugger dialog text field to add them as line breakpoints (displayed in the Break on Line field). You can remove the line breakpoint by deselecting the line (that is, selecting the line a second time) on the Script Debugger dialog.
Delete Line Breakpoints — This button removes line breakpoints from lines in the Break on Line field. Select Delete Line Breakpoints. Then select the lines to
Break on Line — This field specifies the lines of a function on which the Script Debugger stops processing.
Review Variables
When you select Variables from the Script Debugger dialog, the Review Variables dialog appears.
Function — This field specifies which function’s variables are currently displayed.
You can key in a function or select one using the list indicator.
Name — This field specifies the name of the variable.
Type — This field gives the type of the variable, its level of indirection, if its an array
and the numbers and sizes of dimensions.
Value — This field specifies the value of the variable.
Most pointers are displayed as two integers separated by a colon. Some pointers, such as dba_errtxt, appear as a single integer.
To change a variable’s value, position the cursor on the value to be changed and key in a new value.
Template Librarian 5 - 1
Template Librarian
Template Librarian 5 - 3
__________________________________________________________________________________________________________________________________________________
5.
Template Librarian
The Template Librarian (templib) is a standalone utility. It is an integrated part of the administrator which provides many of the features of templib, RIS Report Writer template libraries. You can use the Template Librarian to:
List the templates in a specified template library. Copy templates from one template library to another.
Extract a template into a text file (only available from templib). Delete a template.
Compress a template library to recover space after templates have been replaced or deleted.
The Template Librarian is an embedded DBA application. Most of the code behind it are scripts. These dialogs and scripts can be customized and added to by the user.
Accessing the Template Librarian
The Template Librarian screen appears.
Before you can copy, delete, or move libraries, you must select the libraries necessary for the operation. To select one library, just click on it. To select more than one, hold the Ctrl key down when using the mouse.
Copy Libraries
To copy a library select Template and then select Copy from the pulldown menu.
Template Librarian 5 - 5
Copy selected templates to — The name of the library to copy to. If it does not exist,
you are prompted to create it.
Browse — The Browse button can be used to activate the standard Open dialog to
search for the library to copy to.
Replace templates —The Replace templates option must be checked for a DBA
existing template to be overwritten.
Move Libraries
To move a library select Template and then select Move from the pulldown menu.
When Move is selected the Move dialog appears.
Move selected templates to — The name of the library to move to. If it does not
exist, you are prompted to create it.
Browse — The Browse button can be used to activate the standard Open dialog to
search for the library to move to.
Replace templates —The Replace templates option must be checked for a DBA
Delete
When you select Template, then Delete, a confirmation dialog box displays.
Undelete
To retrieve previous versions of a template or recover a template that has been deleted, you first have to see the deleted/modified templates. Then choose the templates to undelete. (The Undelete command is disabled until this point). Select Undelete, the template is moved from the Deleted list and becomes visible again when you do another Show Deleted (to turn the mode off). If there is already a nondeleted template in the library with the same name and type, it becomes deleted (they are basically swapped).
All deleted templates are permanently removed (and do not show up in the deleted list) if a Compress is performed on the template library.
Select All
When you select Template, then Select All, the template librarian marks everything in the Current List of Templates.
Template Librarian 5 - 7
Unselect All
When you select Template, then Unselect All, the template librarian undoes the Select
All command.
Template Types
To see the template types, select View and then select Template Type from the pulldown menu.
When you select Template Types, the Template Types Filter dialog appears.
This is used to control what types of templates are shown in the Template List.
Sort By
To sort a library select View and then select Sort By from the pulldown menu.
Template Librarian 5 - 9
Show Deleted
To look at the deleted templates, select View and then select Show Deleted from the pulldown menu.
When you select Show Deleted, a list of deleted templates dialog appears.
Compress
This command only affects the current library.
This permanently removes any deleted templates.
Bulk Compile
To do a bulk script compile, select Tools and then select Bulk Compile from the pulldown menu.
When you select Bulk Compile, the Bulk Script Compiler dialog appears.
This compiles all script templates in all the libraries in the DBA_LIB_LIST into stored script objects. For information see the section Store Compiled Scripts and Object libraries. If any of the scripts do not compile, use the Review Errors button to view any errors.
Template Librarian 5 - 11
Cross Reference
To generate a cross reference report, select Tools and then select Cross Reference from the pulldown menu.
When you select Cross Reference, the Cross Reference dialog appears.
Object Template Library — This field has the library name that is created in the
bulk script compile command.
Report Output Filename — This is what the report file is named.
Report Description — This field gives you a description of the report to be run.
A cross reference report is really a DBA ASCII Report. You can choose which cross reference you want to generate:
FUNCTION_REF — Lists each function and indicates which scripts call it.
FUNC_SYMBOL_DUMP — Lists each function, along with all automatic variables,
GLOBAL_REF — Lists each global variable and indicates which scripts reference
them.
INCLUDE_REF — Lists each include file/include template and which scripts include
them.
All these reports can be customized and new ones added. For more information see the file c:\win32app\ingr\risrpt\support\tl.tlb.
Database Edit 6 - 1
Database Edit
Database Edit 6 - 3
__________________________________________________________________________________________________________________________________________________
6.
Database Edit
The Database Edit function lets you review, add, delete, or modify rows in an attached relational database. You can manipulate individual rows or a universe of rows. You can process report templates from Database Edit.
For more information, see these sections: 6.1 Accessing Database Edit
6.2 Creating and Using Query Criteria 6.3 Editing Database Values
6.1
Accessing Database Edit
When you select Utilities from the RIS Report Writer menu bar, the pulldown menu appears. Select the Database Edit command and the Database Edit dialog appears. If a schema and table are active, the column list for the table is displayed. If there is not a schema present enter one in the schema field and press the Return key. The schema is attached, and you can select a table in the table field.
Initialize Query — This button initializes (clears) the current query criteria. For
more information, see sections Creating and Using Query Criteria and Initialize Query (Database Edit).
Edit Query — This button lets you edit or save the current query criteria, or load a
query criteria template from a template library. For more information, see sections Creating and Using Query Criteria and Edit Query (Database Edit).
Process Query — This button finds all rows in the current table that meet the query
criteria. If no query criteria exists, Process Query returns all rows in the current table. For more information see sections, Creating and Using Query Criteria and Process Query (Database Edit).
Current Row — This area lets you determine which row in the universe is current.
For more information, see the section Current Row (Database Edit).
Add Query — This area is used to set up query criteria, values used to search the
database for rows with common information. For more information, see sections Creating and Using Query Criteria and Add Query (Database Edit).
Edit Code — This button lets you display, edit, and insert new code list values in a
code list. You can also add a code list value to the current row. For more information, see sections RIS Report Writer Reserved Tables.
Change All — This button lets you change the value for one or more columns for all
rows in the current universe. For more information, see sections Editing Database Values and Change All (Database Edit).
Delete All — This button deletes all rows in the current universe from the database
upon verification. For more information, see the section Delete All (Database Edit).
Fields On/Off — This button toggles all column fields off or on. Off fields are not
included in universe reviews. You can toggle individual fields on or off by selecting the column name for the field on the Database Edit dialog. For more information, see the section Fields On/Off (Database Edit).
Review — This button generates a formatted column-by-column listing of the current
universe. For more information, see the section Review (Database Edit).
Report — This button calls the Process Reports dialog, which lets you load and
process a report template. You can list the report output to the screen or print the output file. For more information, see the section Report (Database Edit).
Key Column — This button puts the dialog in key column mode, which overrides the
key-in mode for database fields. For more information, see the section Key Column (Database Edit).
Change Row — This button puts the dialog in change row mode, which overrides the
key-in mode for key column, add query, and update active fields. For more information, see sections Editing Database Values and Change Row (Database Edit).
Database Edit 6 - 5
Delete Row — This button deletes the current row from the database upon
verification. For more information, see the section Delete Row (Database Edit).
Update Active — This button lets you enter data in the multiple column field which is
used in creating a new row. Select Insert Row to insert the displayed data as a row in the database. For more information, see the section Update Active (Database Edit).
Insert Row — This button inserts a new row in the database, based on the currently
displayed column values. You can select Update Active, enter the column values you want for a row, and select Insert Row to create the new row. For more information, see sections Insert Row (Database Edit) and Update Active (Database Edit).
Update Row — This button updates the current row based on the currently displayed
columns values. For more information, see the section Update Row (Database Edit). The eight gadgets in the lower right corner of the screen are reserved for creating and manipulating graphics linkages in the Intergraph DB Access Administrator Toolkit product. These functions are not available in RIS Report Writer.
6.2
Creating and Using Query Criteria
Database Edit lets you build query criteria to search a database table for rows with common
column values. For example, you might want to search the parts table for all tires made by a particular manufacturer, or all tires of a certain size and price range. The set of rows that match the query criteria is called a universe of rows.
6.2.1
Using Query Functions
The following is a suggested sequence for clearing, creating, and processing query criteria. In this example, the parts table is current. This example creates query criteria to search for 15-inch tires with a price of less than 100 dollars by:
Using a substring search (!!) relational operator for the mfr_name field to limit the query to manufacturers of tires. The two manufacturers in the database that produce tires (GoodTire and Armstrong Tires) have the character string Tire in their names. Using a substring search relational operator for the description1 field to limit the size of the tires to 15.
Using the less than ( < ) relational operator for the price field to search for tires that cost less than 100 dollars.
Operating Sequence
1. Select Initialize Query from the Database Edit dialog to clear any existing query criteria.
The message Query initializedappears. 2. Create a query criteria.
a. Select Add Query.
The Add Query button appears to be depressed. The dialog is in Add Query mode. Any edits to the column values of the current row are added as query criteria and do not change the database.
b. Set the AND/OR toggle to AND.
c. Select the list indicator on the relational operators field. Select the substring search ( !! ) operator from the displayed list.
d. Select the mfr_name column field. Clear the column field and key in Tire. The messageQuery addedappears.
3. Repeat the process to add a substring search for 15 in the description1 field.
4. Add query criteria in the price field for less than ( < ) 100. (This requires you to select a new relational operator.)
5. Select Edit Query to review the query criteria.
The Edit Query dialog displays the query criteria. The query criteria should resemble the following:
a. If there are errors in the query criteria, select the appropriate line and make the needed edits.
Database Edit 6 - 7
6. Select Process Query to process the query criteria.
The system displays the messageFound nn "parts" row(s)and displays the first row in the universe.
7. Use the Current Row functions to page through the universe, or select Review to see a formatted listing of the universe.
6.3
Editing Database Values
You can use Database Edit to change most column values in a database.
By default, the multiple column field on the Database Edit dialog is in change row mode. To update a value in the current row, select the appropriate column field and key in a new value. The row is automatically updated in the database.
Exceptions to this process include:
If a dialog contains add query, update active, or key column fields, you must select
Change Row to change the associated value. For more information, see the section
Change Row (Database Edit).
You cannot edit a serial column value in an INFORMIX database by keying in a new value. You cannot use the Change Row command to change serial column values. If you convert serial columns to integer columns, you should be able to modify their values through RIS Report Writer.
You cannot enter duplicate values in a column used for unique row identification (mslink, mscatalog.key column, or unique index). For more information, see the section RIS Report Writer Reserved Tables.
To change one or more column values for all rows in the current universe, use the
Change All command. For more information, see the section Change All (Database
SQL Statements 7 - 1
SQL Statements
SQL Statements 7 - 3
__________________________________________________________________________________________________________________________________________________
7.
SQL Statements
The SQL Statements button on the RIS Report Writer main dialog calls the SQL
Statements dialog. You can enter and process SQL statements, and save them as
templates. You can load SQL templates to process or edit. You can load an ASCII file to edit and process.
In general, you should use the SQL Statements dialog to process Data Manipulation Language (DML) statements (such as delete, insert, select, and update). Use the RIS Schema Manager in the Schema Edit utility for functions performed by Data Definition Language (DDL) statements (such as create, alter, and drop).
The Structured Query Language (SQL) is an ANSI standard language for creating and manipulating databases. For information on RIS-supported SQL statements, see the Relational Interface System (RIS) for Windows NT SQL User’s Guide.
Dialog Options
When you select SQL Statements, the SQL Statements dialog appears.
SQL Statement Field — This field specifies the current SQL statements. You can key
in one or more statements. You can break a statement over more than one line by inserting a Return between words. If there are multiple SQL statements, terminate each statement with a semicolon (;).
A single SQL statement
Multiple SQL statements
Load ASCII — This field lets you specify an ASCII file to be loaded. The contents of
the file displays in the SQL Statement field. You can edit the display, process it, or save it as an SQL template.
Editor — This button invokes the preferred editor which is defined by the EDITOR
environment variable. If EDITOR is not defined, Notepad is the default editor.
— The name of the current editor appears in the field to the right of the Editor button. To change editors, select and clear this field, and key in the name of the new editor.
Load SQL — This button calls the Load from Library dialog. You can select a
template from the list of available SQL templates. For more information, see the section Load From Library (Report Builder).
Save SQL — This button calls the Save Template dialog. You can save the current
SQL statements as a template in a template library. For more information, see the section Save Options.
Apply — This button executes the SQL statements. If you process an SQL select
statement, the Database Edit dialog appears. Select Review to see a listing of the resulting universe, or select Process Query and use the Current Row functions to step through the universe a row at a time.
SQL Statements 7 - 5
Clear — This button clears the SQL Statement field. Close — This button exits the SQL Statements.
RIS Report Writer Commands 8 - 1
RIS Report Writer Commands
RIS Report Writer Commands 8 - 3
__________________________________________________________________________________________________________________________________________________
8.
RIS Report Writer Commands
This section contains alphabetized descriptions of the commands available in RIS Report Writer.
In addition to general information about the command, a description may include any of the following, if appropriate. All command descriptions do not include all sections.
Command Path — This figure represents the selection path for the command. Select
each palette command in the path (from left to right in the figure) to select the command. A command can have more than one command path.
Dialog Options — If the command invokes a menu, this section describes the gadgets
on the menu.
Before Using This Command — This section contains information you should know
before selecting the command.
Operating Sequence — This section provides step-by-step instructions for using the
RIS Report Writer Commands A - 1
__________________________________________________________________________________________________________________________________________________
Add Query (Database Edit)
The Add Query command lets you establish query criteria.
You can use query criteria to limit the input universe processed by a report. If, for example, you want to search the parts table for tires, you can use the substring search (!!) relational operator to query the mfr_name field for the character string Tire. The resulting query criteria:
parts.mfr_name like ’%Tire%’
would return rows for the two manufacturers in the database that produce tires (GoodTire and Armstrong Tires).
Note that values for substring searches are enclosed in single tics (’) and percent signs (%).
If you want to search for all parts except tires, you can do one of the following: Edit the previous query, adding the word not, to produce:
parts.mfr_name not like ’%Tire%’
For information on editing queries, see the section Edit Query (Database Edit). In the relational operator field, key in not likewhen adding the query. In the mfr_name field, key in %Tire%. Again, the resulting query criteria would be:
parts.mfr_name not like ’%Tire%’
Dialog Options
Add Query — This button places the dialog in add query mode. Any value you key in
for a column field is added as query criteria. The value in the database is not changed. The dialog remains in add query mode until you select Add Query again, or select another key-in mode.
AND/OR — This toggle lets you establish a logical AND or OR operation.
— AND returns rows that contain the previous query criteria and the current column query criteria.
— OR returns rows that contain the previous query criteria or the current column query criteria.
Multiple column searches process in order, with logical AND having precedence over logical OR.
Relational Operator List — This field lets you specify a relational operator for the
query. You can key in a value or use the list indicator to select a relational operator. The following relational operators are available:
= equal to (default)
!= not equal to
< less than
> greater than
<= less than or equal to
>= greater than or equal to
!! substring search
RIS Report Writer Commands C - 1
__________________________________________________________________________________________________________________________________________________
Change All (Database Edit)
The Change All command lets you change the value of one or more columns for all rows in the current universe.
You cannot use Change All to edit a serial column (INFORMIX). You cannot enter duplicate values in a column used for unique row identification (mslink, mscatalog.key column, or unique index). For more information, see sections Editing Database Values and RIS Report Writer Reserved Tables.
Operating Sequence
1. Use the query functions to generate the universe of rows you want to change. 2. Select Change All.
The Change All button appears to be depressed. The screen dialog is in change all mode; any edits you make are added as change criteria.
3. Select a column field. Key in the value you want to establish for all rows in the current universe.
The messageChange criteria addedappears.
4. Continue to select column fields and key in new values until all change criteria have been added.
5. Select Change All again.
The SQL Statements dialog appears, displaying the SQL update statement that changes the column values.
You can save the update statement as an SQL template with the Save
SQL command.
6. Select Process from the SQL Statements dialog to update the values in the universe. — OR —
Select Cancel to cancel the change.
You can bypass Steps 5 and 6 setting the global variable
DBA_NO_CHANGE_ALL_VERIFY. For more information, see the c:\win32app\ingr\risrpt\doc\globals.txt file.
RIS Report Writer Commands C - 3
__________________________________________________________________________________________________________________________________________________
Change Row (Database Edit)
The Change Row command puts the dialog in change row mode, which overrides the key-in mode for key column, add query, and update active fields. Key column, add query, and
update active fields can be placed on custom dialogs created with RIS Report Writer.
By default, the multiple column field on the Database Edit dialog is in change row mode. To update a value in the current row, select the appropriate column field and key in a new value. The row is automatically updated in the database.
You cannot use Change Row to edit a serial column (INFORMIX). You cannot enter duplicate values in a column used for unique row identification (mslink, mscatalog.key column, or unique index). For more information, see sections Editing Database Values and RIS Report Writer Reserved Tables.
Operating Sequence
1. Select Change Row.
The Change Row button appears to be depressed. The screen dialog is in change row mode; any column values you edit are updated in the database.
2. Select a key column, add query, or update active field. Key in a new value. The messageRow changedappears.
3. Continue to select column fields and key in new values until all changes have been made.
__________________________________________________________________________________________________________________________________________________
Column (Report Builder)
The Column (Report Builder) command places a field that retrieves and prints column values from the current universe of rows. Column fields are the most important on the report template, because they retrieve information fields from the database, user data file, or ASCII file. Column fields are usually placed on body lines.
Join columns let you use a single table report to report on more than one table. Any two database tables that have columns with common values can be joined. See the section Join Columns.
There is no need to use the more complex unrelated table report unless the tables are not related. For example, to report on the manufacturer and sale tables, which do not share a join column, you need an unrelated tables report template. You must place separate body lines for each table in the report.
Dialog Options
RIS Report Writer Commands C - 5
The following list describes the options on the menu except for the Print Condition field, which is described in the section Place Field.
Schema — This field specifies the schema containing the table for which you want to
place a column field. You can key in a new schema name or use the list indicator to select a new schema.
Table — This field specifies the table for which you want to place a column field. You
can key in a new table name or use the list indicator to select a new table.
Column — This field specifies the column from the current table for which you want to
place a column field. You can key in a new column name or use the list indicator to select a new column.
Join Columns — This button lets you review and edit the join column relationship.
The button is inactive until join columns are specified.
If you place a column field from a table other than the current table on a single table report, you must specify a join column between the primary (that is, the current) table and the secondary table. See the section Join Columns.
Code Text/Number — These radio buttons lets you specify whether the column field
displays the code number or code text for a coded column. These radio buttons are inactive until a coded column is specified in the Column field.
Length — This field specifies the character length of the column field.
Decimal Places — This field specifies the number of decimal places for numeric data.
Operating Sequence
1. Select Column from the Report Builder menu bar. The DBA Column Field dialog appears.
2. At the Column field, enter the name of a column from the current table using key-in or the list indicator. Go to Step 5.
— OR —
At the Schema and/or Table field, enter a new schema and/or table using key-in or the list indicator.