Home

OPEN SYSTEMS Accounting Software Inventory

image

Contents

1. 12 Click Next The Sort Order box appears Query Wizard Sort Specify how you want your data sorted If you don t want to sort the data click Next Ascending 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 L Lx File Edit View Format Table Records Window Help Hew eN 6 2129 t R 3 Query 1 OSAS Data 0 BATCH li ORDER_NUM TRANSACTION T INVUICE NUMHINVOICE DATCUSTOMER ID SUBTOTAL SALES TAX TOTAL COST gt 000002 00000003 3 24889101 1995 1221 GREOOT 6759 67 0 3893 03 21000002 00000004 3 24889102 19961221 105001 727327 0 3664 63 121000002 00000005 3 24889103 19951221 SUNOOT 4318 74 0 279329 121000002 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 ACEOD 97395 0 1030 85 11 000002 00000010 4 24889108 19961221 105001 4821455 0 27910 M s RecoaTi Select View t
2. Preview Mow Beck Cancel Inventory Report Applet User s Manual 1 21 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 2 Where is your external data stored Get Data Data fields have been retrieved Cancel Back Finish 9 Click Next gt 1 22 Inventory 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 LS K LI Construct your PivotTable by dragging the field buttons on the right to the diagram on the left ORDERED ORDERN rreren 5 Backorg NVOICE ENTRY NI dd INVOICE INIT CO USTOME 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
3. 357 6300 357 5300 26 200200 11 12 99 blank 227 7900 223 0300 22303001 223 0300 27 3 4 99 blank 227 7900 223 0300 22303001 223 0300 28 12 9 89 blank 227 7900 227 5300 227 5300 227 5300 29 12 9 89 blank 227 7900 223 0300 223 0300 223 0300 30 200300 C 11 12 99 blank 429 2500 419 9100 41991001 419 9100 31 MD 3 4 98 blank 429 2500 419 9100 419 9100 419 9100 32 MN 12 9 99 blank 429 2500 4239500 4299500 429 9500 88 TXOO 12 9 99 blank 429 2500 419 9125 419 9125 4199125 34 200400 CAD 11 12 98 blank 125 0000 125 2300 125 2300 125 2300 58 MDI 3 4 99 blank 124 5000 125 2300 125 2300 125 2300 36 MNI 12 9 99 blank 125 0000 127 4000 127 4000 127 4000 37 TXO 12 9 99 blank 124 5000 125 2300 126 2300 125 2300 38 200500 C 11 12 99 blank 46 2500 45 5900 45 5900 45 5900 M 3 4 99 blank 46 2500 45 5900 45 5900 45 5900 40 M 12 9 99 blank 50 0000 47 5000 47 5000 47 5000 41 TXO 12 9 99 blank 46 2500 45 5900 45 5900 45 5900 I4 4 bI Sheet 14 Ready I NOM tT 3 10 Inventory Report Applet User s Manual IN Location Pricing File Name INLOCPRC XLS Description The IN Location Pricing PivotTable uses the data in the Location Pricing INLPx file to display the customer level specific pricing for each item in each location The data is sorted by Item ID Location ID Customer Level Adjustment Type and Adjustment Base but you can easily change the sort order Act
4. Lot Number Transaction Qty Ext Cost 8 700500 0002 120195 48 0000 5184 00 9 120200 130 0000 38025 00 10 121435 50 0000 5625 00 11 800001 MN0001 27400714 2400 0000 264 00 12 T gt D001 27400715 2400 0000 254 00 13 800002 MN0001 27402201 1200 0000 120 00 14 27402202 1200 0000 120 00 15 27402203 1200 0000 120 00 15 71000 27402201 1200 0000 120 00 17 27402202 1200 0000 120 00 18 27402203 1200 0000 120 00 19 810001 MNODO1 1001 20 0000 141 00 20 1002 20 0000 141 00 21 1003 20 0000 141 00 22 TXDO001 1001 20 0000 141 00 23 1002 20 0000 141 00 24 1003 20 0000 141 00 25 810002 MNODO1 1011 00 180 00 25 1012 180 00 27 1013 180 00 28 0001 1011 180 00 E 1012 180 00 30 1013 180 00 31 810003 1 1021 201 30 32 1022 201 30 33 1023 201 30 34 TXDO01 1021 268 40 35 1022 268 40 36 1023 268 40 37 811001 0001 1201 324 45 38 TXO001 4202 216 30 Sheet Ready 3 16 Inventory Report Applet User s Manual IN Physical Counts File Name INPHYSCT XLS Description The IN Physical Counts PivotTable uses the data in the Physical Count Detail file to display frozen and counted quantities and extended cost information for the physical counts you have in process The report is sorted by Item ID Location ID and Bin Number but you can easily change the sort order or include the Batch ID or Product Line in the sort Active Fields Default Field Type Field Page
5. Year Period Source Application ID or Transaction Date You can use this PivotTable to review and analyze your purchases sales and other inventory transactions and to look for trends in inventory movements Active Fields Default Field Type Field Page Transaction Type Year Period Source Application ID Transaction Date Row Item ID Location ID Column Transaction Quantity Extended Cost Extended Price Inventory Report Applet User s Manual 3 7 History Detail IN PivotTables IN History Detail PivotTable Sample XX Microsoft Excel Inhist DI x File Edit View Insert Format Tools Data Window Help 81 x 58 o BS z 60 1 aria B ZU _ 9 Pvotrable BH t fa im Trans Tini bel Ext Cost Ext Price 459 0000 159395 08 102677 67 472 0000 16391984 85131 70 6206 0000 2136558 81 723030 7 1312 0000 455947 85 247051 33 12 0000 10825 32 8570 31 80 0000 69964 80 38116 27 1925 0000 1718277 61 801394 40 121 0000 105821 7 200100 42 0000 15440 46 130 0000 47791 90 1742 0000 647758 27 865 0000 317999 95 200200 42 0000 9367 26 130 0000 28993 90 1742 0000 390105 76 865 0000 19292095 200300 42 0000 17636 22 130 0000 54588 30 1742 0000 734019 45 865 0000 363222 86 200400 42 0000 5259 66 130 0000 16279 90 17
6. 00000 000070 100002 00000 0000011 TODON 1097106 000 217909 000 000 0222911050 96 1299 000070 00000 10000 00000 0000796 TOOONW 056 62 000 000 070 000 000 000 507305 00070 00000 00000 00000 000059 1000 009182 000 erosiz _ 000 000 0529 000 977228 000070 000070 000006 000070 000052 TODON OSE 22 000 000 000 000 000 000 00719 000070 00000 00000 00000 0000752 LODONIA 000 000 000 000 000 000 000 00000 00000 00000 00000 00000 100092 ove SZ 000 2200582 000 000 0028552 000 070 00000 00000 000002 100000 00000 10001 000 000 000 000 000 000 OC 688 00000 000070 20000 00000 0000 LODONIA 032 EE 000 000 000 000 9284 000 000 000070 00000 100001 00000 00000 10001 000 000 000 000 95984 000 000 00000 00000 00000 00000 00000 LODUNA 009002 12 000 000 000 000 06599 000 000 00000 00000 0000001 100000 00000 1000 1 74 000 000 000 000 069 000 000 00000 000070 100001 00000 00000 005002 000 000 000 000 ozi 000 000 000070 00000 100001 00000 000070 10001 000 000 000 000 05292 000 000 00000 00000 00000 00000 00000 LODUNA oorooz ZL 000 000 070 000 6 000 070 00000 00000 0000001 100000 00070 10005 000 000 000 000 eleely 000 000 00000 00000 100001 00000 00000 ___ ooeooz Sr 000 000 000 000 0 0 22 000 000 000070 00000 100001 00000 000070 10001 v 000 000 000 000 o oez 000 000 00000 00000 200007 00000 00000 LODUNA 002002 EL 000 000 000 000 059 95 000 070 00000 00000 0000001 100000 00000 10005
7. 1 6 description 3 23 INREQUIS XLS description 3 25 INREQUIS XLS XLS data files 1 7 INRQxxx file description 1 7 INSERHST XLS data files 1 7 description 3 27 INSERNUM XLS data files 1 6 description 3 29 INSHxxx file description 1 7 installing the applet 2 1 INSUMHST XLS data files 1 8 description 3 31 INTRANS XLS data files 1 7 description 3 33 INTRxxx file description 1 7 INUNITPR XLS data files 1 5 description 3 35 INUPxxx file description 1 5 INVENDOR XLS data files 1 6 description 3 37 Inventory data files 1 5 description 1 5 installing 2 1 system requirements 2 1 INVIxxx file description 1 6 Inventory Report Applet User s Manual M Microsoft Excel 1 11 PivotTable layout 1 23 PivotTable sample 1 25 PivotTable wizard 1 19 selecting a data source 1 20 Microsoft Query 1 11 adding tables 1 17 building a query 1 12 Filtering data 1 15 joining tables 1 18 Selecting a table 1 15 Sorting data 1 16 N No Shadow Dictionary Consistency Check Microsoft Query 1 14 ODBC Kit definition 1 3 OSAS general information 1 3 P PivotTable adding a calculated field 1 25 changing column data 1 32 changing data sort 1 99 changing field properties 1 28 changing selection fields 1 30 definition 1 9 moving fields 1 30 PivotTable Wizard Choosing a data source 1 20 getting the data 1 20 laying out the table 1 23 returning the data 1 24 PivotTables
8. 1 9 Creating Microsoft Excel PivotTables 1 11 Installation 2 1 IN PivotTables IN Bin Analysis 3 3 IN COGS Adjustments 3 5 IN History Detail 3 7 IN Location Detail 3 9 IN Location Pricing 3 11 IN Lot Detail 3 13 IN Lot History 3 15 IN Physical Counts 3 17 IN Price Structures 3 19 IN Quantity Detail 3 21 IN Quantity Totals 3 23 IN Requisitions 3 25 IN Serialized History 3 27 Inventory Report Applet User s Manual iii Serial Numbers IN Summary History IN Transactions IN Unit Pricing IN Vendor Information 3 29 3 31 3 33 3 35 3 37 Inventory Report Applet User s Manual Introduction General Information 1 Inventory Data Files 1 Introduction to PivotTables 1 Creating Microsoft Excel PivotTables 1 Inventory 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 Wi
9. 14 selecting a data source 1 14 F Fast Connect Microsoft Query 1 14 IN Bin Analysis PivotTable description 3 3 files used in 1 6 sample report 3 4 screen 3 4 IN COGS Adjustments PivotTable description 3 5 files used in 1 7 sample report 3 6 screen 3 6 IN Detail History PivotTable files used in 1 7 IN History Detail PivotTable description 3 7 sample report 3 8 screen 3 8 Inventory Report Applet User s Manual Index IN Location Detail PivotTable description 3 9 files used in 1 5 sample report 3 10 screen 3 10 IN Location Pricing PivotTable description 3 11 files used in 1 5 sample report 3 12 screen 3 12 IN Lot Detail PivotTable description 3 13 files used 1 6 sample report 3 14 screen 3 14 IN Lot History PivotTable description 3 15 sample report 3 16 screen 3 16 IN Physical Counts PivotTable description 3 17 files used in 1 7 sample report 3 18 screen 3 18 IN Price Structures PivotTable description 3 19 files used in 1 6 sample report 3 20 screen 3 20 IN Quantity Detail PivotTable description 3 21 files used in 1 6 sample report 3 22 screen 3 22 IX 1 Index IN Quantity Totals PivotTable description 3 23 files used inj 1 6 sample report 2 24 screen 3 24 IN Requisitions PivotTable description 3 25 files used in 1 7 sample report 3 26 screen 3 26 IN Serial Numbers PivotTable description 3 29 files used 1 6 sample re
10. 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 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 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 Inventory Report Applet User s Manual 1 33 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 34 Inventory Report Applet User s Manual Installation You can put the Inventory ODBC Report Applet on your system by installing it through Resource M
11. 23 700300 0002 13 90 00 90 0000 187 20 24 700400 0002 13 4961 00 4961 0000 545 71 25 700500 0002 25 8 00 8 0000 144 00 26 700998 0002 17 36 00 36 0000 94 68 27 700999 0002 17 15694 00 15694 0000 1255 52 28 701 0002 19 34 00 34 0000 2951 74 29 Grand Total 24288 00 24288 0000 69478 20 141 i riti gt bI Sheet Ready 3 18 Inventory Report Applet User s Manual IN Price Structures File Name INPRCSTR XLS Description The IN Price Structures PivotTable uses the data in the Price Structures INPSx file to display the pricing matrices you have set up The report is sorted by Price ID Description Adjustment Type and Adjustment Base but you can easily change the sort order or include the Customer Level in the sort Active Fields Default Field Type Field Page Customer Level Row Price ID Description Adjustment Type Adjustment Base Column Adjustment Amount Inventory Report Applet User s Manual 3 19 IN Price Structures IN PivotTables IN Price Structures PivotTable Sample 188 Ele Edit Insert Format Tools Data Window Help laxi DS M 8mtv imes c 5i ibd 4 0 8 aria rio B ZU
12. 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 Inventory 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 Inventory Report Applet User s Manual 1 9 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 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 Inventory Report Applet User s Manual 1 11 Creating Microsoft Excel PivotTables In
13. Configuration m Advanced gt gt 443 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 60osas gt c Data progFA Proarm print l progGL 1 progSD prog P proglN JRwdata prog R Sample progBK L L sort progBR L progPA mM Ll progPM Files of type Fies tpm Cancel When you select the file the final dialog appears Microsoft Excel PivotTable was changed during Refresh Data operation When you click on OK the PivotTable is updated with your accounting data Inventory Report Applet User s Manual Installation Report Applet PivotTables Use the descriptions of the PivotTables in chapter 3 to work with your accounting data 2 4 Inventory Report Applet User s Manual PivotTables IN Bin Analysis IN COGS Adjustments IN History Detail IN Location Detail IN Location Pricing IN Lot Detail IN Lot History IN Physical Counts IN Price Structures IN Quantity Detail IN Quantity Totals IN Requisitions IN Serialized History IN Serial Numbers IN Summary History IN Transactions IN Unit Pricing IN Vendor Information Inventory Report Applet User s Manual 3 3 3 5 3 7 3 9 3 11 3 13 3 15 3 17 3 19 3 21 3 23 3 25 3 27 3 29 3 31 3 33
14. IN Bin Analysis 3 3 IN COGS Adjustments 3 5 IN History Detail 3 7 IN Location Detail 3 9 IN Location Pricing 9 11 IX 3 Index R IN Lot Detail 3 13 IN Lot History 3 15 IN Physical Counts 3 17 IN Price Structures 3 19 IN Quantity Detail 3 21 IN Quantity Totals 3 23 IN Requisitions 3 25 IN Serial Numbers 2 29 IN Serialized History 9 27 IN Summary History 3 3 1 IN Transactions 3 33 IN Unit Pricing 3 35 IN Vendor Information 9 97 Report Applet requirements for 1 11 report applet installation 2 1 report applets 5 definition 1 3 spreadsheets IN Bin Analysis 3 3 N COGS Adjustments 3 5 N History Detail 3 7 N Location Detail 3 9 N Location Pricing 3 11 N Lot Detail 3 13 N Lot History 3 15 Physical Counts 9 17 N Price Structures 3 19 N Quantity Detail 3 21 N Quantity Totals 3 23 Requisitions 3 25 N Serial Numbers 3 29 Serialized History 9 27 Summary History 3 31 N Transactions 3 33 N Unit Pricing 3 35 IN Vendor Information 9 97 I I I I I I I I I I I I I I I I system requirements 2 1 IX 4 Inventory Report Applet User s Manual
15. Insert Format Tools Data Window Help 1 Dc EH S6RY 5 4 6 aria F B ZU 859 o 5 A j t A6 Status D E R G H zi 1 Location Detail 2 Item Status g Active 4 D Discontinued 5 Obsolete 6 Status S Superceded F 8 Data 9 Item ID Location ID Last Sale Date Last Purchase Date Standard Cost Average Cost Base Cost Last Cost 10 100 CA0001 12 14 99 blank 0 0000 348 0582 3435500 348 0582 11 MDOO01 11 2 99 blank 0 0000 348 0582 3435500 348 0582 12 MNOO01 12 10 99 12 21 99 0 0000 343 5500 343 5500 343 5500 g TX0001 11 15 99 blank 0 0000 340 1100 343 5500 340 1100 14 150 CA0001 12 14 99 blank 0 0000 902 1100 907 5300 902 1100 15 MDOO01 11 2 99 blank 0 0000 874 5600 907 5300 874 5600 15 0001 11 16 99 12 21 99 0 0000 907 5300 907 5300 907 5300 17 TX0001 11 15 99 blank 0 0000 874 5600 907 5300 874 5600 18 200 001 11 12 99 blank 0 0000 0 0000 0 0000 0 0000 19 MD0001 3 4 99 blank 0 0000 0 0000 0 0000 0 0000 20 MNODO1 12 9 99 blank 0 0000 0 0000 0 0000 0 0000 21 TX0001 12 9 99 blank 0 0000 0 0000 0 0000 0 0000 22 200100 001 11 12 99 blank 379 2500 367 5300 357 6300 357 5300 23 MD0001 3 4 99 blank 379 2500 367 5300 357 6300 367 6300 24 MNODO1 12 9 89 blank 379 2500 379 4400 379 4400 379 4400 25 TX0001 12 9 99 blank 379 2500 367 5300
16. 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 36 Sum of SHIPPED SELL 3 3 4 gt KW Sheet1 Sheet2 Sheets 1 1 gt Inventory Report Applet User s Manual 1 29 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 selec
17. gt t 11 Customer Level B D E E 1 IN Price Structures 2 Adjustment Adjustment Base 8 0 Dollar No Base 4 1 Percentage 5 Standard Cost 8 B Base Cost A Average Price 7 8 L List Price Minimum Price 10 C Calculated Price 11 Customer Level H 12 13 Adjustment Amt 14 Price ID Descr Adjustment Type Adjustment Base Total 15 APPL Ace Builders Contract 1 B 10 0000 16 Appliance Price Breaks 0 C 0 0000 17 Jobber Customer 1 10 0000 18 Retail Customer Pricing _ 0 C 0 0000 48 Wholesale Customer 1 B 10 0000 20 BUILD Ace Builders Contract 1 B 5 0000 21 Building Supplies C 0 0000 22 Jobber Customer 1 C 5 0000 23 Retail Customer Pricing _ 0 C 0 0000 24 Wholesale Customer 1 B 5 0000 25 MATRL Ace Builders Contract 1 B 10 0000 26 Jobber Customer 1 C 10 0000 27 Materials Price Breaks 0 C 0 0000 28 Retail Customer Pricing 0 C 0 0000 29 Wholesale Customer 1 B 10 0000 30 Grand Total 25 0000 141415 Sheeti TERI 1 Ready INUM 3 20 Inventory Report Applet User s Manual IN Quantity Detail File Name INQTYDET XLS Description The IN Quantity Detail PivotTable uses the data in the Quantity Detail INQLx file to display the quantities and costs of the items you have on hand The report is sorted by Item ID Lot Number and Initial Date but you can easily change t
18. in this report into the respective areas 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 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 Inventory Report Applet User s Manual 1 23 Creating Microsoft Excel PivotTables Introduction 1 24 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 Star d cif Sum of SHIPPED Q ENTRY NE Am of UNIT COST Invoice ENTRY_N E E invorce UNIT_co custome UNIT_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 gt Click Finish to create your PivotTable Cancel Options lt Back Next gt Es 12 Thelast step lets you create the PivotTable either in the existing worksheet
19. or in a different worksheet Accept the given options and click Finish Inventory Report Applet User s Manual Introduction Creating Microsoft Excel PivotTables The PivotTable is displayed File Edit View Insert Format Tools Data Window DG SRY 3 m e oc PivatTable e SEH INVOICE INVOICE ENTRY N Data 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 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 Pi Sheet1 Shest2 Sheet3 4 Highlight rows and columns to shift them around To 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 Inventory Report Applet User s Manual 1 25 Creating Microsoft Excel PivotTables Introduction The Inse
20. 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 Wizard click Get Data Inventory Report Applet User s Manual Introduction Creating Microsoft Excel PivotTables The Choose Data Source box from Microsoft Query appears Choose Data Source 2 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 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 Cancel 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
21. 00 10 0000 77 0000 TxO00 Furnace 367 6300 3 0000 0 0000 7 0000 56 5000 200200 CAD001 Water Heater 223 0300 8 0000 0 0000 0 0000 3 0000 0000 Water Heater 223 0300 8 0000 0 0000 0 0000 0 0000 MNOOO Water Heater 227 5300 8 0000 0 0000 7 0000 67 0000 TXOOO Water Heater 223 0300 3 0000 0 0000 1 0000 12 9000 200300 CADDOT Air Conditioner 419 9100 0 0000 0 0000 0 0000 0 0000 0000 Air Conditioner 419 9100 0 0000 0 0000 1 0000 9 0000 MNOOO Air Conditioner 429 9500 8 0000 0 0000 10 0000 77 0000 TxO00 Air Conditioner 419 9125 3 0000 0 0000 7 0000 56 5000 200400 CAD001 Water Softener 125 2300 8 0000 0 0000 0 0000 3 0000 0001 Water Softener 125 2300 8 0000 0 0000 0 0000 0 0000 MNOOO Water Softener 127 4000 8 0000 0 0000 7 0000 67 0000 TXDOO Water Softener 125 2300 3 0000 0 0000 1 0000 12 9000 200500 CAD001 Sump Pump 45 5900 8 0000 0 0000 0 0000 3 0000 0000 Sump Pump 45 5900 8 0000 0 0000 0 0000 0 0000 MNOOO Sump Pump 47 5000 8 0000 0 0000 7 0000 67 0000 000 Sump Pump 45 5900 3 0000 0 0000 1 0000 12 9000 200600 CAD001 Hurnidifier 71 8700 8 0000 0 0000 0 0000 3 0000 0000 Humidifier 71 8700 8 0000 0 0000 0 0000 0 0000 MNOOO Humidifier 75 1500 8 0000 0 0000 7 0000 67 0000 TxO00 Humidifier 71 8555 3 0000 0 0000 1 0000 12 9000 0000 Exterior Panels 1298 2400 51 0000 5 0000 0 0000 0 0000 MNOOO Exterior Panels 1342 8700 222 0000 10 0000 0 0000 0 4000 TxO00 Exterior Panels 1274 7641 266 0000 0 0000 0 0000 0 6000 canana Interior Door 1a 8000 1
22. 000 8 MN0001 00001510 12 15 98 0 0000 10 00001532 12 15 39 18 0000 11 00001552 12 15 99 0 0000 12 10001 00001533 12 15 99 6 0000 13 150 CAD001 blank 0 0000 14 00001 blank 0 0000 15 MN0001 00001508 PKG 12 15 39 15 0000 15 TXD00 00001509 12 15 98 7 0000 17 200 CAD001 blank 0 0000 18 MD0001 blank 0 0000 18 MNODO1 blank 0 0000 20 TXD001 blank 0 0000 21 200100 CAD001 blank 0 0000 22 MDODO1 blank 0 0000 93 MNODO1 00001521 12 15 99 8 0000 24 TXD001 00001522 EA 12 15 98 3 0000 25 200200 CADDOT1 blank 0 0000 25 00001 blank 0 0000 27 MNODO1 00001523 12 15 99 8 0000 28 TXD001 00001524 EA 12 15 98 3 0000 29 200300 CADOD1 blank 0 0000 30 00001 blank 0 0000 31 0001 00001525 12 15 99 8 0000 32 0001 00001526 EA 12 15 99 3 0000 33 200400 CADOD1 blank 0 0000 34 00001 blank 0 0000 an MNDODO1 00001527 EA 12 15 99 8 0000 36 Tx0001 00001528 EA 12 15 99 3 0000 37 200500 CADOD1 blank 0 0000 38 00001 blank 0 0000 39 MNOO01 00001529 EA 12 15 99 8 0000 141 LEE IAE pue Ready 3 4 Inventory Report Applet User s Manual IN COGS Adjustments File Name INCOGADJ XLS Description The IN COGS Adjustments PivotTable uses the data in the COGS Adjustment Journal INCJx file to display the details of the GL journal entries required to adjust the inventory value The data is sorted by Sequence Number G
23. 000 0 0000 0 0000 0 0000 7 50 32 700115 15 0000 0 0000 0 0000 0 0000 186 45 33 700117 4 0000 0 0000 0 0000 0 0000 21 64 34 700119 15 0000 0 0000 0 0000 0 0000 54 90 35 700120 582 0000 0 0000 0 0000 0 0000 354 64 361700130 632 0000 0 0000 0 0000 0 0000 271 76 37 700199 25 0000 0 0000 0 0000 0 0000 1551 75 38 700200 91 0000 0 0000 0 0000 0 0000 185 64 39 700300 90 0000 0 0000 0 0000 0 0000 187 20 40 700400 4961 0000 0 0000 0 0000 0 0000 545 71 41 700500 8 0000 0 0000 0 0000 0 0000 18 00 42 120195 8 0000 0 0000 0 0000 0 0000 18 00 43 700998 36 0000 0 0000 0 0000 0 0000 94 68 4 700999 15634 0000 0 0000 0 0000 0 0000 1255 52 I4 4b Sheet1 14 3 24 Inventory Report Applet User s Manual IN Requisitions File Name INREQUIS XLS Description The IN Requisitions PivotTable uses the data in the Requisitions file to display information about the suggested order quantities based on your reorder method The report is sorted by Item ID Location ID and Description but you can easily change the sort order or add Product Line to the sort You can use this report to review and analyze your order quantities order point and safety stock settings Active Fields Default Field Type Field Page Product Line Row Item ID Location ID Description Column Last Cost On Hand Quantity On Order Quantity Safety Stock Forecasted Us
24. 000 000 000 000 09 96 000 000 00000 00000 100001 00000 00000 oorooz 000 000 000 6r zesci 000 000 000070 00000 100001 00000 000070 10001 0L 000 000 000 erzeszl 000 000 00000 00000 00000 00000 00000 LODONIA 00z 6 000 000 070 000 000 000 00000 00070 00000 00000 000051 10008 05178 000 9727096 1000 000 619025 1000 0523256 000070 000070 0000001 000070 0000 222 LODONIA 00172 SAPS SIU 58185 1592 1502 502 585 4509 1502 eseyoing AO peisnipy 0 unie seres MO sees MO Yong Mo eseuoing arueneao 9 eed 5 ii 2 NI W H 5 2 prece gt HSE V e Tle elem tx MH te 49 5001 Pwy Inventory Report Applet User s Manual 3 32 File Name INTRANS XLS Description IN Transactions The IN Transactions PivotTable uses the data in the Transactions INTRx file to display information about the unposted inventory transactions you have entered The report is sorted by Item ID Location ID and Transaction Date but you can easily change the sort order or add Transaction Type Period or Year to the sort Active Fields Default Field Type Page Row Column Inventory Report Applet Us
25. 00600 71 8555 215 57 75 1500 501 20 i 147 0055 1617 05 34 250 7 5 98 121 0000 1269 1000 153561 10 25 7 5 98 93 0000 1269 1000 118026 30 36 1 79 99 120 0000 1274 4423 152933 08 37 1 15 99 14 0000 1298 2400 18175 36 38 1 15 99 61 0000 1298 2400 79192 64 39 3 15 99 53 0000 1274 7641 67562 50 40 6 20 99 48 0000 1287 5500 61807 20 41 10 3 99 20 0000 1342 8700 26857 40 42 12 9 99 40 0000 1342 8700 53714 80 43 12 11 99 10 0000 1342 8700 13428 70 Sheett mc cw gt 1 Ready 2 3 22 Inventory Report Applet User s Manual IN PivotTables IN Quantity Totals File Name INQTYTOT XLS Description The IN Quantity Totals PivotTable uses the data in the IN Quantity Totals file to display quantity and cost information for all of your items in the location you choose The report is sorted by Item ID and Lot Number but you can easily change the sort order or include the Location ID in the sort Active Fields Default Field Type Field Page Location ID Row Item ID Lot Number Column On Hand Quantity Committed Quantity In Use Quantity On Order Quantity Total Cost Inventory Report Applet User s Manual 3 23 IN Quantity Totals IN PivotTables IN Quantity Totals PivotTable Sample bel mue Edit View In
26. 11 2 1000 2 2500 21 21 4 5000 214217 4 5000 214218 4 5000 214219 4 5000 1214220 4 5000 214221 4 5000 214222 4 5000 214223 4 5000 214224 4 5000 214225 4 5000 214226 4 5000 214227 4 5000 214228 4 5000 214229 4 5000 214230 4 5000 214231 4 5000 214232 4 5000 214233 4 5000 214234 4 5000 214235 4 5000 214236 4 5000 214237 4 5000 214238 4 5000 214239 4 5000 214240 4 5000 214241 4 5000 214242 4 5000 214243 4 5000 214244 4 5000 214245 4 5000 jal 4 TPI Sheet a wate Ready 2 3 28 Inventory Report Applet User s Manual Serial Numbers File Name INSERNUM XLS Description The IN Serial Numbers PivotTable uses the data in the Serialized Detail INSNx file to display information about the serialized items you have on file The report is sorted by Item ID and Serial Number but you can easily change the sort order or add Serial Item Status to the sort Active Fields Default Field Type Field Page Serial Item Status Row Item ID Serial Number Column Serialized Item Cost Serialized Item Price Inventory Report Applet User s Manual 3 29 IN Serial Numbers IN PivotTables IN Serial Numbers PivotTable Sample XX Microsoft Excel Insernum DI x File Edit View Insert Format Tools Data Window Help 1 sg er gt 5121 ow a
27. 14 Inventory 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 5 CODE SOTB lt lt 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 include rows where C nd Gr C And Gr C And 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 Inventory Report Applet User s Manual 1 15 Creating Microsoft Excel PivotTables Introduction
28. 188 File Edit view Insert Format Tools Data Window Help IN PivotTables er amp lt 7 z 2 lA aria 10 Bivotrable i gu ew Average Price List Price Minimum Price Base Price 1973 3306 2143 920 0 000 5236 5939 5902 360 9892 7203 11039 960 96 00 445 0000 1999 80 302 7500 1399 80 506 8500 2100 00 155 7500 599 80 50 5000 279 96 89 9900 399 96 7890 3919 10983 96 210 7826 239 96 2179 8333 2400 36 6434 7177 6899 96 1606 1710 1759 2000 1289 4824 1890 0000 4671 4270 6591 4000 4599 6450 6629 0000 840 8540 1389 5600 766 9564 95979 8460 4981 3384 0 0000 0 0000 7001111 7001112 1608 6800 116800 0000 7861 2500 148 2800 70 8000 3 36 Inventory Report Applet User s Manual Vendor Information File Name INVENDOR XLS Description The IN Vendor Information PivotTable uses the data in the Vendor Information INVIx file to display data about the vendors from whom you purchase your items The report is sorted by Item ID and Vendor ID but you can easily change the sort order or add the Location ID to the sort Active Fie
29. 2 Inventory Report Applet User s Manual IN Lot Detail File Name INLOTDET XLS Description The IN Lot Detail PivotTable uses the data in the Lot Detail INLTx file to display quantity information for the lot numbers you choose The report is sorted by Item ID but you can easily change the sort order or include the Lot Number or Lot Status in the sort Active Fields Default Field Type Field Page Lot Number Lot Status Row Item ID Column Received Quantity Returned Quantity Sold Quantity Inventory Report Applet User s Manual 3 13 IN Lot Detail IN PivotTables IN Lot Detail PivotTable Sample File Edit View Insert Format Tools Data Window Help 1 Ds Mem T amp 45 aria 10 B zU E m A A Bivotrable amp T 8 4 Lot Number A B D D E G H fal 1 Lot Detail n 2 Lot Status 3 A Active 4 JLot Number 5 Sold 5 Lot Status All 7 Data 8 tem ID Received Qty Returned Qty Sold Qty 9 700500 178 0000 0 0000 0 0000 10 800001 4800 0000 0 0000 0 0000 11 800002 3600 0000 0 0000 0 0000 12 810001 480 0000 0 0000 0 0000 13 810002 500 0000 0 0000 0 0000 14 810003 840 0000 0 0000 0 0000 15 811001 420 0000 0 0001 0 0000 16 811002 760 0000 0 0000 0 0000 17 81100
30. 3 35 3 37 3 1 IN Bin Analysis File Name INBINS XLS Description The IN Bin Analysis PivotTable uses the data in the Bin Number INBNx file to display the last physical count and quantity information for the bin you choose The data is sorted by Item ID Location ID Last Count Tag Last Count Unit of Measure and Last Count Date but you can easily change the sort order or include the Bin Number in the sort You can use this PivotTable to review and analyze bin usage in your warehouse Active Fields Default Field Type Field Page Bin Number Row Item ID Location ID Last Count Tag Number Last Count Unit of Measure Last Count Date Column Total Counted Quantity Inventory Report Applet User s Manual 3 3 IN Bin Analysis IN PivotTables IN Bin Analysis PivotTable Sample XX Microsoft Excel Inbins DI xi File Edit View Insert Format Tools Data Window Help lal 587 aria 0 BzuEsz Bivotrable 4 Qa rn A3 Y Bin No A B F 5 1 Bin Analysis 2 3 Bin All 4 Last Count Qty B Item ID Location ID _ Last Count Tag Last Count UOM Last Count Date Total 7 100 001 00001531 12 15 99 7 0000 8 MD0001 blank 0 0
31. 3 840 0000 0 0000 0 0000 18 812001 480 0000 0 0000 0 0000 19 812002 1000 0000 0 0000 0 0000 20 812003 656 0000 0 0000 0 0000 21 Grand Total 14654 0000 0 0000 0 0000 22 Id 4 WA Sheett Ready 3 14 Inventory Report Applet User s Manual IN Lot History File Name INLOTHST XLS Description The IN Lot History PivotTable uses the data in the Lot History INLHXx file to display summary historical quantity and cost information for lotted items The report is sorted by Item ID Location ID and Lot Number but you can easily change the sort order or include the Year Period or Transaction Type in the sort Active Fields Default Field Type Field Page Year Period Transaction Type Row Item ID Location ID Lot Number Column Transaction Quantity Extended Cost Inventory Report Applet User s Manual 3 15 IN Lot History IN PivotTables IN Lot History PivotTable Sample BS Ele Edit view Insert Format Tools Data Window Help la x DG o T 946 3 ZU S8 95 PivotTable T Year Pd B D E 1 IN Lot History 2 3 YearPd 199807 4 Transaction All Data 7 ID Location ID
32. 42 0000 219002 23 865 0000 108323 95 200500 42 0000 130 0000 1742 0000 865 0000 200600 42 0000 3018 54 130 0000 9343 10 1742 0000 127267 49 0 865 0000 pee 0 00 4 31k M Sheett Ready 3 8 Inventory Report Applet User s Manual IN Location Detail File Name INLOCDET XLS Description The IN Location Detail PivotTable uses the data in the Inventory Location Detail INLDx file to display cost information for each item and location The report is sorted by Item ID Location ID Last Sales Date and Last Purchase Date but you can easily change the sort order or include the Item Status in the sort Use this PivotTable to analyze the costs of your items and to compare the standard and base costs to actual costs Active Fields Default Field Type Field Page Item Status Row Item ID Location ID Last Sale Date Last Purchase Date Column Standard Cost Average Cost Base Cost Last Cost Inventory Report Applet User s Manual 3 9 IN Location Detail IN PivotTables IN Location Detail PivotTable Sample Eie Edit view
33. 8 nnnn 2 nnnn nnnnn znnnnl 141 1 Ready 7 3 26 Inventory Report Applet User s Manual Serialized History File Name INSERHST XLS Description The IN Serialized History PivotTable uses the data in the Serialized History INSHx file to display information about the serialized items that you ve purchased created or used The report is sorted by Item ID Location ID and Serial Number but you can easily change the sort order or add Year Period Transaction Type or Source Reference ID to the sort You can use this report to review and analyze the purchases sales and returns of your serialized inventory Active Fields Default Field Type Field Page Year Period Transaction Type Source Reference ID Row Item ID Location ID Serial Number Column Total Cost or Price Inventory Report Applet User s Manual 3 27 IN Serialized History IN PivotTables IN Serialized History PivotTable Sample Ele Edi Insert Format Tools Data Window Help xi amp amp o amp A 21 o Aria gt 2 Year Pd E Cost or Price 8 Item ID Location ID Serial Number Total 9 00057300000000000000 0 0000 10 700500 MNODO2 14000 2 2500
34. BER 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 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 1 Sum of SHIPPED 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 151 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 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 p PIN Sheet1 Sheet Sheet 1
35. Batch ID Product Line Row Item ID Location ID Bin Number Column Frozen Quantity Counted Quantity Frozen Extended Cost Inventory Report Applet User s Manual 3 17 IN Physical Counts IN PivotTables IN Physical Counts PivotTable Sample Eie Edit view Insert Format Tools Data Window Help amp o 5 zi da 9 6 zio 5 84 votrabe lt Batch ID A B D E G 5 1 Physical Counts 2 3 Batch ID All gt 4 Product Line 5 Data 7 ID Location ID Number Frozen Qty Counted Qty Frozen Ext Cost 700 0001 1 3 00 3 0000 604 74 9 TX0001 16 3 00 3 0000 604 74 10 700100 0002 15 170 00 170 0000 18684 70 11 700110 0002 85 00 85 0000 4884 95 12 700111 0002 1 525 00 525 0000 36781 50 13 7001111 0002 11 1200 00 1200 0000 72 00 14 7001112 0002 11 12 00 12 0000 22 44 15 700113 0002 7 3 00 3 0000 7 50 16 700115 0002 8 15 00 15 0000 185 45 17 700117 0002 9 4 00 4 0000 21 64 18 700119 MNOOO2 10 15 00 15 0000 54 90 18 700120 MNOOO2 1 12 682 00 682 0000 354 64 20 700130 0002 12 532 00 532 0000 271 76 21 700199 0002 DRY 25 00 25 0000 1551 75 22 700200 0002 13 91 00 91 0000 185 64
36. L Account Code and Transaction Date but you can easily change the sort order or include the Item ID Location ID Adjustment Type Period or Year in the sort Active Fields Default Field Type Field Page Item ID Location ID Adjustment Type Period Year Row Sequence Number GL Account Code Transaction Date Column Total Transaction Amount Inventory Report Applet User s Manual 3 5 IN COGS Agjustments IN PivotTables IN COGS Adjustments PivotTable Sample Biel ES File Edit view Insert Format Tools Data Window Help 18 Dc 587 o c amp e wirfi MOB 10 aria rio B ZU B 92 Bivotrable D gt Adjustment Types C Cost of Goods Sold P Purchase Price Transaction Amt Sequence No Transaction Date 2 13 000001 Total 22 44 14 22 Grand Total Sheet 3 6 Inventory Report Applet User s Manual IN History Detail File Name INHIST XLS Description The IN History Detail PivotTable uses the data in the IN Detail History INHIx file to display details about the inventory transactions that you have entered The history includes transactions that occurred in other applications such as Accounts Payable or Sales Order The information is sorted by Item ID and Location ID but you can change the sort easily to include Transaction Type
37. OPEN SYSTEMS Accounting Software Inventory ODBC Report Applet User s Manual PN 2210 INO60 1998 Open Systems Holdings Corp rights reserved Document Number 22101 0600 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 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 1 3 Inventory Data Files 1 5 Introduction to PivotTables
38. Total 59 59 5979 2882 12473 1185 379074 2907 31 a lt gt MN Sheet Sheett Sheet Sheets J 14 Ready esf 7 i 1 32 Inventory 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 8 File Edit View Insert Format Tools Data Window Help 18 z er gt z A Z 6 so 44 o A Bivotrable B5 INVOICE NUMBER A B G H F 1 All _ Data INVOICE DATE INVOICE NUMBIENTRY NUMBER of ORDERED Sum of SHIPPED 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 588 935 003 4 4
39. age Inventory Report Applet User s Manual 3 25 IN Requisitions IN Requisitions PivotTable Sample IN PivotTables Microsoft Excel Inrequis 15 Ele Edt view Insert Format Tools Data Window Help 287 imm 5c 4 amp AZ 9 3 Arial 10 Bruges 18 2 TAA Product Line IN Requisitions Product Line Item ID Location ID DESCRIPTION Last Cost On Hand Qty On Order Qty Safety Stock Forecasted Use 100 CADDO1 Electrical Package 348 0582 8 0000 0 0000 0 0000 4 0000 0000 Electrical Package 348 0582 3 0000 5 0000 1 0000 8 0000 MNOOO Electrical Package 343 5500 17 0000 6 0000 4 0000 48 5000 T0001 Electrical Package 340 1100 10 0000 0 0000 2 0000 15 1000 150 CADOO1 Plumbing Package 902 1100 2 0000 3 0000 0 0000 1 0000 0000 Plumbing Package 874 5600 2 0000 3 0000 1 0000 8 0000 MNOOO Plumbing Package 907 5300 16 0000 2 0000 2 0000 60 0000 T0001 Plumbing Package 874 5600 7 0000 0 0000 0 0000 2 0000 200100 CADDOT Furnace 367 6300 0 0000 0 0000 0 0000 0 0000 0000 367 6300 0 0000 0 0000 1 0000 9 0000 MNODO1 Furnace 379 4400 8 0000 0 00
40. anager The installation process is described in this section The Inventory Report Applet needs a minimum of 850 kilobytes 850K B for installation You must also have installed Inventory 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 Inventory application before you install this report applet The installation will treat the report applet as though you are reinstalling Inventory This is normal behavior When you install the report applet Resource Manager copies the PivotTables to the directory where your Inventory programs are stored You must have access to this directory from your Windows machine to access the tables in Microsoft Excel 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 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 Inventory Report Applet User s Manual 2 1 Installation 1 Move the CONFIG TPM file to the C WINDOWS directory and change any Data Sources you ha
41. 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 Inventory Report Applet User s Manual 1 13 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 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 By Use the Query Wizard to create edit queries 10 Select the data source you set up in the previous steps 1
42. elds 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 COST COMPNT PRICE ORDERED SHIPPED QTY SELL BACKORDERED QTY Select Save from the File menu to save the query Inventory Report Applet User s Manual Introduction Creating Microsoft Excel PivotTables Using the Query in Microsoft Excel 1 Start Excel open new worksheet Fie Insert Format Tools Data Window Help 18 x JOSE SRY ym m o z 2 Zl 9 wow 2 95 4 BEE 1 9 Ready 2 Select the Data menu then select PivotTable Report Inventory Report Applet User s Manual 1 19 Creating Microsoft Excel PivotTables Introduction 1 20 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 ves Finish In step 1 of the Wizard a list of options is displayed where you can choose your data source to be
43. er s Manual Field Transaction Type Transaction Period Transaction Year Item ID Location ID Transaction Date Quantity Unit Cost Unit Price Extended Cost Extended Price 3 33 IN Transactions IN PivotTables IN Transactions PivotTable Sample XX Microsoft Excel Intrans DI x File Edit View Insert Format Tools Data Window Help la DSR SRY SRA ewrAAUM 9 aria 10 B Qty Unit Cost Unit Price Ext Cost Ext Price 100 0000 2 7500 0 0000 275 00 100 0000 2 2100 0 0000 221 00 100 0000 3 5750 0 0000 357 50 2 1 8900 0 0000 189 00 100 0000 0 7450 0 0000 74 50 500 0000 11 1700 0 0000 5585 00 Sheet 3 34 Inventory Report Applet User s Manual Unit Pricing File Name INUNITPR XLS Description The IN Unit Pricing PivotTable uses the data in the Unit Pricing INUPx file to display units of measure and the amount of the various types of prices associated with them The report is sorted by Item ID and Unit of Measure but you can easily change the sort order or add Location ID to the sort Active Fields Default Field Type Field Page Location ID Row Item ID Unit of Measure Column Average Price List Price Minimum Price Base Price Inventory Report Applet User s Manual 3 35 IN Unit Pricing IN Unit Pricing PivotTable Sample
44. he sort order or include the Location ID in the sort You can use this report to review and analyze the cost and quantities of the items you have on hand You can also use this report to value your inventory items Active Fields Default Field Type Field Page Location ID Row Item ID Lot Number Initial Date Column Quantity Unit Cost Extended Cost Inventory Report Applet User s Manual 3 21 IN Quantity Detail IN Quantity Detail PivotTable Sample File Edit View Insert Format Tools Data Window Help 287 2 Ps 21 Bine a aria 10 m PivotTable G IN Quantity Detail Location ID Lot Number Unit Cost Ext Cost 346 2400 2077 44 343 5500 2061 30 348 0582 696 12 691 6082 11757 34 100 1729 4564 53613 15 150 874 5600 6121 92 907 5300 6352 71 907 5300 7260 24 150 2689 6200 59171 64 200100 367 6300 1102 89 379 4400 3035 52 200100 Tota 747 0700 8217 77 200200 223 0300 559 09 227 5300 1820 24 200200 Tota 450 5600 4956 16 200300 419 9125 1259 74 429 9500 3439 60 200300 Tota 849 8625 9348 49 200400 125 2300 375 69 127 4000 1019 20 200400 Tota 252 6300 2778 93 200500 45 5900 136 77 47 5000 380 00 200500 Tota 93 0900 1023 99 2
45. ive Fields Default Field Type Field Page None Row Item ID Location ID Customer Level Adjustment Type Adjustment Base Column Total Adjustment Amount Inventory Report Applet User s Manual 3 11 IN Location Pricing IN PivotTables IN Location Pricing PivotTable Sample Edit View Insert Format Tools Data Window Help 18 eT o Belz 2 Me OB 0m 8 arial u G EE Ta 72 9 f A13 Y Item ID A B D E Wu G zj 1 Location Pricing 2 Adjustment Type Adjustment Base 3 0 Dollar No Base 4 1 Percentage 5 Standard Cost 8 Base Cost A Average Price 7 8 L List Price g M Minimum Price 10 C Calculated Price 11 12 Adj Amount 13 ID Location ID Customer Level Adj Type Base Tota 14 100 001 ACEDO1 1 C 20 00 15 00001 001 1 C 00 16 MNOOO01 001 1 C 00 17 TX0001 ACE001 1 C 0 18 300 CADOD1 01 1 C 00 19 00001 _ 1 00 20 MNOOO1 1 00 21 0001 ACEOO1 1 C 0 22 550 CADDO1 ACEDO1 1 C 0 23 01 1 C 24 MNOOD1 01 1 C 2 25 TXDOO1 ACEDO1 1 C 20 0 x Sheett 4 1 Ready 3 1
46. k1 BEE 8 Ele Edt view Insert Format Tools Data Window Help 1 tA 21 a o 418 eo 9 Bru eszsiH ss 9 A 6 gt Data INVOICE NUMBERIINVOICE ENTRY NUMBER 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 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 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 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 991 001 10 10 1342 87 2417 166 10742 96 002 5 5 22 01 51 361 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 529 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 D01 5 5 145 98 342 144 980 82 29 24889106 Total 5 5 145 98 342 144 980 82 30 Grand
47. lds Default Field Type Field Page Location ID Row Item ID Vendor ID Column Base Quantity Base Cost Inventory Report Applet User s Manual 3 37 Vendor Information IN PivotTables IN Vendor Information PivotTable Sample S Eie Edit view Insert Format Tools Data Window Help lej xl DG o 815 a 94 6 aria sno slm 5 z a A Bvotrable GE E 4 A3 M Location ID E E G H a IN Vendor Information Location ID Item ID Vendor ID Base Qty 100 4 0000 150 1 4 0000 200 1 4 0000 200100 01 4 0000 01 4 0000 200200 001 4 0000 01 4 0000 200300 01 4 0000 01 4 0000 200400 UM001 4 0000 01 4 0000 200500 01 4 0000 01 4 0000 200600 01 4 0000 01 4 0000 250 01 4 0000 N 300 01 350 01 400 001 450 01 460 01 550 01 555 01 600 001 32 650 PLAD01 4 0001 _ 42119 2500 34 700100 DRY 1 0000 0 0000 35 700110 1 0000 0 0000 39 700113 1 0000 2 5000 700115 1 0000 I4 4 P PIN Sheet1 Jal 1 Ti Ready 3 38 Inventory Report Applet User s Manual accessing data Microsoft Excel 1 11 D data files in Inventory 1 5 Data Source creating a new source 1 13 Fast Connect 1 14 No Shadow Dictionary Consistency Check 1
48. n this file is the basis of the IN Serial Numbers INSERNUM XLS PivotTable INVIxxx The Vendor Information file stores information about the vendors through which you purchase your inventory items The data stored in this file is displayed on the IN Vendor Information INVENDOR XLS PivotTable INQLxxx and INQTxxx The Inventory Quantity Detail and Totals files combine to store all the quantity information for the items you have in inventory The detail data which includes the on hand quantity and cost is displayed on the IN Quantity Detail INQTYDET XLS PivotTable The data in the header file is the basis of the IN Quantity Totals INQTYTOT XLS table Inventory Report Applet User s Manual Introduction Inventory Data Files INCJxxx The COGS Adjustment Journal file stores the unposted adjustments to the inventory valuation amount The transactions in this file are used to update the General Ledger The data in this file is the basis for the IN COGS Adjustments INCOGADJ XLS PivotTable INCTxxx The Physical Counts file stores the unposted frozen and counted quantities from any physical inventory cycles that are in process The data in this file is the basis for the IN Physical Counts INPHYSCT XLS PivotTable INTRxxx The Inventory Transactions file stores the unposted transactions that you have entered in through Inventory Adjustments The data in this file is displayed on the IN Transactions INTRANS XLS PivotTable INRQx
49. ndows 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 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 Inventory Report Applet User s Manual 1 3 Inventory Data Files The Inventory IN application helps you control and keep track of the items you stock For each item including lots and serialized items Inventory tracks the quantities sold purchased and adjusted location transfers and costs Each item is identified by location product line user defined fields bin number status vendor ID serial and lot number and unit of measure IN Data Files The Inventory Report Applet contains several spreadsheets that report information from the OSAS Inventory data files The PivotTables in the IN Report Applet are based on these data files INLDxxx The Item Location Detail file contains general information about the items stored in each location The information stored includes description status and summarized his
50. o show edit limiting records shown NUM Inventory 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 CUSTOMER ID SUBTOTAL SALES TAX TOTAL COST 15 Select Table from the main menu and choose Add tables The Add Table dialog box appears Close SOTH zi Options Dictionary 71 Inventory Report Applet User s Manual 1 17 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 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 fi
51. of UNIT_PRICE 1317 384 14 Sum of Profit Dollars 409 854 15 003 Sum of ORDERED QTY 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 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 4 PIN Sheet Shest2 Z Sheets Z Ready Inventory Report Applet User s Manual Sum of SHIPPED_QTY_SELL Iq 1 31 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 Microsoft Excel Boo
52. ons you made in inventory The report is sorted by Item ID and Location ID but you can easily change the sort order or add Year Period to the sort Active Fields Default Field Type Page Row Column Inventory Report Applet User s Manual Field Year Period Item ID Location ID Purchase Quantity Purchase Return Quantity Sales Quantity Sales Return Quantity Adjusted Quantity Purchase Cost Purchase Return Cost Sales Cost Sales Return Cost Adjusted Cost Sales Price Sales Return Price 3 81 PivotTables Summary History IN Summary History PivotTable Sample ysyunsuy CEREN 2 Je PT hays 000 YGSES6EL 000 000 02950751 1000 66 920677 00000 00000 0000 99 00000 0000 evo pueio ge 000 60990 000 000 089104 000 000 000070 000070 100001 000070 000070 TODON 002 SE 000 000 000 000 000 000 OLGELZBL 00000 000070 00000 00000 0000 EL LOOONWY 059 VE 000 000 000 000 000 000 OZ vSly 000070 00000 00000 00000 0000 0E LOOONW 019 000 voze 000 000 9260 000 92 22901 00000 00000 100052 00000 0000788 TOOONW 009 Ze 000 000 000 000 000 000 808612 000070 000070 00000 00000 000072 TODON 099 1 000 626995 000 000 001 000 05 04881
53. port 3 30 screen 3 30 IN Serialized History PivotTable description 3 27 files used in 1 7 sample report 3 28 screen 3 28 IN Summary History PivotTable description 3 31 files used 1 8 sample report 3 32 screen 3 32 IN Transactions PivotTable description 3 33 files used in 1 7 sample report 3 34 screen 3 94 IN Unit Pricing PivotTable description 3 35 files used inj 1 5 sample report 3 36 screen 3 36 IN Vendor Information PivotTable description 3 37 files used inj 1 6 sample report 3 38 screen 3 38 INBINS XLS data files 1 6 description 3 3 INBNxxx file description 1 6 2 INCJxxx file description 1 7 INCOGADJ XLS data files 1 7 description 3 5 INCTxxx file description 1 7 INHIST XLS data files 1 7 description 3 7 INHIxxx file description 1 7 INHSxxx file description 1 8 INLDxxx file description 1 5 INLOCDET XLS data files 1 5 description 3 9 INLOCPRC XLS data files 1 5 description 3 11 INLOTDET XLS data files 1 6 description 3 13 INLOTHST XLS description 3 15 INLPxxx file description 1 5 INLTxxx file description 1 6 INPHYSCT XLS data files 1 7 description 3 17 INPRCSTR XLS data files 1 6 description 3 19 INPSxxx file description 1 6 INQLxxx file description 1 6 INQTxxx file description 1 6 Inventory Report Applet User s Manual Index INQTYDET XLS data files 1 6 description 3 21 INQTYTOT XLS data files
54. ris 9 z O A Bivotrable a 4 Serial Item Status A B 0 G SE TE 1 Serial Numbers 2 4 Serial Item Status mi 5 6 Data 7 ID Serial Serial Cost Serial Price 8 700500 1 1000 2 2500 0 0000 g 2 1000 2 2500 0 0000 10 214216 2 2500 0 0000 11 21A217 2 2500 0 0000 12 21 218 2 2500 0 0000 13 21 219 2 2500 0 0000 14 21 220 2 2500 0 0000 i5 214221 2 2500 0 0000 16 21 222 2 2500 0 0000 17 21 223 2 2500 0 0000 18 21 224 2 2500 0 0000 19 21 225 2 2500 0 0000 20 21 226 2 2500 0 0000 21 21 227 2 2500 0 0000 22 21A228 2 2500 0 0000 23 214229 2 2500 0 0000 24 21 230 2 2500 0 0000 25 21 231 2 2500 0 0000 26 214232 2 2500 0 0000 27 214233 2 2500 0 0000 28 214234 2 2500 0 0000 zu 214235 2 2500 0 0000 30 21 2 2 2500 0 0000 2 214237 2 2500 0 0000 32 21 238 2 2500 0 0000 33 214239 2 2500 0 0000 34 21 240 2 2500 0 0000 21 241 2 2500 0 0000 36 21A242 2 2500 0 0000 37 21 243 2 2500 0 0000 38 21 244 2 2500 0 0000 39 21 245 2 2500 0 0000 40 21A246 2 2500 0 0000 41 21A247 2 2500 0 0000 414 H 5 122008 un 141 Ready 3 30 Inventory Report Applet User s Manual Summary History File Name INSUMHST XLS Description The IN Summary History PivotTable uses the data in the IN Summary History INSHx file to display cost price and quantity information about transacti
55. rt 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 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 26 Inventory Report Applet User s Manual Introduction Creating Microsoft Excel PivotTables 4 Click OK XX 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 I x D SO Sm gt lt T gt ALL 4 15 6 aria rio B ZU BS 55 8 S 6 PivotTable jm m B D E Giaj 1 TRANSACTION TYT AII 2 g BATCH ID 4 INVOICE NUM
56. sert Format Tools Data Window Help 18 x jg 8 aria 0 BrzUEsZasB so A Location ID A B C D E F G 1 Quantity Totals 2 3 Location ID z 4 5 Data ID Lot Number On Hand Qty Committed Qty In Use Qty Order Qty Total Cost 7 100 31 0000 0 0000 3 0000 11 0000 10697 73 8 150 22 0000 0 0000 1 0000 5 0000 19734 87 9 200100 11 0000 0 0000 0 0000 0 0000 4138 41 10 200200 11 0000 0 0000 0 0000 0 0000 2489 33 11 200300 11 0000 0 0000 0 0000 0 0000 4699 33 12 200400 11 0000 0 0000 0 0000 0 0000 1394 89 13 200500 11 0000 0 0000 0 0000 0 0000 516 77 14 200600 11 0000 0 0000 0 0000 0 0000 815 76 15 250 583 0000 0 0000 20 0000 16 0000 749287 70 16 300 98 0000 0 0000 24 0000 5 0000 2097 80 17 350 19 0000 0 0000 7 0000 4 0000 4066 49 18 400 35 0000 0 0000 5 0000 2 0000 29674 77 19 450 44 0000 0 0000 0 0000 5 0000 6880 57 20 460 63 0000 0 0000 5 0000 6 0000 10945 41 21 550 4 0000 0 0000 4 0000 5 0000 4144 40 22 600 13 0000 0 0000 1 0000 3 0000 1551 11 23 610 26 0000 0 0000 5 0000 0 0000 3748 61 24 650 15 0000 0 0000 5 0000 4 0000 335132 00 25 700 6 0000 0 0000 0 0000 0 0000 1209 48 26 700100 170 0000 0 0000 0 0000 0 0000 18684 70 27 700110 85 0000 0 0000 0 0000 0 0000 4884 95 28 700111 525 0000 0 0000 0 0000 0 0000 36781 50 29 7001111 1200 0000 0 0000 0 0000 0 0000 72 00 30 7001112 12 0000 0 0000 0 0000 0 0000 22 44 31 700113 3 0
57. t 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 30 Inventory 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 z amp A Me ux 9 CH v aria gt B ZU 2 9 9 S 5 A PivotTable E4 Total 1 BATCH ID All 2 TRANSACTION g 4 INVOICE NUMBER INVOICE DATE ENTRY NUMBER Data Total 5 24889101 12 21 991 001 Sum of ORDERED 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
58. torical data Data from this file is used in the IN Location Detail INLOCDET XLS PivotTable INLPxxx The Location Pricing file stores customer specific pricing that applies only to the items stored in a single location The information stored in this file is displayed on the IN Location Pricing INLOCPRC XLS PivotTable INUPxxx The Unit of Measure Pricing file stores various pricing types for each unit of measure for each item in each location The data stored in this file is the basis of the IN Unit Pricing INUNITPR XLS PivotTable Inventory Report Applet User s Manual 1 5 Inventory Data Files Introduction 1 6 INPSxxx The Price Structures file stores price matrices that you establish to calculate prices based on the price category from the items on file and the customer level of the customer you sell to The data stored in this file is the basis of the IN Price Structures INPRCSTR XLS PivotTable INBNxxx The Bins file stores information about the bins you use to store items in each location The data stored in this file is the basis of the IN Bin Analysis INBINS XLS PivotTable INLTxxx The Lot Detail file stores lot numbers and other lot information for items designated as lotted The data stored in this file is the basis of the IN Lot Detail INLOTDET XLS PivotTable INSNxxx The Serial Number file stores information about the serial numbers you have purchased and or sold through Inventory The data stored i
59. troduction Building a Query For a PivotTable 1 Start Microsoft Query 27 Microsoft Query x oud 5 4 amp 3 t fn err Tres pem ee ed 2 Under the File menu select New The Choose Data Source screen appears Choose Data Source BEI 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 Bl Use the Query Wizard to create edit queries 1 12 Inventory 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 21 What name do you want to give your data source 0545 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 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 BASIS ODBC Driver in field 2 6 Click Connect The BASIS ODBC Driver Data Source Setup box appears Enter Data Source Specification OK Data Source Name Description Cancel Database Configuration 6 0 D
60. uP Ready Inventory Report Applet User s Manual NUM 1 27 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 Subtotals Delete Tauber 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 28 Inventory Report Applet User s Manual Introduction Creating Microsoft Excel 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 amp lt 7 2 45 6 Arial 10 B z u GE eR 5 e pivotable 4 B4 INVOICE DATE INVOICE NUMBER INVOICE DATE IENTRY
61. ve 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 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 Using a Different CONFIG TPM 2 2 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 in any PivotTable included with this report applet Microsoft Excel BASIS BASIS ODBC Driver Default configuration Failed DeFault CONFIG C datadict Config tpm error duplicate or missing file Inventory Report Applet User s Manual Installation When you click on OK the BASIS ODBC Driver Data Source Setup dialog box appears BASIS ODBC Driver Data Source Setup x Enter Data Source Specification OK Data Source Name Description C datadict Contig tpm Cancel Database
62. xx The Inventory Requisitions file stores the results of the Inventory Reorder Calculations The data in this file is used to create requisitions in Purchase Order The data in this file is displayed on the IN Requisitions IINREQUIS XLS PivotTable INHIxxx The Inventory Detail History file contains detailed information about past inventory transactions The data in this file is the basis for the IN Detail History INHIST XLS PivotTable INSHxxx The Inventory Serialized History file contains detailed information about past inventory transactions involving serialized items The data in this file is the basis for the IN Serialized History INSERHST XLS PivotTable Inventory Report Applet User s Manual 1 7 Inventory Data Files Introduction INHSxxx The Inventory Summary History file contains summarized historical information about each item by type of transaction in each period The data in this file is used on the IN Summary History INSUMHST XLS PivotTable 1 8 Inventory 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 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

Download Pdf Manuals

image

Related Search

Related Contents

Charte d`engagement « Collectivités lauréates pour la transition  ASRock A75M Quick Start Manual  Jenn-Air W10136129C Ice Maker User Manual  eBookMan Manual del usuario - Franklin Electronic Publishers, Inc.  HP EliteDisplay E271i    Panasonic C-PM-106  940724 MO23EL 23L  Philips Power cord (EU) CRP394  電動グリースポンプ GMS−4−7C  

Copyright © All rights reserved.
Failed to retrieve file