The DATAIR News 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