CA Ramis RELATE to generate records

Products

• Ramis Reporter

Issue/Introduction

What is RELATE?

RELATE is the CA Ramis or CA Ramis Reporter component that enables you to quickly and easily combine data from multiple sources for reporting or other purposes. Data can be accessed from a wide variety of sources including CA Ramis database files, previously built relations, or any external or RDBMS files accessible to CA Ramis and CA Ramis Reporter.

RELATE gives structure-independent access to data with no limit on the size of the source file or on the number of records selected. The full power of the CA Ramis reporting facilities is available to customize reports from relations.

With RELATE, you can build one or more relations by matching one or more key fields. All of the relational operations are available: INTERSECTION, UNION, EXCLUSION, PROJECTION1, PROJECTION2, PROJECTION1 MINUS PROJECTION2, and PROJECTION2 MINUS PROJECTION1. You can also specify whether combinations of records are to be built based on the existing set of key values in the data.

Environment

Release:       7.4

CA Ramis
CA Ramis Reporter

Cause

But, what if some or all of the desired key values do not exist in the data files or in the selection of data?

This article provides a technique for generating records in a relation when some or all of the key values might not exist

Resolution

How Can I Generate Records When the Set of Key Values Might Not Exist?

Let's use the sample file SALESDATA to demonstrate the technique. File SALESDATA, along with additional sample files, can be found on the CA Ramis database built during the IVP step of the product installation process.

File SALESDATA is a multiple level Advantage CA Ramis database file, which is defined as shown by the RAMINDEX utility:

Let's suppose we want to produce a report showing the total number and dollar amount of "executive" chairs sold to certain customers. We want the report to include the new chair model, product number CH4470, that has just started to become available and has not yet been included in the full system. For this example, let's say there are two models:

Product Number   Product Name
--------------   -----------------
CH2470           EXECUTIVE CHAIR
CH4470           EXECUTIVE COMFORT

Now, let's suppose we want to report the total number and dollar amount of executive chairs sold to our biggest customers REGAL MFG whose customer number is 11043 and PEARSON AND TALMADGE whose customer number is 71032.

Running the following request against the SALESDATA file indicates that of the two chair products, only one of them was purchased by PEARSON AND TALMADGE or REGAL MFG to date.

TABLE
CENTER
' Total Sales of Executive Chair Products'
SKIP LINE
' For Selected Customers:'
'  REGAL MFG'
'  PEARSON AND TALMADGE'
SKIP LINE 2
FILE SALESDATA SPACE 1
SUM UNITS AS 'Units,Sold'
AND VALUE AS 'Dollar,Amount'
AND GRAND TOTAL
BY CUSTOMER AS 'Customer'
BY PRODNUM AS 'Product Number'
DISPLAYED WITH PRODUCT AS 'Product,Name'
IF PRODNUM IS CH2470 OR CH4470
IF CUSTNUM IS 11043 OR 71032
END

Results:

Since these customers have purchased only one of the two executive chair products, the report does not reflect any information for the new product code CH4470. However, we want the report to show all of the executive chair products, so we need to generate the missing report data.

Here's how...

To generate report records, we can build a flat QSAM file that will contain the set of the desired chair product codes and product names, then use RELATE to match the existing data from file SALESDATA to the data in the new file. This technique has the added advantage of providing a method of varying the key values used for a selected report, since the data in the QSAM host file can be changed either by direct editing or as a result of some other job process.

First, let's build the new file definition for the QSAM file named GENPROD which will contain a record for each of the desired product codes and product names.

ERASE*
FILE QSAMGENPROD
END
MAINTAIN FILE RAMEXTMASTER
TRANSACTION SOURCE IS IMBEDDED
FORMAT IS FREE
WHEN TRANSACTION DOES NOT MATCH ON
FILETYPE, FILENAME, LEVEL, FIELDNAME, DEFINITION
INCLUDE THE RECORD
DATA
FILETYPE=QSAM,FILENAME=GENPROD,LEVEL=1,
NAME=GENPNUM, ALIAS=GENPN, USAGE=A6, ACTUAL=A6;
NAME=GENPNAME,ALIAS=GENPNM,USAGE=A20,ACTUAL=A20;
NAME=FILL,    ALIAS=FILL,  USAGE=A1, ACTUAL=A54;
END

