Training Module 17: Format Phase Views

The slides for the following video are shown below:

Slide 1

Welcome to the training course on IBM Scalable Architecture for Financial Reporting, or SAFR.  This is Module 17, Format Phase Views

Slide 2

Upon completion of this module, you should be able to:

  • Describe uses for Format-Phase Views
  • Read a Logic Table and Trace with format-phase views
  • Explain the Function Codes used in the example
  • Debug format-phase views

Slide 3

The prior modules covered the function codes highlighted in blue.  Those to be covered in this module are highlighted in yellow.

Slide 4

The Format Phase is the final possible phase of the Performance Engine Processes.  Views that require only extraction and no sorting or aggregation are completed at the Extract Phase.

Any view that requires the following must continue to the Format Phase:

  • Summarize records
  • Use output record filtering which is applied after records are subtotaled
  • Produce sorted outputs
  • Create delimited files, and
  • Make hardcopy formatted printed reports

The Format Phase is run after the Extract Phase (GVBMR95) has completed extracting all data.

Slide 5

The GVBMR95 Extract Engine performs functions one event or transaction record at a time.  Records are evaluated for inclusion or exclusion in output files, fields are extracted and used to build keys for joins, all one record at a time.  Except for Extract Time Summarization (which will be discussed in a later module), GVBMR95 does not produced summarized results. 

You may remember that in Module 10 we discussed summary reports.  These report aggregate detailed records to produce summarized results.  Even detailed reports can include subtotals, which are the results of summarizing many detailed records.  To perform these function, the Format Phase Engine, GVBMR88 is required.

Slide 6

Extracted data is passed in a special format, called the SAFR Standard Extract File, from the Extract Phase to the Format Phase.  Creating this format requires unique Logic Table Functions in the Extract Phase.  In this module we will focus on these Format Phase logic table functions and introduce the Extract File Format.

Slide 7

The Extract Engine, GVBMR95 is a parallel processing engine, yet the Format Phase, GVBMR88 is not; it processes only one Extract Work File.  Yet that extract file can contain the extracted records for multiple views.  Each GVBMR88 execution can produce multiple extract files. The final output for each view is produced by only one format engine execution.

In this example the extract file on the left has the extracted records for views 2, 3 and 4, while the one on the right has the records for views 8, 9 and 10.  The GVBMR88 execution on the left produces the output for view 2, 3 and 4, while the one on the right produces the output for view 8, 9 and 10.

Slide 8

As previously mentioned, GVBMR95 creates a specially formatted extract file and there can be one or more created based on how the data needs to be processed.  The extract file to be used by each view is assigned in the view parameters screen, the Extract Phase Tab.  The Extract Engine concatenates the assigned number to the value “EXTR” with three digits representing the Extract Work File Number, beginning with zeros if necessary to form the DD Name to be used.  It also concatenates this number with the value “SORT” to write the parameters specifying how the views should be sorted.

This example uses Extract Work File Number 1. The extract records will then be written to the file assigned to the DD Name EXTR001, and the sort parameters to SORT001.  These two files must be passed via the JCL as SORTIN and SYSIN files respectively to the Format Engine GVBMR88. 

Slide 9

The Format Phase Extract record has a standard format for all views.  This specialized format allows for very efficient processing of Format Phase views.  The Extract and Format engines have clearly defined functions, such that steps are not repeated between the two engines. The extract file format prepares the data to be sorted and summarized.

The record contains the following segments: 

  • The control area contains values that describe the rest of the record, like the number of SAFR view columns on this record, and the length of the sort key. The last field in the control area is the view number.
  • The Sort Key or SK area contains the values the user has specified the output file should be sorted and/or grouped by.
  • The Data Transform or DT area contains alphabetic and alphanumeric and numeric column data that is not used in a format time calculation, including sub-totaling, or format time selection logic.
  • The Calculated Transform or CT area contains column data that is either used in a format time calculation, including sub-totaling, or format time selection logic.

The sample data is shown in HEX mode.  The first row is the display format, and the next two lines show a half a byte each, first (top line) and last (bottom line).  This shows packed and non-printable characters.

Slide 10

In this module, we will use the view shown here as the example view.  This view includes a simply lookup for record level filtering.  Record level filtering does not affect the format of the extracted records.

This view has 7 columns.  The first three columns are sort keys, highlighted by the yellow sort key indicator.  In other words the view output will be sorted by the values in these three columns.  The records will be sorted first by the Format value in column 1, then the Store ID value in column 2, and finally by the Gender Code in column 3.  We know this because of the #1, #2, and #3 showing the sort key position.

Slide 11

