App Studio > Creating Metadata > Describing Group Fields and Repeating Fields in a Synonym

Describing Group Fields and Repeating Fields in a Synonym

Topics:

How to:

Reference:

For data sources that support groups, you can assign a unique name to multiple fields to create a group field. A group field is created by two or more alphanumeric fields, physically next to each other. A group field provides an efficient means for grouping similar or logically connected fields that will be accessed as a single unit, but do not warrant a separate segment.

Procedure: How to Add a Group Field to a Segment

  1. From the Projects or Data Servers area, open a synonym by double-clicking a Master File from the Master Files folder.

    The Metadata canvas opens.

  2. Right-click a segment (or column), select Insert, and click Group.

    Note: The group option may not be available for some data sources.

    A group is added to the synonym and its attributes and values appear on the right.

  3. Type a name for the group in the GROUP field.
  4. Select the USAGE Type value for the group. Specify the length, the decimal places (if applicable) and any display options for the group.
  5. Select the I - Index check box if you want the group to be indexed.
  6. Select the ACCESS_PROPERTY values to specify access options for the group data.
    • INTERNAL defines a column that will not appear in sample data or in the list of available fields.
    • NEED_VALUE defines a column that requires a value to access the data.
    • Select By defines a column by value, range, or multivalues.
  7. Optionally, you can specify the TITLE and DESCRIPTION display options.
  8. Click Save from the File menu to save the synonym.

Note: When you add a group, a field is automatically added to the group.

Procedure: How to Add a New Field to a Group Field

  1. Right-click the group field to which you want to add a new field, select Insert, and click Field.

    A field is added to the group and its attributes and values appear on the right.

  2. Supply the required information for the group field. For more information about field attributes, see Viewing and Editing Synonym Attributes.

Procedure: How to Add an Existing Field to a Group Field

  1. Click the field you want to add to the group field.
  2. While holding the left mouse button down, drag the field and drop it on the group field name.

    The field is added to the group field.

Procedure: How to Delete a Group Field From a Segment

  1. Right-click the group and select Delete.

    A confirmation appears stating that all columns (fields) within the group will be deleted.

  2. Click Yes to delete the group and move the fields (within the group) under the root segment of the synonym.

    Click No to delete the group and all of the fields within the group.

    Click Cancel to close the Confirm dialog box and return to the Metadata canvas.

Procedure: How to Delete a Field From a Group Field

Right-click the field, then select Delete.

Reference: Group Field Attributes

Group fields in a synonym can have the following attributes:

General

GROUP

Is the name of the group.

ALIAS

Assigns an alternative name for a group.

If you create a report, the group name appears as a column heading unless you have specified an alternate title for the group. Aliases cannot be used as column titles.

TITLE

Supplies a title to replace the group name that is normally used in reports and enables you to specify multiple language titles for the group.

USAGE

Contains the format for the group field. Since the group field is made by concatenating together several other fields, the Metadata canvas determines what this format needs to be. For example, if the group field has two alphanumeric fields in it, each 20 characters long (A20), then the group field must be alphanumeric and 40 characters long (A40). The group field is always alphanumeric, regardless of the fields that make it up.

Miscellaneous

DESCRIPTION

Contains a description or comments about the group. The description displays in Field lists and on the status bar.

Field descriptions also appear as bubble help in OLAP-enabled reports. If you do not include a description, bubble help shows the field name (column title).

FIELDTYPE

Identifies an indexed group. You can index the values of a field to enhance data retrieval performance. To do so, select the Index check box when you add a field and before you add the data. An index is an internally stored and maintained table of data values and locations that enhance the performance of data retrieval. A Master File can have several associated indexes, but the combined total of indices and segments cannot exceed 64.

Note: FIELDTYPE=R indicates a read-only column. This setting is useful for columns that are automatically assigned a value by the RDBMS.

Tip: You can turn on the index after adding data to a field, however, you will have to use the Rebuild Index option to create the index.

ACCESS_PROPERTY

Specifies access options for the column data.

INTERNAL defines a column that does not appear in sample data or in the list of available columns. Restricts the field from showing in any of the Field Lists.

NEED_VALUE defines a column that requires a value to access the data. Indicates that a selection is needed in the report request (WHERE condition).

Select By defines a column by value, range, or multivalues:

  • If Value is checked, only one value should be defined for selection in the report request.
  • If Range is checked, a range selection should be defined in the report request.
  • If Multivalues is checked, multiple values are allowed for selection in the report request.
USE_STYLE

