PA - White Ware, Inc
Contents
1. JOSE 587 SRS lt Q oo lg rio B zU E 0 25 era t n A3 z Eaming Code A B D E F 1 Time Ticket History 2 3 JEaming Code H 4 5 Data 6 Employee ID Transaction Date Hours Worked Hourly Rate Trans Amt 7 JONDO1 1 15 98 8 000 7 5000 60 00 8 1 22 98 40 000 7 5000 300 00 g 1 29 98 40 000 7 5000 300 00 10 2 5 98 40 000 7 5000 300 00 2 12 98 40 000 7 5000 300 00 12 2 19 98 40 000 7 5000 300 00 13 2 26 98 40 000 7 5000 300 00 14 3 4 98 40 000 7 5000 300 00 3 11 98 40 000 7 5000 300 00 16 3 18 98 40 000 7 5000 300 00 17 3 25 98 40 000 7 5000 300 00 18 4 8 98 48 000 7 5000 360 00 19 ROSO01 1 8 98 32 000 5 7500 184 00 20 1 15 98 40 000 5 7500 230 00 21 1 22 98 40 000 5 7500 230 00 22 1 28 98 40 000 5 7500 230 00 Id 4 WI Sheett 151 Ready 3 44 Payroll Report Applet User s Manual A accessing data Microsoft Excel 1 13 D data files in Payroll 1 5 Data Source creating a new source 1 15 Fast Connect 1 16 No Shadow Dictionary Consistency Check 1 16 selecting a data source 1 16 Direct Deposit installing the applet with 2 1 F Fast Connect Microsoft Query 1 16 installing the applet 2 1 M Microsoft Excel 1 13 PivotTable layout 1 252. ID 4 _5 Data Tota B January Hours 0 000 7 February Hours 0 000 B March Hours 0 000 9 April Hours 0 000 10 Hours 0 000 11 June Hours 0 000 12 July Hours 0 000 13 August Hours 0 000 14 September Hours 0 000 15 October Hours 2347 820 15 November Hours 0 000 17 December Hours 0 000 18 YTD Hours 2347 820 19 Hours Worked Weeks Worked dal nm Ready Payroll Report Applet User s Manual Payroll PivotTables Payroll PivotTables PA Employee History PA Employee History Weeks Worked Sheet Active Fields Default Field Type Field Page Employee ID Row None Column Total Weeks Worked PA Employee History Weeks Worked PivotTable Sample X Microsoft Excel Paemhst File Edit view Insert Format Tools Data Window Help la x x c lt gt 100 4 anal 10 B2 U EFS BES 3 Edi 9 Bivotrable ig Lio EIN i ii isi A3 E EMPLOYEE ID A B C D 1 Payroll Employee History 2 3 ID 4 5 Data Total January Weeks Worked 7 February Weeks Worked March Weeks Worked 9 April Weeks Worked 10 May Weeks Worked 11 June Weeks Worked 12 July Weeks Worked 13 August Weeks Worked 14 September Weeks Worked 15 Oc
3. LS K LI Construct your PivotTable by dragging the field buttons on the right to the diagram on the left 124 ORDERED ORDERN rreren 5 Backorg NVOICE ENTRY NI dd INVOICE INIT CO LSTOME m gt The selected fields and four areas Page Row Column and Data to put fields are displayed Drag and drop the fields to use in this report into the respective areas To display the full field name hold the cursor on the button and a tool tip displays the full field name Drag and drop the following fields TRANSACTION TYPE into Page INVOICE NUMBER INVOICE DATE and ENTRY NUM into Row BATCH ID into Column ORDERED QTY SHIPPED QTY SELL UNIT COST COMPNT and UNIT PRICE into Data Payroll Report Applet User s Manual 1 25 Creating Microsoft Excel PivotTables Introduction 1 26 11 The fields are displayed on the screen Numeric fields dropped into the Data section become summary fields PivotT able Wizard Step 3 of 4 L2 1x K Construct your PivotTable by dragging the field buttons on the right to the diagram on the left ORDERED TRANSAG BATCH COLUMN Star StS wore d cif Sum of SHIPPED Q ENTRY NE Am of UNIT COST voice
4. ENTRY_N E E invorce UNIT_co custome UNIT_PR1 1 Finish Click Next The PivotTable Wizard Step 4 dialog box appears PivotT able Wizard Step 4 of 4 HE Where do you want to put the PivotTable C New worksheet Existing worksheet gt Click Finish to create your PivotTable Cancel Options lt Back Next gt 12 last step lets you create the PivotTable either in the existing worksheet or in a different worksheet Accept the given options and click Finish Payroll Report Applet User s Manual Introduction Creating Microsoft Excel PivotTables The PivotTable is displayed File Edit View Insert Format Tools Data Window Hi DG M SRY 3 m e oc PivatTable e s ala INVOICE INVOICE ENTRY N Data Grand Total 24889101 12 21 95 Sum of OR 4 Sum of SH _ 4 Sum of UNIT_ COMP 343 55 Sum of UNIT 475 686 475 686 Sum of OR TY 1 1 Sum of SH Y SE 1 1 Sum of UNIT_ COMP 907 53 907 53 Sum of UNIT 1317 384 1317 384 Sum of OR T 7 7 Sum of SH T 7 7 Sum of UNIT COST T 22 01 22 01 Sum of UNIT 20 7995 20 7995 Sum of OR 5 Sum of SH 5 Sum of UNIT 226 99 Sum of UNIT 526 131 Sum of OR 2 Sum of SH 2 114 Sheet1 Shest2 A Sheet3 4 Highlight rows and columns to shift them around To display only invoices change Transaction T
5. Field Department ID Deduction Code Description Period Quarter and Year to Date Deduction Amounts PA Department Analysis Deductions PivotTable Sample X Microsoft Excel padepts of x File Edit view Insert Format Tools Data Window Help 81 x JOSE GRY OB 100 8 710 B c i Bivotrable T EIN Y Dept ID B E 1 Payroll Department Analysis ig 2 3 Dept ID 100 gt EN 5 Data Deduction Code Desc PTD Deduction QTD Deduction YTD Deduction 7 012 401KMATCH 15 00 150 00 150 00 8 016 Employer Stock 1450 50 1800 00 1800 00 9 Grand Total 1455 50 1950 00 1950 00 10 11 s 14419 M Deductions Eamings Withholdings Jal 2 P Ready 3 18 Payroll Report Applet User s Manual Payroll PivotTables PA Department Analysis PA Department Analysis Earnings Sheet Active Fields Default Field Type Field Page Department ID Row Earnings Code Description Column Period Quarter and Year to Date Earnings Amounts PA Department Analysis Earnings PivotTable Sample XX Microsoft Excel padepts x File Edit View Insert Format Tools Data Window Help 81 x 587 z 52 z
6. 1 6 PA Employee Deduction History PivotTable description 3 25 files used in 1 6 sample report 3 26 screen 3 26 PA Employee Earnings History Earnings PivotTable sample report 3 29 screen 3 29 Hours PivotTable sample report 3 28 screen 3 28 PA Employee Earnings History PivotTable description 3 27 files used in 1 6 IX 2 PA Employee History Hours Worked PivotTable sample report 2 29 screen 3 22 Weeks Worked PivotTable sample report 3 23 screen 3 23 PA Employee History PivotTable description 3 21 files used in 1 5 PA Employee Withholding History Earnings PivotTable sample report 3 32 screen 3 32 Withholding PivotTable sample report 3 33 screen 3 33 PA Employee Withholding History PivotTable description 3 31 files used in 1 6 PA Leave History PivotTable description 3 35 sample report 3 36 screen 3 36 PA Miscellaneous Deduction History PivotTable description 3 41 sample report 3 42 screen 3 42 PA Recurring Deductions PivotTable description 3 37 files used in 1 8 sample report 3 38 screen 3 38 PA Recurring Time Tickets PivotTable description 3 39 files used in 1 8 sample report 3 40 screen 3 40 PA Time Ticket History PivotTable description 3 43 sample report 3 44 screen 3 44 PA Time Tickets PivotTable files used in 1 8 Payroll Report Applet User s Manual Index PACDxxx file description 1 7 PACExxx file description 1 8 PACHECKS XLS data files 1 7 descript
7. PivotTable adding a calculated field 1 27 changing column data 1 34 changing data sort 1 35 changing field properties 1 30 changing selection fields 1 32 definiion 1 11 moving fields 1 32 PivotTable Wizard Choosing a data source 1 22 getting the data 1 22 laying out the table 1 25 returning the data 1 26 PivotTables PA Check Deductions 3 5 PA Check Earnings 3 7 PA Check History 3 9 PA Check Withholdings 3 15 PA Checks 3 3 PA Department Analysis 3 17 PA Employee Deduction History 3 25 PA Employee Earnings History 3 2 7 PA Employee History 3 21 PA Employee Withholding History 3 31 PA Leave History 3 35 PA Miscellaneous Deduction History 3 41 PA Recurring Deductions 3 37 PA Recurring Time Tickets 3 39 PA Time Ticket History 3 43 R Report Applet requirements for 1 13 report applet installation 2 1 report applets definition 1 3 S spreadsheets PA Check Deductions 3 5 PA Check Earnings 3 7 PA Check History 3 9 PA Check Withholdings 3 15 PA Checks 3 3 IX 4 PA Department Analysis 9 17 PA Employee Deduction History 3 25 PA Employee Earnings History 3 2 7 PA Employee History 3 21 PA Employee Withholding History 3 31 PA Leave History 3 35 PA Miscellaneous Deduction History 3 41 PA Recurring Deductions 9 97 PA Recurring Time Tickets 3 39 PA Time Ticket History 3 43 system requirements 2 1 Payroll Report Applet User s Manual
8. 12 21 99 Sum of SHIPPED QTY SELL 18 19 32 12 21 99 Sum of UNIT_COST_COMPNT 1661 22 1661 22 33 12 21 99 Sum of UNIT PRICE 2721 6455 2721 5455 34 12 21 99 Sum of Profit Dollars 20148 0845 20148 0845 35 24889101 Sum of ORDERED QTY 18 19 36 24889101 Sum of SHIPPED QTY SELL 18 19 37 24889101 Sum of UNIT_COST_COMPNT 1661 22 1661 22 38 24889101 Sum of UNIT PRICE 2721 6455 2721 5455 I4 4 PIN Sheet1 Sheet Sheet 1 Ready Payroll Report Applet User s Manual NUM 1 29 Creating Microsoft Excel PivotTables Introduction Changing Field Properties You can also change the properties of the fields in the table For example to remove the subtotals from the INVOICE DATE field 1 Place your mouse cursor on the INVOICE DATE column heading right click and select Field from the menu The PivotTable Field dialog box appears PivotT able Field 2 x Name rientation Cancel Row C Column Page Subtotals Delete Tauber Automatic C Custom C None Hide items 12 21 99 Show items with no data You can use the PivotTable Field dialog box to change the field name its orientation on the PivotTable its display mask subtotalling options and so on 2 To shut of the subtotals select None under Subtotals and click OK 1 30 Payroll Report Applet User s Manual Introduction Creating Microsoft Exce
9. 526 131 299 141 24889104 Total 16 16 1591 87 2994 678 22444 928 4 24889105 12 21 99 001 1 1 855 61 1485 495 529 885 002 4 4 161 14 381 645 882 02 003 4 4 175 47 417 96 965 96 27 24889105 Total 9 9 1193 22 2285 1 9826 92 28 24889106 12 21 99 D01 5 5 145 98 342 144 980 82 29 24889106 Total 5 5 145 98 342 144 980 82 30 Grand Total 59 59 5979 2882 12473 1185 37907 4 2907 31 a gt MN Sheet A Sheett A Sheet A Sheet J 14 Ready esf 7 1 34 Payroll Report Applet User s Manual Introduction Creating Microsoft Excel PivotTables Changing the Data Sort To change the order in which the data is displayed you can simply change the Row fields around For example our PivotTable is sorted by Invoice Number To sort it by Invoice Date instead click and drag the INVOICE DATE field to the left of the INVOICE NUMBER field The data is sorted by Invoice Date and is redisplayed File Edit View Insert Format Tools Data Window Help 18 JOS er gt o 6 so 44 o A Bivotrable JE B5 INVOICE NUMBER A B G H F 1 BATCH ID All gt TRANSACTION Data INVOICE DATE INVOICE NUMBIENTRY NUMBER of ORDERED Sum of SHIPPED SELL Sum of UNIT COST COMPNT Sum of UNIT PRICE Sum of Profit Dolla
10. Date but you can easily change the sort order or include the Employee ID or Sick Vacation Flag in the sort Active Fields Default Field Type Page Row Column Payroll Report Applet User s Manual Field Employee ID Sick Vacation Flag Earnings Code Description Check Number Adjustment Date Adjustment Amount 3 35 PA Leave History Payroll PivotTables PA Leave History PivotTable Sample File Edit View Insert Format Tools Data Window Help 1 K 3 locu say 10 Aris so Bzu 0 00 Pvotrabe j Ea 9 2 t fie i A4 T Employee ID E A B E 6 1 Payroll Leave History n 2 Sick Vacation Flag S Sick 4 ID All v Vacation 5 Sick Vacation Flag gt 7 Adjustment Amt 8 Eaming Code Desc Check No Adj Date Total Vacation Adjustment 6 26 98 20 00 ES 10 Vacation Time Earned 6 26 98 30 00 11 SIC Sick Adjustment 26 98 25 00 12 Sick Time Earned 6 26 98 15 00 13 Grand Total gt DIA Sheett 0 00 Ready 3 36 NUM Payroll Report Applet User s Manual PA Recurring Deductions File Name PAREDED XLS Description The PA Recurring Deductions PivotTable uses the data in the Recur
11. Employee Earnings History Payroll PivotTables PA Employee Earnings History Hours PivotTable Active Fields Default Field Type Field Page Employee ID Earnings Code Row None Column Total Hours PA Employee Earnings History Hours PivotTable Sample XX Microsoft Excel oj xi js File Edit view Insert Format Tools Data Window Help 81 x JOSE S6RY gt 21 MOB 6 Aria u0 BIlU FES SHS PivotTable i e dp PE t zi lis A3 Employee ID A C D ESI 1 Payroll Employee Earnings History 3 JEmployee ID Al 4 Earning Code y Ei B Data Total 7 January Hrs 0 00 Hrs 0 00 9 March Hrs 0 00 10 April Hrs 0 00 11 Hrs 0 00 12 June Hrs 0 00 13 July Hrs 0 00 14 August Hrs 0 00 15 September Hrs 0 00 16 October Hrs 1614 00 17 November Hrs 0 00 18 December Hrs 0 00 18 Total Hrs 1614 00 4 4 V WIN Hours Esrnings Ready 3 28 Payroll Report Applet User s Manual Payroll PivotTables PA Employee Earnings History PA Employee Earnings History Earnings PivotTable Active Fields Default Field Type Page Row Column Field Employee ID Earnings Code None Total Earnings PA Employee Earnings History Earnings PivotTable Sample File Edit vi
12. ID and Voided Flag in the sort Active Fields Default Field Type Page Row Column Payroll Report Applet User s Manual Field Employee ID Voided Flag Deduction Code Description Transaction Date Hours Worked Amount 8 41 PA Miscellaneous Deduction History Payroll PivotTables PA Miscellaneous Deduction History PivotTable Sample XX Microsoft Excel Pathded oj xi me File Edit View Insert Format Tools Data Window Help 81 x 3 E SAY iama D a gt 10 JL res E k 004 010 Grand Total 3 42 Payroll Report Applet User s Manual File Name PATHEARN XLS Description PA Time Ticket History The PA Time Ticket History PivotTable uses the data in the PA Transaction History file to display detailed information about the time tickets you have entered The report is sorted by Employee ID and Transaction Date but you can easily change the sort order or add the Earnings Code to the sort Active Fields Default Field Type Page Row Column Payroll Report Applet User s Manual Field Earnings Code Employee ID Transaction Date Hours Worked Hourly Rate Transaction Amount 3 43 PA Time Ticket History Payroll PivotTables PA Time Ticket History PivotTable Sample File Edit View Insert Format Tools Data Window Help 81 x
13. PivotTable sample 1 2 7 PivotTable wizard 1 21 selecting a data source 1 22 Microsoft Query 1 13 adding tables 1 19 building a query 1 14 Filtering data 1 17 joining tables 1 20 Selecting a table 1 17 Payroll Report Applet User s Manual Index Sorting data 1 18 N No Shadow Dictionary Consistency Check Microsoft Query 1 16 O ODBC Kit definition 1 3 OSAS 1 3 P PA Check Deductions PivotTable description 3 5 files used in 1 6 1 7 sample report 3 6 screen 3 6 PA Check Earnings PivotTable description 8 7 files used in 1 7 1 8 sample report 3 8 screen 3 8 PA Check History Deductions PivotTable sample report 3 11 screen 3 11 Earnings PivotTable sample report 3 12 screen 3 12 Summary PivotTable sample report 3 10 screen 3 10 Withholdings PivotTable sample report 3 13 screen 3 13 IX 1 Index PA Check History PivotTable description 3 9 files used in 1 5 1 6 1 7 1 8 1 9 PA Check Withholdings PivotTable description 3 15 files used in 1 7 1 8 sample report 3 16 screen 3 16 PA Checks PivotTable description 3 3 files used in 1 7 sample report 3 4 screen 9 4 PA Deductions PivotTable files used 1 8 PA Department Analysis Deductions PivotTable sample report 3 18 screen 3 18 Earnings PivotTable sample report 3 19 screen 3 19 Withholdings PivotTable sample report 3 20 screen 3 20 PA Department Analysis PivotTable description 3 17 files used in
14. data dictionaries in a file called CONFIG TPM You can build this file using the ODBC Kit functions You can store this file in any directory but the report applets expect the file to be located in the C WINDOWS directory If your CONFIG TPM file is stored in a different directory you have three choices for using the PivotTables supplied with the report applet 1 Move the CONFIG TPM file to the C WINDOWS directory and change any Data Sources you have set up and any ODBC reports or spreadsheets you have already set up to use the CONFIG TPM in its new location 2 Copy the CONFIG TPM file to the C WINDOWS directory and leave a copy in its current location You do not need to change any Data Sources or reports you have set up but you need to make any changes in both files 3 Change the PivotTables provided with this report applet to use the CONFIG TPM file in its current location You can find instructions for doing this below If you choose methods 1 or 2 above you can load the PivotTables in Microsoft Excel and begin using them with your data by using the Refresh Data command in Excel If you choose option 3 follow the instructions below to point the PivotTable to the correct CONFIG TPM file 2 2 Payroll Report Applet User s Manual Installation Using a Different CONFIG TPM If you store your CONFIG TPM file in a location other than the C WINDOWS directory you will see this message when you attempt to refresh the data
15. table by dragging the fields or data block to the column heading area For example to show the quantity price cost and profit information in our table across the columns instead of in the data block as they now appear drag the Data field above the Total column heading and drop it there The change appears immediately X Microsoft Excel Book1 BEE 8 Ele Edt view Insert Format Tools Data Window Help l xj a 10 9 A 6 gt Data INVOICE NUMBERIINVOICE ENTRY NUMBER Sum of ORDERED QTY Sum of SHIPPED SELL Sum of UNIT COST Sum of UNIT PRICE Sum of Profit Dollars 24889101 12 21 99 001 4 4 343 55 475 686 528 544 002 1 1 907 53 1317 384 409 854 003 7 7 22 01 20 7895 8 4735 004 5 5 226 99 526 131 1495 705 005 2 2 161 14 381 645 441 01 24889101 Total 19 19 1661 22 2721 6455 20148 0845 24889102 12 21 99 001 3 3 840 83 1485 495 1933 995 002 3 3 152 381 645 688 935 E 003 4 4 171 55 417 96 985 64 24889102 Total 10 10 1154 38 2285 1 11207 2 16124889103 12 21 99 001 3 3 348 0582 475 686 382 8834 002 2 2 874 56 1317 384 885 648 003 5 5 0 51 381 256 905 24889103 Total 10 10 1222 6182 1844 451 6218 328 24889104 12 21 99 001 10 10 1342 87 2417 166 10742 96 002 5 5 22 01 51 381 146 855 003 1 1 226 99
16. the Sum of Profit Dollars field oy x 1 D SO Sm gt lt ca T gt ALL 4 15 6 aria rio B zu H 55 8 ml Oa Ar PivotTable 7 jm ci gt A B D E E Giaj 1 TRANSACTION TYT AII x 2 g BATCH ID 4 INVOICE NUMBER INVOICE DATE ENTRY NUMBER Data 000002 Grand Total 5 24889101 12 21 98 001 Sum of ORDERED QTY 4 4 Sum of SHIPPED QTY SELL 4 4 fi Sum of UNIT_COST_COMPNT 343 55 343 55 8 Sum of UNIT PRICE 475 586 475 586 g Sum of Profit Dollars 528 544 528 544 10 002 Sum of ORDERED QTY 1 1 11 Sum of SHIPPED QTY SELL 1 1 12 Sum of UNIT COST COMPNT 907 53 907 53 13 Sum of UNIT PRICE 1317 384 1317 384 14 Sum of Profit Dollars 409 854 409 854 15 003 Sum of ORDERED QTY 7 7 16 Sum of SHIPPED QTY SELL 7 7 17 Sum of UNIT COST COMPNT 22 01 22 01 18 Sum of UNIT PRICE 20 7995 20 7995 19 Sum of Profit Dollars 8 4735 8 4735 20 004 Sum of ORDERED QTY 5 5 21 Sum of SHIPPED QTY SELL 5 5 29 Sum of UNIT COST COMPNT 226 99 226 99 23 Sum of UNIT PRICE 526 131 526 131 24 Sum of Profit Dollars 1495 705 1495 705 25 005 Sum of ORDERED QTY 2 2 26 Sum of SHIPPED QTY SELL 2 2 27 Sum of UNIT COST COMPNT 161 14 161 14 28 Sum of UNIT_PRICE 381 645 381 645 EJ Sum of Profit Dollars 441 01 441 01 30 12 21 29 Sum of ORDERED 18 19 al
17. this PivotTable to review the withholdings calculated in the current check run Active Fields Default Field Type Page Row Column Payroll Report Applet User s Manual Field Employer Paid flag Sequence Number Employee ID Tax Authority Withholding Code Description Earnings Withholding Amount 3 15 PA Check Withholdings Payroll PivotTables PA Check Withholdings PivotTable Sample XX Microsoft Excel Pachkwth iO x File Edit view Insert Format Tools Data Window Help la De a P Q amp A 21 ibd 0 aria gt 10 B zU 5 8 1 AA PivotTable e 7 95 1 E im A3 gt Employer Paid A B D E E G Hay 1 Payroll Check Withholdings n 2 3 Emnlover Paid Al E 4 B Data Sequence No Employee ID Authority With Code Desc Earnings Amount 7 000001 BOUDD1 FED EME Emplyr Medicare 7500 00 108 75 8 EOA Emplyr OASDI 7500 00 465 00 9 FUT Unemp Ins 7062 50 0 00 10 FWH Federal WH 7062 50 1215 54 11 MED Emplye Medicare 7500 00 108 75 12 OAS Emplye OASDI 7500 00 465 00 18 MN SUI MN Unemp Ins 7062 50 93310 14 SWH MN WH 7062 50 436 60 15 000002 GER001 FED EM
18. up of these related spreadsheets Summary Deductions Earnings and Withholdings You can change between these sheets by selecting the appropriate tab at the bottom of the current sheet The data is sorted by Sequence Number but you can easily change the sort order You can use this PivotTable to review any check that you have not purged from the history files Payroll Report Applet User s Manual 3 9 PA Check History Payroll PivotTables PA Check History Summary Sheet Active Fields Default Field Type Field Page Check Type Employee ID Row Sequence Number Check Number Check Date Column Gross Pay Net Check Amount PA Check History Summary PivotTable Sample 188 Edit view Insert Format Tools Data Window Help 281 x amp amp ee rfi MOB om arial 10 a s zu 593 E e owe PivotTable gt i E Cu 4 mj Check Type 0 Manual CHECK TYPE All 1 Calculated EMPLOYEE ID SEQUENCE NUMBER CHECK_NUMBER CHECK DATE Gross Pay Net Check Amt 17 000009 2 28 94 4033 88 14 419 M Summary Deductions Earnings Withholdings dal Ready NUM 3 10 Payroll Report Applet User s Manual Payroll PivotTables PA Check History PA Check History Deductions Sheet Active Fields Default Field Type Field Page Empl
19. 8 1 m X Employer Paid A B D E E 1 Payroll Check Deductions n 2 3 JEmplover Paid H 4 5 Deduction Amt B Seq No Deduction Code Desc otal 7 000001 001 Medical Ins 10 56 8 002 Dental Ins 3 52 g 003 United Way 75 00 10 004 Credit Union 50 00 11 006 401K 337 50 12 010 Stock Plan 100 00 13 000001 Total 575 58 14 000002 001 Medical Ins 10 56 15 002 Dental Ins 3 53 15 003 United Way 25 00 17 006 401K 150 00 18 000002 Total 189 09 19 000003 001 Medical Ins 10 56 20 002 Dental Ins 3 52 21 003 United Way 75 00 22 000003 Total 89 08 23 000004 001 Medical Ins 10 56 24 002 Dental Ins 3 52 EH 008 Parkin 5 00 26 000004 Total 19 08 27 Grand Total 873 83 ca gt b Sheet Ready 3 6 Payroll Report Applet User s Manual File Name PACHKERN XLS Description PA Check Earnings The PA Check Earnings PivotTable uses the data in the Checks Earnings PACEx file to display detailed earnings information from each employee paycheck The report is sorted by Sequence Number Earnings Code State and Department ID but you can easily change the sort order or include the Employee ID in the sort You can use this PivotTable to review the earnings calculated in the current check run Active Fields Default Field Type Page Row Column Payroll Report Applet User s Manual Field Employee ID Sequence Number Earnings Code State Department ID Hourly Rate Hours Worked Amount 3 7 PA C
20. E Emplyr Medicare 5550 00 80 48 15 EOA Emplyr OASDI 5550 00 344 10 17 FUT Unemp Ins 5400 00 0 00 18 FWH Federal WH 5400 00 477 23 18 MED Emplye Medicare 5550 00 80 48 20 OAS Emplye OASDI 5550 00 344 10 21 MN SUI MN Unemp Ins 5400 00 933 10 22 SWH MN 5400 00 319 93 23 000003 JENOO1 FED EME Emplyr Medicare 75 0 10875 24 Emplyr OASDI 7500 00 465 00 pu FUT Unemp Ins 7500 00 0 00 26 FWH Federal WH 7500 00 1842 63 27 MED Emplye Medicare 75 0 108 75 28 OAS Emplye OASDI 7500 00 465 00 29 MN SUI MN Unemp Ins 7500 00 933 10 30 SWH MN W H 7500 00 564 37 31 000004 LUKDD1 FED EME Emplyr Medicare 25 35 25 32 Emplyr OASDI 25 0 155 00 I4 4 gt HW Sheett dal 1 Ready 3 16 Payroll Report Applet User s Manual PA Department Analysis File Name PADEPTS XLS Description The PA Department Analysis PivotTable uses the data in the Departments PADPx file to display historical totals of earnings deductions and withholdings by department The PA Department Analysis PivotTable consists of these related spreadsheets Deductions Earnings and Withholdings You can change between these sheets by selecting the appropriate tab at the bottom of the current sheet The data is sorted by Code but you can easily change the sort order Payroll Report Applet User s Manual 3 17 PA Department Analysis Payroll PivotTables PA Department Analysis Deductions Sheet Active Fields Default Field Type Page Row Column
21. Emplyr Medicare 13 37 0 00 0 00 8 EOA Emplyr OASDI 57 15 0 00 0 00 z FUT Unemp Ins 57 15 0 00 0 00 10 MN SUI MN Unemp Ins 37 80 0 00 0 00 11 Grand Total 165 47 0 00 0 00 144 MA Deductions A Earnings Vithholdings Ready 3 20 Payroll Report Applet User s Manual PA Employee History File Name PAEMHST XLS Description The PA Employee History PivotTable uses the data in the Employee Miscellaneous History file to display historical totals of hours and weeks worked for the employees you choose The PA Department Analysis PivotTable consists of these related spreadsheets Hours Worked and Weeks Worked You can change between these sheets by selecting the appropriate tab at the bottom of the current sheet Payroll Report Applet User s Manual 3 21 PA Employee History PA Employee History Hours Worked Sheet 3 22 Active Fields Default Field Type Field Page Employee ID Row None Column Total Hours Worked PA Employee History Hours Worked PivotTable Sample XX Microsoft Excel Paemhst xi File Edit view Insert Format Tools Data Window Help 81 x ID Z R 587 K Cu gt i g 4 ime 8 68 x BR Bivotrable ig 5 A3 gt Employee ID A B C D zl 1 Payroll Employee History 2 3
22. ID Employer Paid flag Row Tax Authority Withholding Code Column January December Withholding Amounts Year to Date Withholding Amount Employee Withholding History Withholding PivotTable Sample XX Microsoft Excel Paempwth DI x js File Edit view Insert Format Tools Data Window Help laj x Ima 587 gt 32 8 B z U u 5 A Bivotrable T e NE 1 iz ri A3 bd Employee ID A B E M N FI 1 Payroll Employee Withholding History 2 3 JEmplovee ID All x 4 Employer Paid All E 8 _7 Tax Authorit Withholding Code October Amt November Amt December Amt YTD Amount 1539 58 0 00 0 00 1539 58 gu EOA 6583 01 0 00 0 00 6583 01 10 FUT 2547 81 0 00 0 00 2547 81 b FWH 17041 60 0 00 0 00 17041 60 12 MED 1539 58 0 00 0 00 1539 58 e OAS 6583 01 0 00 0 00 6583 01 14 MN SUI 2324 45 0 00 0 00 2324 45 18 SWH 6994 37 0 00 0 00 6994 37 16 Grand Total 45153 41 0 00 0 00 45153 41 14419 kN Earnings Withholding Ready Payroll Report Applet User s Manual 3 33 File Name PALVHST XLS Description PA Leave History The PA Leave History PivotTable uses the data in the Leave Adjustment History PAHV x file to display adjusted and earned time for paid sick and vacation leave The report is sorted by Earnings Code Description Check Number and Adjustment
23. OPEN SYSTEMS Accounting Software Payroll ODBC Report Applet User s Manual PN 2210 PAO60 1998 Open Systems Holdings Corp All rights reserved Document Number 2210 PAO600 No part of this manual may be reproduced by any means without the written permission of Open Systems Holdings Corp OPEN SYSTEMS is a registered trademark and OSAS Resource Manager Resource Manager for Windows and Report Writer are trademarks of Open Systems Holdings Corp BB is a trademark and PRO 5 and Visual PRO S are registered trademarks of BASIS International Ltd Novell NetWare and UNIXWare are registered trademarks of Novell Inc Microsoft Microsoft Access Microsoft Windows Microsoft Windows 95 Windows MS DOS and PivotTable are either trademarks or registered trademarks of Microsoft Corporation Crystal Reports for Windows is a trademark of Seagate Software Inc TrueType is a registered trademark of Apple Computer Inc Printed in U S A August 1998 Release 6 0 This document has been prepared to conform to the current release version of OPEN SYSTEMS Accounting Software Because of our extensive development efforts and our desire to further improve and enhance the software inconsistencies may exist between the software and the documentation in some instances Call your customer support representative if you encounter an inconsistency Contents Introduction General Information Payroll Data Files Introduction to PivotTables Creating Microsof
24. OT 6759 67 0 3893 03 21000002 00000004 3 24889102 19961221 105001 727327 0 3664 63 121000002 00000005 3 24889103 19981221 SUN001 431874 0 279329 121000002 00000006 3 24889104 1995 12 21 CASHCA 249547 1497 28 13765 74 000002 00000007 3 24888105 19951221 CASHMN 4683 92 0 2206 05 00002 00000008 3 24888106 19951221 CASHPS 171072 0 7283 121000002 00000003 4 24889107 19951221 ACEOD 97336 0 1030 65 21000002 00000010 4 24888108 19961221 105001 48214 65 0 27910 M s RecoaTi FP Select View Criteria to show edit criteria limiting records shown NUM Payroll Report Applet User s Manual Introduction Creating Microsoft Excel PivotTables The data in your query is displayed You can delete columns by selecting a column and pressing the Delete key You can also add a column by double clicking on the field name in the SOTH file NOTE You can also select which fields you want in your query in step 6 above Instead of selecting the entire table you can click the box next to the table you want and select the given fields from the list 14 Select the following fields BATCH ID ORDER NUMBER e TRANSACTION TYPE INVOICE NUMBER INVOICE DATE e CUSTOMER ID SUBTOTAL SALES TAX TOTAL COST 15 Select Table from the main menu and choose Add tables The Add Table dialog box appears Add Tables 2 Close SOTH zi Options Dictionary 71 Pay
25. age Employee ID Department ID Row Sequence Number Check Number Earnings Code Description Column Hours Worked Hourly Rate Earnings Amount PA Check History Earnings PivotTable Sample XX Microsoft Excel Pachkhst iO x js File Edit view Insert Format Tools Data Window Help 8 ow rii iiM 9 150 8 aria 0 0 Bivotrable 4 T SEER mat jm A3 gt EMPLOYEE ID A B D E F G FI 1 Payroll Check History 2 3 JEMPLOYEE ID All 4 DEPARTMENT ID E Data 7 SEQUENCE NUMBER CHECK NUMBER EARN CODE DESCRIPTION Hours Worked Hrly Rate Earn Amount 8 1000004 0001029 Regular Pay 128 960 9 000006 10001030 REG Regular Pay 152 8 4 10 000007 0001024 REG Regular Pay 150 0 1342 5 11 000011 10001037 Regular Pay 150 1200 12 000013 0001038 REG Regular Pa 160 920 13 000014 0001032 REG Regular Pa 160 1432 14 1000018 0001045 REG Regular Pa 160 1200 141419 M Summary Deductions Earnings Vithholdings Ia Ready 3 12 Payroll Report Applet User s Manual Payroll PivotTables PA Check History PA Check History Withholdings Sheet Active Fields Default Field Type Field Page Employee ID Employer Paid flag Row Sequence Number Check Number Tax Authority Withholding Code Description Column E
26. ancel pivot Browse didi Options By Use the Query Wizard to create edit queries The Choose Columns box under Query appears Query Wizard Choose Columns What columns of data do want to include in your query Available tables and columns Columns in your query BATCH ID ORDER NUMBER TRANSACTION TYPE INVOICE NUMBER 8 ARCC Ea INVOICE DATE 8 ARCD 1 CUSTOMER ID 8 ARCD 2 SUBTOTAL m 3 mi SALES_TAX _ m Preview Mow Beck Cancel Payroll Report Applet User s Manual 1 23 Creating Microsoft Excel PivotTables Introduction 6 Click Next The query columns are displayed 7 Click Next gt to pass by Filter Data and Sort Order options The Query Wizard Finish dialog box appears Query Wizard Finish Es What would you like to do next Save Query View data or edit query in Microsoft Query lt Back Cancel 8 Select Return Data to Microsoft Excel and click Finish You are returned to the PivotTable Wizard Step 2 dialog box PivotT able Wizard Step 2 of 4 L2 Lx Where is your external data stored Get Data Data fields have been retrieved Cancel lt Back Finish 9 Click Next gt 1 24 Payroll Report Applet User s Manual Introduction 10 Creating Microsoft Excel PivotTables The PivotTable Wizard Step 3 dialog box appears PivotT able Wizard Step 3 of 4 BEI
27. ar on the PivotTable as gray blocks with the field name on them To move any field simply drag it to a new destination You can change your PivotTable by moving fields in these ways Changing the Selection Fields If you want to be able to limit the data in the table you can make any field in the table part of the selection criteria by moving it to the Page area For example to select a specific batch for this table rather than displaying all the batches across the table columns as they are in our sample table follow these steps 1 Position the mouse cursor over the BATCH 1 field press and hold the left mouse button As you drag the BATCH ID field around the table the cursor changes to show where you can drop it If the cursor looks like a block with an X over it you will remove the field from the table by dropping it there 2 Drag the BATCH ID field to the left of the TRANSACTION TYPE field and drop it there 1 32 Payroll Report Applet User s Manual Introduction Creating Microsoft Excel PivotTables The change appears immediately XX Microsoft Excel Book1 53 File Edit view Insert Format Tools Data Window Help Biel E 2181 E SRY Gere Me ux CH v aria rio gt B zu 2 9 9 S 5 A PivotTable 1 iif iei E4 Total B c D E 1 BATCH ID Al
28. arnings Withholding Amount PA Check History Withholdings PivotTable Sample XX Microsoft Excel Pachkhst of x File Edit view Insert Format Tools Data Window Help 81 x Cu ID Z R 58 fe Bf e 100 Arial gt 10 man ale 7 3 33 d5 A PivotTable 109 2 i 9 E ID A B D E F G 1 Payroll Check History E 2 3 JEmplovee ID 4 Employer Paid 5 5 7 Sequence No Check No Authority With Code Desc 6 j000001 Federal WH D 00 1507 99 9 Emplye Medicare 0 00 119 63 10 Emplye OASDI D 00 511 50 11 MN MN W H 0 00 553 53 12 000002 0001025 FED Federal WH 0 00 477 23 13 Emplye Medicare E 14 OAS Emplye OASDI DDD 375 10 141419 M Summary Deductions A Earnings Withholdings lal Ready Payroll Report Applet User s Manual 3 13 File Name PACHKWTH XLS Description PA Check Withholdings The PA Check Withholdings PivotTable uses the data in the Checks Withholdings PACWx and Withholding Codes PAWIXx files to display detailed withholding information from each employee paycheck The report is sorted by Sequence Number Employee ID Tax Authority Withholding Code and Description but you can easily change the sort order or include the Employer Paid flag in the sort You can use
29. d 65 3 Arial lt u BLlU EFESB 2 2 A pivotable i Liz t is lis E A3 gt Department ID A B C D E p 1 Payroll Department Analysis 2 3 Department 500 i 4 B Data B Earning Code Desc Earnings PTD Earning QTD Earnings YTD a 7 Overtime Pay 0 00 0 00 0 00 Bonus 0 00 0 00 0 00 9 02 Travel Exp 0 00 0 00 0 00 10 P 3 Cash Value 0 00 0 00 0 00 11 P04 Commissions 0 00 0 00 0 00 ia 419 MN Deductions Earnings Withholdings eum Ready Payroll Report Applet User s Manual 3 19 PA Department Analysis Payroll PivotTables PA Department Analysis Withholdings Sheet Active Fields Default Field Type Field Page Department ID Row Tax Authority Withholding Code Description Column Period Quarter and Year to Date Withholding Amounts PA Department Analysis Withholdings PivotTable Sample XX Microsoft Excel padepts x 5 File Edit view Insert Format Tools Data Window Help la Da BH e l 2 Arial i B ZU A Ar PivotTable iz wl A3 Department ID B D E F 1 Payroll Department Analysis 2 3 Department ID 100 l E 5 Data B Tax Authorit Withholding Code Descr Withholding Withholding QTD Withholding YTD
30. ease of the ODBC Kit OSAS users have been discovering the power of these popular productivity packages to analyze their accounting data The Report Applets provide a series of pre built Microsoft Excel PivotTables to help you get the most from your accounting data These tables are provided for each of the major data files in each application This manual includes instructions for loading and using these spreadsheets to sort and analyze your data With a little practice you can easily create similar PivotTables or modify the ones provided to customize them to your exact needs Payroll Report Applet User s Manual 1 3 Payroll Data Files You use the Payroll system to automatically figure employee wages federal state and local withholdings and deductions The Payroll system also tracks bonus pay and sick and vacation time and accumulates information for tax reporting Finally use the Payroll system to produce paychecks reports and employee W 2 forms Payroll Data Files The Payroll Report Applet contains several spreadsheets that report information from the OSAS Payroll data files The PivotTables in the PA Report Applet are based on these data files PAEGxxx The Payroll Employee General Information file holds employee information such as employee ID name social security number address and phone number equal employment opportunity code vacation and sick accrual codes and earning code department group code labor class and
31. eck Withholdings PA Department Analysis PA Employee History PA Employee Deduction History PA Employee Earnings History PA Employee Withholding History PA Leave History PA Recurring Deductions PA Recurring Time Tickets PA Miscellaneous Deduction History PA Time Ticket History Payroll Report Applet User s Manual 3 3 3 5 3 7 3 9 3 15 3 17 3 21 3 25 3 27 3 31 3 35 3 37 3 39 1 41 1 43 File Name PACHECKS XLS Description PA Checks The PA Checks PivotTable uses the data in the Payroll Checks PACHx file to display information about the checks prepared in the current payday check cycle The data is sorted by Sequence Number and Employee ID but you can easily change the sort order You can use this PivotTable to review the checks on file before printing and posting them Active Fields Default Field Type Page Row Column Payroll Report Applet User s Manual Field None Sequence Number Employee ID Gross Pay Net Amount Hours Worked Total Pieces 3 3 PA Checks Payroll PivotTables PA Checks PivotTable Sample XX Microsoft Excel Pachecks DI x js File Edit view Insert Format Tools Data Window Help 81 x Dement X C aria gt 10 B 1 UE Bivotrable 75 e 1 i amp gt 4 Zi g 100 6 95 8 5 Hy A Ar Payroll Checks 1 2 8 4 Seque
32. ect PivotTable Report Payroll Report Applet User s Manual 1 21 Creating Microsoft Excel PivotTables Introduction 1 22 3 The PivotTable Wizard appears PivotT able Wizard Step 1 of 4 L2 x Where is the data that you want to analyze External data source Multiple consolidation ranges Another PivotTable Cancel lt Back L ves Finish In step 1 of the Wizard a list of options is displayed where you can choose your data source to be used in your PivotTable Select External Data Source and click Next gt The PivotTable Wizard Step 2 dialog box appears Where is your external data stored No data fields have been retrieved Cancel lt Back Next gt Finish 4 Instep 2 of the Wizard click Get Data Payroll Report Applet User s Manual Introduction Creating Microsoft Excel PivotTables The Choose Data Source box from Microsoft Query appears Choose Data Source L2 1x DK Databases Queries New Data Source dBASE Files not sharable Excel Files not sharable FoxPro Files not sharable MS Access 97 Database not sharable OSAS Data Text Files not sharable Cancel Browse Options By Use the Query Wizard to create edit queries 5 Click the Queries tab and select the query you saved under Microsoft Query Choose Data Source L2 x Databases Queries dunning C
33. ew Insert Format Tools Data Window Help 1 locu Sem gt Cu amp HIA S2 48 n5 A Arial 9 B zU EZE votrabe 9 A3 EMPLOYEE ID A B C Payroll Employee Earnings History 1 2 3 JEMPLOYEE ID All xi 4 EARNING CODE All gt Data 7 January Earnings February Earnings March Earnings 10 April Earnings 11 May Earnings 5 Total 12 Earnings 13 July Earnings 14 August Earnings 15 September Earnings 16 October Earnings 17 November Earnings 18 December Earnings 18 YTD Earnings 106677 65 106677 6 4 4 DIR HE Earnings Ready Payroll Report Applet User s Manual 3 29 PA Employee Withholding History File Name PAEMPWTH XLS Description The PA Employee Withholding History PivotTable uses the data in the Employee Withholding History PAEWx file to display monthly and year to date earnings and withholdings amounts for the employees you choose The PA Employee Withholding History PivotTable consists of these related spreadsheets Earnings and Withholding You can change between these sheets by selecting the appropriate tab at the bottom of the current sheet Payroll Report Applet User s Manual 3 31 PA Employee Withholding History Payroll PivotTables PA Employee Wi
34. he Microsoft Excel User s Guide or Online Help Before you can create this report complete these tasks e Install and set up the ODBC Kit Install and set up the BASIS ODBC drivers Install Microsoft Excel 97 and Microsoft Query 97 Note This section includes instructions for using Microsoft Query with Microsoft Excel If necessary you can install Microsoft Query from the Microsoft Office 97 media You may also need to create a shortcut to Query manually Payroll Report Applet User s Manual 1 13 Creating Microsoft Excel PivotTables Introduction Building a Query For a PivotTable 1 Start Microsoft Query 25 Microsoft Query Of x Eile Help oud sar 5 4 amp 3 t fn 215 emp 2 Under the File menu select New The Choose Data Source screen appears Databases Queries Locum IM M UIT M ETT md dBASE Files not sharable Excel Files not sharable FoxPro Files not sharable MS Access 97 Database not sharable OSAS Text Files not sharable jul Use the Query Wizard to create edit queries Payroll Report Applet User s Manual Introduction Creating Microsoft Excel PivotTables 3 Select New Data Source and click OK The Create New Data Source screen appears Create New Data Source x What name do you want to give your data source FE osas Data Select a driver for the type of database you
35. he list of tables starts at the beginning of the list The Choose Data Source box appears Choose Data Source i x Databases Queries Of lt New Data Source gt dBASE Files not sharable Excel Files not sharable FoxPro Files not sharable MS Access 97 Database not sharable OSAS Text Files not sharable Cancel Browse Options dd By Use the Query Wizard to create edit queries 10 Select the data source you set up in the previous steps 1 16 Payroll Report Applet User s Manual Introduction Creating Microsoft Excel PivotTables The Choose Columns screen appears Query Wizard Choose Columns x What columns of data do you want to include in your query Available tables and columns Columns in your query SORH BATCH ID SORL ORDER_NUMBER lt SDSL TRANSACTION El lt TERMS CODE SOTB Lx Group 8 SOTD Preview of data in selected column By Cancel 11 Selecta table you want to use in your Excel spreadsheet For this example start with one table and add a second table later Select the SOTH table select the columns for the spreadsheet and click Next gt The Filter Data box appears Query Wizard Filter Data 2 x Filter the data to specify which rows to include in your query If you don t want to filter the data click Next Column to filter Only incl
36. heck Earnings Payroll PivotTables PA Check Earnings PivotTable Sample XX Microsoft Excel Pachkern DI x File Edit View Insert Format Tools Data Window Help 81 x DG E er o rfi d 946 3 aria 10 BIUE EEE 9 Bivotrabie gt Ca gt A3 M Employee ID A B C D E F G H a 1 Payroll Check Earnings 2 3 JEmplovee ID MAN H 4 5 Data 6 Sequence No Earning Code State Dept ID Hourly Rate Hrs Worked Amt 7 000001 SAL MN 500 0 00 173 330 7500 00 8 000001 Total 0 00 173 330 7500 00 9 000002 SAL MN 500 0 00 173 330 5550 00 10 000002 Total 0 00 173 330 5550 00 11 000003 SAL MN 500 0 00 173 330 7500 00 12 000003 Total 0 00 173 330 7500 00 13 000004 SAL MN 500 0 00 173 330 2500 00 14 000004 Total 0 00 173 330 2500 00 15 Grand Total 0 00 593 320 23050 00 4141 HW Sheet1 Jal df Ready 3 8 Payroll Report Applet User s Manual PA Check History File Name PACHKHST XLS Description The PA Check History PivotTable uses the data in the Checks History Check Earnings History PAHEx Check Deductions History PAHDx and Check Withholdings History files to display detailed information about the paychecks you have disbursed in past payroll check cycles The Check History PivotTable is made
37. in any PivotTable included with this report applet Microsoft Excel e BASIS BASIS ODBC Driver Default configuration Failed Default CONFIG C datadict Config tpm error duplicate or missing file When you click on OK the BASIS ODBC Driver Data Source Setup dialog box appears BASIS ODBC Driver Data Source Setup Eg Enter Data Source Specification Data Source Name Description C datadict Config tpm OK Database Configuration Help Esca To specify the location of your CONFIG TPM file click Browse and select the file from the location screen Locate Configuration File Look in 73 6 Data __ progF L Proarm print l progGL __ progSD L proglN 1 Rwdata prog4R prog Sample progBK progO W Ld sort progBR __progP4 config tpm progEl L progPM File name config Files of type Fies Cancel Payroll Report Applet User s Manual 2 3 Installation When you select the file the final dialog appears Microsoft Excel e PivotTable was changed during Refresh Data operation When you click on OK the PivotTable is updated with your accounting data Report Applet PivotTables Use the descriptions of the PivotTables in chapter 3 to work with your accounting data 2 4 Payroll Report Applet User s Manual Payroll PivotTables PA Checks PA Check Deductions PA Check Earnings PA Check History PA Ch
38. ion 3 3 PACHKDED XLS data files 1 6 1 7 description 3 5 PACHKERN XLS data files 1 7 1 8 description 3 7 PACHKHST XLS data files 1 5 1 6 1 7 1 8 1 9 description 3 9 PACHKWTH XLS data files 1 7 1 8 description 3 15 PACHxxx file description 1 7 PACWxxx file description 1 8 PADDxxx file description 1 6 PADEPTS XLS data files 1 6 description 3 17 PADPxxx file description 1 6 PAECxxx file description 1 7 PAEDxxx file description 1 6 file description 1 6 PAEGxxx file description 1 5 PAEMHST XLS data files 1 5 description 3 21 PAEMPDED XLS data files 1 6 description 3 25 Payroll Report Applet User s Manual PAEMPERN XLS data files 1 6 description 3 27 PAEMPWTH XLS data files 1 6 description 3 31 PAEMxxx file description 1 5 PAEWxxx file description 1 6 PAHCxxx file description 1 8 PAHDxxx file description 1 8 PAHExxx file description 1 9 PAHVxxx file description 1 9 PAHW xxx file description 1 9 PALVHST XLS description 3 35 PAREDED XLS data files 1 8 description 3 37 PAREEARN XLS data files 1 8 description 3 39 PARExxx file description 1 8 PATHDED XLS data files 1 8 description 3 41 PATHEARN XLS data files 1 8 description 3 43 PATHxxx file description 1 8 PAWIxxx file description 1 7 Payroll data files 1 5 description 1 5 installing 2 1 system requirements 2 1 IX 3 Index
39. its of viewing your data in new and different ways For more information about PivotTables see the Microsoft Excel documentation or online help 1 36 Payroll Report Applet User s Manual Installation You can put the Payroll ODBC Report Applet on your system by installing it through Resource Manager The installation process is described in this section The Payroll Report Applet needs a minimum of 295 kilobytes 295 for installation You must also have installed Payroll and the ODBC Kit on your system and the ODBC drivers on the Windows workstation Installing the Report Applet Use the Install Application function on the Resource Manager Installation menu to install the report applet You must install the Payroll application before you install this report applet The installation will treat the report applet as though you are reinstalling Payroll This is normal behavior If you use Direct Deposit you must install the applet for Direct Deposit in order to access the bank account and employee distribution information you enter When you install the report applet Resource Manager copies the PivotTables to the directory where your Payroll programs are stored You must have access to this directory from your Windows machine to access the tables in Microsoft Excel Payroll Report Applet User s Manual 2 1 Installation The CONFIG TPM File When you install the ODBC Kit you specify the location of the data files and
40. l 2 TRANSACTION g 4 INVOICE NUMBER INVOICE DATE ENTRY NUMBER Data Total 5 24889101 12421433 001 Sum of ORDERED QTY 4 B Sum of SHIPPED QTY SELL 4 f Sum of UNIT COST COMPNT 343 55 8 Sum of UNIT PRICE 475 686 g Sum of Profit Dollars 528 544 10 002 Sum of ORDERED QTY 1 11 Sum of SHIPPED QTY SELL 1 12 Sum of UNIT COST COMPNT 907 53 13 Sum of UNIT_PRICE 1317 384 14 Sum of Profit Dollars 409 854 15 003 Sum of ORDERED QTY 7 15 Sum of SHIPPED QTY SELL 7 ilc Sum of UNIT COST COMPNT 22 01 18 Sum of UNIT PRICE 20 7995 19 Sum of Profit Dollars 8 4735 20 004 Sum of ORDERED QTY 5 21 Sum of SHIPPED QTY SELL 5 22 Sum of UNIT COST COMPNT 226 98 23 Sum of UNIT PRICE 526 131 24 Sum of Profit Dollars 1495 705 25 005 Sum of ORDERED QTY 2 26 Sum of SHIPPED QTY SELL 2 27 Sum of UNIT COST COMPNT 151 14 28 Sum of UNIT PRICE 381 645 29 Sum of Profit Dollars 441 01 30 24889101 Sum of ORDERED QTY 19 31 24889101 Sum of SHIPPED_QTY_SELL 19 32 24889101 Sum of UNIT COST_COMPNT 1661 22 33 24889101 Sum of UNIT PRICE 2721 6455 34 24889101 Sum of Profit Dollars 20148 0845 35 24889102 12 21 99 001 Sum of ORDERED QTY 3 36 4 PIN Sheet Shest2 Z Sheets Z Ready Payroll Report Applet User s Manual Sum of SHIPPED QTY SELL Iq 1 bl 1 33 Creating Microsoft Excel PivotTables Introduction Changing the Column Data You can change the data that appears in the columns in the
41. l PivotTables The PivotTable is redisplayed reflecting your changes XX Microsoft Excel Book1 DI x File Edit view Insert Format Tools Data Window D ZR S gt o 2 6 Arial 10 B z u GE eR Ar pivotable 4 B4 INVOICE DATE INVOICE NUMBER INVOICE DATE IENTRY NUMBER 24889101 Sum of ORDERED QTY Sum of SHIPPED QTY SELL Sum of UNIT COST COMPNT Sum of UNIT PRICE Sum of Profit Dollars Sum of ORDERED QTY Sum of SHIPPED QTY SELL Sum of UNIT COST COMPNT Sum of UNIT PRICE Sum of Profit Dollars Sum of ORDERED QTY Sum of SHIPPED QTY SELL Sum of UNIT COST COMPNT Sum of UNIT PRICE Sum of Profit Dollars Sum of ORDERED QTY Sum of SHIPPED QTY SELL Sum of UNIT COST COMPNT Sum of UNIT PRICE Sum of Profit Dollars Sum of ORDERED QTY Sum of SHIPPED QTY SELL Sum of UNIT COST COMPNT Sum of UNIT PRICE Sum of Profit Dollars R H N N 0148 0845 20148 0845 35 24889102 12 21 88 001 Sum of ORDERED QTY 3 3 35 Sum of SHIPPED_QTY_SELL 3 3 49 KW Sheet1 Sheet2 Sheets 1 1 gt Ready Payroll Report Applet User s Manual 1 31 Creating Microsoft Excel PivotTables Introduction Moving Fields and Sorting Data You can dramatically change the appearance of the table by moving the fields around Fields appe
42. mployer paid and the weeks worked limit are indicated If the withholding is an employer paid withholding this file also holds the employer liability account Data from the Withholdings Codes file is displayed in the PA Check Withholdings PACHKWTH XLS and the PA Check History PACHKHST XLS PivotTables PACHxxx The Payroll Checks file stores information about the checks during a payroll check cycle Data from the Checks file is used in the PA Checks PACHECKS XLS PivotTable PACDxxx The Payroll Checks Deductions file stores the deductions taken for each employee paycheck in the current payroll check cycle Data from this file is displayed in the PA Check Deductions PACHKDED XLS PivotTable Payroll Report Applet User s Manual 1 7 Payroll Data Files Introduction PACExxx The Payroll Checks Earnings file stores the earning codes for each employee paycheck in the current payroll check cycle Data from this file is displayed in the PA Check Earnings PACHKERN XLS PivotTable PACWxxx The Payroll Checks Withholdings file stores the withholdings for each employee paycheck in the current payroll check cycle Data from this file is displayed in the PA Check Withholdings PACHKWTH XLS PivotTable PARExxx The Payroll Recurring Entries file stores information about recurring time tickets Data from this file is displayed in the PA Recurring Time Tickets PAREEARN XLS and PA Recurring Deductions PAREDED XLS PivotTables PATHx
43. mt September Amt October Amt November Amt December Amt YTD Amt 7 001 Medical Ins 000 0 00 0 00 0 00 0 00 0 00 000 000 0 00 241 98 0 00 000 24198 8 002 Dental Ins 000 0 00 0 00 0 00 0 00 0 00 0 00 000 0 00 84 00 0 00 01001 8400 9 003 United Way 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 738 84 0 00 000 73884 10 004 Credit Union 000 0 00 0 00 0 00 0 00 0 00 0 00 000 000 290 00 0 00 000 29000 11 005 401K 000 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 1989 77 0 00 DDD 1989 77 12 010 Stock Plan 000 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 525 00 0 00 000 525 00 13 008 Parking 000 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 20 00 0 00 000 2000 14 005 Dues 000 0 00 0 00 0 00 0 00 0 00 000 000 0 00 60 00 0 00 000 60 00 15 Grand Total 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 3949 59 0 00 0 00 3949 59 S FIBI Sheet 151 Ready I 3 26 Payroll Report Applet User s Manual PA Employee Earnings History File Name PAEMPERN XLS Description The PA Employee Earnings History PivotTable uses the data in the Employee Earnings History file to display monthly and year to date hours and earnings for the employees and earnings code you choose The PA Employee Earnings History PivotTable consists of these related spreadsheets Hours and Earnings You can change between these sheets by selecting the appropriate tab at the bottom of the current sheet Payroll Report Applet User s Manual 3 27 PA
44. n Date but you can easily change the sort order or add the Employee ID to the sort Active Fields Default Field Type Field Page Employee ID Row Earnings Code Description Transaction Date Column Hours Hourly Rate Amount Payroll Report Applet User s Manual 3 39 PA Recurring Time Tickets Payroll PivotTables PA Recurring Time Tickets PivotTable Sample File Edit view Insert Format Tools Data Window Help xl Dement amp HIA S1 M3 v aria ri s B zu B A 40054 _ pivotable Qj Eg is n E A3 Employee ID A B C D E dr G aj 1 Payroll Recurring Time Tickets 2 Employee ID Au 4 8 Data B Earning Code Desc Trans Date Hrs Hourly Rate Amt 7 REG Regular Pay 5 18 00 120 000 22 200 888 00 8 Grand Total 120 000 22 200 888 00 rie PRIN Sheett Jal Ready i hom 3 40 Payroll Report Applet User s Manual PA Miscellaneous Deduction History File Name PATHDED XLS Description The PA Miscellaneous Deduction History PivotTable uses the data in the Payroll Transaction History PATHx file to display detailed information about the miscellaneous payroll deductions you have entered The report is sorted by Deduction Code Description and Transaction Date but you can easily change the sort order or include Employee
45. nce No Net Amt Hrs Worked Total Pieces 000001 4597 53 173 330 000002 4139 17 173 330 5 B 7 000003 H 4430 17 173 330 8 1000004 2206 54 173 330 9 Grand Total 15473 41 593 320 kb Sheet Ready 4 4 3 4 Payroll Report Applet User s Manual File Name PACHKDED XLS Description PA Check Deductions The PA Check Deductions PivotTable uses the data in the Checks Deductions PACDx file to display detailed deduction information from each employee paycheck The report is sorted by Sequence Number Deduction Code and Description but you can easily change the sort order or include the Employer Paid flag in the sort You can use this PivotTable to review the deductions calculated in the current check run Active Fields Default Field Type Page Row Column Payroll Report Applet User s Manual Field Employer Paid flag Sequence Number Deduction Code Description Total Deduction Amount 3 5 PA Check Deductions Payroll PivotTables PA Check Deductions PivotTable Sample 188 Eie Edit view Insert Format Tools Data Window Help x eT Xm amp co cw r5 zig 8 Aria yi F 96 se de de z o A PivotTable me 22 53 5
46. oyee ID Row Sequence Number Deduction Code Description Check Number Column Deduction Hours Deduction Amount PA Check History Deductions PivotTable Sample XX Microsoft Excel Pachkhst oj x js File Edit view Insert Format Tools Data Window Help 181 Bc E S xm lt 5 T ri TA Arial BZzUEZBSH S 55x 5 A PivotTable 3 T w 1 iza i 03 A3 gt EMPLOYEE ID A B D E E ial 1 Payroll Check History 2 3 EMPLOYEE ID mi 4 5 Data SEQUENCE NUMBER DED CODE DESCRIPTION CHECK NUMBER Deduction Hrs Deduction Amt 7 000001 001 Medical Ins 0001025 742 83 000002 001 Medical Ins 0001026 D 189 09 9 1000003 001 Medical Ins 0001027 D 89 08 10 000004 001 Medical Ins 0001029 0 26 57 11 000005 001 Medical Ins 0001028 19 08 12 000006 001 Medical Ins 0001030 55 65 13 000007 001 Medical Ins 0001024 24 17 14 000008 001 Medical Ins 0001033 576 58 15 000009 001 Medical Ins 0001034 189 09 16 000010 001 Medical Ins 0001035 89 08 17 000011 001 Medical Ins 0001037 0 26 57 14 4 p IN Summary Deductions Earnings Withholdings Jal Ready Payroll Report Applet User s Manual 3 11 PA Check History Payroll PivotTables PA Check History Earnings Sheet Active Fields Default Field Type Field P
47. payment type hourly or salaried salary and or hourly pay rate pay periods per year job title and accrued and taken sick and vacation time Data from this file is displayed in the PA Check History PACHKHST XLS PivotTable PAEMxxx The Payroll Employee Miscellaneous History file stores miscellaneous historical information for each employee weeks worked allocated tips cost of GTLI DCB 4577 and non 457 plans for each month advance EIC payments uncollected Medicare and other information Data from this file is displayed in the PA Employee History PAEMHST XLS PivotTable Payroll Report Applet User s Manual 1 5 Payroll Data Files Introduction 1 6 PAEDxxx The Payroll Employee Deduction History file stores month quarter and year to date information about each employee s payroll deductions Data from this file is displayed in the PA Employee Deduction History PAEMPDED XLS PivotTable PAEExxx The Payroll Employee Earnings History file stores month quarter and year to date earnings and hours worked both gross and net pay amounts Data from this file is displayed in the PA Employee Earnings History PAEMPERN XLS PivotTable PAEWxxx The Payroll Employee Withholding History file stores month quarter and year to date information about each employee s payroll withholdings Data from this file is displayed in the PA Employee Withholding History PAEMPWTH XLS PivotTable PADPxxx The Payroll Departmen
48. ring Entries PAREX file to display detailed information about the recurring miscellaneous deductions you have set up The report is sorted by Sequence Number Deduction Code and Description but you can easily change the sort order or add the Employee ID to the sort Active Fields Default Field Type Field Page Employee ID Row Sequence Number Deduction Code Description Column Hours Amount Payroll Report Applet User s Manual 3 37 PA Recurring Deductions Payroll PivotTables PA Recurring Deductions PivotTable Sample X Microsoft Excel Pareded File Edit view Insert Format Tools Data Window Help 81 x K M3 Dz m SD gt amp HIA Q 8 aria 0 BrzulEssE su AR Ed 9 Pvotrable 72 Ds s A3 M Employee ID A B C D EET 5 Ez _1 Payroll Recurring Deductions 2 3 ID ES 5 Seq No 000002 Grand Total 0 000 75 00 0 I4 4 gt HI Sheett Jal Ready 3 38 Payroll Report Applet User s Manual PA Recurring Time Tickets File Name PAREEARN XLS Description The PA Recurring Time Tickets PivotTable uses the data in the Recurring Entries PAREX file to display detailed information about the recurring time tickets you have set up The report is sorted by Earnings Code Description and Transactio
49. roll Report Applet User s Manual 1 19 Creating Microsoft Excel PivotTables Introduction 1 20 16 A list of all the tables is displayed Select the SOTD table and click Close Joining Tables 17 18 19 20 21 Locate BATCH ID in the SOTD and SOTH tables then click and hold the left mouse button down on BATCH ID in the SOTH table Drag the field over to the BATCH ID field in the SOTD table and release the mouse button A line appears between the two BATCH ID fields joining the two fields Follow steps 17 through 18 with the ORDER NUMBER field NOTE You may get the following message For now click Yes to ignore the message and join the fields together Microsoft Query Columns ORDER NUMBER and ORDER NUMBER that you are about to join are of is different types Create the join anyway Select the following fields from the SOTD table ENTRY NUMBER e COST COMPNT PRICE ORDERED SHIPPED QTY SELL BACKORDERED QTY Select Save from the File menu to save the query Payroll Report Applet User s Manual Introduction Creating Microsoft Excel PivotTables Using the Query in Microsoft Excel 1 Start Excel and open a new worksheet Fie Insert Format Tools Data Window Help 18 x JOSE SRY ym m o z Z iy 10 95 8 BEE 1 9 Ready 2 Select the Data menu then sel
50. rs 12 21 99 24889101 001 4 4 343 55 475 686 528 544 002 1 1 907 53 1317 384 409 854 003 7 7 22 01 20 7995 8 4735 004 5 5 226 99 526 131 1495 705 005 2 2 161 14 381 645 441 01 24889101 Total 19 19 1661 22 2721 6455 20148 0845 124889102 1001 3 3 840 83 1485 495 1933 995 002 3 3 152 381 645 588 935 003 4 4 171 55 417 96 985 64 24889102 Total 10 10 1164 38 2285 1 11207 2 24889103 001 3 3 348 0582 475 586 382 8834 002 2 2 874 56 1317 384 885 648 003 5 5 0 51 381 256 905 24889103 Total 10 10 1222 6182 1844 451 6218 328 24889104 001 10 10 1342 87 2417 166 10742 96 002 5 5 22 01 51 381 146 855 003 1 1 226 99 526 131 299 141 24889104 Total 16 16 1591 87 2994 678 22444 928 24889105 001 1 1 855 61 1485 495 529 885 002 4 4 161 14 381 645 882 02 003 4 4 176 47 417 96 965 96 24889105 Total 9 9 1193 22 2285 1 9826 92 124889106 1001 5 5 145 98 342 144 980 82 24889106 Total 5 5 145 98 342 144 980 82 30 Grand Total 69 69 6979 2882 12473 1185 379074 2907 31 Id 4 M Sheet X Sheet1 Sheet Sheets 5 714 gt Ready peu Bai 2 You can also drag the selection fields from the Page area to the Row area to sort the data by those fields Payroll Report Applet User s Manual 1 35 Creating Microsoft Excel PivotTables Introduction More About Using PivotTables Feel free to experiment with the orientation of the fields on this sample report As you become more familiar with the tables and how to use them you can enjoy the benef
51. t Excel PivotTables Installation PA Checks PA Checks PA Check Deductions PA Check Earnings PA Check History PA Check Withholdings PA Department Analysis PA Employee History PA Employee Deduction History PA Employee Earnings History PA Employee Withholding History PA Leave History Payroll Report Applet User s Manual 3 7 3 9 3 15 3 17 3 21 3 25 3 27 3 31 3 35 PA Recurring Deductions 3 37 PA Recurring Time Tickets 3 39 PA Miscellaneous Deduction History 3 41 PA Time Ticket History 3 43 Index iv Payroll Report Applet User s Manual Introduction General Information 1 3 Payroll Data Files 1 5 Introduction to PivotTables 1 11 Creating Microsoft Excel PivotTables 1 13 Payroll Report Applet User s Manual 1 1 General Information The OPEN SYSTEMS Accounting Software OSAS product line consists of several accounting applications Each application addresses a different phase of your financial operations together they form a powerful accounting solution to your daily and periodic accounting needs The ODBC Kit The OSAS ODBC Kit provides users with a way to access their OSAS data through any ODBC compliant productivity package The ODBC Kit includes an ODBC driver for Windows the data dictionaries for the OSAS data files utilities for maintaining the data dictionaries and some sample reports in Microsoft Excel Microsoft Access and Crystal Reports for Windows The Report Applets Since the rel
52. thholding History Earnings PivotTable Active Fields Default Field Type Field Page Employee ID Employer Paid flag Row Tax Authority Withholding Code Column January December Earnings Year to Date Earnings PA Employee Withholding History Earnings PivotTable Sample XX Microsoft Excel Paempwth x 5 File Edit view Insert Format Tools Data Window Help 18 D Z EH s o 100 Arial 10 IEE E a 592 BLAS Bivotrable 9 EMPLOYEE ID A B L M N 0 Payroll Employee Withholding History 55 mlm E olo mim ge 5 5 7 TAX AUTHORITY WITHHOLDING CODE Earnings Nov Earnings Dec Earnings YTD Earnings 8 9 EME 106177 65 106177 65 M EOA 106177 65 106177 65 10 FUT 103662 88 0 0 103662 88 FWH 103662 88 0 0 103662 88 MED 106177 65 106177 65 13 OAS 106177 65 106177 65 14 MN Sul 103662 88 0 0 103662 88 15 SWH 103662 88 103662 88 16 Grand Total 839362 12 839352 12 I4 lt q p M Earnings Withholding 7 Ready 3 32 Payroll Report Applet User s Manual Payroll PivotTables PA Employee Withholding History PA Employee Withholding History Withholding PivotTable Active Fields Default Field Type Field Page Employee
53. tober Weeks Worked 91 16 November Weeks Worked 17 December Weeks Worked 18 YTD Weeks Worked 91 48 44 p MN Hours Worked Weeks Worked dal Ready Payroll Report Applet User s Manual 3 23 PA Employee Deduction History File Name PAEMPDED XLS Description The PA Employee Deduction History PivotTable uses the data in the Employee Deduction History PAEDx file to display monthly and year to date deduction totals for the employees you choose The report is sorted by Deduction Code and Description but you can easily change the sort order to include the Employee ID Active Fields Default Field Type Page Row Column Payroll Report Applet User s Manual Field Employee ID Deduction Code Description January December Amounts Year to Date Amount 3 25 PA Employee Deduction History Payroll PivotTables PA Employee Deduction History PivotTable Sample Edt View Insert Format Tools Data Window Help DG xo c ew rri 3 1 5 683 6 9 f in s A3 Employee ID A M N j 1 Payroll Employee Deduction History 2 3 Emplovee ID Al F 4 5 Data B Deduction Code Desc January Amt February Amt March Amt April Amt May Amt June Amt July Amt August A
54. ts file stores general information for each department you set up each earning code for the department the employer paid withholding and deduction and pieces totals and total hours for the department Data from this file is displayed in the PA Department Analysis PADEPTS XLS PivotTable PADDxxx The Payroll Company Deductions file stores the payroll deduction codes and information relating to these codes which you enter through the Payroll Deductions function Data from this file is displayed in the PA Check Deductions PACHKDED XLS and the PA Check History PACHKHST XLS PivotTables Payroll Report Applet User s Manual Introduction Payroll Data Files PAECxxx The Payroll Earnings Codes file stores information that you use when entering time tickets or manual checks Each earning code includes a description whether or not the earning code is included in net pay and fixed withholding the earning type the general ledger account number and the multiplier and add to base factor Data from the Earnings Codes file is displayed in the PA Check Earnings PACHKERN XLS and the PA Check History PACHKHST XLS PivotTables PAWIxxx The Payroll Withholding Codes file stores payroll information for federal state and local withholdings which you enter through the Withholdings function Each withholding has a description a general ledger account number a tax ID and a fixed percentage if appropriate whether or not the withholding is e
55. ude rows where C nd Gr C And Gr Cind Dr Use the Filter Data dialog box to select specific records from the table In most cases you do not need to choose anything in the Filter Data dialog box For example to filter out credit memos select the field named TRANSACTION TYPE select does not equal and then enter 4 for a value TRANSACTION TYPE 4 is a credit memo Payroll Report Applet User s Manual 1 17 Creating Microsoft Excel PivotTables Introduction 12 Click Next The Sort Order box appears Query Wizard Sort Order Specify how you want your data sorted If you don t want to sort the data click Next Ascending Descending Then by Ascending Descending Then by Ascending Descending zl lt Back Cancel Use the Sort Order dialog box to select how the data is sorted For example select a field in Sort by and check Ascending or Descending Select more fields and orders for hierarchical sorts For now don t enter any sort fields 13 Click Finish You are returned to the Microsoft Query screen Microsoft Query L Lx File Edit View Format Table Criteria Records Window Help Hew sii Fe Le R Query 1 from OSAS Data ic x BATCH I ORDER_NUM TRANSACTION T INVUICE NUMHINVOICE DATCUSTOMER ID SUBTOTAL SALES TAX TOTAL COST gt 000002 00000003 3 24889101 1995 1221 GREO
56. vacation pay Data from this file is displayed in the PA Check History PACHKHST XLS PivotTable Payroll Report Applet User s Manual 1 9 Introduction to PivotTables A Microsoft Excel PivotTable is an interactive table that quickly summarizes or cross tabulates large amounts of data You can rotate its rows and columns to see different summaries of the source data filter the data by displaying different pages or display the details for areas of interest A PivotTable contains fields each of which summarizes multiple rows of information from the source data By dragging a field button to another part of the PivotTable you can view your data in different ways For example you can view any field either down the rows or across the columns The PivotTable summarizes data by using a summary function such as Sum Count or Average You can include subtotals and grand totals automatically or use your own formulas by adding calculated fields and items In the Payroll Report Applet several PivotTables are provided based on the data in the OSAS data files The PivotTable is updated through the ODBC driver The next section includes a tutorial for setting up and modifying PivotTables in Excel Payroll Report Applet User s Manual 1 11 Creating Microsoft Excel PivotTables Read this section for an exercise in creating a PivotTable using the ODBC Kit and Microsoft Excel 97 If you require more information about Microsoft Excel consult t
57. want to access Click Connect and enter any information requested by the driver Connect Save my user D and password in the data source Cancel 4 Enteraname you want to give the data source in field 1 You can use the same source again 5 Select the BASIS ODBC Driver in field 2 6 Click Connect The BASIS ODBC Driver Data Source Setup box appears Enter Data Source Specification DK Data Source Name Description Cancel Database Configuration 6 0 D config tpm Advanced gt gt 7 Enter the file path and name of the CONFIG TPM file you set up from within the OSAS ODBC software in the Database Configuration field or select Browse and locate the file Payroll Report Applet User s Manual 1 15 Creating Microsoft Excel PivotTables Introduction If you have already built the shadow dictionary click on the Advanced button and check the options for No Shadow Dictionary Consistency Check and Fast Connect to improve performance See online help for additional information about the options that come with the Advanced button 8 Click OK to connect to the data source You are returned to the Create New Data Source screen 9 Select a table in field 4 if you want to select a default table source otherwise leave field 4 blank and select any table when you develop the query If you select a table the list of tables always starts at that table otherwise t
58. xx The Payroll Transaction History file stores the time tickets and miscellaneous payroll entries you make through the Payroll Transactions function Data from this file is displayed in the PA Time Ticket History PATHEARN XLS and PA Miscellaneous Deduction History PATHDED XLS PivotTables PAHCxxx The Payroll Check History file stores general information employee ID department check number for each check you have disbursed for payroll expenses Data from this file is displayed in the PA Check History PACHKHST XLS PivotTable PAHDxxx The Payroll Check Deductions History file stores a record of the deductions taken from each check you have disbursed for payroll expenses Data from this file is displayed in the PA Check History PACHKHST XLS PivotTable 1 8 Payroll Report Applet User s Manual Introduction Payroll Data Files PAHExxx The Payroll Check Earnings History file stores a record of the earnings associated with each check you have disbursed for payroll expenses Data from this file is displayed in the PA Check History PACHKHST XLS PivotTable PAHWXxxx The Payroll Check Withholdings History file stores a record of the withholdings associated with each check you have disbursed for payroll expenses Data from this file is displayed in the PA Check History PACHKHST XLS PivotTable PAHVxxx The Payroll Leave Adjustment History file stores the positive and negative adjustments you make to an employee s sick and
59. ype from All to 3 Change it to 4 and credit memos are displayed Totals per type are also displayed Adding a Calculated Field You can also add new fields like profit to the data area To add profit to the data area follow these steps 1 Highlight the last row in your data area Sum of UNIT PRICE right click and select Insert Payroll Report Applet User s Manual 1 27 Creating Microsoft Excel PivotTables Introduction The Insert Calculated Field dialog box appears Insert Calculated Field Mane Formula 0 Delete BATCH ID ORDER NUMBER TRANSACTION TYPE INVOICE NUMBER INVOICE DATE CUSTOMER ID SUBTOTAL SALES TAX zi Insert Field 2 Enter the following information about the inserted field Enter Profit Dollars in the Name field Enter SHIPPED QTY SELL UNIT PRICE SHIPPED QTY SELL UNIT COST COMPBNT in the Formula field 3 Click Add Insert Calculated Field x Name Profit Dollars Modify Formula SHIPPED QTY SELL UNIT_PRICE SHIPPED QT Delete Fields BATCH_ID ORDER NUMBER TRANSACTION INVOICE NUMBER INVOICE DATE CUSTOMER ID SUBTOTAL SALES TAX zi Insert Field 1 28 Payroll Report Applet User s Manual Introduction Creating Microsoft Excel PivotTables 4 Click OK X Microsoft Excel Book1 BS Ele Edit view Insert Format Tools Data Window Help The PivotTable is displayed with
Download Pdf Manuals
Related Search