Columns 4 and 5 contain data that requires a calculation.  In this case, they are to be summed, to produce the total for a change in the sort key value.  Thus the data required for these calculations will be placed in the Calculated Transform or CT area of the extract record. 

Slide 12

Columns 6 and 7 are date fields which are not used in a calculation.  They do not have Format Phase Filtering logic, subtotals or any column calculations.  The data for these columns will be placed in the Data Transform area, or DT columns area of the extract record.

Note that the view columns can be constructed in any order needed for the final output.  The columns will be reordered into either Sort, DT or CT columns in the extract file, but then will be reordered for the final output at the end of Format Phase processing.

Slide 13

This is the logic table produced by our sample view.  The first functions of this view include record level filters which test a join to ensure only records which are found are included in the output.

Slide 14

Sort columns, which populate the Sort Area of the Extract Record, use SK functions to move selected fields and constants to the extract record.  These functions follow a familiar pattern.  SKE functions move data from the Event File to the sort area, where as SKL move Looked-up field values, and SKC move constants.  The sort sequence number is shown in the Sequence Number column.

In this example the first column is a constant value SKC Sort Key Constant.  This places the constant “TRN01” in the first sort position; because this is a constant with no logic functions, all values on the output file from this view will be the same. 

Column 2 moves the Store ID field from an event file field using the SKE Sort Key, Event File Field function.

Column 3 moves the gender code, either an “M” or an “F” from the customer file through a looked up value using an SKL Sort Key Lookup Field function

Note that the entire length of the Sort Key Area is 9 bytes, also indicated in the Logic Table by the SK Length comment on the NV row.

Slide 15

Calculated Transforms, CT columns, also follow the same pattern.  Constants are moved via CTC, CT Column from Constant functions, Event File Fields using CTE CT Column from Event File Field functions, and looked up values using the CTL CT Column from Looked-up Field function. 

Our example view contains a constant value with the CTC function for Column 4.  This is a record count, adding a value of 1 to each record.  Column 5 is an Event File Field value from the CTE function, the Order Amount.  It does not contain a looked up CT value, so no CTL is shown. 

Note that the final two columns, containing our Data Transform values, use DT functions to populate the extract record.  Both these columns are the customer date birth.

Slide 16

Note that the WR, or Write Function, also changes when writing to a standard extract file.  Instead of a WRIN function to copy the input record to the extract file, or the WRDT function to write only the DT column data area of the extract record to the output file, the WRXT function writes the entire standard Extract Record to the extract file.  The sequence number following this instruction contains the file number the extract record should be written to. 

In this example, the extract record contains the control area, Sort Data (9 bytes in length) containing “TRN01001M”, two dates in the DT area of the record, and two CT Columns containing packed data.  This record is written in this instance to Extract File 1.

Next we’ll examine the Logic Table Trace, and the extract record as it is built

Slide 17

When processing the first record, the Logic Table Trace shows the results of these logic table functions. In this example, the SKC constant value of TRN01 is stored in the first position because it is the first sorted field. The Event File Field value of 001 is stored next, and the Looked-up value of M is stored after that. 

Slide 18

If we had simply changed the Sort Key order—the values shown in Yellow in the columns—such that the Gender CD had a #1 as the first sort key, and Format had a #2, and Store ID a #3, the position of the values in the final output file would not change.  But the order of the rows in the file may well have changed after being sorted.  This is because the file would be sorted first by Gender Code, then by Format, and last by Store ID.  Thus all F values would be at the top of the file followed by all M values.  To accomplish this, the Gender code would have been moved to the first position in the extract file for sorting.  The Format Engine would have moved this value back to the 9th position in the output file. 

Slide 19

The Extract Engine formats CT data into an efficient format for the calculations in the Format Engine.  This format is Packed 12 bytes which can hold 23 digits.  The last 8 digits are always fixed decimal points.  The value is also preceded by a binary half word (two bytes) containing the column identifier.  Column values of all zeros are not stored in the extract record, to conserve space and processing time. Because of this sometimes no CT data is stored for a specific column.  Thus all CT columns are on the end of the extract record, allowing the record length to vary. 

In this example, the constant from the CTC function of 1.00000000 is stored immediately after the binary column identifier of 4.  The Event File Field value from the CTE function of 58.25 is stored immediately after the binary column identifier of 5. 

Slide 20

The DT functions move data to the space between the SK area and the CT area.  These functions were used in the earlier module explaining the Extract Only view, with the WRDT function.  The WRDT function only writes the data in the DT Area of the Extract Record. 

