The DATAIR News

Spring 1996 - Page 2
SPECIAL REFERENCE SECTION

USING REPORT WRITER TO DO CLASS ALLOCATIONS
By Randy Glancy

Report Writer can be used for much more than just customizing participant statements or contribution reports. By printing to a file instead of the printer, you create data that you can import into word processors, spreadsheet programs or... back into the Pension System.

Let's say that you have a car dealership as a client. The dealership sells Lexus and Hyundai. You want to give a 14% contribution to the Lexus employees and 3.5 % to the Hyundai employees. You can use your calculator and type the overrides in on screen 31 (let's say it's a big dealership with 130 employees) OR you can use Report Writer to calculate the answers. A little time spent creating a report format can save you HOURS in calculating and typing.

Here's how:

The calculations are done by a report format (did you know Report Writer could do math?). The idea here is to duplicate the operation you would do on your calculator. That is, figure out if the employee is active (eligible for contribution), where he works (Lexus or Hyundai), what his employee number is and what his contribution would be. Then once you know that, put the answer on screen 31 as an override in the EMPLOYER CONTRIBUTION field. We'll use the Import cycle in the Pension System to accomplish that part.

Extracts are probably the easiest formats to do because they require almost no page layout.

The format in this case will look like import data in the NATIVE FORMAT. See page 4.4 - 4 in the Import section of your Defined Contribution System Manual for a complete discussion of importing data in the Native Format.

You can use any text editor such as DOS Edit or Windows Notepad to create or edit your report format. When you save the file, it MUST have the extension .RPT. Save it to your Pension program or User defined directory. For this example lets call our format CLASS.RPT.

This is what the report format should look like when it's finished:

.TI CLASS ALLOCATION FORMAT
.PA NOPAGE
.PA NONAME
.PA NOHEAD
.PA NOCNTL
.PA LINESPACE=0
.SE 04
.SE C 050 = L
.FO ZEENO,~~~~~~~~~
.DA C 000 'employee number
.FO D-064, ~~~~~~~~~~
.DA D 001 * 'compensation multiplied by
.DA # .14 , 'constant which is the contribution rate .SE 04
.SE C 050 = H
.FO ZEENO,~~~~~~~~~
.DA C 000 'employee number
.FO D-064, ~~~~~~~~.~~
.DA D 001 * 'compensation multiplied by
.DA # .035 ,

Following is a line by line breakdown of the format and step by step instructions for doing the class allocation.

The first line in any report format is usually the title (see page 41 in the Report Writer manual). It will look like this:

.TI CLASS ALLOCATION FORMAT

The print parameters used in extract formats turn off the automatic features of Report Writer such as the automatic page headings, page breaks, employee names and numbers, etc. More information on print parameters can be found on pages 35-37 and 47 in the RW manual. Use these print parameters for your class allocation format:

.PA NOPAGE
.PA NONAME
.PA NOHEAD
.PA NOCNTL
.PA LINESPACE=0

Now let's determine which employees to consider. First of all, we only want employees who are eligible for contributions. An employee's INELIGIBLE CODE is use to accomplish this. It is found on the worksheet in the EMPLOYEE WORK section and the definitions are on page 39 in the RW manual. For this example we want active employees and terminees with 1000 or more hours to get contribution, so we will use as our first selection criterion ineligible codes 0 and 4. In our report format, that looks like:

.SE 04

When you set the plan up, make sure that you give the members of each group a different EMPLOYER CODE. The field for this is found on Screen 30. For this example, let's use L for Lexus and H for Hyundai. This is data element C 050. Look on page 68 of the RW manual for a complete list of what's on screen 30 (and on the rest of the employee screens) and when you're on screen 30 (or any screen for that matter) look in the very bottom right-hand corner. Wherever your cursor is, the system displays the corresponding data element on the screen (either you've never noticed it or you've always wondered what that was). It's also helpful to have different employer codes in situations like these to print reports (account balance statements for example) for a specific group of employees. In our report format, let's calculate the Lexus group first:

