How You Can Create Data Fields
A report data field references an individual piece of data. It can be either a database field, which displays information from one column in a query result, or a computed field, which displays a value computed from the data in a function or formula.
After you have defined a query, you can add data fields to the report document.
A data field maps to a field in the query result that you use to supply data for the report.
Data field creation methods include the following:
• Single Field
• Fields from Table
• Variable Field
Each of the data field creation methods are described in the following sections. After data fields are created, they can be modified as described in
Modify Field Properties.
Create Single Fields
You can use Single Field Creation to create fields that are associated with data in the following ways:
Column of a Selected Table
A Column of a Selected Table type of field is one whose value is determined by the main query when the report is run. It references one of the columns selected from one of the tables specified in the FROM clause of the query.
Single Query
A field associated with a single query gets its value from a query that is not the main query. The query associated with such a field should be a singleton select.
A singleton select query is one for which only one row may be returned. If the query returns more than one row, a runtime error will result. By default, the singleton query is run before the main query; however, by checking the Generate in Main Select toggle when creating the field, the query will be issued in the detail section once for each row returned by the main query.
Calculated Expression
A calculated expression field uses functions and arithmetic operators to compute a value. You define a computed field by combining values in other report fields or by using functions.
To create a single field
1. Click Insert, Data Fields, Create Single Field.
2. Click in the work area and drag the cursor to draw a field (rectangular area).
The Reporter DataField List dialog appears.
Note: The Reporter DataField List dialog lists all data fields currently defined in the document. If no data fields are listed, the Reporter DataField Properties dialog appears automatically, allowing you to create a new data field.
3. Select an existing item in the Columns/Expressions list and click Create Field.
Alternatively, to create a new single field using single field creation:
a. Click the N (New) button.
The Reporter DataField Properties dialog appears.
b. In the Reporter DataField Properties dialog, from the Attach To list, select the desired option (Single Query, Column of a Selected Table, or Calculated Expression).
The Reporter DataField Properties dialog changes to reflect the option you select in the Attach To list field.
To attach a single field to a “Single Query”
1. Enter an alias name.
The alias name is used to create a variable in the generated report code.
Note: The alias name must be used in the select statement. The select statement must retrieve only one row and column.
The Attached Query multiline entry field displays the query associated with the Single Query field. This query is specified when the field is created. The Generate Query in Main Select toggle determines when the attached query is issued. It is independent of the main report query generated automatically by Reporter.
2. Click Check to validate your SQL request.
3. Click in the Generate Query in Main Select toggle field if you want the query to be issued in the detail section once for each row returned by the main query.
4. Click OK.
The Reporter DataField List dialog appears with the selected column added to the list.
5. Click Create Field.
The field you created appears in the work area.
6. Click File, Save to save your work.
To attach a single field to a “Column of a Selected Table”
1. Select the desired table (or accept the default) and column.
The Selected Column field displays your choice.
2. Select the Is a Break Column check box if you want to insert a page break.
3. Click OK.
The Reporter DataField List dialog appears with the selected column added.
4. Click Create Field.
The field is created and added to the Detail page of the report document.
5. Click File, Save to save your work.
To attach a single field to a “Calculated Expression”
Note: When you select Calculated Expression from the Attach To list, the lower half of the Reporter DataFields Properties dialog changes to display four tables—Tables, Columns, Functions, Operators—from which you can select the desired values.
1. Double-click the function you want to use in the Functions list field.
2. Double-click the arithmetic operator you want to use in the Operators list.
3. Double-click the appropriate column name in the Columns list.
The result of your selections appears in the Expression Text entry field. You can edit this text if you want.
4. Click Check to validate the syntax of your user‑defined expression.
5. In the Alias Name field, enter the name of the calculated field you are creating.
6. Click OK.
The Reporter DataField List dialog appears with the new calculated field.
7. Click Create Field.
The new data field is created and added to the report document.
8. Click File, Save to save your work.
Types of Functions
OpenROAD Reporter supplies various functions that you can use to create computed fields. The functions perform sophisticated calculations. For example, you can use functions to rank field values or to return the future value of an investment.
When you use a function in a computed field, you must specify certain variables on which the function operates.
The following descriptions summarize the functions available in OpenROAD Reporter:
Aggregate Functions
Perform calculations (for example, counting, summing, and averaging) on grouped data or data for the entire report. Aggregate functions ignore Null values in their computations. For example, the Avg() function does not include the Null value when it computes the average.
Generally, you insert a computed field containing an aggregate function in a report header or footer. The placement of the field determines the value returned by the function.
Note: If your report contains a large amount of data, you can improve performance and memory usage by placing aggregate functions in the report footer. Placing an aggregate function in a report header requires the most time and memory to evaluate.
String Functions
Manipulate text strings and fields with a text data type in the report definition. For example, text functions let you compare strings, insert and extract characters within strings, and convert lowercase strings to uppercase strings.
Date and Time Functions
Calculate dates and times using serial numbers. For example, you can use a function to add a number of weeks to a date and get the future date back.
Numeric Functions
Perform common mathematical operations on data defined in the report definition, such as raising a value to a power or returning an absolute value.
Conversion Functions
Convert a field or value of one data type to a value of another data type. For example, Reporter provides functions that convert date values to number values or text values, number values to date values or text values, and so on.
Create Fields from Tables
You may create more than one field at a time, but all fields that are created together must be associated with a single table in the main query.
To create fields from tables in the main query
1. Click Insert, Data Fields, Fields from Table.
2. Click in the work area and drag the cursor to draw a field (rectangular area).
When you release the mouse button, the Reporter Table Selection dialog appears.
3. In the Tables list, select the table from which you want to select columns.
The Selected Table field displays the corresponding table name.
4. Click the toggle field to the right of the desired column in the Columns list.
An x should appear in the toggle field.
5. Click the Select/Unselect toggle field to select all items in the Column List.
6. In the Pattern field, enter a pattern-matching string (eight characters maximum) to restrict the names of columns to be listed; for example, d% would retrieve only column names beginning with “d”.
7. Click in the Create Fields Labels toggle field if you want labeled table columns.
8. Click Create.
The detail page of the report appears and the selected table fields are displayed.
9. Click File, Save to save your work.
Modify Field Properties
Data fields are edited in the Reporter DataField Properties dialog. After data fields have been created, the distinction between the methods used to create them—Single Field or Fields from Table—goes away. Fields are edited individually. When you select the field you want to edit, the dialog that appears reflects the properties of that field and its data type.
To modify data field properties
1. Click Tools, DataField List.
The DataField List dialog appears.
2. Click Edit (the middle icon on the right side of the dialog).
A “No Details” version of the Reporter DataField Properties dialog appears.
3. Click All Details if you want to view more detail.
4. Make the desired edits.
5. Click Open API Code to view or edit the code for the field, if desired.
6. Click Save to save your edits.
Create a Variable Field
The variable field creation option is only for fields whose value is obtained by explicit assignment rather than by SQL selection. Variable fields are created and managed by the user. They correspond to local variables or parameters in the generated 4GL procedure.
You can create variable fields in the Variables List dialog.
To create a variable field
1. Click Insert, Data Fields, Variable Field.
2. Click in the work area and drag the cursor to draw a field (rectangular area).
The Variables List dialog appears.
Note: The Variables List dialog lists all existing variables. If there are no available variables, the Variable Properties dialog appears automatically also, letting you choose an appropriate variable or create a new variable.
3. Select the desired variable from the Variables list.
4. Click Create Field (or Create Param).
The variable field is added to the report document.
To create a new variable
1. Click the N (New Variable) button on the right side of the Variables List dialog.
The Variables Properties dialog appears.
2. Set the variable properties as desired.
3. Click OK to create the variable and close the Variable Properties dialog.
You are returned to the Variables List dialog.
4. (Optional) Click Edit Variable to edit a selected variable or Delete Variable to delete a selected variable.
5. Click Close, or click the N button to create another new variable.
When you click Close, the report document appears.
Clicking Close does not cancel or discard changes made. Variables are created and edited by calling the Variable Properties frame (New Variable and Edit Variable buttons). Variables are deleted (Delete Variable button) permanently after confirmation if they are not in use.
When the Variables List dialog is opened as a result of choosing to create a variable field or table parameter, any changes made to variables are kept whether you select Create or Close. Selecting Create associates the selected variable with a field or table parameter; selecting Close simply aborts the association but does not affect any changes made to variables.
Variable Properties
The Variable Properties dialog contains the following options:
Name
Specifies the name of the variable field. You can click the file folder icon to select a variable name from a list of available variables.
Data Type
Specifies one of the following data type options:
• Varchar—variable-length text, to 4096 characters
• Nvarchar—variable-length text, to 2048 characters
• Smallint
• Integer—a four-byte integer
• Float—a decimal number
• Money
• Date
Note: For multiline entry fields, select Varchar and specify the maximum length of the field. Selecting any of the other data types (Smallint, Integer, Float, Money, or Date) defaults to a single-line entry field. If Is Table Name is checked, the Data Type must be set to Varchar.
Length
Specifies the desired field length
Nullable
Specifies whether the variable field is nullable
Default Value
Lets you select either System or String as the default value
Format
Specifies single line or multiline format. This field is displayed when the selected Data Type is varchar.
Display Size
Specifies the display size
Set Maximum Characters
Specifies the maximum number of characters allowed by a single-line entry field's Format or Data Type
Rotation
Specifies the desired rotation in degrees. Changes to rotation appear only after the report is run.
Template
Is displayed when the selected data type is not varchar. Click the down arrow to display a list of available templates.
Expression
The entry fields in this edit control—Report's Header, Page's Header, and so on—accept expressions or values that may be assigned to the variable. These expressions can reference other variables and fields that are already defined.
The various locations listed—Headers, Detail Sections, Footers—represent the locations in the report where the assignment takes place. Expressions can be entered into all locations. If more than one location is specified, the Generate Print In field is activated for specifying the location from which the value is used for printing.
Is Parameter
Specifies a variable as a parameter of the generated report (in which case, the value of the variable is determined during the report execution). When a variable is a parameter, the EntryField prompt allows the variable name to be replaced by an explanatory phrase in the Print dialog.
Is Table Name
Specifies that the variable is a table name. If selected, the Data Type must be Varchar, and Is Parameter must be selected also. This field is view-only; the Query Editor controls the use of a variable as a table name parameter.
Report's Header
Lets the user enter expressions or values to be assigned to the variable at the report's header location
Page's Header
Lets the user enter expressions or values to be assigned to the variable at the page's header location
Detail Section
Lets the user enter expressions or values to be assigned to the variable at the detail section's location
Page's Footer
Lets the user enter expressions or values to be assigned to the variable at the page's footer location
Report's Footer
Lets the user enter expressions or values to be assigned to the variable at the report's footer location
Prompt
Specifies a prompt for a variable value when the Print Dialog Frame is called
Generate Print from
Indicates the location from which the value is used for printing. This field is activated if both Page Header and Footer are selected.
Modify Variable Field Properties
You can modify variable field properties in the Reporter Variables List dialog.
To modify a variable field
1. Click Tools, Variables List.
The Variables List dialog appears.
2. Click the Edit icon on the right side of the dialog.
The Variable Properties dialog appears.
3. Make the desired changes.
4. Click OK save changes and close the Variable Properties dialog.
The Variables List dialog reappears.
5. Click Close.
The report document appears.
Delete a Data Field
You can delete a data field using the DataField List dialog.
To delete a data field
1. Select the data field you want to delete and press the Delete key.
The DataField List dialog appears.
2. Click the Delete button (the bottom icon to the right of the Columns/Expressions list).
A standard confirmation pop-up appears.
3. Click OK to delete the data field.