Data is moved to the appropriate area of the extract record no matter the order of the Logic Table functions in the Logic Table.  For example, if the first columns of the view are CT columns, the CT area of the record will be populated. 

In this example, the two dates are moved to the DT area of the extract record even though they contain only numeric values.  CT columns are reserved for columns which require calculations, subtotaling, or Format Phase selection logic.  Numeric data simply placed in the output file are placed in the DT area of the extract record.

Slide 21

The WRXT row adds information to the front of the Extract Record.  This includes total record length, length of the SK and DT areas, counts of CT columns.  These control fields are used by the Format Engine to process the extract record.

The last field in this area, just prior to the SK area, is a binary version of the View Number.  It is stored as binary to conserve space in the extract file, optimizing IO and storage.  The view number is multiplied by 2, 1 added and then converted to binary.  Thus in this example, View 143 * 2 = 286 + 1 = 287 converted to binary is 11F.  This formula allows a header record (discussed later) to be sorted to the top of the view.

Slide 22

In standard mode, the Extract Engine does not perform any summarization.  Thus as event file record 2 is read and passes record filtering, a second extract record is written.  If only one view is running, the records will be written in the same order as they are in the input file, which may have no relationship to the required sort order for the view.  If more than one view is reading and selecting records from the event file, the second record may be the same input record extracted for another view, with completely different view sort criteria.

The end result is that the order of the records in the extract file is unpredictable, particularly if views share extract files while running in parallel.

Slide 23

Standard Extract Files—which are those that are processed by the Format Engine—contain Control and Header Records. Each record contains processing information, such as control record counts, the Run Date, etc. for use by the Format Engine.

One Header Record is produced for each view writing to this standard extract file.  The View Number on the Header Record is one less than the extracted event records (in this example 11E rather than 11F). Thus the formula for the view ID on each extract record.  Only one Control Record is written to each Standard Extract File.  The Control record has a view ID of 0.

Writing header and control records to extract files is is done if the extract file number is less than or equal to the Extract Engine JCL STDEXTR=nnn parameter, where nnn is the file count. For example if a 5 views write to records to EXTR002 DD Name, and the STDEXTR parameter is 2, the file would receive 5 header records and 1 control record.

Slide 24

The Extract Engine writes records in a random order.  The Extract File is sorted before being used by the Format Engine.  The Format Engine then summarizes like record by the sort key, and writes the records in order to the output file. 

The Extract File is always sorted by the combination of the View Number, in the control area of the extract record, and the Sort Key data area. Even though the Header and Control Records are the next to last records in the extract file, the lower View Numbers cause them to sort above all the records for this view, with the Control Record on top for Format Engine processing. 

The data in the Extract records are moved to the final output columns and formatted correctly, for example by applying masks to numeric data.  In this example, the CT data is moved before the DT data and decimal points are inserted.  Note also that in summary outputs, data in DT columns can be unpredictable.  If different DT values are extracted for the same sort key combination, which value will be placed on the output file cannot be guaranteed. For example, if there are two different dates in the file 1/1/2014 and 4/15/2014, either may appear on the final record.  Thus typically summary files only contain Sort Key and CT data. 

Slide 25

No matter what sort order is specified in the view for sort fields, SAFR always instructs the Sort Utility to sort the file in Ascending order.  This is necessary because of the mixture of ascending and descending fields within the same view or across different views.  To create descending sorts, SAFR converts descending data in the extract file to it’s “2’s complement” which simplifies sorting.  When sorted and then converted out of 2’s complement, the data will appear in descending order.

In this example, we changed the sort order on the Gender Code from Ascending (in the top example) to Descending (in the bottom example).  The 2’s complement of M and F are unprintable characters with a hex values of “2B” and “39” respectively.  Because “2B” is less than “39”, the “M” records move to the top of the extract file.  Thus when the data is converted out of 2’s complement, the file output shows M before F, a descending sort.

Slide 26

Multiple views can share an extract file.  Because the sort parameters include the View Number at the front of the sort criteria through the length of the longest sort key, sorting causes all the records for a view to be sorted together, and all records for a view to be placed in the sort order specified by the view.  The Extract Engine dynamically builds the sort criteria parameters for each extract file, inserting the longest sort key length for views writing to the extract file.

In this example, the extract file contains records for view 11F and 22E.  The sort parameters are now lengthened to include the additional three bytes of sort key data required by view 22E.  After sorting the file, the Control Record is followed by the Header Record for view 11F, and all the extracted data for that view in sorted order including the Gender Code, then the Control Record for view 22E followed by it’s extracted data also in order by the sort key.

Slide 27

