Create a Summary Report

TABLE OF CONTENTS

1. Create a summary report
   1. Add new view definition
   2. Report page layout
   3. Specify view source
   4. Add columns
      1. Column headings
   5. Add sort keys
   6. Specify column aggregation functions
   7. Report with Columns
   8. Report with Categories
   9. Report with columns and categories
   10. Report with Subtotals
   11. Specify Format-Phase column logic
   12. Output destinations
   13. Define filters
   14. Extract Phase tab options
   15. Additional Format Phase tab options
   16. Activate the View

Create a summary report

To define a view that will write sorted and aggregated records, in a report style:

Add new view definition

1. Select Administration > New > View from the menu.

View information is displayed on two separate screens:

• The View Properties screen, where you can modify information that applies to the whole view.
• The View Editor screen, where you can define specific data transformations.

To toggle between the View Properties screen and the View Editor screen,

• press F9,
• or click the Show Grid / Properties button in the Editor area toolbar.

The first screen presented is the View Properties screen, which contains the sub-tab General.

1. In the Name field type a descriptive name.
2. In the Control Record field select the control record. See Date Processing Functions for an explanation of how Control Records are used.
3. Select the Default Output Format required. This will affect the other sub-tabs available to you. For this case, a summary view with report style output, select Format-Phase Output > Report.
4. You can select an output LR to pre-define columns by using Create View based on Output Logical Record. This option is only available before the first save. It could be useful if the output of this view is to become the source of another view via a pipe or a token.
5. Select the View Properties sub-tab Format Phase, and click
   Format-Phase Record Aggregation (FRA) > Aggregate all records records with identical sort keys.
6. Save the view File > Save
   When you save a new view, the view ID is assigned by the Workbench and is not editable.

Choosing the output format option as Report makes the sub-tab Report available, as well as the Extract Phase and Format Phase sub-tabs. Options available on all these sub-tabs are described in the sections below.

To define the report page size and customize page headers and footers, go to the Report sub-tab.

Report page layout

Select the View Properties sub-tab Report.

1. In the Details section you can configure the page size of the report. Note that the width you require will depend upon the tools that you use for displaying or printing; this is typically 80 to 255 characters.

• Lines Per Page The number of lines per page for the output report. An integer between 0 and 999999999. This field is 60 by default.
• Report Width The width of the output report. This field is 132 by default and can be changed to any integer between 0 and 999999999.

1. In the Header/Footer section you can define fields to insert into the header and footer of the report pages. Report headings can be entered as a combination of text constants and variable values. The variable values, or functions, are predefined keywords. The most common keywords (Date, Time, Page Number, and View ID) are available as buttons, and many others are available in the drop-down list.

To add a function to the header or footer:

• Click in the section where you want to add the function, for example, Footer > Right Selection
• Click the function button you require, for example, Page to insert the page number, or
• Select a function from the drop down list, then click the Insert button.

Functions available:
&[CompanyName] - This is taken from the Name field of the control record defined for the view.
&[FiscalDate] - The date provided either by the FISCAL_DATE_DEFAULT or FISCAL_DATE_OVERRIDE parameters at Format Phase runtime. See GVBMR88 parameters.
&[ProcessDate] - The date the Format Phase job was executed.
&[ProcessTime] - The time the Format Phase job was executed.
&[RunDate] - The date the VDP file was generated i.e. the date the RCG job was executed.

&[SortKeynnLabel] - The label set for Sort Key number nn in the Sort Key Properties frame.
&[SortKeynnTitle] - The Sort Key Title defined for Sort Key number nn. See Defining sort key titles
&[SortKeynnValue] - The Sort Key value for Sort Key number nn.

These are valid for up to five sort keys (nn is 01 to 05).

