Home

Fixed Assets (FA)

1. Ready 2 Select the Data menu then select PivotTable Report FA Report Applet User s Manual 1 17 Creating Microsoft Excel PivotTables Introduction 3 The PivotTable Wizard appears PivotTable Wizard Step 1 of 4 L2 x Where is the data that you want to analyze C External data source C 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 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 Next gt Finish 4 Instep 2 of Wizard click Get Data FA Report Applet User s Manual Introduction Creating Microsoft Excel PivotTables The Choose Data Source box from Microsoft Query appears Choose Data Source L2 1x DK Databases Queries 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 did El Use the Query Wizard to create edit queries 5 Click the Queries tab and select the query you saved under Microsoft Query Choose Data So
2. PivotTable adding a calculated field 1 23 changing column data 1 30 changing data sort 1 31 changing field properties 1 26 changing selection fields 1 28 definition 1 7 moving fields 1 28 PivotTable Wizard Choosing a data source 1 18 getting the data 1 18 laying out the table 1 21 returning the data 1 22 PivotTables FA Asset Books 3 3 FA Asset Master 3 5 FA Retired Asset Books 3 9 FA Retired Asset Master 3 11 R Report Applet requirements for 1 9 report applet installation 2 1 2 report applets definition 1 3 5 spreadsheets FA Asset Books 3 3 FA Asset Master 3 5 FA Retired Asset Books 3 9 FA Retired Asset Master 3 11 system requirements 2 1 FA Report Applet User s Manual
3. PivotTable Wizard Step 3 of 4 BEI LS x LI Construct your PivotTable by dragging the field buttons on the right to the diagram on the left 109 ORDERED ORDERN rreren ransad Backorg NVOICE ENTRY NI dd INVOICE INIT CO USTOME UN 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 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 FA Report Applet User s Manual 1 21 Creating Microsoft Excel PivotTables Introduction 1 22 11 The fields are displayed on the screen Numeric fields dropped into the Data section become summary fields PivotTable Wizard Step 3 of 4 L2 1x EJ 2571 Construct your PivotTable by dragging the field buttons on the right to the diagram on the left ORDERED TRANSAG BATCH Star ORDERED_d cif Sum
4. FA Report Applet User s Manual Installation When you click on the BASIS Driver Data Source Setup dialog box appears BASIS ODBC Driver Data Source Setup x Enter Data Source Specification DK Data Source Name Description C datadict Contig tpm Cancel Database Configuration m Advanced gt gt dd To specify the location of your CONFIG TPM file click Browse and select the file from the location screen Locate Configuration File Look in 73 6 c Data progFA JProgrm print JprogGL JprogsD prog P L proglN _ Rwdata prog R L Sample progBK L prog0w 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 amp PivotTable was changed during Refresh Data operation When you click on OK the PivotTable is updated with your accounting data FA 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 FA Report Applet User s Manual Fixed Assets PivotTables FA Asset Books FA Asset Master FA Retired Asset Books FA Retired Asset Master FA Report Applet User s Manual 3 3 3 5 3 9 3 11 FA Asset Books File Name FABOOKS XLS Description
5. 151 Eie gdt view Insert Format Tools Data Window Help la x JOSE amp amp amp amp lt A SB 10 0 B ZU BRS x 9 _ 9 pvotrabe 961 8 M E Asset ID B D E G H 1 L M TH 1 Asset Books 2 3 Data 4 seer Asset Number Sequence No Book Begin Year Book End Year Salvage Value Other Bonus 179 Expense Base Cost Depr Taken YTD Depr Current Depr Annual Depr 5 AUTO 1996 1999 1500 00 0 00 000 1100000 6518 60 815 10 0 00 815 10 6 92 2 1996 2001 0 00 0 00 000 123008 87583 106 28 0 00 14171 7 95 1999 2004 6500 00 0 00 850000 35735 00 380142 0 00 380142 8 BUILDING 1997 2024 0 00 0 00 0 00 197500 00 _12568 18 5386 37 0 00 7181 82 COMPUTER 0001 1993 1996 0 00 0 00 000 6329 84 595060 37924 0 00 379 24 91 1995 1997 0 00 0 00 500000 1102100 6021 00 0 00 0 00 0 00 92 1996 2000 0 00 0 00 O00 5684 78 404757 49117 0 00 654 89 1 1996 2000 0 00 0 00 0 00 7925 47 564293 68476 0 00 91301 63 1 1997 2001 0 00 0 00 O00 478217 248672 688 64 0 00 918 18 B 1997 2001 0 00 0 00 0 00 8810 2 4581 26 1268 66 169154 pa 1 1998 2002 0 00 0 00 O00 1276 44 446 75 248 90 0 00 381 87 2 1998 2002 0 00 0 00 0 00 1055226 _ 52751 3007 40 0 00 400986 65 1 1999 2004 0 00 0 00 O00 1276 44 33187 0 00 331 87 2 1998 2003 0 00 0 00 9000 00 1055225 0 00 58986 0 00 589 86 CRANE 0001 1989 1999 8000 00 000 0 00 42130259 40651170 6790 89 0 00 579089 20
6. What name do you want to give your data source ir fosas Data Select a driver for the type of database you want to access Click Connect and enter any information requested by the driver a Connect Save use D and password in the data source Cancel 4 Enter a name you want to give the data source in field 1 You can use the same source again 5 Select the BASIS ODBC Driver in field 2 6 Click Connect The BASIS ODBC Driver Data Source Setup box appears Enter Data Source Specification DK Data Source Name Description Cancel Database Configuration 6 0 D config tpm Advanced gt gt 7 Enter the file path and name of the CONFIG TPM file you set up from within the OSAS ODBC software in the Database Configuration field or select Browse and locate the file FA Report Applet User s Manual 1 11 Creating Microsoft Excel PivotTables Introduction If you have already built the shadow dictionary click on the Advanced button and check the options for No Shadow Dictionary Consistency Check and Fast Connect to improve performance See online help for additional information about the options that come with the Advanced button 8 Click OK to connect to the data source You are returned to the Create New Data Source screen 9 Select a table in field 4 if you want to select a default table source otherwise leave field 4 blank and select any table when yo
7. blank 4 13 98 1248 96 1 0 1248 96 0 31 95 10 19 99 blank blank 7639 54 14 0 7639 54 0 32 PLANER 0001 3 2 89 2 28 99 3 2 89 98537 80 2 0 98537 68 7883 02 33 0001 3 2 89 2 28 99 3 2 89 216131 20 4 0 216131 2 17290 5 34 WELDER 0001 3 2 09 2128 99 3 2 89 318265 13 3 0 318265 13 25461211 35 Grand Total 1763421 97 67 8 176342197 11125718 Id 4 IM Acquisition Data Vaustion Data 77 Ready 3 6 FA Report Applet User s Manual Fixed Assets PivotTables FA Asset Master Active Fields Valuation Data Default Field Type Page Row Column FA Report Applet User s Manual Field None Asset ID Number and Sequence Number Insured Valuation Date Assessed Valuation Date Replacement Cost Valuation Date Acquisition Cost Quantity Insured Value Assessed Value Replacement Value 3 7 FA Asset Master Fixed Assets PivotTables FA Asset Master Acquisition Data PivotTable Samples icrosoft Excel Famaster olx 18 Edt view Insert Format Tools Data Window Help DSR S6RY s BAS T 2 04 S 0 aria gt Til e 9292 A4 M Asset ID A B C D E F G H J K F 1 FA Asset M
8. ei 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 19 19 37 24889101 Sum of UNIT_COST_COMPNT 1661 22 1661 22 38 24889101 Sum of UNIT PRICE 2721 6455 2721 5455 I4 4 gt HW Sheet 1 Sheet f Sheet 7 14 Te Ready FA Report Applet User s Manual Num 1 25 Creating Microsoft Excel PivotTables Introduction Changing Field Properties You can also change the properties of the fields in the table For example to remove the subtotals from the INVOICE_DATE field 1 Place your mouse cursor on the INVOICE_DATE column heading right click and select Field from the menu The PivotTable Field dialog box appears PivotT able Field 2 x Name rientation Cancel Row C Column Subtotals Delete uber Automatic Custom None Hide items 12 21 99 Show items with data You can use the PivotTable Field dialog box to change the field name its orientation on the PivotTable its display mask subtotalling options and so on 2 To shut of the subtotals select None under Subtotals and click OK 1 26 FA Report Applet User s Manual Introduction Creating Microsoft Excel P
9. DRILL 0001 1989 1999 0 00 0 00 0 00 22016 54 2165035 366 18 0 00 366 18 21 FORKLIFT 0001 1989 1999 5000 00 0 00 0 00 16000000 15247401 2525 99 0 00 252599 22 LAND 0 D 0 00 0 00 0 00 45000 00 000 0 00 0 00 0 00 23 _ 0001 1989 1999 0 00 0 00 0 00 151421 66 148903 21 2518 45 000 251845 24 OFFICE 31 1995 1999 0 00 0 00 0 00 2421 00 191259 38131 0 00 508 41 25 92 1996 2002 0 00 0 00 0 00 3329 67 205203 27246 0 00 363 28 25 93 1 1997 2003 0 00 0 00 O00 114204 442 89 14981 0 00 199 74 27 2 1997 2003 0 00 0 00 0 00 79499 308 29 10428 0 00 139 04 28 B 1997 2003 0 00 0 00 0 00 121 05 46 95 15 0 00 21 17 20 4 1997 2003 0 00 0 00 000 3 993 14733 4984 0 00 66 45 30 pa 1 1998 2004 0 00 0 00 0 001 1240 96 312 24 200 74 0 00 267 65 31 95 1999 D 0 00 0 00 0 00 7639 54 0 00 0 00 0 00 0 00 32 PLANER _ 0001 1989 1999 3500 00 0 00 0 00 96537 80 9349351 1544 29 0 001 154429 33 SAW 0001 1989 1999 2000 00 0 00 0 00 216131 20 21059056 3540 64 0 00 354064 34 WELDER 0001 1989 1999 0 00 0 00 0 00 318265137 31297174 529339 O00 529339 35 Grand Total 26500 00 0 00 22500 00 176342137 1401484 46 41553 82 4590594 41 F WIN Book Values Tar Values Other Values Z AMT Values 7 Jed 1 n Ia Ready el NMI 1 A 3 4 FA Report Applet User s Manual FA Asset Master File Name FAMASTER XLS Description The FA Asset Master PivotTable uses the data in the Fixed Assets Master FAMAx file to display summarized acquisition insur
10. SUBTOTAL SALES TAX TOTAL COST 000002 00000003 3 24889101 1995 1221 GREOOT 675967 0 3893 03 000002 00000004 3 24889102 19951221 __ 105001 7273 27 0 3664 69 121000002 00000005 3 24889103 19951221 SUNOOI 4318 74 0 279329 000002 00000006 3 24889104 1995 1221 CASHCA 249547 1497 28 13765 74 000002 00000007 3 24889105 19951221 4683 92 0 2206 05 1280 000002 00000008 3 24889106 1995 12 21 CASHPS 1710 72 0 7283 121000002 00000009 4 24889107 19951221 001 973 95 0 1030 65 11 000002 _ 00000010 4 24889108 19951221 105001 4821455 0 27910 M s RecoaTi Select View to show edit limiting records shown NUM FA Report Applet User s Manual Introduction Creating Microsoft Excel PivotTables The data in your query is displayed You can delete columns by selecting a column and pressing the Delete key You can also add a column by double clicking on the field name in the SOTH file NOTE You can also select which fields you want in your query in step 6 above Instead of selecting the entire table you can click the box next to the table you want and select the given fields from the list 14 Select the following fields e BATCH ID ORDER NUMBER e TRANSACTION TYPE INVOICE NUMBER e INVOICE DATE e CUSTOMER ID e SUBTOTAL e SALES TAX e TOTAL COST 15 Select Table from the main menu and choose Add tables The Add Table dialog bo
11. 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 FA Report Applet User s Manual 1 3 Fixed Assets Data Files You use the Fixed Assets system FA to record the assets that you purchase and to calculate and track the depreciation of those assets When you calculate depreciation on your assets you can post the amounts automatically to the General Ledger application FA Data Files The Fixed Assets Report Applet contains several spreadsheets that report information from the OSAS Fixed Assets data files The PivotTables in the Fixed Assets Report Applet are based on these data files FAMAxxx The Fixed Assets Master file stores information about your assets After you add an asset to the master file and place it in service the system stores calculated depreciation amounts in the file and calculates future depreciation based on the prior calculations Other information stored in the file includes insurance and assessed valuations asset location and tax information Data from the Fixed Assets Master file is used in the FA Asset Books FABOOKS XLS and FA Asset Master FAMASTER XLS PivotTables The Retirements Journal file stores information about assets that no longer in service Assets are stored in th
12. only invoices change Transaction Type from Allto 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 FA Report Applet User s Manual 1 23 Creating Microsoft Excel PivotTables Introduction The Insert Calculated Field dialog box appears Insert Calculated Field Formula 0 Delete BATCH ID ORDER NUMBER TRANSACTION TYPE INVOICE NUMBER INVOICE DATE CUSTOMER ID SUBTOTAL SALES TAX zi Insert Field 2 Enter the following information about the inserted field Enter Profit Dollars in the Name field e Enter SHIPPED QTY SELL UNIT PRICE SHIPPED QTY SELL UNIT COST COMPBNT in the Formula field 3 Click Add Insert Calculated Field x Name Profit Dollars Modify Formula SHIPPED QTY SELL UNIT_PRICE SHIPPED QT Delete Fields BATCH ID ORDER NUMBER TRANSACTION TYPE INVOICE NUMBER INVOICE DATE CUSTOMER ID SUBTOTAL SALES TAX zl Insert Field 1 24 FA 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 Pi
13. this PivotTable to review and analyze depreciation calculated and remaining asset value in each of the books Active Fields Default Field Type Page Row Column FA Report Applet User s Manual Field None Asset ID and Number Book Tax Other and A M T Book Tax Other and A M T Book Tax Other and A M T Book Tax Other and A M T Book Tax Other and A M T Book Tax Other and A M T Book Tax Other and A M T Book Tax Other and A M T Book Tax Other and A M T Beginning and Ending Years Salvage Value Other Bonus Depreciation 179 Expense Base Cost Accumulated Depreciation Year to Date Depreciation Accumulated Eligible Depr Annual Depreciation 3 9 FA Retired Asset Books Fixed Assets PivotTables FA Retired Asset Books PivotTable Sample 185 Ele Edt view Insert Format Tools Data Window xl A 21038 E Aria BivotTable E E 1 FA Retired Asset Books 2 3 Data 4 JAsset ID lAsset No Begin Xr Yr Salvage Value Other Bonus Depr 179 Expense Base Cost Accum Depr Taken Depr Accum Eligible Annual Depr 5 COMPUTER 0001 1993 1996 0 00 0 00 0 00 6329 84 5950 60 379 24 5950 60 0 00 B CRANE 0001 1989 1999 8000 00 0 00 0 00 42130259 406511 70 6790 89 406511 70 0 00 7 DRILL 0001 1989 1999 0 00 0 00 0 00 22016 54 21650 36 3
14. 151421 66 151421 66 182003 12 24 OFFICE 91 blank blank blank 2421 00 1 00 0 00 0 00 0 00 25 92 blank blank blank 332387 1 00 0 00 0 00 0 00 26 93 1 blank blank blank 1142 04 10 00 0 00 0 00 0 00 27 2 blank blank blank 794 99 1 00 794 99 0 00 0 00 28 3 blank blank blank 121 06 1 00 0 00 0 00 0 00 29 n blank blank blank 379 933 5 00 0 00 0 00 0 00 30 94 1 blank blank blank 1248 96 1 00 0 00 0 00 0 00 31 85 blank blank blank 7639 54 14 00 0 00 0 00 0 00 32 PLANER 10001 1 15 99 1 15 99 1 10 99 98537 80 2 00 75000 00 75000 00 125000 00 33 0001 1 15 99 1 15 99 1 10 99 216131 20 4 00 150000 00 150000 00 350000 00 34 WELDER 0001 1 15 99 1 15 98 1 10 99 318265 13 3 00 95000 00 115000 00 55632500 35 Grand Total 1763421 97 67 00 1384151 72 1378712 63 1881684 80 4 RIM Acquisition Data Yauation Data dal 1 wl Is Ready 3 8 FA Report Applet User s Manual FA Retired Asset Books File Name FARETBOK XLS Description The FA Retired Asset Books PivotTable uses the data in the Retirements Journal FARJx file to display asset value and depreciation amounts for retired assets You can use the tabs at the bottom of the current spreadsheet to display the values for the four depreciation books The data in the table is sorted by Asset ID Asset Number Begin Year and End Year but you can easily change the sort order You can use
15. 66 18 21650 36 0 00 B FORKLIFT 0001 1989 1999 5000 00 0 00 0 00 160000 00 15247401 2525 99 152474 01 0 00 9 LATHE 0001 1989 1999 0 00 0 00 0 00 151421 66 148903 21 2518 45 148903 21 0 00 10 PLANER 0001 1989 1999 3500 00 0 00 0 00 _ 98537 80 93493 51 1544 29 93493 51 0 00 11 SAW 0001 1989 1999 2000 00 0 00 0 00 216131 20 21059056 3540 64 210590 56 0 00 12 WELDER _ 0001 1989 1999 0 00 0 00 0 00 318265 13 312971 74 5293 39 312971 74 0 00 13 Grand Total 18500 00 0 00 0 00 1394004 76 1352545 69 22959 07 1352545 69 0 00 bi Book Values Tas Values Other Values AMT Values E x Jal E Ready caf sf A 3 10 FA Report Applet User s Manual File Name FARETMST XLS Description FA Retired Asset Master The FA Retired Asset Master PivotTable uses the data in the Retirements Journal FARJx file to display summarized value cost and proceeds information about each retired asset The data is sorted by Asset ID Asset Number Sequence Number Retirement Sequence Number and Retirement Date but you can easily change the order of the data You can use the data in this table to analyze the tax implications of your asset retirements Active Fields Acquisition Data Default Field Type Page Row Column FA Report Applet User s Manual Field None Asset ID Number and Sequence Number Retirement Sequence Number Retirement Date Retirement Quantity Retirement Amount Retirement Proce
16. 89101 001 4 4 343 55 475 686 528 544 002 1 1 907 53 1317 384 409 854 1003 7 7 22 01 20 7995 8 4735 1004 5 5 226 99 526 131 1495 705 005 2 2 161 14 381 645 441 01 24889101 19 19 1661 22 2721 6455 20148 0845 124889102 1001 3 3 840 83 1485 495 1933 995 002 3 3 152 381 645 688 935 003 4 4 171 55 417 96 985 64 24889102 Total 10 10 1164 38 2285 1 11207 2 24889103 001 3 3 348 0582 475 686 382 8834 1002 2 2 874 56 1317 384 885 648 003 5 5 0 51 381 256 905 24889103 10 10 1222 6182 1844 451 5218 328 24889104 001 10 10 1342 87 2417 166 10742 96 002 5 5 22 01 51 381 146 855 1003 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 1193 22 2285 1 9826 92 124889106 1001 5 5 145 98 342 144 980 82 24889106 Total 5 5 145 98 342 144 980 82 30 Grand Total 69 69 6979 2882 12473 1185 379074 2907 31 _ Id 4 M Sheet Sheet Sheet Sheets dad gt Ready peu NUM fisi You can also drag the selection fields from the Page area to the Row area to sort the data by those fields FA Report Applet User s Manual 1 31 Creating Microsoft Excel PivotTables Introduction More About Using PivotTables Feel free to experiment with the orientation of the fields on this sample report As you become more familiar with the tables and how to use them you can enjoy the benefits o
17. 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 2824889106 12 21 99 001 5 5 145 98 342 144 980 82 2924889106 Total 5 5 145 98 342 144 980 82 30 Grand Total 59 59 6979 2882 12473 1185 379074 2907 31 141 HN Sheets Sheet Sheet Sheet 7 14 Ready af NUM 7 1 30 FA Report Applet User s Manual Introduction Creating Microsoft Excel PivotTables Changing the Data Sort To change the order in which the data is displayed you can simply change the Row fields around For example our PivotTable is sorted by Invoice Number To sort it by Invoice Date instead click and drag the INVOICE_DATE field to the left of the INVOICE_NUMBER field The data is sorted by Invoice Date and is redisplayed Eile Edit View Insert Format Tools Data Window Help 18 T 0 8 10 so 4 _ o A Bivotrable B5 INVOICE NUMBER A B G H F 1 BATCH ID All y _ 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 248
18. AWINDOWS 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 FA 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 have set up and any ODBC reports or spreadsheets you have already set up to use the CONFIG TPM in its new location 2 Copy the CONFIG TPM file to the CAWINDOWS directory and leave a copy in its current location You do not need to change any Data Sources or reports you have set up but you need to make any changes in both files 3 Change the PivotTables provided with this report applet to use the CONFIG TPM file in its current location You can find instructions for doing this below If you choose methods 1 or 2 above you can load the PivotTables in Microsoft Excel and begin using them with your data by using the Refresh Data command in Excel If you choose option 3 follow the instructions below to point the PivotTable to the correct CONFIG TPM file Using a Different CONFIG TPM 2 2 If you store your CONFIG TPM file in a location other than the CAWINDOWS directory you will see this message when you attempt to refresh the data in any PivotTable included with this report applet Microsoft Excel BASIS BASIS ODBC Driver Default configuration Failed DeFault CONFIG C datadict Config tpm error duplicate or missing
19. ID All y 2 TRANSACTION y 3 4 INVOICE NUMBER INVOICE DATE ENTRY NUMBER Data Total 5 124889101 12721 99001 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 606 g Sum of Profit Dollars 528 544 10 002 Sum of ORDERED QTY 1 11 Sum of SHIPPED QTY SELL 1 12 Sum of UNIT COST COMPNT 907 53 18 Sum of UNIT_PRICE 1317 384 14 Sum of Profit Dollars 409 854 15 003 Sum of ORDERED QTY 7 16 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 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 Wi Sheer1 Shest2 7 Sheets 7 Ready FA Report Applet User s Manual Sum of SHIPPED QTY SELL Iq 1 29 Creating Microsoft Excel PivotTables Introduction Changing the Column Data You can change the data that appears in the columns in
20. OPEN SYSTEMS Accounting Software Fixed Assets ODBC Report Applet User s Manual PN 2210 FAO60 1998 Open Systems Holdings Corp rights reserved Document Number 2210 FAO600 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 Fixed Assets Data Files 1 5 Introduction to Pi
21. The FA Asset Books PivotTable uses the data in the Fixed Assets Master FAMAx file to display asset value and depreciation amounts for the books you choose You can use the tabs at the bottom of the current spreadsheet to display the values for the four depreciation books The data in the table is sorted by Asset ID Asset Number Sequence Number Begin Year and End Year but you can easily change the sort order You can use this PivotTable to review and analyze depreciation calculated and remaining asset value in each of the books Active Fields Default Field Type Page Row Column FA Report Applet User s Manual Field None Asset ID Number and Sequence Number Book Tax Other and A M T Beginning Year Book Tax Other and A M T Ending Year Book Tax Other and A M T Salvage Value Book Tax Other and A M T Other Bonus Depreciation Book Tax Other and A M T 179 Expense Book Tax Other and A M T Base Cost Book Tax Other and A M T Accumulated Depreciation Book Tax Other and A M T Year to Date Depreciation Book Tax Other and A M T Current Depreciation Book Tax Other and A M T Annual Depreciation 3 3 Asset Books Fixed Assets PivotTables FA Asset Books PivotTable Sample
22. ance assessment and in service information about each asset The table is made up of two spreadsheets You can access the data on the sheets by using the tabs at the bottom of the spreadsheet The data is sorted by Asset ID Asset Number Sequence Number and Acquisition Date but you can easily change the order of the data You can use the data in this table to analyze your insurance and replacement risks Active Fields Acquisition Data Default Field Type Page Row Column FA Report Applet User s Manual Field None Asset ID Number and Sequence Number Acquisition Date Retirement Date In Service Date Acquisition Cost Quantity Sales Tax Paid Accumulated Nondepreciable Cost Adjusted Cost Total Credits 3 5 FA Asset Master Fixed Assets PivotTables FA Asset Master Acquisition Data PivotTable Samples rosoft Excel Famaster olx 15 Eie Edt view Insert Format Tools Data Window Help laj xl Dc m em imes 312 0948 100 8 Fara 10 Or Ay eivotrable 9 mie cro A4 Y Asset ID B C D 6 K E F 1 FA Asset Master 2 Data 4 JAsset ID Asset Nu Retirement Date In Service Date Acqu
23. aster 2 3 Data 4 JAsset ID Asset Number Seg No Insure Value Date Assessment Date Replacement Date Acquisition Cost Qty Insured Value Assessed Value Replacement Value 5 AUTO 5 15 99 5 15 98 5 15 98 11000 00 1 00 8000 00 8000 00 12500 00 6 92 2 3 31 96 blank blank 1230 08 1 00 1000 00 0 00 0 00 7 95 2 1499 2 1 99 blank 35735 00 1 00 35735 00 35000 00 0 00 B BUILDING 411197 12 15 98 blank 197500 00 1 00 19750000 222790 00 0 00 9 COMPUTER 0001 6 10 93 6 10 93 11 15 93 6329 84 1 00 4500 00 6329 84 5800 00 10 91 4 1 95 blank blank 11021 00 1 00 11021 00 0 00 0 00 11 92 6 9 96 blank 7 1 98 5684 78 1 00 5684 78 0 00 4400 00 12 1 3 31 96 blank blank 7925 47 1 00 7925 47 0 00 0 00 13 93 1 6 2 97 blank blank 4782 17 1 00 4782 17 0 00 0 00 14 3 4 1 97 blank blank 8810 12 1 00 8810 12 0 00 0 00 15 94 1 blank blank blank 1276 44 1 00 1276 44 0 00 0 00 16 2 blank blank blank 10552 26 1 00 10552 26 0 00 0 00 17 95 1 5 22 99 5 22 99 blank 1276 44 1 00 1276 44 1300 00 0 00 18 2 12 1 99 12 1 99 blank 1055226 1 00 10552 26 10552 00 0 00 19 10001 3 2 89 3 2 89 1 1 92 42130259 200 42130259 421302 59 457200 34 20 DRILL 0001 3 2 89 3 2 89 1 1492 22016 54 1 00 22016 54 22016 54 28456 34 21 FORKLIFT 0001 3 2 89 3 2 89 14 92 160000 00 4 00 160000 00 160000 00 160000 00 22 LAND blank blank blank 45000 00 1 00 0 00 0 00 0 00 23 10001 3 2 89 3 2 89 1 1 82 15142156 2 00
24. eds Retirement Expenses Retirement Credits Acquisition Cost 3 11 FA Retired Asset Master Fixed Assets PivotTables FA Retired Asset Master PivotTable Samples X Microsoft Excel Faretmst of x File Edit View Insert Format Tools Data Window Help E A 28 aria suo B ZU EE Eivotrable Asset ID C AS 8 9 5 COMPUTER 0001 01 5 30 96 1 00 6329 84 300 00 150 00 253 19 0 00 B CRANE 0001 01 2 28 99 2 00 421302 59 0 00 0 00 33704 21 0 00 7 DRILL 0001 01 2 28 99 100 22016 54 0 00 0 00 1761 32 0 00 8 FORKLIFT 0001 01 2 28 99 4 00 160000 00 3500 00 365 00 12800 00 0 00 9 LATHE 0001 01 2 28 99 2 00 151421 66 0 00 0 00 12113 73 0 00 10 PLANER 0001 01 2128 99 2 00 98537 80 0 00 0 00 7883 02 0 00 11 SAW 0001 01 2 28 99 4 00 216131 20 0 00 0 00 17290 50 0 00 12 WELDER _ 0001 01 2 28 99 3 00 318265 13 0 00 0 00 25461 21 0 00 13 Grand Total 19 00 1394004 76 3800 00 515 00 111267 18 0 00 I 4 Sheett 14 Ready Data 4 JAsset ID sset No Seq No Retire Seg Date Retire Qty Retire Amt Retire Proceed Retire Expenses Retire Credits Acquisition Cost 3 12 FA Report Applet User s Manual accessing data Microsoft Excel 1 9 D data files in Fixed Assets 1 5 Data S
25. elds 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 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 BATCH ID field to the left of TRANSACTION TYPE field and drop it there 1 28 FA 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 SRY o gt Me Q1 CA v aria B 7U 9 9 S 5 A PivotTable 1 iif E4 Total D E 1 BATCH
26. f viewing your data in new and different ways For more information about PivotTables see the Microsoft Excel documentation or online help 1 32 FA Report Applet User s Manual Installation You can put the Fixed Assets ODBC Report Applet on your system by installing it through Resource Manager The installation process is described in this section The Fixed Assets Report Applet needs a minimum of 120 kilobytes 120KB for installation You must also have installed Fixed Assets 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 Fixed Assets application before you install this report applet The installation will treat the report applet as though you are reinstalling Fixed Assets This is normal behavior When you install the report applet Resource Manager copies the PivotTables to the directory where your Fixed Assets 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
27. is file to maintain a record of the depreciation taken on the asset as well as the reason for the asset s retirement This data is displayed on the FA Retired Asset Books FARETBOK XLS and FA Retired Asset Master FARETMST XLS PivotTables FA Report Applet User s Manual 1 5 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 Fixed Assets 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 FA Report Applet User s Manual 1 7 Creating Microsoft Excel PivotTables Read this section for an e
28. isition Cost Qty Sales Tax Paid Accum Nondepr Cost Adjusted Cost Total Credits 5 AUTO blank 5 14 96 11000 00 1 660 455 11000 0 6 92 blank 3 31 96 1230 08 1 0 0 1230 08 0 7 95 blank 2 1 99 3573500 1 0 0 35735 0 B BUILDING blank AA 1 97 19750000 1 0 12763 197500 0 9 COMPUTER 0001 5 30 96 5 10 93 6329 84 1 0 6329 84 253 19 10 91 blank 4195 11021 00 1 0 11021 0 11 92 blank 6 9 96 5684 78 1 0 5684 78 0 12 1 3 31 96 blank 3 31 96 7925 47 1 0 7925 47 0 13 93 yl 6 2 97 blank 6 2 97 4782 17 1 0 4782 17 0 14 3 4 1797 blank 441 97 8810 12 _ 1 0 8810 12 0 15 94 1 5 17 98 blank 5 17 98 1276 44 1 0 1276 44 0 16 2 12 1 98 blank 12 1 98 10552 26 1 0 10552 26 0 17 95 1 5 17 99 blank 5117 99 1276 44 1 0 1276 44 0 18 2 12 1 99 blank 12 1 99 10552 25 1 0 10552 26 0 19 CRANE 0001 3 2 89 2 28 99 3 2 89 42130259 2 0 421302 59 33704 21 20 DRILL 0001 3 2 89 2 28 99 3 2 89 22016 54 1 0 22016 54 1761 32 21 FORKLIFT 10001 3 2 89 2 28 99 3 2 89 160000 00 4 0 160000 12800 22 9 22 92 blank 10 22 92 45000 00 1 0 45000 0 23 LATHE 0001 3 2 89 2 28 99 3 2 89 15142166 2 0 151421 66 12113 73 24 OFFICE 91 441 35 blank 4 1 95 2421 00 1 0 2421 0 ES 92 3 31 96 blank 3 31 96 3323 67 1 0 3323 67 0 26 93 1 7 20 97 blank 7 20 97 114204 10 0 1142 04 0 27 2 12 16 97 blank 12 16 97 794 99 1 0 794 99 0 28 3 5 10 97 blank 5 10 97 12106 1 0 121 06 0 29 4 7 15 97 blank 715497 37293 5 0 379 93 a 30 94 1 4 13 98
29. ivotTables The PivotTable is redisplayed reflecting your changes X Microsoft Excel Book1 iO x 83 File Edit View Insert Format Tools Data Window amp lt 7 2 2 da 4 10 2 Arial 10 B z u GE eR 5 G0 A pivotable INVOICE DATE INVOICE NUMBER INVOICE DATE IENTRY NUMBER 24889101 Sum of ORDERED QTY Sum of SHIPPED QTY SELL Sum of UNIT COST COMPNT Sum of UNIT PRICE Sum of Profit Dollars Sum of ORDERED QTY Sum of SHIPPED QTY SELL Sum of UNIT COST COMPNT Sum of UNIT PRICE Sum of Profit Dollars Sum of ORDERED QTY Sum of SHIPPED QTY SELL Sum of UNIT COST COMPNT Sum of UNIT PRICE Sum of Profit Dollars Sum of ORDERED QTY Sum of SHIPPED QTY SELL Sum of UNIT COST COMPNT Sum of UNIT PRICE Sum of Profit Dollars Sum of ORDERED QTY Sum of SHIPPED QTY SELL Sum of UNIT COST COMPNT Sum of UNIT PRICE Sum of Profit Dollars R H N N 0148 0845 20148 0845 35 24888102 12 21 88 001 Sum of ORDERED QTY 3 3 36 Sum of SHIPPED GTY SELL 3 3 4 gt KW Sheet1 Sheet2 Sheets 7 1 1 gt Ready FA 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 Fi
30. lude 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 FA Report Applet User s Manual 1 13 Creating Microsoft Excel PivotTables Introduction 12 Click Next gt The Sort Order box appears Query Wizard Sort Order Specify how you want your data sorted If you don t want to sort the data click Next Sortb Ascending 3 Descending Then by Ascending Descending Then by Ascending Descending zl lt Back Cancel Use the Sort Order dialog box to select how the data is sorted For example select a field in Sort by and check Ascending or Descending Select more fields and orders for hierarchical sorts For now don t enter any sort fields 13 Click Finish You are returned to the Microsoft Query screen Microsoft Query 5 Eile Edit View Format Table Criteria Records Window Help Ejea y 4 fa vs x wE a Query 1 from OSAS Data 0 BATCH MORDER NUM TRANSACTIUN TIINVOICE NUMHINVOICE DANCUSTOMER ID
31. of SHIPPED Q ENTRY NE Am of UNIT COST Invoice EnTRY_N E E invorce unir co custome PR1 1 Finish Click Next The PivotTable Wizard Step 4 dialog box appears PivotTable Wizard Step 4 of 4 HE Where do you want to put the PivotTable New worksheet Existing worksheet 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 or in a different worksheet Accept the given options and click Finish FA Report Applet User s Manual Introduction Creating Microsoft Excel PivotTables The PivotTable is displayed 83 File Edit View Insert Format Tools Data Window Hi DG M SRY BAS oc PivatTable e 888 A 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 E 5 Sum of UNIT 226 99 Sum of UNIT 526 131 Sum of OR _ 2 Sum of SH 2 114 pl Sheet1 Shest2 Sheet3 1 Highlight rows and columns to shift them around To display
32. ource creating a new source 1 11 Fast Connect 1 12 No Shadow Dictionary Consistency Check 1 12 selecting a data source 1 12 F FA Asset Books PivotTable description 3 9 files used inj 1 5 sample report 3 4 screen 9 4 FA Asset Master PivotTable description 3 5 files used inj 1 5 sample report 3 6 3 8 screen 3 6 3 8 FA Retired Asset Books PivotTable description 3 9 files used inj 1 5 sample report 3 10 screen 3 10 FA Retired Asset Master PivotTable description 3 11 files used in 1 5 sample report 3 12 screen 3 12 FA Report Applet User s Manual Index FABOOKS XLS data files 1 5 description 3 3 FAMASTER XLS data files 1 5 description 3 5 file description 1 5 FARETBOK XLS data files 1 5 description 3 9 FARETMST XLS data files 1 5 description 3 11 FARJxxx file description 1 5 Fast Connect Microsoft Query 1 12 Fixed Assets data files 1 5 description 1 5 installing 2 1 system requirements 2 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 IX 1 Index Microsoft Query 1 9 adding tables 1 15 building a query 1 10 Filtering data 1 13 joining tables 1 16 Selecting atable 1 13 Sorting data 1 14 N No Shadow Dictionary Consistency Check Microsoft Query 1 12 ODBC Kit definition 1 3 OSAS general information 1 3
33. 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 2121 D c B 7z 0 A E e gt Data INVOICE NUMBERIINVOICE DATE ENTRY NUMBER Sum 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 y 907 53 1317 384 409 854 003 7 7 22 01 20 7995 8 4735 004 5 5 226 99 526 131 1495 705 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 1002 3 3 152 381 645 688 935 E 1003 4 4 171 55 417 96 985 64 24889102 Total 10 10 1164 38 2285 1 11207 2 16 24889103 12 21 99 001 3 348 0582 475 686 382 8834 1002 2 2 874 56 1317 384 885 648 003 5 5 0 51 381 256 905 24889103 Total 10 10 1222 6182 1844 451 6218 328 24889104 12 21 99 001 10 10 1342 87 2417 166 10742 96 002 5 5 22 01 51 381 146 855 003 1 1 226
34. u 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 lt New Data Source gt dBASE Files not sharable Excel Files not sharable FoxPro Files not sharable MS Access 97 Database not sharable 0545 Text Files not sharable Cancel Browse Options dd El Use the Query Wizard to create edit queries 10 Select the data source you set up in the previous steps 1 12 FA Report Applet User s Manual Introduction Creating Microsoft Excel PivotTables The Choose Columns screen appears Query Wizard Choose Columns x What columns of data do you want to include in your query Available tables and columns Columns in your query SORH BATCH ID SORL ORDER_NUMBER SOSL TRANSACTION TYPE c TERMS CODE E SOTB Lx Group EH SOTD Preview of data in selected column El 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 21x Filter the data to specify which rows to inc
35. urce 121 x Databases Queries dunning Cancel pivot Browse didi Options El Use the Query Wizard to create edit queries The Choose Columns box under Query appears Query Wizard Choose Columns 21x What columns of data do you want to include in your query Available tables and columns Columns in your query BATCH ID ORDER NUMBER TRANSACTION TYPE INVOICE NUMBER 8 ARCC Ea INVOICE DATE EH ARCD 1 CUSTOMER ID 8 ARCD 2 SUBTOTAL FLARED 3 il SALES TAX _ Preview Now Beck Cancel FA Report Applet User s Manual 1 19 Creating Microsoft Excel PivotTables Introduction 6 Click Next gt The query columns are displayed 7 Click Next gt to pass by Filter Data and Sort Order options The Query Wizard Finish dialog box appears Query Wizard Finish 21x What would you like to do next Save Query View data or edit query in Microsoft Query lt Back Cancel 8 Select Return Data to Microsoft Excel and click Finish You are returned to the PivotTable Wizard Step 2 dialog box PivotTable Wizard Step 2 of 4 2 Where is your external data stored Get Data Data fields been retrieved Cancel lt Back 1 Einish 9 Click Next gt 1 20 FA Report Applet User s Manual Introduction 10 Creating Microsoft Excel PivotTables The PivotTable Wizard Step 3 dialog box appears
36. votTable is displayed with the Sum of Profit Dollars field 1515 1 lt 7 co AR x ALL LM 4 10 aria B ZU H SA 55 8 ml Oa A PivotTable aia hd A B D E E Giaj 1 TRANSACTION TYT AII 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 686 475 686 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 18 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 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 99 Sum of ORDERED QTY 18 19
37. votTables 1 7 Creating Microsoft Excel PivotTables 1 9 Installation 2 1 Fixed Assets PivotTables FA Asset Books 3 3 FA Asset Master 3 5 FA Retired Asset Books 3 9 FA Retired Asset Master 3 11 Index FA Report Applet User s Manual iii Introduction General Information Fixed Assets Data Files Introduction to PivotTables Creating Microsoft Excel PivotTables FA 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 provided for each of the major data
38. x appears Close SOTH zi Options Dictionary FA 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 FA Report Applet User s Manual Introduction Creating Microsoft Excel PivotTables Using the Query in Microsoft Excel 1 Start Excel and open a new worksheet Ele Edt view Insert Format Tools Data Window Help le x JOSE SRY 5 c wWwirf Z iy B 10 aria 8
39. xercise 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 FA Report Applet User s Manual 1 9 Creating Microsoft Excel PivotTables Introduction Building a Query For a PivotTable 1 Start Microsoft Query Zr Microsoft Query x oud 5 72 4 amp 3 irn 3 Ies pese 2 Under the File menu select New The Choose Data Source screen appears Databases Queries rn ed dBASE Files not sharable Excel Files not sharable FoxPro Files not sharable MS Access 97 Database not sharable 0545 Text Files not sharable El Use the Query Wizard to create edit queries FA Report Applet User s Manual Introduction Creating Microsoft Excel PivotTables 3 Select lt New Data Source gt and click OK The Create New Data Source screen appears Create New Data Source x

Fixed Assets (FA)

Contents

Download Pdf Manuals

Related Search

Related Contents