To avoid writing the sorted extract file, and then reading this file again for summarization and formatting, GVBMR88 calls the Sort Utility as a “Sort Exit.”  This allows the sort utility to pass the sorted records to GVBMR88 a record at a time after completing the sort, instead of writing them to disk, thus avoiding a pass of the extract file.  In other words, the sorted records are a virtual file that is never actually written to disk.

The Sort Utility also prints a control report.  This example shows some of the key messages, including noting the utility is running as an exit to “MR88”, the sort parameters that were generated by the Extract Engine, the number of records sorted.  Records are “deleted” from the virtual file by GVBMR88 as it produces the final output file.

Slide 28

The Format Engine also produces a control report at the end of it’s execution.  This control report shows the number of records read from the input file, the number of records written to various types of format files, and the number of records written to view specific output files.  The total records read by GVBMR88 should equal the number of records written by the Extract Engine. 

In this example, 12 records were written to the extract file, and all 12 were read by the Format Engine.  The final output file for view 143 was 2 summarized records.

Slide 29

Not all SAFR functions are specified in the logic table.  For example, column calculations using column numbers are not specified in the logic table.  Other functions include column spacing, creating hard copy report formatting, subtotaling, and summarizing.  All these parameters are passed to GVBMR88 in the View Definition Parameter or VDP file and performed during the format phase.

This screen shot shows the VDP parameters used by GVBMR88, including column definition sizes and formats.  This report is printed at the end of the Format Phase.  The highlighted area for Column 7 contains a column calculation.  The logic for this calculation is “Column 6 divided by Column 5”

Slide 30

One last specialized Logic Table function should be mentioned.  The Format Engine does one type of join, for one format of output:  Sort Titles (descriptions of sorted key values) on hard copy reports.  These GVBMR88 look-ups though are not multiple level joins.  They are simply the last step in the Join process to obtain the sort title.  The Format Engine does not perform any of the LKLR, LKE or other functions to do the join.  Rather it receives the fully formed key as an area of the Sort Key Area.  Saving this key in the sort key area of the extract record.  The Format Engine then performs the lookup to find the sort title.

In this example, the KSLK function save the lookup key of 0001 into the Sort Title Key Save area of the extract record.  GVBMR88 then uses this key to search for customer e-mail addresses to place next to customer numbers in the hard copy report. 

Slide 31

This logic table has contained the following functions:

  • SK functions, including SKC, SKE, and SKL, to build Sort Keys.
  • CT functions, including CTC, CTE, and CTL, to build Calculated Transformations
  • WRXT, which writes the extract record to a standard extract file
  • KSLK, which saves the Look Up Key for a Sort Title on hard copy reports

Slide 32

This module described Format Phase views. Now that you have completed this module, you should be able to:

  • Describe uses for Format-Phase Views
  • Read a Logic Table and Trace with format-phase views
  • Explain the Function Codes used in the example
  • Debug format-phase views

Slide 33

Additional information about SAFR is available at the web addresses shown here. This concludes Module 17, Format Phase Views

Slide 34

Training Module 10: Hardcopy Formatted Output

The slides used in the video are shown below:

Slide 1

Welcome to the training course on IBM Scalable Architecture for Financial Reporting, or SAFR. This is module 10, “Hardcopy Formatted Output.”

Slide 2

This module provides you with an overview of SAFR hardcopy formatted output. Upon completion of this module, you should be able to:

  • Create a hardcopy report in detail format
  • Create a hardcopy report in summary format
  • Properly set column widths to avoid truncating data
  • Create reports with display modes of “As Data” and “Categorized”
  • Use record aggregation and group aggregation functions

Slide 3

Recall that the SAFR Performance Engine consists of six phases. The Extract phase determines which source data records are selected for output. The Extract phase can produce final output files, but if the data requires sorting, summarizing, or special formatting, the Extract phase instead writes the output to temporary work files. These files are then read by the Format phase. Hardcopy reports are produced in the Format phase.

Slide 4

SAFR provides numerous options for formatting hardcopy or printed reports. The report images shown here illustrate the most commonly used report formats and are not intended to represent actual report content. Common report choices include:

  • Summarized or detailed reports
  • Sort data categorized or displayed in a column
  • Subtotaling, and
  • Others, including column width and headers

We’ll begin by setting up the basic canvas.

Slide 5

Begin on the View Properties tab and select Hardcopy Report. Enter the number of lines to be printed per page, which is normally about 60. Then enter the report width. Note that the width depends upon the tools that will be used for display or printing; this is typically 80 to 255 characters.

Slide 6