You can use the SortKey functions to promote a sort field into the header or footer. Once this is done, the sort field becomes a page break field, and any change in value begins a new page that contains the sort value in the header. This overrides values set in the related Sort Key Properties frame, for example if the Sort Key Properties window has Sort Key Header Option set to Print on Same Page, this will be overridden if any of the SortKey Header/Footer functions are selected.

Specify view source

Next specify the view source. You must have already defined the physical files, logical files and logical records definitions for the source you want to specify. Note that this is the same process for Extract-phase only views and summary views.

For information on how to define your own logical record, logical file and physical file definitions look at Create LR, LF, and PF metatdata in Advanced Features.

1. Toggle to the View Editor screen.
2. Select Edit > Add View Source
   Alternatively, you can Right Click on View Sources then select Add View Source
3. In the pop-up window use the drop down arrow to select the Logical Record, and the Logical File you require. Click OK.

Add columns

Next define the output layout by adding columns to the view. The columns contain the characteristics of the output such as data type, the length, and the alignment, such as left, right, or center. They can also contain constants, logic and lookup fields.

1. Select Edit > Insert Column After
   or you can insert a new column using the View Editor toolbar in the top right of the Editor Area. The arrow icons are used to insert new columns, or to move columns.
   A new column is added to the grid.
2. Click the green cell at the bottom of the new column.
   The Column Source Properties frame opens on the right.
3. From the Column Source Type list, you can select Column Logic, Constant, Lookup Field or Source File Field.
4. The following depend the Column Source Type selection
   • For Column Logic:
     To open the Extract-Phase Column Logic editor, click on the icon in Column Source Value
   • For Constant:
     Type the constant value in Column Source Value
   • For Lookup Field:
     Select the target Lookup LR from the drop down list.
     Select the Lookup Path from the drop down list.
     Select the Lookup Field from the drop down list.
     Click anywhere in the Column Source Properties panel, and the properties of the selected field will be filled in.
   • For Source File Field: Select the field from the Column Source Value drop down list.
     Click anywhere in the Column Source Properties panel, and the properties of the selected field will be filled in.

After filling in the Column Source Properties, you can select the Column Output properties. For example you can specify in the column properties the Data Type, any Date/Time formatting, data alignment etc.

For more information on the column types see Column Assignment, Data Types, Lookup Paths, and Extract-Phase Column Logic.

You can add as many columns as you want, but be aware of the output record length. Each column displays the start and end position in the output record.

Column headings

The column headings that are used in reports are specified on the View Editor tab. For column types Source File Field and Lookup Field, the column heading value defaults to the heading value in the logical record. If there is no heading value in the logical record, the field name is used. You can over type this value as needed. You can split a long column heading over the three heading cells Heading 1, Heading 2 and Heading 3.

Add sort keys

Summary views require at least one sort key, and can have up to 5 sort keys defined. The sort keys are based on view columns. You must define the column first, then assign it as a sort key.

To define a column as a Sort Key,

1. Select the View Editor tab.
2. Select the column by clicking any cell in the column.
3. Select Edit > Make Sort Key
4. The Sort Keys cell for the column will be highlighted in yellow and show the sequence number of the sort key. Click the yellow cell and the Sort Key Properties frame will open on the right.
5. The Sort Sequence can be changed from the default Ascending to Descending here.
6. Save the view File > Save

Multiple columns can be assigned as sort keys. The columns do not need to be in order since the numbers within the yellow cells indicate which column will be sorted first, second, third, and so on. The arrows within the yellow box indicate whether the column is sorted in ascending or descending order.

To unassign a column as a sort key,

1. Select the View Editor tab.
2. Select the column by clicking the column.
3. Select Edit > Make Non-Sort Key
4. The Sort Keys cell for the column will be greyed out.
5. Save the view File > Save

Specify column aggregation functions

Aggregation is performed in the format phase, only on non-sort key columns with a numeric data type. You can specify the type of aggregation for each column: Group Calculation, Maximum, Minimum, Sum (default).