Are the styles (for example, font and color) to apply to the field.

Note: The attributes available depend on the type of synonym.

Storing the Number of Repetitions of a Repeating Field in a Virtual Field

How to:

The OCCURS attribute in a Master File describes repeating fields or groups of fields in a data source. The repeating group of fields is described as a descendent segment in the Master File, and the OCCURS attribute for that segment specifies how to determine the number of repetitions.

The number of repetitions does not have to be the same for every record instance. Sometimes, the number of repetitions can be derived from a field in the data source. In that case, you can create a virtual field in the Master File that indicates the number of repetitions for each record and use that virtual field as the value of the OCCURS attribute.

Syntax: How to Specify an OCCURS Segment Using a Virtual Field 

SEGNAME = parent, SEGTYPE = segtype,$
    .
    .
    .
  DEFINE definefield/In = expression;
  SEGNAME = osegname, SEGTYPE=S0, PARENT = parent,
     OCCURS = definefield ,$
  FIELDNAME = rfield, ALIAS = ralias,
     USAGE = rufmt, ACTUAL = rafmt,$
    .
    .
    .
 [FIELDNAME = orderfield, ALIAS = ORDER,
     USAGE = In, ACTUAL = I4,$]

where:

parent

Is the name of the parent segment.

segtype

Is the SEGTYPE of the parent segment.

definefield

Is the virtual field that indicates the number of repetitions of the repeating field or group of fields. This field must be defined in a segment that is an ancestor of the segment containing the repeating fields.

n

Is the format of the virtual field that describes the number of repetitions. It must be an integer format.

expression

Is a valid expression that derives the number of repetitions for each record instance.

osegname

Is the name of the descendent OCCURS segment.

rfield

Is the name of a repeating field in the OCCURS segment.

ralias

Is the alias of a repeating field in the OCCURS segment.

rufmt

Is the display format for a repeating field in the OCCURS segment.

rafmt

Is the actual format for a repeating field in the OCCURS segment.

orderfield

Is the name of an internal counter field that you can specify as the last field in the OCCURS segment. The ORDER field associates a sequence number with each occurrence and is useful when the order of the repeating data is significant. For example, the values may represent monthly or quarterly data, but the record itself may not explicitly specify the month or quarter to which the data applies. The USAGE format must be integer and the ACTUAL format is I4.

Note: The virtual field used as the OCCURS value cannot be redefined inside or outside of the Master File.

Example: Using a Virtual Field With an OCCURS Segment

The following request against the EMPLOYEE data source creates a fixed-format sequential file with a repeating field. The request:

  • Counts the number of FICA deductions for each employee.
  • Creates a calculated field that contains the length of all FICA deduction fields for each employee.
  • Creates a HOLD file in which each record contains the calculated length of the deduction fields for the employee, the identifying information for the employee, and all FICA deductions for the employee.

Note that the number of deductions will vary for each employee. The part of the record that contains the deductions will constitute the OCCURS segment. The number of repetitions will have to be derived from the length field created in the TABLE request.

The procedure to create the file with the repeating deduction field follows: 

DEFINE FILE EMPLOYEE
  CTR/I5 WITH DED_AMT = IF EMP_ID NE LAST EMP_ID THEN 1 ELSE LAST CTR + 1;
END
TABLE FILE EMPLOYEE
  SUM CNT.DED_AMT NOPRINT EMP_ID LAST_NAME FIRST_NAME CURR_SAL
  COMPUTE DEDLEN/I5 = 12 * CNT.DED_AMT;
  BY EMP_ID NOPRINT
  SUM DED_AMT
  BY EMP_ID NOPRINT
  ACROSS CTR NOPRINT
  WHERE DED_CODE EQ 'FICA'
  ON TABLE SET HOLDLIST PRINTONLY
  ON TABLE HOLD AS OCCURS1 FORMAT ALPHA
END

The OCCURS1 file has one record per employee with a variable number of DED_AMT fields. The total length of the number of actual instances of DED_AMT is stored in the field named DEDLEN. The Master File generated by the HOLD command lists 10 DED_AMT fields: 

