Finding Data in Reports and Files

Usually, when performing a balancing job, you are given a report or file to work with, and that report or file clearly was not designed with Control-M/Analyzer in mind. You may be working with a report that was designed a long time ago, or one that is very complex.

Some of the challenges you may encounter, when searching for data in a report or file are:

• knowing when and how to use the NOT EQUAL parameter
• the data for which you are searching do not always appear in the same line or place on a report page
• difficulty in uniquely identifying data in a WHEN clause
• knowing how to use the TYPE AV and TYPE SU parameters

  These parameters operate only on numeric data. Therefore, variables that are extracted from a file or report with one of these operators must be numeric.

The following are some suggestions for alternate approaches:

Using the NOT EQUAL Condition to Identify Data

Sometimes a group of report lines or file records can be readily identified because they are the only lines that do not contain certain characters. See, for example, Problem 4: Balance Divisional Accounting Files.

In this example, two types of file records exist—detail records and total records. One way of separating the two types of records is to describe detail records as not having the characters "TOTALINVOICES" in columns 3 through 15.

In using a NOT EQUAL condition, it is important to be 100% precise when specifying the column width. You cannot specify a column range where the string can be found. Instead, you must specify the exact columns where the string is located. This is because Control-M/Analyzer compares the string which it has extracted from the file, character by character, to the string that you specified in the WHEN statement.

For example, what would happen if you were to specify a WHEN parameter to search for detail records as described above? Assume that your WHEN statement specifies columns 1 through 20 for a string NOT EQUAL to TOTALINVOICES. All the detail lines meet the NOTEQUAL search criteria, which is exactly what you expected. But, when the TOTALINVOICES line in the report is examined, it also meets the criteria. This is because a blank in column 1 had been compared to the character "T" from the WHEN statement. Because the blank and the character "T" are not equal, the total line is therefore presented as a detail line.

Data in Different Locations in a Report

Sometimes a line of data that you are looking for cannot be uniquely identified by itself. For example, you might have a sales analysis report that shows detailed sales information for a product, followed by a line of total amounts for that product. Further, the total line has no special characters, such as "TOTAL SALES," with which it can be identified. Also, the number of detail lines occurring before the total line is printed may be any number. Therefore the total line can occur almost anywhere on the page.

For Control-M/Analyzer to be able to find the line, you must establish a unique relationship between this total line and another line.

For example, perhaps the line can always be found two lines before the next product heading, which contains the characters SALES ANALYSIS FOR PRODUCT.

You could then define a WHEN clause that searches for the string "SALES ANALYSIS FOR PRODUCT," and then define a DO EXTRACT from that line, minus 2.

Data that Must Be Associated with External Data before Balancing

Assume, for example, that you have a summary report of all of the accounting transactions for your organization for three months. The report shows the three month time period range as a heading line on the report, but the exact date is not shown within each detail line. However, each detail line does have a transaction number from which it is possible to determine a transaction date by looking up this number in a database, such as DB2, ADABAS, or ORACLE.

The auditing department has asked for a balancing report showing all the transactions for one account number, for one month.

Assume that you can define a WHEN parameter to select all transactions for a given account number from the report. However, to determine the time period to which a transaction belongs, you need to

• examine the detail line, which contains a transaction number
• find the date with which this transaction is associated

  To do this, you have two alternative approaches:

  • You can set up a table of variables that provides a transaction date for a range of transaction numbers, and look up the transaction number in this table.
  • You can issue a DO CALLUSER parameter. This would call a user-written routine that would look up a transaction number in a database, and return a date to your rule.
• use groups of IF statements to determine if this transaction meets your criteria

Problem 6 in chapter 2 illustrates an example of how to set up and use a table of variables.