Next, click the Extract Phase tab. Normally, record aggregation, or the summarizing of numeric values for a group of records, occurs in the Format phase of a SAFR job stream, but a certain amount of aggregation can be accomplished in the Extract phase. This feature can be selected in the Extract-Phase Record Aggregation section of the screen. However, if you want to produce a detailed listing of all extracted records, select Do not aggregate records. If you do not select this option for a detailed report, the report might contain an inconsistent mix of aggregated and detail records.

Because hardcopy reports require the use of the Format phase, you must assign the extract output to an Extract Work file. This example uses file number 1. Format phase job number 1 will read file number 1.

Slide 7

Next, perform the following steps:

  • Click the Format Phase tab.
  • To create a detailed report of all records extracted, select Do not aggregate records.
  • Select Write all eligible records.
  • Enter the error fill and truncation fill values. The error fill value is displayed in numeric columns if the data source is nonnumeric. The truncation fill value is displayed if a numeric value does not fit in the space provided by the column width.

Slide 8

Click the Header/Footer tab to enter the report headings. Report headings can be entered as a combination of text constants and variable values. The variable values are represented as functions and are shown in the center frame of the screen. These functions are predefined keywords that can be used to define the headings. 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 in the Functions area.

Slide 9

Now the view properties are defined and it is time to build the view columns.

Hardcopy views must have one or more alphanumeric columns containing items such as names, dates, or codes. At least one of these columns must be a sort field, sometimes referred to as a sort key. In addition, one or more numeric columns can be subtotaled.

In this example, the view consists of three columns: the two sort key fields (Store_ID and Customer_ID)and Order_Total_Amount.

The column headings that are printed on the report can be specified on this screen. A 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 overtype this value as needed.

Slide 10

The simplest report contains a sort field displayed as simple data and includes no subtotals. To produce a report that is formatted to display information as data and to have no sort key footer, you open the Sort Key Properties tab and select As Data for the display mode and Suppress Print for the sort key footer option. If all sort keys have these same parameters, the report will look much like a spreadsheet with columns and rows of data.

Slide 11

The second column has the same sort key attributes.

Note the other key attributes. For example, the spaces before column attribute places blank spaces between columns. This can be adjusted to accommodate more columns or improve the appearance of the report layout.

You must be careful when assigning the column length or width. The column width must be large enough to fit the numbers that will be placed within it. This will help prevent truncation.

The next page shows the output from this view.

Slide 12

This output header displays the three pieces defined on the View Properties Header/Footer tab: the view ID, the label “Hardcopy Report Example,” and the processing date. The output also displays three columns. Although the column widths were large enough to store the data in the input file, they are not wide enough to display the data properly in the report columns. Also, the column headings defaulted to the field names but were truncated because they are longer than the defined column widths. Finally, additional spaces are needed for masking characters, so the overflow characters from the View Properties panel were inserted in place of the numeric values.

Slide 13

To format the report output so that it is easier to read and understand, we can adjust the column length from 6 to 12 for column 3 and use more than one row to accommodate more descriptive column headers. For example, instead of a column heading of “STORE_ID,” we now have a row for “STORE” and a row for “ID.” Both values fit within the column length of 5.

Note also that we have increased the spaces before columns from 2 to 5, simply to improve the look of the report.

Slide 14

Note that the column headings are now understandable. Also, the order total amounts are now visible and are properly formatted.

Because this is a detail report, the rows of data on this report show one extracted record from the Extract phase. There are no subtotals.

Slide 15

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.

The Sort Key Properties list for the second column specifies data that is categorized with a CUSTOMER sort key label.

Our report will still not include any subtotals because both sort keys still suppress the Sort Key Footer, or Subtotal,option.

Slide 16

The modified view now produces the report shown here. In this report, sort keys are shown on the left side of the report and are only shown only when their value changes. Because this report shows all detailed records, the headings are printed once, followed by values.

Because subtotals are still suppressed, a summarized amount accumulating the orders for Customers 1, 2, and 3 for Store 1 is not shown yet.

Slide 17

If we select Print instead of Suppress for the sort key footer option on both sort keys, the report will also contain subtotals at each sort break. The subtotals will be prefaced with the value in the Sort Key Footer Label field; in this example, the sort key footer label is “Subtotal.”

The value to be placed in this subtotal field is determined by the group aggregation function, which in this example is Sum. The subtotal will contain a simple sum of the more detailed values.

Slide 18

The output now shows subtotaled rows after each sort key break. In this example, the subtotal for Customer 1 at Store 1 is 10,249.01, which is the accumulated value of the three individual orders of 58.25, 10,065.43, and 125.33.

The subtotals for Store 1 also include the order totals for Customers 2 and 3, plus a store total at the bottom of this section of the report.

