General Ledger (GL)
Contents
1. Source Code jest Credit Amt Debit Amt 11838 80 0 00 0 0 0 0 549 45 6934 94 0 0 25039 4 11289 91 0 00 351 64 0 00 31237 27 0 00 21 15 15 8 165 HOLDING ACCT Dec Amortization Dec Amortization Allocated Amortization 000030 12 1 99 Allocated Amortization 000031 12 1 99 Allocated Amortization 000032 12 1 99 000037 12 4 99 Transfer of Funds 0 000038 12 4 99 Transfer of Funds 000039 12 4 99 000040 12 4 99 806000 Stop Payment Charge 7 50 000041 12 499 100000 Pymts to Affiliates 1141 5 0 00 olnlolnlo Jlolwv ololv o eIumjojmio o 12 ad Bag E tns ceo 2 2 8 8 8 en e ojolo o Q i e e 000042 12 4 98 201000 Pymts to Affiliates 0 E 000043 1 16 99 100000 Period 01 Activit 00 3 10 GL Report Applet User s Manual A accessing data Microsoft Excel 1 9 D data files in General Ledger 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 F Fast Connect Microsoft Query 1 12 G General Ledger data files 1 5 description 1 5 installing 2 1 system requirements 2 1 GL Budget Detail PivotTable description 3 3 files used in 1 5 sample report 3 4 screen 3 4 GL Comparative Analysi2. 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 C New worksheet Existing worksheet gt Click Finish to create your PivotTable Cancel Options lt Back Next gt 12 The last step lets you create the PivotTable either in the existing worksheet in a different worksheet Accept the given options and click Finish GL 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 SRY BAS oc PivatTable e s EG INVOICE INVOICE ENTRY N Data 1 1 Grand Total 24889101 12 21 95 Sum of OR 4 Sum of SH E 4 Sum of UNIT COMP 343 55 Sum of UNIT 475 686 475 686 Sum of OR 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 4 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 T
3. Browse didi Options El IV 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 mi SALES_TAX_ m Preview Now Beck Cancel GL 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 m 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 21x Where is your external data stored Get Data Data fields have been retrieved Cancel lt Back i Finish 9 Click Next gt 1 20 GL Report Applet User s Manual Introduction 10 PivotTable Wizard Step 3 of 4 HE Le Creating Microsoft Excel PivotTables The PivotTable Wizard Step 3 dialog box appears Construct you
4. online help 1 32 GL Report Applet User s Manual Installation You can put the General Ledger ODBC Report Applet on your system by installing it through Resource Manager The installation process is described in this section The General Ledger Report Applet needs a minimum of 487 kilobytes 487KB for installation You must also have installed General Ledger 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 General Ledger application before you install this report applet The installation will treat the report applet as though you are reinstalling General Ledger This is normal behavior When you install the report applet Resource Manager copies the PivotTables to the directory where your General Ledger 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 CAWINDOWS directory If your CONFIG TPM file is stored in a different directory you have three choices for using the
5. 000 000 000 000 000 000 000 000 000 000 PUZ ANE NI HSV9 0010019 000 00 001 098 88 881 00 02Z 06 221 3258 00 51 CEN LIST Zr ISE OS psp 15 ANYS NI HSW9 000001 EL ponad ZL poued ponad OL ponad 6 8 12 pousd gpouag spousd TE PoWed cPous soueeg EA E eed 239 q 15928103 19 Emm o N s gt i H 9 3 3 3 Y bY lt SER s lt mw oo BM IE SUT AUS BD Rem 4 pesu 3p3 ad EET 199 yosorly GL Report Applet User s Manual 3 8 File Name GLJRNL XLS Description GL Journal The GL Journal PivotTable uses the data in the General Ledger Journal GLJRx file to display the posted and unposted general ledger entries on file The report is sorted by Entry Number but you can easily change the order of the sort Active Fields Default Field Type Page Row Column GL Report Applet User s Manual Field GL Period Entry Number Entry Date GL Account Number Description Credit Amount Debit Amount 3 9 GL Journal GL PivotTables GL Journal PivotTable Sample X Microsoft Excel Gljrn iO x S Fie Edit view Insert Format Tools Data Window Help 8 DG E 587 o o amp w rfi d 96 3 aria rio B ZU eivotrable AG Gy gt L a El A3
6. 6 eczrsE 0SZ908l Or Olp l 6 8 6889164 0692924 000 318YAYd ONICIOHHLIAA 31v1S OOFEDZ 62 000 000 S6t cZ4l 8 1 1 rp SSE 8 91 CP LOOZI 62 01 69 0851 Ol PEZSI 6l6cccE 8202291 17 99791 000 318WAWYd ONICIOHHLIAA v2I3 DOZE0Z Bc 000 000 EZ tSL6t 8 E86Z996 FS62F 6r S SBy 9078988 109 gt 8611106 tcccez 681 _ 000 318vAvd ONIOIOHHLIMA TV43934 QO0E0Z Z 000 00 0 000 000 000 000 000 000 000 000 000 000 000 000 INNOIIY SNIBY319 TIOSAYd 00020292 000 000 82866 99 9822 1 9 987982 OLEZ6ZE FC 26 197786 06 PEEZL E E69 1 8 10 S31WMI43W 318vAYd SINNOIIW 000102 SC 000 000 ZIGESGL Su 8 7L ZELIZ OLEttIL 786 11 9299601 cr Pell 81 995 52 1871 C99BE BP BZLEZ Viv 99LGEZ 3081 F18YAYd SINNOIIW 000002 PE 000 BFF eso 8S CEE BFF BFF eso CES 89 9 es eso eso 00 05 9 NOLYZILYOWY 000181 EC 000 000 000 000 000 000 000 000 000 000 000 000 000 00 00057 THAAGOOS 000081 2 000 000 20712927 201c9c 2012927 2012907 Z0 LZ9Z 2071292 101 LO L9EZ 1019 LO L9EZ LO LIEZ LE GFZCE S IS0W010v 3d330 WNIIY 005751 be 000 000 000 000 000 000 000 677986 000 000 000 000 000 TE 966r8 5311800115 000751 000 000 LLS0rv OO 6Er O0tecr O0 E6Er ON GER 27 19969C SNIHOVA 391430 4d30 UNIV QOSESL 000 000 S6 682 000 000 000 000 000 000 2895iz
7. 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 69 69 6979 2882 12473 1185 379074 2907 31 I HN Sheets Sheet Sheet2 Sheet 7 ad zii Ready I esf NUM 7 1 30 GL 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 E File Edit View Insert Format Tools Data Window Help 18 Ae z T 100 0 70 so 9 EE o A Bivotrable 5 INVOICE NUMBER A B G H F 1 BATCH ID All y _ TRANSACTION Data INVOICE DATE INVOICE NUMBIENTRY NUMBER Sum of ORDERED Sum of SHIPPED_QTY_SELL Sum of UNIT COST COMPNT Sum of UNIT PRICE Sum of Profit Dollars 12 21 99 24889101 001 4 4 343 55 475 686 528 544 002 1 1 907 53 1317 384 409 854 1003 7 7 22 01 20 7995 8 4735 1004
8. PivotTables supplied with the report applet GL 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 file GL Report Applet User s Manual Installation When you click on OK the BASIS ODBC Driver Data Source Setup dia
9. 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 81 2 D c eo 0 B 7z 0 A E e Data INVOICE NUMBERIINVOICE DATE 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 1002 1 y 907 53 1317 384 409 854 003 7 7 22 01 20 7995 8 4735 004 5 5 226 99 526 131 1495 705 1005 2 2 161 14 381 645 441 01 24889101 Total 19 19 1661 22 2721 6455 20148 0845 24889102 12 21 99 001 3 3 840 83 1485 495 1933 995 002 3 3 152 381 645 688 935 E 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 3 348 0582 475 686 382 8834 1002 2 2 874 56 1317 384 885 648 003 5 5 D 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 1003 1 1 226 99 526 131 299 141 24889104 Total 16 16 1591 87 2994 678 22444 928 4 24889105 12 21 99 001 1 1 855 61 1485 495 629
10. 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_ID field press and hold the left mouse button As you drag the BATCH_ID field around the table the cursor changes to show where you can drop it If the cursor looks like a block with an X over it you will remove the field from the table by dropping it there 2 Drag the BATCH_ID field to the left of the TRANSACTION_TYPE field and drop it there 1 28 GL 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 28 o amp z HA d Q1 CA v aria rio B ZU 95 9 9 E S 5 A PivotTable 7 1 i iif 030 E4 y Total A B c D E 1 BATCH ID All y 2 TRANSACTION TY All y 3 4 INVOICE NUMBER INVOICE DATE ENTRY NUMBER Data Total 5 124889101 1272
11. the sort order You can use this PivotTable to review and analyze your forecast figures or use these forecasted figures to perform what if analyses or calculate financial ratios Active Fields Default Field Type Field Page None Row GL Account Number Description Column Beginning Balance Period 1 13 Amounts GL Report Applet User s Manual 3 7 GL PivotTables GL Forecast Detail GL Forecast Detail PivotTable Sample Asse ji Apesa 4 1 Hays M Po 0 000 19 8 8 9 90 80F86S 6c FZS9rF9 BE EEL9S9 Z LvESC9 06 E 0 Sr99c9 60 866 89 SF LOr r9 S9 SSIEIS C9 EZr6cy 000 SLOVYLNOD Q313 1dAO2 DNI 000 0 6F 000 000 Os e89p1Z 81 199577 80 01907E 8 17956 V8 IVOLEZ ZE 99866C SI S6SFSZ 06 ZIEVPZ 92 DEZPLE Z8 E66902 2S 176 000 S3TvS W138 000107 BF 000 000 000 000 000 000 000 000 000 000 000 000 000 12 SEL888 SONINYY3 Q3NIYL34 DOOrDE ZF 000 000 000 000 000 000 000 000 000 000 000 000 000 89 SE66L SON30IAIG OO0 0 Sr 000 000 000 000 000 000 000 000 000 000 000 000 000 10 66Ec6r TvLldv2 NI Qiyd TeNOLLIQGY 000708 SF 000 000 000 000 000 000 000 000 000 000 000 000 000 9s SCr26V 219015 034833380 DODLOE FF 000 000 000 000 000 000 000 000 000 000 000 000 00
12. 0 SS EFBIZEI 219018 NOWWOI 00000 EF 1000 000 000 000 000 000 000 000 000 000 000 000 000 000 017 40 NOLLYOd IN3HHNI 5531 QOOESZ cr 000 00 0 8L t198 8171958 87158 87158 8158 81 t198 8l rise 8L rise 87158 87158 8L t198 02 098015 OAL 318VAYd LON 000292 ir 000 000 vOZ2vCOL PO 201 pO vOZvCOL vOZrCOL vOZvcOL vOZvcOL 201 vOZrCOL vOZvCOL 0 ZIEH F18YAYd SLON 000152 OF 000 000 S0S2vci 909 1 SOSZvC SO 9ZbZL 509 1 509 701 509 71 609 01 SO92bzL 509 7 9092 318vAvd ANOA 000052 6E 000 000 000 000 000 000 000 000 000 000 000 000 000 000 40 NOLLYOd IN3HUNI 000012 BE 000 000 000 000 000 000 000 000 000 000 000 000 000 000 1 3AO NI 03883330 000802 000 000 000 000 000 000 000 000 000 000 000 000 000 000 d18vAvd XYL 3WOINI 31v1S 000202 9 000 000 000 000 000 000 000 000 000 000 000 000 000 000 318YAYd SWOONI T3033 000902 SE 000 000 000 000 000 000 000 000 000 000 000 000 000 000 318vAvd ONNA NOISN3d 00902 FE 000 000 000 000 000 000 000 000 000 000 000 000 000 000 318vAYd AWAA OSLINA 000702 000 000 2858 6090065 v8SsEcscc 9 801 0 9911 007961 SE 9SIOL 99 Z8E6 9686956 829166 BIELZSI 000 318WAWd SAWS 008 02 ZE 000 000 000 000 000 000 000 000 000 000 000 000 000 000 alavAvd INS 002602 LE 000 000 sr 1919 9E9619 POSELOL LCSEOZ 0015 POLES wir EZ DZSp 1921296 LEOS 266er 000 vin 009207 OE 000 000 6919964 9816264 v6ll98E vZ 18L6l OZ 0
13. 00 000 000 000 000 000 000 000 000 000 000 000 000 0000057 MIM0IOO9 000081 zZ 000 000 201292 201292 20712927 20712927 12012927 20712927 LO L9EZ 101 101 LO L9EZ I0 1967 LE GPZCE SdgoWolnv sd3a ANI 005751 1 000 000 000 000 000 000 000 67296 000 000 000 000 000 ZE 96668 53118040115 000751 OZ 000 000 A1LS vy EcOvv O0t6tr OD SEP ON EGER Q0tety SE EEL GE EELb SE CEL PSE EEL 19969 SNIHOVA 391440 4439 WNIIV QOSESL 6L 000 000 56 682 000 000 000 EZBSIIE 000 000 000 285527 000 49 WCEGF S3NIHOVIA 391550 DO0es Bl 000 000 T 940EV 2 Z 920El 2Z 920El 9 0 1 Z 920 l AZ OLDEL Z 920El 6S DE0ZL 6Z 26844 6Z 26814 627268 Ol ICLOES dI003 9 AYINIHOVA 4d30 WOO ooszsi 21 000 000 000 000 000 000 000 000 991879521 8296694 000 000 000000 68292881 INMdINO3 AH3NIHOVIA reme 000 000 000 000 000 000 000 000 000 00 05229 000 000 000 100000097 ONY 000151 SL 000 000 SC 1092 996857 6699812 918295 Ep vIOSE 8668651 605982 27tselvy Z9SZIOI E8S98ZL 99 86 92597195 S3L18NI3S NIYLIAYYN 000901 FL 000 000 000 000 000 000 000 000 000 000 000 000 000 000 S3SN3dx3 000501 000 000 99tcc8 VIEZEIZ GEEMI LI pacer 90 1029 9 8 Br 9001 ZvESC tcv89ct O9EZOS Or LOrbZ 9SrESSS 0009 0O3HSINIA AHOIN3ANI 0077012 000 000 28 ZELS TE ESHS ZTESPSZI FL 2607 82 ZEEL 16 6792 8220007 69 Z69 6911016 011698 1Z Z2ZSe0L SS3908d NIAYOMA AMOLNSANI 00zroL bb 000 0
14. 00 180599 _ 096682 ZE SBFIE 195 9 88 2 8766 0 0669 091100 1 6 189972 190 1 vL 9 66 STVIMSLVW AHOINSANI O00y01 OL 000 000 16 61 Q0 88 O0 0E LM 09 ET 10121 EZ 1862 Lost es 18 DLERZ 1830 404 39NWAAO Tv 000201 06 000 000 05 566 OOO0rr 00009 09 880c 099008 Oc89cl 090998 SZ LOL es ezgerl 92 0Scc 068207 000901 dl8vAl393d SINNOIIW 00010 8 000 0000 000l 00 001 0000 00752 DO DE 000 0009 00 Sr 000 00 s 00 SL 0000 HSWI ALL3d 005001 Z 000 000 0000 000 000 000 000 000 000 000 000 000 000 000 PUZ INVB NIHSYI 0010019 000 00 001 _09 8 89 88L 00 027 06 22V EET 298 00 51 05911 Na TUI 05197 TYNOLLYN 151 NI HSY9 000001 El ponad Zi Ponad 11 ponad OL POUB 16 pousg BPOWed Pouad 19 Spousg Epousg 10 poueq poleg MBuluuiBsg jenisy 3530 wunooov 19 F I Ex eseduo 19 Esa o N AA A l H 9 y 3 E Y bY E lt Ve TT 0 BER EA rey 21 BM y lt o oder rer eH mopum eyed spoI 490663 wa ad y ETEEWEIFERTIETES GL Report Applet User s Manual 3 6 GL Forecast Detail File Name GLFORCST XLS Description The GL Forecast Detail PivotTable uses the data in the General Ledger Master GLMAx file to display the forecast amounts on file for each GL account The data is sorted by GL Account Number and Description but you can easily change
15. 000 000 000 000 000 000 000 318vAvd ONNA NOISN3d 00090z re 000 000 000 000 000 000 000 000 000 000 000 000 000 000 TVA AVA OSLINA 000702 EE 000 000 FE 2899 509006 v9 1 801 0Z y99lLl 6 SEOSIOI G9 28E6 9682997 829166 BLELZSI 000 318WAWd XYL S3TvS OO8 0Z CE 000 000 000 000 000 000 000 000 000 000 000 000 000 000 318vAvd INS 00 0 1 000 000 61 1919 9E9619 PYSELOL 12509 EP OOS POLES wir 0267 92 1 96 ee 1 05 26 6897 000 318vAvd via 09207 DE 000 000 6919964 9816264 PELIIBE PA 18161 02 DEb6l O6Z PSE 05 9081 OW Olp l 62vv89E 6989164 065292 000 318WAYd ONICIOHHLIMA 31V1S QOFEDZ 62 000 000 8etUcZl 28 21EZl 0 8 91 Zh 001 62 BEDIE 69 08SL Ol PETSI 6L6EZZE 8272294 99791 000 318WAWYd ONIOTOHHLIMA 9913 DOZE0Z BZ 000 000 EC pol6b 6 8 67996 6v G SOvy 9 6988 sz 95915 l Scstr 86 11106 EZB 92 681 000 318vAvd ONIQTIOHHLIMA Tv43034 QO0E0Z ZZ 000 000 000 000 000 000 000 000 000 000 000 000 000 000 INNOIIY 9NIHY319 TIOHAYd 00020292 10070 000 EA AP 987982 OLEZ6ZE Z9SBFLE PO 26 197786 06 PEELL E69 128871 19 SalvNldsv 318vAvd SINNOIIV 000102 SZ 000 000 ZI SEGGI SSPGBEE ZL ZELIZ 22v86ll 9299601 8 81 9995 EL C998E 8Bt BZLET 1 99LSEZ 30v8l 318vAvd SINNOIIW 000002 PE 000 CER ess 959 eso CES eso CES 959 CES eso CEN CES 00 05 9 NOLLWZLLYOWY 000181 EC 0
16. 000 19 WCEGF SNIHOVA 391430 QO0ES BL 000 000 LZILDEL ZC 920E 2 Z 920El 27 OLDEL 9 0 1 2Z 920 l 9 0 1 65 0E0cl 62 26814 6Z 26844 6c Z26814 0L LZLDES dINO3 9 AYINIHIVA 8d30 WOO ooszsi 2b 000 000 000 000 000 000 000 000 99 187521 8296694 000 000 0070000 TE FZ9Z9E1 ANAWdINOS 3 AU3NIHIYW 000251 Sb 000 000 000 000 000 000 000 000 000 00 05265 000 000 000 00 000097 QONY T 000151 8 000 000 SE 10947 996992 66 SPBIZ 978 95 86 68651 60 99E7 LEGGI 2995010 8 99871 999 66 Sc 99199 S3L18N93S NISVLIAYVA 000901 Fk 000 000 000 000 000 000 000 000 000 000 000 000 000 000 S3SN3dX3 Qlvd38d 000501 000 000 99 EEZ8 POEZELC LIEBTE SOLOZSE 98EcrSP 87 9001 QZvtSc PEGZE OY9EZOS LOrbz TISPEBSE 50009 0O3HSINIA AHOIN3ANI OOrrOL EL 000 00 0 c8 ZELS CE ESHS CE SFSCI vL 2607 82 ZEEL le6v97 91 8rREZ 8220007 6SEZ69 691101 811698 12 207GE0L SS3908d NIAYOAA AHOLN3ANI OOzrOL bb 000 000 18 0599 FC 056682 9BPLE 199 68 2 6866 02 06651 05 11221 1899907 190 11 7 SEEB66 STVIMALVI AHOLNSANI 000y01 000 000 16 61 00 88 00 0 LM 09 ER 10121 EZ ZOZ 1862 MEA 8918 OL ERZ 1830 404 39NVAAOTIV 00020 6 000 000 09 566 00 0077 00 0051 098807 099008 0 891 090998 921410 99 066 SZ0Scc 068 07 05 95121 318vAl3238 SINNOI9W 0001018 000 0000 _ 00 01 00 001 0000 00 52 DO DE 00 09 0005 00 5 00 0E DO Sc 00 Sl 0000 HSYI ALL3d 009001 2 000 000 0000 000
17. 000003 3 24889101 1995 1221 GREOOT 675967 0 3893 03 000002 00000004 3 24889102 19951221 __ 105001 7273 27 0 3664 69 000002 00000005 3 24889103 1995 12 21 SUNOOI 4318 74 0 279329 000002 00000006 3 24889104 1995 1221 CASHCA 24954 7 1497 28 1376574 000002 00000007 3 24889105 1995 1221 CASHMN 4683 92 0 2206 05 000002 00000008 3 24889106 1995 12 21 CASHPS 1710 72 0 7283 000002 00000009 4 24889107 1995 12 21 ACEOO1 373 96 0 1030 65 1288 000002 00000010 4 24889108 1995 12 21 105001 4821455 0 27910 M s RecoaTi PT Select View Criteria to show edit criteria limiting records shown NUM GL Report Applet User s Manual Introduction Creating Microsoft Excel PivotTables The data in your query is displayed You can delete columns by selecting a column and pressing the Delete key You can also add a column by double clicking on the field name in the SOTH file NOTE You can also select which fields you want in your query in step 6 above Instead of selecting the entire table you can click the box next to the table you want and select the given fields from the list 14 Select the following fields BATCH ID ORDER NUMBER e TRANSACTION TYPE INVOICE NUMBER INVOICE DATE CUSTOMER ID SUBTOTAL SALES TAX TOTAL COST 15 Select Table from the main menu and choose Add tables The Add Table dialog box appears Add Tables L L Close
18. 1 99001 Sum of ORDERED_QTY 4 6 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 99 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 24869102 12 21 99 001 Sum of ORDERED_QTY 3 36 ld 4 V PIN Sheer1 Shest2 Z Sheets Z Ready GL Report Applet User s Manual Sum of SHIPPED QTY SELL Iq 1 el 1 29 Creating Microsoft Excel PivotTables Introduction Changing the Column Data You can change the data that appears in the columns in the table by dragging the fields or data block to the column heading area For example to show the quantity price
19. 3d 000502 FE 000 000 000 000 000 000 000 000 000 000 000 000 000 000 AVAA 000702 EE 000 S0Sc06 0077961 vt ese 9680956 829 66 BLELZSI 0C49911 9 1280L SESSIOI 992886 000 318WAWYd SAWS DOSEOC CE 000 000 000 000 000 000 000 000 000 000 000 000 000 000 al8vAvd INS 002207 LE 000 ges6ls pg illes BL ISIS 921296 8leos Z66 9 v9SElOL 0019 12505 9 LIES Iris tcO S 000 318vAvd VIN 009200 OE 000 9S8 16264 06ZIrGE 6919961 6Z vrege 68 8916l 0692924 1198 0OZ 0 r6 18161 06 5 09790817 OF Olp t 000 318WAYd ONICIOHHLIMA 31v1S DOFEOZ 62 000 28 21EZl 6 8 86 EU0cZl 6l6EccE 8272294 It 999l 100 1 0 8 9 62 0 69rO8s 0l vezst 000 318YAYd ONICIOHHLIAA WOl3 00202 BZ 000 C96Z2voy 9778988 EZFSIEH 86 11126 S468ivv 96 6 9 58 vtYSG Y 9728988 6 9515 lOScstv 000 3 18vAvd 9NITTOHHLIAA TWei3034 DODEDZ Z 000 000 000 000 000 000 000 000 000 000 000 000 000 000 1 002725 ONIHVITI TIOHAVd 000202 92 000 99 veszz 82 86672 06 ZE E699 lc88ric 59 1 9 OLEZ6 E 997992 PO ZEEE 19 86 LE 10 531 3 FIGVAWd SINNOIIV 000102 SZ 000 SS YSBEE 9 0560 299551 92 871 ELC9SBE 1 2 ZI 8 611 9295601 cr ZB E997 v 99ISEC 30v8l F18YAYd SINNOIIW DO000Z vC 000 eso eso CES BFF BFF BFF eso CES 85 s BFF 89 S eso D0 0S
20. 5 5 226 99 526 131 1495 705 005 2 2 161 14 381 645 441 01 24889101 Total 19 19 1661 22 2721 6455 20148 0845 124889102 1001 3 3 840 83 1485 495 1933 995 002 3 3 152 381 645 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 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 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 629 885 002 4 4 161 14 381 645 882 02 1003 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 la 4 M Sheet X Sheet1 Sheet Sheet dad gt 74 Ready NUM 4 You can also drag the selection fields from the Page area to the Row area to sort the data by those fields GL Report Applet User s Manual 1 31 Creating Microsoft Excel PivotTables Introduction More About Using PivotTables Feel free to experiment with the orientation of the fields on this sample report As you become more familiar with the tables and how to use them you can enjoy the benefits of viewing your data in new and different ways For more information about PivotTables see the Microsoft Excel documentation or
21. 7 AR gt ALL 4 100 2 Paria rio B ZU BS 55 8 S 6 PivotTable lt 7 aia cl gt A B D E E Giaj 1 TRANSACTION TYT AII x 2 g BATCH_ID 4 INVOICE_NUMBERIINVOICE DATE ENTRY NUMBER Data 000002 Grand Total 5 124889101 12 21 98 001 Sum of ORDERED QTY 4 4 6 Sum of SHIPPED_QTY_SELL 4 4 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 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 301 645 301 645 EJ Sum of Profit Dollars 441 01 441 01 30 12 21 99 Sum of ORDERED QTY 18 19 ei 12 21 99 Sum of SHIPPED_QTY_SELL 19 19 32 12 21 99 Sum of UNIT COST COMPNT 1661 22 1661 22 33 12 21
22. 9 R Report Applet requirements for 1 9 report applet installation 2 1 IX 2 report applets definition 1 3 S spreadsheets GL Budget Detail 3 3 GL Comparative Analysis 3 5 GL Forecast Detail 3 7 GL Journal 3 9 system requirements 2 1 GL Report Applet User s Manual
23. 92 9l 8vrez 8270007 14 2Z2S 01 SS3908d NIAYOAA AYOLNIANI 000 22 BYES 180999 Ee Ores 18997026 190 11 096682 1S 9E88 cESBrIE 228066 02 06651 0911021 bl SEEB66 STWI331VW AHOINSANI DDDFOL OL 000 0088 Se sc 16 61 1862 10 Sr 8918 00 0 09 E 10121 EZ ZOZ DOLENZ 1830 404 3ONvVAAOTIY 000201 6 000 D000rv 078901 05 966 S9066t 2 0922 06 8207 00 0081 055008 088807 08921 0S0S99 52110 0069101 318VAI3938 SINNOIIW 0001018 000 00 001 0004 00 01 00 0 00 0051 007001 00 DE 00 52 00 09 00 05 90 001 HSYI ALL3d 005001 2 000 000 000 000 000 000 000 000 000 000 000 000 000 000 PUZ ANYA NI HSv9 0010019 000 88 2 98 r SL al ra ZL ISE 00 0zz ESSE 06 221 S498 DO S 09911 Irish TYNOLLYN 15 ANYA NI HSWI 000001 EI ponad ZL Ponad pong OL ponad amp pousd Spouse Beusd 9poued S polled ppolad zPousd poued soueegbumubsg 358 iunosov 19 F eed aq 19 Eg 0 N WE A i H 3 v bY 9 anos Ve IA Em coo amp ME x Q Q O AUS II deR eyed 5001 4 3 pasu a a Yobpng g 19x7 yoso y X GL Report Applet User s Manual 3 4 GL Comparative Analysis File Name GLCOMP XLS Description The GL Comparative Analysis PivotTable uses the data in the General Ledger Master GLMAx file to display all of the balances stored for each pe
24. 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 p PIN Sheet1 Sheet Sheet 7 dal 1 4 Ready GL 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 12 1x Name rientation Cancel Row C Column C Page Subtotals Delete Bree e Automatic Custom None Hide items 12 21 99 Show items with no data You can use the PivotTable Field dialog box to change the field name its orientation on the PivotTable its display mask subtotalling options and so on 2 To shut of the subtotals select None under Subtotals and click OK 1 26 GL Report Applet User s Manual Introduction Creating Microsoft Excel PivotTables The PivotTable is redisplayed reflecting your changes X Microsoft Excel Book1 iO x 83 Fil
25. GB66Z SL 665 06 GPELSP CB 6 ZS 1r61 2 000 S3TvS vidas 000107 BF 000 000 000 000 000 000 000 000 000 000 000 000 000 L2 GELBBS SONINYY3 03NIV138 DOOFDE ZF 000 000 000 000 000 000 000 000 000 000 000 000 000 8S SE66L SONQIAIO D0DEDE SF 000 000 000 000 000 000 000 000 000 000 000 000 000 10 66Ec6r NI dl d TeNOLLIOGV 000Z0 SF 000 000 000 000 000 000 000 000 000 000 000 000 000 99 SCv26V 219018 QOOLOE vr 000 000 000 000 000 000 000 000 000 000 000 000 000 SS EV89 E1 219018 00000 Er 000 000 000 000 000 000 000 000 000 000 000 000 000 000 017 40 NOlLHOd IN3YUNI 5531 QO0ESZ cr 000 000 8L rise 814198 8L TIS8 8L 158 8L t198 8L t198 817158 8L TIS8 87158 8L t198 el rise 02098019 OML 318vAYd ILON 0oozsz 1 000 000 vOZvcOL 0 700 2701 201 20 vOZvCOLl 2 0 vOZvCOL tOZbzOL vO ZbZOL 07728619 318WAWd 000152 OF 000 000 909 71 509 701 509 7 509 01 509 71 SO 9ZbzZL 5092 509 7 909 1 SO 92bzL 909 1 E6 7998b2 318vAvd ANOA 000052 BE 000 000 000 000 000 000 000 000 000 000 000 000 000 000 40 NOLLYOd IN3YYNI 00001286 000 000 000 000 000 000 000 000 000 000 000 000 000 000 1 SWOONI 03983330 000802 26 000 000 000 000 000 000 000 000 000 000 000 000 000 000 318WAWd 3WOINI 31W1S 000202 9 000 000 000 000 000 000 000 000 000 000 000 000 000 000 318vAvd XYL 3WOINI Tv 43034 000902 SE 000 000 000 000 000 000 000
26. OPEN SYSTEMS Accounting Software General Ledger ODBC Report Applet User s Manual PN 2210 GLO60 O 1998 Open Systems Holdings Corp All rights reserved Document Number 2210 GLO600 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 General Ledger Data Files Introduction to PivotTables Cr
27. SOTH zi Options Dictionary GL 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 ENTRY NUMBER UNIT COST COMPNT UNIT PRICE ORDERED QTY SHIPPED QTY SELL BACKORDERED QTY Select Save from the File menu to save the query GL Report Applet User s Manual Introduction Creating Microsoft Excel PivotTables Using the Query in Microsoft Excel 1 Start Excel and open a new worksheet 8 Fie Edt view Insert Format Tools Data Window Help 18 x JOSE SRY BR o z 2 Zl u wow aria 70 B 8 BEE S 5 A Ready 2 Select the Data menu the
28. e Edit View Insert Format Tools Data Window amp amp lt 7 gt 2 Z da 4 10 2 Arial gt 10 je z u zm GE eR 5 e pivotable p ls 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 p PIN Sheet 1 Sheet2 Sheets 7 1 1 gt Ready GL 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
29. eating Microsoft Excel PivotTables Installation GL PivotTables GL Budget Detail GL Comparative Analysis GL Forecast Detail GL Journal Index GL Report Applet User s Manual A mmc on CO 3 5 3 3 9 Introduction General Information General Ledger Data Files Introduction to PivotTables Creating Microsoft Excel PivotTables GL 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 files in each ap
30. ive pour 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 user 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 ExOSASprogO 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 GL Report Applet User s Manual 1 11 Creating Microsoft Excel PivotTables Introduction If you have already built the shadow dictionary click on the Advanced button and check the options for No Shadow Dictionary Consistency Check and Fast Connect to improve performance See online help for additional information about the options that come with the Advanced button 8 Click OK to connect to the data source You are returned to the Create New Data Source screen 9 Select a table in field 4 if you want to select a default table source otherwise leave field 4 blank and select any table when you develop the query If you selec
31. log 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 1 Help 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 60osas c Data progFA JProgrm print JprogGL JprogsD prog P L proglN Rwdata prog R _JprogJO Sample progBK L progOw L sort progBR L progPA confia tom progEl Ll progPM File name config Files of type Fies tpm Cancel When you select the file the final dialog appears Microsoft Excel Q PivotTable was changed during Refresh Data operation When you click on OK the PivotTable is updated with your accounting data GL 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 data 2 4 GL Report Applet User s Manual GL PivotTables GL Budget Detail GL Comparative Analysis GL Forecast Detail GL Journal GL Report Applet User s Manual 3 3 3 5 3 7 3 9 GL Budget Detail File Name GLBUDGET XLS Description The GL Budget Detail PivotTable uses the data in the General Ledger Master GLMAx file to display the budget amounts on file for each GL account The da
32. n select PivotTable Report GL Report Applet User s Manual 1 17 Creating Microsoft Excel PivotTables Introduction 3 The PivotTable Wizard appears PivotT able Wizard Step 1 of 4 121 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 lt Back Next gt Finish 4 In step 2 of the Wizard click Get Data GL Report Applet User s Manual Introduction Creating Microsoft Excel PivotTables The Choose Data Source box from Microsoft Query appears Choose Data Source HE 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 IV Use the Query Wizard to create edit queries 5 Click the Queries tab and select the query you saved under Microsoft Query Choose Data Source 121 x Databases Queries dunning Cancel pivot
33. otals 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 GL Report Applet User s Manual 1 23 Creating Microsoft Excel PivotTables Introduction The Insert Calculated Field dialog box appears Insert Calculated Field Name Formula 0 Delete BATCH_ID ORDER_NUMBER TRANSACTION_TYPE INVOICE NUMBER INVOICE DATE CUSTOMER ID SUBTOTAL SALES TAX zi Insert Field 2 Enter the following information about the inserted field Enter Profit Dollars in the Name field Enter SHIPPED QTY SELL UNIT PRICE SHIPPED QTY SELL UNIT COST COMPBNT in the Formula field 3 Click Add Insert Calculated Field x Name Profit Dollars Modify Formula SHIPPED_QTY_SELL UNIT PRICE SHIPPED_QT Delete Fields BATCH ID ORDER NUMBER TRANSACTION TYPE INVOICE NUMBER INVOICE DATE CUSTOMER ID SUBTOTAL SALES TAX zi Insert Field 1 24 GL Report Applet User s Manual Introduction Creating Microsoft Excel PivotTables 4 Click OK X Microsoft Excel Book1 BS Ele Edit view Insert Format Tools Data Window Help The PivotTable is displayed with the Sum of Profit Dollars field 1515 1 DZ Sm lt
34. ough the other OSAS applications When you post the information in this file is used to update the General Ledger Master file The data in this file is displayed on the GL Journal GLJRNL XLS PivotTable GL 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 General Ledger 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 GL Report Applet User s Manual 1 7 Creating Microsoft Excel PivotTables Read this section for an exercise in creating a PivotTable
35. plication 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 GL Report Applet User s Manual 1 3 General Ledger Data Files You use the General Ledger GL system to consolidate information about your business You can use it alone but it works best when interfaced with other applications You can set up accounts and produce statements that include debit and credit information from the interfaced applications GL Data Files The General Ledger Report Applet contains several spreadsheets that report information from the OSAS General Ledger data files The PivotTables in the GL Report Applet are based on these data files GLMAxxx The General Ledger Master file holds the general ledger accounts and their balances The balances are updated when you use the Post to Master function Each record stores the account number description beginning balances and balances for each period for five types of balances actual budgeted last year next year and forecast Data from the GL Master file is used in the GL Budget Detail GLBUDGET XLS GL Comparative Analysis GLCOMP XLS and GL Forecast Detail GLFORCST XLS PivotTables GLJRxxx The General Ledger Journal file stores general ledger transactions You can enter the transactions through General Ledger or thr
36. r PivotTable by dragging the Field buttons on the right to the diagram on the left BATCH 109 SUBTOTA ORDERED ORDER NI SALES T rreren ransad Backorg NVOICE ENTRY NI dd INVOICE INIT CO USTOME UNI amp 3 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 GL 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 121 x EJ 2571 K Construct your PivotTable by dragging the field buttons on the right to the _ diagram on the left ORDERED TRANSAG BATCH IO coLumy Star StS ore ORDERED_d cif pAckond Sum of SHIPPED Q ENTRY NE Am of UNIT COST Invoice EnTRY_N E E invorce UNIT co custome UNIT_PR1 1 Finish
37. riod for each GL account The data is displayed on 5 separate spreadsheets actual budget last year forecast and next year You can view the data on the sheets by selecting the appropriate tab at the bottom of the current spreadsheet The data is sorted by GL Account Number and Description but you can easily change the sort order You can use this PivotTable to review and analyze your account balances compare the balances of different types perform what if analyses and calculate financial ratios Active Fields Default Field Type Field Page None Row GL Account Number Description Column Actual Budget Last Year Forecast and Next Year Beginning and Period 1 13 Balances GL Report Applet User s Manual 3 5 GL PivotTables GL Comparative Analysis GL Comparative Analysis PivotTable Sample Alaa Ss Ss e ed E PT IES A wen Y Ssoueegistosioj Y ESA ise Y seouejegisBpng seouejeg emoy V PA 000 000 19 8 8 9 90 80EE69 6c 9 8E EEL9S9 Z LvESC9 06 918682 ED Sr99z9 60866289 8r LOrOr9 S9 SGLEIS ZIELFETF 000 SIOVHLNO9 d313 1dO5 WOU ONI 000Z0 6h 10070 000 Os E89y1Z 81 15992Z 80 01907E 58 217957 v8 9rOlEZ CE I
38. s PivotTable description 3 5 files used in 1 5 screen 3 0 GL Forecast Detail PivotTable description 3 7 GL Report Applet User s Manual Index files used in 1 5 sample report 3 8 screen 3 8 GL Journal PivotTable description 3 9 files used in 1 5 sample report 3 10 screen 3 10 GLBUDGET XLS data files 1 5 description 3 3 GLCOMP XLS data files 1 5 description 3 5 GLFORCST XLS data files 1 5 description 3 7 GLJRNL XLS data files 1 5 description 3 9 GLJRxxx file description 1 5 GLMAxxx file description 1 5 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 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 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 GL Budget Detail 3 3 GL Comparative Analysis 3 5 GL Forecast Detail 3 7 GL Journal 3
39. t 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 OK 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 El IV Use the Query Wizard to create edit queries 10 Select the data source you set up in the previous steps 1 12 GL Report Applet User s Manual Introduction Creating Microsoft Excel PivotTables The Choose Columns screen appears Query Wizard Choose Columns 12 x What columns of data do you want to include in your query Available tables and columns Columns in your query SORH gt BATCH ID Bl SORL ORDER_NUMBER lt SDSL TRANSACTION TYPE El lt TERMS_CODE E SOTB lt lt HARE Group EH SOTD Preview of data in selected column El Cancel 11 Select a table you want to use in your Excel spreadsheet For this example start with one table and add a second table later Select the SOTH table select the columns for the spreadsheet and click Next gt The Filter Data box appears Query Wizard Filter Data 21x Filter the data to specify which rows to include in your query If you don t want to fil
40. ta is sorted by GL Account Number and Description but you can easily change the sort order You can use this PivotTable to review and analyze your budget figures or use these budget figures to perform what if analyses or calculate financial ratios Active Fields Default Field Type Field Page None Row GL Account Number Description Column Beginning Balance Period 1 13 Amounts GL Report Applet User s Manual 3 3 GL PivotTables GL Budget Detail PivotTable Sample GL Budget Detail a pe e e p E Tre pi RA TREE 000 vOZvCOL vO7vCOL vOZvCOL vOZrCOL 0 0 vOZvCOL 201 vOZrCOL vOZvCOL 20 07778619 JIBvAYd SLON 000152 OF 000 SOS2vci 909 71 909 71 SO 92bZL 509 1 90927 509 7 509 71 609 0 509 71 5092 SO 9pzl 8679986 2 318WAWd ONOG 000052 BE 000 000 000 000 000 000 000 000 000 000 000 000 000 000 40 NOLLYOd IN3HUNI 00001288 000 000 000 000 000 000 000 000 000 000 000 000 000 000 1 SWOON 03443330 000902 2 000 000 000 000 000 000 000 000 000 000 000 000 000 000 AVA XYL SWOONI 31915 000202 SE 000 000 000 000 000 000 000 000 000 000 000 000 000 000 318YAYd 3AOONI TvH3034 000902 SE 000 000 000 000 000 000 000 000 000 000 000 000 000 000 318YAYd ONNA NOISN
41. ter the data click Next Column to filter Only include rows where C nd Gr C And Gr C And Dr Use the Filter Data dialog box to select specific records from the table In most cases you do not need to choose anything in the Filter Data dialog box For example to filter out credit memos select the field named TRANSACTION_TYPE select does not equal and then enter 4 for a value TRANSACTION TYPE 4 is a credit memo GL 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 Descending Then by Ascending Descending Then by Ascending Descending zl lt Back Cancel Use the Sort Order dialog box to select how the data is sorted For example select a field in Sort by and check Ascending or Descending Select more fields and orders for hierarchical sorts For now don t enter any sort fields 13 Click Finish You are returned to the Microsoft Query screen Microsoft Query L Lx File Edit View Format Table Criteria Records window Help Ejea s ke fa vs x HH HE a Query 1 from OSAS Data HER BATCH MORDER NUM TRANSACTIUN TIINVOICE NUMHINVOICE DANCUSTOMER ID SUBTOTAL SALES TAX TOTAL COST 000002 00
42. using the ODBC Kit and Microsoft Excel 97 If you require more information about Microsoft Excel consult the Microsoft Excel User s Guide or Online Help Before you can create this report complete these tasks e Install and set up the ODBC Kit Install and set up the BASIS ODBC drivers Install Microsoft Excel 97 and Microsoft Query 97 Note This section includes instructions for using Microsoft Query with Microsoft Excel If necessary you can install Microsoft Query from the Microsoft Office 97 media You may also need to create a shortcut to Query manually GL 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 olx Eile Help oud z 5 72 4 amp 3 t R err Ies FE WWE 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 El WM Use the Query Wizard to create edit queries GL 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 121x What name do you want to g
43. z9 NOLLWZILYOWY WN99Y 000181 EC 000 000 000 000 000 000 000 000 000 000 000 000 000 000005 TIMADOOS 000081122 000 20712927 20712927 201292 101 LO LIEZ 10 19E7 201292 Z0 LZ9Z 20712927 0169 LO L9EZ 01967 1 SNBSOWNOLAW 8d30 ANIIY 005791 000 000 6779886 000 000 000 000 000 000 000 ezzg 6 000 000 ZE 96678 SSTIEOWOLNY 000791 000 JEc vr OD EBEN Z S0rv SEER 6 6 OD E6Er O0tecr 10 199697 SNIHOVA 391430 4d30 UNIV DOSESL 000 FS EEZSG EZOSILE 96682 000 289927 000 000 000 000 EZSSIIE 000 000 49 WCE6V SNIHOVA 391430 000 91 000 27 920 l ZC 920E Z 920 l 62 681 l 60 26811 62 26811 9 0 1 Z 920 l 2 Z 920 l 2 940 l Z 920 l 6S DEOZL OL ICLOES dINO3 9 AYINIHOVA 8d30 WOO 005291 000 000 000 000 000 000 0000007 000 000 000 000 99 I8rszl 8256651 z6Yv29 8 l ANAWdINOS 3 AYINIHIVA 000291 000 000 000 000 000 000 000 000 000 000 000 000 0005725 20 0000Sr 000151 000 99 6892 86 68651 SELO9ZZ 9 SZLOL ES S9BZL 957 86 66SP8LZ EW POSE 97 8 95 8668694 60SSEC 22 561 9957195 SSIWYNISS NISVLIAYVA 000901 000 000 000 000 000 000 000 000 000 000 000 000 000 000 S3SN3dx3 Qlvd38d 000501 000 PY EZELZ IBECHSP 9YEEZB OYEZOS Ow LOrbZ EJ6EEFL SOLOZSE II v8Bcty 98 ezvsb 9001 02 VER Z9 Svt8S8 50009 0O3HSINIA AHOIN3ANI OOFFOL 000 ZE ESPE 16 692 0 69 EZ69 6911016 911698 CESPSCYI 82 ZEEL FL 2907 1667
Download Pdf Manuals
Related Search