Home

End User Guide

1. Zj Reqvests 7024 i kxanglzs o Scecia Fest res Sales Zecuily 4 Invertory Regods D xi7 riu PTR los eM mexpia Safe ie a Fl FT All RYSTFU 12011974 Er JIAN sans iaa cifferere hbls R4 Wall SATT 49H ADEA PCr ANL Proft ana ysis by desler Rat ICTAIL SYSTEM 02242070 E an E Scie Ropos Or gnd Ret DETAIL SYSTEM 0602010 e ULUERAM sslcoiran ul Marc EXIN Ret JEIAIL SYSTEM UbCEZU_U pKa yuo wrap o ny etmen Ret JELAIL SYSIEMW TZUN AY Perier 2 ikar FSI Fi erence Tannie Daa Se Figure 4 5 Foxfire Request Manager Request List The Request Manager displays a list of your saved Requests for the active Preference Set Each line in the Request Manager identifies a stored Request For each Request the following information is displayed Short name Description Type of Output User who created it Creation date LANAA Command Buttons in the Request Manager To take action on a Request use the command buttons at the bottom of the Request Manager The Request Manager also includes an Exit command button that ends your Foxfire session 4 5 gt To run a Request 1 Highlight the Request you want to run 2 Choose Run Running a Request means producing the output that is defined by the Request It may be output to the screen to a printer or to a file Note When Foxfire is installed on a network Foxfire allows two or more users to run a Request at the same time But because Foxfir
2. 4 55 Output Types and formats are available with any Request Type they are most commonly used in conjunction with a detail request type The table below summarizes each of the different Output Types and Formats that are available Output Output Format Comments Type Report Columns See above Form See above Master Detail Group See above Headings Master Detail Page See above Headings Browse Data is displayed on the screen in a grid format Labels Choose type of label from standard list based on labels across Alternately you can use the ry maay Beane s J Label Request Type sone ta Mai Lebe mayes ci saree a mrs eek Graph Generic GenGraph Excel 3D Pie Column Bar or Line Excel regular Pie Column Bar or Line Spreadsheet Excel XLS File Only Excel XLS formatted file Excel XLS Launch Just launch Excel and show the data Excel XLS Launch Pivot Launch Excel and create a Table pivot table Note that items marked with an will automatically generate a file in the Foxfire OUT subdirectory This file will be named the same thing as the Request and have an Output Format specific file extension 4 56 Output Output Format Comments Type Lotus WKS Lotus WKS formatted file Lotus WK1 Lotus WK1 Mail Merge Microsoft Word Creates a mail merge WordPerfect 5 x formatted file which can be u
3. Enter filter value a Functions String i text 7 I Options Logical Date i date F Fields Variables ffvarread _jexitkey Cau Ghee a From table Cars The Expression Builder shows you all of the available Foxpro commands as well as all of the available fields and variables in the current environment All of the commands are organized by the kind of data they work on i e String functions work on Character based data Date functions work on Date based data When you select a command from one of the 6 3 dropdowns the syntax for that command is provided in the Enter a Filter Value box Replace the syntax placeholders with the actual field name or variables that you want to use The following sections are organized by function type and describe the most commonly used Foxpro commands Common String Commands These commands can be combined to apply several different formatting options to one or more character based fields If you want to Use this function Concatenate two character fields n together and leave the trailing spaces Concatenate two character fields together and trim the trailing spaces from the first field and the leading spaces from the second Remove leading and trailing blanks from ALLTRIM character expressions Remove leading blanks LTRIM Remove trailing blanks RTRIM Add specified characters to the left right PADL PAD
4. Sample Data field for each data item with an actual value drawn from the source table The first non empty non zero value found in the source table is used Show Configuration Info Creates a text file listing a wide range of information about your computer s configuration the settings being used by FoxPro and the current values of many memory values Please do not submit listing to technical support unless asked to View Query Log Foxfire can record information about every request query that is run including the request name description of records returned how long the query took what date and time the query was run on and which user ran the Request This utility provides you with a way to view and sort this information including the actual SQL statement that was run Additionally this utility can be used to clear the query log User Account Manager Setup and maintain user account information for all users who will be accessing Foxfire This 4 90 information includes username password security access and preference overrides v User License Monitor Enterprise Edition only Every time a user logs into Foxfire Enterprise addition Request Manager they use a license If so some reason they exit Foxfire abnormally their license remains in use Use this utility to monitor and accurately reflect the number of users currently in the product The Batch Builder Requests may be batched to strea
5. 1 Move to the Is Not portion of the filter condition Choosing the operator switches it from Is to Is Not or from Is Not to Is Filter Condition Relationships When constructing a filter condition you must specify a relationship between the contents of a data item and the values you are seeking Are the prices more than 5 000 Is the dealer name exactly like Fremont Toyota Each of these questions is a comparison that Foxfire makes on the data in each record it filters for a query Foxfire displays an appropriate list of comparison relationships based upon the data item type character numeric logical or date Table 4 1 Filter Condition Relationships by data type Character Numeric Logical Date and Datetime Like Equal to Equal to Exactly Like Less than Before Before Less or to On or Before Before or More or to On or After or After More than Equal to After After Between Between Between In the list In the list In the list Zero Empty Contains Blank Null Null Null Null To FoxPro and Foxfire a value of NULL means that data has never been entered into an item so the value cannot be determined As a result during a filter comparison for a specific value records with an item containing a null value are often not selected even if they appear to meet the comparison For example say you are filtering records for CARS COLOR IS NOT EQUAL
6. 4 Count of Records in Group appears in the Selected list 5 Highlight Cars Sale Price and choose Sum to specify the total sum of sales to each dealer This procedure creates a column for the total dollar value of all sales to each dealer Sum of Cars Sale Price appears in the Selected list Note Cars Sale Price remains highlighted in the Available list indicating it is still selected 6 Choose Avg to specify the average amount of sales to each dealer 7 Avg of Cars Sale Price appears in the Selected list 8 Highlight Gross Profit and choose Sum to specify the total gross profit of sales to each dealer 9 Choose Avg to specify the average gross profit of sales to each dealer 10 Highlight Cars Age at Sale and choose Avg to specify the average age of the cars sold to a particular dealer 3 40 11 Highlight Cars Days in Inventory and choose Avg to indicate that you want to the average days in inventory of the cars purchased by a specific dealer 12 Choose OK to return to the Request Editor 13 The completed Request should look like this Kequest Editor x Nare Description bursaves Care Sok 10 Dealers Data teme 07012008 Cas Suld tu Deale s kem Sum of Avg of Sum of Avg of Avgaf Avgof Cuunt Piive Revd Price Revd Profit Piufit Car Aye liveatury 12 5 1360 260 00 250 00 Eiter done Sort Grour t Sole To Dealsr Naire Qurput Request Type 5umnary Columnar Report 13 Screen Layo
7. 5 Choose Preview to preview the request lt HTML gt lt HEAD gt lt TITLE gt Quartile rank profit gt lt TITLE gt lt HEAD gt lt BODY gt lt PRE gt Request QUARTILE Note the Quartile rank profit gt HTML tags that are built Page 1 into the Quartile 1 repan Vehicle Last j ehicle as output This Make Mode Mileage Color Profit ID No Update allows the amp RSSSERS55S 2 SESEREEERERE RESEEEE SRRSRSSERE ISAI ISSI SOIE HONDA WAGEN 67852 1 136 66 663873 GM report to HONDA ACCORD 125187 1 075 09 126499 RMK viewed in a HONDA PRELUDE 94878 RED 1 005 06 613011 LMC HONDA CIVIC 68704 950 800 844256 GM Web HONDA CIVIC 121892 s 916 66 632416 LMC Browser HONDA ACCORD 91562 BLUE 866 66 676993 RMK HONDA CRX 142873 RED 775 66 666629 GM HONDA ACCORD 85754 GREEN 666 66 668161 LMC Totals for Quartile 1 Avg 998386 7 305 06 Quartile 2 Vehicle Last Make Model Mileage Color Profit ID No Update HONDA ACCORD 96912 BROWN s 635 66 165986 GM HONDA ACCORD 38002 666 606 618166 GM HONDA ACCORD 87627 s 536 66 655616 GM Figure 3 15 Example of HTMLOutput Foxfire provides a variety of other layout styles for you to use Below is an example of each of the different layout styles which are available 3 61 Try changing the layout styles for this report to some of the other types available on the Output dialog Presentation Style Request QUARTILE Quartile rank profit Bold group page and Page column Q
8. 6 8 The HF command is just one of the very powerful functions available to you in Foxpro Chapter 7 Indexing and Rushmore Indexing Explained Indexing is a technique that speeds up retrieval of database records They are particularly helpful for shortening the time required to run a query or report Therefore indexes are nearly always used with tables that are important enough to be included in a query or report Indexing is accomplished by creating a second smaller index file with one index record corresponding to each record in the table During retrieval the index records can be quickly searched to locate the corresponding database records instead of having to search through the larger table Unlike database records which are added to the table sequentially index records are located in the index file according to a very clever and precise formula that for a specific data value always results in the same record address location The database record might be located anywhere in the table but the index record is precisely placed so it can be retrieved very quickly Run the formula when you create the data record and its index record is stored in a precise location run it again to retrieve the index record in a flash Since the index record contains the address of the database record the database record can now be retrieved almost instantly too The principle underlying indexing is similar to the card catalog at the lib
9. Add Or A Delele Jiu Tup N I Cmit Qupinates Ask at Runtine Options Help Cictionary Cancel 26 Choose OK again to return to the Request Editor Specifying Sort Group Criteria When designing a report you might want to sort the records in a particular sequence for example by last name Specify sort order in the Sort Group section of the Request Editor Sometimes you might also want the report divided groups or sections based on one or more of the selected data items or available data items Example In this report you are going to Sort the records by car make and car model within each car make Group the detail lines by car make Whenever the car make changes the report will skip a line print a total for that group skip another line and print a group heading for the new group This is called a group break 3 22 You specify sorting and grouping in the same area of the Request Editor gt To specify sorting and grouping 1 Choose Sort Group 2 The Sort Group Selector opens gt i Sort Group for Request SAMPLE1 ineventory on hand F7 Stock Number Make Mileage color Purchase Price Transmission Purchased From ID 3 Select Cars Make from the Available list just like you did when selecting the data items for the Request 4 Select Cars Model from the Available list 5 To indicate a group break when Cars Make changes highligh
10. drag the band indicator up close to the bottom of the text object Totals For to reduce the amount of empty space 5 3 11 Your report layout should now look like this THe D piimaga AH IF Pigg WR pagenan 1 Tr Aap aaam m ege leazer Wake PAAWC___ Tuski Mudsl Macys Oulu Prices Paid Trams Pure a GxupHoada FASE u fo YIL L WILLS po o i Hk Ibi Fasa S a Lahki 0 Ara Sin at Brice Peet ota for au MILLA punci am Lozupl octer TMEL O4rpt_date__ ppt time am lage che Aro E GAAHD TOTALS MILEAGE PURCHPR__ K Jsqi taiyi A a SIMY 12 Preview the report layout frequently from inside the Layout Editor as you make changes to see how they affect the layout This shortens your learning curve To preview your layout from the Layout Editor even if you haven t saved your layout changes yet choose Preview from the View menu After previewing click the Open Door toolbar icon to return to the Layout Editor 13 When your changes are complete save the modified layout see To save the customized layout below You can then see the enhanced report on screen gt To Save The Customized Layout 1 Click on the Close Box in the upper left hand corner of the Layout Editor window You are asked if you want to save your changes OR 2 Choose Close from the File menu You are asked if you want to save your changes OR 3 Press Ctrl W to save your changes and close the Layout Ed
11. on page 4 58 Directing Output to a Pivot Table The following exercise demonstrates a number of interesting ways in which you can analyze related character date and numeric data as a pivot table in Excel gt To direct a Request s output to a pivot table 1 Create a new Request choosing Detail as the Request type 2 Enter XLPIVOT as the Request name and Comparison of Price Paid and Profit as the description 3 Choose Data Items and select the following data items Cars Make Cars Purchase Price Cars Date Purchased Gross Profit 4 Move to the Filter Builder and add the filter conditions Cars Make Is In the List BUICK FORD HONDA Cars Date of Sale Is Not Empty 5 Move to the Output Selector and change the Output Type to Spreadsheet Excel XLS Launch Pivot Table 6 Return to the Request Editor and Run the Request Foxfire launches Excel and places you in it with the generated pivot table displayed 3 68 j Zle Idt Yiew nest Fomat Iecs Dea i adow Lele E tE SAY sea A a R BB or lA A Je yi PUP SSR Wrst SA oa r Mi e Se oe F votTable lE ic 9 23 e 3400 481 1184 17300 1203 TSALY LLY 2930 6213 vedi E10 arn 1 2X9 795 I 4 3X0 1053 SAN SH 3375 10 UIZU t12 13400 209 Ico 203 am FM 25426 ACGD Aall AHI PaRESERENE n i lk Figure 3 20 Output to Pivot Table Example By default Foxfire will put all chara
12. or a file For more information see Report Output Types on page 4 52 Command Buttons in the Request Editor In the Request Editor you can choose the following command buttons Run Preview Count Show SQL Figure 4 8 Request Editor Command Buttons Save as Close v Run Processes the Request and automatically saves any changes you have made v Preview Processes the Request and displays the output on screen Fore more information see Preview on page 4 66 Saves any Request changes Count Displays the number of records that will be selected in the query v Save Saves changes to the Request v Save as Saves a copy of the Request with another name This new Request will also appear in the Request Manager See Save As section below v Close Cancel Exits from the Request Editor and returns to the Request Manager The button says Close if no changes were made to the Request during the session or says Cancel if changes were made during the session Canceling a Request here results in all unsaved changes being abandoned gt Save As The Save As dialog box makes a copy of a Request which you can edit to create a new version the original Request is not changed Save As is also useful for saving interim versions of a complex Request or one in which you want to explore successive what if scenarios Save Request as x Name Description
13. rather than trying to place by hand every field that doesn t fit at standard sizes see Chapter 5 for information on how to create a custom layout 4 94 Chapter 5 Customizing Report Layouts When you choose to edit the layout of your report Foxfire opens the Layout Editor in Visual FoxPro it is labeled the Report Designer This chapter shows you how to use the Layout Editor to customize a report layout to enhance its appearance With the Layout Editor you can make such common enhancements as amp _ lt lt amp Adjusting vertical spacing Moving data items and text Adjusting the appearance of data items and text Adding lines and boxes Adding custom calculations A report layout is make up of the following types of objects see Figure 5 1 Layout Editor on page 5 3 v Report bands e Title Page Header Group Header e Detail Group Footer Page Footer e Summary vy Data Items v Text v Lines Boxes These bands contain text data items and custom calculations which appear on the final report There are seven kinds of bands Prints once per report at the top of the report Prints once per page Prints at the start of a new group of information Groups are setup in the Sort Group Selector There is one of these bands for each data item marked as Group By Prints for each record selected in the query Prints at the end of each group as soon as a new group is detected There is one of these b
14. selected Nosted Ssquontial styl gt even if Sequential style ony there are Llelp OK Carcel Note that the text in the middle of the form now indicates that the Request will select ALL CARS whether or not there are matching EXPENSES Also note how the picture on the right of form indicates that there could be Cars data without any corresponding Expense data 4 Choose OK Choose Run Audi 2387 and 2389 are again included in the report Note that the Expense Date and Repair Amount column entries for these two cars as well as others are blank Reports with gaps like this are typical of outer joined Requests 5 Choose Close when you are done to return to the Request Editor and Close to return to the Request Manager Foxfire s Join dialog gives you the ability to specify how data from every table referenced in the Request is selected regardless of whether or not there is matching data in the related table Simply check the box next to the table containing the data you want to always select If you check both boxes then all data from both tables will be selected 3 51 Limiting a Request to the Top N Values Foxfire offers special filter feature when you want to report only the highest or lowest first or last or top or bottom values from each group Business Specification The District Manager would like to know for all cars sold last year for a specific make what where the top 3 highest grossing
15. Administration permissions and should be used with extreme caution Access these utilities by selecting System Administration from the Tools menu 4 88 Foxfire System Administrator Sample Data Set Edit Preference Sets Edit Data Items Import Data Items Edit File Relationship Info Import File Relationships from Environment Edit Report Template Edit Label Template Reindex Foxfire System Files Adjust Data Items to Match Indexes Refresh Sample Values in Data Items Show Configuration Info View Query Log User Account Manager User License Monitor Figure 4 38 System Adminstration Utilities These utilities appear in the list v Setup Wizard An automated method of achieving a basic Foxfire configuration for your data set Edit Preference Sets Creates and identifies characteristics of Preference Sets Determines which data set will appear in the Request Manager at startup See page 4 87 Edit Data Items Controls descriptions and behaviors of the data items that will appear in Foxfire Import Data Items From File Automatically loads information form data fields into Foxfire Data Dictionary Edit File Relationship Info Set up how Foxfire matches data from separate tables which are used in a request Data is shown in a tree view Also known as the Join Editor Import File Relationships from Environment Automatically Loads SET RELATIONS from FoxPro s current
16. BECER EU LK ZHU hth IWW hes lW Atcralie EE 4 BEGER EU TK 2h SKY ARK 9666 FE 1 200 Speec Totalsfor Make DUIK w 97879 2700 Moke CHRYSLER SITY RS SACP IA 244 renara naa ars T NN lt Speer Totalefor Make CHRYSLER fay anda nt 5 Click the Open Door button on the tool bar 6 The Output Choices window opens showing you a summary of your Output Choices 7 When you exit from a preview your Request is automatically saved 8 Choose Close 9 The Request Manager opens 10 Choose Close 11 The Request Editor opens Creating Label Request The procedure for creating a label Request is similar to that of creating a report Request You specify the data items to be included filter conditions sorting and grouping criteria and the output type But labels have different output options than reports Labels can be printed in columns and are formatted to fit a specific type of label Data items for city state and zip usually need to appear in the same line To deal with special label formats Foxfire provides a special Data Items Selector window for labels It allows you to select from many commonly used label formats including Avery forms As you select data items you 3 32 specify their location on each line of the label adding punctuation if necessary Business Specification We want to create mailing labels for letters we are going to send to dealers who we ve purchased cars from in the la
17. Depending on your computer s processing speed hard disk speed and memory configuration it can take a minute or more before report writer window opens Due to disk swapping you may find that your computer operates more slowly than usual while the character report writer is in use Warning Any customizations you make to the report layout can be lost if you make subsequent changes to the Request Once you understand the precautions explained in Tips for Customized Report Layouts on page 4 70 you will be able to maintain your custom reports v Close Closes the Preview window You can always change the report layout style after first creating the Request see Layout Styles on page 4 9 Visual FoxPro Preview The Preview in Visual FoxPro is similar to Windows except you choose the options from a toolbar 4 68 rx lege 7 oxtrel Heport Writer Preview re toolbar TAS Se FA i 5 BH SS SILVER Sulun aie Arim BLLE 5 Spee gt Spee ctp 5 Spss Suton sic SILVER 3 Spec 16 Records Sciceted Pecord CCPC Cxchisive 4 69 Preview the output using options from the toolbar Print Preview Ea o to pagejno Exit Z First page Last page n Previous Next Page Page Figure 4 28 Print Preview Toolbar The Visual FoxPro Print Preview toolbar can be customized from the VFP command window to include an option for printing directly from the Preview window When you choose t
18. Figure 4 21 Group Formatting Options Formatting options include Choose Formatting to view the formatting options for group breaks Highlight the desired formatting option Calculation buttons are disabled when the window is in formatting mode v New Page inserts a page break after each group break Vv Reset Page resets the page number to 1 one for the following page after each group break v No group header suppresses the group header Y No group footer suppresses the group footer yY Repeat hdr if grp continues repeats the group header if the group continues to another page 4 50 v New page if lt lines for group displays a dialog for defining the minimum number of lines which must be available to follow the group header or else a new page will be started for the group gt To add a formatting option to a group break 1 Select Formatting from the Group Options dialog 2 Highlight the group break band Cars Make Summary in the Selected list 3 Choose the formatting option New page in the Available list 4 The formatting option appears in the Selected list beneath Cars Make Summary group break band Note The only formatting feature available for the Grand Total band is New Page gt To remove a formatting option From the Selected list choose the formatting option you want to remove The item is removed Specifying Output Options Foxfire offers you enormous f
19. I Sort I Fitter Joins J Output coe Figure 3 8 Save As dialog 6 10 11 In the Name field change SAMPLE1 to SAMPLEX Note you have to change the name of the request or you will not be able to save this new request You cannot have two requests with the same name In the Description field change the description to Red Cars On Hand Choose OK The Request Editor opens Add the additional data items for Purchased From Name and Purchased From Address1 Note how these new data items are added to the end of the Selected Data Items list Use the Slider Control to move Purchased From Name to the top of the list The next task is to edit the filter and add a condition that returns only red cars 3 29 gt To add a filter condition l 2 4 6 8 9 Choose Filter in the Request Editor The Filter Builder opens To delete the filter condition which limits the Request to only Toyota cars highlight the Cars Make Is Like filter condition Choose Delete The Filter Builder now looks like this Filler ful Request SAMPLEA Inverdury On Hard Dafa Item Is Not Relationship Value s T EPEAT TEAT ee Z i PETE f Edit Acd Or Delete Unon Tap h I Onit Duplicates Ask at Runtime Options Help Dictionary o cnc To add another filter condition choose Add The Data Items list appears Choose Cars Color from the Data Items lis
20. SAMPLEX Rea Cars On Hand QUARTILE RUNTIME Quartile rank Cprofit gt Newest features of Ask at Runtime SAMPLE2 AUDI s and BMW s SAMPLES AUDI s and BMW s H SAMPLEX Red Cars On Hand STAT_DLR Dealer activity summary x put File Name CAFFEMOUTYNYVENTORY RPT C New Same Cancel Figure 4 9 Save Request As dialog In the Save As dialog box enter a new name and description for the Request The new Request name must be unique The list of existing Requests is presented for reference Note you must specify a new unique name for the Request If you specify the name of an already existing Request Foxfire will require you to change it Up to three sections may appear in the lower half of the window depending upon the specific details and ownership of the Request 4 12 Y Lock Allows the owner of a Request the creator or a programmer to lock specific elements of the Request so they cannot be changed by other users Choices correspond to the main sections in the Request Editor Data Items Sort Filter Output To change locks for an existing Request choose Save As keeping the same name but changing lock settings y Output File Name Allows you to control what happens if the current Request sends the output to a file Your options are New Sends output to a file named for the new Request Same Sends output to the file name specified in the original Re
21. only Makes starting with the letter M are shown Request AVPROFT Aaraaye Piufil uf Mokes fiuiii1992 lu 1999 CraseTah Aserage of Grose P oof foe Make by Year Average by Make mN tear for all years reported Make maage 1982 1993 1994 1995 1995 MADA 500 46 45 26575 4 00 1 25 00 250 MCRCCDIS 1 570 07 2290 00 31 380 00 MEROUR P 014 42 7 70 0 p 900 p 3x00 c10 X3 MITSLEISH 38 00 453 75 530 ENSPROS 192 02 S15 0C SO 20 67 140 56 448 57 Average by Year for all Makes reported 3 47 Creating a Request with an Outer Join Outer joins are a special kind of filter condition where there are unmatched records between two joined tables An outer join is needed when the result joins two tables and you wish to include the records in one table which have no match in the second This exercise demonstrates how to include outer join results in a report To simplify the steps you will modify the Request SAMPLE2 Business Specification The Business Manager wants to be able to track expense information for all cars in inventory She likes the format of the report called SAMPLE2 but wants to see Expense Date and Expense Repair Order Amount instead of information related to the transmission purchase price and mileage gt To create a request with an outer join 1 In the Request Manager highlight the Request SAMPLE2 and choose Edit 2 Review the Request components in the Request Editor Na
22. see Chapter 3 Tutorial Learning Foxfire 4 23 When you choose Build Expression the FoxPro Expression Builder opens Expression Builder Ea Please enter vcluss comme ssparated a Latcel Functions Siring vath E fae x Options Logcal date 0 X dete X Varables Fromtebls Car ks Figure 4 14 Foxpro Expression Builder Select the functions operators and constants necessary to construct the expression to which you want to compare Choose Verify to confirm that you have constructed a valid expression For more information on the FoxPro Expression Builder refer to the User s Guide in the FoxPro documentation or Chapter 6 Ask at Runtime Options Overview Ask at Runtime options add considerable flexibility to a Request by allowing the user to supply filter conditions when the Request is actually run instead of when it is created You can enter data values to be used in the comparison the simpler form of Ask at Runtime or you can edit the entire filter including adding and deleting entire conditions the more comprehensive form of Ask at Runtime In either case you can also enter a report heading The user specified filter conditions are not saved after the Request is run Entry of data values only is specified by choosing the Ask at Runtime check box in the Value s section of the Filter Builder see Ask at Runtime on page 4 22 4 24 Editing of the entir
23. 3 52 4 24 4 30 4 62 Report Layout customizing 4 70 editing customized reports 4 71 Report Output Formats 3 25 4 52 Column Output Format 3 54 4 52 Form Output Format 3 55 4 53 Master Detail Group Headings Format 3 56 4 53 Master Detail Page Headings Format 3 25 4 54 Master Detail Page Headings Format 3 57 repositioning data items 3 19 Request Editor 3 6 3 7 3 12 3 13 3 14 3 17 3 19 3 22 3 23 3 24 3 26 3 27 3 28 3 29 3 30 3 31 3 32 3 33 3 36 3 39 3 41 3 42 3 45 3 46 3 47 3 48 3 49 3 50 3 51 3 52 3 54 3 59 3 64 3 65 3 66 3 68 4 7 4 9 4 10 4 11 4 13 4 37 4 42 4 46 4 59 4 70 4 75 4 76 4 77 4 93 5 5 buttons 3 26 cancel button 4 11 close button 4 11 command buttons 4 11 command buttons list 4 11 contents 4 10 count button 4 11 data items selector 4 13 Data Items Selector 3 7 filter builder 4 15 Filter Builder 3 7 filter button 4 15 joins button 3 50 preview button 3 26 4 11 4 66 run button 3 26 4 11 save as button 3 26 4 11 4 12 save button 3 26 4 11 Request Exporter 4 83 Request List 4 5 items displayed in 4 5 Request Manager 2 4 3 5 3 6 3 8 3 11 3 12 3 14 3 16 3 17 3 27 3 28 3 32 3 33 3 39 3 41 3 48 3 51 3 52 3 59 3 60 3 61 3 64 3 65 3 71 4 3 4 4 4 5 4 8 4 11 4 67 4 76 4 81 4 82 4 83 4 84 4 85 4 86 4 87 4 89 4 91 5 2 command buttons 4 5 creating a new fold
24. AN Toft andves kv deact Ret DETAIL SYSTEM 02242020 Bio asl F Riralik a k ipfii Ral RFT AI SYS FW 1201 r ARE RUNTIME Newest factarcse cf Ack ol Purrine Rat DETAIL SYSTEM 0 M120 Pijs ama F byer y mand Ril PETAI SYS Fu NASRONIY SAMLE AUDI S ard BMA s Rat DETAIL SYSTEM 06262020 isan FS SHNS a l AMA s Rud PETAI RS Fu ART PNT EESAMLEX Red Cavs Or and Rat DETAIL SYSTEM 06 232020 Eista R Deal r a divily su ry Ral SMARY WETE Neen Bhwospyrg Word wrap tor onyicxt neno Rat DETAIL SYSTEM 1201 OX kistan n xx pwi by pease dimi h R i OPORS TAR SYS Fu Aen Remmers User SYSTEM Pre erence Sencle Cita S3 Figure 1 4 Simple List Foxfire uses three kinds of lists y Simple List Drop down list For more information see Drop down Lists on page 1 10 Y Dual lists side by side lists For more information see Dual Lists on page 1 11 1 9 gt To choose an item from any of these using the mouse 1 Scroll through the list until the item you want is highlighted 2 Double click on the item selected gt To choose an item using the keyboard 1 Use the PGUP PGDN and arrow keys to highlight the item you want 2 Press SPACE twice in quick succession Available lists are displayed alphabetically When searching for a specific item pressing the first letter of that item highlights the first item that contains that letter For example to go to Sales Activity press the letter S to jump to t
25. Choices SAMPLE1 When you exit from a preview your Request is automatically saved Choose Close The Request Editor opens Choose Close 10 The Request Manager opens 3 27 Creating a New Version of a Request Foxfire provides an easy way to create a new Request based on another similar Request Business Specification After using the Request you just created for a month one of the Sales Managers decides that they would like to have a similar report for red cars instead of Toyotas Additionally it is important to see the name and address of the dealer who we bought the car from The following section shows you how to accomplish this request gt To rename your Request 1 Choose SAMPLE Inventory on Hand from the Request Manager 2 Choose Edit or right click and select Edit from the pop up menu to display the Request Editor 3 The Request Editor opens with the Inventory on Hand Request 4 Choose Save as Note make sure to select Save As and not Save 5 The Save Request As dialog opens 3 28 Save Request as xi Description inventory On Hand MAIL_LBL Mailing Labels for Dealers a MULTJOIN Joins four different tables PROANAZ Profit analysis by dealer Top 3 cars PROF_ANL Profit analysis by dealer QUARTILE Quartile rank profit RUNTIME Newest features of Ask at Runtime SALES Sales Report SAL_ORIG Sales Report Original SAMPLE1 Inventory On Hand Fal ock I Data tems
26. Ifthe Request list is empty or it contains different Requests than the example above don t be concerned You ll correct this in the next step In order to begin the tutorial exercises you must be sure the Sample Data Set is the active Preference Set This is because the Preference Set 3 8 Highlighting a Preference Set makes it active Caution Selecting another Preference Set and pressing this button with change the default to that Preference Set controls which database Foxfire uses for reporting For more information on the sample database see Chapter 2 gt To select the sample Preference Set 1 Choose Preference Set from the Tools Menu The Preference Set Picker opens Note Important Before taking any other actions examine the list of Preference Sets and note the following you may want to write down the following Which one is currently highlighted Which one contains the phrase Startup to the right side If your version of Foxfire has already been customized you must restore these settings after completing the tutorial before you can use Foxfire to work with your own data Preference Set Picker x Preference Set User Department Demo Use of Views ENDUSER Micromega Startup means this Preference Set is the default and is automatically l active when Edit _ you first start Foxfire If Sample Data Set is highlighted in the list the sample data set i
27. Meleage Calar pE IIR HEL LID i H IEN S150 a 1476 LO bS 54 Bare A t spi Beale bh LUE E LER nS E SSE b SIE i Mi b mi il UE v IMI Pin S476 Welty IINE SfE 149 SPLAT Vee SeA iddl 5 711 A t 73651 Mileage 67852 123187 94878 68764 171297 91562 142079 85754 1 Avg 29236 amp J M N Color RED BLUE RCD GREEN Setecte Last Th an Uptake IEN oH RAELE Lng LIRLI LHE l4 5i br Hai LHe LTA EERI LLIS LLLTTE bn HiH tae Setecte Lash Th as Spdale ETIS m S IEH br SSI 6H S454 GH IHIEL Lng LIESEL LEIS IHSE GH wari br IPHI GH Ushicle Profit ID No 1 136 86 663873 l1 979 90 1254649 1 085 80 613611 950 90 644256 S1A AAR ARPAIA 806 96 978993 775 86 666629 666 960 668161 7 305 36 last Update cM KMK LMC CM IMC RMK GM LMC This section describes some of the other layout options available when working with Requests These options allow you to control on a request by request basis the spacing between rows or columns what kind of filter conditions are displayed on the request and how much of the detail data is shown on the request 3 63 1 2 3 From the Request Manager highlight the INV_ORG request Right click and choose Output Choose Options under the Style drop down Layout Options Figure 3 18 Layout Options dialog 4 5 10 Select All under Show fil
28. Nunber Make Mileage Sslect Space Selected Remove Text Figure 4 32 Label Request Data Items Selector In this example we want to create mailing labels for letters we are going to send to dealers who we ve purchased cars from in the last two months that have still not sold We will be sending these dealers a letter indicating that until these cars sell we will be unable to purchase additional cars from them gt To create a label Request See Creating Label Request on page 3 32 for detailed instructions on creating a label request Label Types The first step in creating labels is to indicate the label type To specify the Label Type select Choose from the Data Items Selector The Label Type dialog opens 4 79 Label Type Ea Type Height Width sce TE Ra Foa N E ai A Characters 3 x 5 Rolodex 14 Lines 56 Characters 4 x 1 7 16 x 1 8 Lines 46 Characters 4 x 2 1 44 Rolodex 16 Lines 46 Characters 6 1 2 x 3 5 78 Envelope 14 Lines 65 Characters 9 778 x 7 178 Envelope 17 Lines 78 Characters Avery 4145 15716 3 172 Avery 4146 1 7716 4 Avery 41686 2 7716 3 172 Avery 4161 2 15716 4 xj Sort list by Name Height and Width inches Label types displayed fi across e millimeters Cancel Figure 4 33 Label Type dialog The Label Type dialog offers a list of available label types The list changes with how many labels across you select 1 2 3 or 4 columns and whet
29. SILC 15 t 00 OFLC 770 oF as Fane 5 WU o A Pol Skew Jomi BS EVENS Os ER YNIA Pog Scr 3 owe 14 EMAND TOTALS 1 FRE OD 5 GHA NEM All remaining columns contain summary data about the identified Group See Creating A Summary Request on page 3 38 for a detailed description of how to create a Summary Request gt Specifying Summary Data Items Data item selection is different for a summary Request than for a detail Request For those items being summarized you must specify the type of summarization for example a count in addition to the data item itself Note If you select an item and do not indicate a summary function for it the item becomes part of the group identity information Foxfire knows based on the data type of the data item being selected what type of summary functions are applicable For example if you select a character based data item only the Min and Max functions are available since you cannot calculate an Average or Sum of a character field 4 73 gt Group Filters HAVING Clause When you are building a summary Request an additional feature appears in the Filter Builder the Available data items list for filtering includes all calculated items selected for this Request in the Data Items Selector in addition to the data items available for detail Requests These special items at the top of the Data Item list actually let you filter the result based on the results of
30. Save your changes and Preview the Request Seven Audis and three BMWs should be listed gt Using In the List Relationship For the Request SAMPLE2 you just created you could achieve the same results by using an In The List relationship in place of the two filter conditions connected by OR In The List is really a shortcut eliminating the need for creating multiple filter conditions 1 Highlight the Request SAMPLE2 and choose Edit 2 Choose Save As and name the report SAMPLE3 and change the description to Audi s or BMW s In the List 3 Choose Filter 4 Delete the two conditions and the OR connector 5 Choose Add and from the list of data items choose Cars Make 6 Since we want to select both Audis and BMW s choose In the List from the Relationship drop down list 7 Under Value s type AUDI BMW When using an In The List relationship the values must be for the same item and must be separated by commas 8 Choose OK to save the filter condition 9 Save your changes and preview the Request The results are seven Audis and three BMWs the same as for Request SAMPLE where you used 2 filter conditions connected by OR 4 36 gt OR Connectors With More Than Two Filter Conditions The effects of AND and OR connectors are very different Therefore when you have several filter conditions and you want to include some OR connectors you must specify which portions are connected by the OR and which parts
31. Show list of Values to display a list of valid values for the data item The value you choose from the list is entered in the Value s field Empty Sometimes you will want a report consisting of records lacking an entry in a particular field For example records with an empty Car Sold To field would yield a list of unsold cars Table 4 2 shows the Empty value for each data type Data Item Type Corresponding Empty Value to Specify Numeric Zero Date Datetime Empty Character Blank 4 21 Note Visual FoxPro users Empty is not the same as Null For more information see Null values on page 4 19 Ask at Runtime Ask at Runtime enables you to defer entering the value until you actually run the Request This is useful if you run a report repeatedly but want different comparison values each time For example you may want to specify a different range of dates each time you run the Request If you leave the Value s field blank and choose Ask at Runtime the user will be asked and is required to enter a value for the filter condition at runtime You can also enter a default value and choose Ask at Runtime which changes to Ask at Runtime w Default in which case the user can change the default value at runtime If you want to make the Ask at Runtime comparison value optional i e the user can optionally leave the value blank to select all values or you want to display special instructions about the
32. To switch the sort sequence to and from descending order highlight the data item whose order you want to change in the Selected list and choose Descend located below the Selected list Group Breaks A group break also known as a control break occurs in a report when the value of a certain data item changes 4 44 Example You want to list cars in inventory by make At the end of the records for each make you want a subtotal of the purchase price You would specify a group break on Cars Make In report output a group break triggers the following actions YA horizontal line is printed under each numeric data item vY A subtotal is printed for each numeric item that is identified in the Data Dictionary as Sub total on Group Break Other types of calculations may also be specified in the data dictionary vA blank line is inserted YA group ID heading is printed to identify each new group Note that the group usually has a new group ID heading printed before the first detail line of that group vA final blank line is inserted gt To create a group break 1 Place the data item you want to group on in the Selected list 2 Choose Group Located below the Selected list The grouped indicator appears to the right of the data item s name Note Group breaks can only appear in reports Foxfire ignores group settings for labels browse and file exports gt To cancel a group break 3 In the Sort Group dialog
33. a value which will be specified at runtime vY Sorting and grouping information called sort group criteria and group break options The Sort Group section of the Request Editor shows that the output will be sorted first by Cars Make and then by Cars Model within Cars Make The Group indicator in Windows or that appears to the right of Cars Make means this report will force a group break each time the Make changes A group break is a set of actions that occur whenever the value of a grouped item changes v Information about the appearance of the final report called the output Before exiting the Request Editor Figure 3 5 Sample Request and Figure 3 6 The Resulting Report to see how the query defined in the Request Editor looks when it is output as a report After you have studied the Request and the report choose Close to return to the Request Manager 3 14 Relationship Between Requests and Results A NAME and DESCRIPTION This section describes the relationship between a Request and the final report which is produced when the Request is run You will choose a sample Request from the list of those provided and then run it The following two figures illustrate the relationship between a Request and the report it produces lt Specifies the report name C FILTER Specifies selection criteria Which records will be included in the results D OUTPUT Specifies output type report file et
34. are connected by the AND Business Specification You have a customer interested in Audis and BMWs or any 5 speed vehicles in inventory regardless of make 1 Highlight Request SAMPLE3 2 Choose Edit 3 Add the Cars Date of Sale as the first data item 4 Choose OK to return to the Request Editor 5 Choose Filter 6 Construct the filter as follows Cars Make Is In the List Audi BMW Cars Date of Sale Is Empty OR Cars Transmission Is Like 5 Speed Cars Date of Sale Is Empty 4 37 7 The filter should now look like this These will Filler lor Hequesl SAMPLE Auie w BMW s I Lhe Lisl retrieve unsold Audi s and BMW s These will retrieve unsold cars with a 5 Speed transmission m am ar ES Caste Uz Tore M arr leae Sok tDurtim Options Hele Ciclitray 8 Preview the request and note that you only get unsold cars which are either Audi s or BMW s or have a 5 speed transmission gt Using the Parenthesis to Change Order of Evaluation When you mix AND and OR operators in complex filters the order of evaluation of the filter conditions can change the results Foxfire automatically evaluates AND conditions first then OR conditions Two buttons allow you to insert parentheses wherever you wish in the Filter Builder to change Foxfire s normal evaluation sequence However parentheses only affect the evaluation sequence when there is a mixture of AND and OR connector
35. conditions on report in the section on Layout Options on page 4 61 Edit Runtime Filter If Ask at Runtime was specified for the value in a Request s filter condition and the Ask at Runtime option Edit Entire Filter is enabled when you run the Request the Edit Runtime Filter window opens 4 29 Edit Runtime Filter for Request SAMPLE2 Inventory on hand x Data Item ls Not Relationship Value s Cars Date Of Sale Cars Make Is Like TOYOTA Cars Date Of Sale On or After CEnter Filter Value Gross Profit More or to 1666 66 Cars Year Is Between 1992 1999 fs Edit dd OP J Delte Top N Help Heading Cancel All filter conditions are displayed and depending on what options are specified in the Ask at Runtime Options dialog see Ask at Runtime Options on page 4 24 the runtime user can edit them Any editing in this window is effective for the run only It is not saved as part of the Request Editing a Value The runtime user enters a value by choosing to Edit the filter condition Depending upon the filter relationship the user running the Request can enter a value or choose a value of Blank Zero Empty Another Data Item or choose Build Expression The runtime user can add and delete filter conditions for the run only if the option Filter Conditions may be added or deleted was chosen in the Ask at Runtime Options dialog Help at Runtime The Edit Runti
36. customization 4 63 layout options 4 63 output destinations 4 64 7 5 output types 3 25 special processing options 4 66 Output Type ASCII 3 65 chart 3 65 pivot table 3 65 report 3 25 report output formats 3 25 spreadsheet 3 65 table 3 65 Output Types 3 25 Output Types and Formats examples 4 55 P page 3 80 See page 3 54 Parentheses using 4 38 Pivot tables changing layout 3 69 working with in Excel 3 69 Pivot Tables 3 65 Power User Work Plan 1 2 Practical User End User Work Plan 1 1 Preference Set 3 8 3 9 3 10 3 71 4 5 4 65 4 72 4 87 4 88 4 90 changing default 4 87 editing 4 88 Picker 4 87 restoring 3 71 selecting 3 8 Selecting 3 9 setting default 4 88 Presentation Style 3 62 Preview 4 66 4 67 a Request 4 6 button 3 26 character report options 4 67 close 4 68 output choices 4 66 print preview toolbar 3 27 toolbar options 4 70 window 4 66 Print Preview Toolbar 3 27 Q query data items 3 3 defined 3 3 filter conditions 3 3 sort order 3 3 R relationships 3 50 removing data items 3 19 Repeating header if group continues 4 50 Report bands 5 1 5 6 column header 5 6 detail 5 1 5 6 group footer 5 1 5 6 group header 5 1 5 6 page footer 5 1 5 6 page header 5 1 5 6 summary 5 1 5 6 title 5 1 5 6 Report Controls toolbar 5 7 Report Designer See Layout Editor Report heading
37. filter conditions when the user runs the report choose Ask at Runtime Options to specify additional options for Ask at Runtime values For information on this feature see Ask at Runtime Options on page 4 24 Another Data Item Sometimes you may want to compare one data item with another in the same record Example If you want a report showing cars that sold for less than cost you would build the following filter 4 22 Figure 4 13 Filter for Request INVENTRY Inventory on hand X Data Item ls Not Relationship Value s Ee Cars Date Of Sale i Cars Sale Price On or After Gross Profi More or to Cars Year Cars Sale Price is compared to another data item Cars Purchase Price When you choose Another Data Item as the Value a list of data items appears from which you choose the data item to use in the comparison For the example above you would select Cars Purchase Price This completes the filter condition so that it reads Cars Sale Price Is Less Than Cars Purchase Price i e cars which sold for less than cost Note Remember the data type of the data items being compared must match Build Expression To specify a more complex value than is displayed in the list of Available data items you can use the FoxPro Expression Builder to build a compound expression Note Expressions generally require an advanced level of knowledge of FoxPro or the Xbase language For more information
38. for it to become a reality but still doesn t know how to install it And to the following people and organizations for their noteworthy software contributions Steven Black s INTL Toolkit Steven Black Consulting email steveb stevenblack com GENREPOX report generator public domain Markus Egger EPS Software email max eps software com Dixon Mono Thin font for Ledger style reports with permission 1993 Page Technology Mktg Inc 858 794 6884 Bob Pooley email info pcltools com We appreciate all your efforts toward making Foxfire the best it can be Copyright Notice and Restriction on Duplication 2000 by Micromega Systems Inc All Rights Reserved The Foxfire Software and related materials are protected by U S and International Copyright Laws and are licensed to you under the terms of a Foxfire License included with your software No part of this publication may be reproduced in any form without prior written permission of Micromega Systems Inc No part of the Foxfire Technology may be reproduced except as provided in the license accompanying the Foxfire software Trademark Acknowledement Foxfire is a registered trademark of Micromega Systems Inc All other trademarks or registered trademarks are the property of their respective owners FOR MORE INFORMATION about Micromega s licensing policies please call Micromega s Foxfire Customer Service at 415 945 3333 Fax 415 945 330
39. ilen iE ily Only f Ala uss Zantal Any Part of this condition allows the user to edit all parts of the filter condition only available when Edit Entire Filter is chosen Nothing prevents the user from changing any part of the filter condition This is the equivalent of a filter condition not marked as Ask at Runtime Runtime Prompt Enter a prompting message a special instruction which is displayed at runtime at the top of the Edit Runtime Filter window only available when Edit Entire Filter is chosen Runtime Help Enter a help message which can be viewed by choosing the Help command button located at the top of the Edit Runtime Filter window Press CTRL W to save edited help text If you do not enter any Runtime Help the Help button does not appear in the Edit Runtime Filter window only available when Edit Entire Filter is chosen 4 28 Using Ask at Runtime Dialogs at Runtime Enter Values A value must be entered for each filter condition displaying Reqd When a value is not required and the user leaves it without an entry the filter condition will be ignored during the query Enter Values Request SAMPLE Inventory on hand This filter condition must be specified at runtime Cars Date Of Sale Is On or After Ci E Cancel Ask at Runtime filter conditions can automatically be added to the report header through a setting in the Layout Options dialog of the Output Selector See Show filter
40. like ask at runtime Note that all of the Filter Sort Group and Output features available in a detail request are also available for a Label 3 37 Intermediate Tutorial The intermediate tutorial introduces features needed for more complex reports In this section you will learn how to create summary reports and cross tab reports Additional exercises introduce the use of special filter options such as the outer join and top N values And you will learn how to use the master detail layout option to affect the appearance of groups in a report Creating A Summary Request Sometimes you may want a summary Request that provides a single line summarizing selected information for each group as opposed to providing all of the details For example you might want a report that shows how much total business you have done with each dealer as opposed to seeing all of the details of each dealer sale This type of request is a perfect application of the Summary Request type There are two types of columns in a summary report group identity and summary data Reyes STAT_DLR Dealer activity summans Pape 1 The Group a enaena O roe ee cry ene E ener T tren Identify Items ae maybe be Purdnsal Iteni Sumuf Awyu Anyu Daysinu one or more fromDealer i Cout Profit Price Paid _Car oe __Invenow columns CARL CHE YROLE GEO 4 1465 20 x7 190 00 identify i COURTESY CHEVROLET 13 4580 0 q 4 505 190 00 i D
41. lists are the most common location for scroll bars in Foxfire For example the Selected list shown in Error Reference source not found has a scroll bar at the right because the actual list contains more elements than can be displayed in the viewing area By scrolling the hidden elements can be viewed gt To scroll using the mouse One line scrolling click once on the arrow pointing in the direction you want to scroll Repeat each time you want to scroll another line 1 8 Lists Continuous scrolling move the cursor over the square in the scroll bar sometimes called the thumb and drag it in the direction you want to scroll gt To scroll using the keyboard Use the up or down arrow to scroll one line PGUP or PGDN to scroll by one full window of data or HOME or END to scroll to the top or bottom of the list Lists are used to display items that can be selected Request Manager Requests Root i ViewTous Di inry R Trze Miess 7 Dare Cxt E Cers Sok Tc Ieaere SULMARY 06 23 2020 IVES TRY Fyerto y on hard Rat DETAIL SYSTEM 092220 A OPES Ever gle v ile uny daa ien Ra DETAIL SYS EW 12 01 935 LRSE TEN La penze for cast car Rat DETAIL SYSTEM 1201 9 m i_ LBL Wai bets fur Dockery Lay L amp Bas SYSTEW 12017 93 M_L JCIN dons tour ci tererr tades Rat DETAIL SYSTEM 1201 9X MyYLASBE_S Leh fur Dede y We Panerreved Fi Lars LBS SYS EW 0623 2020 EPROF
42. make a group permanent so that its objects always move together until you explicitly ungroup them by using the Group or Ungroup option on the Format menu Changing the Appearance of an Object gt To format a data item or text object 1 2 Choose the data item or text object you want to format Choose either Font or Text Alignment from the Format menu depending on the customization you are applying other style or formatting options are shown in the Format Menu Ej faite By rielfeie jes This menu and toolbar aeaaing WiGer provide options which fall allow you to modify the Ren appearance of objects Figure 5 7 Layout Editor Format Menu and Toolbar 3 If you choose Font the Font dialog opens you should recognize this dialog from other standard Windows packages T Arial Black T Arial Narrow F Book Antiqua FP Bookman Old Style Calisto MT Century Gothic HM Black Figure 5 8 Layout Editor Font Dialog gt To change an object s width Click and drag on the bottom right hand corner of a data item to adjust its width In Windows sometimes it is hard to see if an item s defined width is sufficient to accommodate the data To assess this move the cursor just below the item and type the characters CW in the same font style and size repeatedly until you have typed an equivalent number of characters to accommodate the data In this way you will be able to gauge the fit and if n
43. of the filter conditions can completely change the effect of the filter If there is no OR the order of the criteria has no effect on the query s results or execution 4 40 Table 4 5 Note Due to FoxPro s optimizer the order of the filter criteria will not generally effect Foxfire s retrieval speed See Rushmore Optimization Explained in Chapter 7 Using Like Instead of Exactly Like For most filter conditions Like is sufficient to get the results you wish and is preferable from a performance standpoint Rushmore optimization makes most Like criteria lightning fast while Exactly Like requires much more and slower disk activity for record by record comparison Exactly Like is needed only in the rare circumstance where you must consider trailing spaces or data beyond the end of a filter value entry Table 4 5 demonstrates the difference in results that occurs when you use Like in comparison with Exactly Like Filter condition Result records retrieved Cars Make Is Like GMC 1981 GMC 1984 GMC Truck Cars Make Is Exactly Like GMC 1981 GMC Cars Make Is Exactly Like GMC 1984 GMC Truck Truck Sorting and Grouping Requests Presenting information in a particular order is required in many reports It is also common to produce the same report using a variety of different sorts or groups Sort and Group Explained This section describes how
44. on the page by shortening some of the longer fields and using a smaller font When the data items you have selected for your request are too wide to fit on the report Foxfire s Fit to Page feature tries to fit your print output to the dimensions available when you run the Request You will be prompted with the following dialog box 4 93 Just chop off the fields that don t fit Foxfire Report Writer Ed Report layout is not wide enough for all the selected Data tems Options for finishing this run now Run amp s Is Cancel Explain techniques fof adjusting the layout later Master Detail Layout Other Suggestions Figure 4 40 Fit to Page dialog In character styles if you pick Fit to Page Foxfire will automatically shift the font of your request to compressed print and a landscape orientation if these are supported by your printer In graphical styles if you select the Fit to Page option it will reduce the font size and if the output still won t fit narrow fields individually This may cause word wrapping that you can adjust by modifying the layout Additionally you can using the Sort Group feature and pick a different Layout Style see page 4 61 to move fields out of the detail band thus narrowing the request width When you need to arrange a large number of fields on a layout it is easiest if you let Foxfire place them all Use Fit to Page then adjust by hand
45. profit sales We already have a Request PROF_ANL which shows sale price and gross profit sorted with the biggest gross profit first for each car sold in the last year gt To limit a request to the Top N Values 1 From the Request Manager highlight the Request PROF_ANL Profit analysis by dealer and choose Edit 2 In the Request Editor choose Save As 3 Enter the new name PROFTOP3 and add top 3 cars for each model to the description Choose OK 4 Run the Request In the Enter Values dialog type FORD and a heading of your choice Choose OK 5 In the Page Preview note there are 7 Broncos and 7 Escorts Each model group is ordered from the most profitable to the least 6 Choose the Open Door icon to open the Output Choices window Choose Close to return to the Request Editor 7 Choose Filter 8 Choose Top N The Top N Values dialog appears 3 52 Top N Yalues Ea gt Number of Data tem alues F Use SQL TOP N TORN values CROP N percent Figure 3 14 Top N Values dialog 9 10 In the Number of Data Item Values section of the Top N Values dialog choose the check box labeled Include only the first This turns on the spinner selector to the right which defaults to the value 5 Reduce this to 3 by clicking on the down arrow or entering a 3 The drop down list shows Cars Make This would give you sales data for the first three makes which appear on the report not w
46. the chart as desired 3 66 Microsoft Excel Chartt Summary Compartson of Price Pald and Profit 20000 g e que yu al suc acim es he gt SE a BHU mHC A i irean AEAN ALHA et Price aie Se af rafi cI Charts i Fescy I Rel Figure 3 19 Example of Output to Excel Graph Some Points to Keep in Mind Generally Excel graphs provide the most useful information and are most readable when the number of rows in the result set is small The following suggestions will help reduce the number of rows without omitting essential information v Use summary type Requests Summary Requests Creating A Summary Request will by default group all character or data type data items Grouping items condenses the data Although you can certainly use detail Requests for charts they frequently produce too much detail for the chart to be readable y Use aggregating calculations on numeric data items such as Sum to limiting the number of rows in the output v Use a filter to further reduce the size of the result set Limiting the rows in the result set creates readable charts If you had created this Request as a detail type even with the same filter there would have been over 150 rows in the result set which would result in 150 columns in the chart a chart that s not very readable For more 3 67 information on creating Excel charts see Working With MS Office
47. the rows using the same calculation method chosen for the cross tab values To summarize columns mark the check box Col Summaries To summarize the rows mark the check box Row Summaries This sample report does not require row or column summaries gt Filtering data for Cross Tab Requests You create filters in the same way you create detail and summary Requests For this Request you only want to see cars for the years 1992 to 1999 1 Choose Filter to open the Filter Builder 2 Choose Add A list of available data items appears 3 Choose Cars Year 4 Skip over the Is Not column 5 Choose Between from the Relationship drop down list 6 In the Value s field type the beginning and ending model years 1984 1988 and choose OK 7 The Filter Builder displays the filter conditions you ve specified 8 Choose OK again to return to the Request Editor 3 45 9 The Request Editor opens 10 Your Request should look like this Request Editor AVPROFT Average Profit of Makes from 1992 to 1999 average a ot Cross rot z ay ez zA C CC CHEVROLET ei fT E CO ee es gt Output Type Previewing and Saving The cross tab Request output defaults to the output type Report with Columns so you do not need to change the output for this report At this point you are ready to save your report and preview it 1 Choose Save 2 Choose Preview 3 Your report is displayed Note how some o
48. to sort and group information for your reports 4 41 Doubled headed arrow changes position of the data item in the sort order Arrow signifies descending or ascending order Switch by choosing the Descending button Figure 4 19 Sort Group Selector V Sort to organize detail lines in a specific order The lines can be organized in ascending or descending order For more information see To specify ascending descending order on page 4 44 vy Group to organize a set of related detail lines into its own section of the report You specify a data item upon which the detail lines are grouped When a change is encountered in this data item s value the current group is ended and a new group is started this is called a group break For example you could specify Cars Make as the group identifier Foxfire sorts the output primarily on Cars Make After displaying all Audi cars there is a group break The report prints subtotals in the group footer and then generates a new group header for the next group BMW s Numeric data items can be subtotaled and other calculations can be applied at group breaks For more information see Working with Group Options on page 4 46 Sorting and grouping are accomplished in the Sort Group Selector which can be opened by choosing Sort Group from the Request Editor Sort Group for Request INV_ORG Inventory on hand Stock Number Color Cars Tra
49. 1 email sales micromegasystems com or write Foxfire Customer Service c o Micromega Systems Inc 2 Fifer Ave Suite 120 Corte Madera CA 94925 Contents Chapter 1 About This Manual 1 1 What is Foxfire __ 1 1 Which Kind of Foxfire User Are You __ 1 1 How the Manual is Organized 1 3 Conventions Used in the Manual 1 3 Moving Around Foxfire 1 4 gt Navigating with a Mouse 1 4 gt Navigating with a Keyboard 1 5 Screen Controls What They Are and How to Use Them 1 7 gt To scroll using the mouse 1 8 gt To scroll using the keyboard 1 9 gt To choose an item from any of these using the mouse 1 10 gt To choose an item using the keyboard 1 10 Chapter 2 Databases and Foxfire 2 1 What is a Database 2 1 Foxfire 2 1 The Sample Database 2 2 The Database Tables Records and Fields 2 2 Relations Tie Database Tables Together _ 2 2 Knowing Your Database Makes Reporting Easier and Smarter 2 3 E R Diagram for the Sample Database__ 2 3 gt To display the sample E R diagram in Foxfire 2 4 Multiple Relationships Between the Same Two Tables 2 4 gt To see how these relationships appear in Foxfire 2 5 Inner and Outer Joins 2 5 Keep In Mind 2 6 Chapter 3 Tutorial Learning Foxfire 3 1 Basic Tutorial 3 1 Request Components 3 3 Foxfire s Organization 3 5 Starting Foxfire _ 3 8 gt To start Foxfire 3 8 Selecting the Sample Preference Set 3 8 gt To select the sample Prefe
50. 101 ALSE 4 PAOLA BHI 194740 amet 2 45 00 A2CroupeScicetod 19 Apart from the special group filters and the fact that there are two types data items group identity items and summary items summary Requests operate similarly to detail Requests 20 Choose the Open Door 21 Choose Close to return to the Request Editor 22 Choose Close to return to the Request Manager Cross tab Requests Creating a Cross Tab Request A cross tab report is a matrix that shows a relationship between two variables for all records meeting the filter criteria The results appear at the intersection of each row and column gt Creating a cross tab request See Creating a Cross Tab Request on page 3 41 for a detailed example of creating a cross tab request Business Specification We are trying to decide what kinds of cars to purchase for resale The decision on what kind cars to purchase will be based on the average profit we made for each year and make of car we sold We are only interested in cars made between 1992 and 1999 Car makes should be displayed vertically down the rows while year of manufacture should be displayed horizontally across the columns 4 76 Requcst AvPROFT Average Prefit of Makes from 1992 to 1999 Editor Figure 4 29 Cross tab Request Editor Royan APROFT Auer ip Prt of Makes tram 49 gt m 10 Crue Tal Arment uf Gi umes P vail fun Cb CHEVROLET CHR YS ER LATA bos FORE mar hou 1 r
51. 3 26 request editor list 4 11 request editor list of 3 26 request editor preview 3 26 request editor run 3 26 request editor save 3 26 request editor save as 3 26 request manager 4 5 run 3 26 save 3 26 save as 3 26 ungroup 3 23 C Calculated Data Items 4 14 Calculations adding custom 5 1 Cancelling Changes 4 11 Changing order of selected fields 4 14 Character Style 3 63 Chart 3 65 Check Boxes 1 13 Choosing Data Items 3 18 4 13 Column Output Format 3 54 4 52 Column Spacing 4 63 Column Summaries 4 78 Command Buttons in the Request Manager 4 5 Commands common functions 6 6 for dates 6 4 for strings 6 4 IIF Q 6 7 math 6 6 Comment Editor 4 8 7 1 Comments 4 8 adding 4 8 editing 4 8 viewing 4 8 Common Date Commands 6 4 Common Math Commands 6 6 Common Other Commands 6 6 Common String Commands 6 4 Concatenating Strings 6 1 Constants using in filter conditions 4 19 Context Sensitive Help 4 2 Count 3 42 in a group break 3 40 Counting number of records in Request 4 11 Creating a Cross Tab Request 3 41 4 76 Creating a Detail Request 3 16 Creating a Filter 3 19 Creating a Request 4 6 Creating an Ask at Runtime Filter 3 59 Creating Label Request 3 32 Cross tab 4 59 4 76 calculation method 3 42 4 77 column summaries 4 78 creating 3 41 4 76 filter builder 3 45 filtering 3 45 other options
52. 3 47 output type 3 46 previewing 3 46 request editor 3 42 row summaries 4 78 rows and columns 3 43 saving 3 46 sorting within 3 44 specifying column values 3 44 specifying data items 3 43 specifying row values 3 43 summarizing column values 3 45 summarizing row values 3 45 what to report on 3 43 Current Requests in Foxfire 4 85 Customizing a Layout 5 2 D Data files See tables Data Item Editor filter options 7 4 Data Item in a Filter Condition 4 17 data items available 3 18 choosing 3 18 hiding 4 14 removing from selected list 3 19 repositioning 3 19 selected 3 18 specifying 3 18 Data items removing from selected list 3 49 Data Items Selector 2 5 3 7 3 17 3 18 3 32 3 33 3 35 3 39 3 40 4 10 4 13 4 14 4 64 4 71 4 74 4 78 available list 3 40 4 13 changing order of selected fields 4 14 hiding items 4 14 removing items from selected list 4 14 selected 3 40 selected list 4 14 showing hidden data items 4 14 Database 3 50 7 1 E R diagram 2 3 files and tables 1 4 Inner and Outer Joins 2 5 Relations 2 2 relationships 3 50 tables 3 50 Tables Records and Fields 2 2 The Sample Database 2 2 What is it 2 1 DBF 1 4 dBase4 4 58 Foxbase 4 58 Foxpro 4 58 Deleting a Request 4 7 Detail Line Spacing 4 63 Dialogs defined 1 7 Directing Output to a File 3 65 Directing Output to a Graph 3 65 Directing Output
53. 5 8 System Menu 1 14 Tools menu 4 86 Merge with Other Query Results See Union Microsoft Excel 3 65 Microsoft Word 4 57 4 60 mail merge 4 60 Minimum 3 42 4 48 Movable List Items 4 14 Moving data items and text 5 1 MS Excel 4 58 MS Office 4 58 Excel 4 58 Excel charts 4 58 Excel pivot tables 4 59 Word mail merge 4 60 working with 4 58 N New Page 4 50 New page if not enough lines for group 4 51 New Request Type and Style 3 17 3 33 3 39 3 42 New Request Type and Style 4 6 No group footer 4 50 No group header 4 50 NULL 4 19 O Omit all duplicates 4 33 On line Help Messages 4 2 Open Door 3 11 3 32 3 41 3 52 4 76 5 4 Option Buttons 1 12 OR Connector 4 38 Outer joins 3 48 Output graph format 3 65 graph example 3 67 graph reminders 3 67 pivot table 3 68 pivot table example 3 69 Output Choices 3 11 3 27 3 32 3 41 3 52 3 54 4 66 4 67 4 70 5 2 close 4 67 edit layout 4 67 preview 4 67 print 4 67 show SQL 4 67 Output Destinations 4 64 output to file 4 65 output to printer 4 65 output to screen 4 65 Output file name 4 13 output information output destination 3 4 output type 3 4 Output Options 3 64 always prompt for header 3 64 specifying 4 51 Output Options dialog 3 64 Output Selector 3 7 3 24 3 26 3 60 3 65 3 66 3 68 4 9 4 11 4 29 4 31 4 51 4 52 4 64 4 66 4 71 4 72 layout
54. 7716 4 Avery 41686 2 7716 3 172 Avery 4161 2 15716 4 xj Sort list by Name Height and Width Labeltypes displayed 1 across Piia millimeters Cancel Figure 3 11 Label Type dialog 11 12 13 14 15 16 17 18 Choose 3 in the line Label types displayed Select Avery 5660 from the list of label types Choose OK to return to the Data Items Selector In Indent choose 3 to indent the text three spaces Choose Purchased From Dealer Name from the drop down list of Available fields Move to the list of Selected Data Items and highlight the line below Purchased From Name Note After placing a data item on a line if you choose another data item without highlighting the next line in the Selected Data Items list Foxfire will ask you Start a new line with data item Choose Yes or No to confirm your intent From the list of Available Data Items choose Purchased From Address In the list of Selected Data Items highlight the third line 3 35 gt To add multiple data items to a single line in the label 1 From the Available Data Items choose Purchased From City 2 Choose Comma located below the Available list 3 Choose Space to insert a space after the comma 4 Select the data item Purchased From State 5 Choose Space to insert a space after the state 6 Select Purchased From Zip In the sample label the city state and zip code should appear correctly Da
55. 8 685 3895 JGHN D HINSON 18965 115 8 8660 MARVIN KASPER 11481 5076 6415 Use Wizard JOSEPH KOENIG 111288 59611 50467 JONALD J RADER 99167 17582 18965 17407 128562 132105 ayoul Style Detaut Presentetion 7 Cancal Help Figure 3 13 Cross tab request editor 5 Inthe Name field type AVPROFT 6 Inthe Description field type Average profit of makes from 1992 to 1999 7 Choose the Calculation Method list to select the calculation function Count Sum Average Minimum Maximum 8 Choose Average 3 42 9 A prompt appears in the drop down list to the right Choose a Value 10 To indicate which numeric data item you want averaged choose Choose a Value Note that only the numeric or currency data items appear in the list 11 Choose Gross Profit ne Hequest Editor jeen 5 Choose a Value az zal What to Report on Rows and Columns You must now specify the item whose values will be displayed vertically down the rows and the item whose values will be displayed horizontally across the columns gt To specify data items for a cross tab Request 1 Select Choose a Value directly above the rows on the left side to display a drop down list The rows are numbered 1 2 3 and so on 2 Choose Cars Make from the list The rows are now headed with the legend Cars Make and several sample values of Cars Make are displayed down the rows from the database you are u
56. Customized Report Layouts 4 14 Top N Values 3 52 4 31 4 32 Number of Data Item Values 3 53 Use SQL Top N 3 54 4 33 Tutorial Advanced 3 1 Basic 3 1 Intermediate 3 1 Learning Foxfire 3 1 U Union 4 31 4 33 omit all duplicates 4 33 subordinate request 4 33 User May Edit any part of condition 4 28 data value ignore and prompt 4 28 data value ignore if blank 4 27 data value required 4 27 nothing 4 28 V Variance 4 48 Verifying expressions 4 24 viewing Comments 4 8 W Web browser 3 60 4 60 Web Page 3 60 Weighted Average 4 48 What is Foxfire 1 1 Wildcard Searches 4 34 Windows defined 1 7 Windows Explorer 3 65 WordPerfect 5 x 4 57 X Xbase 1 3 1 4 4 20 4 23 6 1 6 2 common date commands 6 4 common math commands 6 6 common other commands 6 6 common string commands 6 4 expressions creating 6 1 expressions string concatenation 6 1 functions 6 2 IIF 6 7 7 8
57. EAN ACURA 41 00 0 4 4 000 4 150 00 groups or FREMONT ZORD gt ymn ARM 4 annn values which i INVENTORY LIQUIDATION SY 2 3130 0 750 1 120 00 are being JONES CHEVROLET i 3 140 0 4516 Z 190 00 ae i MCIIUBI LNECLN MERCURY 3 5415 20 q 3 445 c 420 00 summanze L PINAM MASIDA i O ha 3 SASS SUEZ a ae Paa 1D in each line of STEVENS CREEK I VUNDA 3 101 00 J 2444 c 420 00 the report GRAND TOTALS 33 20 466 00 9 240 amp 19 00000000 9 Groups Selected All remaining columns contain summary data about the identified Group Business Specification 3 38 The Business Manager at our location wants to see the breakdown of sales for each of the dealers who purchase cars from us in the last three months having a total gross profit of more than 1 000 She wants to see how many cars have been purchased by each dealer total sales in dollars per dealer average price of the car sold average age of the cars sold and an average of how long the cars were in inventory before they were sold This report will be used to target our high volume dealers with a promotional campaign gt To create the summary Request 1 From the Request Manager choose New 2 The New Request Type and Style dialog opens 3 Choose Summary and then OK to continue 4 The Request Editor for Summary Requests opens 5 Type DLRSALES in the Name field 6 Type Cars Sold to Dealers in the Description field 7 Choose Data I
58. Exporter dialog 3 Select one or more requests to export by highlighting them in the Available list To also export the data dictionary data item definitions used in the request by checking the Export Request Data Items checkbox If the selected request s also have custom report layouts export them by checking the Export Custom Request Layouts checkbox 4 After selecting the request s to export choose Export to create the export file 5 When prompted enter the name of the export file By default the export file will be created in the Foxfire OUT directory and have an XML extension Importing a Request and Data Items After having exported a request using the Foxfire Export function see Exporting a Request and Data Items on page 4 83 you can then import the Request Data Items and Custom Report layout back into the same or a different version of Foxfire gt Importing a Request Data Items and Custom Report Layout 1 Choose Import from the Request Manager File menu 4 84 2 The Request Exporter dialog appears Choose the import file name here Impar File CAFFBOWCUTIR ION 323 gt ML Select one request from im ort file to im ort Rapes Nh Imat FIle p P it Desuriplunt List of oy LOPIES Zxamoe ol fiter only data tem 7 requests Viel 14 Lek elg fur Deals s previously exported to Select all requests from the import file import file to import List of requests selected from Re
59. Folder See page 1 120 Create Rename or Delete a Rename Folder Request Manager Folder See PIREA page 1 119 ele HE po NGET Rl Exit Figure 4 35 Request Manager File Menu The following options can be found on the Request Manager Tools menu Note that some of these options may be inaccessible based on your security rating as setup in the Foxfire User Manager Select or Edit a Foxfire preference See below Ids Preference Sets Move a request to a Display the Data Item Editor Edit Data Items F9 new folder See Batch Builder Ctrl B page 1 119 Select a group of Request to run in a batch See page 1 129 Move Hequest 9 r Show sii _ Display the Structure Run the Request and display are L the number of records returned owt pane aed Soa About Foxfirel __ Display an overview of Add view Request Foxfire See 4 3 comments See page 1 9 DPSS F8 P Display Foxfire System Administrator Ctrl U System Administration tools See paqe 1 129 Figure 4 36 Request Manager Tools menu 4 86 Choosing a Preference Set The Preference Set is the main organizational structure in Foxfire and provides control of an entire Foxfire session The Preference Set table contains one or more Preference Sets The choice of a Preference Set determines the availability to the user of Privileges and product features Requests Subsets of data files and data File relationships that
60. If only some of the criteria involve indexed data items Rushmore performs the consolidation process on those resulting in subset of records is then passed sequentially rather than passing the entire file sequentially This is called partial Rushmore Optimization Though not fully optimized it is still significantly faster than ordinary retrieval of records with multiple indexing criteria Foxfire automatically takes advantage of Rushmore whenever it can With Rushmore a large file that might take hours to filter can be filtered in minutes or sometimes even seconds Therefore it s helpful to incorporate fields that support Rushmore Optimization in your filters wherever possible they have an R to the right of the data item name in the list displayed when you build a filter condition in the Filter Builder The principle Rushmore follows is for each criterion where a usable index can be identified use that index in the consolidated indexing process to filter narrow the search then run the results through the sequential search to complete the filtering 7 2 Rushmore Can Be Accidentally Cancelled While Rushmore can produce results in an astonishingly short time it has one major weakness that causes FoxPro to revert to a much slower search method under certain circumstances This happens when the format of the comparison value does not exactly match the format of an index expression in an open index file for that table If t
61. Price Paid Dirnum 1500 PONTIAC FIREBIRD 147123 VWHITE 4 Speed 1100 535679 Figure 3 4 Request Editor Five separate windows lie below the Request Editor Each window allows you to define or modify a particular aspect of the Request v Data Items Selector where you specify the data items to be included v Filter Builder where you create filters v Joins where you can see and override the way tables used in your Request are related Sort Group Selector where you specify sorting and grouping criteria v Output Selector where you specify the form and format of the results Starting Foxfire Report Writer gt To start Foxfire Double click on the Foxfire V 6 0 Report Writer icon from your desktop After the splash screen appears the first screen you will see is the Request Manager Note that the Request Manager looks similar to the Microsoft Windows Explorer It is comprised of three sections the Request Manager toolbar the Request Manager tree and the list of requests in the currently selected Request Manager folder Selecting the Sample Preference Set When you start Foxfire the Foxfire banner screen first appears then the Request Manager opens Y Foulirel Kopeet Wailer Ke went EZAN EE et Hew WEW wcw ta gt feos pwin d z LESH eer Ful dne gt E ate DETAL y vy dereo crkey den 4 OCTAL 3 5 Brabant n Nig Hio td Note
62. R or both sides of a string PADC Work with parts of a character string for SUBSTR comparisons Use a specified number of characters LEFT starting with the left of a string Use a specified number of characters starting from the right of a string RIGT Change uppercase to lowercase or UPPER lowercase to uppercase LOWER Convert a string to initial capitals PROPER Have a numeric field interpreted as a character string STR or VAL Common Date Commands If you want to Use this function 6 4 If you want to Use a specific hard coded date Get number of days between two dates note that both fields used must be Dates and not DateTime Calculate a new date by adding or subtracting a specific number of days to a date note that Days may be positive or negative Return the name of the Day of the Week represented by a specific date i e Monday Tuesday Return the number of the Day of the Week represented by a specific date Sunday 1 and Saturday 7 Return the name of the Month of the Year represented by a specific date i e January February Return the Month of the Year as a number i e 1 12 for a specific date Return the Day of the Month as a number i e 1 31 for a specific date Return the Year as a number i e 2000 for a specific date Returns a number representing the week of the year from a Date field If you omit the optional nFirstWeek and nFirs
63. Reporting with End User Guide Compatible with all Versions of Foxpro 2 x VFP SQL Server Sybase and Arrace Version 6 0 ENTIRE CONTENTS COPYRIGHT 1993 2001 MICROMEGA SYSTEMS INC MICROMEGA SYSTEMS INC MICROMEGA 2 Fifer Avenue Suite 120 S F Y 7 5 ri T Fi E M f 5 Corte Madera California 94925 Phone 800 453 6655 Intl 415 945 3333 Foxfire is a registered trademark of Micromega Systems ACKNOWLEDGEMENTS Foxfire Report Writer is the result of thousands of hours of invaluable contributions over a twelve year period by these individuals and other too numerous to mention The late Carl E Henderson whose work spawned Account Manager the great grandmother of Foxfire David Coyle who named it Alan Schwartz Andy Neil Barry Chertov Don Robins Dale Kiefling Steven Black Jim Booth Whil Hentzen Paul Russell Andrew Ross MacNeill Jacob Bruner Mike Feltman David Tansey Alan Griver Mary Hintermeier Chris Probst Karen Higgins Mac Rubel Ceil Silver and many others who contributed effort or ideas to make it great Joanne Probst for making this guide intelligible in print Missy Philips whose graphic talents are a delight to the eye Karyn Woodsum for providing world class customer service and administrative financial support Bill Wood whose architectural vision for the future kept Foxfire alive and healthy when others said forget it Chick Bornheim who saw its potential and opened the space
64. Sk Ecpon window Hd Yon Farma WU yew Items in circles are called Renort Bands a AE Items here appear for each record selected in the euncomn query Items here appear once at Figure 5 1 Layout Editor the bottom of each page 4 10 Referring to the instructions in the section Layout Editor Features and Terminology that begins on page 5 6 customize the layout as follows Move an object In the Summary band locate the object Grand Totals Select it and move it so that it is aligned horizontally with the data items MILEAGE and PURCHPR in the same band Delete an object In the Group Footer band 1 Make choose the text object Make and delete it Move a group of objects In the Group Footer band 1 Make choose the objects Totals for and the adjacent data item object MAKE and move them as a group to the left so that they are aligned on the Left Hand Margin Then select all of the items in the group band and move them up to the center of the bad leaving the Totals for object still aligned on the left hand side Add a text object In the Group Footer band 1 Make next to the text object Avg add the text Sum of Price Paid and center align it with the PURCHPR object Change the appearance of an object In the Group Footer band 1 Make select the text objects Avg and Sum of Price Paid change the font to Bold Italic Adjust the height of the band In the Group Footer band 1 Make
65. TO RED If a record has a null in CARS COLOR it will not be selected even though the value is not Red The reason FoxPro assumes that a value of null really means the value can t be determined so it can t tell whether the condition is met or not Therefore it simply does not include that record gt To choose a relationship for the filter condition 1 Move to the Relationship drop down list for the filter condition 2 Press the SPACE BAR or click the mouse button A list of comparison operators appears 3 Choose the comparison you want to make Filter Condition Values The Value s portion of the filter condition is where you indicate the value against which all records in the database will be compared Example Is the price less than 7000 You have five options at this point You can choose one of the more common options Y Enter a constant value a number date character string or yes no choice Examples of constant values are 7000 Fremont Honda Yes 08 20 51 y Ask at Runtime when you run the Request Foxfire presents a dialog box where you can enter the value then proceeds with filtering using that value Thus you can create reports with different results 4 19 from the same Request depending on the values you specify at runtime For example this is particularly useful for accounting reports where you want to choose the period start and end dates each time the report is run Or choose one o
66. alculated group items This pattern is repeated for each group on the report Note that even though the group information is shown at the top and bottom of the group it also appears with the other selected data items 4 52 Page numbers at the top of each page Group header information including group value Group footer information including group value and calculations Page Numbers ON Column Headings at the top of the paae Request SAMPLE Invertory On Hand Pace 1 Stacks ake Motel Aileage Color Price Paid Trans Pirr 3 Speec MLM 3 Sp r SUC 3 Speec MLM teiralic ot Data associated with each selected Data Item displayed in columns Y Form Output Format In this output type only the page numbers are displayed at the top of each report No group information is displayed The selected data item column headings are displayed to the left of each data item and the data for each record which appears on the report is displayed vertically on the page with a single blank line between each record Note that all selected data items appear vertically down the page even those selected as group fields Request SAMPLE1 Inventory On Hand Page i Poa ea Data Items i Mardel smn i Mileage 1101 i Calor SIIVFR i Price Paid 16m Trans 3 Speed Pure MLM Stack 2018 Make ALDI Model 50008 Miloage 923835 Color BLUE Price Paid 1 600 Trane 5 Speed Pure SLCP y Master Detail Group H
67. ands for each data item marked as Group By Prints at the bottom of each page of the report Prints once at the end of the report These are the fields that you selected in the data item selector These are labels that have been extracted from the Data Dictionary built by Foxfire for group summary data or placed there by you Based upon the Layout Style you ve chosen these 5 1 items can add a polished look to your request Copying a Request In order to preserve the original Request we will make a copy and customize this copy gt To copy a Request 1 In the Request Manager highlight Samplel then choose Edit 2 Choose Save As 3 Name the new request INV_CUST 4 Choose OK then Close to return to the Request Manager Steps For Customizing a Layout Figure 5 1 shows the Layout Editor s report layout window You access the Layout Editor from the Output Choices window gt To start the Layout Editor from within Foxfire 1 Preview the Request INV_CUST 2 When the report displays in the Output Choices window choose Edit Layout 3 The Layout Editor opens displaying your report layout Items here appear once at the top of the report Items here appear once at the top of each page Items here appear each time a new Cars Make is printed Items here appear just before a new Cars Make is printed Items here appear once at N the end of the report Y toniel Bepul Wider Ziz
68. ants a report showing from whom each car was purchased and to whom it was sold The query for such a report would have to access data from two different dealer records for each car record let s call them the Purchased From dealer and the Sold To dealer Each car record has to be related to two dealer records at the same time and each relationship would require a separate relationship expression with a different identifying item in the Dealers table Foxfire employs a clever trick to simplify reporting when there are multiple relationships between two tables each table can be given multiple alias names one per relationship and each alias is treated like a physically separate table The result A single physical table with two aliases can be related to a another table by two different relationships So when the broker runs his query Foxfire can join each car record to two different dealer records gt To see how these relationships appear in Foxfire Open the Data Items Selector to see how data items from both the Purchased From Dealer relationship and the Sold To Dealer relationship appear in the Available list as though they came from two physically separate tables files Creating a report that shows the names of both dealers associated with each car is a simple matter of choosing the Purchase From Dealer Name and Sold to Dealer Name data items Foxfire knows how to set up the relationships using the two tables and t
69. araaye Piufil of Makes fiur 1992 lu 1999 Cras Tah Aserage of Grose P imi fne Make by Year Year Make fuera ge 1982 1993 13994 1935 1995 MADA 500 46 456 52 26575 496 00 1 5 00 250 MERCEDIS 1 507 220 0 31 340 00 MCROURY p 014 42 q 700C p 9000 p 9 00 c10 X3 MITSLEISHI 395 00 453 75 510 Araraga 192 02 315 00 SO 2 67 140 56 4418 57 Figure 4 31 Cross tab with row and column summaries Label Request The procedure for creating a label is similar to that of creating a report Request You specify the data items to be included filter conditions sorting and grouping criteria and the output type But labels have different output options than reports Labels can be printed in columns and are formatted to fit a specific type of label Data items for city state and zip usually need to appear in the same line To deal with special label formats Foxfire provides a special Data Items Selector window for labels It allows you to select from many commonly used label formats including Avery forms As you select data items you 4 78 specify their location on each line of the label adding punctuation if necessary Data Items foi Request DEALLAB Labels for Dealers We Purchased Crummy Cars From Lasel Tyge siz xisns x1 choose Il Data tems Age at Sale Color Indent Date OF Sale p Date Paid Date Purchased Days in Inventory Full Date of Sale License
70. ars on the report the group header is immediately followed by the records for that group Each record contains data for all of the selected data items except for the group by items since this data was just displayed in the group header At the end of the group the group footer information is displayed This request output type is good to use when you have more data items selected then you can display on a printed report since the group by items are pulled out of the widest part of the request It is less cluttered than the Master Detail Group Headings output type because the column headings only appear at the top of the page rather than at the top of each group Request SAMPLE1 Inventory On Hand Column Headings appear at Page1 the top of the page only Stock Model Mileage Color Price Paid Trans Pure Make AUDI 2448 5000 110291 SILVER 1 500 3 Speed MLM 2018 5000S 92836 BLUE 1 500 5 Speed SUCP 2387 5000S 129127 GREY 2 000 5 Speed MLM 2389 5005 THB 5 000 Automatic ABI Totalsfor Make AUDI Aug 101505 10 000 Make BMW 2251 320I 91623 2 000 4 Speed RMS Totalsfor Make BMW Aug 91623 2 000 Other Output Types and Formats In addition to the standard Report Output Type Foxfire also offers many other extensible Output Types and formats These other Output Types make it simple to integrate your reporting data into executive level presentations financial models and other client deliverables While these
71. atabase reports may incorporate data from multiple tables For example a report that shows the make and model of all cars sold by a specific salesperson must use data from the Cars table as well as the Salesman table Likewise a report that shows all expenses incurred for cars sold in a specific month must use data from the Cars table and the Expenses table To produce reports using data from multiple tables the tables can be related joined to one another Once specific relationships are defined in Foxfire s data dictionary Foxfire automatically joins the appropriate tables when data items from two or more tables are included in the same Request If Foxfire has already been customized for you then the relationships are probably defined already as part of that process Knowing Your Database Makes Reporting Easier and Smarter The way to gain the most benefit from Foxfire is to have a good understanding of your database what information is in it and how that information is related To understand how tables are related in a database review Entity Relationship diagram E R diagram for the sample database shown in Figure 2 1 An E R diagram shows all the tables in the database and the relationships between them It can be a helpful exercise to consider the E R diagram for your own database tables or draw one if needed E R Diagram for the Sample Database The E R diagram for the sample database appears in Figure 2 1 It shows the f
72. be subsequently imported back into Foxfire using the Import option see page 4 84 This feature is useful if you have multiple copies of Foxfire which are installed on separate computers but share the same data dictionary or if you are developing requests for someone else You may also want to use this feature to create text based backups of your important requests to help mitigate the risk of someone editing an important request gt To export a Request Data Items and Custom Report Layout 1 Choose Export from the Request Manager File menu 2 The Request Exporter dialog appears 4 83 Move one request i Repas Foquates from the Available list Avaiabe Peqesis to the Export List Nae exii hm lax M siia Iiri in oe rrentory on hans ILAA UUU SYSTEW WALOFIZS Cxsmpe ot fiker onty data fein 1cA 2000 SYcTcv SI GSPN Large exp iss fir eari or 175711999 SYSTFW M WAL_LBL Meling Lekels or beers 12171999 SYSTEW ove all requests to WULTJON Jore faut atfcrend zabes 121999 NETEN the Exnort List P3OTLANI Prot anaysix ay realer TPAGANN SHETTY GUSRTLE i artle ran lt prot 13171999 SYSTEW kune Nawest estu es ot Sek st lt untire AAT AUUU SYS IEW SAKPLIZ hwerndory on hanz 1C 22 2000 SYETE Foca rl Ranex x Move all requests back m to the Available list Move one request back to the Available list gi Iv Czpzit Rezpes Cala teins txor Cancel I Expzrt Custom Rees Lavo ts oga Figure 4 34 Request
73. buttons to add or insert a comma period colon dash or space between data items This is especially useful when city state and zip codes are separate data items rather than combined in a single data item Use the Text button to add or insert text into a line of the label such as Attn or c o Filtering Sorting and Grouping in Labels Note that all of the Filter Sort Group and Output features available in a detail request are also available for a Label Filing Requests in the Request Manager Now let s file this new Request in the Sales Reports folder of the Request Manager 1 Make sure the DLRSALES request is highlighted 2 Choose Tools then Move Request then Request then Sales Reports from the menu OR Right Click on the request and choose Move Request then Requests then Sales Reports 4 81 Request Manager Requests Root Ee view Tooke Pareles rece Fes a Fi an AI Edtiste tems atoae Rey ties Ve Requester _ Batch Bite Sih eFader M59 Recuesly R a a Stew SCL LD Exe ples of Special Feau es GQ ine in Oe iaerFe leu Fadel Hiei 2 motin opole Lomnanls 1 M Air analysis by deaer Prt SPS Sea Lc Zaes Report Orizi ah Prt Suzien Admicizzalo CURU yy zacamzn Ful Kame biano Hot Notice how the Request seems to disappear from the Request Manager 3 Click on the Sales Reports folder in the Request Manager tree Notice
74. c and destination printer screen or file For reports this also determines the format Request Editor A report sample with one line of sample data gives you a preview of the repor s appearance Description A Inventory on hand Original Request C bena Inventory on hand Original Request Stock Make He 1500 PONTIAC FIREBIR Mileage Color Trans Price Paid Dirnum Q 147123 WHITE 4 Speed 1100 535679 E SORT GROUP Determines Cars Date Of Sale Is Empty t Cars Make Grp primary Cars Color Is Like ask optional t Cars Model secondary and B DATA ITEMS Specifies additional the contents of the detail line sort orders Output Request Type Detail and column headers Master Detail Report vw Page Headings To Screen Layout Style Presentation Run Preview Count Show S L Save Save as Figure 3 5 Sample Request Note The callouts A E correspond between the Sample Request and the Resulting Report 3 15 A NAME and DESCRIPTION Specifies the report name D Output TAE Type Report Primary Sort is by Cars Make secondary sort is by Cars Model There is a group break when Cars Make changes B Column headings and C Filter Criteria Cars Date of Sale is Empty and Cars Make is in the List BMW BUICK NOTE that the second Slakt hunt Meg Chile rice filter condition has been added t
75. can format your report to increase the distance between columns Choose the number of spaces to insert between columns gt Layout Customization Edit Choose Edit when you want to modify the layout This opens FoxPro s report Layout Editor or Label Editor if your Request is of the Label type See Chapter 6 Customizing Report Layouts for assistance in using the Layout Editor When you save your changes Foxfire saves the report layout in a file with the same name as the Request If an FRX already exists with that name Foxfire prompts you to enter a name for the layout The name is displayed in the box immediately above the command buttons in the Layout section of the Output Selector see Figure 4 25 4 63 When a Request is using a custom report layout the FRX is listed here Output Type Report Columns faya lt Auto Format from Template Style atomization Defaut Presentation Edit Choose i ptic E Save as Auta Formet Figure 4 25 Name of FRX Indicates Custom Report Layout in Use For information on working with customized report layouts see Tips for Customized Report Layouts on page 4 70 Save As Choose Save As to save the report layout FRX LBX for labels under another name A Save As dialog opens for entry of the new name This allows you to copy a report layout for later editing and as a quick start in creating a new report from an existing Request Choose The Choo
76. cter or date data items as rows in the generated pivot table In this example the row consists of the data items Cars Make and Cars Date Purchased Numeric data items are by default summed and form the core of the pivot table around which the character and date data items pivot If there is more than one numeric data item then a column button labeled Data is created to represent all numeric data in the pivot table In this example Cars Purchase Price and Gross Profit are summed separately but can be arranged vertically or horizontally in the pivot table by dragging the Data button Once the pivot ttable is created you can rearrange the table column and row fields to change the analysis of you r data When you move a selected row column or data field to a new position Excel automatically recalculates the data giving you a new perspective on the data The steps below take you through a simple rearrangement of the sample pivot table For more detailed information on using pivot tables consult Excel s User s Guide and on line help 3 69 gt To change the layout of the sample pivot table 1 Select the row button labeled Purch Dt and drag it straight up to the top row of the pivot table to the immediate left of the Data button As you drag the Purch Dt button notice how the table icon associated with the mouse cursor changes form to indicate whether or not the data that you are dragging will be positioned as a row or column W
77. d Ur t Delete Union Top I T Omit Cuplizetes Ask al Runtime Optiors Help Distu wary cance 13 To save this filter condition choose OK 14 The Filter Builder displays your completed filter condition 15 To add the second filter condition choose Add 16 Choose the data item name you want as the second filter condition Cars Make 17 To specify a positive comparison choose Is in the Is NOT operator 18 Choose Relationships to indicate the type of relationship 19 A drop down list appears with a list of possible relationships that are relevant to the data item 20 Choose Like 21 Under Value s type TOYATA intentionally misspelled and press ENTER Toyota misspelled as TOYATA Foxfire automatically validates entry Data ltem Cars Date Of Sale inerentory on hand value si Blarks Another Data Item Build Expression 3 21 22 When you try to exit the field a list of valid options appears This list was set up in the data dictionary and can be used to validate the values being entered at Request design time or run time 23 Select TOYOTA from the list and press Enter 24 To save this filter condition choose OK 25 The Filter Builder displays your completed filter condition Filter for Request SAMPLE1 ineventory on hand Ea Data tem Is No eletionshi Vahe s Cars Date Of Sale Is Empty i Edi
78. d RMS Totalsfor Make BMW Aug 91623 2 000 3 58 Advanced Tutorial Creating an Ask at Runtime Filter A common situation in reporting is the need to run a report many times each time with a small variation in how the report is filtered Perhaps you want to run the same report but for a different customer or a different range of dates Foxfire provides this kind of reporting flexibility with an Ask at Runtime filter In this exercise you will edit an existing Request JALOPIES to add an Ask at Runtime filter condition gt To create an Ask at Runtime filter 1 From the Request Manager choose the Request JALOPIES under the Examples of Special Features Folder Choose Edit from the toolbar Choose Data Items Add the data item Cars Date of Sale to the Selected list Choose Filter from the navigation bar Choose Add to add a new filter condition Choose Cars Date of Sale from the Data Item drop down list Choose Between from the Relationship drop down list Oe So ge a Leave the Value s entry blank Instead choose the Ask at Runtime checkbox 10 Choose OK then Run from the Request Editor 11 You will see the ask at runtime Enter Values dialog Enter the heading Jalopies sold in May and June 1989 Enter the date range 05 01 89 06 30 89 Examine the Request in the Preview window You ll see that only jalopies low cost high mileage cars sold during your selected date range are listed Note F
79. d hai Return the next highest integer that is greater than or equal to the specified CEILING numeric field Return the next lowest integer that is greater than or equal to the specified FLOOR numeric field a the integer portion of a numeric INT Return the minimum value from a comma MIN delimited list of numeric fields or values Return the maximum value from a comma delimited list of numeric fields or MAX Common Other Commands 6 6 These commands will return Yes or No as an answer and can be used in the IIF command discussed below If you want to Use this function Determine if a specific field value is between two other values or fields Note that the data type of all three fields specified in this command must be the BETWEEN same For character fields Yes is returned if the first field is alphabetically between the second and third values Return Yes if the passed field is empty All data types are supported EMPTY Return Yes if the first field passed is found in the comma delimited list specified in the following parameters INLIST Note that the data type of all fields specified in the command must be the same IIF IF THEN ELSE Support Foxpro has a command that will return one of two values based on the result of a Yes No expression The Yes No expression can be made up of any series of Foxpro commands that return a Yes No result The syntax for this command i
80. defaults to In Request and displays only the numeric data items that were included in the Request To display all numeric data items in the dictionary choose All Calculation Buttons Foxfire allows you to add the following calculations to group footers For the overall Request Count The number of rows contained within the group For numeric data items only Sum Average Weighted Average Minimum Maximum Standard Deviation ARALAN Variance Note You may add as many calculations as you want Each occupies one line in each group break or in the report summary area You can rearrange the order of appearance of the summary calculations here or place them anywhere you wish in the corresponding group by and of the Report Layout Editor See Edit on page Editing Requests that Generate Customized Reports4 71 for more information on editing a report layout 4 48 gt Adding a calculation to a group break Ordinarily Foxfire sums numeric data items down to the grand total You can also add counts for non numeric data items or other statistics for numeric items Summary calculations can be placed v Ina group footer group summary V At the conclusion of the report summarizing all detail lines in the report 1 Highlight the Request SAMPLE and choose Sort on the navigation bar Note the data items in the Request and the Sort Group data items 2 Choose Options from the Sort Group Selector to
81. desired changes to the chart Consult the Excel documentation for chart formatting options As you experiment with charting your query results in Excel you will find that not all queries make sensible charts Or to put it another way not all chart types can sensibly be used for any given query Be careful when charting detail Requests Detail Requests with non numeric data in the result set usually do not make sense as charts This is because each row in the query s result set generates an X axis label in the chart For example if you attempt to chart the sample Request Inventory on Hand using no filter Excel will attempt to chart over 900 different car makes on the X 4 58 axis of the chart The resulting chart is barely readable and obviously does not provide a useful picture of the data Generally the only Requests that produce useful charts are those that contain columns of numeric only data Line charts are useful for this type of data An example of this type of chart using the sample data set would be to plot the price received vs the expense repair amount A line chart could perhaps illustrate any correlation between car price and repair expense Summary and Cross tab Requests are usually best for charting when there are non numeric result columns because grouping is forced in these types of Requests However you still must be careful that the total number of rows in the result set is minimal Particularly summary Request
82. dialog 5 11 format menu 5 11 format toolbar 5 11 formatting text objects 5 10 layout controls toolbar 5 7 making changes after customization 5 5 moving a group of objects 5 10 moving a single object 5 10 report bands 5 1 5 6 report controls toolbar 5 7 5 9 report menu 5 8 ruler 5 7 7 4 saving 5 4 toolbars 5 7 types of objects 5 1 view menu 5 8 Layout Options 4 29 4 31 4 61 formatting 4 62 column spacing 4 63 detail line spacing 4 63 formatting 4 63 formatting options 4 62 showing filter conditions 4 62 formatting 4 63 suppress repeat values 4 63 formatting 4 63 Layout Style 3 60 4 9 4 60 auto format 4 61 changing 3 60 character style 3 63 4 60 character web page style 4 60 character web page 3 60 choosing one 4 61 defined 4 60 examples 4 9 executive style 3 62 executive type 4 60 layout options 4 61 ledger style 3 63 4 60 presentation style 3 62 presentation type 4 60 LBX 4 64 Ledger Style 3 63 Like 4 41 Limiting a Request to the Top N Values 3 52 Lists defined 1 9 Drop down Lists 1 10 Dual Lists 1 11 Movable List Items 1 11 Lock 4 13 Lotus 4 57 M Mail Merge 4 57 Master Detail Group Headings Format 3 56 4 53 Master Detail Page Headings Format 3 25 3 57 4 54 Maximum 3 42 4 48 Menus 1 13 4 85 File menu 4 86 Foxfire Pulldown Menu 1 14 view 5 8 report layout report
83. ding of your choice Choose OK 13 Review the report again in the Page Preview This time only the 3 most profitable Broncos and Escorts are listed 14 Choose OK then Close the Output Choices window to return to the Request Editor Note that the other option on the Top N dialog Use SQL Top N gives you the option to show only the top N records selected or top N percent of all the records selected for the request If you select this option you will only get N records shown on your Request Whereas if you do not pick this option you will get the top N records in the group that you specified in the dropdown Report Output Types Foxfire offers four 4 standard output format for Report Output Types These different Report Output Types can also be used to modify the layout of the report so that more data can be displayed without having to use the Fit to Page option discussed above This section describes each of these different output formats and provides examples using the Sample report you created earlier Column Output Format 3 54 Page numbers at the top of each page Group header information including group value Group footer information including group value and calculations a Invertory On Hand In this output type each page of the report shows the current page number the column headings associated with each selected data item For each group identified on the request the group heading and
84. e may create output files for the Request which all have the same name as the Request short name each additional concurrent user is asked for a temporary name gt To preview a Request 1 Highlight the Request you want to preview 2 Choose Preview Previewing a Request means displaying the Request output on the screen for review regardless of the output choice specified in the Request For more information see Preview on page 4 66 gt To create a Request 1 Choose New The New Request Type and Style dialog opens For more information see Creating a New Request on page 4 6 New Request Type and Style x Example of Detail Repor F Stack Model Price Paid Mileage Color Trans Make PLYMOUTH 2118 CARAVELLE 3 100 61233 Autonatic 2418 RELIANT 725 118555 WHTE 3 Spsed 2220 SEDAN 79038 3 Speed 1658 VOYAGER 34738 CREAM 5Spzed Totals forMake PLYMOUTH Aug 15 925 73391 Make POHTIAC 2629 CATALINA 165 194441 BLLE Aulurrralic 2360 FIREBIRD 1 000 109420 BLACK Automatic uest Type ayou Style Coa Detail Summary Deteut Presentation z Cancel Cross Tab F Labels Help gt To edit a Request 1 Highlight the Request you want to edit 2 Choose Edit 3 The Request Editor opens Fore more information see Request Editor on page 4 7 Editing a Request allows you to alter the elements that make up the Request Note If Foxfire is installed on a network be a
85. e Description sampo BM or Audi cars in inventcry with expenses Data Items 07 03 2000 BMW or Audi cars in inventory with expenses Expense Repair Stock Make Model _ Color Pure Date Filler Oins Sort Group Cars Meke Is Like BMV t Cars Make Gp n t Cars Model Care Meke le Like AUDI Quiput Request Type Detail Master Detail Report wiGroup Headings To Screen Layout Style Presentation Run Preview Count Show SGL Save az Cencel 10 Choose Save As Name the modified Request SAMPLEOJ Add with expenses to the description 3 49 11 Choose OK Run the Request Note that Audi 2387 and 2389 are no longer listed This is because there are no expenses for these vehicles When a Request contains data from two linked tables by default Foxfire automatically excludes records that have no link to information in the other file Perhaps this is not what you wanted though Suppose you wish to see all vehicles regardless of whether they had repair expenses or not In database terminology this is an outer join Foxfire makes it easy to adjust this Request to create this result You can override this default behavior when setting up your Foxfire data dictionary or on a Request by Request basis The instructions below show how to override this setting for one Request 1 Choose OK to close the Page Preview window then Close to return to the Request Editor 2 Choose Joins
86. e cursor is currently positioned by displaying horizontal and vertical lines Y Formel Deport Writer Tke Id Nepot window leb Yes x a __ Tr i 9 a AU Sa Fr 9 Bf jirumonan a FE Pad iF pagenn im Alapdoy r E a Psae llesde pene Tate Item Cajact Mileage ninw i Prine Paki Trana Mer a iuus deena HORE receys Mote LESAGE pe T Pare reas avg Ered iisti a Bard Separstor Zar Separstor Zar apl Hid f Totals tor PAARL MILLAGL FUIF a fimin avu PMSF d PiyrFeria y pm on oor nec GRAHD TOTALS MILEAGE PURCHPR ENE Caha her thon CETELE l Yovie Yetis 104 I yz va SOT HLY 5 Figure 5 3 Layout Editor Controls The report layout window displays the objects that make up the physical appearance of the report These objects include v Text objects Text is treated as an object which can be selected edited moved or deleted These objects originate from the Data Dictionary and Calculation Types 5 7 vY Data item field objects Data item objects display the data from a table from a calculated value or from a UDF yY Graphic objects Lines rectangles and pictures are available for use in reports and enhance the readability of a report The Layout Editor provides menu options and control buttons to add delete and manipulate these objects View Format Browse Fftemp01 Preview the 7 edited layout Table Desig
87. e fill in fields buttons check boxes option buttons drop down lists and other kinds of screen controls Descriptions of the various screen controls follow Choosing a button begins a process for example choosing a button may open or close a window or dialog The button s face contains text that suggests what will happen when the button is chosen Whenever the text 1 7 of a button is followed by ellipses a second window opens when the button is chosen Edit Save aS Figure 1 2 Buttons To choose a button using a mouse click on the face of the button To choose a button using a keyboard use the TAB key to highlight the desired button and press ENTER A button is highlighted when a border surrounds it Each press of the TAB key moves the border from one screen control to the next Scroll Bars When there is more text or data items than can be displayed in an area a scroll bar may appear at the bottom or right side of the area Scroll bars permit you to see beyond the apparent boundaries of the area through an action called scrolling thus the term scroll bar Avaiabe lj Cars Coremen 1 a4 Cars Dale Of Rabe 9 H mM Scroll bar Cars Dale Paint 9 Cars Ral Purshanssl 3 Cars Days in Tuumulury in Cars liemane Hamer R Cars Mih Boll R Cars Purihasril From TD s Cars Rawlins 4 Cars Rale Prine 11 Cary Raleanain Til a Cary Rabil Tu Penley Tal a Figure 1 3 Scroll Bar Available and Selected
88. e filter is specified in the ASk at Runtime Options dialog where editing controls as well as a prompt and help text to assist the user in completing the filter are specified gt Specifying Ask at Runtime Options for a Request To specify controls on filter editing choose Ask at Runtime Options from the Filter Builder Edit Add Or I Omit Duplicates Union Top N Ask at Runtime Options Ask at Runtime Options button in the Filter Builder Figure 4 15 shows the Ask at Runtime Options dialog where control of the Ask at Runtime interface is specified Ask at Runtime Options Allow the user to enter values only at runtime simple dialog Allow the user to edit the entire filter at runtime a comprehensive dialog Like TOYOTA Nothing Date Of Sale Is On or After Cask at run Data Value Entry Required gss Profit Is More or to 1666 66 Nothing drs Year Is Between 1992 1999 Nothing Allow the user to add and delete entire filter conditions at runtime Runtime Prompt Changes made here are for this run only Enter a prompt and help text which can be viewed at runtime Runtime Help Edit Cancel List of all filter Place additional controls i s conditions for on what the user may Figure 4 15 Ask at Runtime Options the Request edit in each condition 4 25 The pair options at the top of the Ask at Runtime Options dialog Enter Filter Values Only and Edit Ent
89. e menu pad containing the pulldown that you want to access Alternatively you can press Alt the underlined letter found on each menu pad If the menu does not appear on your desktop simply press the ALT or F10 key Note that whenever the text of a menu option is followed by ellipsis it indicates that a window opens when that item is chosen You can then choose several options from that window to continue with your originally chosen option For example Selecting the Run menu option from the File menu runs the request possibly opening a dialog box containing ask at runtime prompts Chapter 2 Databases and Foxfire This chapter provides a brief introduction to basic database concepts and explains the sample database used in the Foxfire tutorial For a more in depth discussion of database concepts see Indexing and Rushmore in Chapter 7 and Getting Started in FoxPro in the FoxPro software documentation You may want to refer back to this chapter as you work through the tutorial What is a Database Foxfire A database is a collection of descriptions or measurements of real world things or events These descriptions or measurements are called data A database management system DBMS is a computer program that allows you to collect store and manipulate data in different ways to achieve a specific purpose Relational database management systems RDBMS allow you to track relationships between diff
90. eadings Format 4 53 In this output type each page contains the page number at the top of the page For each group that appears on the report the group header is followed by the column headings for the selected data item The records for that group are then displayed Each record contains data for all of the selected data items except for the group by items since this data was just displayed in the group header At the end of the group the group footer information is displayed This request output type is good to use when you have more data items selected then you can display on a printed report since the group by items are pulled out of the widest part of the request Note that the group data does Column Headings not appear in the with the appear at the top of column data each group Request SANPLE1 Inventory On Hand Page 1 Make AUDI Stod Model Mileage Color Price Paid Trans Pure 2448 5000 110291 SILVER 1 500 3 Speed MLM 208 500S 92836 BLUE 1 500 5 Speed SLOP 2387 50008 129127 GREY 2 000 5 Speed MLM 2389 50008 THE 5 000 Automatic AEI Totalsfor Make AUDI Aug 101505 10 000 Make BMY Stock Model Mileage Color Price Paid Trans Pure 2251 320 91623 2 000 4 Speed RMS Totalsfor Make BMW Aug 91623 2 000 yv Master Detail Page Headings Format In this output type each page contains the page number and selected data item column headings at the top of the page 4 54 For each group that appe
91. eaks 4 44 group formatting options 4 50 group options 4 46 how to 4 43 removing items from sort 4 43 selected items 4 43 sort 4 42 7 7 Special Filter Options 4 31 Special Processing Options 4 66 Specify Output Type 3 24 Specifying Data Items 3 18 Spreadsheet 3 65 4 56 SQL 4 74 Standard Deviation 4 48 Starting Foxfire 3 8 subordinate request 4 33 Sum 3 42 4 48 Summary calculations 4 46 summary data 3 38 4 73 Summary Request 4 59 4 72 aggregate data item 3 66 creating 3 38 3 39 3 67 4 73 data item selector 3 39 group identity items 3 38 4 73 request editor 3 39 specifying data items 3 40 summary data 3 38 4 73 Summary Requests selecting data items 4 73 Suppress Detail Lines 4 63 Suppress Repeat Values 4 63 Suppress Zeros in Cells 4 63 System Administration Utilities 4 88 adjust data items to match indexes 4 90 edit data items 4 89 edit file relationship info 4 89 edit label template 4 90 edit preference sets 4 89 edit report template 4 90 import data items from file 4 89 join editor 4 89 refresh sample values in data items 4 90 reindex Foxfire system files 4 90 setup wizard 4 89 show configuration info 4 90 user account manager 4 90 user license monitor 4 91 view query log 4 90 System Menu 1 14 Accessing 1 6 T table outputting to 3 65 tables 3 50 7 1 defined 1 4 The Sample Database 2 2 Tips for
92. ecessary stretch the item to the required width by dragging its rightmost border Adding Objects gt To add text 1 Choose the Text tool 4 2 Click at the desired location and type the text gt To add a line 1 Choose the Line tool 7 2 Position the cursor where you want the line to start then drag to size the line 3 Note For a double line draw two lines and place one on top of the other Turn Align To Grid off first gt To add a rectangle 1 Choose the Rectangle Ol or RoundedRectangle Oh tool 2 If you are adding a rounded rectangle double click on the rounded rectangle to open the Rounded Rectangle dialog Use the options to set the corner style Creating Custom Totals and Calculations Foxfire automatically calculates most of the summary information you need While you can insert a custom calculation here generally it is preferable to create calculations using the Group Options features Chapter 6 Xbase Fundamentals Creating Expressions Part of creating database reports and documents is manipulating the contents of the data to make the more presentable Much of the time you will be able to use data items as they come straight from the database But in the case of mailing labels you usually have to create a custom data item Foxfire uses Xbase expressions to permit you to create combinations of smaller data items and to modify the contents of data items When you combine data items o
93. ected to the printer you selected until you specify a different printer To specify a new printer configuration choose Setup and enter the printer parameters To change instructions for the currently selected printer choose Options below Setup Note Ona network all users running the Request will be directing it to the specified printer Be sure to specify a network printer Output to Screen Choose Screen to preview results on the screen When you run a Request with Screen as the destination Foxfire displays the result in a window See Preview on page 4 66 Output to File 4 65 Preview When you want to store the results of a Request to disk choose File A dialog box opens prompting you for a file name for the Request output The default file name uses the same name as the Request If the Request is called INVENTRY the default file name is INVENTRY RPT Type a file name or leave the default name and choose Save Note If you have directory access privileges the dialog box allows you to select drive and directory designations If not your output is restricted to Foxfire s default output directory gt Special Processing Options The Special Processing Options at the bottom of the Output Selector is an advanced feature which requires some knowledge of programming For more information consult the Foxfire Reference Manual The Preview window allows you to view a Request s output on screen befo
94. elected data items These data items will be included in each detail line of the report Every time you choose a data item from the Available list it moves to the Selected list gt To choose the data items 1 Choose the following eight data items as specified in the business specification above Cars Stock Number Cars Make Cars Model Cars Mileage Cars Color 3 18 Cars Trans mission Cars Purchase Price Cars Purchased From ID Note The sequence in which you choose the data items is the same order in which they will appear in the detail line of the final report 2 If you want to remove a data item from the Selected list choose the item in the Selected list 3 The item moves back to the Available list 4 If you select the items in the wrong order you can use the mover buttons in the selected list to reposition the data items 5 To accept the data items you just chose choose OK 6 The Request Editor displays a sample detail line containing the data items you selected Creating a Filter Once you have chosen data items to display you may also want to specify a filter to narrow the results In the sample Request you previously examined the report was filtered to show records where the data item Cars Date of Sale is Empty and Cars Color is like a value specified when the request is run Now you will create similar filter conditions but instead of filtering on Cars Color you will filter on Cars Make and
95. ent and read more closely when you see particular areas of interest This chapter contains a complete description of all of Foxfire s features and functions You can always go back later to find out how a specific feature works Customizing Report Layouts Refer to Chapter 5 when you want to make cosmetic adjustments to Foxfire reports You may not be a programmer but you know FoxPro well enough to type commands in the command window and use the built in FoxPro menus You use Foxfire to simplify reporting and to speed up the process of querying the databases of the FoxPro applications you work with Work plan Chapter 2 Chapter 3 Chapter 4 Chapter 5 Understanding the Sample Database Tutorial Learning Foxfire including the Advanced exercises Using Foxfire Survey the full functionality of the product find out what it can do for your particular application requirements Customizing Report Layouts Refer to this chapter when you want to make cosmetic adjustments to Foxfire reports Chapter 6 Xbase Fundamentals Refer to Chapter 6 when you want to build expressions for data items or filter values Chapter 7 Indexing and rushmore Refer to Chapter 7 to understand more about how you can improve the query speed of your Requests through Rushmore indexing Refer to chapter 1 for information on basic concepts if necessary How the Manual is Organ
96. environment into the File Relationship Editor 4 89 Edit Report Template When Foxfire creates a new report layout it starts by copying from a template which specifies the basic arrangement of the layout Each layout style may have a different template This option lets you use the FoxPro Report Layout Editor to modify templates For example if you want every report produced to include your company name or logo at the top you may use this feature to add it to the template For details on editing of reports see the FoxPro documentation or Chapter 6 Edit Label Template Works the same way as the report template see above If you want to adjust the way your labels are created selecting this option lets use the Foxpro Label Editor to make and save changes to the template For details on editing of labels see the FoxPro documentation Reindex Foxfire System Files Causes the Foxfire support files as specified in the currently selected Preference Set to be packed old records removed and reindexed Note if using a Network Reindexing requires require exclusive access to the files so if others are using Foxfire it may not be possible to reindex Adjust Data Items to Match Indexes If you have changed any index tags on your application s data set this option re synchronizes Foxfire s data item where expressions to the tags insuring optimization Refresh Sample Values in Data Items Replaces the
97. epor writer for Foro Fuxfie pruvdes o maiagi query enviru menl su end users Can quickly a d easily Creede ad hur queries end resorts Foxfrel offe s a user chcices in familiar language s2 they can focus on their resans withcul worrying about technical detalls Tha sottoni Ine goct proot end usr regortinc For dveopers oxtirel is n ideal way to imeyrate tull ad hoz yue wrerortine wih Custcm apps Full gorrec cade deca dctionary anc ogic hool s provide a level of control and custonizetion unavailable in cther commercial grade report witars Raview toe list of topics a the hwer left for more information To review Freoctirel s angarizatnn nirs un sMe ul Fustrel Tu review hz san ple dalakase fur lhe Lulurial click un sCeenu Fils Relativi ship Dagran gt For more irformatior choose How o Crder gt May of Fustire Denu Fle Rekabu ships Abort Mizromega Acknowkdgemens Ho te Order Figure 4 4 About Foxfire Request Manager The Request Manager is the control center for Foxfire It lists existing Requests and provides access to the functions for creating new Requests and provides access to the functions for creating new Requests or editing existing ones Command Buttons List of Requests ry Fasfirel Report Writer Be Bj Je Ezit Whdw ddp l lequrst Manages lleguests linnt qs Rr Pe ci we hee Rai Dele Cat Dri msy x Req Tse hre Lory unes Ogi a Beruezl
98. equest SAMPLE1 Inventory On Hand Report gt Master Detail Page Headings 8 Choose OK to return to the Request Editor You have just created your first Request Examine the Output section of the Request Editor It indicates the Request Type is Detail and the output will be Master Detail Report w Page Headings To Screen Other Request Editor Buttons The Request Editor offers several other options Preview Displays the report on the screen even when output is set to print or file Run Processes the Request and creates the output If output is set to screen Run does the same thing as Preview Save Press this button to save Request changes to disk Save as Use this when you want to make a copy of an Request under a new name or to make another version of the Request Whenever you run or preview a Request Foxfire compares each record to the filter conditions selects those records that meet the conditions and then arranges the data items in the manner you specified in your Request 3 26 gt To preview the Inventory on Hand report Choose Preview The report appears on screen Note These preview screens are displaying the Inventory on Hand report with a different filter Print Preview Figure 3 7 Print Preview Toolbar 4 5 8 9 Click the open door button on the tool bar The Output Choices window opens showing you a summary of your Output Choices Output
99. er 4 82 delete button 4 7 deleting a folder 4 83 edit button 4 7 exit button 4 5 7 6 filing requests 4 81 new button 4 6 preview button 4 6 renaming a folder 4 83 request list 4 5 run a request 4 6 run button 4 6 Request Types 3 2 4 9 cross tab defined 3 3 Detail defined 3 2 label defined 3 3 summary defined 3 2 Requests adding comments to 4 8 automatic saving 3 32 cancelling changes 4 11 copying 3 26 Creating a Detail Request 3 16 cross tab 3 41 Cross tab 4 76 data item selector 3 17 defined 3 1 editing 3 12 layout styles 4 9 handling too wide 3 32 labels 3 32 4 78 layout styles 4 9 limiting to Top N values 3 52 making a copy of 3 28 omitting duplicate records 4 17 output component 3 2 output information 3 4 Output Selector 3 24 parts of 3 14 parts of data items 3 14 parts of fields 3 14 parts of filter 3 14 parts of output 3 14 parts of sorting and grouping 3 14 previewing 3 27 3 31 4 11 query component 3 2 3 3 query information 3 3 relationship to report 3 15 request editor 4 9 Request Types 3 2 request types see page 3 63 4 9 run as is 3 32 running 3 11 4 11 save as 3 28 saving 3 26 4 11 saving a copy of 4 11 4 12 sorting and grouping 3 22 4 41 Specify Output Type 3 24 specifying report header 3 52 4 24 4 30 4 62 summary 3 38 3 67 4 73 Reset Page 4 50 Restor
100. erent kinds of data By accommodating different data types and relationships a RDBMS allows you to build a computer model of things or events You can then manipulate this model to better understand or control the real world things or events it describes The steps involved in working with a database to collect data and extract information are typically repeated in a cycle As new events occur their descriptions or measurements are captured in the database and the cycle is repeated The new events enable you to see the effects of the changes you have made Foxfire is an ad hoc query and report generator written using FoxPro s programming language It streamlines the query and report process and eliminates many of the repetitive steps It also remembers details about your specific database by storing them in its data dictionary That way it can provide the precise result you re after without you having to repeatedly specify details or learn complicated nearly programming procedures The basic customizing procedures are straightforward and can be mastered quickly The Sample Database The sample database is for an application for a car brokerage The car broker buys cars from several sources private individuals dealers auctions and so on The broker repairs and refurbishes the cars as necessary and then resells them for profit From time to time the supplier and customer may be the same person or agency In preparing cars for
101. erminology used throughout this manual What is Foxfire Foxfire is a query and report writing tool that enables you to extract information from your databases and format it into a variety of outputs including reports spreadsheets graphs labels and others Foxfire can be adapted to a particular database by storing information about the database in a data dictionary That way a Foxfire user does not have to specify technical details repeatedly or learn programming skills to create complex queries and reports A modest investment of time and effort in learning Foxfire s capabilities and features will be well rewarded with the power and flexibility Foxfire provides for obtaining information and answers from your database Which Kind of Foxfire User Are You This manual is intended for several types of Foxfire users Once you have identified which type you are follow the suggested work plan by reading the recommended chapters Practical User End User You may or may not have extensive knowledge of database principles and procedures but you use one to store track and analyze information Foxfire is designed for you first and foremost Work Plan Chapter 1 About This Manual 1 1 Power User Chapter 2 Chapter 3 Chapter 4 Chapter 5 Understanding the Sample Database Tutorial Learning Foxfire Basic and Intermediate exercises Using Foxfire At first just scan the cont
102. f the columns of data for certain makes are blank This is a typical look of Cross Tab reports The blanks are because there were no sales of cars for that make and year so there is no profit to be calculated 3 46 Request AUPROIT Furs gue Profit ut Makes tun 992 tu 1999 CmseTah Arrage of GroesP refit fre Mako by Yous Sbip4 Pape 1 Year Make 182 1985 1871 1595 1 6 1387 193 SCL RS F 24 00 2000 MC Eo ALLI SSW PSU U 5 Sa tS WUW ya WIL NM BLIK i0 100 25 78 20 172 10 1050 97D CALLAS z 2 75 6657 370 20 21183 5 303 00 5500 SLT T 177 aan f aT narn 7A fan 22 9 sarm CFR YSLES 4803 31 300 0 19500 Fh 02 00 3 m paTsuy E29 85 3333 ESC ne Tarm Tam 2M 3F 297 TAO Tamm ww FORD iaa il 29544 145 Fl 351 28 yaen 3 57 2 oe 70 54 50 6 11 403 00 Thee IOM lie env fi te HETI LEEI Leen BL HYLNCAL 22250 3 371 BF BLW oot 2 30 305 0 3 6000 aed tie STAG Lie 14 nt a LINTOLb 35 500 0 p 2667 aa i E IS 33 2375 19800 1 8235 0 1629 1500 3 DID MES DG AA wisi BPO MER IRY 7203 200 30 B 27300 3 810 55 F 16500 PIC 3 1 500 0C gt Other Cross Tab Options There are other options available on the Cross Tab Request Editor You can create column and or row summaries by clicking the appropriate checkbox The example given below shows you what the report would look like with both Row and Column summaries turned on Note that for this example an additional filter has been applied so that
103. f the following less frequently used options Compare the first data item to Another Data Item in the same record Example All records where the Sale Price data item is lower than the Cost data item View only those records lacking values for the data item you specified For data items of character type these are Blank numeric are Zero and date and datetime types are Empty Build Expression The FoxPro Expression Builder helps you construct more complex Xbase Expressions For more information on how Xbase expressions work consult the FoxPro user manuals or a reference book on FoxPro and Xbase concepts gt To specify the filter condition value Do one of the following v Type a constant value into the input field or y Choose one of the option buttons Empty Blank Zero Ask at Runtime Another Data Item Build Expression Specific instructions for each of these options follow 4 20 Table 4 2 Filter for Request SAMPLE Inventory On Hand x Input field where you can enter a constant value Deta teir Is Nuk Relaliui eship BCA ate fi Cars Make Arutier Dala Iern Amili Pypressicn Option buttons provide alternative means of specifying fitler values OK Cancel Show List of Values A command button Show list of Values is available in the Filter Builder when you select a data item for which this feature is enabled in Data Item Editor Choose
104. gt Changing the Selected Items and their Order You can change the order of the Selected data items in the output by highlighting the item and dragging it to the desired position for detailed instructions on changing an item s position see Movable List Items on page 1 11 In most cases you can return to the Data Items Selector whenever you wish to change the chosen data items and their order However if your Request has a customized layout when you attempt to change the chosen data items or their order Foxfire displays a warning that the layout customization may be inadvertently overwritten and you will be asked to specify how you wish to proceed See Tips for Customized Report Layouts on page 4 70 for more information about making changes to a Request once its layout has been customized gt Data Item Hide Option The Hide option enables you to include a data item in the results of a query but exclude it from the output This advanced feature can be very useful for example when you want to create calculated data items expressions that depend on these hidden data items but don t want the items themselves to appear in the final output A hidden item is indicated by a symbol appearing immediately to the left side of the name in the Selected list To remove the Hide option from a hidden data item highlight it and choose Show The symbol disappears indicating the Hide option has been turned off for that i
105. h feature is independent of the others and may be selected separately Formatting C Always Prompt for Header Ls Sup sou ge Selected check box Figure 1 9 Checkboxes Menus To choose or cancel a check box using the mouse click on the check box To choose or cancel a check box using the keyboard 1 Press TAB repeatedly until you reach the check box you want to select 2 Press SPACE m System Menu f Foxfire Report Writer File Edit Window Help Request Manager Requests Root Ele View Tools l t Bun chhR O pe x Qt A Preview Ctrl l New Edt Delete Exit Ta Tree ee Views er Contents of Requests Root Inventory on hand DETAIL gt Largest expense for each car DETAIL MULTJOIN Joins four different tables Rpt DETAIL Bi PROF _ANL Profit analysis by dealer Rpt DETAIL EE worpwrap Word wrap for long text nemo Rpt DETAIL Nen toden Rename koden Delete kolder Foxfire pulldown menu Ws Exit Figure 1 10 Menus in Foxfire The System Menu The System menu runs horizontally across the top of the screen see Figure 1 10 The Foxfire Pulldown Menu The Foxfire pulldown menu see Figure 1 10 provides e Options for frequently performed activities e Reference to keyboard shortcuts for common tasks e Options for advanced users and developers e Features for viewing the current Request Each menu option can be opened by clicking on th
106. hat Foxpro stores fields with a fixed width The data item Salesman Last Name is 25 characters wide Even if you only use 5 characters for the last name i e Smith has 5 characters Foxfire saves 30 characters on the output filling the unused characters with spaces Xbase provides useful tools to manipulate data items and to transform them These are called functions Function can turn dates into words lower case characters into upper case characters decimal numbers into integers and much more There is a function that trims off spaces from both the front and back of data items Here s how to use it in the last name first name custom data item we created above ALLTRIM Sales Lname Sales Fname The result will look like this Smith John 6 2 Expression Builder If you are a power user you can open the Expression Builder to assist you with creating an expression This dialog is available in the Data Dictionary by choosing Expression Builder from the Data Item Editor we showed in the last section Edit the Data Item Expression x TRIMSALES Flame IFINQ7 EVPTYCSA ES Middle SPACE I SALES Micce SPACE 1 SPACE 1 i TRIWSALES Llamef Zepieeson Dier Coa Corca You can also get to the Expression Builder from the Filter Builder by choosing Build Expression for the Filter Value When you make either of these choices the Expression Builder dialog is displayed Expression Builder x
107. hat we want since we are being prompted for a specific Car Make we will only get one make on the request This results in all the data on the report being displayed as if we never picked the Top N option If you picked Cars Model for each Car Make you would get the sales information for all of the cars sold last year for the specific make and the alphabetically first three models Again not what we want Instead choose Gross Profit for each Cars Model This yields the top 3 gross profits for each model of car sold last year for a specific make Note the reason this works is because the Request is sorted and grouped by Cars Make Cars Model and Gross Profit sorted in descending order biggest profit first 3 53 heme Lescrinion PRO 2e3 Prefl a avas uy Ceeder Lop vars Deta temz 0G 20 2000 Profit analysis by dealer top 3 cars Purchaced Stock from Ocalcr Make Modcl Pricc Revd Profit Mil 1300 Arstal creda PONTIAC NRCDIRD 1330 260 00 lt 74120 Filer Jui ts Soil Group Cary Yer le Aller 1984 are Je Purchased Is Eetween UUJ TII NYS Sars Sac Price a More Than 0 00 Care Wake Is _ke ias a runtime Guitars Request Tyge Detail Master etal Kannt wace Headings IN SEIEN Layout Style csertation Ran il Preview Cuunl Siw sal save ws Caneel 11 Choose OK to return to the Request Editor then choose Run 12 In the Enter Values dialog enter FORD and a hea
108. he three aliases Inner and Outer Joins When two tables are joined related during a query there is usually at least one record in the second table that matches each record in the first This situation is called an inner join When using an inner join you will only retrieve records that can be found in both tables But occasionally there is no matching record in the second file or vice versa and that situation is called an outer join Depending on the nature of the application and the data structures an outer join may be a legitimate situation or an error condition that requires attention and possibly correction of the data Here are two examples from the sample database to explain the difference between report which include both inner and outer join conditions 1 A car a record in the Cars table may have associated repair costs i e individual records in the Expenses table or it may have no repair costs i e no records in the Expenses table In an outer join report a car with no associated expenses would appear as a row in the resulting report and would have empty spaces in the expense columns In an inner join report that same car would not appear at all because it would not be selected during the query because only records with both Cars and Expenses records are included on the report 2 5 2 In the reverse situation an expense record with no associated car record would appear as a row with no corresponding ve
109. he first S item in the list and then use the arrow keys to highlight the correct item Drop down Lists A drop down list initially appears as a single line with an item already displayed and a down arrow at the far right J Click here to display the entire drop down list Style Character Web Page Figure 1 5 Drop down List Drop down lists are used when there is only space to show one of several options or where only one of several possible choices is permitted To display the complete list 1 Click on the down arrow 2 Highlight your choice and click Dual Lists In Foxfire the Available and Selected lists appear side by side so you can see which data items are available and which are selected Date Items far Request INVENTRY Inventory on hand 49 Cars Comment Cars Date Ot tale Cars Date Pald Cars Date Purchased Cars Days in Inventory Cars License Number Cars Munlh Suld Cars Purchased ron ID Cars Radio Cavs Sale Brine Cars Salezmains Id Cars Sold To Dealer Id Cars Nake Cars Node Cars Nileane E Cars Calne Cars Transmission Cars Purohase Priac 1 y 3 J B C B 5 4 1 4 5 Figure 1 6 Dual Lists When you select an item from one list Foxfire automatically moves it to the other Note You can change the order of data items in the Selected list by dragging them to new positions This is explained in Movable List Items below Movable List Item
110. he following types of group options y Calculations adds custom calculations to group footers v Formatting adds formatting to the group breaks Show All Sections displays both Calculations and Formatting options you ve established in the Selected list 4 46 Givup Optiuns fur Reyuest SAMPLE Inventory Ori Hamd Sacuntiznz bormating Seow al Leezhons Adel Saleaildi ns H F Harlqcted zuc Break l The type of options you rasa The Aa a specify Cars Mi lcage you selected are here fuy uf Cars Purehanr Pri added to the group T Cite rere break levels shown here The groups are listed from controls innermost to outermost just as they are on the reports summary area what options are available here Group Calculation Options When you choose the option type Calculations the Available section lists the numeric data items in the Request and the command buttons for summary calculations become active Group Options for Request INY_ORG Inven Option Types i Choose Calculations to a view the options for summary calculations Show Data Items In Request F Gii Choose the 2 a source of the Cars Mileage Available numeric r data items Cars Purchase Price Highlight the source of Available numeric data items Choose the type of calculation Figure 4 20 Group Summary Calculations 4 47 Show Data Items The Available list
111. he report layout you must choose Auto Format to apply the new style This causes Foxfire to create a new layout which overwrites you customizations You then must reapply them For more information see Editing Requests that Generate Customized Reports on page 4 71 gt Layout Options Foxfire has additional options for designing reports Choose Options to open the Layout Options dialog 4 61 Layout Options Ea Show fitter conditions on report ormatting None Always Prompt for Header All I Suppress detail lines C Ask at Runtime conditions only I Fit to page Detail Line Spacing uppress repeat values Use em Line s per Detail Line in first 0 lt 4 Columns Column Spacing 1 4 Spaces between Columns Cancel Figure 4 24 Layout Options dialog Show filter conditions on report To help you see at a glance how the report was filtered you can print the filter conditions for each run in the report header These are the choices y None Does not print filter conditions in the report header y All The full filter condition prints in the header of the report every time the Request is executed y Ask at Runtime conditions only This report may have a combination of filters Some of these filters are built in to the Request while others are based on the Ask At Runtime option If you enable this feature reports display just the filter condition that you fill in at the
112. hen the cursor icon changes to a horizontal form you will know that the data item will now be displayed as columns instead of rows Microsoft Excel xiplvot xls J Eile S t giw not Fona Tole Dew irda Help k M ira GAS img e u REl hm OE MWA re Purch Dt lt DIT SSSR ON BSE L a A now apears eee as a row x 7 j os te instead of as a EELA Pi a Bib Piil a column 2 Choose the Purch Dt button the selected button appears recessed 3 While the focus is on a date cell choose the Group button z on the Pivot Table toolbar to open the Grouping dialog If the Pivot Table toolbar is not displayed use the Toolbars option on the View menu to activate it 4 For date data types the Grouping dialog defaults to Months Grouping 2 x Auto IV Starting at JANEE lV Ending at 9 6 1999 M Cancel Quarters x Number of days fi 4 5 Choose OK 6 While the focus is again on a date cell choose the Group button again While leaving Months highlighted choose Quarters so both 3 70 are highlighted The pivot table will now summarize the data by month and quarter as displayed below Microsoft Excel x lpivot xis S Eile Coit view acer Fome Iocs Data Yirdow Lelp IM SOS S Atm T AEA Measles art m peu lS S Salle we WMS Ow Data is now recalculated and displayed by months and quarters Restoring Your Preference Set Setting After you have co
113. her your label paper is measured in inches or millimeters The list of label types includes the standard Avery label forms If you choose 1 label across and are measuring in inches the list also includes rolodex and envelope forms You can choose to sort the list of available label formats by Name or by Height and Width for easier selection Indentation You can specify how far from the label margin to indent the text by indicating the number of character to indent in the Indent field Table and Data Item Selection The Available list is comprised of two sub lists The first is a drop down list of available tables From this list you choose the source for the data items you wish to use The source table determines which data items appear in the second list the list of available data items When you choose a data item you are choosing not only what you want on the label but also where you want the item on the label You control 4 80 this placement or positioning by first highlighting the desired line in the Selected list This becomes the active line Then chose the data items for that line in the order in which you want them appear As an item is selected it appears on the active line of the Selected list If an item is already on the active line the item you choose is placed after that item Use the sample label as a reference for double checking your placement of data items Punctuation and Text Use the punctuation
114. here is not an exact match to an index expression FoxPro instead searches sequentially for the records that meet the filter conditions resulting in a far slower search Rushmore can be cancelled under a variety of circumstances that are rarely caught by a casual user Two examples are v Ifyou build an index on a street name as it typically appears in an address first letter upper case remaining letters lower case and you check Upper Lower in FoxPro s RQBE screen the index format will not match the SQL SELECT command created by RQBE and Rushmore will not use the index for that item v Ifyou specifically a partial key value as a search criterion when the actual index expression is a concatenated field or compound index indexing is not used with for that criterion Here s an example from Foxfire s sample database An index tag called Make Model1 exists for the Cars file Although from a logical standpoint the two expressions are identical when FoxPro evaluates them as filter criteria to determine whether it can employ optimized retrieval vY The expression Make FORD is not optimized Vv The expression Make Model FORD is optimized 7 3 Insuring Rushmore Retrieval The need to properly format each filter criterion is therefore obvious Foxfire can be customized to automatically insure that its SQL SELECT command will always include an optimizable expression To see one example of how t
115. hicle information in an outer join report but would be absent altogether in an inner join report Using an outer join in this case would produce a list of orphan records an orphan record is a record with no matching in the related table By default Foxfire automatically includes inner joined records in it s query results The joins or relationships between all of the tables in your database is setup in the Foxfire Join Editor Foxfire does provide a way for you to override the type of join which is done on a request by request basis Keep In Mind Foxfire has been designed for use by the most novice user However you will get the most from Foxfire if you v understand basic database concepts v are familiar with the information in your database v understand how that information in interrelated y and know what information you want out of your database With that in mind work through the tutorial to further familiarize yourself with Foxfire and refer back to this chapter when necessary 2 6 Chapter 3 Tutorial Learning Foxfire This chapter contains exercises designed to familiarize you with the operation of Foxfire Before you start to work with your own data step through these exercises to learn about Foxfire s key features The exercises and examples in the tutorial use the sample data set described in Chapter 2 Understanding the Sample Database The tutorial is divided into three level
116. highlight the grouped data item you want to ungroup in the Selected list The grouped indicator appears to the right of the data item s name 4 Choose Ungroup Located below the Selected list The grouped indicator no longer appears to the right of the data item s name 4 45 gt Creating a Derivative Request by Varying the Sort Order Sometimes it s useful to use the current Request to create one or more derivative Requests based on the current one but with a different sort order This helps you view the data in the Request in a number of ways To do this in the Request Editor choose Save As and assign a new name and description to the Request Then choose Sort Group and vary your choices as described above Working with Group Options Foxfire automatically generates summary calculations when a group break occurs You can also specify other calculation options average data count items in a large group or perform statistical analysis as well as formatting for example a new page with each group change or printing a group header on each subsequent page if the group continues for more than one page Summary calculations can be placed v in a group footer group summary v at the conclusion of the report summarizing all detail lines in the report v to a single specified group break v to all breaks including the grand totals Option Types In the Option Types section you can choose form t
117. his can be accomplished follow these steps 1 Using the sample database open the Data Item Editor 2 Choose List and choose the data item Cars Make from the list 3 Choose Filter to open the Filter Options dialog 4 Examine the Filter Value Processing section Two fields in this section control the processes that insure that Rushmore won t be accidentally cancelled by an improper entry The in the Input Picture field causes any characters typed into the Value field of the Filter Editor to be converted to upper case insuring a match on character format The VALTABLE CARMAKE T in the Validation field causes any string of characters typed into the Value field of the Filter Builder to be matched against a validation second table a second table for the purpose of storing the valid values for a particular data item insuring a match on content of the value A About Foxfire 2 4 Foxfire Navigation Map 3 5 About Foxfire 4 3 Adding a calculation to a group break 4 49 Adding custom calculations 5 1 Adding lines and boxes 5 1 Adjusting the appearance of data items and text 5 1 Adjusting vertical spacing 5 1 Aggregate data item 3 66 Always Prompt for Header 3 64 4 62 ASCII 3 65 delimited 4 57 HTML output 4 60 output type 3 65 4 11 previewing character reports 4 66 SDF defined 4 57 Ask at Runtime 4 22 4 62 ask at runtime options 4 22 default value specified 4 22 dial
118. hoose a different layout style gt Layout Options gt Layout Customization gt Output Destinations gt Special Processing Options Preview Tips for Customized Report Layouts Editing Requests that Generate Customized Reports gt To modify a Request which has minor custom layout changes gt To modify a Request which has extensive custom layout changes Creating A Summary Request gt Specifying Summary Data Items gt Group Filters HAVING Clause Cross tab Requests Creating a Cross Tab Request gt Creating a cross tab request Label Request gt To create a label Request Filing Requests in the Request Manager gt To create anew Request Manager Folder gt Renaming a Request Manager folder gt Deleting a Request Manager folder Exporting a Request and Data Items 4 35 4 36 4 37 4 38 4 41 4 43 4 43 4 44 4 44 4 44 4 45 4 45 4 46 4 46 4 49 4 50 4 50 4 51 4 51 4 51 4 52 4 55 4 58 4 60 4 61 4 61 4 63 4 64 4 66 4 66 4 70 4 71 4 71 4 72 4 72 4 73 4 74 _ 4 76 4 76 4 78 4 79 4 81 4 82 4 83 4 83 4 83 vi gt To export a Request Data Items and Custom Report Layout Importing a Request and Data Items gt Importing a Request Data Items and Custom Report Layout The Foxfire Menus Choosing a Preference Set System Administration Utilities The Batch Builder gt To create a batch gt To edit a batch g
119. how the Request now appears in this folder gt To create a new Request Manager Folder 1 Click on the folder pane of the Request Manager 2 Right Click or Choose Create Folder from the File menu 3 The New Folder dialog appears Name I Create as subfolcer of Examples of Special Features Sales Reports Inventory Requests 4 Enter the Name of the new folder and choose the folder under which you want to create the new folder For example If you wanted to create a subfolder of Sales Reports to hold Monthly Sales Reports you would highlight and select Sales Reports 4 82 5 Choose OK gt Renaming a Request Manager folder You can change the name of a Request Manager folder by selecting in the Request Manager folder tree and then choosing Rename Folder from the File menu You can also right click on the folder name and choose Rename Folder When prompted for the new folder name enter it and select OK gt Deleting a Request Manager folder You can delete a Request Manager folder and all the requests and sub folders in it by selecting in the Request Manager folder tree and then choosing Delete Folder from the File menu You can also right click on the folder name and choose Delete Folder Exporting a Request and Data Items Foxfire has the capability to export Request definitions including associated data dictionary items and custom layouts see page 4 71 The export file created can then
120. ibed below Filter Conditions May Be Added and Deleted If you chose Edit Entire Filter checking this box also allows adding and deleting of whole filter conditions at runtime All Filter Builder command buttons will be active for the user amp Edit Entire Fitter User May Edit At Runtime User May Edit Data Value Entry Required Data Value Ignore if Blank Data Value Ignore Prompt if Empty O K Any part of this condition Choose User May Edit to display a list of what may be edited at runtime For each condition this list controls what may be edited at runtime Data Value Entry Required requires that the user enter a value at runtime Data Value Ignore if Blank allows the user to optionally leave the value blank at runtime If the value is left blank the condition will essentially be eliminated from the filter at runtime and all values will be included in the retrieved record set 4 27 Data Value Ignore Prompt if Empty O K allows the user to optionally leave the value blank at runtime If the value is left blank the user will be prompted as to whether they want to include all records i e act as if the Ignore If Blank option was chosen or only records with a blank value in the specified field For example i Foztec Hoport Wutcr Tare Crier was not ertered tetieve In y those recores whare tas nen is LM ly 3r do yowan SL serwis eyar dess Uf lhe valle of bis
121. igating with a Mouse Moving Around the Screen When the mouse is moved the mouse pointer which appears on screen moves in the corresponding direction and distance The mouse pointer can select which window area or screen control is currently in focus in other words it highlights that particular item In this guide the term highlight means positioning the mouse pointer on that item Making Choices You indicate a choice by highlighting your selection with the mouse and taking any of these actions v Click To Click highlight your selection Then press and release the primary mouse button quickly one time on most mice the left button is the primary mouse button Y Double Click Essentially the same steps as click except that you press and release the primary mouse button twice in rapid succession yY Drag To drag the mouse press down the primary mouse button and hold it down as you move the mouse in the appropriate direction When you reach the destination release the mouse button gt Navigating with a Keyboard Working without a mouse has become rare because mice are now standard equipment with new computers In many cases however keeping both hands on a keyboard can speed your progress For this reason we recommend you study this section to learn about some options for quicker data entry Moving Around the Screen Pressing the TAB key moves the cursor to the next item or screen cont
122. ime you want an explanation of a feature highlight it and press F1 4 2 Request Editor Eq Name Cesctigtion S4VPLE nvertory Cn rand Context Inventory On Iland sensitive help Stochi mar Aa Help for Output Esc to Exit Df x for Output 4500 PoN h22 e Qutpitto specitythe orm ype anc dest nation of he Requectrescits button uput typas include browse raport labels grapas sfraadsheet malmerga ASCII data Daparding on tra type of output rasults can be dractadte screen ainte or fie Cars pateri i Cars Makeis 0U Can a so do printa setua from 1318 Qipi i Recaest Type Detail Salamnar Revot To Scresn aycu Style Presentation Run Proview Count show sa_ Save Save as Tose Figure 4 2 Context Sensitive Help gt Help Button A Help button is available in the bottom left hand corner of some windows Choose this button for an explanation of features in that window Choose this to view ema Help Figure 4 3 System Help gt About Foxfire Report Writer About Foxfire is a set of windows which explain the purpose of Foxfire and contain contact information It also contains an explanation of the sample data set To access About Foxfire from the Request Manager pull down the Foxfire menu and choose About Foxfire 4 3 About Foxfire Query Tool and Report Writer Ea We come to Foire F Report Ywiter ihe award winning qacry and r
123. ing 4 50 Foxfire installed on a network 4 6 organization of 3 5 starting 3 8 using Rushmore 7 2 FoxPro 1 2 2 1 3 1 3 20 3 65 4 17 4 19 4 20 4 22 4 23 4 24 4 41 4 60 4 63 4 67 4 68 4 70 4 89 4 90 5 1 6 1 6 7 7 3 FRX 4 63 4 64 4 70 5 5 G Getting Help 4 2 Graph 4 56 formatting 4 50 Group Break 3 22 4 42 4 44 4 50 adding calculations 4 49 adding formatting 4 51 calculation options 4 47 calculations 4 46 cancelling 4 45 creating 4 45 formatting 4 46 removing calculations 4 50 removing formatting 4 51 Group calculations Average 3 40 Avg 3 40 Count of records 3 40 Max 3 40 Min 3 40 Sum 3 40 Group Calculations options 4 47 7 3 Group footer 4 50 Group Formatting Options 4 50 group header 4 46 header 4 50 Group Break 4 46 group identity 3 38 4 73 Group Options 4 46 Group totals 3 22 H HAVING clause 4 74 Help 4 2 About Foxfire 4 3 button 4 3 Context Sensitive 4 2 Help button 4 3 On line Help Messages 4 2 hide data items 4 14 HTML 3 60 I IF THEN ELSE support 6 7 IIF function 6 7 Importing data items 4 84 request layout definition 4 84 Importing request definition 4 83 4 84 In the List 4 36 4 39 indexed search 7 1 indexes 7 1 Indexing 7 1 explained 7 1 indexed search 7 1 Indexing and Rushmore 7 1 Inner and Outer Joins 2 5 example 2 5 inne
124. ing Your Preference Set Setting 3 71 rolodex 4 80 Row Summaries 4 78 Ruler 5 7 run as is 3 32 Run Button 3 26 Running a Request 4 6 runtime help 4 28 runtime prompt 4 28 Rushmore 3 20 4 41 7 1 7 2 accidently cancelling 7 3 example 7 3 explained 7 2 full optimization 7 2 identifying data items 4 17 identifying items in filter builder 7 2 insuring retrieval 7 4 partial optimization 7 2 S SAMPLE 3 17 3 28 3 29 4 8 4 35 4 49 SAMPLE2 3 48 4 35 4 36 SAMPLES 4 36 4 37 Save As 3 28 3 49 3 52 4 11 4 12 4 13 4 35 4 36 4 46 4 64 4 68 4 92 5 2 lock option 4 13 output file name option 4 13 Save As Button 3 26 Save Button 3 26 Scroll Bars defined 1 8 Show 4 14 Show filter 3 64 show hidden data items 4 14 Show list of Values 4 21 Showing filter conditions on report 4 62 Sort Group Selector 3 7 3 23 4 10 4 42 4 49 Sort order 3 3 Sort Group Criteria available list 3 23 example 3 22 group totals 3 22 grouping 3 23 grp indicator 3 23 sort by list 3 23 specifying 3 22 un grouping 3 23 Sort Group Selector 4 32 Sorting and Grouping 4 41 ascending descending order 4 44 available data items 4 43 cancelling a group break 4 45 changing sort order 4 44 choosing data items for sorting 4 43 displaying all data items 4 43 displaying only items in request 4 43 example 4 42 explained 4 41 group 4 42 group br
125. ire Filter determine the extent of filter editing that will be permitted at runtime Depending upon which type of runtime editing is chosen a runtime a simple dialog will be provided for entering data values only or a more complex one will be provided to edit the entire filter i Ask ot Nuntime Options ing i gt Enter Val Choosing Enter Filter a ntar Values Vaen inl Cans oor See Peques SAMPLE2 Inv2ntory on hare the Enter Values ars Cate Dr Sale le Cin of Ata Bet 3S Regt dialog to open at 3K Cencel Choosing Edit Entire Filter causes Bala tem Relationetip Valuets Cars Date Of Sale is Empty Cars Make is Like TOYOTA Car Dale Of Sale Ou ur f fler CEnler Filler Valued cross Profit Mare or to 1Da 00 Cars Vear Ic Ret ween 1992 1999 the Edit Runtime Filter window to open at runtime id Edt kad OF it J Daie Toph be reser corci Figure 4 16 Effects of Ask at Runtime Options Enter Filter Values Only Enter Filter Values Only is the default if Ask at Runtime is specified as a value in any filter condition With this choice an Enter Values dialog will be displayed at runtime The user may then enter comparison values Edit Entire Filter 4 26 If Edit Entire Filter is specified the Edit Runtime Filter window opens at runtime The user can edit several aspects of the filter for that run which are determined by the controls descr
126. itor Press Ctrl Q to close the Layout Editor without saving your changes 5 4 Making Changes after Customization Customized reports must be kept in sync manually if you change certain aspects of a Request later When appropriate Foxfire will notify you and offer to recreate Auto Format the layout If you proceed your customizations will be overwritten and will have to be reapplied If you don t regenerate you must update the layout manually to match the Request Specifically changes to the following aspects of a Request cause any previous layout customization to be lost Vv The list of Selected data items in a detail or summary Request or data items included in a cross tab Request Y Sorting or grouping specifications v Group break calculations or layout options v Calculations in a summary Request Loss of customization occurs because Foxfire must recreate the entire layout to accommodate any of the above changes and overwrites the old customized layout with the new one This loss of previous customization cannot be prevented so the best strategy for using Foxfire is to wait until you are satisfied with the other aspects of the report before customizing the layout When making any of the above mentioned changes you will be warned before you begin your editing Foxtue Heport Writer Tais Requestuses a custom zec Report WasternDela Soup Ileadings aycu Custam ed layouts tennothe Jpdated lt 0 retlestchenges toha Rege
127. ized This user guide is organized into these main topics v Sample Data Base v Tutorial v User Reference vY Appendices Conventions Used in the Manual The following sections explain the user interface and typographic conventions used or referred to in this manual Instruction to Press a Key Whenever you re asked to press a key the instructions look like this Press Enter to continue Section Names In references to a section elsewhere in the manual the section name appears within quotation marks along with a page reference For example Screen Controls What They Are and How to Use Them on page 8 Important Notes Notes and Warnings appear throughout the text to alert you especially important information or instructions For example Warning Deleting a Request means permanently discarding the Request Once deleted it cannot be recalled Before deleting a Request make sure no one will need the Request any longer Xbase Commands Xbase commands that you type appear in Courier New font CARS STOCKNO EXPENSES STOCKNO Files and Tables Throughout this book and occasionally in on screen text you will see references to the terms files tables database files or DBF files When referred to as part of an application database these terms mean essentially the same thing and are interchangeable Moving Around Foxfire This section explains how to navigate in Foxfire using a mouse or a keyboard gt Nav
128. l Filters section and pick Cars Date of Sale Then specify that the rest of the filter conditions should be Is On or After an Ask At Runtime value Choose OK Next we want to only show dealers who we ve made a significant profit on Choose Add Choose Sum of Gross Profit Then specify that the rest of the filter conditions should be Is More or Equal To 1 000 00 Choose OK The Filter Builder should now look like this Filter fnr Request DERSAIFS Cars Sale tr Mesabers or Alter ask at runtiier Graup Falters a za aa s eae Dems _Lrien ToN I art Cupizatss AY at Are ONTS Metz Cictionsry Cana Choose OK to return to the Request Editor Choose Preview 4 75 17 When prompted to Enter a Sale Date enter 01 01 2000 18 Also note that when the Request runs you get far fewer records than we did before you specified any Filter Conditions Mpt OLPSALES GareSold to Beslan Mager Akra Combar WSaclaon a Aier i OR ham Sumat Mga Sumet Ingea linyn Coum Price Revd Prier Mwd fxm Prom corage Invenioay 4 ma lotn I Oud 1 4D fil AX f 26E ASDO DN ino aye tye ll n sate nie 4 ain 2 700 t 39x 40 in ino Tie LES aeDA SELLES 1 mt c 2 000 t 39x 2000 20 426 ino tine tin ew Sic min WOU Ws 30x t 30x Z200 310 4 ipo WASH SEM ALKI a ENA PAY FaN FPA 1 imine SANT CARD WILLS 2 C3 caa of me EDO i ino AHHA FCSN AFRO 2 ETN Pr swn ENM mw ROIA 2G ALO i 14 Leis ELI ky 1 13u GHAMU
129. la Items for Nequest DCALLAD Label for Dealers We Purchased Crummy Cars rom EG Lakel Type Aveilabe avery 56603 Across 1 x 2 5 6 Choose aw Data Items Largest Single Expense Purchased From Address 2 Purchased Frum Bank Numer Arata Honda 198 Califorria Avenuc Burlngeme CA 94010 Purchased From Contact Purchased From Dealer Id Purchased From Dealer Nunber From Phane H Rep Expenses Type Of Expense Sales Jlarlul Seerterd Remove a Text Purchased From Name 2 2 sss ee we ew ew ee ll lt le oe Purchased From Address 1 1 sasssa sss sss we ee le lll ll Purchased From city Purchased From State Purchased From Zip Help Detiorary Cancel 7 Choose OK 8 The Request Editor opens 9 Choose Preview Notice that the labels now look much better because they display the city state and ZIP on a single line with the comma 3 36 ARAL HYNA HJO WSI AC LRA MUL YYEST HONDU 198 Cdiiriin Acer 27158 Frei Fal SAG Fern ine Aw Buangsne CA YIUIL rancnt CA Y4 dob Famam C 24535 ATI VE ST TIVI TA 4LW ADEN MADA ALMAZE s HOMJA 38555 Front Ela 220 WY Cepitel Expressvay TEA Std Exprocovey Freniwid CA 94556 Ban Wuse CA 95138 Sun Juss CAES S3 10 Now add filter conditions to only select unsold cars Cars Date of Sale is Empty which were purchased before a date which is to be specified at runtime Cars Date of Purchase is
130. le Inventory Report being created in Foxfire It is important to have a clear understanding of the Request requirements prior to actually implementing the Request This will help minimize the amount of rework required due to miscommunication Business Specification As acar broker we must be able to know how many cars are currently in inventory i e unsold at a particular time We frequently receive requests for a specific color of car so we also need to be able to only see cars of a certain color We don t know what color the consumer will be asking for if any so we want to be asked when the Request is run We need to be able to see the following information about each car in inventory stock number make model mileage color transmission type how much we paid for the car and the dealer number we bought it from We need to be able to see all of the cars for the same make and model sorted together on the report The request and report are displayed in Figure 3 5 Sample Request and Figure 3 6 The Resulting Report so you can see the relationship 3 10 gt To run the Request for the sample report 1 Be sure the Request Manager is displayed 2 Highlight the INV_ORG report from the Request Manager 3 Choose Run from the toolbar or right click on the Request and pick Run from the pop up menu 4 Foxfire generates status messages at the bottom of the screen to inform you of Foxfire s progress as it assembles the report y
131. lexibility in the variety of Request outputs and your ability to process and customize them These options are specified in the Output Selector and it s related windows and dialogs 4 51 Uutput tor Request SAMPLET Inventors Un Hand Outpur Tye Rcpor Master Detail Page Feadingo SS Layout Js Auto Foma tem Teinplate Sye Oustomization Defaul Pese deiu gt Ceit Zhoose Optiors Dave as Anta horarat Send Tr Cc irme R i r Sp Serecn cre ms Special Pruveszii y Cpliw is Help 3K Cace Figure 4 22 Output Selector Report Output Types Foxfire offers four 4 standard output format for Output Types which are Reports These different Report Output Types can also be used to modify the layout of the report so that more data can be displayed without having to use the Fit to Page option discussed above This section describes each of these different output formats and provides examples using the Sample report you created in Chapter 2 Y Column Output Format In this output type each page of the report shows the current page number the column headings associated with each selected data item For each group identified on the request the group heading and value is displayed followed by each record in that group The selected data items are displayed in columns under each data item header At the end of the group the group footer is displayed including any c
132. link the data Se S S S Available Report Layout Styles You can change the current Preference Set by choosing Preference Sets from the Tools menu The Preference Set Picker dialog opens Preference Set Picker xi Preference Set User Department Demo Use of Views ENDUSER Micromega Micromega Startup fe Edit et Sternup Cancel Figure 4 37 Preference Set Picker fe o Ae Use the features in this dialog box to do the following v Change the current Preference Set You can change the Preference Set that appears in the Request Manager highlighting the desired Preference Set in the list and choosing OK 4 87 Depending on how Foxfire is set up on you system some users might not be granted access to change Preference Sets v Set the default Preference Set You can designate one Preference Set as the default to be activated when Foxfire is started Do this by highlighting the Preference Set and choosing Use at Startup v Edit a Preference Set Choose Edit to open the Preference Set Editor Note Editing or creating a new Preference Set is an advanced customization task Preference Sets control the features of Foxfire You can create multiple Preference Sets to display different data sets or to change what users can access System Administration Utilities This option offers a set of operations to define and configure your Foxfire environment These utilities are restricted to only users with System
133. lsfor Make Aug 101505 10 000 Make BMW Stock Model Mileage Color Price Paid Trans Purc 2251 320I 91623 2 000 4 Speed RMS Totalsfor Make BMW Aug 91623 2 000 Master Detail Page Headings Format In this output type each page contains the page number and selected data item column headings at the top of the page For each group that appears on the report the records for that group immediately follow the group header Each record contains data for all of the selected data items except for the group by items since this data was just displayed in the group header At the end of the group the group footer information is displayed This request output type is good to use when you have more data items selected then you can display on a printed report since the group by items are pulled out of the widest part of the request It is less cluttered than the Master Detail Group Headings output type because the column headings only appear at the top of the page rather than at the top of each group 3 57 Request SAMPLE1 Inventory On Hand Column Headings appear at the top of Page 1 the page only Stock Model Mileage Color Price Paid Trans Pure Make AUDI 2448 5000 110291 SILVER 1 500 3 Speed MLM 2018 5000s 92835 BLUE 1 500 5 Speed SUCP 2387 5000S 129127 GREY 2 000 5 Speed MLM 2389 5000S 7367 500 Atomatic ABI Totalsfor Make AUDI Aug 101505 10 000 Make BMA 2251 3201 91623 2 000 4 Spee
134. lter conditions You may add a filter condition to a Request because you want to either create a filter condition for the first time or further narrow the filter on a Request Example The filter on a certain Request currently specifies Cars Color Is Like Red Now you want to narrow the condition so that the report displays only red cars with a sale price more than 7 000 You already have the condition for red cars specified Now you must add a second filter condition Cars Sale Price Is More Than 7000 v Or permits you to broaden filter conditions by splitting two conditions into an either or pair Either red cars or cars sold during 1988 See The OR Connector on page 4 37 v and let you control the order in which conditions are evaluated when Or is used to combine conditions See Using Parenthesis to Change Order of Evaluation on page 4 38 v Delete removes the selected filter condition Warning Once you exit the Filter Builder deleted conditions cannot be retrieved If you previously saved this Request you may be able to abandon the changes including the deleted filter condition by choosing Cancel y Ask at Runtime Options displays the Ask at Runtime Options window for setting additional filter values at runtime See Ask at Runtime Options on page 4 24 for more information Omitting Duplicate Records A duplicate record is one where every data item in the record exac
135. lude in the sort instructions Note If you want to reorder the data items to change the sort order see the procedure To change the sort order precedence below gt To remove data items form sorting Choose the Selected data item to remove it from the Selected list The data item returns to the Available list and no longer appears on the Selected list 4 43 gt To change the sort order precedence 1 In the Selected list highlight the data item you want to move 2 Drag the data item up or down into position The sort data item moves to the new position in the Selected list Note Dragging sort criteria around can completely change the nature of output if you specify Group Breaks See Group Breaks below If you are unsure how to coordinate sorts and breaks experiment with a copy of the report first before modifying the original gt To specify ascending descending order Each item in the sort criteria can be sorted in ascending or descending order A single headed arrow appears tot he left of each Selected data item name v An upward pointing arrow indicates that the data item will be sorted in ascending order A to Z or 0 9 v A downward pointing arrow indicates that the data item will be sorted in descending order Z to A or 9 0 Ascending is the default sort order for Selected data items You can switch the sort order button back and forth from ascending to descending for each item
136. me Desrriptinn Bavpve2 Audi s cr BMA s In Inventcry Data items 07032000 Audi s or BMWs In Inventory Stock Make Model _ Mileage Color Price Paid Trane Pure 1500 PONTIAC FAREBRD 147123 WHITE 1100 4 Speed CCP Fiter Joins Sort Group Cars Make le Like Bity t Cars Make Gp t Cars Model Care Make Is Like AUDI Ourput Request Type Detal Master Detai Regort w Group Headngs To Screen Layout Style Presentation Rur Preview Count Stow SQL Save Save as 3 Choose Run and look over the Request in the Page Preview 3 48 Note the two Audis whose Stock s are 2387 and 2389 They appear in the report at the bottom of the Audi group 4 Choose Close to return to the Request Editor 5 Choose Data Items 6 Remove the data items Cars Transmission Cars Purchase Price and Cars Mileage from the Selected list selecting them moves them to the Available list 7 Add the data items Expenses Date of Expense and Expenses Repair Order Amount Note Expenses are kept in a separate table from Cars Foxfire knows that the information in the Stockno field links the tables It will join them automatically in all Requests where Cars and Expenses data is selected Refer to the E R diagram in Chapter 2 for a graphical representation of the table relationships in the sample database 8 Choose Close or OK to return to the Request Editor 9 The request should now look like the following Nar
137. me Filter window displays any special instructions and enables the Help button if entries were made in the Runtime Prompt and Runtime Help options of the Ask at Runtime Options dialog Adding a Heading The runtime user can choose Heading to enter a heading which will appear beneath the Request name and description in the report header This is useful for distinguishing a particular run of the Request since the values can be different for each run 4 30 Ask at Runtime filter conditions can automatically be added to the report header through a setting in the Layout Options dialog of the Output Selector See Show filter conditions on report in the section on Layout Options on page 4 61 Special Filter Options In addition to the items we ve already covered Foxfire handles two additional complex filtering processes Top N Values and Unions These options appear on the Filter Builder window as buttons Not available for cross tabs Data tem ls Not Relationship Vahe s Eat Or j Delete I Omit Duplicates Ask at Runtime Options Jelp Dicticnary OK Cancel Top N Use Top N to specify a limit to the number of records reported for a specified data item the Request is sorted by This feature is typically used when records are sorted to rank the results for example to produce the Top 5 Top 10 etc 4 31 Top N Yalues x Number of Data tem Value
138. mline the process of running them for example if you run the same group of Requests periodically daily weekly monthly etc To create edit or run a batch of Requests choose Batch Builder from the Tools menu Batch Mame Juntiiea Available Requests Name Description Outrut Type Requests in Jatch Cross Tab amp DLRSALES Cars Sold To Dealers Rpt Summary INVENTRY Inventory on hand Rpt Detail JALOPIES Example uf filler unmly dale ile Rel Delail LRCEXPEN Largest eomponse for each car Rpt Detail MAIL_LBL Mailing Labels for Dealers Lab Labels MULTJOIN Joins four different tables Rpt Detail MYLABELS Labels for Dealers We Purchased F Lab Labels PROF_ANL Profit analysis by dealer Rpt Detail QUARTILE Querlile rank lt prufil gt Rel Delail RUNTIME Newest foatures of Ask at Runtime Rpt Detail SAMPLE1 Inventory On Hand Rpt Detail SAMPLE2 AUDI s and BMW s Rpt Detail SAMPLES AUDI s and BMW s Rpt Detail SAMPLEX Ked Cars On Hand kpt Detail z 4 Konm Batch Open Batch Figure 4 39 Batch Builder Ect thse Note Be careful to choose Requests that are suitable for running automatically You should avoid Requests with Ask at Runtime filters for example which would cause the batch printing process to halt while waiting for the Ask at Runtime input 4 91 Other settings to watch out for are Requests whose default output destination is Screen and Requests with the Prompt for Print Options at Runtime optio
139. mouse or pressing CTRL W v Close closes the Output Choices dialog to return to the Request Manager The ability to run the Preview repeatedly without re running the query is especially helpful when fine tuning a customized layout You can make changes to the layout and see the results immediately without a need to wait for the query to run each time These buttons appear when you preview a character report 4 67 y Zoom Presents a Zoom Request Output dialog box Choose a point size 7 9 14 or 16 pt to scale the report up or down v Print Displays the Print Settings dialog v Edit Allows you to view or edit the report The display window becomes active allowing you to scroll around the Report to view or edit it The standard FoxPro window controls are available for mouse users or you can use the cursor control keys to help you move around the Report If you make changes to the report press CTRL W to save them or press ESC to abandon them This is plain ASCII text Changing the result does not alter the Request that created the results If the Request is read only the button text says Scroll v Copy to Disk Presents a Save As dialog box Specify a file name to hold the ASCII result file v Edit Layout Opens a character version of FoxPro report layout editor not available on Macintosh Note Loading a full copy of the FoxPro character oriented report writer takes a considerable period of time
140. move all requests O EELA T T eee Firer Requests in Frein the import file from Import list List of requests in the current preference set Remove one request from the Import list 3 Choose a file to import by clicking the button By default the file picker will look for the import files in the Foxfire OUT directory 4 Select one or more requests from the import file to import Use the Current Requests in Foxfire tab to see which requests you already have in your currently selected preference set Note if you import a Request that already exists in your preference set you will be prompted before the Request is overwritten 5 Choose Import to begin the import process 6 After the import process has completed you will be shown a summary of what was actually completed The Foxfire Menus When the Request Manager is visible the options shown in the following illustration may be available from the File menu Note items marked with an asterisk can also be accessed from the Request Manager Toolbar 4 85 Run a request See page 1 5 lt lt View Tools Preview a request See page 1 6 Run Ctri R me Ctrl Create a New request See page 1 6 New Ctrl N he Edit Ctrl E Edit a Request See page 1 7 Delete a request See page 1 8 Delete Import Import a previous exported Request See Export Export a Request and page 1 120 re optionally it s data items NEw
141. mpleted this final exercise return to the Request Manager and check the Preference Set setting to be sure it s the same as it was when you started Repeat the steps on Selecting the Sample Preference Set on page 3 8 to verify that the settings are the same as those you wrote down at the beginning of this chapter If they have changed reset them to their original condition You should now be able to use Foxfire with your own application 3 71 3 1 Chapter 4 Using Foxfire This chapter provides detailed descriptions of the features and functions of Foxfire and gives step by step instructions for more complicated procedures The chapter is organized to follow a logical progression or User s approach through Foxfire To quickly locate a specific topic or feature consult the index Getting Help In Foxfire help is available several ways gt On line Help Messages On line help messages display a single line of help text at the bottom of the screen to provide information about the active screen control The dotted line around the text on the button On line help indicates displays the this is the Run Preview Count Show SQL correspondi active ng single control Request s 20 ser Sv TEM line help message Choose to specify the output type and options for this Request y Figure 4 1 gt Context Sensitive Help Context sensitive help is available throughout the program Any t
142. n Limiting a Request to the Top N Values__ gt To limit a request to the Top N Values Report Output Types Advanced Tutorial gt To create an Ask at Runtime filter Changing a Layout Style gt To change a Request s layout style Other Layout Options Directing Output to a File gt To direct a Request s output to a table Directing Output to a Graph gt To direct a Request s output to an Excel chart Directing Output to a Pivot Table gt To direct a Request s output to a pivot table gt To change the layout of the sample pivot table Restoring Your Preference Set Setting___ Chapter 4 Using Foxfire Getting Help gt On line Help Messages gt Context Sensitive Help gt Help Button gt About Foxfire Report Writer Request Manager gt To run a Request gt To preview a Request gt To create a Request gt To edit a Request gt To delete a Request gt To quit Foxfire Comments gt To view or edit Request Comments Request Types Layout Styles Request Editor Command Buttons in the Request Editor gt Save As Choosing Data Items gt Changing the Selected Items and their Order gt Data Item Hide Option Filtering Requests gt Filter Builder Command Buttons gt To choose the filter condition s data item gt To choose the Is Not operator gt To choose a relationship for the filte
143. n v Design Previe Display or Data Environment Hide the specific toolbar eport Controls Toolba Layout Toolbar alette Toolbar Peeve amie Show the position of the RT cursor in the lower left Grid Lines hand side of the layout Show Position editor Figure 5 4 Layout Editor View Menu Beport Window Help Edit special sections of the report layout TithSumimary Data Grouping Edit Group Band l Vanables Information Default Font Create Edit Delete Report Private Data Session Variables iit Report Aun Report Figure 5 5 Layout Editor Report Menu 5 8 Resizes and moves ESH Add a picture or controls After you create a control the Select Objects button is automatically reselected unless the Button Lock Allows you to add button is pressed down multiple controls of Figure 5 6 Report Controls Toolbar the same type without having to Add Edit a text object click the control button on the Add a rounded toolbar more than rectangle or circle once Add Line Object Customizing The Layout gt To adjust the height of a band 1 Drag the band s separator bar to the desired position 2 To set a precise band height double click the band s separator bar 3 In the Height dialog that opens enter a value for the height gt To adjust the height of an object To make a specific object taller or shorter first insure that the band is tall enough to accomm
144. n variables around the core data area to generate different views of your data As with Excel s charting capabilities there are many options available so please consult your Excel documentation for a complete explanation of this feature Detail summary or cross tab Request output can be sent to a pivot table Since no interface is provided with this release as to where each result column is to be placed in the pivot table some assumptions are made when creating the pivot table If you output a cross tab or a summary Request to a pivot table you get a one to one mapping of rows in the result table to rows in the pivot table since the Foxfire SQL query puts the 4 59 Layout results into summary rows with numeric columns aggregated entirely within Excel All character and date data items in detail or summary Requests are initially placed as rows in the pivot table and all numeric columns are initially placed as data fields Once the pivot table has been generated the user can change the positions of any of the variables using Excel s powerful interactive pivot table interface MS WORD Foxfire can send query results to a MS Word mail merge DOC file This file can then be used within Word for merging into another Word document that has the merge placeholders for the records produced by Foxfire Word has an easy to use mail merge tool for merging properly delimited query result files into parent mail merge documents Actually W
145. nce Set Note A correspondence must be maintained between the layout and the selected data items of a particular Request Otherwise an error message appears when you run the Request This means if you add items to the layout you must also add them to the Request Similarly if you add data items to the Request you must also add corresponding fields to the report layout Do not specify any table aliases use only the output field names Creating A Summary Request Sometimes you may want a summary Request that provides a single line summarizing selected information for each group as opposed to providing all of the details For example you might want a report that shows how much total business you have done with each dealer as opposed to seeing all of the details of each dealer sale This type of request is a perfect application of the Summary Request type 4 72 The Group Identify Items maybe be one or more columns identify groups or values which are being summarized in each line of the report Except for the selection of data items and one new type of filter condition the steps of creating a summary Request are very similar to creating a detail Request There are two types of columns in a summary report group identity and summary data Request iiA ULE Lender actrurty summery Pome Hem Smunt dupt Imin dyd Mupi Prier Min Carag 4 pein FR gt fF r paran amp sare 1S 1 oo 3 002 00 4x0 icon
146. next to the Filter button Joins for Request SAMPOJ BMW or Audi cars in inventory with cxpenacs x Left Perent Right Crile CARS EXPENSES 7 ExXPEWSES ey Indicates that only records with both CARS and EXPENSES will Join Type INNER be selected Joir Descripton Include ONI Y CARS that have matching FXP NSFS as Include ONLY EXPENSES shat have matching CARS i alia Reset to Defaults wiew Join Tree IV Use JON clause 0 relate tables o Nested Sequential style Sequertial style only Cancel Note that the form shows all of the tables needed for the Request and their relationships i e Cars and Expenses Also note that the text in the middle of the form indicates what kind of records from both tables will be selected For this example we want to include all Cars whether or not there are associated expenses for the car 3 50 3 To do this click the box to the left of Cars meaning that you want to include ALL cars The form now looks like this Joins for Nequest SANPOJ DAW or Audi cars in inventory with expenses Leil Parel Rigt l Child CARS EXPENSES TT NENEGENAENEN Note how LLLLLELLLLL L the Juin Type LEFT OUTER pl ctu re Join Jescristion now helude ALL CARS whether ov not there ara matcaing EXPENSES 4 indicates helude ONLY EXPENSES taat have matching CARS that ALL a CARS will Aerts Resettn Defaults Yew dnin Tree be I Use JOIN cause ta relate tables
147. ng a filter condition using the Like operator with a character or memo data item you may use wildcard characters to specify a partial filter value entry The wildcards by default are and _ underscore character The wildcard may take the place of one or more characters while the _ takes the place of only a single character Example A filter condition of Customer Name Is Like BL would retrieve the names like Blackstone Bligh and Bly while Customer Name is Like BL_ would retrieve only Bly since the underscore character indicates that there is exactly one wildcard character If you need to search for a character string that really does include one of these wildcard characters you may tell Foxfire to treat it as a non wildcard character by preceding it with a the tilde character Thus a search value A_ would find all two letter strings beginning with A while A _ would look for the exact string A_ Building Filter Conditions gt Using Connectors AND CONNECTOR Each time you add a new filter condition Foxfire assumes that you are doing so to narrow the potential result set At the end of Chapter 2 you created the Request SAMPLEX Red Cars on Hand by adding the filter condition Cars Color Is Like Red to the existing filter condition Cars Date of Sale is Empty 4 34 With the initial filter condition the repor
148. ns enabled gt To create a batch 1 Choose Requests from the Available Requests list The chosen Requests appear in the Requests in Batch list 2 Choose Save Batch The Save As dialog opens swenfaout Ale Save Batch to fi Save as type Table DBF dbf Cancel 3 Enter a name for the batch Note that the batch information is contained in a table DBF By default the batch table will be stored in the Foxfire OUT subdirectory gt To edit a batch 1 Choose Open Batch 2 Select a previously saved batch table By default Foxfire looks in the OUT subdirectory for batch files 3 Change your selection of Requests by adding Requests from the Available list or removing Requests from the Request in Batch list 4 92 4 Choose Save Batch gt To run a batch 1 Choose Open Batch The Open dialog opens 2 Choose the desired batch 3 Choose Run Batch The Requests will run in the order in which they appear in the Requests in Batch list gt To edit a Request while in the Batch Builder 1 Choose the desired Request in either the Available list or the Requests in Batch list Since a Request is highlighted in each list the active Request can be identified by the dotted line around it 2 Choose Edit to open the Request Editor for the active Request After making your changes and saving them Foxfire returns you to the Batch Builder Foxfire Fit to Page Try to squeeze as much as you can
149. nsmission Purchase Price i Display Data tens In Request Call Help Dictionary Group indictor specifies if records will be grouped or not by this data item Switch on and off by choosing the Ungroup button 4 42 Sorting The order in which sort by data items are selected controls the sorting sequence The first item in the list specifies the primary or major sort the second specifies the secondary or intermediate sort and so on The last sort is called the minor sort Example If the first item in the Selected list is Last Name and the second is First Name then these choices make the initial sort on last names and the secondary sort on first names This is a phone book sort where Adams comes before Baker If there are several Bakers in the report Frank Baker will come before Gail Baker gt To choose data items for sorting On the left is the list of data items available to be used as sorting criteria 1 Foxfire automatically displays only data items you specified for the Request In some cases you may want to sort a Request based on a data item that doesn t appear in the Request In such cases choose All To switch back to only the Request s data items choose In Request 2 Choose the data item you want to sort on Note that the data item moves to the Selected list and no longer appears on the Available list 3 Repeat for each data item you want to inc
150. o exit the preview the Output Choices dialog box opens see The Output Choices Window on page 4 66 Tips for Customized Report Layouts Foxfire automatically generates reports in formats that fit most reporting needs However some applications require customized layouts so you will have to modify report layouts When you modify a report layout the generated layout is saved as an FRX file and the name of this FRX file is stored in the Request Every time the Request is run thereafter the FRX file is used as the layout Note If you add a data item to a custom report make sure the added data item exists in the Data Items table and has been selected for the Request You can verify these by inspecting the Available and Selected lists in the Request Editor 4 70 Editing Requests that Generate Customized Reports If you customize a report layout and then edit the associated Request Foxfire cannot reflect your editing of the Request in the customized report layout See Chapter 6 for a complete explanation of how to create custom layouts There are two ways to include new data items in a customized report y Auto Format and reapply the customization 1 Edit the Request 2 Regenerate the report layout with Auto Format 3 Re apply the desired customizations OR v Add change the layout manually Use the Report Layout Editor to manually add or subtract fields change groups and so on to the existing layout No
151. o make the report Nhk DIVA fit in th ict 77M an FIFA 3 Ee DIGI Tot sfor Moke BMW Avg 91623 2 000 zE Sed 3 SRAM FANA 447 AIF 3S1 S975 INR TINSA 3504 fh mn amy IAN ANF 3s 3 1498 TMAA EEM 3s f ann am IMIA REM atinar 3 10M 4A IRIT D atnan AO OMA ISARA EMA RRC 3S7 3 1AM SNRA AER RRC ysa 3 170M ARITA FEST atna 1975 nR EEA REM ysa 3 15M 450A Ang an 17 409 aug an 19 009 2 Rrs im bs Sedradral These calculations are set up Figure 3 6 The Resulting Report in the data dictionary or Group options Creating a Detail Request In this section you will create a Request for a detail report This report will be very similar to the INV_ORG report we just looked at but will include some minor changes Business Specification for Request We must be able to know how many TOYOTA cars are currently in inventory i e unsold at a particular time We need to be able to see the following information about each car in inventory stock number make model mileage color transmission type how much we paid for the car and the dealer number we bought it from We need to be able to see all of the cars for the same make and model sorted together on the report Before starting be sure the Request Manager is displayed 3 16 gt To create a new Request 1 Choose the New toolbar button OR Right Click on the tree side of the Request Manager and pick New Request 2 The New Reque
152. odate the change 1 Drag the object to where you want its top to be positioned 2 Stretch or shrink it by dragging the lower border of the object until it is the desired height 3 The height is adjusted in whole line increments if Snap to Grid is set on or increased in direct proportion to the adjustment if Snap to Grid is not set on gt To adjust vertical spacing spaces between lines 1 To adjust the number of blank lines between detail lines drag the Detail band to the desired height 2 To adjust the spacing between groups drag either the upper or lower Group band to the desired height 5 9 Note Vertical spacing adjustments are made in whole line increments if Snap to Grid is set on or in direct proportion to the adjustment if Snap to Grid is not set on Moving Objects gt To move a single object Simply click and drag the object to the desired location gt To move a group of objects 1 3 4 Point the mouse cursor above the desired objects then holding down on the mouse button mark a circle or rectangle around the objects A dotted line appears around them When you release the mouse the dotted line disappears and the objects are selected OR Hold down the Shift key as you choose each object you wish to include in the group Drag the group to the desired location A group created this way is temporary and is released as soon as you click outside the group However you can
153. ogs using at runtime 4 29 making optional 4 22 no value specified 4 22 options 4 24 Ask at Runtime Filter advanced options see 4 28 3 59 creating 3 59 Ask at Runtime Options 4 22 4 24 add prompt 4 25 add delete conditions at runtime 4 27 edit entire filter 4 25 4 26 4 29 effects of 4 26 enter filter values only 4 26 4 29 help text 4 25 runtime help 4 28 runtime prompt 4 28 specifying options 4 25 user may edit 4 27 Auto Format 4 61 4 64 5 5 5 6 Automatic Saving 3 32 Average 3 42 4 48 Avery labels 3 35 4 80 B Batch Builder 4 91 creating a batch 4 92 editing a batch 4 92 editing a request from a batch 4 93 running a batch 4 93 Browse 3 4 3 24 4 11 4 45 4 56 Build Expression 4 23 Building Filter Conditions 4 34 Filter Builder 3 7 Business Specification 3 10 Index Cross tab Request AVPROFT 3 41 4 76 Label Request DEALLAB 3 33 OR Connector SAMPLE3 4 37 Outer JoinSSAMPLEOJ 3 48 Renamed Request SAMPLEX 3 28 Sample Inventory Report INV_ORG 3 10 Sample Inventory Report SAMPLE1 3 16 Summary Request DLRSALES 3 38 Top N PROFTOP3 3 52 Button cross tab sorting 3 44 print preview open door 3 11 Buttons defined 1 7 group 3 23 help 4 3 Map of Foxfire 3 5 moveable list items 1 11 mover 3 19 new request 3 17 option buttons 1 12 preview 3 26 request editor 3 12 request editor copying
154. only select TOYOTA cars gt To create the filter condition 1 Choose Filter 2 The empty Filter Builder opens 3 19 Filter for Request SAMPLE Inventory On Hand Ea Data Item Is Not Relationship Values E Edt i Dy t jj paete Union Top N I Cmit Duplicates Ask at Runtime Options Help Dictionary OK Cancel 3 To build a new filter you must add conditions to the filter one by one 4 To add the first filter condition choose Add 5 A drop down list of data items appears Note Some data items have an R next to them which means they will be retrieved using FoxPro s Rushmore optimizer to achieve significantly faster results 6 Choose the data item name you want as the first filter condition Cars Date of Sale 7 To specify a positive comparison choose Is in the Is NOT operator 8 The IS option is automatically highlighted when chosen This option switches from IS to IS NOT and back again 9 Choose Relationships to indicate the type of relationship 10 A drop down list appears with a list of possible relationships that are relevant to the data item 11 Choose Empty 12 The completed filter condition should now look like this This filter narrows the results to unsold cars only 3 20 Fiker for Request SANPLEI Inventory On Hand Lx Gera ttem fe Noi elstionchi Velus g3 Cars bate Of Sate Filter condition i zat Ad
155. ord recognizes a wide variety of file types so you can just as easily specify your output to a FoxPro DBF and let Word s mail merge tool use the DBF file If you choose an output type of Report or Label you can use the options in the Layout section to further refine the appearance of your Request s output For Report you can choose a Layout Style and define additional Options For Label you can choose a Layout Style Layout Style The Layout Style determines the way the report is physically formatted and what fonts it uses see Layout Styles on page 4 9 If you are using a graphical version of Foxfire You can choose from Presentation Executive and Ledger in addition to Character and Character Web Page World Wide Web Browseable Output By selecting the layout style Character Web Page Foxfire processes the result data through a special character layout template to a file then post processes the file to remove carriage return characters The result is an ASCII file with HTML directives in it allowing it to be viewed in Web browser software While the layout is bare bones you can enhance the template to fit your specific requirements 4 60 Layout styles available Figure 4 23 Output for Request INVENTRY Inventory on hand gt To choose a different layout style 1 Choose Auto Format if it is not dimmed 2 Choose a layout style from the Style drop down list Note If you have customized t
156. ose selected as group fields 3 55 Page Numbers TS Request SAMPLE1 Inventory On Hand Page i re Tanien si PE E E Selected ek Fe Data Items Model smn Milrape 110701 Calor SIUVER Price Paid i 12m Trans 3 Speed Pure ML Stack 2 8 Make ALDI Model S000s Milpage 92835 Color BLUE Price Paid 1 600 Trane 5 Speed Pure SLCP Master Detail Group Headings Format In this output type each page contains the page number at the top of the page For each group that appears on the report the group header is followed by the column headings for the selected data item The records for that group are then displayed Each record contains data for all of the selected data items except for the group by items since this data was just displayed in the group header At the end of the group the group footer information is displayed This request output type is good to use when you have more data items selected then you can display on a printed report since the group by items are pulled out of the widest part of the request 3 56 Request SAMPLE1 l ineas On Hend Note that the group data does Column Headings not appear in the with the appear at the top of Page 1 column data each group Make AUDI Stod Model Mileage Color Price Paid Trans Pure 2448 5000 110291 SILVER 1 500 3 Speed MLM 20 8 5000s 92836 BLUE 1 500 5 Speed SLCP 2387 50008 129127 GREY 2 000 5 Speed MLM 2389 50008 73767 500 Automatic EI Tota
157. ou have requested 5 When prompted with Cars Color is Like just press OK 6 When the report appears on the screen you can scroll around it Move the cursor anywhere inside the report and click Scroll arrows will appear on the right side of the screen and you can click on these to move up and down through the report i Meport Designer inv_ong trs l age 1 Rrape IN ULORG ne on konitan dinal Rowe n MERE Trans Price Paid Dimum Am A Fin SUL EL SUU ty SS 200 261123 218 50005 F 50 stirs lotalstor Make AUDI amn AYSU N Mok BUICK FWA CNIRY Speed 241 CNIR Y AN 5l spred 233 LES BRE Spsed 2355 RIGAL i Sp d 222 RIGAL Z 3p d agy RCAL ront e 2299 SAYHA arona e ar Sarl ARs Ayre 2 S4VLAR d Spocd Fen vug 20072 383 703419 pa re ay 245588 nid 176 CEEE E E E E 7 Choose the Open Door on the Print Preview toolbar button 8 The Output Choices window opens 3 11 Output Choices INY_ORG 9 This window provides a summary of the number of records printed on the report and gives you an opportunity to Print the report or review what you just looked at Other items on this window will be reviewed later in the class 10 Choose Close 11 The Request Manager reappears 12 Next view the Request that produced the sample report examining its different parts gt To view the parts of a Request 1 Highlight the INV_ORG Request in the list 2 Choose
158. our tables and the relationship between them A line indicates each relationship with one or two arrowheads at each end A single arrowhead indicates one record in the table a double arrowhead indicates many one or more records in the table In the E R diagram for the sample database the relationship lines indicate that many Cars are sold by one Salesman and one Car can incur many expenses Sample Data Car Broker System File Relationships Ea satesman gt feces al Sold by aa 4 Sold to iaa cars H a Incur v Yv EXPENSES Demo Use of Views Figure 2 1 gt To display the sample E R diagram in Foxfire You can view the E R diagram shown in Figure 2 1 while running Foxfire First open the Request Manager From the Foxfire pulldown menu choose About Foxfire and then choose Demo File Relationship Diagram or press F7 at any time while in Foxfire Multiple Relationships Between the Same Two Tables In more complex situations you may have two tables with multiple relationships between them In the sample database Cars and Dealers actually require two relations cars purchased from dealers and cars sold to dealers To understand how this might occur consider the way the broker thinks about a dealer For any car a dealer can either be the seller if the broker purchases the car form that dealer the buyer if the broker sells the refurbished cars to the dealer or both Now suppose the broker w
159. oxfire has a more sophisticated Ask at Runtime option which presents the full Filter Builder when the Request is run instead of the simple Enter Values dialog seen in the above example See 4 29 for more information 3 59 Changing a Layout Style This exercise is an example of how to change a Request s layout style It is also an example of how to change a Request so that its output can be browsed on a Web Page You do this by changing the layout style to one that adds the necessary Hypertext Markup Language HTML to the Request s results Note These instructions show you how to add HTML notations to a Request s results so it can be browsed on a Web page They do not attempt to cover the more technical issues of obtaining a Web server creating a home page and other aspects of making items accessible on the World Wide Web which are beyond the scope of this manual gt To change a Request s layout style 1 From the Request Manager Examples of Special Features folder highlight the Request QUARTILE and choose Output from the navigation bar 2 In the Output Selector choose Character Web Page from the Style drop down list 3 Choose OK then Run Notice in the Report Preview that HTML instructions now appear in brackets in the output This allows the output to be viewed in a Web browser 3 60 Output for Request QUARTILE Quartile rank profit 4 Choose Close to return to the Request Manager
160. page Foxfire provides these layout styles v Presentation uses proportional fonts Sans Serif v Executive uses proportional fonts Serif v Ledger uses a very tall narrow non proportional font to place maximum text on the page v Character uses a fixed space font y Character Web Page produces a report in an HTML format for use on a Web page Once a Request has been created you can change the layout style in the Output Selector See Layout on page 4 60 for information on how to change the layout style Request Editor The Request Editor is where you specify all the characteristics of a Request Request Editor Ea Naire Deszr plion EAMPLE1 Invertory Cn Hand Dela Ilerns 08 27 2800 Inventory On Hand Stock Make Mod l Mileage Color Trane Pure 150 PONTIAC FREBIRD 1471235 WHTE 4 Speed COP titer Jans Scrt Group Care Date Of Sale Is Empty Cars Maka s Like TOYOTS Outpt Request ype Detal Columnar Raport 70 Ssrsen Layout Style Presentatior Run Previews Count Show sa Figure 4 7 Foxfire Request Editor Seve as The Request Editor contains buttons or places to hold the following parts of a Request v Name Enter a name to be used to identity all output created by the Request up to 8 characters Spaces between characters are replaced with underscores Name is required to save the Request If left blank here you will be prompted
161. produces labels one label for each record __ name address PRAT A ASK A 195 Cdi u nia Svcine Evmingame a UIL AUTO WEST ACURA 27136 Fient ui L3 F enort i 4 J345 AUTC WEST HINDA 3356 F eni ud Bid remort f 4 Yds A TA AFSAT TIOTA 33555 F emort Bilyd Freriuil CA 3453 A v ANFI MATA 4 MALF BND A H20 V7 Captol Engrescvay 715 W Captol Exxresonay a 1 Juw 4 96135 Ba 1 Jose 04 95135 One label for each name address Request Components Each Request contains information that defines the query what data to include in the results and the output the form and format of the results The query information includes y The data items that will appear in your report For example if you were in the auto wholesale business you might want a report listing the cars in inventory Among the data items would be Car Make Car Model and Year of Manufacture y Filter conditions Filters enable you to select and report subsets of information In other words filters narrow the potential results For example you may want to see all red cars sold to a specific car dealer Sort order 3 3 Sorting determines the order of records in your report For example you commonly find primary and secondary sorting in telephone directories Your phone book is primarily sorted on Last Name and secondarily on First Name within Last Name That means that when there are several listings for the last name Smith the phone di
162. quest may overwrite the output of the original Request Choosing Data Items Use the Data Items Selector to choose the data item fields you want include in the Request output Data Items for Request INVENTRY Inventory on hand O x Drag this double Comment 31 headed arrow up or Cars Date Of Sale 9 scald 3 down to move a Cars Date Paid 9 Cars Model 13 selected item toa Cars Date Purchased 9 Cars Mileage 8 igs Cars Days in Inventory 16 new position on the Cars License Number 8 CarsiColor 11 list and the Cars Month Sold 8 Cars Transmission 11 Cars Purchased From ID 5 i generated output i ahaa F Cars Purchase Price 11 Cars Sale Price 11 Cars Salesmans Id 4 Cars Sold To Dealer Id 5 EA Hideg Characters Selected 77 Dictionary Cancel Figure 4 10 Data Item Selector The Available list on the left side of the window displays the data items that could be chosen for the Request The Selected list on the right side of the window displays data item already chosen 4 13 The chosen data items appear in the output in the same sequence as in the Selected list For example if your Selected list includes Name Salary and Phone Number in that order the leftmost column in the output will be Name and the rightmost will be Phone Number A chosen data item can be removed from the Selected list by choosing it there and double clicking or pressing Enter The data item returns to the Available list
163. r condition gt To specify the filter condition value Ask at Runtime Options gt Specifying Ask at Runtime Options for a Request Using Ask at Runtime Dialogs at Runtime Special Filter Options Filtering Tips Building Filter Conditions gt Using Connectors 3 65 3 65 3 65 3 66 3 68 3 68 3 70 3 71 4 2 4 2 4 2 4 2 4 3 4 3 4 4 4 6 4 6 4 6 4 7 4 7 4 8 4 8 4 8 4 9 4 9 4 9 4 11 4 12 4 13 4 14 4 14 4 15 4 16 4 17 4 18 4 19 4 20 _ 4 24 4 25 4 29 _ 4 31 4 34 4 34 4 34 gt To insert OR connector gt Using In the List Relationship gt OR Connectors With More Than Two Filter Conditions gt Using the Parenthesis to Change Order of Evaluation Sorting and Grouping Requests gt To choose data items for sorting gt To remove data items form sorting gt To change the sort order precedence gt To specify ascending descending order Group Breaks gt To create a group break gt To cancel a group break gt Creating a Derivative Request by Varying the Sort Order Working with Group Options gt Adding a calculation to a group break gt To remove a summary calculation Group Formatting Options gt To add a formatting option to a group break gt To remove a formatting option Specifying Output Options Report Output Types Other Output Types and Formats gt Working With MS Office Layout gt To c
164. r join defined 2 5 outer join defined 2 5 INV_CUST 5 2 Is Not Operator 4 17 J Join Builder 3 51 4 10 dialog 3 50 Join Editor 4 10 Joins inner join defined 2 5 join builder 3 50 modifying default joins 3 7 outer join 3 48 outer join defined 2 5 overriding default 3 50 L Label Editor 4 63 Label Request 4 78 Label Request adding multiple items to single line 3 36 available data items 3 35 creating 3 32 3 33 data item selector 3 32 3 33 output options 3 32 selected data items 3 35 Label Request label type 4 79 Label Request sort types by name 4 80 Label Request sort types by height and width 4 80 Label Request specifying indentation 4 80 Label Request table selection 4 80 Label Request data item selection 4 80 Label Request punctuation and text 4 81 Label Type 4 79 Labels 4 56 Layout 4 60 customizing 5 2 other options 3 63 show filter 3 64 Layout Controls toolbar 5 7 Layout Customization 4 14 4 63 auto format 4 64 choose layout 4 64 save as 4 64 Layout Editor 4 48 4 63 4 71 4 90 5 1 5 2 5 3 5 4 5 6 5 7 5 8 adding custom calculations 5 12 adding custom totals 5 12 adding formatting a data item 5 10 adding lines 5 12 adding rectangles 5 12 adding text 5 12 adjusting height of band 5 9 adjusting objects height 5 9 adjusting vertical spacing 5 9 changing object s width 5 11 features 5 6 font
165. r transform one or more in some way you are creating an expression Here is a very brief look at Xbase expressions For further information consult the FoxPro user manuals or obtain a good book on FoxPro and Xbase concepts Concatenating Strings One of the most common expressions you will have to create is pasting together two character data items for example First name and Last name to form a single expression that is the Full name To paste together two data items and make a new expression you use a plus sign The technical term for putting two character data items together is concatenation The following expression concatenates two data items You could have typed this concatenated expression in the Expression input field to create a single data item which represented the salesman s full name last name then first name Sales LName Sales Fname The result would look something like this when Foxfire creates a document Smith John 6 1 Functions This is not very presentable There is a large gap between the last name and the first name and you probably want a comma and a space separating the two Here s how to concatenate a comma into the expression Sales LName Sales Fname By placing a comma and space between quotes you are indicating that you want those quoted items appear in between the other data items The result would look like this Smith John The reason for the gap is t
166. rary by looking for a reference to an Author Subject or Title you can find a particular book directly using a location coding scheme book address without having to search through the entire library Indexing Files Speeds Up Filtering An indexed search significantly cuts retrieval time for a single database record but the timesavings are far more impressive when retrieving groups of records You have already seen how a filter functions when you 7 1 worked with the sample Requests When a Request is run the records in the table are compared to the filter conditions and are included in the results if the filter conditions are met Because indexing saves such a significant amount of retrieval time you should use it liberally on all of your tables Rushmore Optimization Explained Rushmore Optimization Rushmore is a proprietary method for speeding up index record retrieval Rushmore matches all filter query criteria you have specified to the index available in your database and creates a custom sequence of processing steps for retrieving the matching records in the most efficient manner possible For example v If every criterion you ve specified in a filter contains indexed data items Rushmore can pull all of the database pointers into memory at the same time and create at very high speed a consolidated list of records that meet all the criteria This processing sequence is called full Rushmore Optimization v
167. re printing The window can take different forms depending on the type of report you create See Layout Styles on page 4 52 Windows Preview Foxfire provides you with the following options for creating Y Character reports of plain ASCII output i e without printer control codes are created when you select Character from the Report Style list Y Graphical reports are created when you select Presentation Executive or Ledger from the Report Style list You can also create a graphical report by placing a picture on it or by specifying fonts and font sizes The Output Choices Window When you close the Preview window this window opens 4 66 Output Choices SAMPLE1 Request 13 Reccrds Selected SAMPLE1 Inventory On Hand Output Request Type Detail Columnar Repert To Screen Layout Style Presentation Next Action Edit Layout Preview Show SQL Close Figure 4 27 Output Choices dialog y Print Displays the Print dialog in preparation for directing the results to the printer vV Edit Layout Opens the FoxPro report or label layout editor For more information on changing report layouts see Chapter 6 Customizing Report Layouts and Tips for Customized Layouts on page 4 70 v Preview Displays the output on the screen again Y Show SQL Opens a dialog box that displays the SQL statement You can edit the SQL by pressing F6 Save any edits by closing the window with the
168. rectory sorts all first names alphabetically Alice Smith appears before Zachary Smith The output information includes v The output type You can select a report labels browse list and so on v The output destination You can choose to send the output to the printer the screen or to a file on disk Figure 3 1 illustrates the various components that make up a Request DEFINE QUERY DEFINE OUTPUT Report type summary detail cross tab label Output T ype report graph spreadsheet etc Data Items which data Output Subtype Master itemsto indude in the Detail F om column etc report Font styling presentation Fitter conditions criteria for character executive narrowing the results Destination printer screen Sort amp Group criteria how e to sort and group the data Request Figure 3 1 Request Components 3 4 Foxfire s Organization Foxfire is organized to reflect the various parts of a Request and to follow the sequence of steps normally used in working with them Refer to the following diagram as you study this brief overview Foxfire Navigation Map Request Manager Master List of Requests Request Editor The big picture of the current Request Specify Data Specify Fitter Specify Table Specify Sort Specify Output tems Criteria Relationships Group Criteria Details i Figure 3 2 Foxfire Navigation Map If at anytime you want to reference this diagram on screen select
169. rence Set 3 9 Request for the Sample Report 3 10 Sample Inventory Report 3 10 gt To run the Request for the sample report 3 11 gt To view the parts of a Request Relationship Between Requests and Results Creating a Detail Request gt To create a new Request Specifying Data Items gt To choose the data items Creating a Filter gt To create the filter condition Specifying Sort Group Criteria gt To specify sorting and grouping Specify Output Type gt To specify an output type format Other Request Editor Buttons gt To preview the Inventory on Hand report Creating a New Version of a Request gt To rename your Request gt To add a filter condition gt To preview your modified Request Creating Label Request gt To create a label Request gt To add multiple data items to a single line in the label Intermediate Tutorial Creating A Summary Request gt To create the summary Request gt To specify summary data items Creating a Cross Tab Request gt To create a cross tab Request What to Report on Rows and Columns__ gt To specify data items for a cross tab Request gt Sort Order Options gt Summarizing the Values gt Filtering data for Cross Tab Requests gt Output Type Previewing and Saving gt Other Cross Tab Options Creating a Request with an Outer Join __ gt To create a request with an outer joi
170. resale the broker may spend money on repairs tires upholstery painting detailing and so on Any expenses incurred by the broker must be added to the original purchase price to determine actual profit upon resale of the car Additionally whenever a car is sold the broker needs to know who the salesperson was in order to track sales and profits for each salesperson The Database Tables Records and Fields A database is a collection of data descriptions and measurements about the things entities involved in an activity that is being studied or managed In the car brokerage the entities involved are cars expenses salespeople and dealers The sample database contains four corresponding tables also known as files v Cars v Expenses y Salesman yY Dealers Each table contains records with one record describing each specific entity For example each car would have its own record in the Cars table A record contains one or more data items This is detailed information specific to that entity In a car record for example Make and Year are two data items that describe each car Relations Tie Database Tables Together Good database design requires that data which directly describes a specific entity should be assigned its own table and each table should have indexes created for the data items most commonly used for retrieval For example all car data is stored in the Cars table To make the most effective use of a d
171. rms The following samples illustrate each Request type in a form of a report yY Detail a Request that displays a row for every qualifying record Page 1 Stock Make Modd Mileage Color Trans 1531 TOYOTA Pi 57154 4 Speed oF COROLLA 105102 4 Speed TOYOTA VAN ARINA VWHITF Automatic OyoTs CEUCA 121065 i 4 2333 a a Pi 135189 YELLOW Auton atic One row per individual car OTUA Summary a Request that groups records then summarizes and calculates information about that group You specify how to group the records Page 1 Avg of Sull w lene Suni Avy uf Sununu uyur Ary uf Days in 1970 4275 gaus 1SuULU LU 2 Tk Dac C2 4UTO AKIO 0G 4 0 077 24 9 23 600 Kw 4 140 77 BIARN AK SUTIN IES 1 totaa 13 250 41 ccoco gaama 3 130 00 Buh ANS CONDO AP 3 12 6 po 4225 F2LEULU i 4 TL One row per grouping of cars the group is all cars purchased from the same dealer 3 2 Variable 1 each salesman Y Cross tab a Request that analyzes the relationship between two variables and displays the results in a matrix containing a value for every pair of variables Salesman Name aseph Collins Mark Dodge Mark Harrison John D Hinson Marvin Kasper Joseph Koenig a 4724 Ronald J Rader 387 79 450 05 343 91 38 71 Variable 2 by month Average 367 78 433 86 323 50 362 79 Analysis average of gross profit vY Label A Request that
172. rol To move to a more specific location press TAB as many times a needed another way of highlighting Note In a Windows application a data item changes color when it is highlighted A button is surrounded by a border Making Choices You can indicate a choice in several ways depending on the type of items being offered v Data Entry Fields Highlight the desired field and press ENTER v Lists Highlight the desired item by pressing the arrow PGUP PGDN HOME and END keys Then press SPACE twice in rapid succession Buttons Highlight the desired button then press ENTER Keyboard Shortcuts Pressing a combination of keys can quickly specify most menu and button choices These are called keyboard shortcuts because with a single action they highlight and choose the option menu choices can be made without displaying the menu The specific key combinations are indicated on the menus or hinted in the buttons themselves with an underlined character To use keyboard shortcuts to choose an option from the v System menu press ALT and the key corresponding to the underlined or different colored letter that appears in the text of the choice y Pull Down menu press the key combination listed to the right of the option Keyboard Action shortcut The keyboard shortcut is the CTRL key plus the indicated letter Button press ALT and the key corresponding to the underlined letter in the tex
173. runtime options 4 16 between operator 3 59 build expression 4 23 building Foxpro Expression 4 20 choosing a relationship 4 19 command buttons 4 16 filter condition values 4 20 4 22 comparing against NULL values 4 19 connectors 4 34 filter condition values 4 19 creating conditions 4 34 delete button 4 16 deleting a filter 3 30 edit button 4 16 filter condition values comparing against another data item 4 22 Top N Values 4 32 filter condition 4 15 filter condition parts of 4 15 filter condition relationships 4 18 filter condition values 4 19 In the List 4 39 In the list relationship 4 36 Is Not Operator 4 17 omitting duplicate records 4 17 OR button 4 16 OR connector 4 35 OR connector with more than two conditions 4 37 parentheses buttons 4 16 4 38 4 39 relationships 3 20 selecting only blank empty or zero values 4 20 4 21 show list of values 4 21 special filter options 4 31 tips 4 34 Top N values 3 52 Top N Values 4 31 Union 4 33 using data items in filter conditions 4 17 Filter Condition values 4 19 Filter Condition Relationships 4 18 exactly like 4 41 In the List 4 36 4 39 like 4 41 Filter Conditions creating 3 19 defined 3 3 Is Not operator 3 20 3 21 Filter Options 7 4 Filtering Requests 4 15 Filtering Tips 4 34 Fit to Page 3 31 3 54 4 52 4 63 4 93 4 94 footer 4 50 Form Output Format 3 55 4 53 formatt
174. s Basic This section introduces the basic concepts and organization of Foxfire You learn the parts that make up a Request and step by step how to create a detail Request and a label Request v Intermediate After learning the fundamentals you are ready to create a summary Request and a cross tab Request Additional lessons demonstrate how to use special filter options such as the outer join and top N values and how to affect the appearance of groups in a report v Advanced In this section of the tutorial you learn how to use filters that can be edited at runtime Other lessons include how to create a report that can be browsed on a Web page how to create a database table from the results of a Request and how to send a Request s results to a graph and an Excel pivot table For some of the advanced exercises it is helpful to have some understanding of programming and FoxPro Basic Tutorial Requests A Request is a complete job or task in Foxfire A Request contains three types of information v Identification The Request name description and type You can create four types of Requests in Foxfire detail summary cross tab and label 3 1 v Query What data to include in the results i e the Request s contents Y Output The format of the data in the results i e the Request s form or shape Request Types Request types control which type of processing or analysis the Request perfo
175. s IIF logical expression return result 1 return result 2 If the logical expression returns Yes then the return value specified first is returned If the logical expression returns No then the return value specified second is return Note that these IIF commands can be nested within each other to create a structure demonstrated below IF condition 1 THEN IF condition la THEN RETURN result 1A ELSE RETURN result 1 ELSE RETURN result 2 We can use this example when creating a data item that is the salesman s full name including middle initial i e John R Smith We do not want to include the period and space after the middle initial if the middle initial was not specified i e EMPTY In other words if the salesman had a middle initial then we want to concatenate a space the middle initial a period and another space to the first name Otherwise we only wanted to concatenate a space to the first name The data item expression would look like this ALLTRIM SALES fname IIF NOT EMPTY SALES middle o SALES middle SALES lname Note the bolded expression This is the part of the expression which knows how to handle the case where the salesman does not have a middle initial IIF NOT EMPTY SALES middle SPACE 1 SALES middle SPACE 1 If the salesman has a a Just Concatenate middle initial Concatenate a space a a space period and a space
176. s T Use SQL TOP N TOP hvalues C TGP h Percent ca Figure 4 17 Top N Values dialog To use the Top N choose the Include check box then specify how many of the top values you want Then choose a sort item from the list which contains the same data items selected in the Sort Group Selector Note This feature is meaningful only in context of a sort sequence If the Request has no sorts specified output is limited to the first x rows in the unsorted result set which is essentially a random selection The impact this type of filter has on your report can be described by the following example Example Your Request is sorted by Cars Make then Cars Year then Cars Purchase Price Table 4 1 on page 4 18 describes how this type of filter limits the results of the Request depending upon which sort item you choose Choose to include Result will include The first 5 values of Cars Make Records for only the first 5 Car Makes The first 5 values of Cars Year Records for the first 5 Cars Years of each Car Make The first 5 values of Cars Purchase Records for the Top 5 Purchase Prices 4 32 Price for each Car Model for each Car Make Table 4 3 Top N options Note that the other option on the Top N dialog Use SQL Top N gives you the option to show only the top N records selected or top N percent of all the records selected for the request If you select
177. s In some lists you see a series of small buttons to the left of the list items This indicates that you can change the order of the list by dragging the items to new position Double Make headed Cars Model 13 arrow Cars Mileage 8 signifies a Cars Color 11 movable Cars Transnission 11 list item Cars Purchase Price 11 E Hidew Chevacte s Selected 7 Figure 1 7 Movable List Items To reorder items using the mouse 1 Highlight the item you want to move A highlighted item has a button marked with a double headed arrow 2 Drag the double headed arrow 3 Release the mouse button To move an item using the keyboard press CTRL along with the up or down arrow to move the highlighted item to the desired location Option Buttons Option buttons are displayed when there are only a few choices and only one choice is permitted A dot appears in the selected option button send To i Printer Selected option button Screen File Figure 1 8 Option Buttons To choose an option button using the keyboard 1 Press TAB repeatedly until you have highlighted the option button you want to choose 2 Press SPACE Check Boxes Check boxes allow you to enable or disable a feature To choose a feature click its check box so an X appears To cancel the feature click the check box so that it becomes empty the X disappears When a window displays several features in a group with check boxes eac
178. s already selected Choose Cancel and go to the next section of the tutorial 2 Choose Sample Data Set and choose OK Warning Do not choose Use at Startup while Sample Data Set is highlighted as this will cause Foxfire to start with the sample data rather than your regular data the next time you start Foxfire 3 9 Note If you had to choose the Sample Data Set then you are working with a copy of Foxfire that has already been customized Don t forget to restore your Preference Set settings when you finish the tutorial so that every time you start Foxfire your customized Requests will appear Request for the Sample Report This section describes the relationship between a Request and the final report which is produced when the Request is run You will choose a sample Request from the list of those provided and then run it It is as important to know your database as it is to understand the business problem that needs to be solved with each Request that you create In the following sections we will review some sample Requests and examine how the different pieces of Foxfire effect the way the final report looks Before each example is reviewed a simple business specification will be provided to help you better understand the business requirement for the request you will be reviewing Sample Inventory Report The following describes the business specification which was written by the Business User prior to the Samp
179. s can generate many rows if there are many different columns of character or date data that are unique For example the Request Inventory on Hand would be better suited for a chart as a summary Request however you still would probably want to eliminate all columns that do not lend themselves to aggregation such as the Stock No data item Also since there are many different combinations of Makes and Models you will want to eliminate the Model column or filter the Makes to one or two different types It is usually best to output your report to screen and check the size of the result set before attempting to chart Finally the order of the data items as selected in the Request Editor greatly influences the chart output Keep the non numeric data items separated from the numeric data items in the selected list and list them before the numeric data items Excel Pivot Tables A pivot table is one of Excel s great data analysis tools that allows users to display and analyze summary information as a relation between row and column variables Foxfire can automatically send query results into an Excel pivot table A pivot table is much like a cross tab report but you can specify multiple row column or calculation field variables with groups and subgroups Pivot tables also allow a wide variety of subtotal calculations such as sum average minimum and percentage It is called a pivot table because you can interactively rearrange row and colum
180. s in the filter Refer back to the OR Connector example above and delete the last Cars Date of Sale is Empty condition If you run the Request like this you Il see unsold Audis and BMWs and all cars with 5 speed transmissions sold and unsold This because the expression that Foxfire evaluated was Cars Make Is In the List Audi BMW AND Cars Date of Sale Is Empty OR 4 38 Cars Transmission Is Like 5 Speed 1 Now in the Filter Builder drag the Cars Date of Sale condition to the top of the list 2 Move the highlight down one row to Cars Make Is In the List Audi BMW and choose the Left Parens 3 Move the highlight down to the last filter condition Cars Transmission Is 5 Speed and choose the Right Parens Fite fur Asywest SAMPLES Audis ui Bos Iri Ue Lick Das kan SINT sanhacshia velit Position of added ee os Date OF Sale parentheses changes the results oa Cars Mahe Cars lransmission xt sa a gt uw tem tern To tps sa Aeric oske Bo basse C vera 4 Try the query again Note that cars with 5 speed transmissions are now retrieved only if they are unsold The parentheses caused the conditions inside to be evaluated first and the Cars Date of Sale is Empty condition to be applied after all the conditions inside the parentheses For more complex filter conditions you may use multiple levels of parentheses Foxfire will require
181. se button allows you to select a layout from a list of report layouts previously created and saved The report layout file name FRX appears in the box immediately above the command buttons in the Layout section Auto Format The Auto Format button is enabled only if the Request is using a customized modified report layout Choose Auto Format when you wish to regenerate the report layout Foxfire regenerates the report layout based on your choices in the Data Items Selector Filter Builder etc When you select Auto Format you have the choice of saving the existing modified report layout or deleting it For more information on using Auto Format see Editing Requests that Generate Customized Reports on page 4 71 gt Output Destinations From the Output Selector you can choose to send a Request s results to one of three destination types Send To C Printer Screen cre o Figure 4 26 Output Destinations 4 64 The destination types available are v Printer v Screen v File Output to Printer Select Printer to display a list of defined printers Default printer name specified in the active Preference Set is displayed unless a printer has previously been associated with this Request You may either accept the default or choose another printer from the drop down list If you select a different printer that printer is then associated with the Request Every time you run that Request the output is dir
182. sed in each word processor Generic WordProcessor as a data source ASCII SDF fixed length SDF extension Standard Delimited Format file each row is the same length and fields are right blanked padded Delimited standard ASC extension Character values are surrounded in quotations and fields are comma separated 4 57 Output Output Format Comments Type Data DBF Foxpro DBF extension Current version of Foxpro format DBF Foxbase dBase4 DBF extension dBase 4 or Foxbase format DBF Fox 2x DBF extension Foxpro 2 x format Cursor Creates an internal temporary table to be used by programmers Array Creates an internal structure to be used by programmers gt Working With MS Office MS Excel Foxfire can send query results directory to Microsoft Thus Foxfire can dynamically create Excel spreadsheets charts or pivot tables Running a Request with any of these three output types will automatically launch Excel if it s not already running create the specified Excel spreadsheet and place the user in Excel with the generated sheet active Excel Charts There are eight different chart types available Pie Bar Column Line 3D Pie 3D Bar 3D Column and 3D Line Foxfire generates the selected chart type at runtime then launches Excel with the chart displayed for viewing or editing At this point the user can use Excel s Chart Editor to make any
183. sing The final report will appear with one Car 3 43 Make per row as shown on the screen 3 Select Choose a Value directly above the columns to display a drop down list The columns are numbered One Two Three and so on 4 Choose Cars Year from the list The columns are now headed with the legend Cars Year and several actual data values of Cars Year are displayed in the column headings as a sample Requcst Editor fAVPROFT Average Prcfit of Makes from 1992 to 1999 curv am gt Sort Order Options You can specify sort orders for both the rows and columns The sort indicators are the two buttons located to the left of the rows and above the columns The downward pointing arrow next to the row sort indicator means that the records are listed down the rows in the order indicated by the 3 44 depressed button AZ indicates the value increases i e alphabetically or numerically ordinal while ZA indicates the value decreases as you move down the row The rightward pointing arrow above the column sort indicator means that the records are listed from left to right in the order indicated by the depressed button In this Request the columns are ordered chronologically from earliest to latest year while the rows are listed alphabetically in ascending order A to Z Both of these settings are correct for this Request gt Summarizing the Values It is possible to summarize the columns and
184. specify group options 3 In the Option Types section choose Calculations to add custom calculations to group footers 4 When you choose the option type Calculations the Available section lists the numeric data items in the Request and the command buttons for summary calculations become active 5 The Available list defaults to In Request and displays only the numeric data items that were included in the Request 6 Highlight the group break band Cars Make Summary in the Selected list 7 Highlight the data item Cars Purchase Price in the Available list 8 Choose the command button Avg 9 The summary item Avg of Cars Purchase Price appears below the Cars Make Summary group break band in the Selected list Now the average price paid for the cars in each group will be listed in the group break 4 49 gt To remove a summary calculation From the Selected list choose the summarized data item you want to remove The item is removed Group Formatting Options When you choose the Formatting option type in the Group Options window the Available section lists the formatting options for group breaks Group Options for Request INY_ORG Invent C Gatouanons Forminettne Uptions CREES Gri Gptlior Types iNew page Reset page H No group header No grown fonter Repeal hur if yep cunlinues New ps if lt lines for grp Sui ke Gaunt FVA Win Max Sta DEY vae
185. st Type and Style dialog opens New Request Type and Style Page 6 Stock Model Price Paid Mileage Color Trans Make PLYMOUTH 2118 CARAVELLE 3 100 61233 Automatic 2418 RELIANT 725 118555 WHITE 3Speed 2220 SEDAN 400 79038 3 Speed 1958 VOYAGER 11700 _34738 CREAM 5 Speed Totals forMake PLYMOUTH Avg 15 925 73391 Make PONTIAC 2429 CATALINA 165 194441 BLUE 2360 FIREBIRD 1 000 109420 BLACK Default Presentation j 3 Choose Detail as the Request Type and choose OK 4 A blank Request Editor opens 5 To name your Request type SAMPLE in the Name field and press Tab 6 To describe your Request type Inventory On Hand in the Description field and press Tab 7 Choose Data Items 8 The Data Items Selector opens 3 17 Data Items for Request SAMPIF1 Inventory fin Hand Saas ES Cars Color 11 Cars Comment 31 Cars Date Of Sale 9 Cars Date Paid 9 Cars Date Purchased y Cars Days in Inventory 16 Car Full Dale uf Sale 19 Cars License Number 8 Cars Make 12 Cars Mileage 8 Cars Mndel 12 Cars Month Sold 8 r a Ride Characters Selected 0 Dictionary a Cancel Specifying Data Items In the Data Items Selector you specify which data items will appear in the report and what their column headings will be This window contains two lists The list on the left shows Available data items You may choose any or all of these data items The list on the right shows S
186. st tvou make chenyes Fuatrslwil pruripl you willi spliuns fr adjusting the leyurt u rnalct Sanlirive Explain Sancel When you return to the Request Editor after having made your changes you will be given the opportunity to preserve the layout forcing you to manually edit the FRX to show the changes you have just made or to have Foxfire re generate the FRX forcing you to manually make ALL of your changes again 5 5 Foxfire Report Writer Choosing this option will preserve your This Request currently uses a previously customized Report Master Detail Group manual changes but Headings ayout C FFB0 REPORTSIUNY_CUST FRY you willhave to Foxfire canNot update it to reflect changes to the Request Options are further manually edit the layout to include the changes you just made in the Request Editor Make the Request use an Auto Formatted layout and then manually reapply EJ customizations Knecessary will override any layout as necessary to Meflect changes to the Request manual changes you have made using the lavout editor Choosing this option a Keep your existing custom layout Manually update the Keep custom layout Explain Figure 5 2 Auto Format warning Layout Editor Features and Terminology Figure 5 3 shows the Layout Editor The call outs show several of the control and status indicators v Bands Bands define sections of the layout Band types include v Title prints once a
187. st two months that have still not sold We will be sending these dealers a letter indicating that until these cars sell we will be unable to purchase additional cars from them gt To create a label Request 1 From the Request Manager choose New 2 The New Request Type and Style dialog opens 3 Choose Label as the Request Type and choose OK 4 The Request Editor opens 5 In Name type DEALLAB 6 In Description type Labels for Dealers We Purchased Crummy Cars From and press Enter 7 Choose Data Items 8 The Data Items Selector for labels opens 3 33 Data Items foi Request DEALLAB Labels for Dealers We Purchased Crummy Cars From Age at Sale Color Date OF Sale Date Paid Date Durehased Days in Inventory Full Date of Sale License Nunber zi Make Figure 3 10 Label Request Data Item Selector 9 To specify the Label Type select Choose 10 Label Type dialog opens Note The list of available label types changes with how many labels across you select 1 2 3 or 4 columns and whether your label paper is measured in inches or millimeters 3 34 Label Type Ea Type Height Width E a R Tea oe ee A Characters x 5 Rolodex 14 Lines Characters 4 x 1 7716 x 1 8 Lines 46 Characters 4 x 2 174 Rolodex 16 Lines 46 Characters 6 1 2 x 3 57 8 Envelope 14 Lines 65 Characters 9 7 8 x 7 178 Envelope 17 Lines 78 Characters Avery 4145 15 716 3 172 Avery 4146 1
188. t Choose Is under Is Not 10 Choose Like from the Relationship list 11 To specify the value to which the data must be compared type RED in the Values field and press Enter 12 Choose OK to complete the filter condition You now have two filter conditions cars with an empty date of sale and cars with a red body color 3 30 You are done modifying the filter The Filter Builder should now look like this Filles fue Request SAMPLEA Inverntury On Hand Cars Dale OF Sale Emply Cars Color 13 Choose OK to exit the Filter Builder 14 The Request Editor opens gt To preview your modified Request 1 Choose Preview in the Request Editor 2 You receive a message indicating that Report Layout is not wide enough for all the selected data items and that you have three options Foxfire Report Writer x Try to squeeze as 4 Just chop off the much as you ee fields that don t fit can on the page by Emer nace coe shortening some of the longer fields and using a smaller font Figure 3 9 Fit to Page dialog 3 31 3 Choose Run As Is 4 The resulting report appears Note that all the cars on the report have a color of RED and the Purchaser Address is chopped off the edge of the report Reres SARP ITS Inventory On Hand Paget Fite CareDate OfSale leEmpty AND Cars Culu helike REO lurthascd from O saler Stoner Modal Misage Cola Price Paid Trane Make BUICK EEY A
189. t To run a batch gt To edit a Request while in the Batch Builder Foxfire Fit to Page Chapter 5 Customizing Report Layouts Copying a Request gt To copy a Request Steps For Customizing a Layout gt To start the Layout Editor from within Foxfire gt To Save The Customized Layout Making Changes after Customization ____ Layout Editor Features and Terminology Customizing The Layout gt To adjust the height of a band gt To adjust the height of an object gt To adjust vertical spacing spaces between lines Moving Objects gt To move a single object gt To move a group of objects Changing the Appearance of an Object __ gt To format a data item or text object gt To change an object s width Adding Objects gt To add text gt To add a line gt To add a rectangle Creating Custom Totals and Calculations Chapter 6 Xbase Fundamentals Creating Expressions Chapter 7 Indexing and Rushmore Indexing Explained Rushmore Optimization Explained Rushmore Can Be Accidentally Cancelled 4 84 4 84 4 85 4 87 4 88 4 91 4 92 4 92 4 93 4 93 4 93 5 1 5 2 5 2 5 2 5 2 5 4 5 5 5 6 5 9 5 9 5 9 5 9 5 10 5 10 5 10 5 10 5 12 12 5 12 5 12 5 12 6 1 6 1 7 1 7 1 7 2 7 3 vii Insuring Rushmore Retrieval viii Chapter 1 About This Manual This chapter provides an orientation to Foxfire and an introduction to the t
190. t Cars Make in the Sort by list 6 Choose Group A Grp indicator appears at the right of the Cars Make criterion choosing the Group button toggles to Ungroup in case you change your mind 3 23 Sort Group for Request SAMPLE1 ineventory on hand Stock Number t Cars Make Color Purchase Price Trausmissiui Purchased From ID 7 Choose OK to return to the Request Editor 8 The sorting and grouping criteria are displayed in the Sort Group section of the Request Editor Specify Output Type Now you must specify whether you want the report sent to the screen a file on a diskette or hard disk or the printer You can also specify the output type for the report browse list report labels graph and so on Note Foxfire assumes you want a report displayed on screen unless you specify differently gt To specify an output type format 1 Choose Output 2 The Output Selector opens 3 24 Output for Request SAMPLE1 Inventory On Hand 3 In the Output Type section choose the drop down list to display output type choices 4 Choose Report 5 Choose the drop down list to the right of Report to display the Report Output Formats available 6 Choose Master Detail Page Headings See Report Output Formats on page 3 54 for a detailed explanation of each of the other Report Output Types and Formats 3 25 7 The Output Selector should now look like this Output for R
191. t lists all unsold cars in inventory But as soon as you add the second filter condition to the first you narrow the results to show only red unsold cars Foxfire automatically assumes AND connectors between conditions in the filter OR CONNECTOR If you want to broaden the potential result set insert an OR connector between two conditions gt To insert OR connector 1 Highlight the Request SAMPLE and choose Edit on the navigation bar 2 Choose Save As and name the report SAMPLE2 and change the description to Audi s or BMWs in Inventory 3 Choose Filter 4 The existing condition in the filter specifies Cars Date of Sale Is Empty Delete this condition by highlighting the filter condition and choosing Delete 5 Add two conditions Cars Make Is Like Audi and Cars Make Is Like BMW 6 If you simply add the two conditions the resulting filter would describe an impossible situation Make is like Audi and like BMW at the same time so no records would be selected To include records where the make is BMW or Audi you insert an OR connector into the filter 7 Choose the Or button An OR connector is inserted 8 Drag the OR connector between the two conditions Now your Request will include those records where the Make is Audi or BMW 4 35 i Filter iar Nequest AMMIT Audi s m DM W s In Inventory ig Ed de oe z 1 Doete Uricn Tapi T cmtu Ask y hr ma Dainys ae techn w re 9
192. t of the button Keyboard shortcut the shortcut is ALT the underlined letter After working with Foxfire a while the keyboard shortcuts can be easily memorized to save you considerable time even when you use a mouse Screen Controls What They Are and How to Use Them The following sections explain the various kinds of screen controls that you use to communicate with Foxfire Data Items for Request INVENTRY Inventory on hand Avcailstin Sr Cars Cimri a1 Carce Date of Sale 3 Sura Malie a Gars Date Pord y Cars Ninlel 13 Cars Date Purchased J Gars Milcage u Care Days In Inventory 1a Cars Lioensc Number y Saye GUN ue Cars Month Sold a Gars Iransnissiion 11 Cars Pirihasni 1 Fran If ni G are Biiniiteake Meh 11 Cave Hadio 4 Cars Sale Price 11 Cars Jalesmans Tal 4 Carce Sold To Daaler Id 1 zl dee hercere s Selected 3 Diliwi y Care Figure 1 1 A Foxfire Window Windows and Dialogs Buttons Foxfire communicates using two types of visual displays windows and dialogs Both ask the user to enter information or make a choice but there is one primary difference The dialog interrupts the current process and allows no further action until its question or subject matter has been satisfactorily addressed while a window allows the user to open other windows and dialogs and complete other processes A simple dialog may ask you to choose OK or Cancel or to answer Yes or No Windows may includ
193. t the beginning of the report Page Header prints at the top of each page Column Header prints once at the top of each column for labels Group Header prints just after a group value changes Detail prints for each selected record Group Footer prints just before a group value changes Page Footer prints at the bottom of each page SS SS Wy Se OS Summary prints once at the end of a report The name of the band is displayed in the band separator bar directly below it The objects that are printed in each band are found just above the band separator A new layout has three bands page header detail and page 5 6 footer Additional bands appear if you include such features as groups title page etc in the Request Each band can contain text data items calculated fields lines and rectangles v Toolbars This is a set of buttons that can be moved to any place on the Layout Editor design surface These tools allow you to create report and label objects and provide quick access to some of the more common formatting features such as left right center justify For the Report Controls toolbar choose the desired tool and move the mouse pointer to the desired location in the layout then click to place that type of object at that specific location For the Layout Controls toolbar select the item s to which you want to apply the format and then click the appropriate toolbar button v Ruler Shows where th
194. tDayOfWeek arguments uses Sunday as the first day of the week Return a character field in day month year format for example 31 May 1998 Return the specified date field in month day year format with the name of the month spelled out for example February 16 1998 Convert a character value formatted as yyyy mm dd into a date value Convert a date value into a character value formatted as mm dd yyyy Convert a character value into a date value Convert a date value into a character value formatted as yyyymmdd Use this for sorting values by date Use this function Ayyyy mm dd hh mm ss Date1 Date2 Date1 Days CDOW DOW CMONTH MONTH DAY YEAR WEEK DMY MDY CTOD DTOC CTOD DTOS 6 5 If you want to Get the current date at the time the Request is run Get the current time at the time the Request is run Returns the current system time in 24 hour eight character string hh mm ss format Return a date that is a specific number of months before if negative or after if positive a given Date value Note that all leap year and maximum days in a month rules apply For example GOMONTH 2000 01 31 1 returns 2000 02 29 Use this function DATE DATETIME TIME GOMONTH Common Math Commands If you want to Get the Absolute Value for a numeric Use this function values fiel
195. te If you don t want to lose a custom report layout when you choose Auto Format Foxfire gives you the opportunity to save it before regenerating gt To modify a Request which has minor custom layout changes If you need to make changes to a Request that has a minimally customized layout already use the Auto Format option and reapply the customization 3 Add the data items using the Data Items Selector 4 Go to the Output Selector and choose Auto Format or select Auto Format from the alert dialog displayed when exiting the Data Items Selector 5 This regenerates the layout from the Request specifications including the new data items 4 71 6 Edit the layout to reapply the minor changes 7 Save the Request The minor layout changes that existed before adding the new data items will be available for future reference if you chose not to delete the custom layout at the time of regeneration gt To modify a Request which has extensive custom layout changes Go to the Output Selector 2 Choose Edit 3 Modify the generated report to manually add or delete data items to match the Request If you use the Expression Editor while placing fields the data items from the current Request appear for selection in the picklist on the lower left 4 Save the report Note When you manually customize reports all fields must refer only to data items that appear in the Foxfire data dictionary for the current Prefere
196. tem Filtering Requests One of the primary and most powerful uses of a database is to search for subsets of information that meet specific conditions For example you may want to see all red cars sold to a specific dealer To obtain that subset you would create a filter Foxfire filters the data and returns only the records you want to see in your output You define filters in the Filter Builder Filter for Request PROF_ANL Profit analysis by dealer x Data tem Is Mot Relationship Valie s Cars Date Purchased Is Between 61 61 1999 12 3171999 Cars Sale Price Is More Than 6 68 Cars Make Is Like Cask at runtime a Edit Add Or gt Delete Union Top N T omit Duplicates Ask Runtime Options Help Dicliunary cencel Figure 4 11 Foxfire Filter Builder Each row in the Filter Builder represents an individual condition or test that comprises part of the filter Each filter condition is composed of the following parts vY The data item on which the filter acts v The is not operator v The filter relationship v The filter value 4 15 gt Filter Builder Command Buttons Edit Add Or J Delete Union Top N I Omit Duplicates Ask at Runtime Options Figure 4 12 Filter Builder Command Buttons v Edit permits you to edit existing filter conditions Double clicking the filter condition is the same as choosing Edit v Add permits you to add new fi
197. tems 8 The Data Items Selector opens Data ltame for Raquast DLRSALES Cars Sold to Dealers Avziwle Se 53 cars Aye al Tale Cars Color Cars Date Of Sale Cars Date Paid Cars Date Purchased Cars Days in FPovenlury Cars Full Late ot Sale Cars License Number Cars Mako Cars Mileosse Cars Peale Cars Month sald Cars Purcliase Price a Sur Sui in ine Crasctes Selected O VoL of rezods Im stolp U onary ve varel Figure 3 12 Summary Request Data Items Selector 3 39 Specifying Summary Data Items Data item selection is different for a summary Request than for a detail Request For those items being summarized you must specify the type of summarization for example a count in addition to the data item itself Note If you select an item and do not indicate a summary function for it the item becomes part of the group identity information Foxfire knows based on the data type of the data item being selected what type of summary functions are applicable For example if you select a character based data item only the Min and Max functions are available since you cannot calculate an Average or Sum of a character field gt To specify summary data items 1 Inthe Data Items Selector choose Sold To Dealer Name from the Available list 2 Next you will specify the items to be summarized 3 To display the count of cars sold by a particular dealer choose Count of Records in Group
198. ter conditions on report Choose OK Choose OK to return to the Request Editor Preview the report Notice how the ask at runtime and hard coded filter conditions are displayed at the top of the request Return to the Output Options dialog Choose Always Prompt for Header Preview the Request Note that now you are prompted to specify a heading when you run the request and that the heading you type in appears at the top of the first page of the output 3 64 Directing Output to a File It is often useful to send the results of a Request to a file which you can then manipulate further for additional analysis You may want to work with the results in a spreadsheet or add additional formatting with a word processor It is also useful to send a Request s output to a table then use FoxPro to perform additional processing on the records In this exercise you will send the Request s output to a file specifically a table DBF file gt To direct a Request s output to a table 1 From the Request Manager highlight Request QUARTILE and choose Output from the navigation bar 2 In the Output Selector choose Data from the left Output Type drop down list You may have to scroll down to find it An advisory message reminds you that group options will not appear in the output 3 Choose OK then Run in the Request Editor Foxfire creates the file QUARTILE DBF in your OUT subdirectory You may use Windows Explorer
199. the Tools menu from the Request Manager Then select the About Foxfire menu option then select the Map of Foxfire button 3 5 Y Fonfire Report writer See Organize Fie Et tows bdp i your Requests in Explorer like folders Acqucst Manager Fcquests Foal Fk View Twh R l be oo review Mew x Deri nay Rog tree E Ers ples of Specia Fral zY p 3 E Sar Repurls ARL eye a 3 STEW E teed re Reps a Juans four d thes a gt 4 EYSTEY 1201 299 tort snslysk by desler of EYSTEM I2 2100 sdice kepit Ye qnal r LYSIEM BBL saicomen Ful Nanc Exompic a LYSIEM BUU List of Requests Aan wras for eny edanern EYST 1 99 currently in the Requests Root folder Note that the icons on the left hand side identify the type of request Also note the other information available Requests can be sorted by any of the grid columns Sre evence Sample Jaca Set wm Figure 3 3 Request Manager The first top level window you see is the Request Manager see Figure 3 3 which presents a list of all existing Requests You can access any Request through the Request Manager The Request Editor window which appears whenever you edit or create a new request provides you with a roadmap or workbench of tools necessary to create or edit a request Request Editor vore Inventory on hand Original Request 06 29 2000 Inventory on hand Original Request Stock Make Model _ Mileage Color Trans _
200. the Edit toolbar button or right click and choose Edit Note that the Request Name and Description display in the Request Editor matches the name and description from the Request Manager Additionally note that Request Type and Output Options match the ones that appear in the Request Manager list Exarpies of pocizi Forus Haes LATS Irvertery Qepods asno CONA Requect Er S STBe ORTADI Reyi et Edui E E ff ngosom Stock Make tadel Mileage Color Trans Prr 1530 URIS REl 1412 SHIE 4 tpecd 3 The Request Editor opens Request Editor Jinv_oRG Inventory on hand Original Request 06 29 2000 Stock Make Model Inventory on hand Original Request Mileage Color Trans Price Paid Dirnum 1500 PONTIAC FIREBIRD 147123 WHITE 4 Speed 1100 535679 3 13 Each Request contains the following elements yY The name of the report Vv The description of the report vV The data items to be included in the results data items are also called fields by programmers The Data Items section in the upper portion of the Request Editor displays the eight specified data items or fields that appear in the detail area of the INV_ORG report Y Selection criteria called a filter The Filter section of the Request Editor shows that the report includes only records where the data item called Cars Date of Sale is empty i e the car is not sold and the data item called Cars Color is like
201. the number of left and right parentheses to match before allowing you to exit from the Filter Builder Using the In the List Operator When you want to compare one data item to multiple possible values use the In The List operator instead of a series of individual conditions with OR connectors 4 39 Table 4 4 Example You want a report of red blue and green cars You can build the filter condition as Cars Color Is In The List Red Blue Green Connecting Is Not Conditions Avoid connecting two Is Not conditions with an OR if possible Connecting two negative filter conditions is logically permissible but confusing If you must connect two negative filter conditions think carefully about the meanings of AND and OR connectors It is better to rephrase them as positive conditions if possible Example To select only Cars that are neither RED nor WHITE use AND not OR refer to Figure 4 4 Cars Color Is Not RED Incorrect OR Cars Color Is Not WHITE Cars Color Is Not RED Correct Cars Color Is Not WHITE Even Better Cars Color Is Not In The List RED WHITE Each record is tested against all the conditions The OR condition is incorrect because white cars will be selected by the expression COLOR Is Not RED and red cars will be selected by the expression COLOR Is Not WHITE This expression actually defeats itself by selecting cars of any color Note If there is an OR in the filter the order
202. the summarization performed on the summary Request It is just as easy to build this type of filter condition as any other Example Our summary request consists of the data item Purchased From Name This means that the result contains one row for each uniquely named dealer we ve sold a car to Currently we are showing summary sales information for every dealer who we ve ever sold a car to The business specification see page 3 38 indicates that we want to only see information for cars sold in the last three months with a total profit of more than 1 000 The following steps show you how to accomplish this task First we want to restrict which sales we count and calculate 1 Choose Filter 2 Choose Add 3 Note that the list of available filter items starts with Group Filters 4 These are filter items that are based on the summary data being shown on the report after all the calculations have been done Foxfire s Filter Builder keeps track of these special group filters and places them at the top of the filter list 5 Atruntime group filters are turned into a SQL HAVING clause 4 74 10 11 12 13 14 15 16 Filter for Request DLRSALES Cars Sold to Dealg Data tem Is Not Avg of Cars ge at Sale x r Group filters E Avg of Cars Age at Sale of Cars Days in Inventory Cars Sale Price Cars Sale Price Gross Profit Gross Profit Scroll down to the Detai
203. this option you will only get N records shown on your Request Whereas if you do not pick this option you will get the top N records in the group that you specified in the dropdown Merge with Other Query Results Union Use Merge with Other Query Results to merge the results of another Request with the current Request Essentially the results of both Requests are combined Union Requests x erge V ith Other Request Union Run Request and merge its results with the Request F Omt all duplicates in final result i Cancel Figure 4 18 Union dialog Enter the name of the Request subordinate request whose result you want to merge with the current Request master request The subordinate Request must have the same number of data items selected as in the master Request and the corresponding data items in both Requests must match in type and width When the master Request is run the records selected in the query i e the filtering process of the subordinate Request are combined with the results of the master Request in the result table Since the Requests in a merge could return some identical records you must specify whether to allow duplicate records when you set up the merge Choose Omit all duplicates in final results to prevent duplicate records from being included in the result of a merge 4 33 Filtering Tips This section offers a few tips to help you create filters Wildcard Searches When buildi
204. time you run the Request For more information see Ask at Runtime on page 4 22 Formatting You can choose one or more of these formatting options v Always Prompt for header Foxfire asks the user for a report header every time the Request is executed If disabled the user is prompted only if there are Ask at Runtime filter conditions 4 62 Y Suppress Detail Lines Suppresses the detail lines and produces only the group and summary sections of the report v Suppress Zeros in Cells Cross tab Requests only Suppresses cells whose values is zero displays as blank v Fit to Page graphical only Foxfire places all the report objects on the layout within the page boundaries This entails reducing the type size and or shortening the individual objects Note You may have to adjust the layout manually to avoid truncating data in some fields Detail Line Spacing Use this option to control the spacing for each line of detail in the Request You can set the spacing in increments of 1 10 of a line In DOS whole lines only You cannot choose a spacing of less than 1 line per detail line Suppress Repeat Values Suppresses repeat values by columns Indicate in how many columns the rightmost being the first column this feature should appear Leave it at zero if you do not want to implement this feature Column Spacing Foxfire automatically inserts one space between columns on a report With this feature you
205. tly matches those of the preceding record To omit duplicate records choose the Omit Duplicates check box Note selecting this option can significantly reduce the performance of your Request when it runs Data Item in a Filter Condition The first part of the filter condition is the data item that forms the basis of the filter condition Note Data items that are indexed have an R appearing to their right The R indicates that filters you build with this data item automatically employ FoxPro s Rushmore technology to speed up the filtering process See Chapter 7 Indexing and Rushmore Note If you change the data item in an existing filter condition you should check the other parts of the filter condition if the new data item is not of the same data type character numeric date and so on The original value entry for the filter condition will be blanked out gt To choose the filter condition s data item 1 Choose Add or Edit 2 From the Data Item list select the data item you want to use in the filter condition Is Not Operator The Is Not operator indicates whether the condition is positive Is Equal to Is After or negative Is not Equal to Is not After Note You cannot alter the Is Is Not operator when your filter condition includes a logical type data item yes no true false You can only set the value in the condition to Yes or True or No or False gt To choose the Is Not operator
206. to a Pivot Table 3 68 DLRSALES 3 39 4 81 E Edit Entire Filter 4 26 Editing add custom calculations 5 1 add lines and boxes 5 1 add text 5 3 adjust appearance of data items and text 5 1 5 3 adjust vertical spacing 5 1 adjusting the height of each band 5 3 deleting data items and text 5 3 move data items and text 5 1 5 3 move more than one item 5 3 requests 4 7 End User Work Plan 1 1 Enter Filter Values Only 4 26 envelope 4 80 E R diagram 2 3 displaying in Foxfire 2 4 for Sample database 2 3 Exactly Like 4 41 Excel 4 56 charts 4 58 pivot tables 4 59 Executive Style 3 62 Request Manager 4 8 7 2 Exit Foxfire 4 5 Exiting Foxfire 4 8 Exporting data items 4 83 request definition 4 83 request exporter 4 83 request layout definition 4 83 Expression Builder 4 20 4 23 4 24 6 3 verify expression 4 24 expressions 4 14 F Files defined 1 4 outputting to 3 65 Filter Creating an Ask at Runtime Filter 3 59 example 4 15 group filters 4 74 group filters specifying 4 74 Filter Builder 3 7 3 19 3 21 3 22 3 30 3 31 3 45 3 59 3 66 3 68 4 10 4 15 4 16 4 21 4 24 4 25 4 27 4 31 4 38 4 39 4 64 4 74 4 75 6 3 7 2 7 4 add button 4 16 adding 3 45 3 59 adding a filter condition 3 30 adding conditions 3 20 AND connector 4 34 4 37 ask at runtime 3 59 4 22 4 24 ask at runtime option 4 19 ask at
207. to confirm that the file QUARTILE DBF is now there Similarly Foxfire can create several formats of ASCII and data files Just select the type you want in the Output Type drop down lists Choose OK then Run Directing Output to a Graph Foxfire can send a Request s output directly to Microsoft Excel as a spreadsheet chart or pivot table In this exercise you are going to create a Request Foxfire automatically starts Excel then places you in Excel with the generated chart active 3 65 gt To direct a Request s output to an Excel chart 1 Create a new Request choosing Summary as the Request type Enter XLCHART as the Request name and Summary Comparison of Price Paid and Profit as the description 2 Choose Data Items and select the following data items Cars Make Sum of Cars Purchase Price Sum of Gross Profit Reminder To create an aggregate data item in a Summary Request highlight the data item in the Available list then choose the appropriate calculation button located below the Available list in this case Sum 3 Move to the Filter Builder and add the filter condition Cars Make Is In the List Honda Ford 4 Move to the Output Selector and change the Output Type to Graph Excel Chart 3D Column 5 Return to the Request Editor and run the Request If Excel is not already running Foxfire launches it with the chart displayed You can then use the regular Excel Chart tools to edit
208. to enter one when you try to save y Description Enter a description of the Request up to a 75 characters Spaces are permitted Description is optional but strongly encouraged v View Narrows the available data items and table relationships to a particular application or reporting task Views are setup in the Join Editor v Data Items Opens the Data Items Selector where you specify the data to appear in the Request For more information see Choosing Data Items on page 4 13 v Filter Opens the Filter Builder where you define the criteria for selecting which records to include in the result For more information see Filtering Requests on page 4 15 v Join Opens the Join Builder where you can override the default joins for this particular request Joins are setup in the Join Editor see System Administration Utilities on page 4 88 and tell Foxfire how to get data i e relate from more than table at a time y Sort Group Opens the Sort Group Selector where you specify how or in what order the Request will arrange records for 4 10 subtotals For more information see Sorting and Grouping Requests on page 4 41 Output Opens the Output Selector in which you specify where to send the results of a Request to a report browse table labels graph spreadsheet mail merge ASCII or stored data formats You also indicate whether the output should be directed to the screen a printer
209. uartile 4 headings ore cie and footers Make Model Mileage Color Prom ID No update Sans Serif TONGA WAGIN ooz T1100 Iri M Font HONDA ACCORD 425187 1 07500 12640 RIK H NL PRELUDE yas RED 1 WSW UTSUII LME LONDA ce cov 990 02 04425C GM HND A CMC 12 992 910 03 024C Luc HONDA ACCORD 915 BLUE 800 02 o70992 RMK HONE CRX 142872 RED 775 02 00e GM H NDA ACCORD 85751 GREEK 660 03 068101 LMC Totalsfor Quartile 4 mwa 93830 7 305 00 Figure 3 16 Presentation Style Example Executive Style Same features as Presentation 3 Velie Lsi but uses a i S lt gt E o Serif font WASIN EA 1 15000 a53 M APT 129473 sirnm lye PRK PRELUDE A XSL somo All IMZ crc AE 900 e5 GM craic 12tesz 91000 a0 LMC ADDED am SWE mo oN PME cRX HBE 3I 7s sy LODGED sy REF q 62000 a LMC L ay 99830 7 305 00 Figure 3 17 Executive Style Example 3 62 Ledger Style Rearlele I Little bolding Muael Vable Uses a small aig monospaced PRELUDE font to fit more one information on ALLIR standard size Che ACCILD paper alals Var Rearkele ACCELD ACCELD rere rere ic ACCELE ACCILE rere Talals Far Rearlele Character Style Reauest QUARTILE Quartile rank Cprofit gt Page 1 No Quartile 1 formatting Mono jake Model spaced font HONDA WAGEN HONDA ACCORD HONDA PRELUDE HONDA CIVIC HONDA CTUTE HONDA ACCORD HONDA CRER HONDA ACCORD Totals for Quartile Other Layout Options ALLIED
210. unved BLL FER ur MAS MERSECES MPR IPY Figure 4 30 Cross tab request example output ft 400 99 2 200 tnm EET ama Y je S iE FPR oe te EN ASAE 9 200 25 em Pinker hyr wrar Waai 153 1w ELI Vas Pav TRI fim y uw 38273 on 8 DIX gt er Un yawn 3 29344 3 tas ynm HDI FAM fram 3 e000 AA Bas 490 00 PF ls XL tL 3 mam E FNRI Fo ALU arer FM 31 REET 31x50 J FII 31 EB a Tom 69 29 ya HEM faa y x20 eI FEM The following characteristics distinguish cross tab requests from other requests v The Request Editor differs in appearance You work in a grid that visually resembles the final result As you fill in the grid you specify which data items constitute the horizontal and vertical components of the grid v You specify the calculation method used to produce the numerical data that fills the grid 4 77 Average by Make for all years reported Average by Year for all Makes reported v It is possible to summarize the columns and the rows using the same calculation method chosen for the cross tab values To summarize columns mark the check box Col Summaries To summarize the rows mark the check box Row Summaries Note When computing Row or Column Summaries using the Average aggregating function Foxfire calculates the true average for the Row and column data groups rather than the averages of averages which could be mathematically misleading Request AVPROFT f
211. ut Style Presertation Ru ravie Cuuil Stuy EQ Save Save as Corel 14 Choose Preview to display the report Note the number of Groups selected at the bottom of the report 15 Choose the Open Door 16 The Output Choices window opens 17 Choose Close 18 The Request Editor opens 19 Choose Close to return to the Request Manager Creating a Cross Tab Request A cross tab report is a matrix that shows a relationship between two variables for all records meeting the filter criteria The results appear at the intersection of each row and column Business Specification 3 41 We are trying to decide what kinds of cars to purchase for resale The decision on what kind cars to purchase will be based on the average profit we made for each year and make of car we sold We are only interested in cars made between 1992 and 1999 Car makes should be displayed vertically down the rows while year of manufacture should be displayed horizontally across the columns gt To create a cross tab Request 1 Choose New 2 The New Request Type and Style dialog opens 3 Choose Cross Tab and then OK 4 The Request Editor for cross tabs opens New Request Type and Style Es Examale of Sross Tab Report Cross Tab Sum of Gross Profit for Salesman Name by Cars Month Sold Page 1otstrp 1 Salesman Name Sum May 83 Jun 89 JOSEPH COLLINE 37755 16351 20254 i MARK NONGF 494 41 18747 PARI Be MARK HARR SON 624
212. value is displayed followed by each record in that group The selected data items are displayed in columns under each data item header At the end of the group the group footer is displayed including any calculated group items This pattern is repeated for each group on the report Note that even though the group information is shown at the top and bottom of the group it also appears with the other selected data items Column Headings at N VA the top of the page page rnin Starke ake Minded Aileage Cninr Price Paid Trans FPN R 1 PEPEE EEE AN EEEE AAA AA OEE OER AESI EENE EI ET E POEA AATA E O E SU AE IA IAAT 246 AUDI S 1021 SILVER 150 3 Speec MLM 2016 5 3233 BLUE 150 5 Spee ue 2307 hr 14123127 OPC p 200 3 Speec MLM 2309 Er 7H p 35 000 Actciratic 40 Totalsfor Make AUDI wg AM DE 10 000 Moke BNW pi EE Data associated with each 2251 m oo SAN t d D t It di d hvs scan air MaN N selected Data Item displayed in columns Makr RIECK 2239 BIIK CoN URRY 3995 3 680 3 Speec ILE Form Output Format In this output type only the page numbers are displayed at the top of each report No group information is displayed The selected data item column headings are displayed to the left of each data item and the data for each record that appears on the report is displayed vertically on the page with a single blank line between each record Note that all selected data items appear vertically down the page even th
213. ware that Foxfire will prevent more than one user from editing a particular Request at the same time gt To delete a Request 1 Highlight the Request you want to delete 2 Choose Delete 3 A dialog box asks Are you sure you want to delete Request 4 7 lt name gt 4 Choose Yes to delete the Request choose No to retain the Request Warning Deleting a Request means permanently discarding the Request Once deleted it cannot be recalled Before deleting a Request make sure no one will need the Request any longer gt To quit Foxfire Choose Exit or pull down the Foxfire menu and choose Exit Comments You can attach comments or notes to a Request so a user can view them when working with the Request gt To view or edit Request Comments 1 In the Request Manager highlight the Request SAMPLE1 Inventory on Hand 2 Press F8 3 The Comment Editor opens Comments for Request SAMPLE1 OL x Figure 4 6 Comment Editor 4 Type This Request includes only unsold cars 5 Press Ctrl W or click on any Foxfire window outside the Comment Editor to save your changes and exit To exit without saving your changes or to exit after viewing press Esc Request Types See a description of the different kinds of requests on page 3 63 Layout Styles You can also choose one of several predefined layout styles for Request results Your choice will affect yY Fonts used v Placement of information on the

End User Guide

Contents

Download Pdf Manuals

Related Search

Related Contents