.SE C 050 = L

Let's take a deep breath now and review. So far in our report format we've: 1) given it a title, 2) turned off the automatic print features, 3) told it to only look at employees eligible for contribution, and 4) told it to look at the Lexus group. Now let's do the part that will tell the system who the employee is and how much the contribution will be.

Remember that we're going to put this information back into the system in the Native Format. Basically that means that when the Import program of the Pension System reads this data, it will see an identifier followed by the data to import. This controls the "page layout" of our format. When importing employee data, we need to know first; the employee number, and then; the data that we want to import into the plan for that employee. In our example, that data to import will be a contribution override on screen 31.

Employee number is called a Control Identifier. Plan numbers, account numbers and insurance record numbers are also Control Identifiers. Control Identifiers ALWAYS precede data to be imported. If there is no Control Identifier, the system has no way of knowing WHERE a piece of data belongs. In our report format, the Control Identifier is ZEENO.

.FO ZEENO,~~~~~~~~~
.DA C 000 'employee number

.FO because the information is coming from the employee level in the system (page 31 in the RW manual).

ZEENO because that tells the import program the next thing it sees will be an employee number

~~~~~~~~~ (9 tildes or squiggles) because A) that's how long an employee number can be and B) a "~" denotes position of data in a report format.

.DA because we need to tell Report Writer to take something out of the system and put it in the ~~~~~~~~~~'s.

C 000 because it is the element number for employee number.

'employee number because anything after a single quote is ignored by the system and is used for commenting.

Hang on, we're almost done. The last bit of information that we need is the contribution itself. On the calculator you would multiple the employee's salary by .14. We do the same thing in the report format. It looks like this:

.FO D-064, ~~~~~~~~.~~
.DA D 001 * 'compensation multiplied by
.DA # .14 , 'numeric constant which is the contribution rate
D-064 because that is the element for employer contribution on screen 31.

~~~~~~~~.~~ because this is where we want to put the data. If there were no decimal point the contribution would be rounded to the nearest whole dollar instead of dollars and cents.

.DA because this is data

D 001 because this is the element for compensation on screen 30

* because we want to multiply by the next line (see page 27 in the RW manual).

# because that means a constant ( see page 26)

.14 because that is the contribution rate for the Lexus employees

, (the comma) - so that a comma won't print in the result (also on page 26)

The whole format should look like this so far (spaces added between lines for clarity they are ignored by the system):

.TI CLASS ALLOCATION FORMAT
.PA NOPAGE
.PA NONAME
.PA NOHEAD
.PA NOCNTL
.PA LINESPACE=0

.SE 04

.SE C 050 = L

.FO ZEENO,~~~~~~~~~
.DA C 000 'employee number

.FO D-064, ~~~~~~~~.~~
.DA D 001 * 'compensation multiplied by
.DA # .14 , 'numeric constant which is the contribution rate

Don't forget that we have to worry about the Hyundai employees too. The easiest thing to do is to block this part:

.SE C 050 = L

.FO ZEENO,~~~~~~~~~
.DA C 000 'employee number

.FO D-064, ~~~~~~~~.~~
.DA D 001 * 'compensation multiplied by
.DA # .14 , 'numeric constant which is the contribution rate

And then copy that block on to the end of what you just did. The only things that have to be changed are the employer code and the contribution rate. When everything is done, it should look like this:

.TI CLASS ALLOCATION FORMAT
.PA NOPAGE
.PA NONAME
.PA NOHEAD
.PA NOCNTL
.PA LINESPACE=0

.SE 04
.SE C 050 = L

.FO ZEENO,~~~~~~~~~
.DA C 000 'employee number

.FO D-064, ~~~~~~~~.~~
.DA D 001 * 'compensation multiplied by
.DA # .14, 'constant which is the contribution rate