To specify the type of subtotaling, double-click in the Record Aggregation Function cell for the column, and select the function from the drop-down list.

• Maximum returns the maximum column value for each unique sort key value.
• Minimum returns the minimum column value for each unique sort key value.
• Sum returns the sum of the column values for each unique sort key value. Any Format-Phase Column Logic is performed before aggregation i.e. on every extract record.
• Group Calculation returns the sum of the column values for each unique sort key value. Any Format-Phase Column Logic is performed after aggregation i.e. on the summarized record.

Note that the cell will be grey, and can not be selected, if the column is data type Alphanumeric, the column is a sort key, there is no format phase defined, or Format Record Aggregation is not selected for the view.

The group aggregation function specifies the action at all higher-level sort breaks. This is described in Reports with Subtotals.

Report with Columns

The simplest report contains the sort fields displayed as data in columns, and includes no subtotals. To produce a report that is formatted to display information as data and to have no sort key footer,

1. Select the View Editor tab.
2. Open the Sort Key Properties frame by clicking on the yellow Sort Keys cell in the column grid.
3. Select As Data for the Display Mode. This indicates the sort key data to be displayed in a column.
4. Select Suppress Print for the Sort Key Footer Option. This indicates no subtotal to be displayed at the sort key break (change in sort key value).

If all sort keys have these same parameters, the report will look much like a spreadsheet with columns and rows of data.

Report with Categories

As an alternative to displaying sort key values “as data” (that is, in columnar format), you can display sort key values on the left side of the page, with indentation, and group records with the same sort key values together. This is known as the “categorized” format.

1. Select the View Editor tab.
2. Open the Sort Key Properties frame by clicking on the yellow Sort Keys cell in the column grid.
3. Select Categorize for the Display Mode. This indicates the sort key data to be displayed as categories. With Categorize selected, the following options are available:
4. The Sort Key Label defaults to the text in Heading 1 for the Sort Key. This can be edited.
5. The Sort Key Footer Label is used when subtotaling is required. See Report options - Subtotals.
6. The Sort Key Header Option. Select from the options:
   • Print on New Page - the sort key headings, for each sort key break, are printed on a new page along with page headers and footers.
   • Print on Same Page - the sort key headings are printed for each sort key break.
   • Suppress Print - no sort key headings printed.
7. Select Suppress Print for the Sort Key Footer Option. This indicates no subtotal to be displayed at the sort key break (change in sort key value).

Report with columns and categories

Report styles can be mixed, for example Sort Key 1 can be defined with category headings displayed, and sort key 2 can be defined “As Data” in columns.

Report with Subtotals

You can opt to print subtotals for numeric columns, at each sort break (change in sort key value). You can choose to define this for one or many of the defined sort keys. For example, you may want to print the subtotals for the primary sort key, but not a secondary sort key.

The value to be placed in this subtotal field is determined by the group aggregation functions, for the numeric columns.

1. Select the View Editor tab.
2. Open the Sort Key Properties frame by clicking on the yellow Sort Keys cell in the column grid.
3. Select Print for the Sort Key Footer Option. This indicates subtotals are to be displayed for numeric fields, at the break (change in sort key value) for this sort key.
4. The subtotals will be prefaced with the value in the Sort Key Footer Label field. The default is “Subtotal,” but this can be edited.

The value to be placed in the subtotal field is determined by the group aggregation function, for the numeric column. You can specify the type of aggregation for each numeric column: Group Calculation, Maximum, Minimum, Sum (default).

To specify the type of subtotaling, click in the Group Aggregation Function cell for the column, and select the function from the drop-down list.

• Maximum returns the maximum column value for each unique sort key value.
• Minimum returns the minimum column value for each unique sort key value.
• Sum returns the sum of the column values for each unique sort key value. Any Format-Phase Column Logic is performed before aggregation i.e. on every extract record.
• Group Calculation