FILENAME=OCCURS1 , SUFFIX=FIX     , $
  SEGMENT=OCCURS1, SEGTYPE=S0, $
    FIELDNAME=EMP_ID, ALIAS=E01, USAGE=A9, ACTUAL=A09, $
    FIELDNAME=LAST_NAME, ALIAS=E02, USAGE=A15, ACTUAL=A15, $
    FIELDNAME=FIRST_NAME, ALIAS=E03, USAGE=A10, ACTUAL=A10, $
    FIELDNAME=CURR_SAL, ALIAS=E04, USAGE=D12.2M, ACTUAL=A12, $
    FIELDNAME=DEDLEN, ALIAS=E05, USAGE=I5, ACTUAL=A05, $
    FIELDNAME=DED_AMT, ALIAS=E06, USAGE=D12.2M, ACTUAL=A12, $
    FIELDNAME=DED_AMT, ALIAS=E07, USAGE=D12.2M, ACTUAL=A12, $
    FIELDNAME=DED_AMT, ALIAS=E08, USAGE=D12.2M, ACTUAL=A12, $
    FIELDNAME=DED_AMT, ALIAS=E09, USAGE=D12.2M, ACTUAL=A12, $
    FIELDNAME=DED_AMT, ALIAS=E10, USAGE=D12.2M, ACTUAL=A12, $
    FIELDNAME=DED_AMT, ALIAS=E11, USAGE=D12.2M, ACTUAL=A12, $
    FIELDNAME=DED_AMT, ALIAS=E12, USAGE=D12.2M, ACTUAL=A12, $
    FIELDNAME=DED_AMT, ALIAS=E13, USAGE=D12.2M, ACTUAL=A12, $
    FIELDNAME=DED_AMT, ALIAS=E14, USAGE=D12.2M, ACTUAL=A12, $
    FIELDNAME=DED_AMT, ALIAS=E15, USAGE=D12.2M, ACTUAL=A12, $

You can edit the Master File to describe these repeating DED_AMT fields with an OCCURS segment. The DEFINE field named NUMOCC derives the number of occurrences from the DEDLEN field. The ORDER field is not actually in the file. It is an internal counter populated by App Studio. 

FILENAME=OCCURS1 , SUFFIX=FIX     , $                             
  SEGMENT=OCCURS1, SEGTYPE=S0, $                                  
    FIELDNAME=EMP_ID, ALIAS=E01, USAGE=A9, ACTUAL=A09, $          
    FIELDNAME=LAST_NAME, ALIAS=E02, USAGE=A15, ACTUAL=A15, $      
    FIELDNAME=FIRST_NAME, ALIAS=E03, USAGE=A10, ACTUAL=A10, $     
    FIELDNAME=CURR_SAL, ALIAS=E04, USAGE=D12.2M, ACTUAL=A12, $    
    FIELDNAME=DEDLEN, ALIAS=E05, USAGE=I5, ACTUAL=A05, $          
    DEFINE NUMOCC/I2 = DEDLEN/12;,$                               
  SEGNAME=DEDUCTION, SEGTYPE=S0, PARENT=OCCURS1, OCCURS=NUMOCC,$
    FIELDNAME=DED_AMT, ALIAS=E06,   USAGE=D12.2M, ACTUAL=A12, $   
    FIELDNAME=ORDER,   ALIAS=ORDER, USAGE=I2    , ACTUAL=I4 , $

The following request uses the ORDER field to select and print the first occurrence of the repeating field for each employee. Since every employee has at least one deduction, every employee is represented on the report output: 

TABLE FILE OCCURS1
  PRINT NUMOCC LAST_NAME CURR_SAL DED_AMT
  WHERE ORDER EQ 1 
END

The output is:

NUMOCC  LAST_NAME               CURR_SAL          DED_AMT
------  ---------               --------          -------
    10  STEVENS               $11,000.00           $64.17
     8  SMITH                 $13,200.00          $100.10
     4  JONES                 $18,480.00          $247.94
     8  SMITH                  $9,500.00           $60.96
     1  BANNING               $29,700.00          $519.75
     8  IRVING                $26,862.00          $626.78
     4  ROMANS                $21,120.00          $317.62
     1  MCCOY                 $18,480.00          $161.70
     5  BLACKWOOD             $21,780.00          $444.67
     7  MCKNIGHT              $16,100.00          $187.88
     4  GREENSPAN              $9,000.00           $52.50
    10  CROSS                 $27,062.00          $631.40

If you print the tenth occurrence of the repeating field, only two employees are displayed on the report output: 

TABLE FILE OCCURS1 
  PRINT NUMOCC LAST_NAME CURR_SAL DED_AMT
  WHERE ORDER EQ 10 
END

The output is:

NUMOCC  LAST_NAME               CURR_SAL          DED_AMT
------  ---------               --------          -------
    10  STEVENS               $11,000.00           $58.33
    10  CROSS                 $27,062.00          $526.20

 WebFOCUS

Feedback