Slide 19

On the View Properties Format Phase tab, if you modify the aggregation parameter to aggregate similar records, your report will show only one row for each subtotaled value.

Slide 20

No changes to the sort keys are required. However, note that the footer options become unavailable on the lowest sort key. By definition, aggregated views do not print the individual records that are extracted in the Extract phase. In aggregated views, the lowest level of detail is the record aggregated to the lowest sort key. In this instance, subtotaling would be meaningless because there would be only one record with those values in the entire report.

Slide 21

The column aggregation functions specify what should occur within the column at aggregation time.

The record aggregation function specifies what should happen on the lowest print level (the record aggregated across all sort keys). In this example, that is the record aggregated to Store and Customer.

The group aggregation function specifies the action at all higher-level sort breaks. In this example, it applies to sort breaks on the store ID.

In this example, at the lowest level (the record level) and on subsequent subtotals (at the group level), the order total amounts will be summed.

Slide 22

Our new report will aggregate all records to the Store and Customer level as the lowest level of detail. These records are very similar to the subtotals printed on the detail report at the end of each customer section (the record aggregation function) and then at the sort break for stores (the group aggregation function).

Slide 23

It is possible to mix sort keys as categorized and as data, depending upon the report needs. In this example, we have changed the lowest sort key, Customer, from “Categorize” to “As Data.”

Slide 24

The report now shows the store on the left side of the screen, but the second sort key, Customer, is shown as columnar data. Note that the aggregated values are the same as on the previous report.

Slide 25

If we change the first sort key to display as data but leave the sort key footer option as “Print,” we will see subtotals for the store, but the data will be displayed in columnar format.

Slide 26

After the changes to the view, the sort keys are both shown as data. Subtotals are also shown on the report, unlike the first example report in this module, where no subtotals were shown. Note that the store ID is repeated on the subtotal line but the customer ID is not, indicating that this is a subtotal.

Slide 27

We can also change the sort keys so that new page headings are printed on a page break.

Slide 28

The change to the view creates a report in which each page contains the value for one and only one store. The first character on the report heading line (which is not shown on this graphic) contains a character that causes the printer to advance to the next page.

Slide 29

You can use the list in the middle of the View Properties Header/Footer tab 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.

Slide 30

The results show that, in addition to each page of the report containing the value for one and only one store, the value is now shown in the heading of the report. Note that the store ID is no longer listed at the top of each store break.

Slide 31

When you are creating subtotals, the Format phase provides more options than simply summing column values. You can specify that, when a sort key value changes or “breaks,” one of two things happens:

  • The column calculation specified in logic text for the column is repeated, or
  • The more detailed records at the next lowest sort break level are examined and one of the following values is printed:

a) The value from the first record in the group

b) The value from the last record in the group

c) The maximum value in the group, or

d) The minimum value in the group

In this example, one column will print the maximum value for the sort break and the other will print the minimum. When these functions are specified for record aggregation functions, they are repeated as the group-level aggregation function.

Slide 32

The output from this view shows that, for Customer 1, the highest order was $10,065.43 and the lowest order was $58.25. For Store 1, Customer 1’s order of $10,065.43 was the highest order amount, but Customer 2’s order of $33.12 was the lowest order amount.

Slide 33

Group-level functions may be different from record aggregation only when record aggregation is specified as Sum. When they are different, the detailed values for the lowest sort break will be aggregated. The group-level functions will be performed on each subtotal. In this example, we will see the accumulated orders for each customer, and the subtotals will be either the maximum accumulated customer orders or the minimum.

Slide 34

The results show the accumulation of all orders for Customer 1, rather than the highest or lowest individual order. At the store level, the group function is performed, which shows the highest or lowest accumulated orders for all customers at that store. For example, the accumulated order for Customer 1 is the highest for Store 1, and the accumulated orders for Customer 3 are the lowest.

Slide 35

In this example, we’ve selected First and Last as the “group by” functions. First will show the first record for a sort key break after the extract file has been sorted. Last will show the last record.

Slide 36

The first accumulated orders within Store 1 are for Customer 1; the last is for Customer 3. Using the “first” and “last” functions requires considering the interaction with the sort fields, particularly when using “first” or “last” record aggregation functions on summary views.

If the sort order of the extract file is not predictable because of duplicate values in all the view sort fields, the results of the “first” or “last” functions may seem random. Sorting a view by a time stamp, even if the time stamp is hidden from the final printout, can make the results predictable.

Slide 37

