Report Writer User Guide
payentry.com™ Report Writer User Guide MPAY Software 2006
1
Table of Contents INTRODUCTION ________________________________________________________________ 4 GETTING STARTED ____________________________________________________________ 4 Accessing the Report Writer _______________________________________________________ 4 Creating a New Report ___________________________________________________________ 5 Report Type ________________________________________________________________ 5 Report Title_________________________________________________________________ 7 Sub Title ___________________________________________________________________ 7 Saving Your Report ______________________________________________________________ 7 Editing an Existing Report_________________________________________________________ 7
CREATING A REPORT__________________________________________________________ 8 Fields __________________________________________________________________________ 8 Field Code _________________________________________________________________ 8 Heading___________________________________________________________________ 10 Len ______________________________________________________________________ 10 Dec ______________________________________________________________________ 11 L/R _______________________________________________________________________ 11 Fill _______________________________________________________________________ 11 Order _____________________________________________________________________ 11 Remove___________________________________________________________________ 11 Format____________________________________________________________________ 11 Sorts __________________________________________________________________________ 12 Field _____________________________________________________________________ 12 Description ________________________________________________________________ 13 Sub Total _________________________________________________________________ 13 Order _____________________________________________________________________ 13 Remove___________________________________________________________________ 13 Filters _________________________________________________________________________ 13 Field _____________________________________________________________________ 14 Comparison/ Value _________________________________________________________ 14 And/Or____________________________________________________________________ 14 Remove___________________________________________________________________ 15
Advanced _________________________________________________________________ 15 payentry.com™ Report Writer User Guide MPAY Software 2006
2
RUNNING A REPORT _________________________________________________________15 Running a Report From the Editor _________________________________________________ 15 Running from the Report Screen __________________________________________________ 15
EXAMPLE REPORT 1 __________________________________________________________16 Step 1. Creating a New Report_____________________________________________________ 16 Step 2. Adding Fields ____________________________________________________________ 16 Field 1. Plan Id ____________________________________________________________ 17 Field 2. SSN______________________________________________________________ 17 Field 3, 4. Employee Name __________________________________________________ 17 Field 5. Current Contribution ________________________________________________ 17 Field 6. YTD Contribution ___________________________________________________ 18 Field 7. Employee Contribution Rate _________________________________________ 18 Step 3. Sorts ___________________________________________________________________ 19 Step 4. Filters___________________________________________________________________ 19
payentry.com™ Report Writer User Guide MPAY Software 2006
3
Introduction The built in report writer on Payentry.com is a quick and easy way to create and store custom reports for your payroll and hr data. The report writer allows you to create, edit, and save an unlimited number of custom reports. This document describes the various features and functions of the report writer as well as providing some sample reports to get you started.
Getting Started This section will explain how you can access the report writer, create and save new reports, edit existing reports, and remove unwanted reports.
Accessing the Report Writer The report writer can be accessed by selecting the Reports link from the main menu. This link will take you to the main reporting area of Payentry. When you enter this area, the "Request Area" tab should be active. This tab will display all existing report writer reports at the top, and all other available system reports on the bottom. To run any existing report writer reports, simply select the "Run >" link displayed to the right of the report name. To modify any of the reports, press the "Edit >" link.
payentry.com™ Report Writer User Guide MPAY Software 2006
4
To create a new report, select the "Report Writer" tab. (This tab can also be used to edit an existing report.
Creating a New Report To create a new report, select the "Report Writer" tab and press the "New" button. After you press New, the screen will update and you will see fields for entering the Report Type, the Report Title, and a Report Sub Title.
Report Type The report type selection is one of the most important, and most difficult, decisions you will need to make when creating your report. This setting controls the type of data available for the report as well as the level of detail that a report goes into.
The following table lists some of the different report types available.
Report Type
Available Data
Report Detail
Common Uses
Employee Accruals
Company Demographic, Employee Demographic, Employee Accrual Setup Company Demographic, Employee Demographic, Employee Deduction Setup
1 line per accrual per employee.
Time Off Balances EE Accrual Setup
Employee Deductions
Employee Dependents
Company Demographic,
payentry.com™ Report Writer User Guide MPAY Software 2006
1 line per deduction per employee.
EE Deduction Setup. (for reports that include current, mtd, or ytd deduction amounts use the Employee List type.) 1 line per dependent Dependent Listing. 5
Employee Direct Deposits
Employee Education
Employee Emergency Contacts
Employee Events
Employee Fringe Benefits Employee List
Employee Paychecks
Employee Rates
Employee Reviews
Employee Skills
Employee Taxes
Employee Demographic, Employee Dependents Company Demographic, Employee Demographic, Employee Direct Deposit Setup Company Demographic, Employee Demographic, Employee Education Company Demographic, Employee Demographic, Employee Emergency Contacts Company Demographic, Employee Demographic, Employee Events Company Demographic, Employee Demographic, Employee Fringe Setup Company Demographic, Employee Demographic, Summarized Payroll Information Company Demographic, Employee Demographic, Employee Prior Checks Company Demographic, Employee Demographic, Employee Rate Setup Company Demographic, Employee Demographic, Employee Reviews Company Demographic, Employee Demographic, Employee Skills Company Demographic, Employee Demographic, Employee Tax Setup
per employee. 1 line per direct deposit per employee.
EE Direct Deposit Setup
1 line per education entry per employee.
EE Education Listing.
1 line per emergency contact per employee.
EE Emergency Contact Listing
1 line per event per employee.
EE Event Listing
1 line per fringe EE Fringe Setup earning code per employee. 1 line per employee. Employee List, 401k export files, Reports with payroll numbers. 1 line per check.
Check Register
1 line per rate code per employee.
EE Rate Setup
1 line per review per employee.
EE Review Listing
1 line per skill per employee.
EE Skill Listing
1 line per tax code per employee.
EE Tax Setup
For example, if you needed to create a report showing all of the different recurring deductions set up on employees including the deduction code, the effective dates, and the contribution amounts you would select a type of "Employee Deductions" because it will give you access to employee deduction setup information and because it will have 1 line for each individual deduction code set up on an employee. If your company had 2 employees "Joe" and "Lisa" each with a 401k and a 125 deduction, the report would have 4 lines on it, 1 for each individual deduction. On each line you can display any piece of setup information for the deduction. If you needed to do a report to show the current amounts paid towards specific deductions you would use an Employee List report (not a deduction list), because the employee list has 1 line per ee. On each line you can display any piece of ee information and summarized payroll information.
payentry.com™ Report Writer User Guide MPAY Software 2006
6
The Employee List type is the most common and will probably be used for a majority of your reports. If you do not know which type to select, try this type first. NOTE: The list contains several types not listed in the table above. Most of those types are for showing company setup information for clients with multiple codes on Payentry. Those types will not be covered in this document.
Report Title This is the name of the report. This will be used to display the report in the list of Report Writer reports on the reporting screen. This will also be displayed on the left hand side if the report title block which is displayed on the top of all pages in the report.
Sub Title This is an optional field. Whatever you enter into this field will be displayed in the report title block, directly underneath the report title.
Saving your report Once you have entered the Report Type and Title you can press the Save button (located between the New and Run buttons) to save your new report and to bring up the editing screen. You should also press the save button periodically while you are editing your report.
Editing an Existing Report To edit an existing report, you can press the "Edit >" link to the right of the report name on the Reports screen, or select the report in the drop down window on the Report Writer tab.
payentry.com™ Report Writer User Guide MPAY Software 2006
7
Creating a Report All creating and editing of reports is performed using the report editor screen shown below.
The left hand side of the screen contains the field selection tool which displays all of the data fields available to you for your report. The right hand side contains four tabs (Fields, Sorts, Company Filters, and Employee Filters) which are used to design and build your report.
Fields The "Fields" tab is used to select the data to display on your report. Each entry in the list of fields produces a column of data on the finished report. The columns are displayed from left to right starting with the first row in the fields list. The report displayed above will have 8 columns with the first column being employee id (EE #).
Field Code The "Field" column is used to enter the field code. This code tells Payentry what data to display in the column. There are 4 different types of field codes that you can use in your reports (Data Fields, Static Text, Simple Formulas, and Special Payroll Formulas)
Data Fields Data fields contain actual company and employee setup data that is stored on the Payentry site for your company. To enter a data field, simply click on the field name in the field selection tool located on the left hand side of the screen. Once you click on the field (single click is fine), it will automatically be added to the list of fields included on your report.
payentry.com™ Report Writer User Guide MPAY Software 2006
8
Static Text If you want a column to contain specific text you simply need to enter the text you want surrounded by quotes into the "Field" column. For example, if you were creating an export file and needed to put the characters T01 at the beginning of each line you would enter "T01" (with the quotes) as the first field in your report. Note: The text you enter will be repeated on each line of the report.
Simple Formulas The report writer will allow you to enter a formula in place of a field. A formula can be used to provide special text formatting, such as displaying the employees first and last name together in the same field. A formula can also be used to add values together such as combining ee and er 401k contribution amounts. For example, the formula ee.lastName + ", " + ee.firstName can be used to format the employees name as last name, first name. Formulas can be written using the VB scripting syntax and can make use of most VB script methods and functions. VB scripting will not be covered by this document.
Special Payroll Formulas The report writer has several special payroll functions available to get current, MTY, QTD, and YTD payroll numbers as well as current deduction and rate setup information. These functions are available in the field selection tool at the bottom of the Employee section.
The Deductions section is used to get the current deduction setup information (based on date selected when running the report) for any payentry.com™ Report Writer User Guide MPAY Software 2006
9
deduction code. To use one of these item simply expand the deductions group in the field selection tool in order to see a list of all deductions. Find the deduction you want and expand it to see all of the available deduction setup fields. Single left click on any field to add it to the report. The Rates section is used to get the current rate code setup information (based on date selected when running the report) for any rate code (including base rate). To use one of these item simply expand the Rates group in the field selection tool in order to see a list of all rate codes. Find the rate you want and expand it to see all of the available rate setup fields. Single left click on any field to add it to the report. The Current section is used to enter payroll numbers (Earning, Deduction, and Tax) for the current payroll period. NOTE: If the report is run across a range of dates, the numbers will be the sum total for that range. If you expand the Current section, you will see all earning, deduction, and tax codes, as well as all code sets (Earnings will be preceded by an E, Deductions by a D and taxes by a T). Expanding any code (or code set) will give you the available totals for that code. For earnings you can select total hours or amount, for deductions you can select total amount, and for taxes you can select total taxable wages, capped taxable wages, or total amount. The CMTD, CQTD, and CYTD sections function the same as the Current section except they are designed to display total Month to Date (CMTD), Quarter to Date (CQTD) and Year to Date (CYTD) numbers.
Heading The column next to the field code is used to enter the heading that will appear at the top of each data column on the report. This field is automatically filled in when you select a field from the field selection tool.
Len The Len column is used to specify the number of characters to display in the column. If the actual length of the data is more than the number you enter, the data will be truncated and only the length you specify will be shown. This field is automatically filled in when you select an item from the data selection tool (you can change the value if you wish). For fields that you manually enter such as static text and formulas you will need to enter a value in the Len field. NOTE: If you leave the value in the Len field at 0, the data column will not be displayed.
payentry.com™ Report Writer User Guide MPAY Software 2006
10
Dec This field is used to specify the number of decimal places to display for number fields such as rate and pay amounts.
L/R This field is used to select the justification for the text. Selecting "L" will cause the text to be left justified; selecting "R" will cause it to be right justified.
Fill This field is used to enter a character that will be used to fill in any extra space in a field. For example if you have a number field (rate for example) and set the length to a value of 10 and fill to 0 (the zero character) you will get the following output for the number 100.25; 0000100.25 The blank spaces in the field are filled with zeros. This is especially useful when creating fixed length export files.
Order The order arrows are used to move the field up (or down) in the list of fields. This will allow you to change the left to right order of the columns without the need to delete and readd them.
Remove The remove button is used to remove the field from the report permanently.
Format Pressing the format button will bring up an additional formatting menu for the field. This menu will allow you to perform special formatting on the field. The following table describes the special formatting codes.
Formatting Option Show 0
Total
Description This setting controls how number fields are displayed when the value to be shown is a 0. If this box is checked, zeros are displayed; if it is unchecked a blank is displayed in place of the zero. This setting is used to specify which number fields are included in sub totals and in report totals. If the box is checked, the field will be displayed on the total
payentry.com™ Report Writer User Guide MPAY Software 2006
11
No Punct
No Periods No Commas OverPunch
UpperCase
lines. This setting is used to strip all punctuation from the field before it is displayed. This is especially helpful for formatting items like phone and social security numbers when creating output files. This setting is used to strip decimal points from fields. This setting is used to strip commas from fields. This setting is used to convert number fields to an overpunch format. The overpunch format replaces the last digit in a field with a character that is based on the value of the last digit and on the sign (positive or negative) of the number. This setting will convert the field to uppercase if checked.
Sorts The sorts tab is used to create groups with sub totals as well as to control the sort order of data within a group.
Field The field column is used to enter the field code that you want to group or sort on. You can use the field selection tool on the left hand side of the screen to select your fields. The grouping and sorting is performed based on the first field entered, then the second, and so on for all fields that you specify. For example, in the report shown above, the data will be grouped by Department and then sorted by employee id within each department.
Description This field is used to enter the text description for the field selected. This will automatically be populated when a field is selected using the field selection tool.
Sub Total This setting is used to specify when a particular group should have a sub total displayed after the group. For example, in the report above a sub payentry.com™ Report Writer User Guide MPAY Software 2006
12
total will be displayed after each department (Sub Total is checked). A sub total will not be displayed after each employee (Sub Total is not checked).
Order These arrows are used to move the field up (or down) in the list of sorting fields.
Remove This button is used to remove the field from the list of sorts.
Filters You will typically see one or more filter tabs located to the right of the sorts on the report designer screen. These tabs are used to filter (limit) the data that is displayed on the report. The system will let you create a filter for each major type of data displayed on the report. For example, if you are doing an employee listing you will be allowed to create a company filter to filter on company demographic data and an employee filter to filter on employee demographic information. To add a filter to your report, you simply select the appropriate filter tab (based on the data you want to filter) and enter your filter conditions.
Field The field column is used to enter the field code that you want to filter on. You can use the field selection tool on the left hand side of the screen to select your fields.
Comparison / Value The system performs filtering by comparing the field you select to a value you provide. For example you can filter for all employees where the department (ee.cc1) is equal to (= Equals) department 500 (500). in that example, the field would be ee.cc1, the Comparison would be = Equals, and the value would be 500. The system allows for several different payentry.com™ Report Writer User Guide MPAY Software 2006
13
types of comparisons such as greater than (>), less than (