The new QSAM file named GENPROD is defined as shown by the RAMEXTINDEX utility:

Then, we need to create the external QSAM file with a fixed record length of 80 and add the records that will contain the requested product code values. For this example, we want to see records for the executive chair product codes CH2470 and CH4470, so we've created the QSAM file with the following records:

The QSAM file named GENPROD must be defined to the Advantage CA-Ramis or Advantage CA-Ramis Reporter session or job using appropriate operating system statements (FILEDEF, ALLOC, DD or DLBL).

Now, using RELATE...

We create a "dummy" field in a DEFINE for both the new file GENPROD and the data file SALESDATA. Since the dummy field is the same value, all of the records will match. With this method, the resulting relation will contain all of the records from file GENPROD associated with the customer records from file SALESDATA.

This is the DEFINE set to create the dummy field for file GENPROD:

DEFINE DEF1 RETAIN
FILE GENPROD
DUMMY/A1 = '1';
END 

This is the DEFINE set to create the dummy field for file SALESDATA:

DEFINE DEF2 RETAIN
FILE SALESDATA
DUMMY/A1 = '1';
END  

Now, let's perform the RELATE keeping the UNION of all chair products for the selected customers:

RELATE
DEFINE  DEF1
PROJECT GENPROD   USING EACH GENPNUM AND GENPNAME BY DUMMY
DEFINE  DEF2
PROJECT SALESDATA USING EACH CUSTNUM AND CUSTOMER BY DUMMY
IF CUSTNUM IS 11043 OR 71032
KEEP UNION REPLACING CN_GEN_PN WITH COMBINATIONS
END 

NOTE: We've selected the two customers (CUSTNUM values 11043 and 71032).

Using this TABLE request, let's see the generated records in relation CN_GEN_PN:

TABLE FILE CN_GEN_PN PRINT P1 AND P2 AND P3 AND P4 AND P5 END

Notice that relation CN_GEN_PN above contains a record for each executive chair product code for each customer.

Now, let's use relation CN_GEN_PN against file SALESDATA, this time retrieving the desired data fields CUSTOMER (the customer name), VALUE (the sales amount) and UNITS (the number of sales units) and keep all of the records from relation CN_GEN_PN.

RELATE
PROJECT CN_GEN_PN USING CUSTOMER AND GENPNAME BY CUSTNUM BY GENPNUM
PROJECT SALESDATA USING VALUE AND UNITS       BY CUSTNUM BY PRODNUM
IF CUSTNUM IS 11043 OR 71032
KEEP PROJECTION1 REPLACING CN_ALL_PN WITH DATA FROM BOTH
END

Now, we can run the desired report, this time against relation CN_ALL_PN.

TABLE
CENTER
' Total Sales of Executive Chair Products'
SKIP LINE
' For Selected Customers:'
'  REGAL MFG'
'  PEARSON AND TALMADGE'
SKIP LINE 2
FILE CN_ALL_PN SPACE 1
SUM UNITS AS 'Units,Sold'
AND VALUE AS 'Dollar,Amount'
AND GRAND TOTAL
BY CUSTOMER AS 'Customer'
BY GENPNUM AS 'Product Number'
DISPLAYED WITH GENPNAME AS 'Product,Name'
END

Notice the results include all of the executive chair product codes.

 

Additional Information

For more information on reporting from external files and user routines QLINK and QUSER, refer to the CA Ramis publication Reporting from External Files (REF).

The CA Ramis Version 7.4 set of documentation can be found at CA Ramis Bookshelves and PDFs.