The last feature is called Sort Titles. Sort Titles allows for descriptions to be placed next to sort keys by doing a lookup in a reference file. These are useful when sorting by codes, such as customer or store IDs. They are specified on the bottom panel when you view a sort key property. Select the sort key and then, on the Sort Key Title tab, select the title to be used to describe the sort field. To do this, first select the logical record from the View Source list and then select the field from the Title Field list. Be sure to adjust the length of the title field to be shown on the report.

In this example, we have selected the customer email name to describe the customer ID.

Slide 38

The report now shows the customer email name next to the customer ID, which is used to sort the view. It is important to remember that sort titles do not affect the sort order of the view.

Note: If no sort title is found, the value “NO TITLE FOUND” is printed instead.

Sort titles are the only type of lookup performed in the Format phase. The data used for the lookup is prepared by the Reference phase in the same way that lookup data is used in the Extract phase.

Slide 39

This module provided an introduction to hardcopy formatted output. Now that you have completed this module, you should be able to:

  • Create a hardcopy report in detail format
  • Create a hardcopy report in summary format
  • Properly set column widths to avoid truncating data
  • Create reports with display modes of “As Data” and “Categorized”
  • Use record aggregation and group aggregation functions

Slide 40

Additional information about SAFR is available at the web addresses shown here.

Slide 41

Slide 42

This concludes the Module 10. Thank you for your participation.

Training Module 5: Introduction to the Format Phase

The slides for this video are shown below:

Slide 1

Welcome to the training course on IBM Scalable Architecture for Financial Reporting, or SAFR. This is module 5: “Introduction to the Format Phase.”

Slide 2

This module provides you with an overview of the Format phase. By the end of this training, you should be able to:

  • Describe the Format phase
  • Determine when the Format phase should be employed
  • Specify the view and JCL parameters necessary to use it
  • Verify the results of Format phase processing

Slide 3

The Format phase is the final possible phase of the Performance Engine processes. Views that require only extraction and no aggregation are completed at the Extract phase. Any view that requires aggregation or summarization must continue to the Format phase.

Slide 4

The Format phase is required to:

  • Summarize records
  • Use output record filtering which is applied after records are subtotaled
  • Produce sorted outputs
  • Create delimited files, and
  • Make hardcopy formatted printed reports

Slide 5

The Format phase is run for views by selecting the Use Format Phase check box on the View Properties screen. Views that require the Format phase can be run at the same time as views that end at the Extract phase (“extract only views”).

Slide 6

The “Extract Work File Number” identifies temporary file which receives output for this view from the Extract phase and is further processed by the Format phase. The output DD Name used in the Extract phase is a concatenation of letters “EXTR” with three digits representing the Extract Work File Number, beginning with zeros if necessary. For example, the Extract phase outputs for this view would be written to an Extract Work File with a DD name of EXTR001.

Slide 7

The primary SAFR program in the Format phase is GVBMR88. MR88 reads the MR77 VDP, REH, and RED files created by earlier steps, along with the assigned Extract Work file. It then sorts, summarizes, and formats the data, producing one or more View Output files.

Slide 8

Multiple Format phase processes can be run simultaneously, each processing one or more aggregation views. The Extract Work files have a special internal SAFR format. The total number of Extract Work files is set in the Extract phase as the STDEXTR=nnn parameter, where nnn is the file count.

Views with no Extract Work file number specifically assigned are given a default value in the Performance Engine execution. The assignment can change depending upon the mix of views being run, which may be different between test and production applications. Therefore, it is always best to assign an Extract Work file number to a view.

Slide 9

The final output file is designated by selecting the output logical and physical file on the View Properties screen. The additional steps needed to find the actual DD name that should be placed in the Performance Engine job stream are explained next.

Slide 10

To find the output DD name associated with this physical file, follow these steps:

  • Click Physical File in the navigation pane. The list of all physical files is displayed.
  • Find file name in the list of physical files (ID 30 in our example) and double-click the name. The Edit Physical File page opens.
  • Look in the Output subsection of the Dataset section of the page.

The output DD name in this case is OUTPUT06, which happens to be the same as the physical file name. This is a good practice in defining physical file names, but it isn’t required.

Slide 11

The DD statement for the output file must be placed in the JCL for the Format phase step that is running GVBMR88. Because a physical file was assigned to the view, the DD name associated with that physical file is used. In this example, the output DD name is OUTPUT06.

Note: If the physical file is not assigned, a default DD name is generated at runtime. This name is the letter “F” followed by the last seven digits of the view number, using leading zeros if required. For example, when running view 34, the DD name would be F0000034.

Slide 12

