The URL can be used to link to this page

Home My WebLink About9 Crystal ReportsSTUDENT MANUAL -7 ~ elementk COURSEWARE Crystal Reports 9.0 Level 1 JEANNINE P. PRAY Crystal Reports 9.0 Level 1 Jeannine P. Pray ii Crystal Reports 9.0: Level 1 Part Number: 085513 Cou rse Edrtion : 1.2 ACKNOWLEDGMENTS ' Project Team Sr. Curriculum Developer and Technical Writer: Jeannine P. Pray • Development Assistance : Cindy Kre do and Sue Reber • Content Manager : Cheryl Russo • Copy Editor : Peter Ba uer • Technical Editor : Eli za beth M Swank • Graphic Print Designer : lsolina Sa lga do • Project Technical Specialist: Mich ae l Toscano NOTICES DISCLAIMER : While Elemenl K Coursewaie LLC lakes care lo en&ll e Ille accu racy.and QUalily ol lhese malerials. we cannol ~aran lee lheir accuracy, and all malerials are provided wi lhOUI any warranly whalsoever, including, bul nol limiled lo . Ille imp lied warranlies ol merchanlabi lily or lil ness 101 a parlicu lar puipose . The n<Vne used in Ille dala liles lor lhis course is lhal ol a liclilious company. Any resemblance lo cunenl or lulure companies is purely coincidenlal. We do nol believe we have used anyone's name in crealing lhi s cou rse. bu l ii we have . please nolily us and we will change Ille name in Ille nexl revision ol lhe course. Elemenl K is an independenl provider ol inleg raled !r aining solulions lor individuals. businesses . educa li onal inslilulions. and governmenl agencies . Use ol sc reenshOls. phOlogr~hs ol anolher enli ly's produclS , or anolher en li ly's producl name 01 service in lhis book is lor ed ilorial purposes only. No such use should be conslrued lo imply sponSOfship or endorsemenl ol Ille book by, nor any allilk11ion ol such enlily wilh, Elemenl K. TRAD EMARK NOTICES Eleinenl Kand Ille Element K logo are trademarkS of Elemenl K LLC . Cryslal Reporls is a registered lra demark of Cryslal Decisions , Inc . All olher producl n<Vnes and services used lhroughOUI lhis book may be common law or regislered lrademar kS of lheir respec live proprielors . Copyrighl © 2003 Elemenl K Conlenl LLC . All righls reserved . Screenshols used for illuslralive purposes are the properly of the soflware proprietor. This publication , or any parl lhereol , may not be reproduced or lransrnilled in any lorm 01 by any means , electronic or mechanical , including phOlocopying. recording , stOlage in an in lormalion retrieval system , 01 otl'e!wise. wilhOul express wiillen permission of Elemenl K, 500 Canal View Bou levard, Rochesler, NY 14023. (585) 24()-7500, (800) 434-3466 . Elernenl K Courseware LLC's Wor ld Wide Web sile is localed al www.el ementkc ou rseware.com . This book conveys no righls in Ille sollware or olher producls about which ii was wrillen ; all use or licensi ng of such soflwaie or olher pr oducls is the responsibilily of Ille user according lo lerms and condilions of Ille owner . Do not make illegal copies of bookS or sollwaie. II you believe lhal lhis book, related malerials , or any other Element K malerialS are being reproduced or transmitted withOul permission . please call 1-B00-4 7fJ.7788. Your comments are important to us. Please contact us at Element K Press LLC , 1-800-478-n88, 500 Canal View Boulevard , Rochester, NY 14623, Attention : Product Planning , or through our Web site at http://support.elementkcourseware.com. Crystal Reports 9.0: Level 1 KNOWLEDGE by Element K Courseware Knowledge2 , available exclusively from Element K Courseware, gives you two great ways to learn using our best-in-class content. This courseware includes a companion online ID. Use your online ID to reinforce what you've learned in the classroom, prepare for certification tests, or as a reference guide. It's easy, and available to you anytime, 24x7, at www.elementk.com. To use your Knowledge 2 online ID, follow these five easy steps: 1. Log on to www.elemen t k.com 2. Click on Student Enrollment 3. Enter the following Enrollment Key 6266-CREP9L 1-0163 4. Choose a user name and password, complete personal information, and then click Submit. 5. Your profile has been set up successfully. You may now proceed to Login to Element K. Your Knowledge 2 online ID is valid for 90-days from init ial log on . CRYSTAL REPORTS 9. 0: LEVEL 1 LESSON 1 : CREATING A LIST REPORT A. Set Default Report Settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 Plan a Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 Crystal Reports Software . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 Crystal Environment. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 Default Settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 B. Build the Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 Data Sources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 Table Relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 C. Preview the Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 Navigate Using the Preview Tab................................. 24 D. Add a Report Title. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 Text Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 Re -size Fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 Format Fields. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 E. Align Fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30 LESSON 2: DISPLAYING DATA IN A REPORT A. Find Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34 B. Sort Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36 Natural Order . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37 C. Filter Data by Single Criteria . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39 Filter .. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39 Comparison Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40 Contents CONTENTS iii CONTENTS iv LESSON 3: GROUPING REPORT DATA A. Insert a Group . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46 What is a Group? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46 B. Add Summaries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50 Summary Operations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50 C. Change Group Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53 D. Add an Additional Group . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56 E. Format a Group Header . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60 F. Filter by Group.................................................. 62 What is a Formula? .................... ·. . . . . . . . . . . . . . . . . . . . . . . . 62 G. Create a Top N Sort Group . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65 What is Top N? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65 LESSON 4: BUILDING FORMULAS A. Write a Formula . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70 Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70 Formula Workshop . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71 B. Edit a Formula. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78 C. Group by Formula . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80 D. Delete a Formula . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83 E. Build a Filter by Multiple Criteria . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85 F. Modify a Filter with Multiple Criteria to Create an OR Condition . . . . . . . . . 88 G. Create a Parameter Field. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90 What is a Parameter Field? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90 H. Write a Formula that Incorporates Null Fields . . . . . . . . . . . . . . . . . . . . . . . . 95 Nulls ........................................................ 96 LESSON 5: FORMATTING REPORTS A. Remove White Space . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102 B. Insert Page Header/Footer Data .................................. 105 Special Fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . l 05 Crystal Reports 9.0: Level 1 C. Add Borders and Lines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107 D. Change the Background Color ................................... 110 E. Change the Margins ............................................ 112 LESSON 6: ENHANCING REPORTS A. Create a Watermark . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116 Re asons to Cre ate Sections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116 B. Add Bulleted Lists . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122 O b ject Link ing and Embedding ................................. 122 C. Modify Format Based on Doto Value ............................... 125 D. Conditionally Suppress Doto ..................................... 127 Boolean v e rsus Attribute Formatting ............................. 127 E. Insert Hyperlinks ................................................ 131 What is a Hy p e rlink? ........................................... 131 F. Hide Report Objects ............................................. 134 LESSON 7: CREATING AND MODIFYING P1E CHARTS A. Create a Pie Chart with a Drill-down . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138 Chart Typ e s .................................................. 138 Chart Layouts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139 Chart Exp e rt . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 39 Chart Elements ............................................... 140 B. Change the Chart Title. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143 C. Format a Chart. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145 D. Present a Chart by Group ........................................ 148 LESSON 8: DISTRIBUTING DATA A. Export to Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154 B. Export to on Access Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 158 C. Create a Report Definition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161 Report Definition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .. . . . . . . . . . . . . 16 1 Contents CONTENTS v CONTENTS D. Create Mailing Labels ........................................... 163 Report Creation Wizard ........................................ 163 APPENDIX A: SETTING FILE LOCATIONS LESSON LABS ...•.................................... ~ ..•.......... 173 SOLUTIONS ..•.•••.••••..•.•••••••••••.........••...•.•••••••...•.•. 183 GLOSSARY ....................................•.........•.•......•• 195 INDEX ••.••..•..••••••••••••••••••••••••••••.••••..•...•••••.••..••• 197 vi Crystal Reports 9.0: Level 1 ABOUT THIS COURSE Crystal Reports 9.0: Level I is th e primary recommended course yo u should take if yo ur job responsibilities require yo u to obtain output from datab ases. It is also the first co ur se in th e Crystal Reports 9.0 series. In thi s co urse , yo u will build bas ic li st and gro up reports that work with almost any database . Data, also known as information, is a great thing. H owever, if there 's too mu ch of it or it 's presented in a mann er th at has no meaning to yo u, it has little or no va lu e . B y usin g Crystal Repo1t s, yo u can build reports that extract and s ummarize th e data yo u need and present the data so that it is meaningful to you and others who view tho se reports . Course Description Target Student This co urse is designed for a person who ne eds output fro m a database. In some cases, data- base pro grams hav e limited reporting tools, and/or th ey may not have access to tho se tools. They may or may not have progranuning an d/or SQL expe1ie nce. Course Prerequisites Exposure to a Windows operating sys tem would be help ful and a basic und erstandin g of data- base concepts is necessary. How to Use This Book As a Learning Guide Each lesson covers one broad topi c or set of related topics. Le ssons are ananged in order of increasing proficiency wit h Crystal Reports 9.0; ski ll s yo u ac quire in one le sso n are use d and developed in subs eq uen t lessons. For this rea son , yo u should work throu gh the lessons in seq uence . Introduction INTRODUCTION vii I NTRODUCTION vi ii We organized each lesson into results-oriented topics. Topics i nclude all the relevant and sup- pmting information you need to master Cry stal Reports 9.0, and activities allow you to apply this information to practical hands-on examples. You get to try out each new skill on a specially prepared sample fi le. This saves you typing time and allows you to concentrate on the ski ll at hand. Through the use of sample files , hands-on activities, illustrations that give you feedback at crucial steps, and supporting back- ground information, this book provides you with the foundation and structure to learn Crystal Reports 9.0 quickly and easi ly. As a Review Tool Any method of instruction is on ly as effective as the time and effm t you are willing to invest in it. In addition , some of the information that you learn in class ay not be impo1tant to you immediately, but it may become important later o n. For this reason, we encourage you to spend some time reviewing the topics and activities after the co urse . For additional challenge when reviewing activ ities, try the What You Do column before looking at the How You Do It column. As a Reference The organization and layo ut of the book make it easy to use as a learning tool a nd as an after- class reference. You can use this book as a first source for definitio ns of terms, background information on given topics, and summaries of procedures. Course Objectives In this course, you will bui ld basic list and group reports that work with almost any database. You will : • • • • • • • • create a list report by using data from an existing database . locate and present data in a specified order . create groups to sumn1arize report data . build formulas to add non-database data to a report and disp lay data differently . format repo1ts . enhance reports by adding and modifying elements in a rep01t. create sing le data series c harts . distrib ute repmt data to other users . Crystal Rep orts 9.0: Level 1 Course Requirements Hardware • A Pentium or higher processor. • 60 MB of hard-di sk space (full installation 235 MB). • 32 MB of RAM (64 MB RAM recommended ). • Either a local CD-ROM drive , DVD d1ive, or access to a networked CD-ROM drive. Software • Microsoft Windows 98 , Windows ME, Windows 2000, Windows NT (version 4.0 or higher), or Window s XP. • A Cu stom installation of Crystal Repo1ts 9 .0. • A default installation of Access 2000 if you plan to complete the activity, "Exp01ting to an Access Database." • A default install ation of Excel 2000 if you plan to complete the activity, "Exporting to Excel." • A default installation of Word 2000 if you plan to complete the activity, "Adding Bulleted Lists." • A valid emai l address if you plan to complete the practice activity, "Enhancing a Report." Class Setup ., This course was written using Crystal Reports 9.0 Professional Edition and Microsoft Windo ws 2000. 1. Install Windows 2000 Profess ional on a newly formatted hard drive using the Typical option. 2. Install a Custom copy of Crystal Reports 9 .0. On the Select Features page of the Crystal Reports 9 Setup Wizard , click the Export Support drop-down arrow and choose Entire Feature Will B e Installed On Local Hard Drive. 3. If necessary, reboot your computer. 4. Install a default in stallation copy of Microsoft Ac cess 2000, Microsoft E xce l 2000, and Microsoft Word 2000. 5. On the course CD-ROM , open the 085 _513 folder. Run th e 085513dd .exe self-extractin g file located within . This will in stall a folder named Student on your C d1ive. This folder contains the data files you will need to complete this co urse. 6. Copy the X treme.mdb database file from C :\Prograrn Files\Crystal D ecisio ns\Crystal R ep01ts 9\Samples\En\Databases to the C:\Student folder. Introduction INTRODUCTION ix INTRODUCTION x 7. This is an optional step. If data file s are copied to a different folder than instrncted in the course setup, users will receive an en-or message when they attempt to refresh data . The en-or can be con-ected by : (1) Re-installing the data files to th e appropriate folder as instructed in the course setup; or (2) Using the Database-7Set Datasource Location option on the Crystal Rep01ts menu bar to change the location of the database files to match the installed location . To use the Set Datasource Location option: a. Open any Crystal Reports data file li sted in the C:\Student folder. b. Choose Database-7Set Datasource Location. The Set Dat asource Location dialog box is displayed. c. In the Cunent Data Source list box , select the database or table you want to replace. d. In the Replace With list box, select the databa se or table you want to use. e. Click Update . f. Repeat steps c through e as needed. g. Click Close to c lo se the Set Dataso urce Location dialog box. h. Repeat steps a through g for each Crystal Reports class data file. 8. In Windows Explorer, choose Tools-7Folder Opti ons to display the Folder Options dialog box. On the View tab, verify that Hide File Extensions For Known File Types is unchecked. List of Additional Files Printed with each activity is a list of files students open to complete that activity. Many activi- tie s also require additional files that students do not open, but are needed to support the file(s) students are working with. These s upp orting files are included with the student data files on the course CD-ROM or data disk. Do not delete these files. Crystal Reports 9.0: Level 1 LESSON 1 Creating a List Report Lesson Objectives: In thi s lesso n, you will create a li st re port by u sin g d ata from an ex isting database . You will: • Set defa ult report se ttin gs. • Bui ld a list report. • Prev iew the report. • In se rt a re port titl e. • Ali g n fie ld s in a re port. Les so n 1: Creatin g a List Rep ort LESSON 1 Lesson Time 1 hour(s), 30 minutes to 2 ho ur(s) 1 LESSON 1 2 Introduction Many people store information in a database. At some point, they 'll need to get that infonna- tion out of the databa se so that they can create reports that include some of that information. In this lesson , you will create a list report from information in a database. Suppose you have a database that contains a large amount of data. You need to pull out and summarize a portion of that data so that you can show it to a co-worker. It would be very helpful if the information could be displayed in co lumns. T he simplest way to acces s th at information is to create a list report. By using Crystal Reports , you can select only the data you need from the database and build a re port that summarizes and presents that data in a meaningful wa y. TOPIC A Set Default Report Settings You have a database of information. You need to get some of that information out of the data- base so that you can create a repo1t. You want the report to include certain formatting. Therefore, you might want to change the default behavior of Crystal Reports. In this topic, you will set default report settings. You need to create a new report in C1ystal. You know exactly what you want the repmt to include . You also know that all programs have certain default settings, so you want to make sure that those default settings are appropriate for the repmts that you 're going to create . By setting default repmt settings before you build your repo1t, you 'll save time by minimizing the amount of changes you need to make to newly created reports. It is more efficient to set the defaults first than it is to go back after you have already created a report and change the settings. Plan a Report Before you begin creating a report, you need to determine what information your report is going to include . If you don 't make plans , you might end up with a report that you can 't use or one that you 'll need to spend a lot of time modifying to fit your needs. Two elements you should consider when creating a report include the content of the report and developing a prototype on paper. When deciding on the content of a report , ask yourself the following question s : • What is the purpose of the report? • • • • • • • • Who is going to use the report ? l s your report going to include a title ? If so, what will it be? Do you need to include information in the header and footer? If so , what information? What data will you use in the report ? What data will be included in the body of the report ? Does the data ex ist or do you need to create it? What types of data will you include in the repmt? Will the data need to be grouped? Crystal Reports 9.0: Level 1 • Will yo u need to sort th e da ta? If so, how ? • Will th e da ta be limited to s pec ifi c record s or group s? • Will th e da ta nee d to be summari ze d ? • Will any of th e d ata need to be hi g hli g ht ed ? If so , h ow? • In wh a t order will th e data in th e re po1t be printed ? · w0he>-~t)~ w~pp~~t;:~re~i +~ fltp0tt+? Crystal Reports Software C rys tal Re p01ts i s a software a ppli cati o n th a t all ow s you to use da ta bas e informati on to create re ports that analyze a nd interpret importa nt in fo rmati o n. The re are a numbe r of di ffe re nt ty pes of re p01t s yo u can create . Some of th ose re p01t s are o utlined in th e fo llo win g ta bl e . Report Type List Gro up ed S ubre port Cross-Tab Drill -Dow n Cryst •I Envire nment l escription Di spl ays databa se fie ld s in co lumn s . Di splays data hi erarc hi call y so yo u can summarize it. A report within an oth er re port . Di spl ays data two-dim ension all y with column and row headin gs and summari ze d d ata as th e intersec- tion , a ll ow in g yo u to anal yze th e relati o nship of two or more fi e ld s. Di spl ays summari ze d data onl y. Th e detai led d ata i s hidde n and can be viewed by drilling dow n. Before yo u create a re p ort in Crys ta l, you 'll need to beco me fa miliar with th e e nvironm e nt. Table 1-1 lists some of th e c ompone nts of Crys tal R e ports and th e ir descriptions . Table 1-1: Toolbar Components of Crystal Reports Component me nu bar Stand ard too lb ar F01m attin g toolb ar In se rt To ols too lb ar Ex pert Too ls too lbar Lesson 1: Creating a List Report Description Di spl ays a li st of command s. Is di spl ayed by defa ult , and co ntain s butto ns th at yo u can use to access common co mm and s. Is di spl ayed by default , and co ntai ns butto ns th at yo u can use to access commo n fo rm attin g co mman d s. Is di splayed by default , and co nt ai ns butto ns th at yo u can use to access advan ced , report-enh ancin g comm and s. l s di spl ayed by default, and co ntain s butto ns that yo u can use to access man y advan ced ex pert s . LESS •N 1 fite.,hAM~ . -f.tl(, loc~-n~ 3 LESSON 1 4 Component status bar Description A hori zo ntal bar at the botto m of th e application window that provides information regardin g th e current state of the report or se lected object. Once yo u ope n a report, the Crystal Report s environment changes. Table 1-2 lists some of the component s you'll see when a repo1t is open . Table 1-2: Components of Crystal Reports when a Report is Open Component rul er g uid e lin e field field label Description Provid es a visual refe rence for moving and resizin g o bj ects. A non -printin g line that yo u can plac e on a report to assist yo u in placin g objects. A category of infom1ation . A label th at id entifie s a category of infom1ation . The Design tab is th e tab on which you perform mo st of th e work when creating and modify- ing a repo1t . It contains data represe ntation s, not the actual repo1t data , so yo u can perform all types of ta sk s without ty ing up the computer or network resources needed to gather th e data. When yo u create a rep01t, the Design tab is di s played with five default sections. Each sec tion name is dis played only once. Those sections are outlined in the followin g table. Section Report Header Pa ge Head er Detail s Repo rt Footer Page Footer Function Di splays only o nce at th e begi nnin g of a report and can contain items suc h as a title page , lo go, introductory information , or charts or cross-tabs that apply to th e entire report. Displays at th e top of every page and can contain items such as field titl es , print date/time, and the name of th e docum ent. Di spla ys o nce per record and co ntain s th e bulk of the re port data . Di spla ys o nl y once at th e end of th e report and can contain items such as grand total s, charts, or cross-tabs th at apply to th e entire re port . Di splays at th e bottom of eve ry pa ge and can con- tain items suc h as pa ge numbers and re port nam es. Figure 1-1 di splays an example of a report that is di s pl ayed on th e Design tab. Crystal Reports 9.0: Level 1 s -(Speclflc Orderupt] ~~ ~ ~$ ' El Fie Edi. v-1nser1 '""'"' oat.i.... R"""' """"""' lido -Menu bar D ~ · . c.~ --lllil @ CJI lti jmi% :::J ~? --Standard toolbar fl l ') !.!.i ... " s > .. -Formatting toolbar •b 1s :r 1111•l3D ~l'fJ iz D11 ------Insert Tools toolbar ,,. ------------Expert Too ls toolbar JI; •• . ·• .. ·o · ·o .. ·o . ·• .. ·o·. ·•. l=il'lti l~h~I C ustomer Orders ·~ Aa:runl t m::t.orrer~ .. 0 11:1 ... 11 broa·oa< b n:J8' Arroll'Jt ustoroer 10 Customer Name Order ID Order Dal· )rder Arnow-ii . ; Field ·status bar I-.. ... Records : 2"193 ___ ---f For Help, press Fl Figure 1-1: A report d;splayed on the Des;gn tab. To turn the fields in the Design tab into a final repo1t, you simply add the data. You can add the data by previewing the report. When you preview the report , the actual data from the data- base is displayed on the Preview tab. Crystal u ses the saved data until you refresh th e report or add a new field, which requires Crystal to retiieve new data. On the Preview tab , the section names are displayed each time a section is di splayed. Unlike the Design tab , the section names are abbreviated, such as RH in stead of Report Header. Fig- ure 1-2 displays an example of a repo1t that is di s played on the Preview tab. The user can contro l how section name s are displayed by choosing File__,Option s and se lecting the desired options on the Layout tab. c tW\ v e.-n +11rY\ s- ~ t ttr to 8 lo~f.feCtA.Se Lesson 1: Creating a List Report LESS •N 1 5 LESSON 1 6 D°'9l I Pie'"°"' j 7110/2002 7.58 x '~ ~ 1ot1+ • •I • Specfic Ordeit. ED· .. I • ' ' 1 ' ' ' I ' ' ' 2 ' ' ' I • ':"°, 3 • '__:__ .. 4 . · I • ' · 5 ' . . e . ... 7 . ·C _I:] i.!. RH C ustomer Orders PH·- Accrunl # CuSlonerN:i:rne Orcl <r# Orda·Da1e Ortla"Aml tnt l_D ---1 C1tyCyd!sl s 1 1212196 $41 90 &__ 1 C1tyCychsl s 1,033 1218196 $3,520 .30 D I C1tyCyd!sts 1,04 1 12111196 $764 .85 ,,o I C11yCychsts 1,092 12n4196 $42 .00 D 1 City Cyclists 1.143 1/6197 $62 33 0 -I C1tyCyd!sts 1.246 1/30/97 $3.884 25 'o -I C1tyCychsl.s 1.296 2116/97 $6.682 98 ~ N I C1tyCychsls 1.366 2127196 $764 85 - I C1tyCydists 1.337 311/97 $1.5 1535 I C1tyCyclists 1.7 17 6/14/97 $70 50 I C1tyCychsts 1.763 6124197 $2.378 35 ,o I C1tyC'ldists 1.952 8/0197 $119 43 D ~1 1 C1 tyCychsls 2,05-1 9/1197 $4,07 8.95 jo--1 C1tyCyd!sts 2,142 9n5/97 $46 .50 '0 -1 C1tyCychsls 2,167 9/30/97 $75 .80 ji:i 1 C1tyCychsl s 2,277 1om197 $1 22 .65 ID -,. 1 C1tyC ychsls 2,337 lln/97 $6 8 00 IL 1 C1 tyCyd!sts 2.40 2 11122197 $1 85 .20 1 C1tyCychsls 2,528 12124197 $13 6.47 I C1tyCychsts 2.640 l/l6/98 $2.939 35 ,o 1 C1tyCyclisls 2.659 1/30/93 $659 70 I ~ 1 C1tyCychsts 2.682 21219 8 $931 05 1 C1tyCydists 2.637 214/9 8 $27 00 D 1 C1tyCydists 2.772 2128193 $2.294 55 ..:. ,, I .!J •I J .t Figure 1-2: A report displayed in the Preview tab. Each database contains fields. A field is a category of information. Each field ha s a de signated data type. Formatting options will differ based on the data type. You can use the fo ll owing data ty pe s in Cry stal Report s. Data Type Number String Date D ate Time Currency Boolean Description Can co ntain o nl y numb ers , a period for a dec imal point, or a minu s sign. Can contain a combin ation of characters, includin g tex t, numbers, and sy mbol s. Can contain a date on ly. Contain s a date and tim e, but th e field can be for- matted to di splay date, tim e, or a com binati o n of both. Can contain numbers and avoids rounding errors, unlike the Num ber data type. Can contain data that can ha ve a tru e or false value. Crystal Reports 9.0 : Level 1 ACTIVITY 1 -1 Getting Familiar with Crystal Reports Data Files: • Orders .rpt Setup: The Windows desktop is disp layed . Scenario: Yo u recently install ed Crystal Reports software so th at yo u can create re ports . Yo u want to begin creating th ose reports as soo n as possi ble . H owever, yo u 've never used the software. Before yo u can create any repo rt s, yo u need to become fa mili ar w ith th e compo nent s of the Crystal Reports enviro nme nt. What You Do 1. Open the Orders .rpt report in Crys- tal Reports, and maximize the application. How You Do It a. From the Start menu , choose Programs ~ Crystal Reports 9. The Welcome To Crystal Reports dialog box is displayed . The Open An Existing Report option is selected . b. Click OK to display the Open dialog box. c. If necessary , navigate to the Student folder on your hard drive. d . From the list box , select Orders.rpt. e. Click Open to open the report. f . Click Maximize to maximize the applica - tion window . LESSON 1 Lesson 1: Creating a Lis t Report 1 LESSON 1 8 2. Using the ToolTips, Table 1-1, and the following graphic as a guide, identify some of the components of Crystal Reports. Report Footer section Formatting toolbar status bar field D Page Header section ruler Insert Tools toolbar menu bar Details sectio n Standard toolbar Page Footer section field label Report Header section Expert Tools toolbar guideline Design tab El Fie Edi ·-...... fumot °"""'"' ·---...., --E o ca:. a~® v -rn:i 111.· ot "fiim"3 11 --B :::ir-::i ~ II "' V, • • --C •b llil I: .m • l3D ~ ~ Al ~ " --0 "cl!!:;C1C~!:! !'I ,. --H 2!l -J -"l~I • • p 0 .. , . ~, ... 2 .. , . . , ... , . " .•..• , .. 0 • . . . • . , .,. , . . . -L =:=i ===~-u =st=o=n=•e_r_O==rd_e=r=s=#=·N~=====-~======~=======_=_=_=~-~~-_,J o .. .u Aepoit Foott11 Custorr.er ID Custoner l·~ Ordtr Ir• 'ome-O<i.e brd~Amnn' A , tomer ID l..us omc:r ne er. l Order Dale rdcr Amoun -K F ~================================================-M pnn t Dale : G I --~~~-1-----------~~~~"'='"=""~~ 3. What information can you gather by placing the mouse pointer over the Customer ID field label? What information can you gather by placing the mouse pointer over the Customer ID field? Crys tal Reports 9. 0: Level 1 4. On the Standard toolbar, what is displayed next to the Design tab and in the report when you click the Print Preview button IC9. I? 5. What data is contained in the Order Date field? The data type of the Order Date field is ---- The format of the data in the Order Date field is: a) 01 /01 /02 12:00AM. b) 1 /1/0212:00:00AM. c) 01 /01/200212:00:00. d) 1I112002 12:00:00AM. Select the Design tab to return to Design view. 6. What data is contained in the Page Footer section? The data type of the Print Date field is The format of the data in the Print Date field is: a) 01 /01 /2002 . b) 01 /01 /02. c) 1/1/2002. d) 1 /1 /02 . ., On the Preview tab , the Page Footer section is labeled PF. 7. Close the report without saving changes. Default Settings a. Click the Close button ~to close the report only . A message box is displayed prompting you to save the report. b . Click No to close the Orders .rpt report without saving . Like other software applications, Crystal Reports in stall s with certain default setting s . There are two type s of settings: those that affect the behavior of th e current report only and tho se that affect the behavior of all new rep01ts. You can leave the default settin gs unchanged , or yo u can change the ones you need to. Some of the settings you might want to change are out- lined in the following table. Lesson 1: Creating a List Report LESSON 1 9 LESSON 1 Settings Fi e ld Formats Set D ata Source D efault s Funct ion Control s th e fonnatting for all strin g, number, currency, date, time , date/time , and bool ea n fi eld s. Controls the fold er location of th e data so urces. De fau lt Specific to eac h fi eld Un s pecifi ed Convert NULL Fi e ld Value to Defa ult Con ve rt s any null field va lu es to Off th e databa se default value. Defa ult Fonts Control s th e font for all fi eld s in Aria l 10 a re port . Di s play Group Trees Creates a group tree on th e left On side of a grouped report . Save Data With Repo rt Automatically saves data with On th e report. Di scard Sav ed Data Wh en Load -Remove existing data from a Off in g Reports report when opening (recom- mended when data is pa ssword protected ). For more information on these settings or any other report or app lication se tting s, refer to Crystal Reports Online Help. How to Set Default Report Settings Procedure Reference: Format Any Field Type To change the formatting of String , Number, CmTency, Date, Time , Dateffime, and Boo lean field s: 1. Choose File-?Options to display the Option s dialog box. 2. Select the Field s tab . 3. On the Fields tab in the Field Format box , click the button that contains the name of the field for which yo u want to change the settings. The Format Editor dialog box for that field is di s played . 4. Make the appropriate changes to the default settings. Some of the changes you can make include: • Horizontal alignment • Number style • For number field s, tum off the Allow Field Clipping option to avoid number trun cation • Can Grow (String) 5. Click OK to clo se the Format Editor and return to the Fields tab of the Option s dialog box . 6. Click OK to close the Option s dialog box. 10 Crystal Reports 9.0: Level 1 Procedure Reference: Change Report-level Defaults If you want to affect the behavior of the current report only, you can change Report Options. To change rep01t-level default s: 1 . Choose File-7Report Options to display the Report Options dialog box. 2. Make the appropriate changes to the default repo1t se ttings. Some of the changes you can make include: • Convert databa se NULL field value to default • Save data wit h report • Discard saved data w hen loading reports • Show all headers on drill-down • Suppres s printing if no records selected 3. Click OK to close the Report Options dialog box. Procedure Reference: Change Fonts for Any Field Type To change the fonts for any field in your report: 1 . Choose File-70ptions to display th e Options dialog box. 2. Select the Fonts tab. 3. In the Default Fonts box, click the button that contains the type of field(s) for which you want to change the font. The Font dialog box is displayed . 4. Make the appropriate changes to th e default font settings for fields, summary fields, group name fields, text objects, field titles , or chaits. 5. Click OK to close the Font dialog box and return to the Fonts tab of the Options dialog box . 6. Click OK to close the Options dialog box. 'f The changes yo u made to the default settings will now affect all future reports you cre ate. 'f To ch ange the behavio r of the current report only, choose File ~Report Options. Lesson 1: Creating a List Report LESSON 1 11 LESSON 1 12 ACTIVITY 1 -2 Modifying Default Report Settings Setup : No files are open in Crystal Repo1t s. Scenario: Now that yo u 're familiar with the Crystal Reports environment, yo u 're ready to create your first report. You 've planned it out, and you know how you want it to look. You know that Crys tal Report s, like other programs, has ce1tain defau lt settings. T herefore, you need to make s ure that the default settings that will affect your reports are se t as you need them to be to meet yo ur reportin g need s and save you time. • You want all the D ate and Dateffirne field s to di s play with the sa me format. That fo1mat should be 3/1/99. • If number values are too large for their field s, they will be repre se nted by a suing of# sig ns. • T he data in Group Name field s sho uld be larger so that it stand s out. What You Do 1. Display the Fields tab of the Options dialog box . How You Do It a . From the menu bar, choose File ~Options to d isplay the Optio ns dialog box. b. Select the Field s tab to dis play its con tents. Crysta l Re ports 9.0: Level 1 2. Customize the Number fields by turning off field clipping. 3 . Display the Custom Style dialog box for Date fields. Lesson 1: Creating a List Report a. In the Field Format box , click Number to display the Format Editor dialog box . b. Select the Number tab. c. Click Customize to display the Custom Style dialog box. d . Uncheck Allow Field Clipping so that numbers that are too large for a column will not be cut off. r Rever se Sig n for Display r AUow Field Clipping e. Click OK to close the Custom Style dialog box . The Format Editor dialog box is active . f. Click OK to return to the Fields tab of the Options dialog box . a . In the Field Format box , click Date to dis · play the Format Editor dialog box . b. Select the Date tab. c . Click Customize to display the Date tab of the Custom Style dialog box. LESSON 1 13 LESSON 1 14 4. For Date fields , change the format to 3/1/99. 5. For Group Name fields , change the font size to 12 and close all open dialog boxes. a. In the Format box , from the Year drop - down list, se lect 99. Format Month: 13 3 Day: 11 3 Year : I IP@ 3 b. Click OK to apply the change and close the Custom Style dialog box . '! This format is available in the Style list bo x. c. Click OK to close the Format Editor dialog box and return to the Fields tab of the Options dia l og box. a. Select the Fo nts tab. b. In the Defa ult Fonts box , click Group Name Fie lds to display the Font dialog box . c. From the Size list box , select 12. Size: 111 ~ --1 d. Click OK to close the Font dialog box. e. Click OK to close the Options dialog box . Crystal Reports 9.0: Level 1 TOPIC B Build the Report You 've set yo ur program defaults as needed. Now, all your new reports will contain the appro- priate settings. You 're ready to create your first report. In thi s topic , that 's what you'll do. You have a database file full of data waiting to be used in a report. It 's difficult to find the info1mation yo u need becau se the database contains a lot of info1mation . By using Crystal Reports, you can pull the information you need out of the database and create reports that dis - play the data you want in a meaningful way. Data Sources When you're creating a report in Crystal Rep01t s, you can use any of the following data so urces : • Database, which includes SQL and ODBC data sources • Query • Dictionary A databas e is a collection of related information or data. Standard PC database s, including the data and the software used to access that data , typically reside on a single machine. Stru ctu red Query Lan guage (SQL) database s usually work over a clienUserver network architecture and use SQL to manipulate database tables, fields, and records . Op en Database Connectivity (ODBC) allows many different type s of data to be acce ssed by Cry stal Reports . ., Do not confuse SOL with SOL Server. SOL is a language used to communicate wit h many common client/server databases , incl ud ing Oracle , SOL Server, lnformix , SyBase , and others . A query is a request that retrieves specific fields and/or records from a database . A dictiona ry is a structured and secme view of data that can be placed between a user and the data. Creat- ing a report with a dictionary doe s not require the end use r to perform field linking or construct complex formulas. Table Relationships The most common data source use d when creating reports in Cry stal is a database . A relational database is one in which data is stored in a structme of rows and columns, usually called tables. Those tables ha ve an established rel<l:tionship between each other. When you build a report , you link the database tables by joining link field s from one table to another. A link is a common field (or field s) between two or more tables that is used to con- nect the tabl es. Usin g indexed field s in report generation speeds pe1formance, allowing quick sorts and searches. An index is a pointer ass igned to a field that identifies the location of a record usin g that field . When you're not using a nati ve driver for the connect ion , field s in a primary table can be linked only to ind exed field s in the related table. In all cases, the fields used to link two table s must have the same data type. Lesson 1: Creating a List Report L ESS ON 1 15 LESSON 1 16 Once a link is established between two tables, the type of join ma y need to be changed in a SQL/ODBC environment. J oin types determine how the records from more than one table are matched . There are three common join type s that you will use when linkin g tab les. Those types are outlined in the following table. Join Type Equa l Join Left Outer Join Ri g ht Outer Jo in Function Includ es reco rds from both tables onl y if th e join- ing fi eld s are equal. Thi s is th e default join in a SQL/ODBC co nn ection . Includes all record s from th e left tabl e and record s from th e ri g ht table o nl y if th e joining fi eld s are equal . Thi s is the only type of join availabl e when linkin g direct-access database tab les usin g non- SQL dri vers. Includ es all reco rd s from th e ri ght tabl e and record s from th e left tabl e only if th e joining field s are equal. '! Indexed fields in a direct-access database connection are identified in the Links tab of the Database Expert by small colored tags to the left of the field . _ In Crystal Reports, yo u will use the Database Expert to build your reports. The Database Expert contain s a Link tab that you can use to link two or more tables . Crystal wi ll attempt to link the tables on its own. Sometimes , the links are not what yo u want, so you shou ld always verify their accuracy. The fo llowing table outlines some of the buttons on the Links tab. Button Tables Auto-Arrange Auto-Link Clear Links Function Displays the Choose Tabl es To Use In Visual Link- ing dialog bo x, allowin g yo u to cho ose th e table s yo u want to use in yo ur report. Effici entl y aiTanges th e tables withi n th e Links di s- play area. Links th e tables automaticall y. Clears th e c urre nt tabl e links . ., Onl y string fields can be used for linking when linking from two different ODBC data sources . Crystal Reports 9.0: Level 1 How to Build the Report Procedure Reference: There are three ways to create a report in Crystal. You can create a report manually, you can create a report from an existing report, or yo u can use the Report Creation Wi zard . The mo st common type of report is a li st report . An example of a li st repo1t is displayed in Figure 1-3 . Customer Orders Custo!'l'ff ID 1 1 1 1 1 1 Custorrer N3me Ci ty Cycli sts City Cyclists Ci ty Cycli st s Ci ty Cycli sts Ci ty Cyclist s C1tyCydi sts Figure 1-3: An example of a list report. Ordff ID 1 1,033 1,04 1 1,092 1,143 1,246 Ordff Date 1212/2 000 12:00 :00AM 1218/200 0 12 DO ODAM 1211112 000 12:0D :OOAM 1212 4/2 000 12:DO :OOAM 1/6120 0 I 12 :00 :00AM 113012 00 I 12 OD ODAM Ordff Am:lrn t $41.90 $3,520 .30 $764 .85 $42 .00 $6 2.33 $3,884 .25 To create a simple report manually by u sing the Blank Report option: 1. On th e Standard toolbar, click New to display the Crystal Reports Gallery dialog box. 2. In the Create A New Crystal Report Document box , se lect As A Blank Report. 3. Click OK. The Database Expert is displayed. Using the Data tab of the Database Expert, you can browse to the data so urce that yo u want to use for your report by us ing the fo ld ers li sted in a tree view. Those folders and the data source options you can access usi ng them are listed in Table 1-3. Table 1-3: Data Source Folders Access;b[e through the Data Tab ;n the Data - base Expert Folder Current Connections Repository Favorites History Create New Connection Displays a List of Data so urces to whi ch you are currently connected. Data so urce connections. Data so urces yo u use often and have added to yo ur Favorites li st. The last five data so urces yo u have used. Subfold ers that incl ud e data sourc es to which yo u can connect. 4. In the Available Data Sources list box , expand the Create New Connection folder. 5. Expand the Access/Excel (DAO) category. The Access/Excel (DAO) dialog box is disp la yed . , If you 're using SOL -base d database s such as SOL Server and Oracle , you will most likel y be con - necting using the OLE DB or ODBC catego ry. Check wi th your database adm ini strator. Lesson 1: Creating a List Report LESSON 1 17 LESSON 1 18 6. In the Database Name text box, enter the path and filename of the database that you want to use to create the report. Or, you can search for the database . To do so: A. To the right of the Database Name text box , click the button to display the Open dialog box. B. If necessary, display the contents of the folder that contains the database that you want to use for your report. C. In the list box, select the database. D . Click Open to display its path in the Database Name text box. 7. Click Finish. A connection to the selected database and its contents is displayed 8. If neces sary, expand the Tables category. Select the tables you need for your repo1t and click the Add aITow button. The se lected tables are displayed in the Selected Tables list box. A Links tab is also di s played. 9. Select the Links tab.Verify that the links are cotTect. If they are not, then link them manually. To link tables manually: A. Drag the field from the first table to the field that you want to link it to in the second table. A link will be displayed between the two tables. B. Repeat step A until all tables are linked. 10. Click OK to display the new report. The Field Explorer is docked on the left side of the repo1t window. 11 . If necessary, expand the Database Fields category and the appropriate tables. 12. Add the fields you need in your report using the method of your choice. To place fie ld s in a report by dragging and dropping : A. From the Field Explorer, drag the appropriate field to the appropriate location in the Details section on the Design tab . B. Release the mouse button. To place fields in a report by right-clicking: A . In the Field Explorer, right-click the appropriate field and choose lnse1t To Report. B. Place the mouse pointer at the appropriate location in the Details section and click. To place fields in a report by double-clicking: A. In the Field Explorer, double-click the appropriate field . B. Place the mouse pointer at the appropriate location in the Details section and click . 13. Close the Field Explorer. 14. If necessary, edit field labels. To edit field labels: A. Double-click the appropriate field label to select it. B . Select the existing field label text that you want to replace. C. Enter the new field label text. 15. Save the file. Crystal Reports 9.0: Level 1 Add Tables to a Report After you 've created a report , you might need to add additional tables, so that your report will contain all of the data that you need. To add additional tables to an existing report: 1. Choose Database~Database Expert. 2. Use the Avai lab le Data Sources list box to browse to the database and select the desired table(s). 3. Click the Add arrow button to add the selected table(s). 4. On the Links tab , confirm that the links are coITect or establish links. 5. Click OK to close the Database Expert. Delete Tables from a Report After yo u 've created a repott, yo u might find that th e rep01t includes a table that you no longer need. To delete tables from an existing rep01t : 1. Choose Database~Database Expett. 2. In the Selected Tables list box, select the table to be removed . 3. Click the Remove atTOW button. 4. Repeat steps 2 and 3 for any additional tables you need to remove. 5. Click OK to close the Database Expe1t . ., Any fields from the table being removed will be deleted from the report . ACTIVITY 1 -3 Building the Report Setup: Crystal Reports is active. No reports are open. Scenario: You work for a company that sells bicycles and eq uipment. You need to create a report that summarizes the orders by c ustomer. The report should include cu stomer account numbers , cu s- tomer names, order numbers , order dates , and order amounts. You 've been given the xtreme database, which contains the data that you need to create the report. Now you need to create the report using Crystal. An example of the completed report is displayed in Fig ure 1-4 . Design l x • 0 • I • • (;;>1 Q • • I ' ' ' 2 • ' • I • • ' J ' ' ' I ' ' Q 4<;1 ' • I • • ' O • • 0 I 0 • • 5 ' • • \;:>' Fl epoit Header Page Header r Accou nt .J Customer Name 0 rd er i 0 rd er Da te 'b rd er Amoun t D~~l~s-=--=--~~-==·*l ~:=c u=s=tom=e=r =I Q'===P=us=to=m=er=N=am=e===="'===Or=de=r =ID~( ===Pr=de=r =Da=te==="'==:=o=rd=er=Am=o=u~n{= : I _,,,, .... ,,. ___ ,_,,,, ____ ,,, =='============================ Repo<t Footer Page Footer Figure 1-4: An example of the My Customer Orders .rpt report. Lesson 1: Creating a List Report LESSON 1 19 LESSON 1 What You Do 1. Display the Database Expert dialog box for a new blank report. 2. Create a new connection to the C:\ Student\xtreme. mdb database. How You Do It a. On the Standard toolbar, click the New button [QJ to display the Crystal Reports Gallery dialog box. b. In the Create A New Crystal Report Docu - ment box , select As A Blank Report. Crystal Reports Gallery "'~ I Create a New Cry stal Re port Document - ~ r Using the Report Wizard D r. As a Bla nk Re port c . Click OK. The Database Expert dialog box is displayed. a. In the Available Data Sources list bo x, expand the Create New Connection folder. b. Expand the Access/Excel (DAO) folder. The Access/Excel (DAO) dialog box is displayed . c. To the right of the Database Name text box , click the button D to display the Open dialog box. d. If necessary, display the contents of the folder .that contains the database that you want to use for your report. e . In the list box , select the xtreme.mdb database. f. Click Open to display its path in the Data - base Name text box. 20 Crystal Reports 9.0: Level 1 3. Create a blank report using the Cus- tomer and Orders tables. Lesson 1: Creating a List Report g. Click Finish. A connection to the selected database and its contents are displayed B Create New Connection 1;J Access/Excel (DAO ] · -Make New Conne cti o B ~ C \Student \xtreme m :. ~Ad d Com mand EH~ Table s ffH~View s ffi .. ~ Stored Proce dur e: a. If necessary , expand the Tables category. b. Select the Customer table, press Ctr!, select the Orders table, and release Ctr I. c. Click the Add arrow button LJ . The selected tables are displayed in the Selected Tables list box. Selected Tables: g.. C:\Student\xtreme .mdb .. ~ Customer ··~ Ord er s d. Select the Links tab. Im Oat~~sc Expert " Data Lnk.~ I Link together the lab&es ,ou added lo the Htpod. Li'h.g ~ needed to match records of one t~ ~ COfreil)Ol"ICi"lg reC;Ordi of anothef ta.. .OJst""°' ID • Customer Credit ID Customer Ncwne Contact FS"st Name Cont&t Last Name r~+ .. r +Tif.a,,. di j\_ •c.det!D C.det Amornt •customer ID DE..,ioyeeIO ..:J Ord.er Date n -.-...in ...... . or. Auto-Anar10e I [C~1 I o~cv~1 L!rh I a. .. Link.< I C~Hr :.I' I Llrt -~-1.""'~ I lnde•Leoend . I H~ LESSON 1 21 LESSON 1 22 4. What does the Links tab indicate has occurred? Is the link correct? Click OK to display the blank report. 5. Display the Field Explorer. From the Customer table , add the Customer ID and Customer Name fields, start- ing from the left end of the section. a. If the Field Explorer is not open , on the Standard toolbar, click the Field Explorer button ~· fl You can also choose View--tField Explorer. b. Expand the Database Fields category. c. Expand the Customer table to display the list of available fields. ~"1 1 ~'~x i;l·· 111 0 ataba se Field s ;. ~~ Cu stomer : a!!'~ Order s d. From the Customer table, drag the Cus- tomer ID field to the left end of the Details section of the report. Design I I •• 91 • Report Header fl In the Field Explorer, a check mark is displayed on the field icon indicating that the field has been added to the report . e. From the Customer table, drag the Cus- tomer Name field to the right of the Customer ID field. Crystal Reports 9.0 : Level 1 6. From the Orders table , add the Order ID, Order Date, and Order Amount fields. .p From the Field Explorer, the contents of a field can be browsed by selecting the field and clicking the Browse button . This is helpful when faced with confusing field names . The Bro wse window display s the first 500 unique values for the field . 7 . Change the Customer ID field label to Account #. 8. Change the Order ID field label to Order#. 9. Save the report as My Customer Orders.rpt in the C:\Student folder. Lesson 1: Creating a List Report a. In the Field Explorer , expand the Orders table. b. From the Orders table , drag the Order ID field to the right of the Customer Name field. c. From the Orders table, drag the Order Date field to the right of the Order ID field . d. From the Orders table , drag the Order Amount field to the right of the Order Date field. e. Click Close to close the Field Explorer. a. Double-click the Customer ID field label. .p You can also right-click the field label and choose Edit Text . b . Select the existing text. c . Type Account#. a. Double-click the Order ID field label. b. Select ID and type#. a. Choose File ~Save As to display the Save As dialog box. b. In the File Name text box , select the existing text. Type My Customer Orders. c. Click Save to save the report. LESSON 1 23 LESSON 1 24 TOPIC c Preview the Report You 've added the necessary fields to your repo1t. H owever, you can't see the actual data in those fields. In this topic, you 'll preview your report. A field in a report can contain hundreds, even tho usands, of data values. Until you preview a report, you will be able to see only the data placeholders. By previewing a rep01t, you'll be able to view the actual data in the fie lds and work with the data . Also, you'll know if the fields are set up the way you want them. Navigate Using the Preview Tab When you preview a report, Crystal Reports disp lays the report in a Standard view. In that view, the report is displayed one page at a time. Yo u can use the Navigation buttons on the Preview tab to move around within the report. Those buttons are o utlined in Table 1-4. Table 1-4: Nav igatio n Opti on s on the Preview Tab Navigat ion Butto n Close View Show First Page Show Previo us Page Show Next Page Show Last Page Stop Descr iption Closes the Preview tab. Displays the first page of a report. Moves back to the previous page of a report. Moves forward to the next page of a report . Displays the last page of a report. Stops the readi ng of data . The Date Age Indicator is displayed to the left of the navigation b u ttons on the Preview tab. It displays the date the data was refreshed or retrieved for the first time , whichever is most recent. If the data was initiall y refres hed or retrieved today, it dis pl ays that time. How to Preview the Report Procedure Reference: Once you 've placed the approp1iate fields in a rep01t, you'll want to preview it so that you can see the actual data. To preview a rep01t : 1. On the Standard toolbar, click the P1int Preview button. The report is displayed with the actual data on the Preview tab. 2. Use the navigation buttons on the Preview tab as desired to preview the report. 3. To close the Preview tab, click its Close button. Print a Report Suppose you need to distribute a report to other users, or you want a hard copy of it. You can easily print a report by following a few quick steps. To print a repo1t: 1. On the Standard toolbar, click the Print button. The P1int dialog box is displayed. Crystal Re po rts 9.0: Level 1 fl You won't be abl e to displ ay th e Pr int dia lo g box unle ss you have a pri nter driver installed. 2. Select the desired print options. 3. Click OK to print the repo1t. ACTIVITY 1 -4 Previewing the Report Setup: The My Customer Orders .rpt report is open. Scenario: You've added the necessary fie lds to your repo1t . However, on ly the fie ld placeho lders are displayed . You can 't actually see the data in those field s. Yo u need to preview the data so that you can see how it looks in the report. Figure 1-5 displays a preview of the My Customer Orders report. P1eview l odO'; 1!\32 X :~ ~ 1 ol37 >tomerOr .-'----.-.-=. :-. :--, 7. -. -, -. ------. -. -. 3-. --:--.-.-.-.--.-.-.-5-.-.-.-,-.-.-.-6-.---:--. -. -, -. ---- RH D -D - I 8.li..2.!mU 1 41 C usto mer Name C ltv Cyc lists Deals on Wheels 77 Warsaw Sports, Inc . 18 B ikes an d Trike s 64 SA B M oun1 a1n 7 Po ser Cyc les 32 Spokes ~ ~ Q r~~[AffiQUnt 1 121212 000 12:00 .00AM $41 .90 1,002 121212000 12:00 :00AM $5,060.28 1 ,003 121212 000 12:00 .00AM $1 86 .87 1 ,00 4 1 2nl2000 12·oo·OOAM $8 23 05 1,005 121312000 12:00 .00AM $29.00 1 ,006 121312000 12:00:00AM $64.90 1 ,007 121312000 12:00 .00AM $49.50 Figure 1-5: A previ ew of the My Customer Orders.rpt repo rt. What You Do 1 . Preview the report and view the report data using the report naviga- tion buttons. How You Do It a. Click the Print Preview button . T he Pre - vi ew tab i s di splayed . b . Us ing Table 1-4 as a gu i de , navigate through the report to preview the data . 2. On the Preview tab, what information is displayed between the report navigation but- tons? Lesson 1: Creating a Lis t Report LESSON 1 25 LESSON 1 26 3. The Date Age Indicator is displayed to the left of the report navigation buttons. What data does it display? a) The system date and time. b) The date and time the report was opened. c) The date and time the data was last refreshed or initially retrieved. d) The date and time the report was last saved. TOPIC D Add a Report Title You 've previewed your first report in Crystal Reports . It contains th e data yo u need . However, you would like some type of identifier in the report that immediately de sc ribe s its contents . In thi s topic, you' 11 add a title to your report. Your report contains the appropriate data field s. Unless you add a titl e to the rep01t , it might look incomplete. Adding a title is an important step when creating a report. By adding a title , th e viewers of your re port will be able to easi ly identify what data yo ur report includes. Figure 1-6 displays an example of th e M y Customer Orders rep ort before and after a title is added to it. Ac c oun t# 1 41 77 18 Acco unt# 1 41 77 18 C ustomer Name C lt y Cy c lists Deal s on W heel s Warsaw Spo rt s, In c . Bike s and Trike s C ust omer Name C lty Cy c li sts Deals on \IV heel s Wars aw Spo rt s , I nc . Bikes and Trikes 0 rder # 1 1,002 1,003 1,00 4 Order Date 121212000 12 :DO :OOAM 12/212000 12:DO :OOAM 12/212000 12 :00 :00AM 12/212000 12:00 :00AM Customer Orders 0 rder # 1 1,0 02 1,003 1,004 0 rder Date 121212000 1 2:DO :OOAM 121212000 12:DO :OOAM 121212000 12:00 ODAM 121212000 1 2:DO :OOAM 0 rd er .A. m aunt $41.90 $5,0 60 .28 $1 86 .87 $823 .05 O rder Amount $41.90 $5,060.28 $186 .87 $823 .05 Figure 1-6: An examp le of My Customer Orders report without and with a report title. Text Objects Jn addit ion to addin g fields to a rep01t , yo u ca n also add te xt obj ects th at inclu de te xt or field s . There are ma ny ways yo u can u se te xt objects in yo ur reports . You can use a text object to add literal te xt, s uch as a report title , or it can contai n a databa se field. Crystal Reports 9.0 : Level 1 Re-size Fields Procedure Reference: After you've added a text object or any other field to your repo1t, you might need to re-size it. You can re-size a field only, the field label only, or both the fie ld and its associated label. To re-size a field only, select the field and drag the left or right sizing handle left or right to increase or decrease the size of the field. To re-size a field and its associated field label: 1 . Select the field 's label. 2. Press Ctrl. 3. Select the associated field. 4. Release Ctrl. 5. From the left or right sizing handle, drag left or right as desired . Format Fields Procedure Reference : You mig ht also want to change the formatting of a text object. There are a number of formatting changes you can make including font style, font size, bold, underline, italic, and alignment. To format a text object: 1. Select the object. 2. If desired , make the object bold by clicking the Bold button on the Formatting tool bar. 3. If desired, change the font size by clicking the Font Size drop-down arrow on the Formatting too lbar, and selecting the appropriate size. 4. If desired, align the object by clicking the Align Left, Align Center, Align Right, or Justify buttons on the Formatting toolbar. '! Reports that were created in a previous version of Crystal Reports mi ght not retain all of their fo rmatting. How to Add a Report Title Procedure Reference: Add a Report ntle Most of your repo1ts will contain text objects. You can add a variety of information to your repo1ts by using text objects. To add a report title by using a text object: 1. On the Insert Tools toolbar, click the Insert Text Object button ab . 2. Place the insertion point anywhere in the Report Header section and click to insert a text object. 3. Type the title text. 4. Click outside the text object to deselect it. Less on 1: Creating a List Rep ort LESSON 1 27 LESSON 1 28 5. If desfred , you can re-size the text object. 6. If desired , you can format the text object. ACTIVITY 1 -5 Adding a Report Title Setup: The My Customer Orders.rpt report is open. Scenario: You 've just saved your first report containing info1mation abou t customer orders. That repo11 will eventuall y be viewed by others. You want the viewers of yo ur report to be able to imme- diately identify what your report includes. Therefore, you need to add a report title that stands out. What You Do 1. On the Design tab, insert a text object on the left end of the Report Header section that reads Customer Orders. How You Do It a. Select the Design tab. b. On the Insert Tools toolbar, click the Insert Text Object button ~. c. Place the insertion point on the left end of the Report Header section. An object frame is attached to the mouse pointer . d. Click to display a text object at that location. e. Type Customer Orders. Crystal Reports 9.0: Level 1 2. Format the title to 14 pt bold. 3. Re-size and center the title at the top of the page , and deselect the title. Lesson 1: Creating a List Report f. Click outside the report header to enter the title. ' ' • I • • [:;u st om er O rd e rs a. Click on the title text object to select it. b. On the Formatting toolbar, click the Font Size drop-down arrow 11 o ::J I to display the drop-down menu and choose 14. c. On the Formatting toolbar, click the Bold button ~ to make the title text bold. 'fl The title te xt is too big for the text object. a. Place the insertion point on the right sizing handle until it displays as a double-headed arrow. b . Drag to the end of the ruler to re -size the field margin to margin. ' -~~ ., ' c. Place the insertion point on the bottom sizing handle and drag down to increase the height of the field . d. On the Formatti~toolbar , click the Align Center button L!J to center the text within the object. e. Click in a blank area of the report to deselect the title. 'fl You could center the title by guessing , but to ensure the title is accuratel y centered , follow the previous steps. LESSON 1 29 LESSON 1 30 TOPIC E Align Fields Your new ly created report has a report title that is centered. It rea ll y adds to the organization of the report. To further organize the report, you mig ht want to alig n the fields. In this topic, that 's what yo u 'll do. Suppose the fie lds in a report don 't fit con-ec tl y or the spacing does n't look right. Alig ning report fields is an impo1tant step in building a report. By align in g fields , the report wi ll be easier to read and will look better. An example of a repo1t with poor alignment is displayed in Figure 1-7 . A c co unt# C ustomer N ame 1 C rt y Cy c list s 41 Deal s on W heel s 77 Warsaw Sport s , In c . 18 Bike s and T rike s 64 SA 8 MOU nt ai n 7 Po s er Cyc le s 32 Spokes 11 C lean Air Tran s portation Co . 25 Ext reme Cyc li ng Custom er Orders O rder# 1 1,002 1,00 3 1,004 1,005 1,006 1,007 1,008 1,009 Figure 1-7: An examp l e of a report wnh poor alignment. How to Align Fields Procedure Reference : Ord er Amount $4 1.90 $5,060 .28 $186 .87 $82 3.05 $29 .00 $64 .90 $4 9.50 $2,214 .94 $29 .00 After you place fie lds in a report, you mig ht have to move them so that they are prop- erly aligned. To move fie lds: 1. If necessary, re-size the field you want to move. 2. On the ruler, locate the guideline for the field you want to move. 3. D rag that fie ld's guideline left or right to the desired location . Re-attach Objects to Guidelines Sometimes when you move fields or other objects, they become detached from their associated gui delines . To re-attach an object to a guidel ine , simply drag it onto the guideline . The object will snap to the guideline and wh en you deselect the object, a red mark will display on the left side of th e object indicating that it is attached . Crystal Re ports 9.0: Level 1 ACTIVITY 1 -6 Aligning Fields Setup: The My Customer Orders.rpt report is open. Scenario: Your report is really comi ng together. Now that you 've added th e title, yo u look a t the fie ld s and realize that some of them need to be re-sized and moved so that th e rep01t looks better. What You Do How You Do It 1. When you preview the report , what do you notice about the alignment of the fields? 2. Using the Design tab, move the Cus- tomer Name field to the 1. 5" mark. 3. Move the Account# field to the 1" mark. ' To verti cally or horizontally align fields · with out using the guidelines , multiple fields can be selected and aligned by choo si ng Format ~Align and the appro- priate alignment option . ' After placing a field in a report , guideline arrows display in t he ruler at the left edge of string and date fields . Guideline arro ws for number fields run along the right edge of the field . Lesson 1: Creating a List Report a. Display the Design tab. b. Place the mouse pointer on the guide- line for the Customer Name field. c. Drag the Customer Name guideline to the 1 . 5" mark on the ruler. If an object becomes detached from its associ- ated guideline , you can re-attach the objec t by dragging the object until it snaps to the guideline . a . Place the mouse pointer on the guide- line for the Account #field. b. Drag the Account # guideline to the 1" mark on the ruler. LESSON 1 31 LESSON 1 4 . Move the Order Amount field to the a. Using the associated guideline , move the 7.5" mark, the Order Date field to Order Amount field to the 7. 5" mark. the 5" mark, and the Or der No. field to the 4.5" mark. b. Using the associated guideline , move the Order Date fie ld to the 5" mark. c. Using the associated guideline , move the Order No . field to the 4 . 5" mark. 'fl The loc ation s of th e fiel ds don 't have to be exact. 5. Re-size the Customer Name field . a. Select the Customer Name field label. b . Press Ctrl an d click the Customer Name field. Release Ctrl. c. Place the insertion point on the right sizing hand le and drag to the right to the 3 . 75 " mark on the ruler t o increase the size of t he field . 6 . Preview the report. Save and close a. Preview the report. the report. b . On t he Standard toolbar, click the Save button ~to update the saved report. c . Click the Close button t o close the report . Lesson 1 Follow -up In thi s lesso n, you created a list report from in fo1mation in an exis tin g database. First, you set defau lt report settings so th at the report wo ul d dis pl ay the way yo u needed it to . T hen, you built the report, incl uding previewing the d ata , ad din g a report ti tl e, and aligning fie lds . Now when you or someone else needs to di s play specific information from a database in a report , you 'll know how to do it. 1. What default settings do you plan to change for your reports? 2. What types of data will your reports include? 32 Crysta l Reports 9.0 : Le vel 1 LESSON 2 Displaying Data in a Report Lesson Objectives: In this lesson , you will locate and present data in a specified order. You will: • Find data in a report. • Sort data in a rep01t . • Filter data in a report. Les son 2: Displaying Data in a Repo rt LESSON 2 Lesson Time 1 hour(s) to 1 hour(s), 30 minutes 33 LESSON 2 Introduction You 've just created your first report, and it contains a lot of important data. At times, you'll want to reference only portion s of that data. Therefore , yo u need to find ways to quickly access s pecific data in the report. In thi s le sson, you'll di scover a few options for dis playing specific data in a re po1t. There 's not hing more frustrating than having access to infonnation but not being able to quickl y find a s pecifi c piece of that information when you need it. If that information isn 't organized correctly or there 's too much of it , you'll waste a lot of time trying to dig out the piece you need. Crystal Repo1t s provides you with option s to qu ickly find the information you need in a report and for presenting the data in your repo1ts in a specific order or format. By usin g these option s, yo u 'll be able to quickly locate the information yo u need, when yo u need it. TOPIC A Find Data You 've printed your report and it 's quite long . It includes a lot of information. When you 're pre view ing the report , you might need to quickly locate one record in the rep01t. In this topic, you'll find out how to do that. You have a repo1t that contains a large amount of information. A customer just called and you need to preview the report to find the record for that customer. Looking through the report for th at specific record could take quite a bit of time. By using the Find dialog box, you can qui ckly locate the record you need . How to Find Data Proc edure Referen c e : You will create reports that contain a small amount of data and tho se that contain a large amount of data. For the large reports, it would be helpful to have an option avail- able for findin g a few field s or records quickly. You can do that by using the Find dialog box. To find data in a report: 1. Verify that the Preview tab is di s played. 2. On the Standard toolbar, click the Find button to display th e Find dialog box. 3. In the Find What text box , enter the data you want to find. ,. Make sure that your search strings match the field formatting. 4. Click Find Next to find the s pecified record. 5. Click Cancel to close the Find dialog box. 34 Crystal Reports 9.0: Level 1 ACTIVITY 2 -1 Finding Data Data Files: • Specific Orders.rpt Setup: No files are open in Crystal Reports. Scenario: Your boss needs your help. He received a call regarding a problem with order #2,783. The customer appare ntl y se nt a check fo r th e inconect amount. You need to quickly locate that order in th e Specific Orders rep01t so you can reso lve the problem. What You Do 1. Open the Specific Orders.rpt report and save it as My Specific Orders.rpt. 2. Find the record for order number 2,783 . How You Do It a. On the Standard toolbar , click the Open button I ~ ·I· The contents of (:\Student are displayed in the Open dialog box. b . Select Specific Orders.rpt and click Open. c . Save the file as My Specific Orders.rpt. a. On the Standard toolbar, click the Find button ~to display the Find dialog box . b . In the Find What text box , type 2 ,783. c. Click Find Next to find the record . 3. What is the customer name, order date, and order amount of the found record? Lesson 2: Displaying Data in a Report LESSON 2 35 LESSON 2 36 4. What happens when you try to find record 2183 by typing the text 2183 in the Find What text box? How should the order number be entered in the Find What text box? Click OK to close the message box . ., When searching for number fields , if data is formatted to show the thousands separator, the separator must be used in the Find What text box . The same is not true for decim al formatting. Numbers will be found regardless of the decimal forma t. ., w 11 hen formattting m 1 igth t retur~f . unf~x1 pdefcted resuhl~s . us 5 ers ahre adv 1 itsed to uste. the Atdvdabncefd Findtt .optionh, whic _h a ows users o se ec a spec1 1c 1e or searc ing . earc resu s are no 1mpac e y orma ing w en using this option . 5. Close the Find dialog box and return to the top of page 1 of the report. TOPIC B Sort Data a. Click Cancel to close the Find dialog box. b. On the Preview tab , click the Show First Page button [J.iJ to return to page 1 of the r eport. c. Scroll to the top of page 1. By using the Find dialog box , you quickly found a specific record in a report. The order in which data is di s pla yed in a repo1t can also help you find data quickly. In thi s topic, you ' II organize the data in a rep01t by s01ti ng . Suppose you've collected a lot of change over the past few years, and you want to exchange it at a bank for paper money. Before you do, yo u'll need to put it in coin rolls so that it 's sorted conectly and is easier to exchange. Just like you organize your change, you can also organize the records in a report. By controlling the order that records are listed in a report, you'll be able to display your data in a more meaningful way. Therefore, your report will also be easier to read and interpret. Crystal Reports 9.0: Level 1 Natural Order When a database is created, the record s are entered in a tab le ba se d on the design of the table. When you use databa se data in a re port, the records are displayed in the order they were input in the datab ase. Th at order is the natural orde1: There are a number of way s yo u can change the order of the record s in a repo1t. One of tho se ways is by sortin g the data. You can sort the data in ascending or de scending order. If you choose ascending order, the record s are sorted from s malle st to largest (that is , 1 to 9, A to Z ). If you choose descendin g order, the records are sorted from largest to s ma ll est (that is, 9 to 1, Z to A). How to Sort Data Procedure Reference: To so1t the re cord s in a report: 1 . On the Expert Tool s toolbar, click the Record Smt Expert button. The Record Smt Order dialog box is dis played. 2. In the Available Field s li st box , double-click the field by which yo u want to so1t to add it to the Sort Fields li st box. 'ft You can sort on any fiel d in a report . 3. In the Sort Direction box, verify that the direction th at you want to sort by is se lected. Ascending is the default solt order. 4. Click OK to so lt the repolt. Remove a Sort Field At so me point , you'll want to remove a solt field from th e Record Solt Order dialog box . To do so: 1. Di s play the Record Sort Order dialog bo x. 2. In the Solt Fields list box , select the field that you want to remove. 3. Click the Remove arrow button . 4 . Click OK to close the Record Sort Order dialog box . Change Sort Field Order At so me point, you might wa nt to re-rurnnge the order in which field s are so1ted . To do so : 1. Display the Record Sort Order dia log bo x . 2. In the Sort Fields li st box , se lect the field that you want to change. 3. Click the up and down aiTow keys in the upper-1ight corner to re-position the field/sort order. 4 . Click OK to close the Record Sort Order dialog box . Lesson 2: Displayi ng Data in a Repo rt LESSON 2 37 LESSON 2 ACTIVITY 2 -2 Sorting Data Setup: The My Specific Orders.rpt repo1t is open and page 1 is displayed. Scenario: You 've been using the Specific Orders rep01t for a while to help you track customer orders. Now yo u need to know how many orders eac h c ustomer made on a specific date. It would be very helpful if the report data appeared in ascending order by date. You also want to view the d ata by Customer Name in ascending order. Then , all the orders for each customer will be li sted together. What You Do 1 . Sort the report by the Order Date field in ascending order. 2. The first order date listed is --- How You Do It a . On the Expert Tools toolbar , click the Record Sort Expert button !§] . The Record Sort Order dialog box is displayed. b. In the Available Fields list box , double- click the Orders.Order Date field to add it to the Sort Fields list box. Sor t Field s: 1<13 A -Order s.Order Date 'fl You can also select the field and click the Add arrow button . c. In the Sort Direction box , verify that Ascending is selected. d. Click OK to sort the report in ascending order . 38 Crystal Reports 9.0: Level 1 3. In the Record Sort Order dialog box , remove the Orders. Order Date field from the Sort Fields list box. 4. Sort the report by Customer Name in ascending order. a. Display the Record Sort Order dialog box. (Click the Record Sort Expert button.) b . In the Sort Fields list box , verify that Orders.Order Date is selected. c. Click the Remove arrow button [;]. a . Add the Customer.Customer Name field to the Sort Fields list box . b. Perform the sort. 5. How many records are listed for Beach Trails and Wheels? Return to the beginning of the report. TOPIC c Filter Data by Single Criteria The data in your report is now displayed in a particular order. Even though the order of the data has changed, the same amount of data is displayed. Suppose you want to display only part of that data. In this topic, you will filter data based on one field. Undoubtedly, you'll create reports that contain a l arge amount of data . Suppose you are pre- viewing one of those repo1ts . N ot only is the report slow because there are so many records, but you really only want to see data for a specific period of time. By filte1ing the report, you 'll disp lay only the data that yo u need to view at the time, saving you from having to work with more data than you need . Also, the report will be easier to read and wi ll be much quicker to access because less data is displayed. Filter If you do not want all the records to display in a report, you can filter the report . A.filter allows yo u to display ce1tain records based on specific c1ite1ia. Criteria are the rules on whi ch a decision is based. Lesson 2: Displaying Data in a Report LESSON 2 39 LESSON 2 Comparison Operators De finition : When you filter a report, you'll often use comparison operators to set the crite1ia. A comparison operator is a statement you can use to compare data in a field that has a fixed value with the content of another field. Ex ample: Some examples of compaiison operators and their symbols are shown in the following table. Comparison Operator Is equal to Is greater than Is less than Is between Symbol > < >=x and <=y For more information on comparison operators, see Crystal Rep01ts Online Help . How to Filter Data by a Single Criterion Procedure Refere nce: You can filter a report by single or multip le criteria. To filter data by a single criterion: 1. Select the report field on which you want to filter. 'f You can filter data from the Design or Preview tab . 2. On the Expe1t Tools toolbar, click the Select Expert button. The Select Expert dialog box is displayed. 3. From the drop-down list, select the appropriate comparison operator, or enter one in the text box. 4. In the second drop-down list box, select a value from the drop-down list or enter a value. 5. Click OK to filter the data. 6. If necessary, refresh the data. 40 Crystal Rep orts 9.0 : Level 1 ACTIVITY 2 -3 Filtering Data by a Single Criterion Setup: The My Specific Ord ers.rpt report is open. Scenario: Your bos s wants you to create a report that show s th e records for those customers who placed an order for more than $500. He doe sn 't want to see any oth er c usto mer data. What You Do How You Do It 1. There are records currently displayed in the report. Select the Design tab. 2. Display the Select Expert dialog box for the Orders.Order Amount field. Lesson 2: Displayi ng Data in a Report a . Select the Order Amount field. 'fl If you don 't select a field first , the Choo se Field dialog bo x will be displ aye d. From there , you can select a field on which you wa nt to filter an d click OK to display the Select Expert di alog box . b. On the Expert Tools toolbar , click the Select Expert button ~ . The Select Expert dialog box is displayed. LESSON 2 41 LESSON 2 42 3. Display only those records with an order amount greater than 500 by refreshing the data. 4. How many records are displayed? Save and close the report. a. On the Orders.Order Amount tab , from the drop-down list, select Is Greater Than. b. In the second text box , type 500. Im Select Eicpert , Si ; Order s.Order Amount I <New > I j is greater than You can also select a value from the drop-down list. If you are on the Preview tab when you modify selection criteria , as soon as you close out of the Select Expert , you will be asked whether you want to use saved data or refresh the data . c. Click OK to filter the report. d. Select the Preview tab to preview the report. A message box is displayed . . . . Use saved data from : 9113/2002 8:42? . Use_ Saved Data . I ___ Re_fr_e_sh_D_a_ta _ __. e. Click Refresh Data to retrieve fresh data for the report. Crystal Reports 9.0: Level 1 Lesson 2 Follow-up Congratulations. You 've located and pre se nted data in a s pecified order. First, you fo und data. Then , yo u sorted an d filtered data. Now when you need to quickly access or display specific data in a report, yo u 'll know how to do it. 1. How often will you sort data in your reports? 2. How often will you filter data in your reports? 3. What types of filters will you create? Lesson 2: Displaying Data in a Report LESSON 2 43 LESSON 3 Grouping Report Data Lesson Objectives: In this lesson , you will create groups to summarize report data . You will : • Inse1t a group in a report. • Add summaries to a repo1t. • C hange the display of report groups . • Add additional groups to your report. • Format a group header. • Filter group data. • Create a Top N sort group . Lesson 3: Grouping Report Data LESSON 3 Lesson Time 2 hour(s) to 2 hour(s), 30 minutes 45 L ESSON 3 46 Introduction You know how to display a report in the order and with the data that you want, making it easier to read and manage. Another way to organize a report and improve its value is to group, and if desired , summadze the data in each group . In this lesson, you 're going to obtain the basic skills needed to group and summarize data in a report. Suppose you have a spreadsheet that contains co lumns of numbers that make up your monthly househo ld budget for the past six months. Without totals, the report isn't as valuable to you. You want to be able to view each monthly expense, and the total amount you pay each month. The same is true for the data in your reports. When you 're creating reports in Crystal, you 'll want to create reports that can be summarized. By summarizing the data, your reports will be better organized and the data will be more meaningful. TOPIC A Insert a Group Before you can summadze data in a report, you need to display the data in a particular order. In this topic, you will insert groups so that similar data will appear together. By grouping your data, you 'll be able to show related information together, creating a better organized report that is easier to understand. You can easily navigate in a long report when it is grouped . Also, grouping data allows you to summaiize the data by group, instead of on all the records in a report. What is a Group? Definition : A group is a collection of related data. A group is based on a selected field (or for- mul a) and displays in a selected order. When you create a group, a Group Header section and a Group Footer section ai·e displayed. The Group Header section can con- tain a Group Name field. You can use the Group Header or Group Footer section to display summary information for the gro up. 'fl Removal of a Grou p Na me fie ld from a report wi ll not impact th e re port grouping . Example: Some examples of a group are displayed in the following list: • • • All customers living in the same region . All orders placed by the same customer . All orders sold by the same sales representative . Crystal Rep orts 9.0: Leve l 1 Group Tree View Wh e n yo u cre a te a group, th e Group Tree view is di spl ayed . It is a s plit scree n . In th e left pa ne, a hi gh-level outlin e of th e re p011 is di spl ayed th at shows th e hi erarchy of gro up s a nd sub gro up s in a tree form a t. In the ri g ht pan e , th e co nt e nt s of th e re p011 are di s pl ayed. You can use th e Group Tree view to qui ck ly move to a pa11i cul ar group by selec tin g the node fo r th at group . ., If you have a report th at contain s a large number of groups , all t he group names might not displ ay in the grou p t ree , as the gro up tree displays onl y appro ximate ly 64 K of all the group names . How to Insert a Group Procedure Reference : To in se11 a group in a re port: 1 . On the In se11 Tools too lb ar, cli ck th e In sert Gro u p but to n. The Insert Group di al og box is di splayed . ., You can also ch oose ln s ert~Group . 2 . Fro m th e top dro p-dow n li st, select th e fi e ld by whic h yo u want to gro up the da ta. 3. From th e second drop -d own li st, selec t th e ord er by whi ch yo u want to sor t th e group . 4. C li ck OK. ACTIVITY 3-1 Inserting a Group Data Files : • Custo me r Groupin g.rpt Setup: N o fi les are open in Crys ta l Re po11s . Scenario: You created th e Cu stomer Groupin g re port , whic h co ntain s c ust omer order dat a , fo r yo ur boss. He like s th e report. H owever, he th inks th e re port wo uld be more meanin gful if th e re lated da ta was g rouped to ge th er by c ustomer name . An exam p le of th e gro uped re p011 is di spl ayed in Fi gure 3-1. Less on 3: Grouping Report Data LESSON 3 47 LESSON 3 48 Customer Orders Acco unt No . C u st omer Name O rder Ord er O rder Allev Cat Cvcles 1 4 A ll ey Cat Cyc les 2,735 0211 9102 $8,8 1 955 1 4 A ll ey Ca t Cyc les 2,300 1013 010 1 $1,78 4.32 1 4 A ll ey cat Cyc les 2,66 4 0113 110 2 $9,290 .30 1 4 A ll ey C at Cyc les 1,939 0810510 1 $1 ,409 .55 1 4 A ll ey Ca t Cyc les 2,301 1013110 1 $1,66 4.70 Figure 3-1: An example of the Customer Group;ng.rpt report grouped by customer name . What You Do 1. Open the Customer Grouping.rpt report and save it as My Customer Group;ng.rpt. Display the Design tab. 2. Create a group by Customer Name in ascending order. How You Do It a. Click the Open button. b. Select Customer Grouping.rpt and click Open. c. Save the report as My Customer Groupjng.rpt. d . Select the Design tab. a. On the Insert Tools toolbar, click the Insert Group button ~. The Insert Group dialog box is displayed . 'f You can also create a group by choosing Report ~Group Expert . b . From the top drop-down list, select Customer.Customer Name. Insert Group · ¥; Common j Option s I \.I/hen the report is printed, the recor ds will be sorted and grouped by: c. In the second drop -down list, verify that In Ascending Order is displayed. d. Click OK to create the group. Crystal Reports 9.0: Level 1 3. On the Design tab, how has the display of the sections changed? 4. What is displayed in the Group Header #1 section? What is displayed in the Group Footer #1 section? 'fl Th e Group Foot er sect ion norm all y contai ns summari es . 5. Preview the grouped report. a. If necessary , select the Preview tab. b . Use the report navigation buttons to view the grouped report. A sample of the grouped report is displayed in Figure 3-1. 6. What has been added to the contents of the Preview tab? 7. Using the group tree , display the data for Spokes for Folks, and save the report. Lesson 3: Grouping Report Data a. In the group tree pane , scroll down until the customers that begin with 's" are displayed. b . Using the ToolTip as a guide , click Spokes For Folks to display the associated cus - tomer data i n the report . Save -on · Spoke s l··· Spokes or Fol k s~ : .... Spoke s · GH1 On the Stand ard toolb ar, you ca n clic k th e Toggle Group Tree button to turn th e group tree off and on . c . Click Save to save the report. LESSON 3 49 LESSON 3 TOPIC B Add Summaries Now that you have a grouped report, yo u can s ummari ze your data. In this topic , you 'll add summaries to your re port. The data in yo ur report is now grouped by customer name. The next ste p you'll want to take is to summaiize the grouped data . By adding s ummarie s to yo ur report, you can mathematicall y s ummai·ize only the gro uped data you want, further organizing your repo1t. Summary Operations When you 're read y to summarize the data in a report, yo u need to choose how you want the data to be summaiized . You can use summary operations (a lso known as functions) to do that. In Crystal Reports, yo u have access to a number of s ummary operations. The ones you can use depend s on the data ty pe of the field you're summarizing. Some of the more common sum- mary operations are outlined in the following table. Operatio n De scription Sum Add s th e val ue s for th e se lec ted fi e ld . Average Minimum Maximum Calculates the average for the se lected field. Calculates th e lowest value for the selected field. Calculates the highe st va lu e for the se lected field . Calculates the total number of records for the se lected field. Count For more inf01mation on summary operations, see Crystal Reports Online Help . How to Add Summaries Procedure Refere nce: Add Group Summaries To add group summaries to a report: 1. In the Details sec tion, se lect the field that you want to summaiize. 2. On the Insert Tool s toolbai·, click the Insert Summary button . The In se1t Summary dialog box is di splayed. 3. From the Calculate Thi s Summai-y drop-do wn li st, select the desired s ummary operation. 4. From the Summary Location drop-down list, verify that the field by which you want the data to be grouped is se lected. 'ft By default , the summary location is set to Grand Total. 5. Click OK to in sert the summari es. 50 Crystal Reports 9.0: Level 1 6. H desired, you can add a field label for your summaiies by inserting a text object. fl New groups can also be created by inserting summary field s based on a field that is not yet grouped . Adding the summary field will automatically create a group header and footer. Procedure Reference: Add a Grand Total After you summarize the data in a repo1t, you might want to add a grand total. A grand total summai·izes the data of all the groups in a report. To insert a grand total: 1. Select the field for which you want to insert a grand total. 2. Click the In sert Summary button. 3. Click OK to insert the grand total. ACTIVITY 3-2 Adding Totals Setup: The My Customer Grouping.rpt report is open. Scenario: Your boss is very pleased with the group you added to the My Cu stomer Grouping report. Now, he would like to see a total that calculates the sum of the order amount for each customer. In addition , he wants a grand total for the whole report. What You Do 1. On the Design tab , display the Insert Summary dialog box for the Order Amount field. Lesson 3: Grouping Report Data How You Do It a. Select the Design tab. b. In the Details section, select the Order Amount field. c. On the Insert Tools toolbar, click the Insert Summary button [!]. The Insert Summary dialog box is displayed. '! You can also right-click the field and choose lnsert ~Summary. LESS •N 3 51 LESSON 3 52 2. Insert a sum by group for the selected field. 3. Insert a grand total for the Sum Of Orders.Order Amount field. a. In the Calculate This Summary drop-down list box , verify that Sum is displayed. b . From the Summary Location drop -down list, select Group #1: Customer.Customer Name-A. c. Click OK to insert the summary in the report. a. Select the Order Amount field . b. Click the Insert Summary button. c. Click OK to insert the grand total. 4 . Using the Preview tab, the summary for Bikes For Tykes is • The grand total for the report is ------ 5. For the Group Summary field , add a field label at approximately the 5.25" mark that reads Group Total. Make the label bold and decrease the size. 6. Add a field label for the grand total. Re-size the labe l and make it bold. ---- a. Return to the Design tab. b. In the Group Footer section , insert a text object at approximately the 5.25" mark. c. Type Group Total. d. Select the label and make it bold. e . Decrease the size of the label. 1i'i~~~·r~t'~I~ )rder Amo uni -1'<«<« '!is""""" -- )rder Amoun! a. In the Report Footer section , add a text object for the grand total that reads Grand Total. Crystal Reports 9.0: Level 1 b . Re-size the label and make it bold . 5i rour1 T ota l, ;;;,,--,.,.,.,.,.,.,.,,,.,.,.,,.,.,,,,,.,.,,,..,... ;Gr a nd Tota l i ,.,.,.l',.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.h 7 . Prev iew and save the report . a . Previ ew the report . b. Save the report . TOPIC c Change Group Options You've added some summaries to the report, so the report is coming together nicely. However, you want to make the group look better. In this topic, you'll change gro u p options . Suppose you take a closer look at your grouped report and realize that you don 't like the way it is displayed. You can easily change group options so that the groups display as you want them to. How to Change Group Options Procedure Reference: Once a report is grouped, there are a number of options you can change for the group(s). Some of those options include the following: • Change the field that is being used for the gro uping. • Change the sort order of the field being used for the grouping. • Change the header that is displayed for the group. To change group options: 1. On the Design tab, right-click ~he gray area of the group section and choose Change Group. The Change Group Options dialog box is displayed. 2. Disp lay the Options tab. 3. To prevent groups from breaking at the end of a page, check the Keep Group Together option. 4. To print group headers on each page , check the Repeat Gro u p Header On Each Page option. 5. Click OK to apply the settings. Less on 3: Grouping Report Data LESSON 3 53 LESSON 3 54 ACTIVITY 3-3 Changing Group Options O bjec tive: Prevent gro up s from breaking across page s and repeat the group header at the top of each page. Setup: Th e My Cu stomer Group in g .rp t report is open. Sc enario: Your bo ss take s a look at th e total s that yo u have added to the report. He is very pleased with the summaiy data. However, he is concerned abo ut th e di s play of the gro up s. He wants you to change th e report so that th e data for eac h gro up is together on one pa ge . He also wants the group header for each gro up to display at the top of each page. Figure 3-2 displays an example of the gro up s before and after the group options are changed . Acco unt No . C ustomer Name 0 rder Order 0 rder 71 888 Pty 1,1 16 12.'3010 0 $1,095 .75 71 888 Pty 1 ,282 02109101 $2,3 19 .36 71 888 Pty 2,426 1112510 1 $6,970 95 71 888 Pty 1,127 01102101 $520 .35 71 888 Pty 1 ,320 0212110 1 $1,679.25 71 888 Pty 2,9 4 6 04119102 $6,2 09 .55 71 888 Pty 2,824 03117102 $659.70 71 888 Pty 1,1 39 01105101 $2,654 .56 71 888 Pty 1,776 06125101 $5,913 .60 71 888 Pty 2,253 1 0121101 $3,4 79 .70 71 888 Pty 2,280 1 0127101 $3 ,3 55 .22 71 888 Pty 2,265 1 0124101 $2,497 .05 71 888 Pty 1,470 0313 0101 $3 ,7 3 4 10 71 888 Pty 2,221 1 0115101 $2,939 .85 Grou1> Total $55,877.49 Beach Cv c le a n d Sport 156 Beach Cyc le and Sport 3 ,07 8 06105101 $659 .70 Acco unt No . C ustomer Name 0 rder Order 0 rder BBS Ptv 71 888 Pty 1 ,088 12122100 $1 ,889 .40 71 888 Pty 1 ,7 40 06119101 $8,819 .55 71 888 Pty 2,935 04118102 $1 ,139.55 71 888 Pt y 1 , 11 6 1213 0100 $1,095 .75 71 888 Pty 1 ,282 02109101 $2 ,3 19 .36 71 888 Pty 2,426 111251 01 $6,970.95 71 888 Pty 1,127 01102101 $520 .35 71 888 Pty 1,320 0212 1101 $1,679.25 7 1 888 Pty 2,9 4 6 04119102 $6,2 09 .55 71 888 Pt y 2,8 24 03117102 $659.70 Fig u re 3-2: An example of t he groups before and after t he group options are changed. Crystal Reports 9.0: Level 1 What You Do How You Do It 1. By previewing the report, what do you notice about the data for BBS Pty? The data for BBS Pty is on page(s) --- What do you notice about the group header for the BBS Pty data? 2. With t he Design tab acti ve , display the Change Group Options dialog box. 3. Prevent the groups from breaking at the end of a page and print group headers on each page . a. Display the Design tab. b . On the gray area of the Group Header #1 section , right-click and choose Change Group to display the Change Group Options dialog box. a. Display the Options tab. b . Check Keep Group Together. , Use of the Ke ep Group Tog ether option can leave larg e gaps in a repo rt. c . Check Repeat Group Header On Each Page . P Keep Group Together P Repeat Grou p Header On Each Page d. Click OK to close the dialog box and apply the settings . 4. When you preview the report, how is the data for BBS Pty different? Save the report. Le sson 3: Grouping Report Data LESSON 3 55 LESSON 3 56 TOPIC D Add an Additional Group Your repo1t is grouped by Customer Name and it includes the summary data you need . How- ever, you want to be able to further analyze the data. In this topic , you'll add a second level of grouping to your report. Suppose you want your grouped data to be categ01ized further. In addition to having it grouped by Cu stomer Name , you want to group it by Order Date. You can create a group within a group to break down your data so that it is easier to understand and better organi ze d. How to Add an Additional Group Procedure Reference: The steps to add a second group to a report are the same as when yo u add one group. To add an additional group to a rep01t: 1 . Display the Inse11 Group dialog box . 2. From the top drop-down list, select the field by which you want to group the data. 3. From the second drop-down li st, select the order by which you want to sort th e group. 4 . Click OK. ACTIVITY 3-4 Adding an Additional Group Setup : The My Customer Grouping .rpt report is open. Scenario: Your bo ss is very sa tisfied with how the report looks. He wou ld like to be able to analyze the data within the groups even further. You s uggest adding a second level of grouping to the report that groups order date by half year within each customer group. What You Do 1 . Group the repo rt by Order Date fo r each half yea r in asc ending ord er. How You Do It a. Dis p lay the In se rt Group dia log box. b. Se t the option t o group the re po rt by the Order Date fie ld in asce ndin g order. Crystal Re ports 9.0: Level 1 2. Create the group so that a group header prints at the top of each page. c. From the This Section Will Be Printed drop-down list , select For Each Half Year. The section will be printed: I . -. a. Display the Options tab. b. Check Repeat Group Header On Each Page. c. Click OK. 3. How do the sections change based on the new group? Save the report. Change Group Order After you insert a second group in a report, you might want to display the groups in a different order. To change the order of gro up s: 1. If necessary, display the Design tab. 2. Place the insertion point on the gray area of the group header or group footer that you want to move and click and hold the mouse button . 3. ., You can only move sections that are wi thin one of the five default sections. Drag the section up or down and release the mouse button. You can also display the Section Expert dialog bo x, select the section you want to move, and use the Up Arrow or Down Arrow button. Undo/Redo After you make a cha nge in a report, yo u might immediately decide that yo u don 't want that change after all. You can quickly and easily undo an action by using the Undo command. Undo supp01ts multiple actions , so you can undo changes until the report displays as you want it to. Crystal Reports also has a Redo command that reverses an undo action. To undo an action, on the Standard too lb ar, click the Undo button. The most recent change that you made to the report is undone. Each time you click the Undo button after that, it reverses the next most recent change . To und o more than one action at a time, click the drop-down an-ow on the Undo button, and select the actions you want to undo. Lesson 3: Grouping Report Data LESSON 3 57 LESSON 3 58 To redo an action, on the Standard too lbar, click the Redo button. The mo st recent change that yo u made to the report is redone. Each time you click the Redo button after that , it reverses the next mo st recent change. To redo more than one action al a time , click the drop-down an-ow on the Redo button , and select the actions you want to redo. Changes can only be undone or redone in order f1:om the mo st recent backward. If you make a change that cannot be reversed or there are no more changes to undo or redo , the Undo and Redo commands are di sa bled. Delete a Group At so me point, you might want to delete a group. To do so : 1. Right-cl ick the gray area of the group section and choose Delete Group. A warn- ing message box is di s played . 2. Click Yes to delete the group . ., You can undo the deletion of a group. ACTIVITY 3-5 Changing G roup Order Setup : The My Customer Grouping.rpt report is open. Sc enario: Your boss is pleased with the addition of the second level of grouping. However, he isn 't sure he likes the order of the groups in the report. You suggest changing the order of the groups so that the report is grouped by Order Date , and then by Customer Name. Crystal Reports 9.0: Level 1 What You Do 1. Reverse the order of the groups. How You Do It a. Display the Design tab. b. Place the insertion point on the gray area of Group Header #1 and click and hold the mouse button. The mouse pointer changes to a hand and the Group Header #1 and Group Footer #1 secti ons are highlighted . Grouo Header #1: Grouo Heade r U2: r ------- !Detail s Grouo Footer U2: G1ouo Footei ltl : c. Drag down until a highlighted box is dis- played around the Details section. Gl'Oll:l Header #1: Grouo Header t1 2: Details Grouo Foote r tl2: Grouo Footer #1: Release the mouse button. 2. When you preview the report, how is the data displayed differently? 3. Return the groups to the previous order and save the report. Lesson 3: Group ing Repo rt Data a. On the Standard toolbar , click the Undo button EJ to undo the previous acti on of c hanging the order of the groups . b . Save the report. LESSON 3 59 LESSON 3 60 TOPIC E Format a Group Header You 've added some groups to your repo11. Each of those groups contains a group header. At some point , you might want to change the appearance of the gro up headers. That's what you 'll do in this topic . A s you 've noticed when you add groups to a repo11, the grouped data is set off from the rest of the data in the report. Suppose you want to make the data that is grouped stand out even more. To further set off the gro uped data, you can format group headers. By doing so, atten- tion is drawn to the groups because the fonnatting makes them stand out. Also, the formatting makes the report easier to view. How to Format Group Headers Procedure Referen c e : There are many changes you can apply to group headers to change their appearance . For example , you can change the font, style, and size of a group header. You can also change the location of a group header. To fo1mat group headers : 1. Select the group header you want to change . 2. To change the font style: a. On the Formatting toolbar, click the Font Face drop-down aITow to display a li s t of available fonts. b. Select the desired font. 3. To change the text to italics , on the Formatting toolbar, click the Italic s button. 4. To indent a group header, select the Group Header field and press -7 as needed. Control Movement with Arrow Keys You have moved fields and objects in your repo11s by dragging and moving guidelines. You can also use the aITow keys to control the movement of fields and objects. Simply press the approp1iate aITow key to move the field . The amount of the movement for each time an arrow key is pressed is detelTJlined by the field grid size . The grid is a series of row and column coordinates. Like guidelines, you can use the grid to as sist you in aligning fields. ' You can change the grid size of a field by usi ng the Layout tab of t he Options dialog box . Crystal Reports 9.0 : Level 1 ACTIVITY 3-6 Formatting Group Headers Setup: The My Customer Grouping.rpt rep011 is open. Scenario: The gro up s you 've created in th e My Customer Grouping report make th e group data stand out. However, yo u want to increase the viewing ease of the gro up s. Yo u think yo u can accom- plish that ta sk by formatting the group headers. An example of the forma tted gro u p headers is di splayed i n Figure 3-3. Account No . Alley Cat Cycles Figure 3-3: An example of the formatted group headers. What You Do 1 . With the Design tab active , format the Group Header #1 to Verdana and italic. How You Do It a. Display the Design tab. b . Select the Group #1 Name field. c. On the Formatting toolbar , from the Font Face drop -down list, select Verdana. d. On the Formatting toolbar , click the Ital- ics button 0 . 2. When you preview the formatted group header, what is wrong with the group header text? A cc ou nt No . C ust omer Name Allev Cat Cvcles 112000 3. Re-size the group header so that all the text is displayed. Lesson 3: Grouping Report Data a. Verify that the Group #1 Name field is selected. b. Using the bottom -middle sizing handle , increase the height of the group header so that all the text is displayed. 'fl Fields can also be re-si zed by pressing Shift and using the ar row keys on the ke yboa rd . LESSON 3 61 LESSON 3 4. Indent the Group Header #2 field to approximately the 0.5" mark. Pre- view and save the report. TOPIC F Filter by Group a. In the Group Header #2 section, select the Group #2 Name field. [Group # 1 f\lame ~ ""'' ',, '," ;, , ';, ,;; ', ''"'''"'"""' "" "' _,_9X!?H~,.~?,,!";131)),~,, """"'""";= b . Press ~ three times to indent the selected field to the 0.5'' mark. fil If the field doesn 't move using the arrow key , you can move the associated guideline or drag the field to the 0.5'' mark. c. Select the Preview tab. d. Save the report. You've looked at many options for changing the display of your report data using groups, including changing the group order and formatting group headers. Another option you might want to use is to display only certain group data. In this topic, you will filter your report by group. A s you have seen when you group or summarize data in a rep01t, all the grouped data is included in the rep01t. However, there might be instances where you don 't want to display all the grouped data . Suppose you want to display only those customers who have orders totalling more than $500. By filtering data by group, you can limit the record s to those that meet a spe- cific crite1ia based on a group field value. What is a Formula? Definition: Aformula is an equation that pe1forms operations on data . It can be used to perform mathematical operations , such as additio n or subtraction , or it can be used to compare , join, or extract data. In Crystal Reports, a formula consists of two parts: components and syntax . The components are the parts of the formula . You can use any of the fol- lowing components when you create a fo1mula in Crystal Rep01t s : • Fields • Numbers 62 Crystal Reports 9.0: Level 1 • Text • Operators, which are the actions you can use in your formulas, such as add ( +) and subtract (-), to specify the type of calculation that you want to pe1form • Functions • Contro l structures, which are a way to express business logic , such as "If' • Group field values, which summarize a group • Other fonnulas Syntax are the rules that you mu st follow when organizing the components of a formula. Some of the basic rules you must follow in Cry stal Repo11 s include th e fol- lowing: • Text string s mu st be enclosed in quotation marks. • Arguments mu st be enclosed in parentheses, when necessary. • Referenced formulas must be identified using a leading @ sign. When you create formulas in Crystal Reports, you can use Crystal syntax or Basic syntax. A report can include formulas that use both type s of sy ntax , but Basic sy ntax and Crystal sy ntax cannot be mixed within the same formula. Example: Some examples of formulas include the following: • {Orders.Ship Date}-{ Orders.Order Date} -The value in the Order Date field is being sub tracted from the value in the Ship Date field to obtain the difference . • {Orders Detail.Unit Price}*{Orders Detail.Quantity} -The value in the Unit Price field is being multiplied by the value in the Quantity field to obtain the order total. Types of Formulas in Crystal Reports You can create four different types of formulas in Crystal Reports. Those formulas are outlined in the following table . Formula Type Report Conditional Fomrntting Selection Search Function A formu la that stands alone in a report. A formu la that changes th e layout and design of a report. You create th e formula usin g th e Format Editor to access th e Formula Editor. A formu la that specifie s and limits th e records and groups that are di spla yed in a report. You normall y create th e formula using th e Select Expert. A formula used to locate data in a rep ort. You normall y create th e fonnula by using th e Search Expert. Most of the formu las that you will create in Crystal Reports will be report or condi- tional formatting fommlas. Conditional formatting is formatting that is applied based on speci fic criteria-for example, applying italic formatting to record s representing sales from Region B . Lesson 3: Grouping Report Data LESSON 3 63 LESSON 3 64 How to Filter by Group Procedure Reference: When you wa nt to filt er data by group , you need to create a gro up se lecti on fo rmul a . A selection fonn ula is a filter use d to limit th e data included in a re port. Selecti on fo r- mul as can be crea ted at th e record level or th e gro up leve l. To fi lter a re port by gro up: 1 . Ri g ht-cl ick th e group fi eld fo r whi ch yo u want to create a fi lte r and choose Selec t E x per t. Th e Se lect Ex pert dialo g box is di s pl aye d . 2. On the ac ti ve ta b, use th e dro p-d own li st to sele ct th e a ppropriate compari so n o perato r. 3. In th e second li st box, se lect a va lu e fr om th e drop -d ow n lis t or e nt er a va lu e . 4. C li ck OK to filt er th e re port b y th e se le cted group. ACTIVITY 3-7 Filtering by Group Setup: The M y Cu stomer Gro upin g .rpt re port i s o pen. Scenario: To he lp in crease co mp any sale s, th e Marke tin g depart me nt wo uld like to co ntac t all customers who have prev iou s ord ers totallin g $1,000 or le ss . Th ey're goin g to offer th ose custom er s a special promoti on for placin g an ord er. The M arketing M anager wa nts yo u to pro v id e th e ap pro priate data to her. What You Do How You Do It 1. Before you filter the report, how many pages of data are displayed? 2. With the Design tab active , display the Select Expert dialog box for the Group Footer #1 Sum Of Orders. Order Amount field. a. Display the Design tab. b. In the Group Footer #1 section , right-click the Sum Of Orders.Order Amount field and choose Select Expert to display the Select Expert dialog box . The report already contains selection criteria for the Orders.Order Amount field. 3. When you click t he Show Formula button, the Sum Of Orders.Order Amount tab indi- cates that you will be creating a formula for that field. Click Hide Formula . Crystal Reports 9.0: Level 1 4. What type of formula is Orders.Order Amount? Click Hide Formula. 5. Create a group selection formula for the Sum Of Orders.Order Amount field that displays those groups with an order total that is less than or equal to 1,000.00. a. For the Sum Of Orders.Order Amount field , using the drop -down list, select Is Less Than Or Equal To. Ord er s.Orde r Amount Sum of Order s.Order Amount I is less than or equal to iJ b. In the second list box , type 1000.00. c. Click OK to filter the report. 6. When you preview the filtered data, how many pages of data are displayed? Save the report. T eP1c G Create a Top N Sort Group Congratulations. You 've filtered data by group s. Th ere are other optio ns for limiting th e data in your reports. In thi s topic, yo u 'll create a Top N sort gro up to do that. Suppose yo u want to display only the top five sales people in each region. A Select Expe1t filter cannot be used to accomplish this. Howev er, yo u can easi ly create a smt gro up that will include o nl y those salespeo pl e yo u specify. B y doin g so, you can quickly foc us only on the sort gro up data. What is Top N? At some point, yo u might want to display onl y the top or bottom gro up s in a report-for exa mple , the regions th at produce th e mo st orders. You can use the Top N/S ort Group Expert for o btai nin g that group information. The Top N sort gro up is availa bl e only in re ports that have summary fields. Lesson 3: Grouping Report Data LESS •N 3 • 65 LESSON 3 • 66 How to Create a Top N Sort Group Procedure Refere nce: To create a Top N sort group: 1. On the Ex pe1t Tools toolbar, click the Group Sort Expert button. The Group Sort Expert dialog box is displayed. 2. From the For Thi s Group Sort drop-down list, select Top N. 3. In the Where N Is text box , enter the number of Top N values you want to display. 4. If desired, uncheck the Include Others, With The Name option so that the other record s don 't display. 5. Click OK to create the group . Delete Selection Formulas Before you create a Top N sort group, and at other times , you might need to delete a selection formula so that the data displays the way yo u want it to. To do so: 1. Display the Select Expert dialog box. 2. Select the tab that contains the selection fo1mula that you want to delete. 3 . Click Delete to delete the selection fo 1mula. · 4 . Click OK to close the Select Expert dialog box. ACTIVITY 3-8 Creating a Top N Sort Group Setup : The My Customer Grouping.rpt report is open. Scenario: You need to determine the customers with the top five order amounts . Your boss wants to send them a special gift for being such great customers . Crystal Reports 9.0: Level 1 What You Do 1. Remove the selection formula for the Sum Of Orders.Order Amount field . 2. Create a Top N sort group that dis- plays only the customers with the top five order amounts . Lesson 3: Grouping Report Data How You Do It a. Display the Select Expert dialog box. b. If necessary , in the Select Expert dialog box , select the Sum Of Orders.Order Amount tab. c. Click Delete to delete the selection formula . d. Click OK to close the Select Expert dialog box. e. If necessary , click Refresh Data. a. On the Expert Tools toolbar, click the Group Sort Expert button ~ • The Group Sort Expert dialog box is displayed with a tab for the Customer .Customer Name group. b. From the For This Group Sort drop-down list, select Top N. The Based On drop- down list box contains the Sum Of Orders .Order Amount field , as that is the only summary you've created for that group . c . In the Where N Is group text box , verify that 5 is displayed. d. Uncheck the Include Others, With The Name option so that the other records don 't display . Im Group Sort Expert t:t Anali•ze 1epo1l 01 cha1l 1e.."\lll s by tai'ing Uie Top N 01 SOil of lotak. Customet.Custome1 Name I jTopN ::J bMed on I Sum of D1defs.01def AmO<.nl r lncl.Jde Olhe!t, with the name: J5 J' • ::J e. Click OK to add the Top N filter criteria. LESSON 3 67 LESSON 3 3. The top company is Psycho-Cycle with a sum of ----- Save and close the report. '!' Grand totals are calculated before a Top N sort and group filter is applied and will , therefore , be incorrect. Grand totals should be removed from Top N sort and group reports . (As an alternative , they can be manu - ally created using variables in Running Total fields , which goes beyond the scope of this course .) Lesson 3 Follow-up Yo u now kn ow how to create group s and s ummaiize d a ta in your re po1ts . Yo u in se rted group s and summari zed th e d a ta in th e gro up s . Yo u al so chan ged the di spl ay of th e group s. B y gro up - ing and summ arizin g th e data in your re port s, th ey will be be tter orga n ized and th e d ata w ill be more meanin gful. 1. What types of summaries will you add to your reports? 2. In grouped reports, how might you change the display of the groups and the group headers? 3 . What types of Top N sort groups will you create? 68 Crystal Reports 9.0: Level 1 LESSON 4 Building Formulas Lesson Objectives: In this lesson , yo u will build formulas to add non-database data to a report and display data differently. You will: • • • • • • • • Create a fonnul a that calcul ates field data . Edit a formu la . Display groups based on a formula . Delete a fo1m ul a . Create a form ul a that filters data based on specific criteria . Create a formu la that fi lters a report based on more than one condition where the data must meet any one co ndition. Create a parameter field . Create a fo1m ul a that includes null values. ~ 1 p.ljt « c-::-.. .,,, ~~ -\IV'~"" .J Lesson 4: Build in g Formulas LESSON 4 Lesson Time 2 hour(s) to 2 hour(s), 30 minutes 69 LESSON 4 70 Introduction So far, you've created some simple rep011 s that contain data that you have obtained from a database. At so me point, you will want to create calculated fie lds from the data in your data- base or maybe extract paits of String and Date fields. In this lesson , you will build formulas that allow you to add calculated data to your re ports. While your databa se must contain all the information necessai·y to create a rep011, often that data isn't in the format you want. For example, your database contains an order amount and the percentage that was used for shippin g charges, but it doesn't include the actual s hipping charges for that order. By using formulas, you can enhance your reports by adding calculated fields that aren't available in the database. You can also format your reports so that the data displays the way you need it to . ToP1cA Write a Formula You 've added data to reports by using fields that exist in a database. Suppose you need to dis- play data in a repo11 that isn't available in a database . In this topic , you will create formulas to display that data. When yo u need an item that you can't purchase anywhere, so metimes you can make it from scratch. The sa me situation is tme when you' re working with data in Crystal. Suppose you want to display the total cost of unit s so ld per customer. You don 't hav e a database field with that amount, but you do have unit price and quantity field s . To obtain the total cost, you could create a formula that multiplies the unit price by the quantity. Therefore, you ca n add data to a repo11 that you can 't obtain directly from a database by using a fommla. Operators There are many different operators that you can use in your reports to calculate the compo- nents of formulas. Some of th e more common operators ai·e outlined in the following table. Operator Definition Example addition ( +) Adds values 6+7 subtraction (-) Subtracts value s 197 -113 multiplication (*) Multiplies val ues 12 *12 division (/) Divides va lue s 36/6 equal x is eq ual to y {Orders.Quantity }=7 less than x is le ss than y {Orders . Quantity} <50 greater than x i s greater than y {Orders .Quantity} >50 concatenate (&) Combines valu es { FirstN ame} & { La stN ame} and Includes the values x and y {Orders. Quantity} =50 and {Ord ers.Sa lesperson} ="PS" Crystal Reports 9. 0: Level 1 Operator or if-then-else Definition Includes the valu e x or y If th e stateme nt fo ll ow in g the word IF i s tru e, return what co mes after the THEN state me nt ; ot herwi se , return what comes after th e ELSE state ment Example {Orders.Quantity }=50 or {Orders.Salesperson} ="PS" If {Employee.Dept }="No1theast" Then {Empl oyee .S alary}* .08 Else {Employee.Salary}* .06 Fe rmul • Worksh •ll When you create and edit a formula , you will use th e Formula Workshop to do so. The For- mula Work shop is a central place where you can access and modify all the formulas in a report. The com ponents of the Formula Workshop are outlined in th e following table. Component Formula Workshop toolb ar Formula Editor wi ndow Formula Editor toolbar Report Fields tree Functions tree Operators tree Formula Text window Function Contains co mmon co mmand s for creating and edit- ing formulas. The primary formula editor. Contains co mmon fo rmula editing co mmand s. A component of th e Formula Editor window, it contains all databa se fields available in th e report, and formulas and groups already created in th e report. A component of th e Formula Editor window, it contains th e fun ctions yo u can use to create formulas. A component of th e Formula Editor window, it contains th e operators yo u can use to create formulas. The area yo u use to create and edit th e formula. 'f If you change the syntax from Crystal to Basic or vice versa , the list of functions and operators will change . An example of the Formula Work shop is di s played in Figure 4-1. Lesson 4: Building Formulas LESSON 4 71 LESSON 4 72 @ Re port Custom Func tion s L+J (p Rep osi tory Custom Fun cti ons E'l CJ Formul a Field s X•1 j11Ddd! 2J SQL Expr ess ion Fi elds IP _] Selection For mula s Formula Workshop tool bar [£ ~ Formattin g Formula s Formula Editor window Figu re 4 -1 : Th e Form ula Wo rks hop. How to Write a Formula Procedure Reference: Report Fields I ~.!l -~.!l ~ ~ ......I .!lA Operators t ree F ormula Text w i ndow W hen you 're ready to create a formu la , the Formula Workshop makes it easy. To cre- ate a formula: 1. On the Standard toolbar, click the Field Explorer button to display the Field Explorer. 2. Select Formula Fields. 3. On the toolbar, click the New button to dis play the Formula Name dialog box . 4. In the Name text box , enter a name for your formu la. 5. Click Use Ed itor to display the Fommla Workshop. 6. In the Formula Text window, enter the components of the formula by typing them or by double-clicking the components in the ass ociated tree. 7. On the Formula Editor tool bar, click the Check button to check for errors in the f01mula. A mes sage box is di s played indicating whether or not there ar e errors in the formula . 8. If there are en-ors in the formula, you mu st close the message box and resolve the errors. 9. When the foimula is en-or-free , click OK to clo se the me ss age box . 10 . On the Formula Editor toolbar, click Save· to save the foimula . 11. On the Formula Workshop toolbar, click Clo se to close the Formula Workshop . Crystal Reports 9.0: Level 1 12. Place the formu la fie ld where you want it in your report. 13 . Preview th e repo11 . ACTIVITY 4-1 Writing a Formula Data Files: • C ustomer Order Totals.rpt Setup: No files are open in Cry sta l Re ports. Scenario: You've created a re po11 named Customer Ord er Tota ls that summ arizes th e order information for eac h c ustomer. You wa nt th e repo11 to incl ud e th e order total. However, th e data base that yo u used to create th e report does n 't includ e that field. Yo u think yo u can crea te th e total usi ng a f01mula. Figure 4-2 di s plays a samp le of the report wit h the new formul a field. C ustom er Name 7 Bikes For7 Brothers 7 Bikes For 7 Brother s Against The Wind Bikes Aga in st The Win d Bi kes AIC Childrens A IC C hi ldren s Customer Order Totals O rder I D Unit Pri c e Quant ity 0 rd er Tot al NV 05/26/01 3,054 $5 3.90 $5 3.90 Number of Orders NY 05/26/0 1 3,0 55 $4 79 .85 $4 79 .85 Number of Orders Hong K ong 06/21/0 1 3,153 $33 .90 3 $1 01.70 Number of Orders Figure 4-2: The Customer Order Totals.rpt report with the inserted Order Total for - mula field. What You Do 1. Open the Customer Order Totals.rpt report and save it as My Customer Order Totals.rpt. Switch to the Design tab. Lesson 4: Building Formulas How You Do It a. Display the Open dialog box. b. Open Customer Order Totals.rpt. c. Save the report as My Customer Order Totals.rpt. d. Make the Design tab active. LESSON 4 73 LESSON 4 74 2. What formula would you use to calculate the order total for each order? 3. Display the Formula Workshop for the new formula, Order Total. a. If necessary, on the Standard toolbar, click the Field Explorer button [iJ to display the Field Explorer. b. Select Formula Fields. c. On the Field ~orer toolbar , click the New button l_@J to display the Formula Name dialog box. d. In the Name text box , type Order Total. Formula Name '# •. Name : I order Tota l Use Expert 11 Use Editor · I __ c_an_ce_I _ e. Click Use Editor to display the Formula Editor window in the Formula Workshop. 'f The Use Expert option would be used only for reports containing custom functions . Cu st om functions are covered in the Crys tal Reports 9.0: Level 2 Element K course. Crys tal Reports 9.0: Level 1 4. With the Formula Workshop maxi - mized , insert the components for the Order Total formula that calcu- late the order total. 'fl If desired , you can ty pe the components of the formula in the Formula Te xt windo w. Lesson 4: Bu ilding Formulas a. Maximize the Formula Workshop. b. In the Report Fields tree , expand xtreme.mdb. c. In the Report Fields tree , expand the Orders_Detail table. d . In the Report Fields tree, double-click Unit Price to place the field in the For - mula Text window. e. Press Shift and type* to add the operator for multiplication. 'fl If desired , you can use the Operators tree to insert the multiply symbol f rom the Arithmetic folder. f. In the Report Fields tree , double-click Quantity. {Orders_Detail.Unit Price}•(Orders_Deta il .Quantity} LESSON 4 75 LESSON 4 76 5. Check the formula for errors, save, and close the Formula Workshop . 6. Insert the Order Total formula to the right of the Quantity field, close the Field Explorer, and preview the report. a. On the Formula Editor toolbar , click the Check button I x,;2 I to check the formula for errors. A message box is displayed . .'~ No errors found. OK .p If an error is found , Crystal will try to determine the cau se of the error and place the insertion point in th at position . b. Click OK to close the message box. c. On the Formula Editor toolbar, click Save I J;l save I to save the formula . d. On the Formula Workshop toolbar, click Close Iv c lose I to close the Formula Workshop. The Order Total formula is now displayed in the Field Explorer. a. From the Field Explorer, drag the Order Total formula to the right of the Quan- tity field. t::f3 • • • I ' ' '7 ' ' ' IQ' . ~ , 0 rd er Tot al b. Close the Field Explorer. c. Preview the report. 7. The order total for AIC Childrens is , which equals , the unit price, times the quantity, which is . ~~~ ~~- Crystal Reports 9.0: Level 1 8. What do you notice about the font for the Order Total field? 9. The font for the Order Total field is 10. Using the Design tab , change the font for all fields and field labels to Arial 10 . 11. Re-size the text object for the sum- mary field so that all the text is displayed. Preview and save the report. Lesson 4: Bu ilding Formulas . The font for the Quantity field is ---- a. Display the Design tab. b. Choose Edit ~Select All to select all fields and field labels in the report. c. From the Font Face drop -down list , select Arial. a . Using the bottom -middle sizing handle , re-size the text object for the summary field so that all the text is displayed. /;Number of Orders ',;.Order ID ..... ( UU iU UC<<<# («<U CU i(U i !Ii ..I ·rota/ Number of . , , s.Order I~ b . Preview the report. c . Save the report. LESSON 4 77 LESSON 4 TOPIC B Edit a Formula Now that you know how to create a fo1mula, you might need to change an existing formula. Maybe you need to add or delete a component. In thi s topic, you will edit a formula. Suppose you create a fonnula and it is missing a component. You forgot to add some punctua- tion to a fo1mula that contains text field s. You can easily add th at component to the formula by editing the fo1mul a . Editing an existing fo1mula is easier than starting over. How to Edit a Formula Procedure Reference : To edit a formula: 1. If ne cessary, di splay the Field Explorer. 2. Under the Formula Fields category, select the formul a you want to edit. 3. Click Edit to di s play the Fomrnla Works hop. 4. Edit the formula as needed. 5. Check the formula for en-ors. 6 . Save the formula. 7. Close the Formula Workshop. 8 . If neces sa ry, close the Field Explorer. Order of Operations When you create or edit a formula, the fmmula might contain more than one arith- metic expression that contains several operators. In that case, the order of operations becomes a factor. Crystal Reports typically evaluates fo1mulas in the following order: 1. From left to 1ight. 2. Then, follows the order of operations rule from basic math (multiplica tion and divi sion are performed first from left to right, and then addition and subtraction are pe1formed from left to right). The order of operations can be changed by adding parentheses. The following list dis- plays the ruithmetic operators from hlghest precede nce to lowest. 1. Exponentiation (") 2. Negation (-) 3. Multiplication and di vision (*, /) 4. Integer division (\) 5. Modulus (Mod) 6. Addition and subtraction ( +, -) 78 Crystal Reports 9.0 : Level 1 ' While most formulas are evaluated from left to right , evaluation time statements and the type of for- . mula can cau se evaluation to occur ear lier or later. See the topic "Report Process ing Model " in Crystal Reports Online Help . ACTIVITY 4-2 Editing the Formula Setup: The M y C ustomer Order Totals.rpt report is displayed. Scenario: After previewing th e My C ustomer Order Totals repo tt, yo u realize th at th e 01;der total need s to include the company 's standard shippin g charges of $10 .95. What You Do How You Do It 1. How would you edit the formula to include the shipping charges? 2. Display the Formula Workshop for a. Display the Field Explorer. the Order Total formula. b. Expand the Formula Fields category. c. Select Order Total. d. On the Field [S]lorer toolbar , click the Edit button ' to display the Formula Workshop. ' You can also right-click the formula in the report and choose Edit Formula . 3. Edit the formula to include a ship-a. In the Formula Text window , place the ping rate of 10.95. insertion point at the end of the formula. b. Press Shift and type + to add the opera - tor for addition. Lesson 4: Building Formulas LESSON 4 79 LESSON 4 4. Check for errors and save your changes . Return to the Preview tab. Close the Formula Workshop. c . Type 10 .95 to e nter the shipping rate . {Or de r s Deta 1l .Un 1 t Pr1c e }W(Or cters De ta 1 l .Qu ant1 t y }+10.95 a. Click Check. b. Click OK to close the message bo x. c. Click Save . d. Click Close to close t he Formula Workshop. 5. The order total for 7 Bikes For 7 Brothers is , which equals , the unit price, times the quantity, 1, plus the shipping charges of --- --- Save the file. TOPIC c Group by Formula In additio n to creating basic mathematical formul as, you mig ht need to create form ul as that allow yo u to create gro up s based on more than one field. That 's what you 'll do in this topic. Suppose yo u need a fie ld in yo ur report that includes the reg io n and state where e mpl oyees live . However, th e data base contains the regio n and state as se parate fields . Yo u can easily cre- ate a for mul a t hat combines both fie lds in to o ne, allowing yo u to display the data as you need to in yo ur r epo1t. How to Group by Formula Procedure Reference: To gro up a repo1t by using a form ul a: 1. Using the Field Ex pl orer, create a new formula with a name of yo ur choice. 2. In the Formul a Works hop , e nter the components of th e formula. 3 . Check the for mul a for eITor s. 4 . Save the form ul a. 5. Close the Formul a Workshop , and close the Field Exp lorer. 80 Crysta l Reports 9.0 : Le vel 1 6. Using the Change Group Options dialog box, change the field for so11ing and grouping the report using your new formula. ACTIVITY 4-3 Grouping by Formula Objective: Create a fonnula that displays a group header with customer name and ID. Setup: The My Customer Order Totals.rpt report and the Field Explorer are displayed. Scenario: In the My Customer Order Totals report, grouping by customer name is fine . However, you have been informed by your boss that, in the future, the company might have customers with two locations and different billing departments, requiring a separate customer ID. Yo u could simply group off the Customer ID fie ld , but then the records would be s011ed by the customer ID. Therefore, you need to change the way the rep01t is grouped. Creating a field that struts with the customer name and includes the C ustomer ID field will accomplish uniqu e gro upin g by ID, plus maintain alphab etization by customer name. What You Do How You Do It 1. What formula would you write to create a field that starts with the Customer Name field and includes the Customer ID field? 2. Display the Formula Workshop for a new formula named Group Sort. • Lesson 4: Building Formulas a. In the Field Explorer , select Formula Fields and display the Formula Name dialog box. (Click the New button.) b. Name the formula Group Sort. c. Display the Formula Workshop for the Group Sort formula. (Click Use Editor.) LESSON 4 / 81 LESSON 4 (( ( ,, 82 3. Insert the components for the Group Sort formula that strings the Customer.Customer Name field and the Customer .Customer ID field with a space in between. 4 . Check the formula for errors, save the formula, and close all open windows. 5. Change the existing group so that the report is grouped by the Group Sort formula. Save the report. 1' a. Insert the Customer.Customer Name field. b. Type li" and press the spacebar. Type(" to string together the Customer.Customer Name field with a space and enter an opening parenthesis . c. Type li to prepare to string the Customer Name field with the Customer ID field. d. From the Customer table , add the Cus- tomer ID field. e . Type li")" to string the closing parenthe - sis to the Customer ID field. {C ustomer.Customer Nai:ne }r 'r ("&{C ustomer.Customer ID }!")" a. Check the formula for errors. b. Save the formula. c. Close the Formula Workshop. a. Display the Change Group Options dialog box. (Right-dick the gray area of the Group Header #1 section and choose Change Group.) b. Set the option to sort and group the report by the @Group Sort formula in ascending order. Click eK. 'ft The Customer ID field might display with decimals. This can be controlled by either changing the default decimal setting for numeric fields , or by using the ToText function . The final formula would read : {C ustomer.Customer Name) & "(" it ToText((Customer.Customer IDl,O) &")". d. Save the report. Crys tal Reports 9.0: Level 1 TOPIC I lt elete • Fe rmul • So far you 've created a basic mathematical formula and a formula that creates gro ups. At so me point, you might have a fo1mula in a report that you no longer need . In this topic, you will delete a fom1ula. The Formula Workshop s low s down as new formulas are added. Therefore, deleting unneeded formulas will boost pe1formance of Crystal Repo1t s as you build your repo1t. How to Delete a Formula Procedure Reference: Formulas used for display purposes can easily be removed from a repmt by simply deleting the formula from the Field Explorer. Deleting the formula field from the Field Explorer will also remove the formulas from th e report . However, if the formula field is use d to filter, group, or conditionally fo1mat data, deleting the formula become s a two-step process. In that case, you cannot delete the formula from the Field Explorer without first deleting every occunence of the formula in the report. As a general rule , it is good practice to remove all occunences of a formula before removing it from the Field Explorer window since this would alert you to any "hid- den " places where the fo1mula might reside. To delete a formula from a report: 1. If applicable, select the formula field and it s label in the report. 2. Press Delete. If applicable, remove all references to the formula in other report locations (select expe1t formulas, grouping formulas , or conditional format formulas). 3. Disp lay the Field Explorer, and if necessary, expand Formula Fields. 4. Select the formula you want to delete. 5. On the Field Explorer toolbar, click the Delete button. '! If you delete a formula in the Field Explorer and that formula is located somewhere else in the · report , a warning message will be displayed . 6. Close the Field Explorer window. Lesson 4: Building Formulas LESSON ~ 83 LESSON 4 84 ACTIVITY 4-4 Deleting a Formula Setup: The My Customer Order Total s.rpt rep011 and the Field Explorer are di sp layed . Scenario: You no longer need the Order To tal form ul a field in your repo11. Therefore, yo u wi ll delete it from the re port and fro m th e Fi eld Explorer. What You Do 1. Delete the Order Total formula from the report. How You Do It a. Select the @Order Total field and its label. b . Press Delete to delete the field and its label from your report. 2. Has the formula been deleted from the report? 3. Delete the Order Total specification from the Field Explorer. a. Select Order Total. b . On the Field Explorer toolbar, click the Delete button [RJ to delete the formula specification . c. Close the Field Explorer. Crys tal Reports 9.0: Level 1 TOPIC E Build a Filter by Multiple Criteria Another way to affec t the di s pl ay of data in your re po1t s usin g formulas is to create a filter th at displays s pecified data based on more than one field. In thi s topic, th at's what yo u'll do. Suppose you have a report th at contains a large amount of data. You want to display records based on th e co nte nt s of more th a n one field. By creating a formula, yo u can acco mpli sh that task . Onl y th e data th at yo u wa nt to view wi ll be displayed, sav in g yo u th e aggrava tion of having to work with more data than yo u need. Also , th e report will be easier to read because less data is displayed. How to Build a Filter by Multiple Criteria Procedure Re feren ce: You can create a for mul a that filt ers a repo1t ba sed on more than one field when all components of the for mul a mu st be tm e . This procedure results in a form ul a where the word AND separates the filter ciiteria. When AND is used in a se lection formula, th e data extracted must meet all conditions of th e criteria. To create th e form ula: 1. Select th e first report field on which yo u want to filter. 2. Display th e Select Expert dialog box. 3. From th e drop-down li st, se lect the appropiiate comparison operator, or ente r o ne in the text box . 4. In the second li st bo x, se lect a va lu e from the drop -dow n li st or enter a va lu e . 5. Select the New tab, an d repeat ste ps 3 and 4. 6. Click OK to filter th e data. 7. If necessary, refresh the data. Lesson 4: Building Formulas LESSON 4 85 LESSON 4 86 ACTIVITY 4-5 Building a Filter by Multiple Criteria Objective: Filter th e re p011 so th at o nl y Massa chu sett s cu stomers with orders betwee n ce1ta in dates are di spl ayed . Setup: The M y C usto mer Order Totals.rpt report is di s pla yed. Scenario: Your boss need s to know what cu stom ers in Massachu se tt s had ord ers betwe en 111/2001 and 113 112001. Th e easies t way to obtain th e inform ation yo ur boss needs is to fi lter th e repo11 based on th e c1ite1ia your boss has g ive n you. What You Do 1. Create the first part of the se lec - t i on formula that will disp lay those customers equ a l to MA. How You Do It a. Di splay the Selec t Expert dialog box for the Customer.Region field . (S elect the Customer .Region f i eld and click the Sele ct Ex pert button .) b . On t he Customer.Regio n tab , set the comparison operator to Is Equal To. c . In t he second t ext box , type MA . ;lJ Select E>cpert · . Custom er.Regi on I <Ne w> I j is equa l to Crysta l Reports 9.0: Level 1 2. Create the second part of the selec- tion formula that will display those customers who had orders between 1/1/2001and1/31/2001, and filter the report. a. Select the New tab. The Choose Field dia - log box is displayed . b. Double-click Orders.Order Date. c. On the Orders.Order Date tab , set the comparison operator to Is Between . d. From the second drop-down list, select 1/1/2001. '! If the required date isn 't in the drop-down list , users can add their own dates , but the y must follow the same formatting that gets used when a date is selected . It is recommended that the user pull a date from the drop-down list bo x and then manually modi fy it to match the needed date . e. From the third drop-down list, select 1/31/2001. C:U.oma .Regon Ordel• Ordel Dot• I <How> j l•sbetween :::J j1 1112001 and Delete lililrmll :.:J a ....... Sl'IO'r¥Focroola »> f. Filter the report using refreshed data. 3. customers in MA had orders between 1/1/2001and1/31/2001. Lesson 4: Building Formulas LESSON 4 87 LESSON 4 88 TOPIC F Modify a Filter with Multiple Criteria to Create an OR Condition You have created a filter that display s specified data where the data mu st meet multiple criteria. In this topic , you 'll look at a filter which will include records that mu st me et only one of multiple criteria. Suppose you run a retail store and hold four seas onal sales. You would like to see the total sales f<;>r all four seaso nal sa le d ate s . By creating a formula that looks for sale s held on any of th e four seasonal sale dates, yo u can co mbin e thi s information in one easy -to-read report. How to Modify a Filter with Multiple Criteria Procedure Refere nc e : To create a formula that filters a report based on more th an one co ndition where th e data mu st meet any one condition: 1. Di sp lay the Select Expe1t dialog box. 2. Click Show Formula . 3. Click Formula Editor. 4. Modify the formula to add new conditions, se parating each condition with the word OR. 5. Ch eck yo ur formula for en-ors. 6. Save and close the formula. 7. If necessary, refresh the data . ' Selection formulas created using the Se lect Expert dialog box are limited to the AND operator between criteria . To include the OR operator in selection formulas , the formula can be modified using the Show Formula window or the Formula Workshop. Using the Formula Workshop pro- vides the most control , including access to all the formu la co mponents that you would need to build a complex formula . Crystal Reports 9.0: Level 1 ACTIVITY~-' Modifying a Filter with Multiple Criteria Objective: • Fi lter the report so th at Massachusetts and California custo mers with orders between certai n . dates are di splayed. Setup: Th e M y Customer Order Totals.rpt report is di sp la yed . Scenario: You created the report to sho w January 2001 sa les from Massachusetts customers. Your boss now asks that the sa me report include California customer sa les for the sa me time period. The ea siest way to obtai n the information yo ur boss needs is to modify the ex isting filter in the report based on the criteria yo ur boss has given yo u. What You Do 1. Modify the selection formula to add customers equal to CA. Lesson 4: Building Formulas How You Do It a. Display the Select Expert dialog box. (Click the Select Expert button.) b. Click Show Formula to display the con- tents of the formula. c. Click Formula Editor to display the For - mula Workshop. d. Position the insertion point at the end of the formula. e . Type OR{Customer.Region}="CA"). f. Put a left parenthesis before the first occurrence of {Customer.Region}. {Orders.Order Dat.e ) in Date (200 1, 01, 01) to Date (2001, 01 , 3 1 ) and ( l C u:5to~r. Region) • "IU."OR{C u sto~c:. Rll!!:g 1 onJ •"Cl.") LESSON 4 89 LESSON 4 l 90 2. Check, save, and close your formula. a. Check the formu,la for errors. b. Save the formula and close the Formula Workshop. c. Click OK to close the Select Expert dialog box. d . Filter the report using refreshed data. 3. customers in MA and CA had orders between 1/1/2001and1/31/2001. 4. Refresh and update the report. TOPIC G a. Refresh the report. '! If you we re to select Use Saved Data , process - . ing would be much quicker, but that option wo uld start with the current returne d records and never go back to the databa se for other recor ds. If the filter is inte nded to narrow the current record set, you can safely use the Use Saved Data option . If the filter might broaden the search to include records not cu rrently in the recordset, the Refresh Data opti on should be chosen . b. Save the report. Create a Parameter Field In addition to creating a filter that limits the data in a report based on multiple fields, yo u can create a formula that limits the data based on user input. In this topic , that 's what you 'll do. Suppose you have a report that contains a lot of data, and you want to limit the displa y of data based o n who is viewing the report . You can create a parameter field that does that for you. You can avoid creating multiple reports by creating a sing le report that can be modified using the parameter field whenever the needs of the viewer change. The rep01t will include only the data that the viewer wants to see. In addition , if the v ie wer is un familiar with Crystal Report s, u sing the report will be simple, because he/she on ly ha s to answer a question when prompted. What is a Parameter Field? A parameter is a field that prompts a user of a report to enter information . It is a question that must be answered before the report can be generated. The contents of the rep01t are dependent on how the user answers the question. Crystal Reports 9.0: Level 1 Uses of Parameter Fields There are many options for using parameter fields in Crystal Reports. Some of those options are outlined in the following list: • To create for use in a se lection formula • To create a rep01t title • To create conditional formatting formulas • To include in a subreport • To link a primary repo1t to a subreport How to Create a Parameter Field Procedure Reference : Create a Pa rameter Field To create a parameter field: 1 . If necessary, se lect the De sign tab, and di sp lay the Field Explorer. 2. Select Parameter Fields and click New. The Create Parameter Fi eld dialog box is displayed. 3. In the Name text box, enter a name for the parameter field. 4. In the Prompting Text text box , enter the text with which you want to prompt the user. 5. From th e Value Type drop-down list , select the value type that matches your parameter field. 6. Select the parameter value option(s). They include the following: • Allow Multiple Values-Allows the users of the rep01t to se lect more than one default value for the parameter field after the Refresh button is clicked. • Discrete Value(s )-Allow s the users of the report to select discrete or single values for the parameter field. • Range Value(s)-Allows the users of the report to select a range of values for the parameter field. • Discrete And Range Values-Allows the users of the report to select discrete and range va lues for the parameter field. 7. If applicable , click Set Default Values to di s play the Set Default Values dialog box. You can use thi s dialog box to create a li st from which the user of the report can pick when prompted for a value . 8. If default va lues were se t, click OK to close the Set Default Value s dialog box. 9. If you do not want the user to be able to type in a free-form answer to your parameter, uncheck Allow Editing Of Default Value s When There I s More Than One Value. 10 . Click OK to close the Create Parameter Field dialog box and return to the Field Explorer. 11. If desired , place the parameter field in the repo1t where approp1iate. 12. Close the Field Explorer. Lesson 4: Building Formulas LESS •N ~ -i 91 LESSON 4 92 Procedure Reference: Incorporate a Parameter Field in a Selecti on Formula The procedure for placing a parameter field in a report will vary greatly depending upon the purpose of the parameter fie ld. The following example describes how to incorporate a parameter field in a selection formula: 1. Display the Select Expert dialog box for the field that the parameter field is based on. 2. Set the appropriate comparison operator. 3. From the second drop-down list, select the parameter field. '! If your parameter fie ld isn't dis playe d in t he drop-down list box , the data type of the field selected does no t match the data type cho sen for yo ur para meter field. Edi t you r para meter field formula to modify the value type. 4. Click OK. 5. Preview the report. The Enter Parameter Values dialog box is displayed . 6. Select the approp1iate value. 7. Click OK. 8 . Refresh the data to display the specified records . ACTIVITY 4-7 Creating a Parameter Field Setup : The My Customer Order Totals.rpt report is disp layed . Scenario: The Customer Service Manager would like to use your My Customer Order Totals rep ort on a daily basis in his department. However, he wants the report to be updated so the users of the report will be prompted to display only the customers in one region, which is the information they will need to answer customer questions. Also, the users of the report might not be famil- iar with Crystal Rep01ts, and by being prompted for information, they can easily answer a question without having knowledge of Crystal Reports. Crystal Rep orts 9.0: Level 1 What You Do 1. In the Create Parameter Field dialog box, name the parameter field Region and enter the prompting text of Select the region. 2. The default Value Type is How You Do It a. From the Design tab, display the Field Explorer. b. Select Parameter Fields. c. Click New. The Create Parameter Field dialog box is displayed . d. In the Name text box, type Select Region. e. In the Prompting Text text box , type Select the Region. Create Parameter Field '"*. Para meter Field Name: Prompting te xt: Value type: !s elect Reg ion I jsele ct the Regio ~ js tring 'ft The default value type is a string . In later steps, you will be writing a selection formula asking for matches between the Region field and this parameter field . The Region field data type is a string . The parameter value data type must also be a string , so you do not need to change the value type in this example . Lesson 4: Building Formulas LESSON 4 93 LESSON 4 3. Set the default values from the Region field of the Customer table to include all values. 4. On the Design tab , place the param- eter field on the left side of the Page Footer section and make the field bold. Close the Field Explorer. 'fl This step is optional. 5. Create a selection formula that sets the Region field equal to the Select Region parameter field. a. Click the Set Default Values button to display the Set Default Values dialog bo x . b. Verify that the browse table is set to Customer. c. From the Browse Field drop -down list, select Region. 5et Default Values ,,1 'f,. ~ Select from databa se Browse ta ble: I --·--Custo mer·---· Browse field : I-.. d. Click the Add All button GJ t o include all the regions in the Default Val ues list box . 'fl The default list you cre ated is based on the databa se value s currentl y. Region s that are added to the underl yi ng database afte r the parameter field ha s been creat ed will not appear in th e drop -do wn list until you edit the parameter fi eld to drop in ne w def ault values . e. Click OK to close the Set Default Values dialog box. f. Click OK to close the Create Parameter Field dialog box . The parameter field is listed in the Field Explorer. a. From the Field Explorer, drag the Select Region parameter field to the lef t side of the Page Footer section. b. Apply bold formatting to the selected parameter field. c . Close the Field Explorer. a. Display the Select Expert dialog b ox for the Region field. b. Set the comparison operator to Is Equal To. 94 Crystal Reports 9.0: Level 1 6. Display the records for AL using refreshed data. 7. Close the file without saving. TOPIC H c. From the second drop-down list , select {?Select Region}. Customer.Region I Order s.Order Da te I <New> I j is equal to d. Click OK. a. Preview the report. The Enter Parameter Values dialog box is displayed. b. From the Discrete Value drop -down list, select AL. c. Click OK. d. Refresh the data. The report displays the customers in Alabama. a. Close the report. A message box is dis- played verifying if you want to save the updated report. b. Click No to close the report without sav- ing the changes . Write a Formula that Incorporates Null Fields So far, you've writ ten a few different types of form ul as . Another type of formula yo u might need to create is one that deals with empty database fields. In this topic, th at 's what you 'll do. When you 're work in g with database field s, at some point you will enco unter field s that are empty. For exampl e, yo u have a repo1t that includes an Order Amou nt field , and for some of the records , that field is empty. You might have trouble displaying the data you need because of those empty fie ld s . You can create a formula that incorporates null fields, allowing you to di splay the records you need , even those fields that include null values. Lesson 4: Building Formulas LESSON 4 95 LESSON 4 Nulls In Crystal Reports , some database field s mjght contain a null, which s impl y mean s th e field has no data . Field s can be null if a field was never given a value. Another common example of a null field is found when two table s are joined using a left join , and the table on the right side has no matching record. (All fields from the table on the right side would be null.) A common problem with null values in Crystal Report s is that if a null field value is c oncat- enated with another field value, the resulting formula will be null. For example, if you were to write a formula that string s to gether the first name , middle initial, and la st name field s, no name would display at all if the middle initial was missing. One solution to thi s involve s using the I sNull function in the fo1mula creation . Another common problem with null values is found when setting filter criteria. If a user wants to see all record s where the order quantity was Jes s than 5, for example, simply writing a for- mula { Orders .OrderQuantity} < 5 will not return the records where the order quantity is null. Changing the fo1mula to read lsNull( { Orders .OrderQuantity}) OR { Orders.OrderQuantity }<5 would include the null records. If -Then-Else T here are many ways that you can deal with null values when yo u 're creating formulas. One of those ways is by using an If-Then-Else formula. If-Then-Else formu- las pe1form a test on a database field, formula, or both . An example of an If statement might look like the following: If x (the te st) The n y (the re s ult if true) Else z (the re s ult if false) In all If-Then-Else statements , the value for y and z must be the same data type. lsNull Function The lsNull function will test for the existence of a null value, returning true if a null value is found in the field following the function . For example, the formula lsNull( { Orders.OrderAmount}) would return true if the field was null, and fal se if there was a value in the field. ToText Functio n The ToText function will convert a Number, Cun-ency, Date, Time , or DateTime value to a text string so that it is displayed as text in the report. The ToText functio n can be useful in conjunction with If-Then-Else statements, because the values returned in an If-Then-Else statement must always be the same data type. If there is a need to mix numbers with text , the ToText function will facilitate this. For example, if X was a number field, you might use the following formula to show X when it is not null and to display "NIA" if X is null: If IsNull(X) Then "NIA" Else ToText(X ,0). How to Write a Formula that Incorporates Null Fields Procedure Reference : To create a formula that tests for null values, replacing a null value with x: 1. Display the Field Explorer, and select Formula Fields. 2. On the toolbar, click the New button to display the Formula Name dialog box. 96 Crystal Reports 9. 0: Level 1 3. In the Nam e text box, enter a nam e for yo ur f01mula, and click Use Editor to dis- play the Formula Workshop. 4. In the Formula Text window, enter the components of the formula. Include an If-Then-Else function and IsNull function where appropriate-for example, If IsNull( {field being tested}) Then X Else Y. 5. Check for errors in the fo1mu la, and click OK to close the me ssage box. 6 . Fix any errors. 7 . Save the formu la and close the Formula Works hop. 8 . Using the Field Exp lorer, place the formula fie ld in the approp1iate location in your repo1t. 9. Close the Field Explorer. 10. Preview the rep01t. ACTIVITY 4-8 Writing a Formula that Incorporates Null Fields Data Files: • Nulls .rpt Setup: No files are open in Cry stal Reports. Scenario: A co-worker is learning how to use Crystal Reports, and she just created her first report called Nulls. H owever, she 's having trouble with a specific field that contains null values. You sug- gest that she modify the report to change the negative numbers in the Credit Amo unt field to positive numb ers. Then, she co uld create a form ul a that shows the total credit amo un t. If a customer ha s no credits, the Credit Amount fie ld will state "No Credits ." What You Do How You Do It 1. When you preview the Nulls.rpt report, what do you notice about the numbers in the Credit Amount field? Save the report as My Nulls. 2. What formula would you write that returns the absolute value of the Credit.Amount field? Lesson 4: Bu ilding Formulas lESSON 4 97 LESSON ~ 3. Create a formula named AbsCredit that returns the abso l ute value of the Credit.Amount field. 4. On the Design tab , replace the Credit.Amount field with the AbsCredit field. Change the AbsCredit field label to Credits and the font size to 12. Align the Cred- its field label w i th the Customer Name and Credi t Authorization field labels. Add a summary that totals the AbsCredit field. a. Display the Formula Workshop for a for- mula named AbsCredit. b . Using the method of your c hoice , enter the Abs function in the Formula Text window . ., The Ab s function is a Math functio n. c . Using the method of your choice , enter the Credit.Amount fie l d in the Formula Text window. d. Check the formula for errors. e . Save the formula and return to t he report. a. On the Design tab , delete the Cr edit. Amount field. b . Insert the AbsCredit field in plac e of the Credit.Amount field . An AbsCred i t field label is inserted on top of the Credit Amount field label. c. Modify the AbsCredit field label to read Credits and change the label to a font size of 12. d. Align the Credits field label with the Customer Name and Credit Autho riza- tion field labels . ., There is often a nee d to select all ob jec ts in a · section for fo rmatting , moving , and so on . You ca n ri ght-click a secti on and choo se Sele ct All Section Object s to qui ckl y accompli sh this . e. Add a summary to the Group Footer #1 se ction that sums the Ab sCredit fi eld. 5. When you preview the report, what do you notice about Spokes 'N Wheels Ltd. and some of the other customers? 98 Crys tal Reports 9.0: Le vel 1 6. What formula would you write that replaces the sum of the AbsCredit field with 'No Credits" when there is nothing in the field? 7. Create a formula named CredjtSum that replaces the sum of the AbsCredit field with 'No Credits" when there is nothing in the field . 8. On the Design tab, delete the Sum Of @AbsCredit field. Place CreditSum in the Group Header section. Close the Field Explorer. Lesson 4: Bu ilding Formu la s a. Display the Formula Workshop for a for- mula named CredjtSum. b . Type If lsNull to begin the If-Then -Else statement. c . Type (Sum({@AbsCredjt),{Customer . Customer ID))). If I s Null (S un1 ( { 0AbsCredit}, (Customer. Customer ID })} 'fJ You ca n use the field tree to ent er the field names . d . Type then" No Credjts " t o enter the text 'No Credits " if the field is null . If I:i:Null(Swn({QAb.sCred1t},lCu.sto1uer.C u.stomer IDl))then "No Cred1t.s" e. Type else ToText(Sum({@AbsCredjt),{Customer. Customer ID)),O) to enter the credit amount and set the number of decimals to zero . then "No Credits" else ToText(Sum((@ Ab sCredit ),(Cu.:1tomer .Cu !5toml!r ID}),O) f . Check the formula for errors. g. Save the formula and return to the report. a. On the Design tab , delete the Sum Of @AbsCredit field. LESSON 4 99 LESSON 4 9 . Re-size the @CreditSum and Group Name fields as appropriate. Make @CreditSum bold. b. Insert the CreditSum field in the Group Header #1 section. tust omer Nane md Cred it ID: Credit Arn a .mt I Group #1 Name c . Close the Field Explorer. a. Re-size the @CreditSum field so that it is about 1" in length. b. Make the @CreditSum field bold. c. Re-size the Group #1 Name field so that it is about 2.5" in length. tust cmer Na-ne and Credit ID : Credit Amount 10. When you preview the report, what do you notice about Spokes 'N Wheels Ltd . and the other customers who had no credits? Save and close the report. Lesson 4 Follow-up In th is less on , you ad de d non-da ta base da ta to reports an d di s pla yed d ata differe ntl y by build - ing formulas . N ow, yo u will be ab le to enh ance your reports by addin g data th at isn 't part of the d a ta b ase . Yo u can also di spl ay d ata th e way you need it by usin g fo rmula s to fo rm at yo ur reports . 1. What types of formulas will you create in your reports? 2. What types of parameter formulas will you create? 100 Crystal Reports 9.0: Level 1 LESSON 5 Formatting Reports Lesson Objectives: In this lesson, you will format reports . You will: • Remove white space from a report. • Inse11 page header/footer data in a report. • Add borders and lines to a report. • Change the background color of a field. • Change the margins of a report. Less on 5: Formatting Reports LESSON 5 Lesson Time 45 minutes to 1 ho ur(s), 15 minu tes W\j .fut.m"\ll.S 101 LESSON 5 102 Introduction By using formulas, you ha ve updated your repo 1t so that now it contains all the nece sary data. Now yo u want to chan ge the layout and design of the report. In thi s le sso n, you'll do that by changing th e format of a varie ty of items. We all have certain routines that we follow eac h day to make ourselves look and feel good. You can do the same with your reports. By changing the way that a report looks, you can draw the viewer's attention to a particular record , se t of records, o r area of a report. By doing so, yo ur repo1ts will be vis ually appealing and effective. TOPIC A Remove White Space Depending up on the amount of data in a report, you might find th at there is too much white space. If so, you might want to decrease the amount of white space. That's what you'll do in this topic. Suppose you've created a repo1t that contains some extra s pace within a section. By re moving the extra white space, yo u wi ll enhance the appearance of the report. In addition, if th e report contains a large amount of data , yo u can decrease the amoun t of pages in the report , saving scrolling tim e and paper when you print. Figure 5-1 displays an example of a report with too much white s pace and after the white space is decreased . O rd er Date Order Amo unt 7 Bikes For 7 Brothers 1132 .00I 05/26/2001 Ac1a inst The Wind Bikes (133.001 05/26/2001 AIC Ch ildrens 1231 DOI 06/2112001 $53 .90 $53 .90 $479 .85 $479.85 $101 .70 $101.70 O rder Date Order A mount 7 Bikes For 7 Brothers 1132.00I 05/26/2001 $53 .90 Aaainst The Wind Bikes 1133.00I 05/2612001 AIC Childrens 1231 DOI 0612112001 $53.90 $479 .85 $479.85 $101 .70 $101.70 Figure 5-1: An example of a report with too much space and after the extra white space is decreased . How to Remove White Space Procedure Reference: The amount of white space that is displayed between rows on a report is affected by the height of a section in relation to the objects within it. If there is too much white s pace in a report, there are a number of ways that you can remove that extra space, including re-sizing the section using section borders, fitting a section using the shortcut menu , and s uppre ss ing a section using the Section Expert dialog bo x. Crystal Reports 9.0: Level 1 To remove white space in a rep011 usi ng the section borders : 1. If necessary, display the De sign tab. 2. Place the insertion point over the appropriate section border. 3. Click, drag up the appropriate amount, and release to decrease th e white space . .,. You can re-size a section onl y to the point of an existing field or vertical guideline . ACTIVITY 5-1 Removing White Space Data Files: • Fonnatting.rpt Setup: No files are open in Crystal Reports. Scenario: You in herited a repo11 named Formatting from another depai1ment. As soon as you open the report, you realize that there is too much white space in the Group Header #1 sec tion . What You Do How You Do It 1. In the Formatting.rpt report, there are approximately pages. Save the report as My Formatting.rpt. 2. Using the Design tab , decrease the amount of white space in the Group Header #1 section to approximately 0.25". Lesson 5: Formatting Reports a. Display the Design tab. b. Place the mouse pointer over the bot- tom Group Header #1 section boundary until a double-headed arrow is displayed • • 1 _ _tf rouu #1 Name -..... , c. Click and drag the insertion point up about 0.25". LESSON 5 103 LESSON 5 104 3. What happens when you try to move the section boundary up more? Why can't you further decrease the amount of white space in that section? 4. Move the Group Header field to the top of the section, and remove the vertical guideline. Decrease the size of the section. a. Drag the Group #1 Name field to the top of the Group Header #1 section. b . Drag the guide from the vertical ruler so that it is no longer displayed . > Order Da c. Drag the boundary up to the Group #1 Name field to further decrease the amount of white space . ~i~:~»:~1:ijiii;;::::::::::~:::::: I p rde r D ate 5. When you preview the report, how many pages are in the report now? Crystal Reports 9.0: Level 1 TOPIC B Insert Page Header/Footer Data In addition to removing white space in your report s, there are many other formatting options you can apply. One of those options includes adding data to the Page Header sec tion or Page Footer section. In this topic, that's what you'll do. Suppose you have specific information that you want to display at the top and bottom of each page of a report . At the top, you want to display the date and time. At the bottom , you want to display the page number. You can easily add that information to the Page Header sec tion or Page Footer section in the location you want to increa se the usefulne ss of the report. Special Fields In addition to adding database field s, formula field s, and parameter fields to your reports, there are other types of field s you can add, including special fields. They are system-generated fields, such as Page Number, Print Date , File Path And Name, and Report Comments. Nor- mally, yo u will add a special fie ld to the Page Header or Page Footer section. How to Insert Page Header/Footer Data Procedure Reference: In addition to s pecial fie lds, you can add text or formulas to the Page Header and Page Footer sec tion s. To add information to either of those sections: 1. Di s play the Design tab. 2. Using the Standard toolbar or the Field Explorer, drag the field (s) yo u want to include to the Page Header and/or Page Footer section(s). 3. If desired , format the fields. 4 . If desired, preview the report. Less on 5: Format ting Reports LESSON 5 105 LESSON 5 106 ACTIVITY 5-2 Inserting Page Header/Footer Data Setup: The My Formatting.rpt report is displayed. Scenario: You 've fixed the spacing in th e My Formatting report. However, th e report is still lon g . There- fore, it wo uld be he lpful if th ere was a page number reference in th e report. A lso, thi s report will be lo cated in a different folder than so me of your other reports . So , if the path and filename were di s played at the bottom of eac h page , that wo uld be great, too. What You Do 1. Using the Des i gn tab , place the Page N Of M field on the right side of the Page Header section. 2. Re-size the Page N Of M field to about 1" and place it at the 6" mark. How You Do It a. Display the Design tab. b. Display the Field Explorer. c. If necessary , expand the Special Fields category. d. Drag the Page N Of M field to the right side of the Page Header section. 'ft Using the Page N Of M field can slow the pe r- formance of Crystal Reports, becaus e Crystal must first format all pages to supply the total page count. a. Using the appropriate sizing hand le , re-size the Page N Of M field to about 1 ". b . Place the field at the top of the section at the 6" mark. Crystal Reports 9.0: Level 1 3. Place the File Path And Name spe- cial field i n the Page Footer secti on . Close the Field Explorer. 4. Center the File Path And Name field across the margins . Apply bold formatting . TOPIC c a. From the Special Fields category , drag the File Path And Name field to the Page Footer section . b. Close the Field Explorer. a. Re -size the File Path And Name field so that it extends from the left margin to the right margin . b. Click the Align Center buttonto center the field between the mar gins. c. Apply bold formatting . Add Borders and Lines You've added a page header and page footer to your report. There are many other formatting changes that you mig ht want to make to yo ur repo1t. In this topic, you'll apply some more formatting by adding borders and lines . At some point, yo u might want to add borders and/or lines to a report. By doing so, you wi ll increase the usefu ln ess of your report by emphasizing important data. A lso, your report will be more vis ually pleasing . How to Add Borders and Lines Procedure Reference: Create Borders To add borders to a report: 1 . Disp lay the Format Editor dia log box for the fie ld to whic h yo u want to add a border. 2 . Set the appropriate border options. You can change the line style, color, and back- gro und color of a border. 3. Click O K to app ly the settings. Procedure Reference: Create Lines To add lines to a report: 1 . On the Insert Too ls toolbar, click the Insert Line button. The insertion point changes to a pencil. 2 . Place the insertion point where you want to begin the line. Les son 5: Fo rmatting Re ports LESSON 5 107 LESSON 5 108 3. Click and drag the de sired length a nd release the mou se button. Borders versus Boxes Borders and boxes each possess ce1tain characteristics that mak e them different from one another. Tho se difference s are outli ned in the following li st: • Borders have fixed sizes. Boxes can be any size . • Borders can be used to enclose individual field va lu es. A box placed around a field encloses all the values in the field. • Borders cannot stand alone; the y mu st be around a field or picture. Boxes can stand alone. • Borders increase the height of a text line, resulting in fewer line s per inch. Boxes do not increa se the height of a text lin e. • A field border cannot span multiple sec ti ons . A box can span multiple sectio ns . ACTIVITY 5-3 Adding Borders and Lines Setup: The My Formatting.rpt report is di splayed . Scenario: You show the My Formatting report to yo ur bo ss . He likes the formatting, but th e report still see ms to be mi ssing so mething . He think s it 's important for the gro up total s to stand o ut. Fig- ure 5-2 display s a sample of the formatted report after adding borders and lines. Order Date O rder Amount 7 Bikes For 7 Brothers 1132.00l 05126(2001 II Anainst The Wind Bikes 1133 .00l 05f26f2001 II $53 .90 $53.90 II $4 79 .85 $479.85 11 Figure 5-2: A sample of the report with borders and lines. What You Do 1. Add a double-sided teal border to each side of the Sum Of Orders.Order Amount field. How You Do It a. Display the Format Editor dialog box for the Sum Of Orders.Order Amount field. (Right-click the Sum Of Orders .Order Amount field and choose Format Field.) b . Select the Border tab. Crystal Reports 9.0: Level 1 2. Using the Insert Tools toolbar, draw a line above the Order Date field label from the left margin to the right margin. Preview the report. Lesson 5: Formatting Reports c . In the Line Style box, from the Left drop- down list, select Double. Format Editor 'ii~' Commo n Border 1 ·Font I Numbe r Line style : I Lef t: 11.nm1w::J d. Change the right, top, and bottom line styles to Double. e. In the Color box , from the Border drop- down list, select Teal. r Col or : I Border : Teal . ·a ,, A preview of what the border settings will look like is displayed in the Sample bo x. f . Click OK to apply the border settings. , Formatting changes can be made in Design or Preview mode . a. On the Insert Tools toolbar, click the Insert Line button ~ . The mouse pointer changes to a pencil. b. At the left margin just above the Order Date field label, click and drag to the right margin and release the mouse button. , You can draw only horizontal and vertical lines . c. Preview the report. LESSON 5 109 LESSON 5 110 TOPIC D Change the Background Color You've added a colored border and a line to the report. There are many other ways that you can add color to your repmts. In this topic , you'll change the color of a field background. Suppose you have a repo1t that contains a field that you want to se t off from the rest of the data. One way to e mphasize the s pecified data is to change the background color of the field. By doing so, you will increase the usefulnes s of your report by highlighting the specified data . The user will immediately be drawn to the data with the highlighted background . Also, yom rep01t wi ll look better. How to Change Background Color Procedure Reference: To change the background color of a field: 1. Di sp lay the Fmmat Editor dialog box for the field for which you want to change the background color. 2. In the Color box, check the Background option. The Background drop-down list is di sp layed. 3. From that drop-down list , select the background color of your choice. 4. Click OK to apply the settings. ACTIVITY 5-4 Changing Background Color Setup: The My Formatting.rpt repo1t is displayed . Scenario: The report looks much better with the colored borders. H owever, you want to emphasize the totals a bit more. You think that changing the background color of the Sum Of Orders .Order Amount field might be the key. Crystal Rep orts 9.0: Level 1 What You Do 1. Change the background color of the Sum Of Orders.Order Amount field . 2. Preview and save the report. How You Do It a. Display the Format Editor dialog box for the Sum Of Orders.Order Amount field. b. If necessary , select the Border tab. c. In the Color box , check the Background option. The Background drop-down list is displayed . d. From the Background drop -down list, select Yellow. ~Co lor : Border: P Background : l•real •Yellow :a e. Click OK to apply the settings . , You can also use the Form at Editor or the Increase Decimals and Decrease Decimals but- tons on the Formatting toolbar to increase or decrease the number of decimal place s in a field . a. Preview the report. b. Save the report. 3. What other field backgrounds might you change on this report? Close the report. Lesson 5: Formatting Reports LESSON 5 111 LESSON 5 112 TOPIC E Change the Margins Previously, you removed white space in specific sections by using the section borders. There are other formatting options that affect the white space in your reports. In this topic , you will change the amount of white space between your report and the edge of the page. Suppose you have a long report that you want to set up for binding. By changing the margins , the report will contain enough space where needed so that when the report is bound, the data will still be readable. How to Change Margins Procedure Reference: To change the margins in a report: 1. Choose File~Page Setup to display the Page Setup dialog box . 2 . Change the top, left, bottom, and right margins as desired. 3. Click OK to apply the settings. ACTIVITY 5-5 Changing Margins Data Files : • Employee Guide.rpt Setup : No files are displayed in Crystal Reports. Scenario: The Employee Guide report is going to be bound and distributed to all employees in the com- pany; therefore, you need to change the left margin to allow space for the binding. A sample of the formatted report is displayed in Figure 5-3. Crystal Reports 9.0: Level 1 • • • I ' ' • ' • ' I 0 ' ' 1 ' ' • l ' ' ' 2 ' ' ' I ' ' ' 3 ' ' ' l ' ' ' 4 ' I Nancy Davolio P os iti on: Sales Reores f!'ltati ve Hire Date 03/29/9 1 Education mch.rl es a BA in Psycho logy fr om Colo rad o Stat e Uni =sity in 1994 . She al socollflleted"TheArtofthe Col dCall." Nancy i s a marbe-o fToashrasters lntermti ooaL Figure 5-3: The formatted My Employee Gu;de.rpt report . What You Do 1. Save the Employee Guide.rpt report as My Employee Gujde.rpt. Change the left margin of the report to 1 ". How You Do It a. Save the Employee Guide.rpt report as My Employee Gujde.rpt. b . Choose File-7Page Setup to display the Page Setup dialog box . . c. Press Tab to move to the Left Margin text bo x . d. Type 1. Page Setup >'- Top Ma rgin : Left Maigin: e. Click OK. jo25 11 in in 2. For what other reasons might you change the margins of a report? Preview, save , and close the report. Lesson 5: Formatting Reports LESSON 5 113 LESSON 5 Lesson 5 Follow-up In this lesson , you worked with a number of options for formatti ng your reports. Now you know how to change the way that a rep01t looks, resulting in gra bbin g a viewer's attention to a particul ar record , set of records , or area of a report. Therefore, your reports wi ll be vis ually appealin g and effective. 1. What types of information will you place in the Page Header and Page Footer sections of your reports? 2. List some instances where you would use borders, boxes, and lines to enhance your reports. 114 Crystal Reports 9.0 : Level 1 LESSON 6 Enhancing Reports Lesson Objectives : In this lesson , you will enhance reports by adding and modifying elements in a rep01t. You will: • Create a watermark in a rep01t. • Add a bulleted list to a report. • Modify the format of a report based on the value of specific data. • Apply conditional formatting to a report. • Create a hyperlink in a rep01t. • Hide rep01t objects. Lesson 6: Enhancing Reports LESSON 6 Lesson Time 1 hour(s) to 1 hou r(s), 30 minutes 11 5 LESSON 6 116 Introduction You have applied so me formatting to your report , allowing you to improve its appearance. There are other options that you can use to enhance your report so that it's more effective. In this le sson, you will take a look at a few of those options. Now that you've changed the appearance of your repo1t , you want to make some other enhancements to it that will affect the de s ign and layout. By us ing s uch options as adding sec- tions, using conditional formatting, and hiding objects, yo u wi ll increase the usefulne ss of your repo1ts. TOPIC A Create a Watermark So far, you ha ve wo rked with all the default sections in a li st report. At some point , you might need to add an additional sec tion so that you can add additional data to the report that won 't fit in any other section. In this topic , you'll create a section and add a watermark to it. As yo u know, there are five default sections in a report. Ce1tain ta sks in Crystal Rep01t s work best when there are multiple sec tion s within a section. One of those tasks is creating a watermark. By in serti ng additional sections where approp1iate, you'll be able to create a water- mark and more. Reasons to Create Sections Crystal Report s provides five default sec tions in every report. Those sections are the Repo1t Header, Rep01t Footer, Page Header, Page Footer, and Details sections. If necessary, you can create additional sections. By doing so, yo u 'll have more control over report formatting, as shown in the following table. Reasons for Creating an Addi tio nal Section Avoid overlapping objects such as text fields with the Can Grow option , charts , and subreports Conditional formatting-suppress based on non- field criteria Conditional formatting-suppress based on field value Example A chart grows as data expands , covering up the data below the chart. Putting the chart in its own section solves th e problem. Print company logo in Page Header A on every other page . Show Pa ge Header B (w ith column label s) on each page. Imagine two different format options for an in voice item: One format i s for overdue accounts , done with bold fonts and account aging information . The other format is standard. If two Detail s sec- tion s are us ed , one can use conditional formatting to show the appropriate format de sired ba sed on th e age of the invoice. Crystal Reports 9.0: Level 1 Reasons for Creating an Additional Section Example Show pa ge lab els in Drill-Down re ports w hen reset Page lab e ls can be repeated at th e top of Drill - formulas exis t in the header section s Down re ports (Fil e~R e port Option s~S ho w All Hea der s On Drill-Down). Variabl e formulas often require a rese t in th e header section s, thou g h , caus- ing incon-ec t total s when head ers are repeated in drill-do w n sec tion s. One solution is to put page lab e ls in Detail s A in stead of th e page head er, choo sin g th e Suppress If Dupli cated option o n Widow/orphan control each field . Creating additional group sections is required for detailed widow/orphan control (see Crystal Reports Knowl ed geBase Article C2000972 , "Preventin g Widowed Gro up Headers"). '! You can access the Crystal Reports KnowledgeBase at http ://support .crys talde cisions.com . How to Create a Watermark Procedure Reference: Suppose yo u want to add a watermark to a report. Before yo u add the watermark, yo u mi g ht want to add a new sec tion to put it in. To create a watermark by underlaying it in a new section: 1 . Insert the o bj ect in the appropriate section. To create a new section: A. On th e Expert Tool s toolbar, click th e Section Expert button to di s pla y th e Section Ex pe1t dialog box. B . In the Sections li st bo x, se lect the section after which you want to in se1t a sec tion . C. C lick Insert to insert a section below the selected sec tion . D . Click OK to close the Section Expert dialog box. 2. In se1t the gra phic you want to underlay as a watermark. To in se1t a graphic: A. On the In sert Tool s toolbar, click the In se rt Picture button. The Open dialog box is dis played . B. Select the graphic you want to inse1t . C. Click Open . An object frame is attached to the mouse pointer. D . Click to place the picture in the approp1iate sec tion. 3. If necessary, display the Section Expert dial og bo x. 4. In th e Sect ion s li st bo x, se lect the section that contains the graphic yo u want to underlay. 5. On the Common tab , c heck Underlay Following Section s . 6. Click OK. Lesson 6: Enhancing Reports LESSON 6 117 LESSON 6 118 Repository Explorer The Crystal Repository can be thought of as a central filing cabinet in which to store commonly used report objects. Text objects, graphics, functions, and SQL query com- mands can all be stored in the repository, making them available for all future repo1ts. Objects that are placed in a report from the repository by default maintain a link to the original object, so that updates to the repository object will get updated in a ll the reports. Users can sever the link to the repository by right-clicking the report object and choosing Disconnect From Repository. To add images and text objects to the Crystal Repository: 1. On the Standard toolbar, click the Repository Exp lorer button to display the Repository Explorer. 2. In the Repository Explorer, expand the Crystal Repository category. 3. Right-click the report object and choose Add To Reposito1y. The Add Item dialog box is d isplayed. 4. Provide a name for the repository object. If desired, the Author and Desc1iption fields may a lso be completed. 5. Select a folder location in the Location window. 6. Click OK to close the Add Item dialog box. To use image and text objects from the C1ystal Repository: 1. Display the Reposito1y Explorer. 2. Expand folders as needed within th e Crystal Repository until the desired object is visible. 3. Drag the object from the repository into the appropriate report section. To remove image and text objects from the Crystal Repository: 1. Display the Repository Explorer. 2. Right-click the object and choose Delete. Page Break Insertion Options Besides addin g new sections, there are other options you can set when using the Sec- tion Expert d ialog box. Suppose you wan t to contro l the page breaks in your rep01t. The options that you can use in the Section Expert that allow you to do that are out- lined in the following table. Option New Page Before New Page After Keep Together Function Starts a new page before printing the selected section. Starts a new page after printing the selected section . Prevents a page break from being inserted in the middle of the selected section . 'fl The Keep Together option in the Section Expert dialog box is different than the Keep Groups Together option in the Insert Group and Change Group Options dialog bo xes . The option in the Section Expert works at the record level , while the group option works at the group level. Crystal Reports 9.0: Level 1 ., Using the New Page After option on a group will insert a blank page at the end of your report (or dis- play whatever is in your report footer on the last page). If this is not desired , a conditional format formula can be set on the New Page After option . The formula would read : Not OnLastRecord. (Condi- tional format formulas are covered later in this les son .) ACTIVITY 6-1 Creating Watermarks Data Files: • Enhanced Report.rpt Setup: No files are open in Crystal Repo1t s. Scenario: You created a report named Enhanced Report. You need to distribute it to others for feedback. The report is in a draft stage, so you want to add a watermark to the report so that others will know it is a draft. You also realize that many other rep01t s might use this same watermark, so you will also add the watermark image to the Crystal Repo sitory, making it available in future reports . Lesson 6: Enhancing Reports LESSON 6 119 LESSON 6 120 What You Do 1. Save the Enhanced Report.rpt report as My Enhanced Report.rpt. In the My Enhanced Report .rpt report , create a second Page Header section . How You Do It a. Save the Enhanced Report.rpt report as My Enhanced Report.rpt. b . With the My Enhanced Report. rpt dis - played , on the Expert Tools toolbar, click the Section Expert button ~.The Sec - tion Expert dialog box is displayed . c. In the Sections list box , select Page Header. Lffi ttitin1 .mw Sections: Re ort Header Group Header 111: Detail s Group Foot er Il l : ( d. Click Insert. ' You can also right-clic k the gray area of the appropriate se ct ion in the report and choose Ins ert Section Belo w. e . Click OK to close the Section Expert dia - log box . 2. What happened to the Page Header section? Save the file. 3. What is in Page Header A and B? Drag the gray area of the Page Header A section below the Page Header B section. Crysta l Reports 9.0: Level 1 4. Insert the Draft.bmp graphic file in the Page Header A section. 5. Underlay the following sections and preview the report. Lesson 6: Enh ancing Reports a. On the Insert Tools toolbar, click the Insert Picture button ~.The Open dia - log box is displayed . 'ft You can also choose ln sert-?Pictu re . b . From the (:\Student folder, select Draft.bmp. c . Click Open . d . In the Page Header A section , move the mouse pointer to approximately the 1.5" mark. An object frame i s attached to the mouse pointer. e. Click to place the picture in the Page Header A section. I :~ :~ a. Display the Section Expert dialog box. (On the Expert Tools toolbar , click the Section Expert button .) b. In the Secti ons list bo x, select Page Header A. c . On the Common tab , check Underlay Fol- lowing Sections. d. Click OK . e . Preview the report. LESSON 6 121 L ES SON 6 TOPIC B Add Bulleted Lists You have added various type s of text fields to yo ur reports . At some point , you might need or want to add a bulleted list of text in a report. In this topic , that's what you'll do. Suppose you want to add a bulleted list to a report. You can't easily create a bulleted li st using Crystal Reports. However, you can inse1t an object into a report from another application. By doing so, you can incl ude the informati on you need in your reports without being limited by the features of Ciystal Reports. Object Linking and Embedding There are many type s of objects that you can include in yo ur rep01t s. Object Linking and Embedding (OLE) allows you to embed an object, such as a s preadsheet , text document , or video clip, into a document, called the "container application." When you double-click the object, the application in which the object was created, called the "server application," is launched so you can edit it. If you want, you can link an object instead of embedding it. In that case, the container application does not physically hold the object, but provides a pointer to it. If you make a change to the linked object, all the documents that contain that sa me link are automatically updated the next time you open them . How to Add a Bulleted List Proce dure Refe renc e : You can create a new OLE object in Crystal Reports or create one from an existing file. To add a bulleted list as an OLE object to a report: 1. If necessary, select the Design tab . 2. Choose Inse1t~OLE Object. The Insert Object dialog box is displayed. 3. Select Create From File and click the Browse button. The Browse dialog box is displayed . 4. Select the file you want to inse1t, and click Open to return to the Insert Object dialog box. 5. Click OK to close the Insert Object dialog box. 6. Place the mouse pointer in the sec tion in which you want to add the object and click to inse1t the OLE object. 122 Crystal Reports 9.0: Level 1 ACTIVITY 6-2 Adding Bulleted Lists Setup: Word 2000 has been insta ll ed followi ng the set up instruc tio ns. The My Enhanced Report.r pt repo r t is displaye d . Scenario: Yo u received a bulleted list of items from a mee tin g. Yo u wo ul d l ike to include that list in your report. The format of th e list is M icrosoft Wo rd. What You Do 1. Using the Design tab, add the List.doc file as an OLE object in the Report Header at the first guideline. Lesson 6: Enha ncing Re ports How You Do It a. In Design mode , choose lnsert---7 0LE Object. The Insert Object dialog bo x is di splayed . b. Select Create From File. Insert Object r Create New r. Create fr om File c . Click the Browse button to display the Browse dialog box. d . From the (:\Student folder , select List.doc. e . Click Open to close the Browse dialog bo x. f . Click OK to close the Insert Object dialog bo x . ., It will take a moment for th e object f rame to displ ay. LESSON 6 123 LESSON 6 124 g. In the Report Header section, move the mouse pointer to the first guideline. h. Click to place the OLE object in the Report Header section. • W hts tlt'f Rentals -c.aioe repar ,• lnventcry ct Nteros ts dtr'lgerou sly IM ~ lnVltaJoos te< ce<npa1y e cnic -custOO)ers with wer $5,000 1n sa les y-t-d 2. When you place the mouse pointer over the inserted object, what does the ToolTip indicate? 3. What happens to the menus and toolbars when you double-click the OLE object? Click in a blank ar ea of the report to deselect the object. Save the report . .,. You can use the menus and toolbars to edit the OLE object. Any changes you make will not affect the original file (unless it is linked). Crystal Reports 9.0: Level 1 TOP IC c Modify Format Based on Data Value You've looked at a few ways to enhance your repo1t. There are many others. Suppose you want to change the look of specific data in a report based on its value. In this topic, that's what you'll do. You want to highlight data that meets specific ctite1;a. By using the Highlighting Expert dialog box, you can specify when and how to highlight that data. Then, only the data you want to be emphasized will be the color you specify. How to Modify Format Based on Data Value Procedure Reference : There are many ways to format data based on its value. One of those ways includes using the Highlighting Expert dialog box. To do so: 1 . Select the field you want to affect. 2. On the Expert Tools toolbar, click the Highlig hting button to display the High- lighting Expert d ialog box . 3. In the Item List box, click New. 4. In the Item Editor box, from the Value Of drop-down list, select the field that will be affected . 5. In the second drop-down list, select the approp1;ate comparison operator. 6. In the third drop-down list, enter the appropriate value. 7. From the Font Color drop-down list, select the color of your choice. You could also change the background and border colors. 8. Click OK. 9. Preview the repo1t. Less on 6: Enhan cing Reports LESSON 6 125 LESSON 6 126 ACTIVITY 6-3 Modifying Format Based on Data Value Setup: The My Enhanced Rep01t .rpt report is displayed. Scenario: You s how the My Enhanced Rep01t to your bos s . He wants orders that were eq ual to or over $5,000 to stand out from the s maller orders. Those customers will be invited to a customer appreciatio n day. What You Do 1. Display the Highlighting Expert dia- log box for the @Cost field. 2. Set the item to be greater than or equal to 5,000. 3. Change the font color to Blue, the background to Silver, and the bor- der to Double Box. How You Do It a. Select the @Cost field. b. On the Expert Tools toolbar , click the Highlighting button ~. The Highlight - ing Expert dialog box is displayed. 'f You can also right -click and choose Highlight- ing Ex pert . a. In the Item List box , click New. b . In the Item Editor box , from the second Value Of drop-down list, select Is Greater Than Or Equal To. c. In the third drop -down list box , type 5000. Item editor ---------- Value of: j thi s fie ld 3 I is greate r than or equal to a. From the Font Color drop -down list , select Blue. b . From the Background drop -down list, select Silver. Crystal Reports 9.0: Level 1 TOPIC D c . From the Border drop-down list, select Double Box. Font color : Background : Border : l •Blue ID s il ver !Eiiiiiiiii A sample of the formatting changes is dis- played in the Sample box . d. Click OK to apply the settings . e. Save and close the report. Conditionally Suppress Data Previously, you changed the appearance of a specific field in a report based on the value of th e data in that field. Another way to change the appearance of data in a report is to hid e it. In this topic, that's what you 'll do. Suppose your boss is thinking about closing the business for one day. He wants to check the sales that have occmTed in the past on that day to see how a closing might affect the business. You can create a formula and suppress the sales based on the formu la. Therefore, only the sales you specify will be displayed, making it easier to foc u s on that data. Boolean versus Attribute Formatting Often we want to apply formatting in a repo1t based upon one or more conditions . For example, if the order quantity is above 500, bold the quantity field; if the inventory amount is less than 25, print the detail lin e in a red font. Formatting that is based up on a condition is commonly called "condition al formatting ." · There are two types of form ul as that accomplish conditional formatting : boolean and attribute . A boolean formula is any formula that returns a true/false valu e . For example, a boolean for- mula might be used in a co nditional fo1mat form ul a to indicate whether the f01matting option in question sho uld be turned "on" (true) or "off'' (false). An attribute formula, on the other hand , requires additional info1mation to specify the format- ting properties . For example, font color is not something that is turned on or off. Rather, a color name must be provided, such as red, blue , gray, and so forth . Attribute fomrntting uses an If-Then-Else statement rather than a boolean formula . A sample attribute formatting example would be If {Orders.Order Quantity} > 500 then crRed else crDefaultAttribute. Lesson 6: Enhancing Reports LESSON 6 127 LESSON 6 128 How to Conditionally Suppress Report Sections Procedure Reference: To conditionally suppress report sections: 1 . Display the Section Expe1t dialog box . 2. In the Sections list box, select the section that includes the data you want to Suppress. 3. On the Common tab, click the Conditional Fom1ula button next to the Suppress (No Drill -Down) option. The Formula Workshop is displayed. 4. Create the necessary formula. 5. Check the formula for errors. 6. Save and close. 7. Click OK to apply the settings and close the Format Editor dialog box. 8. Preview the report. Conditionally Suppress Field Data At some point , you might also want to suppress field data in a report. To conditionally suppress field data: 1. Select the field(s) to be suppressed. 2 . Right-click the selected field(s) and choose Format Field. 3. On the Common tab, click the Conditional Formula button next to the Suppress option. The Formula Workshop is displayed. 4. Create the necessary fo1mula . 5 . Check the formula for errors . 6. Save and close . 7. Click OK to apply the settings and close the Section Expert dialog box . 8. Preview the report . ., The concepts utilized to conditionally suppress data can be used to conditionall y apply any format option where a Conditional Format button exists . For example , if someone wanted a border around fields that exceed a particula r dollar amount , a conditional format formula could be applied to the Con- ditional Format buttons next to the applicable border options . l2Qy otweek Func tj Qd Another functi o n that you might want to use when creating formulas is DayOfWeek. This function detemlines the day of the week the given date falls on and converts the day of the week to a number, 1 to 7. For example, DayOfWeek (Date(l990,10,1)) would return the number 2 because October 1, 1990 is a Monday. (Crystal uses Sun- day as day 1, unless otherwise specified in the fommla.) Crystal Reports 9.0: Level 1 ACTIVITY 6-4 Conditionally Suppress a Report Section Data Files: • Conditional Format.rpt Setup: No file s are ope n in Crystal Reports. Scenario: You've created a report na med Conditional Format. Your bo ss likes the report , but wants to see only those customers who had sales on Saturday and Su nd ay . What You Do How You Do It 1. In the file Conditional Format.rpt, the number of customer orders in January 2000 is Currently, pages of the report are displayed. Save the report as My Conditional Format.rpt . 2. Display the Format Formula Editor window for the Details section . Lesson 6: Enhancing Reports a. Display the Section Expert dialog box. b . In the Sections list box , select Details. c. On the Common tab , to the right of the Suppress (No Drill -Down) option, click the Conditional Format button ~.The Format Formula Editor window is dis - played in the Formula Workshop . LESSON 6 129 LESSON 6 130 3. Create a conditional formula for the Details section that suppresses sales that didn't occur on Saturday and Sunday. a. In the Formula text box , type DayOfWeek( to determine the day of the week and convert it to a number. b. Using the Report Fields tree , add the Orders.Order Date field to the formula and type). c. Type > 1 to include the days of the week that follow Sunday. d. Type and to include additional criteria. e . Press Enter. f. Repeat steps a and b . g. Type < 7 to include the days of the week prior to Saturday . DayOfTJeek ( {Orders . Order Date) ) >land Day0f1Jeek ( {Order s . Order Date} ) <7 You could cop y and paste the first line of the for- mula and edit the components as appropriate. h. Check the formula for errors, save the formula , and return to the Section Expert dialog box. 4 . How has the Conditional Formula button for the Suppress (No Drill-Down) option changed? Click OK to close the Section Ex pert dialog box and apply the settings . 5. When you preview the report, now there are remaining data is suppressed. pages of the report displayed. The 6. The number of customer orders in January 2000 is ., You could have cr eated a selection formul a limiting the report to Saturday and Sunday. Ho wever, the totals would onl y include the filtered data . When the data is suppress ed , totals in the report reflect all records , including those that have been suppressed . Crysta l Re ports 9.0 : Level 1 TOPIC E Insert Hyperlinks Previously, you suppressed information in a repo1t, so that only specific data would be displayed. At some point, you might want other users of your report to be able to view addi- tional info1mation using your repo1t. In this topic, you 'll find out how to do that. Suppose you have some data that you want to include as part of a repo1t. However, the repmt is too large and has limited white space, so it 's difficult to actually display the data in the report. By inse1ting a hyperlink, you can provide a link to that additional information. Then, the users of your report can easily access that information when they need to through the hyperlink. What is a Hyperlink? A hyper/ink is a link between two objects that is displayed as text or an icon. The item that you can link to is dependent upon the selected object and its location in the report. In Crystal Repo1ts, you can link a repmt to the following items: • A Web site • A file • A field value • An email address • Another Crystal Reports file When you create a hyperlink in a report, it is saved in the report and is available to other users as a way of viewing additional information. ! The linked information is ava il able only if t he user has t he associated ap plication . How to Insert Hyperlinks Pro c edure Reference: To insert a hyperlink in a report: 1. Select an object in the repo1t from which you want to create a hyperlink. 2. On the Expert Tools toolbar, click the Insert Hyperlink button. The Hyperlink tab of the Format Editor dialog box is displayed. 3. In the Hyperlink Type box, select the type of hyperlink you want to create. 4. In the Hyperlink lnfmmation box, enter the appropriate info1mation based on the hyperlink type. 5. Click OK to inse1t the hyperlink in the report. Less on 6: Enhancing Reports LESSON 6 131 LESSON 6 132 ACTIVITY 6-5 Inserting Hyperlinks Setup: Word 2000 has been in stall ed fo ll owing the setu p in structions. The rep01t My Conditio nal Fo1mat.rpt is displayed. Scenario: You need to in cl ude a lis t of a ll c usto mers in th e M y Conditional F01mat repo1t. H owever, there isn't e nou gh room for the in formatio n . Therefore, you th ink incl udi ng a link to the Link To document th a t contains th at info 1mation is the solu tion. What You Do 1. From the Design tab , add a text object to the left side of the Report Footer section that reads View All Customer Orders : Microsoft Word Format . Re -size the text object. 2. Format the text object so that it is underlined and Navy . How You Do It a . From the Design tab , click Insert Text Object. b . Click on the left side of the Report Footer section . c . In the selected text obj ect, type View All Customer Orders: Microsoft Word Format . d. Re-size the text object as necessary. a . Select the text object. b . On t he Formatting tool ba r , click the Underline button [ill . c . On the Expert Tools toolb a r , click the Format button ~to display th e Fo rmat Ed itor dialog box . d . Select the Font tab. e . From the Color drop -do wn list , select Navy . Crystal Reports 9. 0: Leve l 1 3. Create a hyperlink from the text object to the Link To.doc file. a. Select the Hyperlink tab. If the Format Editor dialog bo x isn 't di spl ay ed , on the Standard toolb ar, you can clic k the Insert Hype rlink button . b . In the Hyperlink Type bo x, select A File. Common I Bor der I Fonl l Paragraph Hyperunk I Hyperlink type:-------------- r No Hyperlink r An E-mail Addre ss ("" A. Website on the Internet r. AFile (' Cu11ent Web rte Field Value r L.urrent E ·ma I heid Value DHTML Viewer Only r Fleport Part Dnlldown r Another Report Ob ject c. In the Hyperlink Information box , click the Browse button. The contents of the (:\Student folder are displayed in the Open dialog box . d. From the Files Of Type drop -down list box , select All Files. e . Double-click the Link To.doc to display the filename in the File Name text box. r. Hyper link in. f. o. r.mation: -- File Name: D Jim•r:r:m= f . Click OK to insert the hyperlink. g. Deselect the hyperlink. 4. When you preview the report, what happens to the mouse pointer when you place it over the hyperlink? Click the hyperlink. \I iew A II C u stom er 0 rde rseJ ic rosoft V\I ord Form at The Link To.doc file opens in Microso f t Word. Lesson 6: Enh ancing Reports LESSON 6 133 LESSON 6 134 5. What does the Li n k To.doc contain? Close Word. Update and close the file. TOPIC F Hide Report Objects You 've su ppre ssed ce1tain section s based on their co nte nt s. Suppose you want to suppress sec- ti o ns if they are blank. That's what you 'll d o in thi s topi c. Suppose you h ave a re po1t that includ es fi eld s th at contain bl a nk data. That blank data mi ght negati ve ly impact your rep ort becau se it does n 't trnl y re fl ect th e data . By hidin g secti ons th at contain blank data, yo ur re po1t will look bett e r and di s pl ay th e inform ation yo u need. How to Hide Report Objects Procedure Reference: Hiding Sections You can hide report section s and rep01t obj ects . To hide repo1t sec ti o ns : 1 . Di spl ay the Section Ex pert di alo g box. 2. In th e Sections li st box, se lec t th e section yo u wa nt to s uppress . 3. On th e Co mmon tab , check Suppress Blank S ec tion . 4. Click OK to app l y th e se ttin g and close th e Sec tio n Expert dial og bo x. Procedure Reference: Hiding Objects There are a number of re as on s to hid e rep01t obj ec ts. T he follo win g ta ble outl in es some of th ose reason s. Reasons t o Hide Objects Duplex printin g Entire section s and/or fi e ld s can be hidd en wh en th e data i s not applicable for a partic ul ar rec ord Fom mla s used fo r testin g o nl y Example Hide o bj ec ts every other page. (1) In an aging report , sho w on ly overdu e ac co unt s usi ng co ndition al suppression . (2) Hid e ta ble headi ngs when th ere is no data in t he ta bl e. (3) Di s play th e word "continu ed" onl y when in a re peat ed gro up head er. Suppressed obj ects di spl ay in Des ign mode. Keepin g fomm las use d in testi ng pro vides qui ck repo1t tro ubles hooti ng. Crysta l Repo rts 9.0: Level 1 Reasons t o Hide Objects Formulas used for calc ulati o n purpo ses only To hide report objects: 1. Di s play the Section Expe1t dialog box. 2. Select the sec tion you want to suppres s. 3. On the Common tab , check Suppress. Exam pl e Often runnin g totals do not nee d to be shown in th e Detail s sec tion, but mu st be place d in each De tail s sec tion to proper ly calculate. 4. If desired , inse1t a formula using the Conditiona l Format button. Suppress versus Suppress If Duplicated Another option for s uppre ss ing sections that yo u might want to use is Suppress If Duplicated. If se lected , Suppress If Duplicated preve nts a fie ld value from printing if it is duplicated within a rep011 page. The value does n 't print, but the space in which it would have printed remain s. ACTIVITY 6-6 Hiding Report Sections Objective: To suppress blank sec tion s where there is no data in the Address2 field. Data Files : • Suppress If Blank.rpt Setup : No file s are open in Crystal Reports. Scenario: You've created a report named Suppress If Blank . When you preview the repo1t , yo u notice that some of the Address2 field s contain blank data. It negativel y affects the appearance of the report , so you want to get rid of the blank lines. What You Do How You Do It 1. When you preview the Suppress If Blank.rpt report, what do you notice about some of the addresses ? Save the report as My Suppres s If Blank .rpt . Less on 6: Enhan cing Reports LESSON 6 135 LESSON 6 2. Suppress the Details C section. Pre- view, save , and close the report. Lesson 6 Follow-up a. Display the Section Expert dialog box . b. In the Sections list box , select Details C. c . On the Common tab , check Suppress Blank Section . d. Click OK to apply the setting and return to the report . e . Preview, save, and close the report. There are man y options that yo u ca n use to e nh ance the design and layo ut of your reports . In thi s lesso n, yo u 've used a nu mber of th ose options. When yo u want to e nh ance yo ur reports and increase th eir usefu ln ess , yo u ' II know so me of th e way s yo u can do it. 1 . What type of conditional formatting might you apply in your reports? 2. What objects might you hide in your reports and why? 136 Crystal Reports 9.0: Le vel 1 LESSON 7 Creating and · Modifying Pie Charts Lesson Objectives: In this lesson, yo u will create single data series charts . Yo u w ill: • Create a pie chart. • Modify the chart. • Format a pie c hart. • Create a gro up ed c hait. Les son 7: Creating and Modifying Pie Charts LESSON 7 Lesson Time 1 hour(s) to 1 hour(s), 30 minutes 137 LESSON 7 138 Introduction You've worked with a numb er of different options that allow yo u to impro ve or enh ance your reports. Another way to affect th e di s pla y of data is by using c hait s. In this lesso n, yo u' II cre- ate and modify charts. Suppose you ha ve s ummai·ize d data in a report. Graphi c representations of data often have more meaning than textual data . B y us in g charts, you can prese nt th at data grap hi ca lly in a colorfu l , more meaningfu l way. Your report will be easier to understand and more useful. TOPIC A Create a Pie Chart with a Drill-down You have e nhanc ed th e appearance of yo ur rep01ts in a number of ways. There are man y more options. In this topic, you ' 11 use charts to e nhance report s. There ai·e a number of ways yo u ca n present yo ur data using a cha1t. If yo u ha ve one gro up of dat a that yo u want to present , yo u mi ght want to use a pie chait. You can create a vis uall y appealing report th at represents your data usin g percentages . Chart Types When creatin g a chart in Crystal Repo rt s, there are man y types from whi ch yo u ca n choo se. It 's important to c hoose th e chart type that be st matche s th e data to be s hown. The available chait types and th eir functions are li sted in Table 7-1. Tabl e 7-1: Chart Types Type B ar Line Area Pi e Dou g hnut 3D Riser 3D Surface Fu nction Compares several sets of data by usi ng bars that are di splayed side by sid e. Di spl ays trends in data as a series of points con- nec ted by a lin e and is best suited for displaying data for a large numb er of grou ps. Di splays data as areas th at are filled with co lor or pattern s and is bes t suited for displaying data for a limited number of group s. Di spla ys data as a pi e, split and filled with co lor or patterns, a nd is usuall y used for one gro up of data . Similar to a pie chart , it di splays data as section s of a circle or dou ghnut , and can di s pl ay th e grand total in the hole in th e middl e. Similar to a bar chart, it di splays th e ex tremes in yo ur data in a se ri es of three-dimensional o bj ec ts, side by side , in a three-dim ensional plane. Di spl ays a topographic view of multipl e se ts of data . Crystal Reports 9. 0: Level 1 Type XY Scatter Radar Bubble Stock Numeric Ax.is Gaug e Gantt Chart Layouts Function Di spla ys a group of plotted point s that represe nt spec ifi c data in a pool of infom1ation , allowin g th e use r to detem1in e trends. Di s plays group data at the perimeter of th e radar and numeric values from the cent er of th e radar to th e perimeter, allowing the user to detem1in e how s pecifi c group data relates to th e whole of th e gro up data . Similar to an XY Scatter chart, it displays data as a se ri es of bubbles . Di spl ays high and low values for data and is use- ful for monitorin g financial or sal es activities . Is a bar, line, or area chart that uses a num eric or date/time field as its On Change Of field . Di spla ys values graphically as points on a gauge and i s usuall y use d for one group of data . Is a horizontal bar chart that provide s a graphica l illu stration of a sc hedule . The horizontal axis di s- plays a time span. The vertical axis displays a series of tasks or events. In addition to cha11 type , you need to determine the la yout of a chart. Cry stal Reports provide s yo u with four layout s from which to choo se, as outlined in Table 7-2. Table 7-2: Chart Layouts Layout Advanced Group Cross-tab OLAP Chart Expert Function Use to graph multiple chart values and when th ere are no gro up or summary field s in a report. Can be placed in any section of a report, except Detail s. Use to graph data in summary fi eld s. Mu st be placed in a summary section (Re port Header, Report Footer, Group Header, or Group Footer). Use to graph data in an existing cross-tab. Use to graph data in an existing OLAP grid . As yo u know, yo u can create a report manually or by using a Report Creation Wizard . You can use the Chart Expert to create a chai1 for tho se reports. The Chart Expert provides you with a number of options for designin g a chart. Initially, you can use the three tab s of the Chart Expe1t to se t those options. The tab s include Type , Data, and Te xt. Lesson 7: Creating and Modifying Pie Charts LESS •N 7 139 LESSON 7 140 Chart Elements When you create a chart, it includes components that each have a s pecific function . The fol- lowing table outlines so me of tho se components and their function s. Chart Com po nent Function Axjs A lin e that borders one sid e of th e plot area in a chart, pro vi run g a reference for measurin g or com- pmin g data. The y-axis i s usually vertical. The x-axis is us ually horizontal. Data points Dat a se ri es Represent data values in a c hart . Related data points plotted in a chart. Label Legend Marker Pl ot area Provides adrutional information about a marker. A box that id e ntifies the patterns or co lors assigned to a data seri es. A bar, area , plot , slice, or sy mbol that re prese nt s a sin gle data point or va lu e in a chart. Area wjthin the axes of a chart that includes the data series. How to Create a Pie Chart with a Drill-down Proc edure Refe re nce : To create a pie cha1t: 1. On the In se1t Tool s toolbar, click the Insert Chrut button. The Chart Expert is di s- played and the Type tab is active. 2. From the Chait Type list box , select Pie . 3. Select the Data tab . 4. In the La yo ut box , verify that the Group la yo ut is selected . 5. In the Data box , from the On Change Of drop-down li st box , se lect the field on which yo u want to base your chart. 6. From the Show drop-down li st box , select the field you want to dis pla y on the chrut. 7. You can use the Text tab to add new title s to yo ur chart. 8. Click OK to create the cha1t. Drill-down As you know, you can create a Drill-Down report , which allow s yo u to display s um- marized data and hide detail data. You can also create a drill-down in a chart. For example, in a pie chart that includes a drill-down , yo u can allow use rs to view detail information for each slice by double -clicking the slice. Crystal Reports 9.0: Level 1 ACTIVITY 7 -1 Creating a Pie Chart with a Drill-down Data Files: • Dr ill Down Ch ait .rpt Setup: No fi les are open in Crys tal Re ports. Scenario: Yo u have a report named Dri ll Dow n C hait that di s pl ay s he lm e t sales fo r a se lect grou p of custo mers . It 's a ve ry pla in report , and th e in fo rmati on is hai·d to interpret. Yo u wa nt to enhan ce the c hart graphica ll y. You think creatin g a pie chait is th e solution . What You Do How You Do It 1. In the Drill Down Chart.rpt report, why is the Details section gray? Save the report as My DrW Down Chart.rpt . .,. If you right-clic k in the gray area of the Details section and choose Sho w, the section turns white and is no longe r hidden . It's common to hide the detail when the chart has a drill-down such as this one . .,. Th is ch art is filtered by group . 2. Create a pie chart based on the Product.Color field that shows the Sum Of Orders_Detail Quantity. Lesson 7: Creating and Modifying Pie Charts a . On the Insert Tools toolbar , click the Insert Chart button [iJ . The Chart Expert is displayed and the Type tab is active. b. From the Chart Type list box , select Pie . c. Select the Data tab . d . In the Layout box , verify that Group is selected. e . In the Data box , in the On Change Of drop -down list box, verify that Product.Color is displayed. LESSON 7 • 141 LESSON 7 • 142 f. In the Show drop -down list box , verify that Sum Of Orders_Detail Quantity is displayed. Chart Expert ih • i· ',,·!<4" Type Data I Text I r Placement ----· l Place chart WI •fiiiiiii!Diiiiiiiii[::J:J Layout ·Data ~ Advanced On change of : ~ I Product. Color Group Show : ---1 :Ffilii. !sum of Ord ers_Detail .Quantity g. Click OK to create the chart . .p If you are in Design mode , the chart won't look the same as it does in Preview mode . 3. When you preview the chart, what happens when you place the mouse pointer over any of the pie slices? What happens when you double-click the slice of the pie labeled Black? Click Close View on the tab labeled Black to close the tab. Crystal Reports 9.0: Level 1 TOPIC B Change the Chart Title Now th at yo u 've created a pi e chait , th ere are many ways yo u can modify it. In thi s to pi c, yo u 'll modi fy a chart tit le. Suppose yo u created a chmt usin g th e C hait E x pe1t . After prev iew in g it , yo u rea li ze th at you wa nt to change th e o pti ons made when yo u firs t created the chai·t. There are man y ch anges yo u can mak e, in cl udin g modify in g a chart title . Yo u can qui ckl y an d eas il y make changes to an ex istin g ch art by usin g th e Chait Ex pert. How to Change the Chart Title Procedure Reference: To mo di fy th e chait title by us ing th e Ch ait Ex pert: 1 . Ri ght-cli ck the chait and choose Ch art Expert . 2. Select th e Text ta b. 3. Nex t to th e Titl e choice, un check Aut o-Tex t. 4. In th e Title fi e ld , e nt er th e new titl e. 5. Cli ck OK to appl y th e chan ges . ACTIVITY 7 -2 Modifying the Chart Objective: Ch ange th e chart titl e and mai·ker sha pe . Setup: The M y Drill D own Ch art .rpt re port i s di s pl ayed . Scenario: Yo u're pl eased with th e chart yo u've created . However, yo u wa nt to change th e chart tit le so th at it be tt er id e ntifi es the data in th e chart. What You Do 1. In Pr eview mod e , display the Te xt tab of the Cha rt Expert for the chart . Less on 7: Creating and Modifying Pie Charts How You Do It a. Rig ht-click the chart and choo se Chart Ex pert to display t he Chart Ex pert for t he selected chart. b. Select the Text tab . LESSON 7 143 LESSON 7 144 2. Change the default chart title to Quantity by He l met Color. a. In the Title field , uncheck Auto-Text. b. Change the title to Quantity by Helmet Color. T l'Pe I Data T eMl -Hies ----·---·-·····-·····------·--·······--- Auto-Text Hie: I ~,0-ua-nt-ity-by-H-elm_e_t C-olo-q c. Click OK. Quantity by Helmet Color I red Crystal Reports 9.0: Level 1 TOPIC c Format a Chart You've changed the title of a chait. Suppose you want to make some formatting changes to the chait. ln this topic, you ' U format a chait. Once you create a chai·t, there are many formatting changes you can make to it , including changing the chart type, the data labels, and the display status. You can make your chart visu- ally pleasing in a way that best accommodates the data being graphed. How to Format a Chart Procedure Referen c e : To format an existing pie chait: 1 . Select the chait. 2. To change a pie chait to a ring pie chart: A. Choose Cha1t-7Chart Options-7General. The Chait Options dialog box is displayed. B. Select the Layout tab. C. Select Ring Pie. D . Click OK. 3. To change the color of a piece of the pie: A. Select the piece for which you want to change the color. B. Choose Chart-7Chai·t Options-7Selected Item. The Formatting dialog box is displayed. C. lf necessary, select the Fill tab. D. From the color grid, select the new color. E. Click OK. 4. To change the border of a piece of the pie: A. Select the piece for which you want to change the border. B. Choose Chait-7Chart Options-7Selected Item. The F01matting dialog box is displayed. C. Select the Line tab. D. From the color g1id, select the desired border color. E. Click OK. 5. To tum off the display of labels: A. Choose Chart-7Chait Options-7General. B . Select the Data Labels tab. C. Uncheck Show Labels. D. Click OK. Lesson 7: Creating and Modifying Pie Charts LESSON 7 145 LESSON 7 146 ACTIVITY 7 -3 Formatting a Chart Objective: Change th e pi e chart to a ring pie chart, tum off the labels, and change the color of the slices. Setup: The My Dlill Down Chart.rpt repo1t is displayed. Scenario: The chait doesn 't represent the data exactly as yo u would like it to. The colors of the slices don 't match the associated labels. Therefore , yo u want to make the pie chart more vis ually appealing. So, you use the Chart Analyzer to make some format tin g changes. Figure 7-1 dis- plays the formatted ring pie c ha1t. Quantity by Helmet Color • bla ck 33 .3 '); • green 1 ~.3'); • red 4 .2% D wMe 47 .2'); Total : 100.0% Figure 7-1: The formatted ring pie chart. What You Do 1. In Preview mode , change the pie chart to a ring pie chart. How You Do It a. Verify that the chart is selected in Pre- view mode. b. Choose Chart _,Chart Options _,General. The Chart Options dialog box is displayed. c. Select the Layout tab. d . Select Ring Pie. e. Click OK to apply the change and close the Chart Options dialog box. Crystal Reports 9.0: Level 1 2. Change the color of each piece of the pie to its label color. 3. Change the border of the white piece of the ring to black. Lesson 7: Creating and Modifying Pie Charts a. Select the piece labeled Black. b . Choose Chart--?Chart Options --?Selected Item. The Fill tab of the Formatting dialog bo x is displayed. c. From the color grid, select Black (R1C1). Co lor: d. Click OK. e . Select the piece labeled Green. f. Display the Formatting dialog box. g. From the top row of the grid , select Green (R1C9). h . Click OK. i. Select the piece labeled Red . j . Using the Formatting dialog box , change the color to Red (R1C6). k. Select the piece labeled White. l. Using the Formatting dialog box , change the color to White (R1C2). & Don 't clo se the Form attin g dialog bo x. a. Select the Line tab. b. From the color grid , select Black (R1C1). c. Click OK. LESSON 7 147 LESSON 7 4. Turn off the display of labels . Save and close the report. TOPIC D a. Choose Chart~Chart Options~General. b. Select the Data Labels tab. c. Uncheck Show Labels to turn off the dis - play of labels. d. Click OK. e. Save and close the report. Present a Chart by Group You 've completed your first chart . At some point, you might want to present chart data by group . In this topic , that's what you'll do. Suppose yo u want to display only a specific gro up 's record in a pie chart. You can create a chart that will appear for eac h customer in the group , displaying only that group's data. How to Present a Chart by Group Procedure Reference: To present a chart by gro up: 1. Display the Chart Expert. 2. From the Chait Type list box , se lect th e chart type of your choice. 3. Select the Data tab . 4. In the Layout box , select the Advanced layout. 5. In the Data box, from the Available Fields list box, select the field on which you want to base your chart and click the top Right Arrow button to place the selec ted field in the associated box . 6. From the Avai lab le Fields list box, se lect the field yo u want to display on the chru·t and click the Show Value(s) Right Arrow button to place the selected field in the Show Value(s) list box . 7. You can use the Text tab to add new titles to your chart. 8. Click OK to create the chart. 148 Crystal Reports 9.0: Level 1 ACTIVITY 7-4 Presenting a Chart by Group Data Files: • Chart by Group.rpt Setup: No files are ope n in Crystal Rep orts. Scenario: You have a report named Chart by Group that co ntains a pie chart that s urnmaii zes the helmet sales for four c usto mers. You want to chan ge the di sp lay of th e data so th at it is gro up ed by qua ntit y of helmet sales by product name for the group. Figure 7-2 displays a sa mple of the new pi e chait. Alley Cat Cycles Helmet Types Xtre me Youth Helmet For Alley Cal Cycle::. L >ft:re me: Adult He:l me:t Trium ph Pro Helm et L___. Triumph 'vl;rtigo Helmet • Triump h Pro Helmet 35 .3 '1; • Triumph \,l;rtigo Helm et 35.3'1; • Xtre me A:Ju lt Helmet 23 .5 'I; Ill Xtre me Yo uth Helm et 5 .9 <i; Total : 100 .0% Figure 7-2: A sample of the completed pie chart. What You Do How You Do It 1. In the Chart by Group.rpt report, when you preview the report, what does the chart summarize? Save the report as My Chart by Group.rpt. Lesson 7: Creating and Modifying Pie Charts LESSON 7 149 LESSON 7 2. In the My Chart By Group.rpt report , create an advanced pie chart based on the Product.Product Name field that displays the Orders_ Detail.Quantity field. Move the chart to the Group Footer #1 section. 3. Delete Report Header A. a. Display the Chart Expert. b. From the Chart Type list box , select Pie . c. On the Data tab , in the Layout box, verify that Advanced is selected. d. In the Data box, from the Available Fields list box , select Product.Product Name and click the top Right Arrow button LJ to place the selected field in the associated box. e. From the Available Fields list box , select Orders_Detail.Quantity and click the Show Value(s) Right Arrow button to place the selected field in the associated box . Ion change of ::J Pr oduct.Product N ame· P. Order TapN Show value(s]: I Sum of Order s_Detail.Quantity f. Click OK to create the chart. The chart is displayed in a new Report Header A. g. Drag the chart to the Group Footer #1 section. a. Display the Section Expert. b. In the Sections box , verify that Report Header A is selected. c. Click Delete. d . Click OK. 4 . When you preview the chart, what items might you change on it? 150 Crystal Reports 9.0: Level 1 5. Change the chart title to Helmet Types. Save and close the report. Lesson 7 Follow-up a. Right-click the Alley Cat Cycles chart. Choose Chart Options ~ Titles. The Titles dialog box is displayed. b. In the Title text box , replace the text with Helmet Types. c. Click OK. d . Save and close the report. In this le sson, you created single data se1ies charts. You created and modified a pie chait. Then, you forma tted and grouped the chait. Now you 'll know how to pre se nt yo ur data graphi- call y, makin g it more vis uall y appealing. 1. What types of charts will you create in your reports? 2. What types of formatting will you apply to your charts? Lesson 7: Creating and Modifying Pie Charts LESSON 7 151 LESSON 8 Distributing Data Lesson Objectives: In this le sson, you will dis t:J.ibute repo1t data to other users . You will : • Export a report to Microsoft Excel. • Expo1t a rep01t for use in an alternate database environment. • Create a report definition. • Create mailing label s from a report. Less on 8: Distribu ting Data LESSON 8 Lesson Time 45 minutes to 1 hour(s), 15 minutes 153 LESSON 8 154 Introduction Yo u've reac hed th e end of yo ur j o urn ey. Yo u 've created bas ic li st and gro up re po11 s. Now yo u 're read y to di stribute re p011 s to o th ers. In thi s le sson, th at 's what yo u'll d o. Mos t peo pl e who need to view re port s wo n 't have Crysta l Re p01t s install ed . By using th e di s- tri buti on o ption s avai lable in Crys tal Re ports, you can di str ibute a rep011 or its assoc iated d atabase d ata in a nu m ber of ways. Th erefo re, non-Crys tal u ser s can access th at info rm atio n. TOPIC A Export to Excel Your re po11 is co mp lete. No w, yo u nee d to di stribute it to oth ers wh o do n't have access to Crystal Re port s. In thi s to pi c, that 's what yo u 'll do . S uppose yo u have a re p011 th at yo u need to di stribute to so me co-worke rs who don't have Crys tal in stall ed o n their comput er s . Th ey do , however, have sev eral Office a ppli cati ons insta ll ed. By ex porting a C rys tal Re ports fi le to an Office fi le fo 1mat, yo u ca n di stribute th at report to yo ur co-workers, and th ey can easi ly vie w th e repo rt in th e desig nated Office ap pl ica tion . How to Export to Microsoft Excel Proce dure Refe re nce: To ex port a re port to Mi cro soft Excel: 1. Open th e fi le yo u want to ex port . 2. On th e S tandard too lb ar, cli ck th e Export butt on . Th e Export di alo g box is di s played. 3. From th e Fmmat drop-d ow n li st, se lect th e a ppropri ate Exce l fo rmat. 4 . Fro m th e Des tin ati on dro p-d ow n li st, sel ec t th e a ppropri ate des tin ati on. '! If you select Applic ation as the destination , the exported file will open in the selected application if it is installed on your computer. 5. C li ck OK. The Exce l Form at Option s di a log box is di s pl aye d . 6 . Change th e fo 1ma tti n g o pti o ns as needed and cl ick OK. Th e Expo11in g Record s di alog box is di spl ayed , showin g the statu s of the ex port. Th e ex po11ed report is o pened in Microsoft Exce l. 7. Yo u can save th e re port in Excel by choos in g Fil e~S ave As. The Save As di alog box is di spl ayed . 8. In th e F il e Name tex t box, enter a name for the fil e . 9. Press E nt er to save th e fil e . 10. Cl ose Exce l. Crystal Reports 9.0: Level 1 Export Formats When expo1ting data from Crystal Reports, you can choose from a number of different format types. Options available to you will depend u pon wha t programs are installed on the PC being used for the export. Optio ns can inc lude the fo ll owing: • Character-separated values • Comma-Separated Values (CSV) • Crystal Repo1ts (earlier versions) • Data Interchange Format (DIF) • Microsoft Excel • HTML • Lotus 1-2-3 • ODBC • Page-separated HTML • Pagi nated text • \ Adobe Acrobat (PDF) \ • Record style (columns of values) • Repo1t definition • Rich Text F01mat • Tab-separated text • Tab-separated va lu es • Text • Word for Windows document When you export data, you might lose some or all of the fo1matting. Crystal Reports attempts to preserve as much of the f01matting as possible, based on the format type you choose. Less on 8: Distributing Data LESSON 8 155 LESSON 8 156 ACTIVITY 8-1 Exporting to Excel Objective: Export to an Excel 97 format. Data Files: • Expo1t.rpt Setup: Excel 2000 ha s been installed accordin g to the setup in stmctions. No file s are open in Crystal Reports. Scenario: You need to distribute the Ex p01t report to a number of yo ur co-workers. They do not hav e Crystal Reports in stalled on their computers. However, they do have Microsoft Excel in stalled. A sa mple of the expo1ted file is di splayed in Figure 8-1. A B C 'D Order Date 7 Bikes For7 Brotliers (132Jl0) 05/2 612001 10 11 Ae:ainst The Wind Bikes (133Jl0) I L 26 28 30 05126/2001 0612112001 01/0 8/2 001 0111912001 01/2812 001 E Order Amotmt $53.9 0 $479.85 $101 .70 $5.879 70 $1.583 .05 $101 .70 Figure 8-1: The exported report in Excel. G Crystal Reports 9.0 : Level 1 What You Do 1. Export the report Export.rpt to MS Excel 97-2000 format and have the file automatically open in Excel 2000. How You Do It a. In the file Export.rpt , on the Standard toolbar, click the Export button ~. The Export dialog box is displayed. , You can also choose File ~E x port. b. From the Format drop-down list , select MS Excel 97-2000. c. From the Destination drop-down list , select Application. EKport ,;;;,, Format: I -~ MS Exce l 97 -2000 iJ ._I _o_K __. De stinat io n: Cancel Help Th e se lected dll s will be installed bef ore the export pro ceeds d. Click OK. The Excel Format Options dialog box is displayed . e. Click OK to accept the default formatting options. The Exporting Records dialog box is displayed showing the status of the export . The report opens in Excel. 2. Did all the data transfer from Crystal to Excel? Lesson 8: Distributing Data LESSON 8 157 LESSON 8 3. In Exc el , save t he e x port file in the Student folder as My Export .xis and close Excel. In Crystal Reports, save the report as My Expor t .rpt. TOPIC B a. In Exce l , choose Fil e~S ave As to dis pl ay t he Save As di alog box. b . Display the contents of the Student fo lder. c. In the File Nam e text box, type M y Expor t . d . Pr e ss Enter to save the file. e. Close Ex ce l. f. Save the report as My Export .rp t. Export to an Access Database Now tha t you know how to export a rep01t to other formats , you might want to export a rep01t to a database. That 's what you 'll do in this topic. While Crystal Reports is a great repo1t tool, it doesn't provide any direct access to an underly- ing database . If you need to access the data in yom repo1ts , yo u can ea sily export that data to a database. Then, the exp01ted data will be availab le in the se lected database for you to modify as necessary. How to Export to an Access Database Procedure Reference : To export a report to an Access database: 1 . Open the file you want to expo1t. 2. On the Standard too l bar, click the Export button. Th e Export dialog box is di s played . 3. From the Format drop-down list, sel ec~ODB S\ 4 . Click OK. The ODBC Fonnats dialog box is displayed. 5 . From the list box , se lect MS Access D atabase. 6 . Click OK. The Select Database dial og box is disp layed. 7. From the appropriate folder in the Database Name list box , select the database file to which you want to expo1t. 8 . Click OK. The Enter ODBC Table Name dialog box is displayed . 158 Crystal Reports 9.0: Level 1 9 . In th e Table N ame tex t box, e nt er th e a pp rop ri ate name for th e table. 10 . C li ck OK. The E x portin g Record s dial og box is di s pl ayed , show in g th e statu s of the ex p01t. '! You can access t he Crysta l Repo rts Kn owledgeBase at http ://support .crystaldec isions .com for an articl e enti tl ed "scr_exporte xcel.p df " t hat di scus ses t roubl es hooti ng with Exce l export s. ACTIVITY 8-2 Exporting to an_ Access Database Setup: Access 2000 has been in stall ed accordin g to th e setup in stm ction s. M y Exp o1t.rpt rep01t is di s played . Scenario: Yo u want to use th e data in th e M y Ex port re port in an Access d ataba se. You co uld re-key th e in fo rm ati o n in Acce ss, but th at would take to o mu ch tim e . T he simples t way to ge t th at info r- mati on into th e data base i s to ex port it fr om Crys ta l Re p01t s . Less on 8: Distributing Data LESSON 8 159 LESSON 8 What You Do 1 . Export the file to the Access data- base named Examine.m db. Name the exported table Exporteddata . How You Do It a. Display the Export dialog box. b . From the Format drop -down list, select ODBC . The Destinat i on drop -down list bo x i s not available. ;J Format: 3 I OK I De stination: Cancel I 3 Help I The selected dlls wi ll be in stalled before the expor t proceeds c . Click OK. T he ODBC Formats dialog bo x is displayed. d . From the list bo x , select MS Access Database. e . Click OK. The Selec t Database dialog box i s di splayed . f. From the (:\Student folder in the Data - base Name list box , select Examine.mdb . g. Click OK . The Enter ODBC Table Name dialog box is displayed . h . In the Table Name text box , type Exportedda ta . Enter ODBC Table Name Table Name DK j EXPORTED DATpj C.3n ce l I i. Click OK. The Exporting Records dialog bo x is disp l ayed , show i ng the status of the export. 160 Crystal Reports 9.0: Le vel 1 2. Open and view the new table in Access 2000. a. Start Microsoft Access. b. From the Open An Existing Database list box, verify that More Files is selected. c. Click OK. The Open dialog box is displayed. d. From the list box, if necessary , select Examine.mdb. e. Click Open. f. From the Tables tab , double-click Exporteddata to open the exported data. 3. Does the data appear to be in a format that you could use in Access? In Microsoft Access 2000, click the application's Close button. ' Every field from the Crystal report, including page number labels and group headers , is exported as a sepa- rate field . This can result in unwanted fields . The primary data , however, is also intact in field /ro w format. When creating a report specifically for export to a database , avoiding labels and other special fields will help give you the data in the format desired . TOPIC c Create a Report Definition So far, you 've lea.med how to gro up , create formulas, set filters, apply conditional formatting, and more. However, there is no one central place to view all the objects created in a report and their formatting. In this topic, you'll create that central place. Suppose you want to keep the design of a report to refer to later or when you 're creating a new repo1t . Creating a report definition provides a single file that contains detailed info1mation concerning your report. Fmmulas are displayed; co nditi ona l format options are included; and groups, so1ts, and filters are identified. The repo1t serves both as a troubleshooting tool and a paper backup, and it's simple to create. Report Definition When you create a report, yo u might find that you want to document the design of the repmt. You can do that by creating a report definition file. It is a text file that contains the details of the design of a report, such as the tables included in th e report, the formulas that have been created in the report, and the objects in each section. Lesson 8: Distributing Data LESSON 8 161 LESSON 8 162 How to Create a Report Definition Procedure Reference: To create a report definition: 1. If necessary, di spla y the repo1t for whi ch you want to create a definition. 2. On th e Standard too l bar, click the Export button . T he Export dialog box is disp layed. 3. From the Format drop-down list , select Repo1t Defin ition . 4. From the De stination drop-down list, select the appropriate de stination. If you se lect Application, the file wi ll ope n in Notepad. If you select Di sk Fi le, the Choose Export File dia log box is disp layed. A. If necessary, in the File Name text bo x, enter a name for your report definition . B . If nece ssary, click Save . 5. Click OK. ACTIVITY 8-3 Creating a Report Definition Setup : The My Export.rpt report is di s played. Scenario: The My Export report is a good example of the type of reports you will contin ue to create. Therefore, you want to save the de sign of that report for future referen ce. You create a report definition so that you' 11 have the details to refer to later. What You Do 1. Create a report definition file for My Export .rpt that opens in the appropriate application. Maximize the application . How You Do It a . Disp lay the Export dialog box . b . Fro m the Format dro p -down list, select Report Definition. c. Fro m t he Destina tio n d rop -down list, select Application . d . Click OK. The r eport definition file is opened in Notepad. e . Max i mize Notepad . Crysta l Repo rt s 9.0: Level 1 2. When you scroll through the file, what type of information is displayed? 3. What information is listed under the 5.1 Page Header section? 4. What does the report definition file tell you about the @GroupSort formula? What information regarding formulas is listed under the 4.0 Formulas section? Close Notepad and the report. 'fl Formulas that are buried in group header names (that's whe re @G roupSort is displaye d) and in conditional formulas don 't display in the report definition file unless the user moves a copy of all those formulas into the report some wh ere . When all formulas are needed in the report definition file , "b uried " formulas must be added to the report . They can be suppressed so that they don 't display in the pre yiew copy. TOPIC D Create Mailing Labels At some point, yo u might need to distribute the same infmmation to multiple recipient s . You can expedite that process by creating mailing labels to distribute the information. In this topic , that's what you 'll do. Suppose you need to di stribute a letter to 50 people outlining the details of a company seminar. The letter is the same, so you can crea te it once and make copies. However, you need to address envelopes for all 50 letters. By using the Mailing Labels Report Creation Wizard in Crystal Reports, yo u can create a repmt th at is formatted to print on any size mailing label s . The expe1t makes it quick and easy to produce profes sional-looking mailing labels. Report Creation Wizard Definition: When you create a report in Crystal, you can create it manually, from a template or by using a Report Creation Wizard. A Report Creation Wi zard offers step-by-step instruc- tions that g uid e yo u through the creation of a report. Report Creation Wizards allow you to create reports quickly and easily. Lesson 8: Distributing Data LESSON 8 163 LESSON 8 164 Ex ample: Crystal Reports provides you with four Report Creation Wizards from which to choose. The following table lists the Report Creation Wizards and their function. Ex pert Guides the Cre ation of a Typical report Standard Cross-Tab Mail Label OLAP Report with a summarized grid Report with multiple colunms Report that contains a summarized grid based on an OLAP data source Lim itations of the Report Creation Wizard With the exception of special needs such as mailing labels, generally it is not recom- mended that the Rep011 Creation Wizard be used for repo11 creation. The tool is very useful for the novice Crystal Repo11s user, but has some limitations. It is not possible to return to the Repo11 Creation Wizard once a report is finalized (only the "template version" of it remains behind). Therefore, users are encouraged to learn how to use the standard built-in features rather than rely upon the wizard. How to Create Mailing Labels Procedure Reference: To create mailing labels: 1. Display the Crystal Reports Gallery. 2. In the Create A New Crystal Report Document box, verify that Using The Report Creation Wizard is displayed . 3. In the Choose A Wizard list box, se lect Mail Label. 4. Click OK. The Data page of the Mai ling Labels Report Creation Wizard is displayed. 5. Cl ick Database. The Data Explorer is displayed. 6 . Select a data source. 7 . Add the tab les that include the fields you want to include in the report. 8 . Close the Data Explorer. The Links page is displayed . 9 . Click Next to link the tables. The Fields page is displayed. 10 . Add the fields you want to include in the report. 11. Click Next. The Label page is displayed . 12. Select the label type. 13. Click Next. The Select page is displayed. 14 . If desired, select a subset of data to display. 15. Click Finish to create the report with mailing labe ls. Crysta l Rep orts 9.0: Level 1 ACTIVITY 8-4 Creating Mailing Labels Setup: No file s are open in Crystal Reports. Scenario: The Human Re so urces Manager ha s a bro chure that she needs to mail to all e mployees. S he needs you to create mailing labels to assist her with the mailin g. A sa mpl e of the labels i s di s- played in Fi gure 8-2. Nancy D avo lio 507 -20t h Ave . E . Port M oody , B C V3D 4F 6 A ndrew Fuller 908 \/If . Capital Way S uite 100 C oq uitl am, B C V3 H4J 7 Tim S mtth 3 03 01 -166th Ave . N .E . Apl#3D Nortr1 Va n co uver, B C V 3K 2G9 Caroline P alters on 1 6 Map le Lane \fliest Va n c ouver, B C V3 L 5 83 Figure 8-2: A sample of the mailing labels. What You Do 1. Display the Mailing Labels Report Creation Wizard. Lesson 8: Distributing Data How You Do It a. Click the New button to display the Crys- tal Reports Gallery dialog box. b. In the Choose A Wizard list box , select Mail Label. Crystal Reports Ga Create a New Cry s r. Using I (' Asa B r Choo se a Wizard - ~ St anda rd Cro ss ·T ab c. Click OK. The Mailing Labels Report Cre- ation Wizard is displayed. The Data page is active. LESSON 8 165 LESSON 8 166 2. Using xtreme.mdb as the database, add the Employee and Employee Addresses tables. 3. Link the selected tables . 4. From the Employee table , add the First Name field. 5. From the Employee Addresses table , add the Address 1, Address2, and City fields. 6 . Set the label type to Address (Avery 5160). Create the labels. Save the report as My Labels.rpt. a. In the Available Data Sources box , expand the History category. b . If necessary , expand the Tables category for xtreme.mdb. c. Add the Employee and Employee Addresses tables. d . Click Next. The Link page is now displayed. a. Verify that the table linking is correct. b . Click Next. The Fields page is now displayed. a. In the Available Fields list box, select First Name. b . Click the Add arrow button. The field is displayed in the Fields To Display list box. Fi elds to Di splay: llEI Emp loyee .Fir st Nam e a. In the Available Fields list box, expand the Employee_Addresses table. b. Select Address1 , Address2, and City. c. Click the Add arrow button. d. Click Next. The Label page is displayed. a. From the Mailing Label Type drop-down list, select Address (Avery 5160). Mailing Label Ty pe: I· .. -•• b. Click Finish. A report with mailing labels is displayed . However , you need to fix the labels by using formulas so the entire name and city, region , and zip code are displayed. Crystal Reports 9.0: Level 1 7. What formula would you write to concatenate the First Name and Last Name fields with a space in between the fields? 8. Create a formula named Employee Name that concatenates the First Name and Last Name fields with a space in between the fields. a. Display the Field Explorer. b. Create a formula named Employee Name . The Formula Workshop is displayed . c. Using the method of you r choice , create a formula that concatenates the First Name and Last Name fields with a space in between the fields. & Don 't clo se the Formul a Work shop . 9. What formula would you write to concatenate the City, Region, and Postal Code fields? 10 . 11 . There should be a comma and space between the City and Region fields and a space between the Region and Postal Code fields. Create a formula named a . On the Formula Workshop toolbar, click CityRegionlip that concatenates the New button. The Formula Name dia - the City, Region , and Postal Code log box is displayed. fields, such as Cincinnati, Ohio 12345. Display the Label tab. b. Create a formula named CityRegionlip . c. Using the method of your choice , create a formula that concatenates the City, Region, and Postal Code fields. There should be a comma and space between the City and Region fields and a space between the Region and Postal Code fields. d. Return to the report. Replace the First Name and City a. Delete the First Name field and replace fields with the Employee Name and it with the Employee Name field . CityRegionZip formula fields. Lesson 8: Distributing Data LESSON 8 167 LESSON 8 168 Suppress Blank Sections b. Delete the City field and replace it with the CityRegionZip field . 0 (@ C ityRegionZip c. Preview the report . ., Your CityRegionZip field might need to be re-sized. d. Save the report as My Labels.rpt. Some of the addresses are missing an @City RegionZip field because some of the Region fields in the database are empty. You could create a formula that deals with the empty Region fields . The formula would look like the one in the following graphic. st ringvar ECity : If I s Null ({Employee A.ddres ses.C1ty}) then ~~~~~9~;r"~~;~~~:CITY" else Ecity :• {8nployee A.ddresses.city}; If I s Null ({Empl oyee Addresses.Region}) then E:Re9ion :"' "MISSI~ REGION" el s e ERegi on :={Employee Addresses.Region}; Stringvar EZip; If I s Null ({Empl oyee Addresses.Postal code}) then EZip :-"MISSIN:'i ZIP" else EZip ; .. {Qnployee Addresses.Postal Co de}; ECity&". "M:Region&" "&Ezip At so me point, yo u mi ght want to hide an entire section in a report that is blank. You can easily eliminate unnecessary white space by using the Section Expert dialog box. Simply se lect the sec tion in th e Section Expe1t dialog box that yo u wan t to suppre ss, and check Sup press (No Drill-Down). Crystal Reports 9.0: Level 1 ACTIVITY 8-5 Suppressing Blank Sections Setup: The My Label s re port is disp layed. Scenario: You decide that it might not be nece ss ary to have blank sec tion s s uppressed . You wonder what wil l happen if yo u turn off that option. What You Do How You Do It 1. What happens when you turn off the Suppress Blank Section option for the Details C section? 2. Turn the Suppress Blank Section option back on. Close the report without saving. Lesson 8 Follow-up a. Turn the Suppress Blank Section option back on. b. Close the report without saving . In this lesson, you explored a number of ways to di stribute report data to other use rs . You exported a report to Exce l , expo1ted a report to an alternate d atabase, created a report defini - tion, and created mailing labels from a report . Now whe n you need to di stribute report data , yo u'll h ave a number of op tion s to choo se from to acco mplis h th e task . 1. In what format will you export reports? 2. Do you have reports for which you will create a report definition? If so , what types of reports are they? Le sson 8: Dist ributing Data LESSON 8 169 FOLLOW-UP 170 Follow-up In this course, you have learned the skills needed to build basic list and group repo11s. Now you can take the database information that you have and create reports in Crystal Reports that display the information you need. You can sort, fi lter, group , format, and enhance the data in your repo1ts, as needed, and then distribute those repo1ts in the appropriate format to the people who need them. What's Next? Crystal Reports 9.0: Level 1 is the first course in this series . Students who want to learn advanced features can take Cry stal Reports 9.0: Level 2 . Crystal Reports 9.0: Level 1 APPENDIX A Setting File Locations When creating a report in Crystal, the user points the program to wherever the data is stored . That information can be viewed from the menu bar by choosing Database~Set Location. The repmts you create in Crystal will often be used on other computers besides yours. If the data location for a report is changed, the report will not run properly until the u ser points the pro- gram to the new data location. To change the location of a data so urce : 1. Open any Crystal Reports data file listed in the C:\Student folder. 2. Choose Database~Se t Datasource Location to di splay the Set Datasource Location dialog box . 3. In the CmTent Data Source list box, select the database or table you want to replace. 4. In the Replace With list box , select the databa se or table you want to use. 5. Click Update. 6. Repeat steps 3 through 5 as needed . 7. Click Close to close the Set Datasource Location dialog box . 8. Repeat steps 1 through 7 for each Crystal Repmts class data file. '! When changing the location also involves changing the data type (that is , from ODBC to a direct-database con- nection), there will be no option to propagate changes . Each table location must be set individually. If the underlying database structure is different , a Map Fields dialog box wil l display allowing you to map fields from the original source field to the new location field . Appendix A: Setting File Locations APP EN DIX A 171 LESSON LABS Du e to classro om se tup co nstraints, some la bs cann ot be key ed in sequence immediately fo l- low in g th eir ass oc iated less on. Yo ur in strn ctor w ill tell you wheth er your labs can be prac ti ced imm edi ately fo llowing the le sson or wheth er th ey require se parate setup fr om th e main le sson conte nt. LESSON 1 LAB 1 Creating a Report Activity Time: 20 minutes to 30 minutes Scenario: Yo ur bo ss need s you to create a re port th at includ es some bas ic emp loyee information, includ- ing Empl oyee ID , Last Name, First Name, Hire D ate, R eg ion , and Po stal C ode. Since you alread y have th e data in th e xtreme database, you 'll use th at data to create the re p01t i n Crys tal. You mi ght change some of the name s of th e fie ld lab els, and you 'll need to make sure that th e fie ld s are al igned coITectl y. Your boss also wa nts you to include an appropriate re po1t titl e. An exa mple of the completed report is di spl ayed in F igure 1-A. Employee Informatio n EmQ loyee # L a st Name F irst Name H ire Date Region Zii;) Code 1 D avo lio Nan cy 312911991 1 2:00:00AM B C V3 D 4F6 2 Fu ll er Andrew 711211991 12 OO :OOAM B C V3 H4J7 3 L ever ling Janet 212711991 1 2:0 0:00AM B C V6M 8S9 4 P ea cock Marga r et 313011992 12 OO :OOAM B C V5S 6H7 5 Bu chanan Steven 91 13119 92 12 :00 :00AM SW1 BJ R 6 S uyama Mi c hael 91 1311992 12 OO :OOAM E C2 7JR 7 K in g Robe rt 1112911 992 1 2:00 00 .A.M RG1 9SP 8 C allahan Laura 1130 119 93 12 :00:00AM B C V7J 5G5 9 D odswo rth .A.nne 1011211993 12:00:00 AM WG2 7LT 10 H ell st ern Albert 31111 993 12 0 0 OOAM B C V2C BH3 11 S mith Ti m 1115119 93 12 :00 :00AM B C V3K 2G 9 12 P at ter so n Ca ro lin e 51151 1993 12 OO :OOAM B C V3 L 5S3 13 Bri d Justin 111 11994 12 :0 0:00AM B as-Rh in 67500 14 M arli n Xavier 111511994 12 :00 :00AM B as-Rh in 67 3 00 15 P er eira Laure nt 21111994 12 :0 0:00AM B as-Rh in 67000 Figure 1-A: An exam ple of th e compl eted My Em ployee lnf or mation.rpt repo rt. Less on Labs LE SSON LAB S 173 LESSON LABS 1. Create a blank report using the Employee and Employee Addresses tables in the xtreme.mdb database . 2. From the Employee table, add the Employee ID , Last Name , First Name, and Hire Date fields to the report. From the Employee Addresses table, add the Region and Postal Code fields to the report. 3. Change the field label for the Employee ID field to Employee#. Change the field label for the Postal Code field to Zip Code . 4. Save the report as My Employee lnformation.rpt. 5. Add a report title of your choice that displays in the font size and style desired, and center the title between the margins. 6 . If necessary , align and re -size the fields as desired. 7 . Preview the report. 8 . Save and close the report. LESSON 2 LAB 1 Sorting and Filtering Data Data Files: • Practice Sort and Fil ter.rp t Scenario: You and your boss are both satis fied with the res ults of the Practice Sort and Filter report yo u created. You will be using the report peiiodicall y, a nd eventuall y, th e size of the rep01t will increase . T he refore, yo u feel the report would be more u sefu l if it was sorted. Also , your bos s has informed you that he needs a li st of those empl oyees who live in the Bas-Rhin region immediately. An example of the sorted report is displayed in Fig ure 2-A. An example of the fi ltered repo rt is di splayed in Fig ure 2-B. 1. Open the Practice Sort and Filter.rpt report and save it as My Practice Sort and Filter .rpt . 174 Crystal Reports 9.0: Le vel 1 2. Sort the report by last name in alphabetical order. Emplovee Information Employee# Last Name FirstNarre Hire Date Refion Zip Co de 13 Brid Justin 111194 Ba s-Rhin 67500 5 Bucffinan St~en 9113 192 SW! 8JR 8 Callahan Laura 1/30/93 BC V7J 5G5 1 Davo lio Nancy 3129191 BC V3D4F6 9 Dodsworth Aru1e 10/12/93 WG27L T 2 Fuller Andrew 7112191 BC V3H4J7 10 Hell stem Albert 311193 BC V2C 8H3 7 King Robert 11/29/92 RGI 9SP 3 Le-.rerling Janet 2/27/91 BC V6M 8S9 14 Martin Xavier 1115194 Ba s-R hin 673 00 12 Patterson Caroline 5/15/93 BC V3L 5S3 4 Pfilcock Ivlar)?pret 313 0192 BC V5S 6H7 15 Pff"eira Laurent 211/94 Ba s-R l-.iin 670 00 11 Snith Tim 1115193 BC V3K 2G9 6 Suyarra Mi crael 9113192 EC2 7JR Figure 2-A: An example of the sorted report. 3. Filter the report using refreshed data so that only the employees in Bas-Rhin are displayed. Emplovee Information Empl oyee # 13 14 15 Last Name Brid Martin Pff"eira FirstNarre Justin Xavier Laurent Figure 2-B: An example of the filtered report. 4. Close the report without saving the changes. Lesson Labs Hi re Date 11 1194 111 5194 2/1194 Re:;ion Ba s-R hin Ba s-R hin Bas-Rhin Zip Code 67500 67300 67 000 LESSON LABS 175 LESSON LABS LESSON 3 LAB 1 Updating a Report with Grouping Activity Time: 10 minutes to 20 minutes Data Files: • Practice Employee Orders B y Region .rpt • Practice Employee Orders B y Regio n Solu tio n .r p t Scenario: You 've created a basic report named Practice E mpl oyee Orders By Region tha t incl udes the order amo unt for each empl oyee and the regio n where they 're located . You wan t to improve the repo1t by creating a gro u p, summarizin g the d ata, and fo1ma tt ing the group headers . When you 're do ne, yo u save the report as M y Practice Em ployee Orders By Region. 1. Open the Practice Employee Orders By Region.rpt report and save it as My Practice Employee Orders by Region.rpt . 2. Group the report in ascending order by the Employee ID field so that the group header displays on each page, and preview the report • ., Some of the field s f or particular record s are bl ank because the y don 't contain an y data. 3. Insert a summary total for the Employee.Employee ID group that sums the Order Amount field, and insert a grand total. 4. Format the group header for each group as desired. s. Preview the report. 6. Save the changes to the report. 7. Compare your results with the sample file Practice Employee Orders By Region Solution.rpt. 8. Close all reports. 176 Crysta l Reports 9.0: Le vel 1 LESSON 4 LAB 1 Working with Formulas Activity Time: 20 minu te s to 30 minute s Data Files: • Practice Customer Credit.rpt • Practice Customer Credit Solut ion.rpt Scenario: You 've created a report for the Credit Manager named Practice Customer Credit, whi ch incl ud es customer credit IDs and amou nt s . The Credit Manager like s the report, but need s you to make a few changes to it. She also needs the users to be ab le to quickly reference a specific credit auth01izatio n number as soo n as they ope n the report . 1. Open Practice Customer Credit.rpt and save it as My Practice Customer Credit .rpt. 2. Create a formula field named Contact Name that concatenates the Customer. Contact First Name, Customer.Contact Last Name, and Customer.Phone fields. There should be a space between each field. 3. Place the Contact Name field in the Group Footer to the right of the Sum Of Credit.Amount field and re-size it. 4. Edit the Contact Name field so that it includes a comma after Customer. Contact Last Name. 5. Create a parameter field using the following information: a. The field should be named Authorization and prompt the user to select a credit authorization number. b. The Browse Table should be set to Credit . c. The Browse Field should be Credit Authorization Number. d. The default list should include all the values. e. Re-size and place the parameter formula at approximately the 6" mark in the Page Header section. 6. Apply bold formatting to the parameter formula field. 7. Create a selection formula where Credit.Credit Authorization Number equals the Authorization parameter formula. 8. Preview the report using a value of your choice. Lesson Labs LESSON LABS 177 LESSON LABS 9. Save the report. 10. Compare your report to the file Practice Customer Credit Solution.rpt. 11. Close all open files. LESSON 5 LAB 1 Applying Various Formatting Activity Time: l 0 minutes to 20 minutes Data Files: • Practice Formatting .rpt • Practice Formatting Solution.rpt Scenario: You 've just finished building a report named Practice Formatting . You need to apply some for- matting to the report before you show it to your boss. You think adding a border, some color, some lines , applying paragraph formatting, and changing the margins is a good idea. 1. Open the report Practice Formatting.rpt. 2. Save the report as My Practice Formatting.rpt. 3. Add a colored border of your choice to a field of your choice. 4. Add lines to the report where desired. 5. Change the background color of any field to a color of your choice. 6. Indent the Group Header #2 field 0.25". 7. Preview the report. 8. Compare your results with the sample file Practice Formatting Solution.rpt. 9. Close all open reports, saving if prompted. 178 Crystal Reports 9.0: Level 1 LESSON 6 LAB 1 Enhancing a Report Activity Time: 10 minute s to 20 minute s Data Files: • Practice Enhance.rpt • Practice Enhance Solution.rpt Scenario: You 've created a report named Practice Enhance . You wa nt to di splay th e company logo behind the data in the report. You also want people using the report to be able to contact you via email. 1. Open the report Practice Enhance.rpt. 2. Save the report as My Practice Enhance.rpt. 3. Insert two sections below the Details D section. 4. In the Page Header section, underlay the Xtreme.bmp graphic at approximately the 0.25" mark. s. In the Report Footer section , insert a text object that reads Have questions, click here! 6. Re-size and format the text object as desired. 7. Create a hyperlink for the text object to your email address. , If you don't ha ve an email address , make one up. 8. Preview the report. 9. Test the hyperlink, canceling out of any open dialog or message boxes. 10. Compare your results with the sample file Practice Enhance Solution.rpt. 11. Close all open reports, saving if prompted. Lesson Labs LESSON LABS 179 LESSON LABS LESSON 7 LAB 1 Creating a Pie Chart Data Files : • Practice Chart.rpt • Practice Chait Solution.rpt Scenario: You've created a report named Practice Chait th at summarize s ord er amounts by region. The chmt is diffic ult to interpret and wo uld be more effec ti ve if the data was displayed vis uall y. You think the an swer is to create a pie ch art. 1 . Open Practice Chart.rpt. 2 . Save the report as My Practice Chart.rpt. 3. Using the Chart Expert , perform the following tasks: a. Create a pi e c hart based on the Customer .Region field that shows the Sum Of Orders.Order Amount. b . Chang e the chart title to a title of your choic e. c. Format the chart as desired . 4. Compare your report to the file Practice Chart Solution.rpt. 5. Close all open reports, saving if prompted . 180 Cry sta l Reports 9.0: Le vel 1 LESSON 8 LAB 1 Exporting Reports and Creating Report Definitions Activity Time: 10 minute s to 20 minutes Data Files: • Practi ce Employee Data.rpt • Practice Rich Text Format Solution.rtf • Practice Rep01t Definition Solution.txt Scenario: You've created a report in Ciystal named Practice Employee Data. You want to export the report as rich text so that you can add the data to a word process ing document. 1. Open the file Practice Employee Data.rpt. 2. Save the report as My Practice Employee Data.rpt. 3. Export the report to a rich text format so that it automatically opens in the appropriate application. 4. If necessary, don't revert to the saved file. s. Save the exported data in the C:\Student folder as My Rich Text Data.rt[. 6. Compare your report to the file Practice Rich Text Format Solution.rtf. 7. Close all RTF files and their associated applications. 8. Using the My Practice Employee Data.rpt file , create a report definition that opens in the associated application. 9. Save the report definition file as My Practice Report Definition.txt. 10. Compare your report to the file Practice Report Definition Solution.txt. 'fl You can't have two files open at once in Notepad . Therefore, you must open the solution file in a se parate " Notepad window. 11. Close all open reports, saving if prompted. Lesson Labs LESSON LABS 181 LESSON LABS 182 12 . Close Crystal Reports. Crystal Reports 9.0: Level 1 SOLUTIONS Lesson 1 Activity 1 -1 2. Using the ToolTips, Table 1-1, and the following graphic as a guide, identify some of the components of Crystal Reports. F Report Footer section I status bar A Page Header section 0 Inse rt Tools toolbar K Details section G Page Footer section J Report Header section P guideline EJ Fiie Edi: Vtew Insert Formot: Oat_tibMe Repxt ~ ~ --E C Formatting toolbar M field L ruler E menu bar B Standard toolbar N field label H Expert Tools tool bar _p__ Design tab : o ~ · 1.h l!HA t'.!l i7 '; e ... · " · • ·= m;i l:O' Di . 111 fiiii':'.:] ,1 :-B D Solutions '} :2 ' fJ I fJ j ab 18 l: ill! • l3o !!:i !!i;l DI 0 If -Q "o:!i:Y C1fl ro!1 ·<'~ ·~~~.· --H ~ ReooitFc.ota Paot-Footei Cunorrrr ID I Cusiorrer ~ :ustomer ID ,Gustomer 1'•<1.llle I M I I > ·~ • . _L J Ord.-JD 1 'om .-Du · brdrrAmlmi A urner Jl Order Date )rder Amount K •F fnnl Date : •G . ---------~l---~------~=--=--'"'""""!'="'"="-"~~~ -----·-······.,···-·· . ···-r T SOLUTIONS 183 SOLUTIONS 184 3. What information can you gather by placing the mouse pointer over the Customer ID field label? A Too/Tip is displayed indicating that the Label is a text object. What information can you gather by placing the mouse pointer over the Customer ID field? A Too/Tip is displayed indicating the table from which the field was inserted, the field name, and the field type. 4. On the Standard toolbar, what is displayed next to the Design tab and in the report when you click the Print Preview button l~I? The actual data in the report is displayed on the Preview tab . 5. What data is contained in the Order Date field? The order date and time. The data type of the Order Date field is Date Time. The format of the data in the Order Date field is : a) 01 /01 /02 12:00AM . b) 1 /1 /02 12 :00:00AM . c) 01 /01/200212:00:00 . ./ d) 1I1 /2002 12:00:00AM . 6. What data is contained in the Page Footer section? The print date . The data type of the Print Date field is Date. The format of the data in the Print Date field is: a) 01 /01 /2002. b) 01/01/02 . ./ c) 1I1 /2002 . d) 1I1 /02. Activity 1-3 4. What does the Links tab indicate has occurred? The tables are automatically linked. Is the link correct? Yes. Crystal Reports 9.0: Level 1 Activity 1 -4 2. On the Preview tab, what information is displayed between the report navigation but- tons? Crystal tells you what page is currently displayed in the report. 3. The Date Age Indicator is displayed to the left of the report navigation buttons. What data does it display? a) The system date and time . b) The date and time the report was opened . ./ c) The date and time the data was last refreshed or initially retrieved. d) The date and time the report was last saved . Activity 1-6 1. When you preview the report, what do you notice about the alignment of the fields? Answers will vary. Lesson 2 Activity 2-1 3. What is the customer name, order date, and order amount of the found record? The customer is Mad Mountain Bikes. The order date is 312 102. The order amount is $1 ,439.55 . 4. What happens when you try to find record 2183 by typing the text 2183 in the Find What text box? A message is displayed indicating that the record was not found. How should the order number be entered in the Find What text box? 2 , 183 . Activity 2-2 2. The first order date listed is 2118100. 5. How many records are listed for Beach Trails and Wheels? One record. Activity 2-3 1. There are 2, 192 records currently displayed in the report. Solutions SOLUTIONS 185 SOLUTIONS 186 4. How many records are displayed? 1,281 records . Lesson 3 Activity 3-1 3 . On the Design tab, how has the display of the sections changed? Two new sections , the Group Header #1 and Group Footer #1 , are added to the Design tab. They surround the Details section. 4. What is displayed in the Group Header #1 section? Crystal Reports automatically places a group name object in the Group Header #1 section that prints the contents of the field on which the group is based . What is displayed in the Group Footer #1 section? It is empty. 6. What has been added to the contents of the Preview tab? The Group Tree view is displayed, allowing you to view the groups in an outline form. Activity 3-2 4. Using the Preview tab, the summary for Bikes For Tykes is $35,376 .01 . The grand total for the report is $3,982,734.36. Activity 3-3 1. By previewing the report, what do you notice about the data for BBS Pty? The data is split across two pages . The data for BBS Pty is on page(s) 1 and 2 . What do you notice about the group header for the BBS Pty data? The group header is displayed only at the bottom of page 1 where the data begins. A group header doesn 't appear at the top of the following page where the BBS Pty data continues . 4. When you preview the report, how is the data for BBS Pty different? All the data is displayed with a group header at the top of page 2. Activity 3-4 3. How do the sections change based on the new group? There are now additional Group Header #2 and Group Footer #2 sections. Crystal Reports 9.0: Level 1 Activity 3-5 2. When you preview the report, how is the data displayed differently? Answe r s will vary, but might include: The customer names are displayed beneath each date by year . There are also many more Group Sum totals. Activity 3-6 2. When you preview the formatted group header, what is wrong with the group header text? Answers will vary , but should include: The text is cut off at the bottom. Activity 3-7 1. Before you filter the report, how many pages of data are displayed? Answers will vary , but should be around 49. 3. When you click the Show Formula button, the Sum Of Orders.Order Amount tab indi- cates that you will be creating a Group Selection formula for that field . 4. What type of formula is Orders.Order Amount? A record selection formula . 6. When you preview the filtered data, how many pages of data are displayed? Answers will vary, but should be around four. Activity 3-8 3. The top company is Psycho-Cycle with a sum of $102,699.95. Lesson 4 Activity 4-1 2. What formula would you use to calculate the order total for each order? Answers will vary , but should include : {Orders Detail.Unit Price]*{Orders Detail.Quantity]. 7. The order total for AIC Childrens is $101.70, which equals $33.90, the unit price, times the quantity, which is 3. 8. What do you notice about the font for the Order Total field? Answers will vary, but should include : The font is different than the font for the rest of the report. Solutions SOLUTIONS 187 SOLUTIONS 188 9. The font for the Order Total field is Arial . The font for the Quantity field is Times New Roman . -- Activity 4-2 1. How would you edit the formula to include the shipping charges? Answers will vary , but should include : {Orders Detail.Unit Price}*{Orders Detail . Quantity}+ 10 . 95 . 5. The order total for 7 Bikes For 7 Brothers is $64.85, which equals $53.90, the unit price, times the quantity, 1, plus the shipping charges of S 10. 95. Activity 4-3 l. What formula would you write to create a field that starts with the Customer Name field and includes the Customer ID field? Answers will vary, but should include : {Customer.Customer Name}&" ("Et {Customer. Customer ID}Et")" Activity 4-4 2. Has the formula been deleted from the report? Answers will vary , but should include: No , the formula is still in the report. Activity 4-5 3. Two customers i n MA had orders between 1/1/2001and1/31/2001 . Activity 4-6 3. Eight customers in MA and CA had orders between 1/1/2001and1/31/2001 . Activity 4-7 2. The default Value Type is String. Activity 4-8 1. When you preview the Nulls.rpt report, what do you notice about the numbers in the Credit Amount field? They are negative numbers. 2. What formula would you write that returns the absolute value of the Credit.Amount field? Answers will vary , but should include: Abs ({Credit .Amount}). Crystal Reports 9.0: Level 1 5. When you preview the report, what do you notice about Spokes 'N Wheels Ltd. and some of the other customers? They have no credi ts . 6. What formula would you write that replaces the sum of the AbsCredit field with 'No Credits" when there is nothing in the field? Answers will vary , but should include : If lsNull(Sum ({@ AbsCredit},{Customer .Customer ID}))Then "No Credits "Else ToText(Sum({@AbsCredit},{Custome r .Customer ID}),O). 10. When you preview the report, what do you notice about Spokes 'N Wheels Ltd. and the other customers who had no credits? The Credits column states "No Credits ." Lesson 5 Activity 5-1 1. In the Formatting.rpt report, there are approximately 64 pages. 3. What happens when you try to move the section boundary up more? You can 't . Why can 't you further decrease the amount of white space in that section? Answers will vary, but should include : You can 't make the section smalle r because the field and the vertical guideline are in the way . 5. When you preview the report, how many pages are in the report now? Less than 64. Activity 5-4 3. What other field backgrounds might you change on this report? Answers will vary, but might i nclude : The field labels , the page header information, or the page footer information . Activity 5-5 2. For what other reasons might you change the margins of a report? Answers will vary, but might include: If you 're going to print the report on both sides , or to make more space in the report because the fields don't fit . So lutions SOLUTIONS 189 SOLUTIONS 190 Lesson 6 Activity 6-1 2. What happened to the Page Header section? There is now a Page Header A and a Page Header B. 3. What is in Page Header A and B? Page Header A contains the field labels. Page Header B is empty. Activity 6-2 2. When you place the mouse pointer over the inserted object, what does the ToolTip indicate? The inserted file is an OLE object. 3. What happens to the menus and toolbars when you double-click the OLE object? Answers will vary, but should include: The menus and toolbars have merged with Microsoft Word. Activity 6-4 1. In the file Cond i tional Format.rpt, the number of customer orders in January 2000 is 163. Currently, 73 pages of the report are displayed. 4. How has the Conditional Formula button for the Suppress (No Drill-Down) option changed? The icon on the button has changed indicating that a formula has been created. 5. When you preview the report, now there are 21 pages of the report displayed. The remaining data is suppressed. - 6. The number of customer orders in January 2000 is 163. Activity 6-5 4. When you preview the report, what happens to the mouse pointer when you place it over the hyperlink? The mouse pointer changes to a pointing hand. 5. What does the Link To.doc contain? A list of all customers and their phone numbers in Word format. Crystal Reports 9.0: Level 1 Activity 6-6 1. When you preview the Suppress If Blank.rpt report, what do you notice about some of the addresses? Answe rs will vary , but should i nclude : There is a blank line between the Address1 fi eld and the @CityRegionlip field. Also , some of the region data i s missing . Lesson 7 Activity 7 -1 1. In the Drill Down Chart.rpt report, why is the Details section gray? Answers will vary , but should include : Gray sections are suppressed . 3. When you preview the chart, what happens when you place the mouse pointer over any of the pie slices? The mouse pointer changes to a magnifying glass , i ndicating that the chart is a dri ll · down . What happens when you double-click the slice of the pie labeled Black? A tab labeled Black is created that displays the underlaying details for that slice of the pie . Activity 7 -4 1. In the Chart by Group.rpt report, when you preview the report, what does the chart summarize? The pe rc ent of helmet sales by type . 4. When you preview the chart, what items might you change on it? Answers will vary , but might include : The chart title, labels , or colors . So lutions SOLUTIONS 191 SOLUTIONS 192 Lesson 8 Activity 8-1 2 . Did all the data transfer from Crystal to Excel? Yes. Activity 8-2 3 . Does the data appear to be in a format that you could use in Access? Answers will vary, but might include: Yes . Activity 8-3 2 . When you scroll through the file, what type of information is displayed? Answers will vary, but might include: The version of Crystal Reports that the report was created in; types of formulas in the report; and the fields in each section, including their type and formatting. 3 . What information is listed under the 5.1 Page Header section? Answers will vary, but might include: Settings and the fields in that section. 4 . What does the report definition file tell you about the @GroupSort formula? Answers will vary, but might include: It tells you where the formula is located in the report. What information regarding formulas is listed under the 4.0 Formulas section? There is no mention of any formulas , including the @GroupSort formula or its components . Activity 8-4 7 . What formula would you write to concatenate the First Name and Last Name fields with a space in between the fields? {Employee.First Nam~}Et" "&{Employee.Last Name}. 9 . What formula would you write to concatenate the City, Region, and Postal Code fields? There should be a comma and space between the City and Region fields and a space between the Region and Postal Code fields. {Employee _Addresses. City}&", "&{Employee _Addresses. Region}&" "&{Employee_ Addresses.Postal Code}. Crysta l Reports 9.0: Level 1 Activity 8-5 1. What happens when you turn off the Suppress Blank Section option for the Details C section? Extra lines are displayed where individuals have an empty Address2 field. Solutions SOLUTIONS 193 GLOSSARY ascending order Records are sorted from smallest to largest' (t hat is, 1 to 9, A to Z). attribute formula Requires additional information to specify the formatting prope1ties and use s of an If-Then- Else statement. boolean formula Any formula that returns a true/fal se value. comparison operator A state ment you can use to compare data in a field that has a fixed value with the content of another field. components The parts of the formula. concatenate To string fields together-for example, com- bining the first and last name fields to show a full name . conditional formatting Formatting that is applied based on specific criteria. criteria The rules on which a decision is based. Crystal Repository A central filing cabinet in which to store com- monly used report objects. data type The type or classification of data represented within a field. All fields in a database are assigned a specific data type, such as su;ng, numeric, boolean , date , and so on. Formatting options in Crystal Reports will vary based upon the field 's data type. Glossary database A collection of related information or data. descending order Record s are sorted from largest to smallest (that is, 9 to 1, Z to A). dictionary A structured and sec me view of data that can be placed between a user and the data . fie ld A category of information. filter Allows you to di sp lay certain records based on specific criteria. formula An equation that performs operations on data. grid A series of row and column coordinates to assist you in aligning fields. group A collection of related data. hyper link A link between two objects that is di splayed as text or an icon . index A pointer assigned to a field that identifies the location of a record using that field. join types Determine how the records from more than one table are matched. link A common field between two or more tables that is used to connect the tables. GLO SS ARY 195 GLOSSARY 196 natural order The order in which data in entered into a database. null The absence of data. (Null should not be con- fused with zero.) ODBC (Open Database Connectivity) A database that allows many different types of data to be accessed by a s ingle application. That application has to communicate with only one set of ODBC file s to instantly work with any source of data accessible by ODBC. OLE Object Linking and Embedding allows you to link or embed an object, such as a spread- s heet or video clip , into a document, called the "container application." operators Th e actions you can use in your fonnulas to specify the ty pe of calculation that you want to perform. parameter A field that prompts a user of a report to enter infom1ation. query A request that retrieves specific fields and/or records from a database. Report Creation Wizard Offers step-by-step instructions that guide you through the creation of a repo1t. report definition file A text file that contains the details of the design of a report, s uch as the tables included in the report , the formulas that have been cre- ated in the report, and the objects in each section. selection formula A filter used to limit the data included in a report. SQL (Structured Query Language) A database language used to retrieve , orga- ni ze, and manipulate database tables , fields , and records . In Crystal Reports, SQL is used in the retrieval and organization process only. syntax The rule s that you mu st follow when organiz- ing the components of a fonnula. Crystal Reports 9.0: Level 1 INDEX A ascending order, 37 attribute formula, 127 B background color, 110 blank sections, 168 boolean formula, 127 borders , 107 boxes, 108 bulleted list, 122 c charts, 145 by group, 148 compo nents, 140 layouts, 139 title, 143 types, 138 comparison operator, 40 components, 62 concatenate, 70 conditio nal formatting, 63 criteria, 39 Crystal environment, 3 Crystal Repository, 118 D data finding, 34, 37 sorti ng, 37 data type , 6 database, 15 DayOfWeek function, 128 default settings , 9 descending order, 37 dictionary, 15 drill-down, 140 Index E export formats, 155 F field , 6 changi ng font , 11 formatting, 10 moving , 30 re-sizing, 27 filter , 39, 64, 85 , 88 formula, 62, 63 creating, 72 deleting , 83 editing, 78 fixing null field s, 96 G group, 46 , 47 adding, 56 changing order, 57 deleting , 58 group header, 60 group options, 53 group summaries, 50 H hyperlinks, 131 If-Then-Else function , 96 index, 15 IsNu ll function , 96 J join types, 16 L lines, 107 link , 15 INDEX 197 INDEX 198 M mailing l abels, 164 margins, 112 multiple criteria, 85 , 88 N natural order, 37 null field, 96 0 Object Linking and Embedding, 122 Also See: OLE objects, 134 ODBC, 15 OLE, 122 Open Database Connectivity, 15 Also See: ODBC operators, 63, 70 order of operation s, 78 p page breaks, 118 parameter field , 90 creating, 91 using se lection formulas, 92 pi e chart, 140 Q query, 15 R records , 37 relational database, 15 report adding tables, 19 adding titles, 27 creati ng , 2, 17 deleting tables, 19 exporti ng to Access , 158 exporting to Excel , 154 identifying types, 3 navigatin g, 24 planni ng, 2 previewing, 5 , 24 printing, 24 Report Creation Wizard , 163 report definition file, 161, 162 report options, 11 s sections, 116, 117 hiding , 134 suppressing, 128 , 135 selection form ula , 64 sort field changing order, 37 remo vi ng , 37 special fields, 105 SQL, 15 Structured Query Language , 15 Also See: SQL summary operations, 50 syntax, 63 T text objects, 26, 27 Top N sort group, 65 ToText function, 96 w white space, 102 Crystal Reports 9.0: Level 1 I I J j • Crystal Reports Environment ~,t.,..A ·;.~'.:.f"'.:i'r'\•f:..1 •"f_.:w''"·"•'•'!'r.kl 'h,,. ··:-·' ·• .· ·' · · wwwajy:wu-iRl:i:.tJzil L'll 'lf'· 1 w1 .,~n~o! 1.~r ,,.; '-;!if ... ...r.J -• 6 File Edt View Insert F:m: oai"aii.ne Report wirdiw H;,;~ ··---· -······ . Menu bar ci ~. 1.. a~~ fJ 1 •• m It.ii' Di . ,.,,cm :::J 'tr? 'Standard Toolbar bar I J ,I J ,, ' .:; I 1 Fonnattlng Toolbar Insert Tools toolbar j Expert Tools toolbar,. Ruler Preview I Preview Tab,..-...,../ Guideline R •nnt t H••d•• \ • . \:!:.)' . 1 . . . ¢ . . . 2 . . • I • • • 3 . . . I • • • 4 • . • ¢ . . . 15 . . • ¢ . . . " . 1.-1 di 8 g,_-~ ~ t·:~.'~~ Field label Customt-1· 01·dft's f,'t~ Ruler Fot Help. press F1 ==========~~===============~~~~:~ ¥ -~~'·t •. Account u ~· N , • "'-'--i1 ~ n.::Y' ~'.t:',(i_ • ='=====~· ~-~-~-~-~-~~~=::ilo'~u~1t~o~m~er~~a~m::g'==============*;::~l.?.llll.l...!t~~~;:-==i~~~~=~==;::==~~~~;:-======'.ti'~~ Customer ID Customer N!lllle Order ID Order Date Order Amount ~t;;).M ====:;::!====::::::!::=;;::==========........__===================!:::::!:=====~=========~==========:::::::i============!!;r~!~ -.........field r ;:":·r~ l !'·':i~ij =================================================================:::;i;(~ .... ;~ i.Y'.~~ .... ~ ,., .... ~· ·1 .. ... . . ' ~ "' ' . . . . ... . . ·.r ·i ...... ,i,• (J..,, Records: 2192 Total records in database ,-. __:~ ! ~f t i 1~!. E:3treme Mountain Bikes How to create group selection based on countries with sales Over $10000 ~~:;;~~-~~ .c_~l~~~0~;~t:~~~~Z;~~~~317-c~r:r~~~~f3:~~:~~~;-~~~·.---~·~~~ ~~-r~-~~::~~~--;~~~ ~::~;:~~r .~.,;t:70z:~::;: Argentina Bicicletas Buenos Aires Mendoza Argentina 6755 56 $55 ,664 .35 $55,664.35 Australia Down Under Bikes New South Wales Australia 2061 $1 ,621 .20 Canberra Bikes New South Wales Australia 5353 $10 ,662 .75 Kangeroo Trikes Victoria Australia 2155 $9 ,594 .70 Bruce's Bikes Victoria Australia 4774 $1 ,138.09 Peddles of Perth Western Australia Australia 3326 $8,945.25 Koala Road Bikes Queensland Australia 4600 $6 ,744.80 Tasmanian Devil Bikes Tasmania Australia 0912 $1 ,739 .85 $40,446.64 Austria Piccolo Salzkammergut Austria A-5020 $201 ,000 .00 $201,000.00 Bahamas Beach Cycle and Sport New Providence Bahamas 5466 $14,463 .35 $14,463.35 Belgium Belgium Bike Co. Brussels Belgium 47899 $200 ,000 .00 $200,000.00 Brazil Brasilia Bikes Inc. Distrito Federal Brazil 23293-300 $14 ,301 .70 Salvado Bike Store Rio Grande do Sul Brazil 90880-390 $5,879 .70 Brasilia Outdoors Belo Horizonte Brazil 31015-040 . $13,466.50 Rio De Janeiro Bikers Rio de Janeiro Brazil 20093-900 $133,551 .30 Saveiros SA Sao Paulo Brazil 20093-400 $5,879 .70 Outdoors Ltda Sao Paulo Brazil 04082-001 $2,378 .35 $175,457.25 Canada Cycle Fever ON Canada M6S4X9 $6 ,629 .1 0 Wheels 'n' Dea ls PQ Canada H9W 1E7 $2 ,850 .85 Pedal Pusher Bikes Inc. BC Canada V3C 5U3 $39,277.38 Halifax Cycle Centre NS Canada B3R 1V9 $1 ,489.05 Dag City Cycle MB Canada R3J ON9 $1 ,276.74 Don 't Tread On Me ON Canada K1A3W9 $5 ,879 .70 Bikes for Tykes BC Canada V6E 2S5 $1 ,800.00 Cycles and Sports BC Canada V6G4V3 $38,199.10 Biking's It Industries BC Canada V5C 7Z1 $30 ,348 .92 Crazy Wheels BC Canada V3C2Y8 $38 ,280.53 $166,031.37 1 How to calculate a percentage of the grand total Argentina Bicicletas Buenos Aires Aruba Aruba Sport Australia Bruce's Bikes Canberra Bikes Down Under Bikes Kangeroo Trikes Koala Road Bikes Peddles of Perth Tasmanian Devil Bikes Austria Piccolo Bahamas Beach Cycle and Sport Bangladesh . Dhaka Bike Store Mendoza Subtotal for Argentina St. George Subtotal for Aruba Victoria New South Wales New South Wales Victoria Queensland Western Australia Tasmania Subtotal for Australia Salzkammergut Subtotal for Austria New Providence Subtotal for Bahamas Dhaka Subtotal for Bangladesh E:3trerne Mountain Bikes $1 ,664 .70 $1 ,664 .70 $5,879.70 $5 ,879 .70 $17 .50 $479.85 . $45.00 $107 .80 $64.42 $5,879.70 $3,305.72 $9 ,899 .99 $25.98 $28 ,813.41 $659.70 $659.70 $65.70 $65 .70 0 .04% 0 .04 % 0.14% 0 .14% 0 .00% 0 .01% 0 .00% 0 .00% 0 .00% 0 .14% 0 .08% 0 .24% 0 .71% 0 .71 % 0.02% 0 .02 % 0 .00% 0.00% Add new formula features to your report Report on Shipping Status Bikes for Tykes 1042 12111 /200( 1095 12126/200( 1430 3/17/2001 1442 3/21/2001 1460 3/28/2001 1482 4/4/2001 1567 4/24/2001 1646 5/23/2001 1651 5/26/2001 1760 6/23/2001 1875 7/21/2001 1919 7/30/2001 1975 8/17/2001 1999 8/21/2001 2218 10/14/2001 2267 10/24/2001 2353 11/14/2001 2356 11 /15/2001 2449 1213/2001 2493 12114/2001 2548 12/30/2001 2646 1/28/2002 ~ Q Biking's It Industries 1356 2126/2000 1034 1219/2000 12/15/200( 12/27/200( 3/2212001 3/22/2001 3/30/2001 4/6/2001 4/27/2001 5/29/2001 5/27/2001 6/25/2001 7/29/2001 7/30/2001 8/17/2001 8/29/2001 10/15/2001 10/24/2001 11/18/2001 11/18/2001 1217/2001 12115/2001 1/1/2002 1/29/2002 10 8 6 • 2 - @For Loop Formula !!!!! !! !!!!!! !! !!! !!! !!!! !!!!!!! !! !!! !!!!!!!!! ! ! !!!!!!!!! !! ! !!!!! .!!!! !!!!! !! !!! 22 Days to Ship ~:: · B :~~e s io: T y ke~ • • .. • • • - • • Order ID @For Loop Formula 212812000 !:U 1219/2000 ! E:3trerne Mountain Bikes @Case Statement (Basic Syntax) Satisfactory Satisfactory Over 5 business days. Flag this . Satisfactory Satisfactory Satisfactory Satisfactory Over 5 business days . Flag this . Satisfactory Satisfactory Over 5 business days. Flag this. Satisfactory Satisfactory Over 5 business days . Flag this . Satisfactory Satisfactory Satisfactory Satisfactory Satisfactory Satisfactory Satisfactory Satisfactory • • • - • @Case Statement (Basic Syntax) Satisfactory Satisfactory > 5 days • > 10days 1 Add new formula features to your report Report on Shipping Status 1080 1150 1295 1475 1478 1591 1803 1812 1831 1940 1970 1980 2038 12/18/200( 1/8/2001 2/16/2001 4/2/2001 4/3/2001 5/1/2001 7/2/2001 7/3/2001 717/2001 8/6/2001 8/14/2001 8/17/2001 8/28/2001 12/19/200( 1/9/2001 2/25/2001 4/3/2001 417/2001 5/2/2001 7/4/2001 7/4/2001 7/10/2001 8/12/2001 8/19/2001 8/19/2001 8/30/2001 !! !! !!U2!!!!! !! !UU n !!! n !!!! !!!!!!! !!!!!! !U !U Satisfactory Satisfactory Over 5 business days. Flag this . Satisfactory Satisfactory Satisfactory Satisfactory Satisfactory Satisfactory Over 5 business days . Flag this . Over 5 business days . Flag this . Satisfactory Satisfactory > 5 days • > 10days ' ' ,. -' : ' ' • ' I ~.' .• .• ' 1 ' , ' • • ' ' • , & • ' • • t • • ,.. .. ~ . ' , : . -. ' -._ . . .. . ... 2283 2286 2329 10/28/2001 10/29/2001 11/6/2001 10/29/2001 11/1/2001 1117/2001 Satisfactory Satisfactory Satisfactory . . . I "' ,' • • ' f ,. ' < ' ' ! • : • ~ • ' -• ' • ' ~ 2415 2672 2695 2724 2742 11/23/2001 2/1/2002 2/6/2002 2/15/2002 2/20/2002 Crazy Wheels 1375 2/28/2000 1138 1/5/2001 1146 1/6/2001 1201 1/19/2001 1216 1/21/2001 1258 214/2001 . 1446 3/24/2001 1551 4/21/2001 11/24/2001 2/1/2002 2/6/2002 2/17/2002 2/28/2002 10 = • 2/28/2000 1/10/2001 1/11/2001 1/28/2001 1/22/2001 2/8/2001 3/31/2001 4/28/2001 • !! 2 ! !!! !!!!!!SU Days to Ship • • • Iii Order ID @For Loop Formula 2 22!22! 2222f 2 !!UU!EE! !! !!!!! 222222!2 "!!!!!!!! • Satisfactory Satisfactory Satisfactory Satisfactory Over 5 business days. Flag this . • • • • • • @Case Statement (Basic Syntax) Satisfactory Over 5 business days. Flag this. Over 5 business days. Flag this. Over 5 business days. Flag this . Satisfactory Satisfactory Over 5 business days. Flag this . Over 5 business days. Flag this. 2 How to summarize group data Argentina Bicicletas Buenos Aires Aruba Aruba Sport Australia Down Under Bikes Canberra Bikes Kangeroo Trikes Bruce's Bikes Peddles of Perth Koala Road Bikes Tasmanian Dev il Bikes Austria Picco lo Mendoza St. George New South Wales New South Wales Victoria Victoria Western Australia Queensland Tasmania Salzkammergut E:3treme Mountain Bikes Buenos Aires 6755 56 $55 ,664 .35 • :, I • j COUNT of Customers = . · . · , . · 1 SUM of Last Year's Sales = . , · · " $55,664.35. AVERAGE Last Year's Sales= $55,664.35 PERCENTAGE of Last Year's Sales· . 0.61% Oranjestad 655456 $3 ,2 39 .85 COUNT of cu~tomers = · . : · · · '· .· 1 SUM of Last Year's Sales= · · · $3,239.85. AVERAGE Last Year's Sales= · $3,239.85 PERCENTAGE of Last Year's Sales ·. . . 0.04%. Sydney 2061 $1 ,621 .20 Canberra 5353 $10 ,662 .75 Melbourne 2155 $9 ,594.70 Churchill 4774 $1 ,138 .09 Perth 3326 $8 ,945 .25 Brisbane 4600 $6 ,744.80 Hobart 0912 $1 ,739 .85 ¥ • ~ • COUNT of Customers = 7 SUM of Last Year's Sales = -$40,446.64 AVERAGE Last Year's Sales= · -· $5,778.09 PERCENTAGE of Last Year's Sales -, 0.45% Salzburg A-5020 COUNT of Customers = SUM of Last Year's Sales= AVERAGE Last Year's Sales= PERCENTAGE of Last Year's Sales $201 ,000 .00 1 $201,000.00 ,. $201,000.00 .· 2.21% 1 Argentina Bicicletas Buenos Aires Aruba Aruba Sport Australia Down Under Bikes Canberra Bikes Kangeroo Trikes Bruce's Bikes Peddles of Perth Koala Road Bikes Tasmanian Devil Bikes Austria Piccolo Bahamas Beach Cycle and Sport Bangladesh Dhaka Bike Store Barbados Barbados Sports, Ltd. Belgium Belgium Bike Co . Bermuda Royal Cycle Bolivia Bicicletas de Montana La Paz · Mendoza St. George New South Wales New South Wales Victoria Victoria Western Australia Queensland Tasmania Salzkammergut New Providence Dhaka Bridgetown Brussels Pembroke Parish La Paz Argentina Aruba Australia . Australia Australia Australia Australia Australia Australia Austria Bahamas Bangladesh Barbados Belgium Bennuda B·olivia 6755 56 $55,664 .35 Customers in this country = 1 655456 $3 ,239 .85 Customers in this country = 1 2061 $1 ,621 .20 5353 $10 ,662 .75 2155 $9,594 .70 4774 $1 ,138.09 3326 $8 ,945 .25 4600 $6,744 .80 0912 $1 ,739.85 Customers in this country = 7 A-5020 $201 ,000 .00 Customers in this country = 1 5466 $14 ,463 .35 Customers in this country = 1 346457 $4,683.50 Customers in this country = 1 . 4532 $4.443 .80 Customers in this country = 1 47899 $200 ,000.00 Customers in this country = 1 46557 $3.493.65 Customers in this country = 1 4542 $6,269.25 Customers in this country = 1 1 Xtreme Adult Helmet -- Xtreme Anatomic Ladies Saddle CA 18,216.89 2,070.39 Bike Shop Changing from Mars Gears 101.70 1,569.62 71.30 135.35 E!3treme Mountain Bikes Off the Rowdy Sporting Tyred Out Mountaing Rims Wheels Inc. Biking Company 169.50 903.44 10,512.06 4,960.57 151.50 1,712.24 ------------··-· --------------·· ----------·--· --------------------------------- Xtreme Anatomic Mens Saddle Xtreme Gellite Ladies Saddle 1,431.42 2,535.79 -·-----··--·-- Xtreme Gellite Mens Saddle XtremeMtn Lock Xtreme Rhino Lock Xtreme Titan Lock 1,997.67 1,874.87 23.80 1,097.87 1, 118.71 29.00 29.00 1,315.42 58.00 2,535.79 . . -· -. ----------.. --- ----- 122.80 23.80 1,024.01 73.86 1,084.60 34.11 E!3treme Add new charting features to your report Mountain Bikes DrillDown Pie Chart c: 0 Cl XY Scatter Chart-----~ Stock Chart--- Ra~~; g~:~ .-------30 Riser Chart ---~.: 3D Surface Chart Area Chart ar Chart 1 30 Riser Chart for Italy Sum of Last Year's Sales 7000K 6000K SOOOK 4000K 3000K 2000K 1000K OK Customer Name 30 Riser Chart Brescia Mountaine1 Magazzlnl Milano Bike Store Cycle City Rome Lazio Lombardia Lombard la Piedmonte Sum of Last Year's Sales 7000K 6000K SOOOK 4000K 3000K 2000K 1000K OK Region $181,444.66 $4,824,000.00 $6,755.80 $6,030,000.00 2 j ·PN -vrp;; ·11s?\4. s~·MG ~~so)Jd'd CTU Course Outline Page 1 of2 NC STATE UNIVERSITY Computer Training Unit The Right Training. Right Here. Crystal Reports Level II Select individual dates for course pricing or to register online. Click to register via Fax, Mail or Phone at 919-515-8163 Suggested Prerequisites: Crystal Reports 9 Level I Description: In this course you will bu il d reports with advanced fo rmula s, run ni ng totals , subreports , cross-tabs and charts . You will also crea te spe cifi ed gro up orders and bu ild report foundations using Crystal SQL queries , SQL co mm ands and dictionaries , exploring the strengths and weaknesses of those too ls . Lesson 1 : Lesson 2: Course Content CREATING RUNNING TOTALS Create a running total field without variables Modify existing running total fields Create manual running totals on detail data Create manual running totals on grouped summary data BUILDING CROSS-TABS IN YOUR REPORT Create a cross -tab Create a specified group order Filter cross-tabs by group Change cross -tab formatting options Work with conditional formatting in rows and columns Lesson 3: ADDING SUBREPORTS Lesson 4 : Understand the purpose and benefit of subreports Insert an unlinked subreport Link a subreport to data i n a primary report Edit subreports Modify subreport formatt i ng options Share formulas between main and subreports Create on -dema n d subreports IMPROVING REPORT PROCESSING SPEED Create a SQL Expression Enable Server Processing Create and edit SQL Queries Build a report on a SQL Query Create SQL Commands in the Crystal Report environment Lesson 5 : BUILDING DICTIONARIES & MORE ON SQL htto ://www.dcs.nc su .edu/ ctu/ courseoutline.cfm ?ID=944 8/16/2005 • CTU Course Outline Lesson 6 : COMMANDS Create a dictionary Organize the view of data in a dictionary Adding graphics to a dict i onary Build a report on a dictionary (Optional) Create SQL Commands as an alternati v e to di c tionaries CHARTING DATA & REPORTING ON EXCEL DATA Create and modify charts Create a chart that displays Top N data Format a chart Create a chart template Setup and modify a data so u rce in Excel through Crystal Reports View updated Excel data through Crystal Reports 553 htto://www.dcs.ncsu.edu/ctu/courseoutline.cfm?ID=944 Page L OI L 8/16 /2005 ~; I J VClose ====i .::.J.!!.l ; f1' Repor riiilSave I ><.;2 I in 0< I ~ ~ I A ~ :.\ ?"• I C'l ii~ ,-~TtfllcrystalS~ax iJ II I ;)~ ! ? !!J ·tJf Repo: ..!!I, 8 0 Fom-.u ..!I ' l--X1 Al . ; !..~; N1 ; D SOLE tE D Setecr i~J D Forma -------·-----------~-~---·---·----~--·-···· ---·----·~--·--¥ B ID) Customei ! . ma Customer ID i ··cm Customer Cred~ ID l · B!ll Customer Name i-am Contact First NlllOO ! ... am Contact Last Name i ,.... Contact T iHe I ,.... Contact Pos~ion , .. ,.... LastYe111's Sales i """ Addfess1 i .am Ad0ess2 l ! ·"""C~y .am Region I """Country I ....... Postal Code ! .. am E·mai amWebS~e .mn Phone ""'Fax ~ ~ : · Recorcf'.lumber ~ '71 ..!I ! ·· Gr~urrbef ..!I ! · RecordSelection II :,. G roupS election II I' i lrAepeatedGroupHeader i.! i. ·· OnFistRecord i OrlastRecord ii '·· DrilOownGrouplevel i! iii lj} Document Properties I' t!l [,,Alerts B I.I) Add~ional F 1.11etions ! Cfl ~ 2000 (u212000 dnJ j f:f 1 ~! cb (u21dts .d!] ,_: ,· !fl I exch (u21exch dill ff l C. linra (u21finta dn] i j {~ (l,}iamp1 (u21samp1 .dll) II ~i @2000(u252000dft) ) ti lfl '.Qj cb ( u25dts . dft) I 1i1 ({~tamp1 (u25samp1 di) • ... ii ,.. JJ It IeNull (eum ({8Absolute Credit),(Cuetomer .Cuetorner ID))) then "no credit" ~lee ToText ({8Absolute Creditl,{Cuetorner.Custorner IDJ) " ,S\lrV\ _!.1_1 __ ~1.J-~..__ _ _, i1'1St11rt llJ rg{J ~ \i, ~ I JIG!l Crystal Reports -[My... mi] Microsoft PowerPolnt ·[CR] I •• ¥• -· ---·-· ----•• ·- Divide ( K I y I ~ · I nleger divide ( M \ y I Per ce nt I K ~ Y I Modi.1~1s ( K mod y l Negate (·MI E Kponentiate ( M ~ y l tt fi Conversion :+: (I Compaiisons :+: ( j Slring s '.~ fj.Ranges '.-\':(*Boolean lfi (*Arrays [f (*Pattern [f ) (*Control Stru ctures :~; LjOlher Pa1enthe ses ( (H) l ; Assignment ( K := y l · Commer~ I I /) Date ·time ~te r al (11...11) :•·1j.scope _, ,,, I a.I \/ •• !.L I."· .1 •.• ,;.... ~ ..:J ~ i ~i:il ~\i ·-3;53 ·p;.;- .,...., Im Formula Workshop -Formula Editor -Order Day I '' • ~ • :' I i r '~ :f,) :t..''tlK,: . f ,: J ~Close I D • ~~ X. UJ ~ ~ l; !J ~I ti ti -= _ __,.:.Jl!J r:,..=-sa-ve~I ><~}:-:-,, _I(}_(')_"-:-, ~rr?=--:i -14i::'""".,:--,,.-:-5i-,~=--> -r.-:--=--, C::l-:-;:I G:::~::;-1;:::-~==-:-:::r.;-::i::::-;. ,:;:, c=r.v=sta=I S=ynt=a=x ===o:J= ... ;-11-:-1 -:/f~l.:---?-------:· ·ttJf Report Custom FlllCtions I I T::: i!J -(JJ Repos~ory Custom Functions 2:1 B D Founula Fields ..!J : L.~.1DIDll : ··D SQL EKpre ssion Fields r~ ·D Sele ction FOlmulas ~1 D Fcxmatting Founulas . ~I ! ... mo Country i -am Postal Code ! . ...., E-mail I· El!D Web Site i · BEi Phone : · Bl!I Fax El (gj) Orders i .. m1 Order ID I· .mu Order Amount i · mo C1ntome r ID I... mD Employee ID 1--IJ!ll Order Date ! · .1m Required Date !--mi Ship Date !--am CouierWebs ~e · . mD Ship Via .a!!J Shipped am POtt L .mn Payment Received ... l!J B · ~Functions ..!J $ r«fMath cfJ fjtS ummar.v ffi ·(@F ina nc ia l riJ .f ~ String s r:fi {@ Date and Time iii (jD Date Range s r~ flt Ar rays $~Range s rf!-rjlj Type Conver sion ~!··~Programming Shortcuts 1$ ~Evaluation Time r1:1!1)Print State lfl-·~ Do cument Propertie s $· (i JAlerts !±!·~Add it i onal Function s If DayO!TJeek ((Orders.Order Date}) • 1 then "Sunday" else if DayOflJeek ((Orders.Order Da t e}) • 2 then "M o nday" else if DayOflJeek ((Orders.Order Date}) .. 3 then "Tuesday" else if DayOfTJeek ((Orders.Order Date)) • 'I then "W e dnesday" else if DayOfTJeek ((Orders.Order Date }) • 5 then "Thursday" else if DayOfWeek ((Orders.Order Date}) • 6 then "Friday" else it DayOfWeek ({Orders .Order Date}) .. 7 then "Saturday" !~Start 11 j utJ ~ @! '° I JIG:? Crystal Reports -[My ... @}Microsoft Po werPo lnt -[CR] I ; · · M u~iply ( x • .Y ) Di vi de ( x / y) Integer divide ( x \ .Y I Percent ( x % y I Mod ulus ( x mo d y ) •. Neg at e(·>! I . · Expo nenti ate ( x ~ y ) ~l fit Conve rsion ltl f *Compari sons ltl ffStr ings 1{1 (*Rang es [f l f *Boolean [fl !*Arrays [f l !'*Pattern lt l I* Contro l Str uc tur es ~JI * Other · Pa renthe se s ( (x) I Ass ignment ( x := .Y ) Com me nt( I I I Date-time literal (IL.It) ,.~, 1 "~ r-A .. _~ £ l<ii ~~~·--11:43Af~ -· Im formula Workshop -formula Editor -Emergency Phone · '{~i:J. :11! ~!Jet'~ .': · J ~e1ose I D • ~~ >< IJ ri ~ ~ J Gi"I d .r -r--0-Re_por_t -Cu-sto-m-Funct-iom----=:::..:Jl-;;~.-r-:=fiil:-sa-v-e-:-l-><.,"".:"'·2 '"'."'l""",.,-01-:-l-:~:-~-=-lrti:-:-__ -:l_=_,,.-_~:-,._.--::-~----3¥.:-:-l-:-:C~-:-;:lr:::~~,;::::~=-::=ji=:--:'.".'1:::1c=IY*=1 al=Synt==~=.= ... _= __ ,_=iJ::-__ -!'_'l-_:-l_)-:-·~=-I~?-_-__ ~---~--= $-tj' Repod01y Custom Fll'lCtions ~ ! .... -Hire Date ~ ~ i,. 1·· CStr (x , y, z) ~ ~. I ··Subscript ( x (y]) ~ $·0 FOl~aFields ..!J i-imHomePhone ..!J i CStr(x ,y,z.w) .!I i·lnanay(xny) i l·-~.j. CityStateZip i..mmEl<tension ! : ·CStr(x.y.z,w.q) ! redimx(n) i l .. ·~j. fini\HIHIHI j ... mm Notes Ill CDate ' i .. ,e<Mn pieserve x (n] L ~~ Name j. -Reports To tfl Clime ~l ftl.Pattern l .. .(lJ SQLEi<pressionFields i ··trmSalary ! £~ CDateTime i i· Startswith(x~ar tsW~hy) r!J CJ SeleclionFor~s j .... 1DJSSN $($Programmino5hortcuts i ' Likepanern(x lik ey) r!J .CJ F01matting FormWi ! .. -IDI Emergency Contact Frst Name cf.l ~Evaluation Time $ ftl. Control Structures !..--Emergency Contact Last Name e .~ Print State ! : .. if K then y el se z • I \.\l'1 . I i .. -mm Emergency Contact Retatiomhip • i· -Previous (fld) i .. select x case a : y delau~: z I L rm Emergency Contact Phone . I 1 1.... Ne>d (fld) ! ·· fOI i :•a to b s.tep c doz El lDI Employee_Addresses i , ·· lsNu~ (fld) i ··exit for I .. rm Employee ID I j" P1ev10Us ls Null (fld) I· ·while x do y ! 1 : .... tn1 Address1 I ! .. Nextl sNiJI (fld) i do x while y I I I i·-·IDI Address2 1 I-· PageNumber ; .... ex it whie i--·-Ci(y j i · T otaPageCO\rl ! ! · option loop rM xlte rations !--Region ! l··PageNofM E;:1 ltf.Othe1 1--·111!1 Country i f' · Recodlumber · i .. Parenthe ses ( (x)) i.: .. 1m Postal Code i ! · GroupNi.mber !· Ass ig nment ( x :• y) ! I I i ... im Emergency ContactAddreu1 I ·· Rec01CISelect ion i .. ·Comment ( //) ! ... -Emergency ContactAddress2 , ... GroupSelection !... Date ·time l~eral 111 ... 11) I---Emergency Contact C~y 1· lnRepeatedGroupHeade! $· (i Scope l··-Emergency Contact Region 1 1 . · OnF ir stRecord i i · Local !---Emergency Contact Country i 1 .... OnLastRec01d i ··Global L.111!1 Emergency Contact Postal Code , .... DriRDownGrouplevel , L .. Shared • I ~1 .f.fi Doci.n]"' Prooerties £ iii ·ltf. V111iable Declarations ,, (" ' Lett (<Employee. Emergency Contact Phone), 3) ' ")" ' " " ' Hid ((Employee.Emeri;yency Contact Phone),4,3)& "-" & Right ((Employee.Emergency Contact Phone),4) ·----·--·-----------·----------·---_,__._ _ _. £ I @)Internet MaM Server Web ... 11[;!1 Crystal Reports -(Rep... Wl)Mlcrosoft Powe rPoint -[P ... j [(Ji~Ci\i·~·16a··-;(i;16-AM __ _ ~~------------------------ r .. ·~! Rep01t Custom Functions ~ Repodory Custom Fll1Ctions B · F 01J!Ua Fields i j .. x•1 Backtvound I -,,. !···~~ New Phone . t.~j. R!lf'l!lme l··-0 SQL E>tpression Fields til CJ Selection Formulas ~ 0 F01m!!lting FornU!ls l·· · ~ Report He!!del rfl ·· ~ Page He!!der S· ~ Deteh I 1· ··~ lllllMl!Nil!!I !······ma Aenemel :·@Aeneme i ···· 11111 Phone1 : Customei .Phone ! .... ma NewPhonel@NewPhone ' i r · · 6fil Aepoit F ooler L. ~ Page Foot"' ~I ~HD! Customei I·· .mJ Custome1 ID i .. mi Customer Credit ID I .. mD Custome1 Name I i· .. am Contl!Ct Fi!st Neme I ···mo Contecl Last Name i .. El!!I Contact Title I· EtE Contact Position I .. m11 Last Yeai 's Salet · mi Add!e$$1 j .. am Address2 1···mD City ' A . i .. l'J!l!I egion I ... ma Couilry i .. liD!I Postal Code ! ·ma E ·mail I·· am \l/eb Site i· ma Phone L mo Fex $ f«t Type Conversion f~ ·flJ Piogremming Srotcuts $ ~ E vekJation Time f$l [$ Print Slate ! i · PreviOus (fld) i i · Next(ffd) ! · lsNul (fld) ! 1 .. Previous! sNull (fld) ! ... NextlsNul (ffd) j ·· PegeNumber !· TotalPageCount i· PageNofM i · · A ec01dN l.IT1bef i .. G roupN l.Mnbel I· .. RecOldSelection ! ··· GroupSelection i i · lnAepeatedGroupHeader I ! OnFirstAecord i 1 • 0 nLestA ecord j 1 ... DrinDownGroupLevel ri ·• (~l '"'--·-·-··' n .--•. ,; .. It remainder (recordnwri:>er ,2) sQ then crsilver else nocolor !'1startl l J rf4:} ·~ @; d} I l IG;crystal Reports-[Rep"' @]Microsoft PowerPolnt -[CR] I @Crystal Reports Online Help I J 3 II I ? Divide ( x I y ) ~ ! · Integer divide ( x \ y) ' · Percent ( x ~ y ) Modulus ( x mod y J Negate ( ·x) Expone ntiat e ( x ~ ~· l ffi fj ConYersion ~1 fl Comparisons ~I (*Strings i i f*R~nge s ftl (*Boolean ~l l .jArrays $if Pattern (fl tf Control Structures ¢!(*Other ! · Parentheses [ (Y.J) As signment ( x := y I Comment ( I/) ! ~. Date ·time literal [ILll)~ •I ~" r .. ,..____ L~ . .r-- ~ rct1 d~i QP ~ -·-z ~Zi .. ;,t.,--. (Cu~tomer.Contact fir~t Name)' w " C(Cu~tomer.Contact La~t Name) ·----·--·--·--·----·····-··-..... ---·-·------·-·--~- Contact First Name Chris Christine Gary Alexander Patrick Heather Alex Kristina Alexandra Anthony Bill Gerry Matthew Rick Christopher James Will Ian David Dan Daryl Lori Annie Tony Zach Trisha Jon Patricia Gary Brent Richard Sheryl Markus Laurie Tracy Julia Jane Donald Stacy Alexa Ron Jerry Nadine Robert Zachary Joe Kim Andrew Cindy Davy Katie Janice Matt Scott Gordon Douglas Elizabeth . Rosie Donna Dave Contact Last Name Christianson Manley Jann is Mast Reyess Davis Smith Chester Burris Shoemaker Carter Wade Banks Pratt Carmine Sergent Castillo Kelly Baker Simpson Davidson Mcisaac Harper Garneau Fabro Sanders Witt Manon Campbell Lansing Grewal Rodgers Holt Lee-Johnson Hanna Jackson Fasan Edwards Bodnar Quartermaine Cunningham Esler Scott Walter Barbera Lee Karl Dong Forester Douglas Cronin Pierson Trent Mueller Bell McDougall Kawa Forbes Marshall Elkins Contact Name Chris Christianson Christine Manley Gary Jannis Alexander Mast Patrick Reyess Heather Davis Alex Smith Kristina Chester Alexandra Burris Anthony Shoemaker Bill Carter Gerry Wade Matthew Banks Rick Pratt Christopher Carmine James Sergent Will Castillo Ian Kelly David Baker Dan Simpson Daryl Davidson Lori Mcisaac Annie Harper Tony Garneau Zach Fabro Trisha Sanders Jon Witt Patricia Manon Gary Campbell Brent Lansing Richard Grewal Sheryl Rodgers Markus Holt Laurie Lee-Johnson Tracy Hanna Julia Jackson Jane Fasan Donald Edwards Stacy Bodnar Alexa Quartermaine Ron Cunningham Jerry Esler Nadine Scott Robert Walter Zachary Barbera Joe Lee Kim Karl Andrew Dong Cindy Forester Davy Douglas Katie Cronin Janice Pierson Matt Trent Scott Mueller Gordon Bell Douglas McDougall Elizabeth Kawa Rosie Forbes Donna Marshall Dave Elkins Append a li st of string values into one line rage 1 01 L. Back to Formula List Appending a List of Details into One Line: How do you get a list of detail strings to print on one line? So that a list of detail items like : Item A ltemB ltemC prints out as one line like : ltemA, ltemB, ltemC The solutions requires three formulas , and assumes that the items are within an existing group on the report : 1) In the Group Header place the @reset formula : WhilePrintingRecords; StringVar chain := "; NumberVar ChCnt := 1 2) On the Details place the @Accum formula , putting your field into the second line : WhilePrintingRecords; StringVar Item := {Your.Field};// place your field in place of {Your.Field} StringVar Chain ; NumberVar ChCnt ; if ChCnt = 1 then (ChCnt:= 2 ; chain := Item) else if Length(Chain) + Length(ltem) > 254 //see note about this number below then Chain := Chain else chain := chain + ', '+ Item 3) On the Group Footer place the @Display formula : WhilePrintingRecords ; StringVar Chain **Important Note . A string formula cannot output more than 254 characters in versions before v9 . So if your items add up to over 254 characters this formula will not print them all. It will only print the items that fit within the first 254 characters . You can select any number lower than 254 to stop the accumulation earlier . To get a longer string in versions before v9 you could create a second parallel! set of formulas that picks up where this one leaves off. If you http://www .kenhamady.com/fo rm16.html 6129 12006 Append a li st of string values into one lin e .a '-40--_._ - are using v9 or later you can replace the 254 limit with any number up to and including 64 ,000 which is the new limit on the output of a string formula. httn://www.kenhamady.com/form16.html 6/29 /2006