Home

Bill of Materials/Kitting

1. aria rio B 7U 95 9 9 S 5 A PivotTable 7 1 ia iif 030 E4 Total B c D E 1 BATCH ID All x 2 TRANSACTION TYf AII g 4 INVOICE NUMBER INVOICE DATE ENTRY NUMBER Data Total 5 24889101 12 21 98 001 Sum of ORDERED QTY 4 B Sum of SHIPPED QTY SELL 4 f Sum of UNIT COST COMPNT 343 55 8 Sum of UNIT PRICE 475 686 g Sum of Profit Dollars 528 544 10 002 Sum of ORDERED QTY 1 11 Sum of SHIPPED QTY SELL 1 12 Sum of UNIT COST COMPNT 907 53 13 Sum of UNIT_PRICE 1317 384 14 Sum of Profit Dollars 409 854 15 003 Sum of ORDERED QTY 7 15 Sum of SHIPPED QTY SELL 7 ilc Sum of UNIT COST COMPNT 22 01 18 Sum of UNIT PRICE 20 7995 19 Sum of Profit Dollars 8 4735 20 004 Sum of ORDERED QTY 5 21 Sum of SHIPPED QTY SELL 5 22 Sum of UNIT COST COMPNT 226 98 23 Sum of UNIT PRICE 526 131 24 Sum of Profit Dollars 1495 705 25 005 Sum of ORDERED QTY 2 26 Sum of SHIPPED QTY SELL 2 27 Sum of UNIT COST COMPNT 151 14 28 Sum of UNIT PRICE 381 645 29 Sum of Profit Dollars 441 01 30 24889101 Sum of ORDERED QTY 19 3124889101 Sum of SHIPPED_QTY_SELL 19 32 24889101 Sum of UNIT COST_COMPNT 1661 22 33 24889101 Sum of UNIT PRICE 2721 6455 34 24669101 Sum of Profit Dollars 20148 0845 35 24889102 12 21 99 001 Sum of ORDERED QTY 3 36 4 Wi Sheet Shest2 7 Sheets 7 Ready BK Report Applet User s Manual Sum of SHIPPED QTY
2. 1 24 BK 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 oy x 1 lt 7 We x ALL aria rio B z U BS 55 8 S 6 PivotTable 7 aia ci hd A B D E Giaj 1 TRANSACTION TYT AII x 2 g BATCH ID 4 INVOICE NUMBER INVOICE DATE ENTRY NUMBER Data 000002 Grand Total 5 24889101 12 21 98 001 Sum of ORDERED QTY 4 4 6 Sum of SHIPPED_QTY_SELL 4 4 Sum of UNIT COST COMPNT 343 55 343 55 8 Sum of UNIT PRICE 475 586 475 586 g Sum of Profit Dollars 528 544 528 544 10 002 Sum of ORDERED QTY 1 1 11 Sum of SHIPPED QTY SELL 1 1 12 Sum of UNIT COST COMPNT 907 53 907 53 13 Sum of UNIT PRICE 1317 384 1317 384 14 Sum of Profit Dollars 409 854 409 854 15 003 Sum of ORDERED_QTY 7 7 16 Sum of SHIPPED_QTY_SELL 7 7 17 Sum of UNIT COST COMPNT 22 01 22 01 18 Sum of UNIT PRICE 20 7995 20 7995 19 Sum of Profit Dollars 8 4735 8 4735 20 004 Sum of ORDERED QTY 5 5 21 Sum of SHIPPED QTY SELL 5 5 22 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
3. BK 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 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 TRANSACTION TYPE field and drop it there 1 28 BK 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 2181 E SRY Gere Me Q1 CH v
4. Using the Query in Microsoft Excel l Start Excel and open a new worksheet 8 Ele Edt Insert Format Tools Data Window Help 18 x JOSE SRY SBS 5 a Oel amp zi B 10 aria 0 Brzu jeEszsB lax 4 3 EFE n 0 A Ready 2 Select the Data menu then select PivotTable Report BK Report Applet User s Manual 1 17 Creating Microsoft Excel PivotTables Introduction 3 The PivotTable Wizard appears PivotT able Wizard Step 1 of 4 L2 x Where is the data that you want to analyze C External data source C Multiple consolidation ranges Another PivotTable Cancel lt Back L ves Finish In step 1 of the Wizard a list of options is displayed where you can choose your data source to be used in your PivotTable Select External Data Source and click Next gt The PivotTable Wizard Step 2 dialog box appears Where is your external data stored No data fields have been retrieved Cancel Back Next gt Finish 4 Instep 2 of the Wizard click Get Data BK 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 Fil
5. Column BK Report Applet User s Manual Field Entry Type GL Period Build Number Inventory ID Reference Location Quantity Used Entry Amount 3 7 BK Journal BK PivotTables BK Journal PivotTable Sample 188 Ele Edit view Insert Format Tools Data Window Help lel x DG E amp l t o rfi d 94 6 10 Aria zo Bzru E H 5 435 dg 5 A Evotrabie 2 t A3 T Entry Type A B C D E E G Tj 1 BK Journal n 2 3 JEntry Type All x 4 GL Period All 5 6 Data 7 Build No Inventory ID Reference Location Qty Used Entry Amt 8 1000008 700 MNODOT 2 0000 175 03 9 700100 MNODO2 3 0000 33 45 10 700110 MNODO2 3 0000 18 23 11 700111 002 1 0000 70 06 12 700113 002 1 0000 2 50 ils 700115 002 1 0000 12 43 14 700117 002 2 0000 10 82 15 700120 MNODO2 1 0000 0 52 15 700130 MNODO2 1 0000 0 43 17 700200 MNDODO2 4 0000 8 16 18 700300 MNDODO2 4 0000 8 32 19 700400 MNDODO2 20 0000 2 20 20 700500 002 1 0000 2 25 21 700998 002 2 0000 5 26 22 700999 002 5 0000 0 40 23 Grand Total 51 0000 0 00 IA FIF Sheet Ready 3 8 BK Report Applet User s Manual File Name BKMSTHDR XLS Description BK Master Header The BK Master Header PivotTable uses the data in the Bill o
6. 0 00 1 00 200600 001 1286 97 0 00 1 00 001 200100 T0001 1286 97 0 00 1 00 200200 TX0001 286 97 0 00 1 00 200300 TX0001 1286 97 0 00 1 00 200400 T0001 1286 97 0 00 1 00 200500 Tx0001 1286 97 0 00 1 00 200600 700100 T0001 002 1286 97 8 32 0 00 34 87 1 00 1 00 700300 002 8 32 34 87 4 00 700500 002 8 32 34 87 1 00 700100 MNO002 700110 002 6 61 40 06 1 00 700120 MNODO2 6 61 40 06 1 00 700130 002 6 61 40 06 1 00 700998 002 6 61 40 06 2 00 700999 MNODO2 6 61 40 06 5 00 700110 002 700111 002 93 58 111 81 1 00 700113 002 93 58 111 81 1 00 0014 3 6 BK Report Applet User s Manual File Name BKJRNL XLS Description BK Journal The BK Journal PivotTable uses the data in the BK Journal BKJRx file to display details about your unposted assemblies The information is sorted by Build Number Inventory ID and Reference Location but you can change the sort easily to include Entry Type and GL Period You can use this PivotTable to review and analyze your current builds Active Fields Default Field Type Page Row
7. OR 2 Sum of SH 2 114 Pi Sheet1 Shest2 Sheet3 1 Highlight rows and columns to shift them around To display only invoices change Transaction Type from All to 3 Change it to 4 and credit memos are displayed Totals per type are also displayed Adding a Calculated Field You can also add new fields like profit to the data area To add profit to the data area follow these steps 1 Highlight the last row in your data area Sum of UNIT_PRICE right click and select Insert BK Report Applet User s Manual 1 23 Creating Microsoft Excel PivotTables Introduction The Insert Calculated Field dialog box appears Insert Calculated Field Mane Formula 0 Delete BATCH ID ORDER NUMBER TRANSACTION TYPE INVOICE NUMBER INVOICE DATE CUSTOMER ID SUBTOTAL SALES TAX zi Insert Field 2 Enter the following information about the inserted field Enter Profit Dollars in the Name field 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 TYPE INVOICE NUMBER INVOICE DATE CUSTOMER ID SUBTOTAL SALES TAX zi Insert Field
8. 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 BK Report Applet User s Manual 2 1 Installation If your CONFIG TPM file is stored in a different directory you have three choices for using the PivotTables supplied with the report applet 1 Move the CONFIG TPM file to the C WINDOWS directory and change any Data Sources you have set up and any ODBC reports or spreadsheets you have already set up to use the CONFIG TPM in its new location 2 Copy the CONFIG TPM file to the C WINDOWS directory and leave a copy in its current location You do not need to change any Data Sources or reports you have set up but you need to make any changes in both files 3 Change the PivotTables provided with this report applet to use the CONFIG TPM file in its current location You can find instructions for doing this below If you choose methods 1 or 2 above you can load the PivotTables in Microsoft Excel and begin using them with your data by using the Refresh Data command in Excel If you choose option 3 follow the instructions below to point the PivotTable to the correct CONFIG TPM file Using a Different CONFIG TPM 2 2 If you store your CONFIG TPM file in a location other than the C WINDOW
9. 000 0 00 1 0000 201 58 EG 700 1 WALNUT CABINET 1 0000 0 00 1 0000 164 46 10 700100 002 UNSTAINED CABINET 1 0000 0 00 1 0000 124 40 11 700111 002 CABINET ASSEMBLY 2 0000 127 53 2 0000 127 53 1700113 002 CUTTING BOARD 2 0000 5 00 2 0000 5 00 13 700115 002 DRAWER ASSEMBLY 2 0000 24 86 2 0000 24 86 14 700117 002 CABINET DOOR 4 0000 10 82 4 0000 21 64 15 700120 2 STAIN WALNUT 2 0000 1 04 2 0000 1 04 16 700130 002 VARNISH 2 0000 0 86 2 0000 0 86 17 700200 002 HINGES 8 0000 408 8 0000 16 32 18 700300 002 HANDLES 8 0000 4 16 8 0000 16 64 19 700400 2 WOOD SCREWS 40 0000 0 22 40 0000 4 40 20 700500 002 SERIES NUMBER 1 0000 0 00 1 0000 2 25 21 700998 002 VARNISH BRUSHES 4 0000 5 26 40000 10 52 22 700999 2 OIL RAGS 10 0000 0 16 10 0000 0 80 23 Grand Total 87 0000 183 99 88 0000 722 30 Hh Sheet1 jal Ready 3 12 BK Report Applet User s Manual A accessing data Microsoft Excel 1 9 B Bill of Materials Detail PivotTable description 3 3 files used in 1 5 sample report 3 4 screen 3 4 Bill of Materials Kitting data files 1 5 description 1 5 installing 2 1 system requirements 2 1 BK History PivotTable description 3 5 files used in 1 6 sample report 3 6 screen 3 6 BK Journal PivotTable description 3 7 files used in 1 5 sample report 3 8 screen 3 8 BK Master Header PivotTable description 3 9 files
10. 02 2 2 874 56 1317 384 885 648 003 5 5 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 124889104 Total 16 16 1591 87 2994 678 22444 928 4 24889105 12 21 99 001 1 1 855 61 1485 495 629 885 1002 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 2824889106 12 21 99 001 5 5 145 98 342 144 980 82 29124889106 Total 5 5 145 98 342 144 980 82 30 Grand Total 69 69 6979 2882 12473 1185 37907 4 2907 31 PIN Sheets Sheet Sheet2 Sheet 7 Ready esf NUM 7 1 30 BK Report Applet User s Manual Introduction Creating Microsoft Excel PivotTables Changing the Data Sort To change the order in which the data is displayed you can simply change the Row fields around For example our PivotTable is sorted by Invoice Number To sort it by Invoice Date instead click and drag the INVOICE DATE field to the left of the INVOICE NUMBER field The data is sorted by Invoice Date and is redisplayed File Edit View Insert Format Tools Data Window Help 18 ez wz 0 10 so 9 o A Bivotrable E Paaa B5 INVOICE NUMBER A
11. B G H F 1 BATCH ID All xi TRANSACTION Data INVOICE DATE INVOICE NUMBIENTRY NUMBER of ORDERED Sum of SHIPPED SELL Sum of UNIT COST COMPNT Sum of UNIT PRICE Sum of Profit 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 526 131 1495 705 005 2 2 161 14 381 645 441 01 24889101 Total 19 19 1661 22 2721 6455 20148 0845 24889102 001 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 586 382 8834 002 2 2 874 56 1317 384 885 648 003 5 5 0 51 381 256 905 24889103 Total 10 10 1222 6182 1844 451 6218 328 24889104 001 10 10 1342 87 2417 166 10742 96 002 5 5 22 01 51 381 146 855 003 1 1 226 99 526 131 299 141 24889104 Total 16 16 1591 87 2994 678 22444 928 24889105 001 1 1 855 61 1485 495 529 885 002 4 4 161 14 381 645 882 02 003 4 4 176 47 417 96 965 96 24889105 Total 9 9 1193 22 2285 1 9826 92 124889106 1001 5 5 145 98 342 144 980 82 24889106 Total 5 5 145 98 342 144 980 82 30 Grand Total 69 69 6979 2882 12473 1185 379074 2907 31 z Id 4 M Sheet Sheett Sheet Sheets 7 5 Fjal gt 74 Ready peu NUM fisi f A You can also drag the selection fields from the Page area to the Row area to sort the data by those fields BK Report Applet User s Manual 1 31 Creating Microsoft Excel PivotTables Introduction
12. BOM Type nil Bd Component ID for 1 Extended Qty Cost for 1 0 0 e eo 200200 eoOe eo 200300 e 200400 jje ojeoejejelejeljejlelboljeoejlejale eoe eio 2 200500 3 E esusl2ao 0 0 0 0 0 200600 e e eJeioojolsI ieojloijjomooojojj lojolimmooaeojio n NN gt 2lolololololololololololololololololololololololololololololololole ce N 3 4 BK Report Applet User s Manual BK History File Name BKHIST XLS Description The BK History PivotTable uses the data in the Detail History BKHIx file to display historical quantities and costs associated with assemblies and kits you ve produced This table is sorted by Assembly ID Assembly Location Component ID and Component Location but you can easily change the sort or include the Bill of Material Header or Detail Types in the sort You can use this PivotTable to review and analyze trends in assembly requirements and costs Active Fields Default Field Type Page Row Column BK Report Applet User s Manual Field Bill of Materials Header Type Bill of Materials Detail Type Assem
13. 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 BK Report Applet User s Manual Installation You can put the Bill of Materials Kitting ODBC Report Applet on your system by installing it through Resource Manager The installation process is described in this section The Bill of Materials Kitting Report Applet needs a minimum of 81 kilobytes 81KB for installation You must also have installed Bill of Materials Kitting 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 Bill of Materials Kitting application before you install this report applet The installation will treat the report applet as though you are reinstalling Bill of Materials Kitting This is normal behavior When you install the report applet Resource Manager copies the PivotTables to the directory where your Bill of Materials Kitting 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
14. OPEN SYSTEMS Accounting Software Bill of Materials Kitting ODBC Report Applet User s Manual PN 2210 BKO60 1998 Open Systems Holdings Corp All rights reserved Document Number 2210 BKO600 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 Bill of Materials Kitting Data Files Introducti
15. S 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 BK 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 Configuration m Advanced gt gt leki 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 gt c Data progFA L Proarm print l progGL 2 1 50 L proglN 1 Rwdata prog R __I progJO L Sample progBK L progOw L sort progBR L progPA mM progEl Ll progPM File name 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 BK Report Applet User s Manual 2 3 Installation Report Applet PivotTables Use the descriptions of the PivotTables in chapter 3 to work with your accounting da
16. SELL Iq beh 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 Book1 BEE 8 Ele Edt view Insert Format Tools Data Window Help 1 film e9 18g D c 9 Bru eszsiH ss 6 e gt Data INVOICE NUMBERIINVOICE ENTRY NUMBER of ORDERED QTY Sum of SHIPPED QTY SELL Sum of UNIT COST COMPNT Sum of UNIT PRICE Sum of Profit Dollars 24889101 12 21 99 001 4 4 343 55 475 686 528 544 002 1 9 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 1003 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 0
17. 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 99 Sum of ORDERED QTY 18 19 il 12 21 99 Sum of SHIPPED QTY SELL 18 19 m 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 F HW Sheet 1 4 Sheet f Sheet 7 dal 1 4 Ready BK Report Applet User s Manual 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 Tauber Automatic C Custom C None Hide items 12 21 99 Show items with no data You can use the PivotTable Field dialog box to change the field name its orientation on the PivotTable it
18. ata files The PivotTable is updated through the ODBC driver The next section includes a tutorial for setting up and modifying PivotTables in Excel BK 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 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 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 BK Report Applet User s Manual 1 9 Creating Microsoft Excel PivotTables Introduction Building a Query For a PivotTable 1 Start Microsoft Query Er Microsoft Query Of x Eile Help oud sar 5 4 amp 3 t fn err emp es 2 Under the File menu select New The Choose Data Source screen appears Databases Queries Locum IM M UIT M ETT md dBASE Files not sharable Excel Files not sharable FoxPro Files not sharable MS Access 97 Database not sharable OSAS Text Files not sharable jul Use th
19. bly ID Assembly Location Component ID Component Location Component Cost Assembly Cost Quantity of Component for 1 Assembly 3 5 BK History BK History PivotTable Sample XX Microsoft Excel Bkhist BK PivotTables Ele Edt view Insert Format Tools Data Window Help JOSE 6RY BOS o T AA m1 arat B z u B 58 5 PivotTable ge 5 BK History BOM Detail Type BOM Detail Type Assembly ID 700 Assembly Location Component ID Component Location 200100 CADOO1 Component Cost Assembly Cost Qty for 1 1286 97 0 00 1 00 200200 CADOO1 1286 97 0 00 1 00 200300 1 285 97 0 00 1 00 200400 1 1286 97 0 00 1 00 200500 CADDO1 1286 97 0 00 1 00 200600 CADOO1 285 97 0 00 1 00 MDODO1 200100 MDODO1 1286 97 0 00 1 00 200200 MDODO1 1286 97 0 00 1 00 200300 MD0001 286 97 0 00 100 200400 200500 MDODO1 Mbo001 1286 97 1286 97 0 00 0 00 1 00 1 00 200600 0001 1286 97 0 00 1 00 001 200100 MNOO01 1286 97 0 00 1 00 200200 001 285 97 0 00 1 00 200300 001 1286 97 0 00 1 00 200400 MNOO01 1286 97 0 00 1 00 200500 001 285 97
20. e Query Wizard to create edit queries BK Report Applet User s Manual Introduction Creating Microsoft Excel PivotTables 3 Select New Data Source and click OK The Create New Data Source screen appears Create New Data Source x What name do you want to give your data source ile osas Data Select a driver for the type of database you want to access Click Connect and enter any information requested by the driver a Connect 7 Save D and password in the data source Cancel 4 Enteraname you want to give the data source in field 1 You can use the same source again 5 Select the BASIS ODBC Driver in field 2 6 Click Connect The BASIS ODBC Driver Data Source Setup box appears Enter Data Source Specification OK Data Source Name Description Cancel Database Configuration CAOSAS prog0 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 BK 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 com
21. e 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 Browse Options dd By Use the Query Wizard to create edit queries 10 Select the data source you set up in the previous steps 1 12 BK Report Applet User s Manual Introduction Creating Microsoft Excel PivotTables The Choose Columns screen appears Query Wizard Choose Columns 21 xi 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 5081 TRANSACTION TYPE c TERMS CODE SOTB Lx rixae 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 sta
22. eld buttons on the right to the _ diagram on the left ORDERED TRANSAG patch COLUMN Star StS wore d cif 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 PivotT able Wizard Step 4 of 4 L2 x Where do you want to put the PivotTable C New worksheet Existing worksheet Click Finish to create your PivotTable Cancel Options lt Back Next gt 12 Thelast step lets you create the PivotTable either in the existing worksheet or in a different worksheet Accept the given options and click Finish BK Report Applet User s Manual Introduction Creating Microsoft Excel PivotTables The PivotTable is displayed File Edit View Insert Format Tools Data Window DG M SRY sma oc PivatTable e 7 SEH 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 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 E 5 Sum of UNIT 226 99 Sum of UNIT 526 131 Sum of
23. en Microsoft Query e x File Edit View Format Table Criteria Records Window Help Bem seu 6 vs x 2 wE Query 1 from OSAS Data ic x BATCH li ORDER_NUM TRANSACTION T INVUICE NUMHINVOICE DATCUSTOMER ID SUBTOTAL SALES TAX TOTAL COST J 000002 _ 00000003 3 24889101 1995 1221 GREOOT 6759 67 0 389303 000002 00000004 3 24889102 19951221 __ 105001 727327 0 3664 63 1288 000002 00000005 3 24889103 19951221 SUNOOT 4318 74 0 279329 000002 00000006 3 24889104 19951221 __ CASHCA 249547 1497 28 13765 74 _ 000002 00000007 3 24888105 19951221 4683 92 0 2206 05 1 0002 00000008 3 24888106 19951221 CASHPS 1710 72 0 7283 1285000002 00000009 4 24889107 1995 12 21 001 973 95 0 1030 85 000002 _ 00000010 4 24889108 19951221 _ 105001 4821455 0 27910 M s RecoaTi PT Select View Criteria to show edit criteria limiting records shown NUM BK 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 followin
24. es not sharable Cancel Browse Options did 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 FLARED 3 il SALES TAX _ Preview Mow Beck Cancel BK Report Applet User s Manual 1 19 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 m What would you like to do next Save Query C View data or edit query in Microsoft Query lt Back Cancel 8 Select Return Data to Microsoft Excel and click Finish You are returned to the PivotTable Wizard Step 2 dialog box PivotT able Wizard Step 2 of 4 L2 Lx Where is your external data stored Get Data Data fields have been
25. f Materials Master Header BKMHx file to display summarized information about your bills of materials and kits The report is sorted by Assembly ID Assembly Location Description and Unit of Measure but you can easily change the order of the sort or include the Assembly Type in the sort Active Fields Default Field Type Page Row Column BK Report Applet User s Manual Field Assembly Type Assembly ID Assembly Location Description Unit of Measure Component Cost Assembly Cost 3 9 BK Master Header BK PivotTables BK Master Header PivotTable Sample XX Microsoft Excel Bkmsthdr iof x File Edit View Insert Format Tools Data Window Help la DG E 58 amp wr 52 LE S A iv 0 aria rio Z UE Ia a 1 9 Pivotrable t M Assembly Type A B C D E F Cz 1 BK Master Header 2 3 Assembly Type All 4 5 Data B Assembly ID__ Assembly Location Desc Units Component Cost Assembly Cost z 200 CADOD1 Heating Cooling Package 1286 97 0 00 8 00001 Heating Cooling Package 1286 97 0 00 9 MNOOO1 Heating Cooling Package 1286 97 0 00 10 Tx0001 Heating Cooling Package 1286 97 0 00 d 11 700 MNOOO1 CABINETS 8 32 34 87 12 700100 MNOOO2 WALNUT CABINET 6 61 40 06 13 700110 MNOOO2 UNSTAINED CABINET 93 58 111 81 14 Grand Tota
26. g fields e BATCH ID ORDER NUMBER e TRANSACTION TYPE e 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 x Close SOTH zi Options Dictionary z BK 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 BATCH ID field in the SOTD table and release the mouse button A line appears between the two BATCH ID fields joining the two fields Follow steps 17 through 18 with the ORDER NUMBER field NOTE You may get the following message For now click Yes to ignore the message and join the fields together Microsoft Query Columns ORDER NUMBER and ORDER NUMBER that you are about to join are of is different types Create the join anyway Select the following fields from the SOTD table e ENTRY NUMBER COST COMPNT e PRICE ORDERED QTY e SHIPPED QTY SELL e BACKORDERED QTY Select Save from the File menu to save the query BK Report Applet User s Manual Introduction Creating Microsoft Excel PivotTables
27. l 5256 39 186 74 _ I4 4 gt M Sheett dal E Ready 3 10 BK Report Applet User s Manual File Name BKTRANS XLS Description BK Transactions The BK Transactions PivotTable uses the data in the BK Transactions BKTRx file to display cost and quantity information for the components used in the current builds The report is sorted by Build Number Component ID Component Location and Description but you can easily change the sort order to include the Assembly ID and Assembly Location Active Fields Default Field Type Page Row Column BK Report Applet User s Manual Field Assembly ID Assembly Location Build Number Component ID Component Location Description Quantity Cost of One Extended Quantity Extended Cost 3 11 BK Transactions BK PivotTables BK Transactions PivotTable Sample 158 Eie Edit view Insert Format Tools Data Window Help 181 x Ds m el A 0 zuo B z u amp w Bvotrabie 6 e A3 Assembly No A B C D B Ir G H a 1 BK Transactions 2 3 Assembly No All 4 Assembly Location All 5 B Data 7 Build No Component No Component Location Desc Qty Cost of One Ext Qty Ext Cost 8 000008 CABINETS 0 0
28. n process on your system This data is displayed on the BK Transactions BKTRANS XLS PivotTable BK Report Applet User s Manual 1 5 Bill of Materials Kitting Data Files Introduction BKHIxxx The Bill of Materials Kitting History file contains detailed information about past builds This file is used only if you elect to save detail history in the Resource Manager Options and Interfaces function The data in this file is the basis for the BK History BKHIST XLS PivotTable 1 6 BK 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 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 Bill of Materials Kitting Report Applet several PivotTables are provided based on the data in the OSAS d
29. ng fields 1 28 IX 2 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 Bill of Materials Detail 3 3 BK History 3 5 BK Journal 3 7 BK Master Header 3 9 BK Transactions 3 11 R Report Applet requirements for 1 9 report applet installation 2 1 report applets definition 1 3 S spreadsheets Bill of Materials Detail 3 3 BK History 3 5 BK Journal 3 7 BK Master Header 3 9 BK Transactions 3 11 system requirements 2 1 BK Report Applet User s Manual
30. on to PivotTables Creating Microsoft Excel PivotTables Installation BK PivotTables Bill of Materials Detail BK History BK Journal BK Master Header BK Transactions Index BK Report Applet User s Manual mk Sa ccm on oo 2 1 3 5 3 7 3 9 3 11 Introduction General Information Bill of Materials Kitting Data Files Introduction to PivotTables Creating Microsoft Excel PivotTables BK Report Applet User s Manual 1 3 1 5 1 7 1 9 General Information The OPEN SYSTEMS Accounting Software OSAS product line consists of several accounting applications Each application addresses a different phase of your financial operations together they form a powerful accounting solution to your daily and periodic accounting needs The ODBC Kit The OSAS ODBC Kit provides users with a way to access their OSAS data through any ODBC compliant productivity package The ODBC Kit includes an ODBC driver for Windows the data dictionaries for the OSAS data files utilities for maintaining the data dictionaries and some sample reports in Microsoft Excel Microsoft Access and Crystal Reports for Windows The Report Applets Since the 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 pr
31. ovided 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 BK Report Applet User s Manual 1 3 Bill of Materials Kitting Data Files You use the Bill of Materials Kitting BK system to record the components required to assemble items and Kits and to track the costs and quantities of assemblies and kits built and components used BK Data Files The Bill of Materials Kitting Report Applet contains several reports that report information from the OSAS Bill of Materials Kitting data files The PivotTables in the BK Report Applet are based on these data files BKMHxxx and BKMDxxx The Bill of Materials Header and Detail files combine to store information about the assemblies and kits you build and the components that make up the assemblies and kits respectively The component detail is presented on the Bill of Materials Detail BKDETAIL XLS PivotTable and information from the header file is used for the BK Master Header BKMSTHDR XLS table BKJRxxx The BK Journal file stores information about the unposted builds in process on your system Data from the BK Journal file is used in the BK Journal BKJRNL XLS PivotTable BKTRxxx The BK Transactions file stores information about the unposted builds i
32. retrieved Cancel lt Back Finish 9 Click Next gt 1 20 BK Report Applet User s Manual Introduction 10 PivotT able Wizard Step 3 of 4 L2 x ES Le Creating Microsoft Excel PivotTables The PivotTable Wizard Step 3 dialog box appears Construct your PivotTable by dragging the field buttons on the right to the diagram on the left BATCH 124 SUBTOTA ORDERED ORDERN rreren RANSAC Backorg NVOICE ENTRY NI A dd INVOICE INIT CO USTOME UN amp E m 2 The selected fields and four areas Page Row Column and Data to put fields are displayed Drag and drop the fields to use in this report into the respective areas To display the full field name hold the cursor on the button and a tool tip displays the full field name Drag and drop the following fields TRANSACTION TYPE into Page INVOICE NUMBER INVOICE DATE and ENTRY NUM into Row BATCH ID into Column ORDERED QTY SHIPPED QTY SELL UNIT COST COMPNT and UNIT PRICE into Data BK 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 PivotT able Wizard Step 3 of 4 L2 1x Cc 2571 Construct your PivotTable by dragging the fi
33. rt 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 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 4 for a value TRANSACTION TYPE 4 is a credit memo BK Report Applet User s Manual 1 13 Creating Microsoft Excel PivotTables Introduction 12 Click Next The Sort Order box appears Query Wizard Sort Order Specify how you want your data sorted If you don t want to sort the data click Next Sottb Ascending _ Descending Then by Ascending Descending Then by Ascending Descending zl lt Back Cancel Use the Sort Order dialog box to select how the data is sorted For example select a field in Sort by and check Ascending or Descending Select more fields and orders for hierarchical sorts For now don t enter any sort fields 13 Click Finish You are returned to the Microsoft Query scre
34. s display mask subtotalling options and so on 2 To shut of the subtotals select None under Subtotals and click OK 1 26 BK Report Applet User s Manual Introduction Creating Microsoft Excel PivotTables The PivotTable is redisplayed reflecting your changes XX Microsoft Excel Book1 OF x File Edit View Insert Format Tools Data Window amp amp lt 7 gt 2 2 da 4 10 2 Arial gt 10 B z u GE eR 5 G0 A pivotable A p B4 INVOICE DATE INVOICE NUMBER INVOICE DATE IENTRY NUMBER 24889101 Sum of ORDERED QTY Sum of SHIPPED QTY SELL Sum of UNIT COST COMPNT Sum of UNIT PRICE Sum of Profit Dollars Sum of ORDERED QTY Sum of SHIPPED QTY SELL Sum of UNIT COST COMPNT Sum of UNIT PRICE Sum of Profit Dollars Sum of ORDERED QTY Sum of SHIPPED QTY SELL Sum of UNIT COST COMPNT Sum of UNIT PRICE Sum of Profit Dollars Sum of ORDERED QTY Sum of SHIPPED QTY SELL Sum of UNIT COST COMPNT Sum of UNIT PRICE Sum of Profit Dollars Sum of ORDERED QTY Sum of SHIPPED QTY SELL Sum of UNIT COST COMPNT Sum of UNIT PRICE Sum of Profit Dollars R H N N 0148 0845 20145 0845 35 24888102 12 21 88 001 Sum of ORDERED QTY 3 3 36 Sum of SHIPPED_QTY_SELL 3 3 x 4 KW Sheet 1 Sheet2 Sheets 7 1 1 gt Ready
35. ta 2 4 BK Report Applet User s Manual BK PivotTables Bill of Materials Detail BK History BK Journal BK Master Header BK Transactions BK Report Applet User s Manual 3 3 3 5 3 7 3 9 3 11 File Name BKDETAIL XLS Description Bill of Materials Detail The Bill of Materials Detail PivotTable uses the data in the Bill of Materials Master Detail BKMDx file to display the components and quantities required to build the assemblies you choose The report is sorted by Component ID Component Location and Unit of Measure but you can easily change the sort to include the Bill of Material Type Assembly ID or Assembly Location You can use this PivotTable to review and analyze the components of an assembly or to review the quantity of those components required Active Fields Default Field Type Page Row Column BK Report Applet User s Manual Field Bill of Materials Type Assembly ID Assembly Location Component ID Component Location Component Unit of Measure Quantity Required for 1 Assembly Extended Quantity Cost for 1 Assembly 3 3 Bill of Materials Detail BK PivotTables Bill of Materials Detail PivotTable Sample XX Microsoft Excel Bkdetail OL x 3 File Edit view Insert Format Tools Data Window Help amp Dc amp amp amp zi MOB s 0 aria rio B ZU 59 10 3 12 o A Biotrable gj A3 bd
36. used in 1 5 sample report 3 10 screen 3 10 BK Transactions PivotTable description 3 11 files used in 1 5 sample report 3 12 screen 3 12 BK Report Applet User s Manual Index BKDETAIL XLS data files 1 5 description 3 3 BKHIST XLS data files 1 6 description 3 5 BKHIxxx file description 1 6 BKJRNL XLS data files 1 5 description 3 7 BKJRxxx file description 1 5 BKMDxxx file description 1 5 BKMHxxx file description 1 5 BKMSTHDR XLS data files 1 5 description 3 9 BKTRANS XLS data files 1 5 description 3 11 BKTRxxx file description 1 5 D data files in BK 1 5 Data Source creating a new source 1 11 Fast Connect 1 12 No Shadow Dictionary Consistency Check 1 12 selecting a data source 1 12 IX 1 Index 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 a table 1 13 Sorting data 1 14 N No Shadow Dictionary Consistency Check Microsoft Query 1 12 ODBC Kit definition 1 3 OSAS general information 1 3 P 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 movi

Bill of Materials/Kitting

Contents

Download Pdf Manuals

Related Search

Related Contents