Select the option to aggregate or not aggregate work file records. Aggregation, or summarization, means subtotaling for a particular key. An example of aggregation includes subtotaling all orders for a particular customer at a particular store. The Performance Engine aggregated or summarized output includes one record for each customer at each store. Non-aggregated output lists each individual order by each customer at each store. Aggregation is similar to using a SORT=SUM statement in a sort utility.

The Format phase is required for all aggregation. Regardless of whether views are aggregated, the Format phase always results in a sorted final output file.

Slide 13

Record limits are often used during debugging to halt processing after a specified number of records are written. Although they are used in testing, these limits often are not appropriate for production applications. It is best to review the need for Extract phase limits and Format phase limits at the end of development.

Slide 14

The Format phase is also required if delimited file output is desired. Use the options in the upper right portion of the screen to select the types of delimiters needed for both strings and fields.

To specify Format phase Record Filtering, click the Create button. This allows you to specify the characteristics of records to be written or discarded after aggregation. Record filtering will be explained in another course module.

Selecting the Zero-Value Record Suppression check box is a simple way of creating a Format phase record filter that will omit all rows on which all aggregated and calculated amounts are zeros.

Slide 15

As noted, the Format phase always sorts the Extract Work file and resulting output files. Column sort keys determine how the files are sorted. Sort keys are shown in the view in yellow. The numbers within the yellow cell 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. In this example, stores will be sorted first, customers second, and both will be in ascending order, for example, from 0 to 9.

Slide 16

Right-click, anywhere on the column, to open a list. Left-click to select the Make sort key option to assign that column as a sort key.

To remove a sort key from a column, follow the same process, but select the Make non-sort key option.

Slide 17

After you assign a sort key to a column, you can click in the yellow cell to display the sort key properties.

Slide 18

You change sort attributes using the fields on the Sort Key Properties tab, including the sort order, or the key number.

Changing the sort key number on the store ID from 1 to 2 means that the output file will be sorted first by customer, and then by store. Note that column placement does not affect the sort key order. Although it may be less intuitive, it is possible to sort first on customer and then by store, even though Store is the first column in the output file.

Slide 19

The order in which the file will be sorted can also be displayed by double-clicking in the Sort Keys cell. The expanded rows show which field will be sorted first and which will be sorted second.

Slide 20

Aggregation views typically have one or more alphanumeric sort columns, followed by one or more columns of amounts or counts, which are typically subtotaled. To specify the type of subtotaling, double-click in the Record Aggregation Function cell and select the function from the drop-down list. 

Note that columns that are not sorted or subtotaled are permissible in Format phase views. However, when multiple records are collapsed into a single output record by the sort keys, only one of the underlying detail values is retained in the final output.

Slide 21

You can change how numbers are displayed. For example, you might want a masked value, with commas marking thousands and a period for decimals points, on a printed report. To select a mask, double-click in the Data Type cell, and then select Masked Numeric from the drop-down list.

Make sure that the column is wide enough to accommodate the size of the ultimate subtotal value. For example, the addition of two records with 3-digit numbers will require a 4-digit column. The overflow and error fill values on the View Properties Format Phase tab will be used if the column or mask size is too small.

Slide 22

The Format phase engine GVBMR88 requires a pair of files that are produced in the Extract phase; the EXT and the SXT files. The EXT file contains the actual extracted data, in the special SAFR internal format. The SXT file contains sort statements on how to sort the outputs. GVBMR88 uses a sort utility before performing the aggregation and format functions.

Slide 23

You assign an Extract Work file number on the View Properties tab. Multiple views can share an Extract Work file.

Extract Work files for the Format phase views:

  • Are written in the internal SAFR Format phase format.
  • Receive a header record for each view written to that Extract Work file containing a count of the records extract for that specific view, and
  • Receive a control record containing a count of the total records written to that Extract Work file.

These additional records are visible on the Extract Phase Control report. Note that only 12 records were read by MR95 from the order file, but 13 records were written for view 34 to the file EXTR001: 12 input plus 1 header record. The total records written to EXTR001 are 14: the 12 input, 1 header, and 1 control record.

Slide 24

The MR88 control report shows the total records that were read from the Extract Work file (14), and the resulting aggregated or summarized records written out to all the data files (04). It also shows a breakdown of the records written for each view to their individual data files, which is also 4.

Slide 25

This module provided an overview of the Format phase. Now that you have completed this module, you should be able to:

  • Describe the Format phase
  • Determine when the Format phase should be employed
  • Specify the view and JCL parameters necessary to use the Format phase
  • Interpret the results of Format phase processing

Slide 26

Additional information about SAFR is available at the web addresses shown here.

Slide 27

Slide 28

This concludes the Module 5. Thank you for your participation.