.SE 04
.SE C 050 = H <_-DIFFERENT EMPLOYER CODE
.FO ZEENO,~~~~~~~~~
.DA C 000 'employee number

.FO D-064, ~~~~~~~~.~~
.DA D 001 * 'compensation multiplied by
.DA # .035, <_-DIFFERENT RATE

Note that with blocking and copying it takes almost no effort to add as many sections as you have rate groups.

Now let's calculate the contributions and put them in the right place. Here's what you need to do:

1. Select Cycle 3 (Reports) for the plan. On the fourth page of the prints at the top of the screen where it has blanks for entering report formats type: CLASS. It is not necessary to include .RPT.

2. On the same screen, in the field called OUTPUT FILE, enter a file name, for example CARDEAL.DAT

3. Enter cycle 4 on the Pension System Main Menu. That will print the report you told it to print to the file name you specified. When it's done, it will return to the Main Menu.

4. Select Cycle I (Import). When it asks the name of the file to import you enter CARDEAL.DAT.
You DO NOT need a .DIS file because the data is already in the Native Format which needs no translation by the system.

5. Once the data is loaded, calculate the plan and print your discrimination tests.

If you pass, you're done. If not, you may need to lower the contribution rate for the Lexus employees or raise the Hyundai people. All that's necessary is to edit the report format and change the appropriate .DA # ... line to change the allocation rate and repeat steps 1 through 5 above.

To learn more about using Report Writer and Import, register for the training class that we offer in our office or sign up for one of our regional classes such as the one in Seattle in September. We also offer classes on the Defined Contribution, Defined Benefit, Cafeteria, and Retirement Document systems along with Pension Basics and a one day seminar on Cross Tested Plans.

Jobs/Help Wanted

Please let us know:

* If your firm is hiring. We may have heard from a DATAIR user who is relocating or we may have an appropriate resume in our files.

* If you are moving, perhaps because of your spouse's transfer. We may have heard of openings in that part of the country. At the very least, we may be able to help you start building a new network, putting you in contact with DATAIR subscribers in your new location.

* If you are relocating to the Chicago area. Please remember that you already have friends here at DATAIR. Also, from time to time, we have openings as well.

Right now is one of those times. We are adding to staff, looking for experienced:

Sales/marketing associates
Sales assistants
Pension/Benefits administrators for customer support positions
Visual Basic programmer/analysts

However, please note that we do not solicit inquiries from employees of current DATAIR customers. If you are employed by a DATAIR customer, you must have your employer's permission to discuss employment with DATAIR.

If we can help you fill or find a position, or if you know of someone who may be interested in employment at DATAIR, please let us know!

System Training & Seminar Schedule

In 1996, we are expanding our systems-related training schedule to devote more time to the topics you requested. We hope the changes we are making will better meet your system training needs. The following training classes will be held at our Westmont, Illinois office.

June 17-21 | July 22-26
August 19-23 | October 21-25
November 18-22 | December 16-20

If you wish to attend a training class at our office or schedule training at your office, please contact Suzanne.

REGIONAL TRAINING
Next stop...Seattle! If you are interested in attending a training class on Defined Contribution and Data Handling in the Seattle area, please contact Laurie.


CURRENT RELEASES

PE Pension Administration 2.58
PR Pension Reporter 4.50a
RD Retirement Plan Doc 1.34a
CA Cafeteria Administration 4.40
CD Cafeteria Plan Document 1.11
PA Plan Accountant 2.02
QP Qualified Plan Distribution 1.30
FA FAS'87 Reporting 1.11
PT Participant Term. Calc 1.31
DE Data Entry 1.12

c The DATAIR News is published by:
DATAIR Employee Benefit Systems, Inc.
735 North Cass Avenue
Westmont, Illinois 60559-1100
(630) 325-2600 FAX: (630) 325-2660

The DATAIR News strives to provide our customers with valuable and enjoyable information about DATAIR software, services and the pension industry. Reader contributions are welcome.


Home Page | Back to Spring 96 Index | News Letter Index