Note that the cell will be grey, and can not be selected, if the column is data type Alphanumeric, the column is a sort key, or there is no format phase defined.

Specify Format-Phase column logic

Format-Phase Column Logic can be applied to non-sort key numeric columns within summary views.

To specify the logic text, double-click in the Format-Phase Column Logic cell for the column.

This opens the Format-Phase Column Logic tab (FCL tab) in the Editor Area. Opening the FCL tab also opens the Logic Text Helper, which enables you to place keywords and column numbers into logic for column assignment.

Note that the cell will be grey, and can not be selected, if the column is data type Alphanumeric, the column is a sort key, or there is no format phase defined.

For more information see Format-Phase Column Logic and Format-Phase Column Logic reference.

Output destinations

Summary views have two phases. The extract phase writes to an extract file EXTRnnn where nnn is the File Number.

This is defined on the View Properties screen > Extract Phase sub-tab > File Number.

The default File Number is 0, so the default file name is EXTR000. If you are planning to create many summary views to run in the same pass, you may require the extract phase to write to different extract work files. If you change the File Number you will see that the WRITE statement changes in the Extract-Phase Record Logic View Source Properites > Record Logic.

The default output destination for the format phase is the DD Fnnnnnnn where nnnnnnn is the view ID. For example, if the view ID is 12188 the default output file for the view is F0012188.

You can specify your own format phase output destination.

1. Select the View Properties sub-tab Format Phase.
2. From Output File > Logical File list select the LF you want to write to.
3. From Output File > Physical File list select the PF you want to write to.
4. Save the view File > Save

Define filters

The view can contain logic to filter input records at extract-phase time, and also filter output records at format-phase time. See overview of record filters and create record filters.

Extract Phase tab options

Select the View Properties sub-tab Extract Phase.

1) Extract-Phase Record Aggregation (ERA) Note: This option is only available if Format-Phase record Aggregation is selected.

• Select Do not aggregate records for no aggregation of output records at extract-phase time.
• Select Aggregate records with identical sort keys using a buffer of n records to aggregate records with identical sort keys using a buffer with a limit of the n field. The n value is a positive integer up to 9 digits. The default for the n value is 4000.

For a description of ERA see Extract-Phase Record Aggregation Overview.

2) You have the option to limit the number of output records written with the Extract-Phase Output Limit.

• Select Write all eligible records to ensure that all eligible records are output. This button is selected by default.
• Select Stop Extract-Phase processing for this view after n records are written to stop the processing of this view after n records are written. The n value is a positive integer up to 9 digits. The default for the n value is 100.

3) File Number is used to manage the intermediate extract file name. See Output destinations for more details. The default is 0.

Additional Format Phase tab options

Select the View Properties sub-tab Format Phase.

The Output File option is described in Output destinations above.

You have the option to specify a User-Exit routine to be called during the format phase:

• Select Format User-Exit Routine > Name
  Select a User-Exit Routine from the drop down list. All the routines are of type Format. Sort the list by clicking on “id” or “name” in the header of the list. Reverse the sort order by clicking again on “id” or “name” in the header.
• Select Parameters to specify input data for the user-exit routine. This can be up to 32 characters.

You have the option to limit the number of output records written with the Format-Phase Output Limit.

• Select Write all eligible records to ensure that all eligible records are output. This button is selected by default.
• Select Stop Format-Phase processing for this view after n records are written to stop the processing of this view after n records are written. The n value is a positive integer up to 9 digits. The default for the n value is 100.

The Format-Phase Record Filter option is described in Define filters above.

To avoid output of records with zero in all numeric fields, tick the Zero-value Record Suppression box.

Activate the View

The view must be activated before the Performance Engine can process it. Activation invokes the compiler which reports errors and warnings.

To activate the view, use any of these methods:

• Select Action > Activate
• Press the Activate icon on the View Editor toolbar
• Press F5

When the view is active, save it to preserve this active state. The view is now ready to be run.