Purchase Order (PO)
Contents
1. 418 sio B 7z 0 9 6 E e 9 Data INVOICE NUMBERIINVOICE DATE ENTRY NUMBER Sum of ORDERED Sum of SHIPPED QTY 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 1002 1 y 907 53 1317 384 409 854 003 7 7 22 01 20 7995 8 4735 004 5 5 226 99 526 131 1495 705 1005 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 1164 38 2285 1 11207 2 16 24889103 12 21 99 001 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 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 629 885 002 4 4 161 14 381 645 882 02 003 4 4 176 47 417 96 965 96 27 24889105 Total 9 9 1193 22 2285 1 9826 92 28 24889106 12 21 99 001 5 5 145 98 342 144 980 82 29 24889106 Total 5 5 145 98 342 144 980 82 30 Grand Total 69 69 6979 2882 12473 1185 379074 2907 31 lap IM Sheet Sheet Sheet Sheet 7 Ready esf2. Trans Number Vendor ID Taxable Sub Nontax Sub Tax Freight Misc Discount Prepaid 00000030 0 00 130 922 000 0 00 000000 1376 64 00000031 0 00 5507 000 0 00 0 00 00000032 00000033 BOR001 00000034 ELLOO1 gt A Invoice Totals _ 72282 56 12153 41 9690 90 nn nn nnn nnn Posted Totals Memo Totals 41 0 00 4336 95 000 0 00 000 72921 000 0 00 0 00 58144 000 0 00 0 00 0 00 Ready 3 18 PO Report Applet User s Manual File Name POTRNTAX XLS Description PO Tax Report The PO Tax Report PivotTable uses the data in the Open Order Header POOHx file to display summarized sales tax breakdowns for your purchase orders The report is sorted by Vendor ID and Location ID but you can easily change the sort order or include the Order Status in the sort Active Fields Default Field Type Page Row Column PO Report Applet User s Manual Field Order Status Vendor ID Location ID Level 1 Tax and Taxable Subtotal Level 2 Tax and Taxable Subtotal Level 3 Tax and Taxable Subtotal Level 4 Tax and Taxable Subtotal Level 5 Tax and Taxable Subtotal 3 19 PO Tax Report PO PivotTables PO Tax Report PivotTable Sample 3 Eile Edit View Insert Format Tools Data Window Help JOSH SRY 5m o c
3. 00000033 00000034 16 13 00000030 01 0 00 0 00 0 00 000 0 00 000 0 00 14 00000031 MILOO1 0 00 0 00 0 00 000 0 00 0 00 0 00 15 00000032 PLADD1 0 00 0 00 000 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 nnn nnn 0 00 0 00 0 00 0 00 4n 15 Ready 3 16 bik Invoice Totals Tee Memo Totals PO Report Applet User s Manual PO PivotTables PO Transaction Summary PO Transaction Summary Posted Totals Sheet Active Fields Default Field Type Field Page Order Status Row Transaction Number Vendor ID Column Taxable and Nontaxable Subtotals Tax Freight Miscellaneous Charges Discount Prepaid Amount PO Transaction Summary Posted Totals PivotTable Sample X Microsoft Excel Potrnsum x File Edit View Insert Format Tools Data Window Help la 587 Elz A EA OB 0 pyotrabie 19 A9 Order Status D E Jj 1 PO Transaction Summary 2 Order Status 3 1 New 4 2 Printed pou 3 Invoice Received 4 Goods Received 7 5 Return S
4. Browse Options El Use the Query Wizard to create edit queries 10 Select the data source you set up in the previous steps 1 12 PO 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 SOSL TRANSACTION c 5 CODE E SOTB lt lt Group EH SOTD Preview of data in selected column El Cancel 11 Select a 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 21x 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 include rows where C nd Gr C And Gr Cind Gr 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
5. T 2 1 30 PO 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 Eile Edit View Insert Format Tools Data Window Help 18 A T 6 10 B Z u E820 8 44 o A Bivotrable BS INVOICE NUMBER A B G H F 1 ID All y _ TRANSACTION Data INVOICE DATE INVOICE NUMBIENTRY NUMBER Sum of ORDERED Sum of SHIPPED_QTY_SELL Sum of UNIT COST COMPNT Sum of UNIT PRICE Sum of Profit Dollars 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 525 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 688 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 686 382 8834 002 2 2 874 56 1317 384 885 648 003 5 5 0 51 381
6. Ready PO Report Applet User s Manual Num 1 25 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 uber Automatic Custom 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 26 PO Report Applet User s Manual Introduction Creating Microsoft Excel PivotTables The PivotTable is redisplayed reflecting your changes X Microsoft Excel 1 OF x 83 File Edit View Insert Format Tools Data Window gt 2 100 2 10 je z u zm GE eR 5 G0 A pivotable AQ Ey B4 INVOICE DATE INVOICE NUMBER INVOICE DATE IENTRY NUMBER 24889101 Sum of ORDERED QTY Sum of SH
7. 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 ex Data progra JProgrm print JprogGL JprogsD prog P L proglN _IRwdata prog R _JprogJO L Sample progBK L progO W Ld sort progBR _JprogPA E config tpm progEl L progPM File name config Files of type Fies Cancel PO 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 PO Report Applet User s Manual PivotTables PO Order Detail 3 3 PO Invoice Detail 3 5 PO Invoice Totals 3 7 PO Purchase Requisitions 3 11 PO Receipt of Goods 3 13 PO Transaction Summary 3 15 PO Tax Report 3 19 PO Report Applet User s Manual 3 1 File Name PODETORD XLS Description PO Order Detail The PO Order Detail PivotTable uses the data in the Open Order Detail file to display detailed information about the purchase orders you have on file The data is sorted by Transaction Number Item ID and Location ID but you can easily change
8. 3 9 PO Invoice Totals PivotTable description 3 7 files used in 1 6 PO Order Detail PivotTable description 3 3 files used inj 1 5 sample report 3 4 screen 3 4 PO Purchase Requisitions PivotTable description 3 11 files used inj 1 5 sample report 3 12 screen 3 12 PO Receipt of Goods PivotTable description 3 13 files used in 1 6 sample report 3 14 screen 3 14 PO Tax Report PivotTable description 3 19 files used in 1 5 sample report 3 20 screen 3 20 PO Transaction Summary Invoice Totals PivotTable sample report 3 16 screen 3 16 Memo Totals PivotTable sample report 3 18 screen 3 18 Posted Totals PivotTable sample report 3 17 screen 3 17 2 Transaction Summary PivotTable description 3 15 files used in 1 5 PODETORD XLS data files 1 5 description 3 3 POINVDET XLS data files 1 6 description 3 5 POINVTOT XLS data files 1 6 description 8 7 POOHxxx file description 1 5 POORxxx file description 1 5 POPQxxx file description 1 5 POPURREQ XLS data files 1 5 description 3 11 PORECPT XLS data files 1 6 description 3 13 PORGxxx file description 1 6 PORIxxx file description 1 6 PORTxxx file description 1 6 POTRNSUM XLS data files 1 5 description 3 15 POTRNTAX XLS data files 1 5 description 3 19 Purchase Order data files 1 5 description 1 5 installing 2 1 system requirements 2 1 PO Report Applet User s Manual Index R Rep
9. m 3 SALES_TAX_ Preview Now Beck Cancel PO Report Applet User s Manual 1 19 Creating Microsoft Excel PivotTables Introduction 6 Click Next gt 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 21x 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 PivotTable Wizard Step 2 of 4 2 Where is your external data stored Get Data Data fields have been retrieved Cancel lt Back Einish 9 Click Next gt 1 20 PO Report Applet User s Manual Introduction 10 Creating Microsoft Excel PivotTables The PivotTable Wizard Step 3 dialog box appears PivotTable Wizard Step 3 of 4 BEI LS x LI Construct your PivotTable by dragging the field buttons on the right to the diagram on the left 109 ORDERED ORDERN rreren ransad Backorg NVOICE ENTRY NI dd INVOICE INIT CO USTOME UN 3 m gt The selected fields and four areas Page Row Column and Data to put fields are displayed Dr
10. 0000 1342 8700 13428 70 20 4 0000 0 0000 0 0000 22 0100 88 04 21 4 0000 0 0000 0 0000 226 9900 907 96 22 4 0000 0 0000 0 0000 14010 7000 56042 80 23 00000033 5 0000 0 0000 0 0000 348 0580 1740 29 24 3 0000 0 0000 0 0000 874 5500 2623 68 25 6 0000 0 0000 0 0000 1298 2400 7789 44 26 00000034 15 0000 11 0000 0 0000 1374 2000 20613 00 27 5 0000 6 0000 0 0000 3630 1200 18150 60 28 00000035 8 0000 6 0000 6 0000 19 8900 159 12 29 4 0000 4 0000 4 0000 210 0300 840 12 30 12 0000 10 0000 10 0000 840 8300 10089 96 8 0000 3 0000 2 0000 1036 1000 8288 80 00000036 00 00000037 00 38 Grand Total 154 0000 90 0000 48 0000 30417 4980 4584294 59 39 2 I4 14 W Sheett 141 1 re Ready NMI 3 4 Report Applet User s Manual Invoice Detail File Name POINVDET XLS Description The PO Invoice Detail PivotTable uses the data in the Invoice Detail PORIx file to display detailed information about the invoices you ve received from your vendors and applied to your purchase orders The data is sorted by Transaction Number Entry Number and Sequence Number but you can easily change the sort order or add the GL Period Invoice Status or Invoice Source to the sort Active Fields Default Field Type Field Page GL Period Invoice Status Invoice Source Row Transaction Number Entry Number Sequence Number Column Quantity Unit Cost Extended Cost PO Report Applet User s Manual 3 5 PO Invoice Detai
11. ACEOD 97395 0 1030 65 21000002 00000010 4 24889108 19961221 105001 4821455 0 27910 M s RecoaTi AT Select View Criteria to show edit criteria limiting records shown NUM PO 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 e BATCH ID ORDER NUMBER e TRANSACTION TYPE INVOICE NUMBER e INVOICE DATE e CUSTOMER ID e SUBTOTAL e SALES TAX e 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 PO Report Applet User s Manual 1 15 Creating Microsoft Excel PivotTables Introduction 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 field in the SOTD table a
12. D data files inPO 1 5 Data Source creating anew source 1 11 Fast Connect 1 12 No Shadow Dictionary Consistency Check 1 12 selecting a data source 1 12 F Fast Connect Microsoft Query 1 12 installing the applet 2 1 M Microsoft Excel 1 9 PivotTable layout 1 21 PivotTable sample 1 23 PivotTable wizard 1 17 selecting a data source 1 18 Microsoft Query 1 9 adding tables 1 15 building a query 1 10 Filtering data 1 13 joining tables 1 16 Selecting atable 1 13 PO Report Applet User s Manual Index Sorting data 1 14 N No Shadow Dictionary Consistency Check Microsoft Query 1 12 ODBC definition 1 3 OSAS general information 1 3 PivotTable adding a calculated field 1 23 changing column data 1 30 changing data sort 1 31 changing field properties 1 26 changing selection fields 1 28 definition 1 7 moving fields 1 28 PivotTable Wizard Choosing a data source 1 18 getting the data 1 18 laying out the table 1 21 returning the data 1 22 PivotTables PO Invoice Detail 3 5 PO Invoice Totals 3 7 PO Order Detail 3 3 PO Purchase Requisitions 3 11 PO Receipt of Goods 3 13 PO Tax Report 3 19 PO Transaction Summary 3 15 1 Index PO Invoice Detail PivotTable description 3 5 files used in 1 6 sample report 3 6 screen 3 6 PO Invoice Totals Current Totals PivotTable sample report 3 8 screen 3 8 Posted Totals PivotTable sample report 3 9 screen
13. must also have installed Purchase Order 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 Accounts Payable and Purchase Order applications before you install this report applet The installation will treat the report applet as though you are reinstalling Purchase Order This is normal behavior You must install the applet for Accounts Payable in order to access the vendors and other master file and historical information you enter When you install the report applet Resource Manager copies the PivotTables to the directory where your Purchase Order programs are stored You must have access to this directory from your Windows machine to access the tables in Microsoft Excel PO 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 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 CAWINDOWS 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 CAWINDOWS directory and ch
14. to display summarized totals for the open purchase orders you on file The PO Transaction Summary Pivot Table consists of these related spreadsheets Invoice Totals Posted Totals and Memo Totals You can change between these sheets by selecting the appropriate tab at the bottom of the current sheet The information is sorted by Transaction Number and Vendor ID but you can change the sort easily to include the Order Status PO Report Applet User s Manual 3 15 PO Transaction Summary PO PivotTables PO Transaction Summary Invoice Totals Sheet Active Fields Default Field Type Page Row Column Field Order Status Transaction Number Vendor ID Taxable and Nontaxable Subtotals Tax Freight Miscellaneous Charges Discount Prepaid Amount PO Transaction Summary Invoice Totals PivotTable Sample 188 Eie Edit view Insert Format Tools Data Window Help 18 2 IBIS ew 88 Arial 10 Jee 2273 E 1 5 Ar PivotTable B i a E Order Status F Formula Bar H PO Transaction Summary Order Status 1 New 2 Printed 3 Invoice Received A Goods Received Return Shipped Debit Memo Applied Cancelled Trans Number Tax Freight Misc Discount Prepaid
15. 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 629 885 002 4 4 161 14 381 645 882 02 003 4 4 176 47 417 96 5 24889105 Total 9 9 1193 22 2285 1 9825 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 Sheet Sheet Sheets 7 714 gt Ready peu 2 You can also drag the selection fields from the Page area to the Row area to sort the data by those fields PO Report Applet User s Manual 1 31 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 benefits of viewing your data in new and different ways For more information about PivotTables see the Microsoft Excel documentation or online help 1 32 PO Report Applet User s Manual Installation You can put the Purchase Order ODBC Report Applet on your system by installing it through Resource Manager The installation process is described in this section The Purchase Order Report Applet needs a minimum of 111 kilobytes 111KB for installation You
16. 4 for a value TRANSACTION_TYPE 4 is a credit memo PO Report Applet User s Manual 1 13 Creating Microsoft Excel PivotTables Introduction 12 Click Next gt The Sort Order box appears Query Wizard Sort Order 21x Specify how you want your data sorted If you don t want to sort the data click Next Ascending 3 Descending 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 El ES File Edit View Format Table Criteria Records Window Help Ejea y 44 E E Ce e a Query 1 from OSAS Data ic x BATCH MORDER NUM TRANSACTIUN TIINVOICE NUMHINVOICE DANCUSTOMER ID SUBTOTAL SALES TAX TOTAL COST gt 000002 00000003 3 24889101 1995 1221 GREOOT 675967 0 3893 03 000002 00000004 3 24889102 19961221 105001 7273 27 0 3664 69 121000002 00000005 3 24889103 19951221 SUNOOI 4318 74 0 279329 000002 00000006 3 24889104 19951221 CASHCA 249547 1497 28 13765 74 000002 00000007 3 24889105 19981221 CASHMN 4683 92 0 2206 05 00002 00000008 3 24889106 19961221 CASHPS 11072 0 7283 121000002 00000003 4 24889107 19961221
17. 7 1 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 304124889101 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 4 Wi Sheer1 Shest2 4 Sheets Ready PO Report Applet User s Manual Sum of SHIPPED_QTY_SELL Iq el 1 29 Creating Microsoft Excel PivotTables Introduction Changing the Column Data You can change the data that appears in the columns in the 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 1 BEE 8 Ele Edt view Insert Format Tools Data Window Help 1
18. 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 Sum of UNIT COST 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 el 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 19 19 36 24889101 5 of SHIPPED_QTY_SELL 19 19 37 24889101 Sum of UNIT_COST_COMPNT 1661 22 1661 22 38 24889101 Sum of UNIT PRICE 2721 6455 2721 5455 I4 4 gt HW 5 4 Sheet 4 Sheet3 7 14 1 Te
19. Driver Data Source Setup box appears Enter Data Source Specification DK Data Source Name Description Database Configuration e108As proso 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 PO Report Applet User s Manual 1 11 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 the 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
20. IPPED 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 36 Sum of SHIPPED_QTY_SELL 3 3 x 4 Sheet 1 Sheet2 Sheets 1 1 gt PO Report Applet User s Manual 1 27 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 appear 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 t
21. OPEN SYSTEMS Accounting Software Purchase Order ODBC Report Applet User s Manual 2210 60 1998 Open Systems Holdings Corp rights reserved Document Number 2210 POO600 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 Purchase Order Data Files Introduction to PivotTa
22. Order Header file is displayed on the PO Transaction Summary POTRNSUM XLS and PO Tax Report POTRNTAX XLS PivotTables The Purchase Requisition file stores records for orders generated the Generate Orders function In the Generate Orders function the records whose orders you generate are sent to the Open Order files The records whose orders you do not generate are kept in this file indefinitely The data stored in this file is the basis of the PO Purchase Requisitions POPURREQ XLS PivotTable PO Report Applet User s Manual 1 5 Purchase Order Data Files Introduction PORIxxx PORGxxx and PORTxxx The Receipts and Invoices files store the receipts and invoices you entered for orders and the debit memos you applied to returns The PORGxxx file stores line item information about goods received the PORIxxx file stores line item information about invoices applied the PORTxxx file stores totals information Data from the PORIxxx file appears in the PO Invoice Detail POINVDET XLS PivotTable Data from the PORGxxx file is displayed on the PO Receipt of Goods PORECPT XLS PivotTable Data from the PORT xxx file is the basis for the PO Invoice Totals POINVTOT XLS PivotTable 1 6 PO Report Applet User s Manual 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 diff
23. Y 1 Posted 5 Debit Memo 5 E Data 7 Trans No Sequencellnvoice Nollnv Date Taxable Subtotal Nontax Subtotal Tax Freight Misc Discount Prepaid 00000035 001 198307 12 21 99 11439 96 0 00 686 40 000 0 00 24253 0 00 9 00000036 001 48017 12 21 99 3189 26 0 00 191 35 000 0 00 0 00 0 00 10 100000037 001 4100 12 21 99 9769 62 0 00 586 17 0 00 0 00 0 00 0 00 11 00000038 001 50701 12 21 99 726 18 0 00 4357 000 0 00 0 00 000 12 Grand Total 25125 02 0 00 1507 44 000 000 24253 0 00 13 I4 4 W Current Totals Posted Totals dal ye 3 8 Ready INUM PO Report Applet User s Manual PO PivotTables PO Invoice Totals PO Invoice Totals Posted Totals Sheet Active Fields Default Field Type Page Row Column Field Invoice Status Invoice Source Transaction Number Sequence Number Invoice Number Invoice Date Taxable and Nontaxable Subtotals Tax Freight Miscellaneous Charges Discount Prepaid Amount PO Invoice Totals Posted Totals PivotTable Sample X Microsoft Excel Poinvtot A ES Eile Edit Insert Format Tools Data Window 8 DG E 587 E 5 6 Aria 0 BruEssE s ui Bivotrable bd Invoice Status A B ir G HESE EX 1 PO Invoice Tot
24. ag 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 SHIPPED QTY SELL UNIT_COST_COMPNT and UNIT PRICE into Data PO Report Applet User s Manual 1 21 Creating Microsoft Excel PivotTables Introduction 1 22 11 The fields are displayed on the screen Numeric fields dropped into the Data section become summary fields PivotTable Wizard Step 3 of 4 L2 1x EJ Construct your PivotTable by dragging the field buttons on the right to the diagram the left ORDERED TRANSAG BATCH IO COLUMN Star ORDERED_d cif Backord Sum of SHIPPED Q ENTRY NE Am of UNIT COST Invoice EnTRY_N E E invorce unir co custome PR1 1 Finish Click Next The PivotTable Wizard Step 4 dialog box appears PivotTable Wizard Step 4 of 4 HE Where do you want to put the PivotTable New worksheet Existing worksheet 5 Click Finish to create your PivotTable Cancel Options lt Back Next gt 12 last step lets you create the PivotTable either in the existing workshe
25. als 2 Invoice Status Invoice Source 3 Invoice Status All Y D Current 1 Invoice 4 Invoice Source y 1 Posted 5 Debit Memo E 6 Data 7 Trans No Sequence No Invoice No Inv Date Taxable Subtotal Nontax Subtotal Tax Freight Misc Discount Prepaid 00000035 001 198307 12 21 99 0 00 0 00 0 00 000 0 00 0 00 0 00 9 00000036 001 46017 12 21 99 0 00 0 00 0 00 0 00 0 00 0 00 0 00 10 00000037 001 4100 12 21 99 0 00 0 00 0 00 0 00 0 00 0 00 0 00 11 00000038 001 50700 12 21 99 0 00 0 00 0 00 000 0 00 0 00 0 00 12 Grand Total 0 00 0 00 0 00 000 0 00 0 00 0 00 13 1414 gt IM Current Totals Posted Totals Jal E Ready PO Report Applet User s Manual 3 9 PO Purchase Requisitions File Name POPURREQ XLS Description The PO Purchase Requisitions PivotTable uses the data in the Purchase Requisitions POPQx file to display detailed information about the items that have been requisitioned but not yet placed in a purchase order The report is sorted by Item ID and Location ID but you can easily change the order of the sort or add the Source Application and Vendor ID to the sort Active Fields Default Field Type Field Page Source Application Vendor ID Row Item ID Location ID Column Quantity Ordered Unit Cost Extended Cost PO Report Applet User s Manual 3 11 PO Purchase Requisitions PO PivotTables PO Purchase Requisitions PivotTable Sample X Micros
26. ange 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 CAWINDOWS 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 PO Report Applet User s Manual Installation Using a Different CONFIG TPM If you store your CONFIG TPM file in a location other than the CAWINDOWS directory you will see this message when you attempt to refresh the data in any PivotTable included with this report applet Microsoft Excel Ez 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
27. ayed 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 Back Next gt Finish 4 Instep 2 of the Wizard click Get Data PO Report Applet User s Manual Introduction Creating Microsoft Excel PivotTables The Choose Data Source box from Microsoft Query appears Choose Data Source L2 1x OK Databases Queries lt New Data Source gt 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 El 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 121 x Databases Queries dunning Cancel pivot Browse didi Options El Use the Query Wizard to create edit queries The Choose Columns box under Query appears Query Wizard Choose Columns 21x What columns of data do you want to include in your query Available tables and columns Columns in your query BATCH_ID ORDER_NUMBER TRANSACTION_TYPE INVOICE_NUMBER ARCC Ea INVOICE_DATE EH ARCD_1 CUSTOMER_ID 8 ARCD 2 SUBTOTAL
28. bles Creating Microsoft Excel PivotTables Installation PO PivotTables PO Order Detail PO Invoice Detail PO Invoice Totals PO Purchase Requisitions PO Receipt of Goods PO Transaction Summary PO Tax Report Index PO Report Applet User s Manual A AS sc on 2 1 3 5 3 7 3 11 3 13 3 15 3 19 Introduction General Information Purchase Order Data Files Introduction to PivotTables Creating Microsoft Excel PivotTables PO Report Applet User s Manual 1 3 1 5 1 7 1 9 General Information The OPEN SYSTEMS Accounting Software 05 5 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 release 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
29. erent 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 Purchase Order 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 PO Report Applet User s Manual 1 7 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 the 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 e Install and set up the BASIS ODBC drivers e Install Microsoft Excel 97 and Microsoft Query 97 Note This section includes instructions for using Microsoft Query with Microsoft Exce
30. ert Calculated Field dialog box appears Insert Calculated Field 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 e 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 24 PO 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 the Sum of Profit Dollars field 1515 le x lt 7 co We gt ALL 4948 100 8 Paria rio B H 6 55 8 ml Oa A PivotTable jm B D E E Giaj 1 TRANSACTION TYT AII x 2 g BATCH ID 4 INVOICE NUMBER INVOICE
31. et or in a different worksheet Accept the given options and click Finish PO Report Applet User s Manual Introduction Creating Microsoft Excel PivotTables The PivotTable is displayed 83 File Edit View Insert Format Tools Data Window Hi DG M SRY BAS oc PivatTable e EG INVOICE INVOICE ENTRY N Data 1 1 Grand Total 24889101 12 21 95 Sum of OR 4 Sum of SH E 4 Sum of UNIT COMP 343 55 Sum of UNIT 475 686 475 686 Sum of OR 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 Sheet3 1 Highlight rows columns to shift them around display only invoices change Transaction Type from to 3 Change it to 4 and credit memos 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 PO Report Applet User s Manual 1 23 Creating Microsoft Excel PivotTables Introduction The Ins
32. ew rz 15 6 BA FE L 9 A Q E el a 9 Order Status C D E G H 1 K E F 1 PO Tax Report x Order Status 3 1 New 4 2 Printed 5 3 Invoice Received 4 Goods Received 7 5 Return Shipped 8 6 Debit Memo Applied 9 JOrder Status y 7 Cancelled 10 11 Data 12 Vendor ID Location ID Lvl 1 Tax Lvl 1 Taxable Lvl 2 Tax Lvl 2 Taxable 3 Tax 3 Taxable Lvl 4 Tax 4 Taxable Lvl 5 5 Taxable 13 ACEO01 001 191 35 3189 26 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 14 BOROD1 MDOOO1 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 15 CLEOD1 MNooo1 586 17 9769 62 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 16 ELLDO1 001 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 17 JOHO01 MNODOM 43 57 726 18 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 18 MILDO1 CADODT 686 40 11439 96 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 19 00001 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 20 PLADD1 MNoo01 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 21 001 001 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 22 Grand Total 1507 49 25125 02 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 2 23 Ir WIN Sheett 14 1 Ready 1 2 3 20 PO Report Applet User s Manual accessing data Microsoft Excel 1 9 Accounts Payable installing the applet with 2 1
33. he batches across the table columns as they are in our sample table follow these steps 1 Position the mouse cursor over 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 28 PO Report Applet User s Manual Introduction Creating Microsoft Excel PivotTables The change appears immediately X Microsoft Excel Book1 53 File Edit View Insert Format Tools Data Window Help Biel ES ajx amp amp amp lt 7 ez HA d 9 Q1 CA v aria rio B 7U 2 9 9 S 5 A PivotTable im E4 Total 1 BATCH ID All 2 TRANSACTION 3 4 INVOICE NUMBER INVOICE DATE ENTRY NUMBER Data Total 5 124889101 12 21 991 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 18 Sum of UNIT_PRICE 1317 384 14 Sum of Profit Dollars 409 854 15 003 Sum of ORDERED QTY
34. hipped 8 Debit Memo Applied 9 Order Status AN 7 Cancelled 10 11 Data 12 Trans ID Taxable Subtotal Nontax Subtotal Tax Freight Misc Discount Prepaid 13 00000030 001 0 00 0 00 0 00 000 0 00 0 00 0 00 14 00000031 MILOO1 0 00 0 00 000 000 0 00 0 00 0 00 15 00000032 PLADO1 0 00 0 00 000 0 00 0 00 0 00 0 00 16 100000033 BOROO1 0 00 0 00 0 00 0 00 0 00 0 00 0 00 17 00000034 01 0 00 0 00 0 00 000 0 00 0 00 0 00 MN Invoice Totals Posted Totals Memo OOO Ready 1 Pri PO Report Applet User s Manual 3 17 PO Transaction Summary PO PivotTables PO Transaction Summary Memo Totals Sheet Active Fields Default Field Type Page Row Column Field Order Status Transaction Number Vendor ID Taxable and Nontaxable Subtotals Tax Freight Miscellaneous Charges Discount Prepaid Amount PO Transaction Summary Memo Totals PivotTable Sample 188 Eie Edit view Insert Format Tools Data Window Help 18 JOSE 3 f BL ELM 9 a 10 amp Arial 10 B Fy es Ar moran E x e WE 2 23 Order Status PO Transaction Summary 1 2 3 4 5 7 Order Status 1 New Printed Invoice Received Goods Received Return Shipped Debit Memo Applied Cancelled
35. l If necessary you can install Microsoft Query from the Microsoft Office 97 media You may also need to create a shortcut to Query manually PO Report Applet User s Manual 1 9 Creating Microsoft Excel PivotTables Introduction Building a Query For a PivotTable 1 Start Microsoft Query Zr Microsoft Query Of x Eile Help oud ela 5 72 4 amp 3 irn err 2 Under the File menu select New The Choose Data Source screen appears Databases Queries RER ed dBASE Files not sharable Excel Files not sharable FoxPro Files not sharable MS Access 97 Database not sharable OSAS Text Files not sharable El Use the Query Wizard to create edit queries PO Report Applet User s Manual Introduction Creating Microsoft Excel PivotTables 3 Select lt New Data Source gt 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 ir fosas Data Select a driver for the type of database you want to access Click Connect and enter any information requested by the driver Connect Save JD and password in the data source 4 Enter aname you want to give the data source in field 1 You can use the same source again 5 Selectthe BASIS ODBC Driver in field 2 6 Click Connect The BASIS ODBC
36. l PO PivotTables PO Invoice Detail PivotTable Sample X Microsoft Excel Poinvdet iO x 83 File Edit View Insert Format Tools Data Window Help 81 x Dc E SRY X o c Ar zi 4094 s aria sio 3 SB HH OA 72 a ie M GL Period A B D G H FR 1 PO Invoice Detail 2 Invoice Status 3 JGL Period All x D Current 4 Invoice Status All Y 1 Posted 5 Invoice Source All Y 6 Data 8 Transaction No Entry No Sequence No Unit Cost Ext Cost 9 00000035 001 001 6 0000 19 8900 119 34 10 002 001 4 0000 210 0300 840 12 11 003 001 10 0000 840 8300 8408 30 12 004 001 2 0000 1036 1000 2072 20 13 00000036 001 001 3 0000 343 5500 1030 65 14 002 001 2 0000 907 5300 1815 06 15 003 001 1 0000 343 5500 343 55 18 00000037 001 001 2 0000 855 6100 1711 22 17 002 001 5 0000 161 1400 805 70 18 003 001 7 0000 1036 1000 7252 70 19 00000038 001 001 5 0000 121 0300 726 18 20 Grand Total 48 0000 5875 3600 25125 02 5 1 Jal 1 Ready 3 6 PO Report Applet User s Manual Invoice Totals File Name POINVTOT XLS Description PO Invoice Totals PivotTable uses the data in the Invoice Header file to display summarized totals for the invoices you have received f
37. lex E A E 5 Arial 10 BZU gt E e a GL Period B D E Hm 1 PO Receipt of Goods 2 Receipt Status Source 3 GL Period All Y 0 Current 1 Purchase 4 Receipt Status All 1 Posted 5 Retum 5 Source All Y 6 7 Data 8 Transaction Num Entry Number Sequence Gt y Unit Cost Ext Cost 9 00000031 001 001 24 0000 57 3600 1376 64 10 00000034 001 001 3 0000 343 5500 1030 65 002 001 3 0000 907 5300 2722 59 12 003 001 1 0000 9075300 907 53 El 004 001 3 0000 3435500 1030 65 14 005 001 3 0000 343 5500 1030 65 15 006 001 2 0000 343 5501 687 10 16 007 001 1 0000 907 5301 907 53 sf 008 001 1 0000 907 5301 907 53 18 00000035 001 001 6 0000 19 8900 119 34 18 002 001 40000 210 0300 840 12 20 003 001 10 0000 840 8300 8408 30 21 004 001 3 0000 1036 1000 3108 30 22 00000036 001 001 3 0000 343 5500 1030 65 23 002 001 2 0000 907 5300 1815 06 24 003 001 1 0000 343 5500 343 55 25 00000037 001 001 2 0000 855 6100 1711 22 26 002 001 5 0000 161 1400 805 70 27 003 001 7 0000 1036 1000 7252 70 28 00000038 001 001 6 0000 121 0300 726 18 29 Grand Total 90 0000 10937 0400 36761 99 44 Sheet1 14 Ready 3 14 PO Report Applet User s Manual Transaction Summary File Name POTRNSUM XLS Description The PO Transaction Summary PivotTable uses the data in the Open Order Header file
38. 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 PO Report Applet User s Manual 1 3 Purchase Order Data Files You use the Purchase Order system to account for orders you place with vendors Purchase Order encompasses the capabilities of Accounts Payable you can record purchases you made from vendors and orders you have not yet paid Purchase Order lends its tracking capabilities to other applications associated with tracking assets Inventory for example PO Data Files The Purchase Order Report Applet contains several spreadsheets that report information from the OSAS Purchase Order data files The PivotTables in the PO Report Applet are based on these data files POORxxx and POOHxxx The Open Order Detail and Header files store records for orders and returns before you post them If Purchase Order is interfaced with Inventory these functions update serialized and nonserialized item balances and costs in Inventory The file stores header and totals information the POORxxx file stores line item information Data from the Open Order Detail file is used in the PO Order Detail PODETORD XLS PivotTable Data from the Open
39. nd 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 e ENTRY NUMBER COST COMPNT e PRICE ORDERED e SHIPPED QTY SELL e BACKORDERED QTY Select Save from the File menu to save the query PO Report Applet User s Manual Introduction Creating Microsoft Excel PivotTables Using the Query in Microsoft Excel 1 Start Excel and open new worksheet Ele view Insert Format Tools Data Window Help 18 x JOSE SRY 2 2 AREA 9 aov 2 95 8 BEE 1 9 2 Select the Data menu then select PivotTable Report PO Report Applet User s Manual 1 17 Creating Microsoft Excel PivotTables Introduction 3 The PivotTable Wizard appears PivotTable 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 displ
40. oft Excel A ES me File Edit View Insert Format Tools Data Window Help 81 x DG E 587 ian 10 J 2 2 pario pre m e Cd y 3 0000 343 5500 1030 65 5 0000 161 1400 805 70 5 0000 176 4700 882 35 gt p Sheet1 Ready 3 12 PO Report Applet User s Manual of Goods File PORECPT XLS Description The PO Receipt of Goods PivotTable uses the data in the Receipts Detail PORGx file to display detailed information about the goods you ve received against your current purchase orders The report is sorted by Transaction Number Entry Number and Sequence Number but you can easily change the sort order to include the GL Period Receipt Status or Source Code Active Fields Default Field Type Field Page GL Period Receipt Status Source Row Transaction Number Entry Number Sequence Number Column Quantity Unit Cost Extended Cost PO Report Applet User s Manual 3 13 Receipt of Goods PO PivotTables PO Receipt of Goods PivotTable Sample me File Edit View Insert Format Tools Data Window Help
41. ort Applet requirements for 1 9 report applet installation 2 1 report applets definition 1 3 5 spreadsheets PO Invoice Detail 3 5 PO Invoice Totals 3 7 PO Order Detail 3 3 PO Purchase Requisitions 3 11 PO Receipt of Goods 3 13 PO Tax Report 3 19 PO Transaction Summary 3 15 system requirements 2 1 PO Report Applet User s Manual IX 3
42. rom your vendors and applied to your orders The PO Invoice Totals PivotTable consists of these related spreadsheets Current Totals and Posted Totals You can change between these sheets by selecting the appropriate tab at the bottom of the current sheet The information is sorted by Transaction Number Sequence Number Invoice Number and Invoice Date but you can change the sort easily to include the Invoice Status or Invoice Source PO Report Applet User s Manual 3 7 PO Invoice Totals PO PivotTables PO Invoice Totals Current Totals Sheet Active Fields Default Field Type Page Row Column Field Invoice Status Invoice Source Transaction Number Sequence Number Invoice Number Invoice Date Taxable and Nontaxable Subtotals Tax Freight Miscellaneous Charges Discount Prepaid Amount PO Invoice Totals Current Totals PivotTable Sample X Microsoft Excel Poinvtot Oy x 83 File Edit View Insert Format Tools Data Window Help 21815 SRY 5 amp wr B 100 8 aria 710 35 3 gt eiotrable 2 ic x bd Invoice Status A B El H IE zl 1 PO Invoice Totals 2 Invoice Status Invoice Source 3 JInvoice Status All 0 Current 1 Invoice 4 Invoice Source
43. the sort order or add the Order Status and Vendor ID to the sort Active Fields Default Field Type Page Row Column PO Report Applet User s Manual Field Order Status Vendor ID Transaction Number Item ID Location ID Order Quantity Received Quantity Invoiced Quantity Unit Cost Extended Order Cost 3 3 Order Detail PO PivotTables PO Order Detail PivotTable Sample X Microsoft Excel Podetord iO x File Edit View Insert Format Tools Data Window Help la Dc E S6RY BBS Ez ALE B rio 7 BZU PivotTable E 9 Order Status A B E D E E G H Fl 1 PO Order Detail 2 Order Status El 1 New 4 2 Printed B 3 Invoice Received 4 Goods Received 7 5 Return Shipped 8 5 Debit Applied 9 Order Status 7 Cancelled 11 12 Data 13 Trans Num Item ID Location ID Order Qty Received Qty Invoice Qty Unit Cost Extended Order Cost 14 00000030 450 CA0001 5 0000 0 0000 0 0000 152 0000 760 00 15 460 CAD001 6 0000 0 0000 0 0000 176 4700 1058 82 15 600 CAD001 3 0000 0 0000 0 0000 121 0300 363 08 17 00000031 24 0000 24 0000 0 0000 57 3600 1376 64 18 00000032 2 0000 0 0000 0 0000 907 5300 1815 06 19 10 0000 0 0000 0
Download Pdf Manuals
Related Search