Crystal Reports 2008 Designer 1 Workshop by Mark Myers

Crystal Reports 2008 Designer 1 Workshop

ÂŠ2008 Vision Harvest, Inc.

Crystal Reports 2008 Designer 1 Workshop

Vision Harvest, Inc. 888-236-8087 sales@visionharvest.com Revised: July 15, 2008 Part: VISI100070S

Crystal Reports 2008 Designer 1 Workshop

This course covers Crystal Reports® 2008 No part of this manual may be copied, photocopied, or reproduced in any form or by any means without permission in writing from the Author - Vision Harvest, Inc. All other trademarks, service marks, products or services are trademarks or registered trademarks of their respective holders. This course and all materials supplied to the student are designed to familiarize the student with the operation of the software programs. We urge each student to review the manuals provided by the software publisher regarding specific questions as to the operation of the programs. THERE ARE NO WARRANTIES, EXPRESSED OR IMPLIED, INCLUDING WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE, MADE WITH RESPECT TO THESE MATERIALS OR ANY OTHER INFORMATION PROVIDED TO THE STUDENT. ANY SIMILARITIES BETWEEN FICTITIOUS COMPANIES, THEIR DOMAIN NAMES, OR PERSONS WITH REAL COMPANIES OR PERSONS IS PURELY COINCIDENTAL AND IS NOT INTENDED TO PROMOTE, ENDORSE OR REFER TO SUCH EXISTING COMPANIES OR PERSONS. VISION HARVEST, INC. NOR ITS RESPECTIVE PRODUCTS OR SERVICES ARE AFFILIATED WITH, OR ENDORSED, LICENSED, OR SPONSORED BY BUSINESS OBJECTS, THE OWNER OF CRYSTAL REPORTS.

This courseware has been developed by the professional team at Vision Harvest, Inc., www.visionharvest.com. Questions, comments or concerns about courseware content should be sent to sales@visionharvest.com or 888.236.8087.

Crystal Reports 2008 Designer 1 Workshop

ÂŠ2008 Vision Harvest, Inc.

Table of Contents Introduction ..................................................................................................................................................... 1 Introduction Objectives ..................................................................................................................... 2 About Crystal Reports ........................................................................................................................ 2 Training Philosophy ............................................................................................................................ 2 Class Objectives .................................................................................................................................. 3 About This Manual .............................................................................................................................. 4 Tips, Notes, and Warnings .................................................................................................................. 5 Lesson 1: Creating a Simple Report ............................................................................................................... 7 Lesson Objectives ............................................................................................................................... 8 Starting the Crystal Reports Program ............................................................................................... 9 Starting a New Report ....................................................................................................................... 9 Choosing a Data Source.................................................................................................................... 13 The Main Components of the Design Window ............................................................................... 17 Exploring the Toolbars ..................................................................................................................... 19 Managing Resources with Explorers .............................................................................................. 20 Placing Fields on the Report ............................................................................................................ 22 Selecting and Sizing Objects ........................................................................................................... 24 Browsing Field Data ......................................................................................................................... 25 Moving and Aligning Objects .......................................................................................................... 27 Using Guides and Guidelines to Move and Align Objects ............................................................ 28 Creating Text Objects........................................................................................................................ 30 Saving the Report ............................................................................................................................ 31 Autosaving the Report ........................................................................................................ 32 Previewing the Report ..................................................................................................................... 34 Refreshing the Data ............................................................................................................. 35 Using the Status Bar.......................................................................................................................... 36 Getting Help ..................................................................................................................................... 37 Challenge Exercise â&#x20AC;&#x201C; Creating a Basic Report ................................................................................. 38 Lesson 2: Formatting Features ..................................................................................................................... 41

iii

Crystal Reports 2008 Designer 1 Workshop

Lesson Objectives ............................................................................................................................. 42 Quick Formatting with the Template Expert .................................................................................. 43 Formatting Objects ........................................................................................................................... 45 Format Painter .................................................................................................................................. 51 Inserting Lines and Boxes ................................................................................................................ 53 Drawing a Line ...................................................................................................................... 53 Drawing a Box ....................................................................................................................... 54 Inserting Graphics ............................................................................................................................ 57 Working with the Page Commands ................................................................................................. 58 Working with Text Objects .............................................................................................................. 60 Adding Fields into a Text Object ......................................................................................... 60 Formatting Part or All of an Object ................................................................................................. 63 Inserting Special Fields ................................................................................................................... 64 Challenge Exercise – Formatting Features ...................................................................................... 66 Lesson 3: Selecting Specific Records from the Database ........................................................................... 69 Lesson Objectives ............................................................................................................................. 70 Filter Types of the Select Expert ...................................................................................................... 71 Database Filtering with the Select Expert ...................................................................................... 71 Selecting Records with Multiple Criteria ........................................................................................ 75 Viewing and Editing the Select Formula ........................................................................................ 76 Case Sensitive vs. Case Insensitive .................................................................................................. 79 Record Selection Formula Templates .............................................................................................. 80 Challenge Exercise – Selecting Specific Records ............................................................................ 81 Challenge Exercise – Selecting a Range of Values .......................................................................... 83 Lesson 4: Grouping and Sorting Data .......................................................................................................... 85 Lesson Objectives ............................................................................................................................. 86 When and Why to Group Records ................................................................................................... 87 Creating a Group ............................................................................................................................... 87 Group and Sort Direction ................................................................................................................. 88 Customize Group Name Field .......................................................................................................... 88 Modifying Groups ............................................................................................................................. 92 Creating Multiple Groups in a Report ............................................................................................. 92 Using the Preview Panel ................................................................................................................... 94 iv

Crystal Reports 2008 Designer 1 Workshop

Using the Group Tree to Navigate the Report ................................................................................ 94 Reordering Groups ........................................................................................................................... 94 Sorting Records within a Group....................................................................................................... 95 Using the Sort Control ...................................................................................................................... 97 Summarizing Groups ........................................................................................................................ 98 Additional Summary Options ...........................................................................................100 Grouping Data in Date/Time Intervals .........................................................................................102 Calculating Percentages ................................................................................................................104 Ordering Groups Based on Their Subtotals Using the Group Sort Expert ................................106 Challenge Exercise – Grouping, Sorting, Summarizing ..............................................................108 Challenge Exercise – Calculating Percentages .............................................................................110 Lesson 5: Combining Multiple Tables ........................................................................................................113 Lesson Objectives ..........................................................................................................................114 Understanding Tables, Records, and Fields .................................................................................115 Learning about Linking .................................................................................................................115 Adding Multiple Tables to a Report ...............................................................................................119 Challenge Exercise – Using the Database Expert to Link Tables .................................................124 Lesson 6: Creating and Using Formulas .....................................................................................................127 Lesson Objectives ...........................................................................................................................128 Understanding Crystal Formula Syntax without Being a Programmer......................................129 About the Formula Workshop ........................................................................................................131 Using the Formula Workshop ........................................................................................................131 Using the Formula Editor ...............................................................................................................133 The Formula Editor Toolbar ...............................................................................................134 Performing Simple Number Calculations .....................................................................................135 Manipulating Dates with Formulas ..............................................................................................140 Creating Boolean (True/False) Formulas .......................................................................................143 Creating String Formulas ...............................................................................................................144 Using Bookmarks to Navigate Through Formulas .......................................................................154 Challenge Exercise – Creating String Formulas ............................................................................155 Challenge Exercise - Doing Calculations .......................................................................................156 Challenge Exercise - Subtracting Dates ........................................................................................ 156 Challenge Exercise – Using Nested If Statements ........................................................................157 v

Crystal Reports 2008 Designer 1 Workshop

Lesson 7: Conditional Formatting ..............................................................................................................161 Lesson Objectives ...........................................................................................................................162 Formatting Sections .......................................................................................................................163 Formatting Sections Conditionally ...............................................................................................165 Conditionally Formatting Fields ....................................................................................................168 Challenge Exercise – Section Formatting and Conditional Formatting .....................................171 Lesson 8: Creating Summary Reports and Charts.....................................................................................175 Lesson Objectives ...........................................................................................................................176 Creating a Summary Report ..........................................................................................................177 Applying the Drill-Down Feature ..................................................................................................179 Applying the DrillDownGroupLevel ..............................................................................................180 Producing Charts ............................................................................................................................181 Editing Charts .................................................................................................................................187 Formatting Charts ...........................................................................................................................188 Using the Chart Options ....................................................................................................189 Modifying Individual Objects in the Chart .......................................................................190 Applying Chart Templates .................................................................................................192 Challenge Exercise – Creating a Summary Report ......................................................................194 Challenge Exercise - Charting ........................................................................................................194 Lesson 9: Exporting Your Reports within Your Organization ...................................................................197 Lesson Objectives ...........................................................................................................................198 Understanding Export Formats and Destinations .......................................................................199 Adobe Acrobat (PDF)..........................................................................................................202 Microsoft Excel 97-2003 (XLS) ...........................................................................................202 Microsoft Excel 97-2003 Data only (XLS) ..........................................................................202 Microsoft Word (97-2003) ..................................................................................................203 Text (TXT) ............................................................................................................................203 Using the PDF Format for Crystal Reports ....................................................................................204 HTML Preview .................................................................................................................................205 Exporting to Windows Applications ..............................................................................................205 Exporting to a Report Definition Format .....................................................................................206 Lesson 10: Using the Report Wizards .........................................................................................................209 Lesson Objectives ...........................................................................................................................210 vi

Crystal Reports 2008 Designer 1 Workshop

ÂŠ2008 Vision Harvest, Inc.

What are the Report Wizards? .......................................................................................................211 Create a Report Using the Standard Report Creation Wizard .....................................................212 The Data Dialog Box ...........................................................................................................212 The Fields Dialog Box .........................................................................................................213 The Grouping Dialog Box...................................................................................................213 The Summaries Dialog Box ................................................................................................214 The Group Sorting Dialog Box ..........................................................................................215 The Chart Dialog Box .........................................................................................................215 The Record Selection Dialog Box ......................................................................................216 The Template Dialog Box ...................................................................................................216 Challenge Exercise â&#x20AC;&#x201C; Create a Report Using a Report Wizard .....................................................219 Appendix A: Mapping .................................................................................................................................223 Lesson Objectives ...........................................................................................................................224 Understanding Maps ......................................................................................................................225 Map Layouts........................................................................................................................225 Map Types ...........................................................................................................................225 Formatting the Map ........................................................................................................................230 Drilling Down on Maps ...................................................................................................................230 Advanced Formatting of Maps ......................................................................................................231 Changing the Geographic Map .........................................................................................231 Using the Map Navigator ...................................................................................................232 Changing Map Layers.........................................................................................................233 Resolving Data Mismatches ..............................................................................................233 Appendix B: Before You Begin ....................................................................................................................237 Lesson Objectives ...........................................................................................................................238 Defining the Purpose of the Report ..............................................................................................239 Determining the Layout of the Report ..........................................................................................240 Finding the Data .............................................................................................................................240 Organizing the Data for the Report...............................................................................................241 The Workbench ...............................................................................................................................242 Appendix C: Setting Default and Report Options.....................................................................................245 Lesson Objectives ...........................................................................................................................246 Setting the Default Layout for Design and Preview Views .........................................................247 vii

Crystal Reports 2008 Designer 1 Workshop

ÂŠ2008 Vision Harvest, Inc.

Appendix D: Pre-Qualification Test for Designer 2 Workshop .................................................................257 Why Should You Pre-qualify yourself for the Designer 2 Workshop? .........................................258 Pre-qualification Exercise...............................................................................................................258 Appendix E: The Northwind 2008 Database .............................................................................................263 Appendix F: Installation and System Requirements ................................................................................267 Installing the Application ...............................................................................................................268 Minimum Installation Requirements ............................................................................................268 Appendix G: Function and Operator Locations ........................................................................................271 Appendix H: Setup Instructions for Crystal Reports 2008 .......................................................................285 Crystal Reports 2008 Application Installation Instructions ........................................................286 Installing Required Class Files .......................................................................................................289 Appendix I: Glossary ...................................................................................................................................291 Index .............................................................................................................................................................301

viii

Crystal Reports 2008 Designer 1 Workshop Introduction

ÂŠ2008 Vision Harvest, Inc.

Introduction

Crystal Reports 2008 Designer 1 Workshop Introduction

ÂŠ2008 Vision Harvest, Inc.

Introduction Objectives This manual is written to give you a step-by-step guide for your classroom training and a handy reference for your daily work. In this Introduction, you will learn how to use this training guide effectively. This section covers the following topics: An introduction to the Crystal Reports application Class objectives Help with using this training guide Information on how to start the program

About Crystal Reports In todayâ&#x20AC;&#x2122;s information intensive environment, every business has a database of some sort. After all, business today is all about information and databases give you a handle on the massive amounts of information you must deal with. Therefore, your business has a database and from that database, you need reports. The problem is, most reporting capabilities that come with database programs are limited. They only report on data from that program. Many users need to report on data from multiple sources, even SQL (Structured Query Language) databases such as Oracle, Microsoft SQL Server, Informix or Sybase. Crystal Reports is one of the most powerful reporting programs available with the ability to pull data from all types of data sources. You can use Crystal Reports to generate reports from any of the standard PC database programs, Access, Paradox, or FoxPro, as well as from a mainframe or server database. Crystal also has a powerful web-reporting server that allows you to distribute your reports over the web. Crystal Reports is bundled with more than 160 other programs including Visual Basic, some medical applications, many accounting packages and several ERP solutions. It makes report generation easy without requiring you to be a programmer or a database expert. If you know how to work in a Windows environment and are familiar with the data you want to use, you can create a Crystal Report that looks professional and makes sense.

Training Philosophy Studies show that people retain 10% of information they see, 20% of information they hear, 50% of what they see and hear, and 80% of what they see, hear and do. In line with this concept, the class utilizes a hands-on method of training. You will see the effects of new procedures on the screen, hear the instructor explain how and why to use features, and perform the actions yourself as you learn. In addition, this class focuses on your ability to perform tasks using the most productive techniques. The manual may contain several methods of accomplishing a certain task. However, class time does not allow for practice of all methods for each task. Your instructor will guide you in the most effective method of performing a task, but inform you of other methods that are available. Questions are encouraged. While we give our best effort to explain new concepts in understandable terms, you may need to hear the concept again or have it explained more thoroughly. Please let the instructor know when you need more information! 2

Crystal Reports 2008 Designer 1 Workshop Introduction

ÂŠ2008 Vision Harvest, Inc.

Class Objectives This class is a performance based instructional system. It is geared to provide you with the tools you need to build and distribute reports the quickest, most efficient way. After completing this course, you will be able to perform the following tasks: Plan and organize a report Create a new report Save and preview a report Navigate through the report Format and edit report objects Use a report style for formatting Add text objects to reports Add lines, boxes, and graphics images to reports Select specific data from a database Group, sort and summarize reports Create reports that pull data from multiple tables Use the Formula Editor to perform number calculations Create formulas that manipulate dates Modify string fields using powerful text functions. Conditionally format numbers with the Highlighting Expert Format sections on the report Use formulas to conditionally format any type of field Create summary reports for efficient analysis of data Add charts and customize how they look Distribute reports throughout your organization with Crystalâ&#x20AC;&#x2122;s export feature Create geographical maps for charting Set up an ODBC connection to your database

Crystal Reports 2008 Designer 1 Workshop Introduction

ÂŠ2008 Vision Harvest, Inc.

About This Manual Each section of this manual contains objectives to provide you with the overall goals for the lesson. Lessons have descriptions of features and concepts followed by systematic directions for completing a specific task. Each section ends with a challenge exercise to help you practice the skills you learned in the lesson. Challenge exercises provide you with tasks to accomplish. Try to complete these exercises on your own. As you work in this Training Guide, certain conventions are used to identify specific procedures. Use the following table as a guide:

Training Guide Conventions Item Menu Commands

Illustrated As Underlined letters for accessing menu commands are shown:

Command Buttons

Example: File/Open Command Buttons in dialog boxes are shown as buttons:

Example: Categories, Radio Buttons, All options within dialog boxes are listed in italicized text: Text Boxes, Check Boxes Example: the Keep Group Together check box the Other radio button Keystrokes Keyboard keys are indicated by uppercase text: Example: press ENTER Keyboard combinations are shown in uppercase text with a plus sign (+) between the keys that need to be pressed simultaneously.

Toolbar Buttons

Example: press CTRL + S to save Toolbar buttons are indicated by the button name and a graphic image of the button:

Typing or File Selections

button Example: click the Print Preview Text to be typed or file names to be selected are printed in bold letters:

Exercises

Example: type Henry select grouping.rpt Step-by-Step exercises in the text are indicated by bold text and the For example:

Exercise - Format Objects

symbol.

Crystal Reports 2008 Designer 1 Workshop Introduction

ÂŠ2008 Vision Harvest, Inc.

Tips, Notes, and Warnings Tips, notes and warnings display with the following icons. Text for these additional comments display in bold and italics as shown below:

This icon indicates a tip or shortcut.

This icon points out a note of additional information.

This icon calls attention to a warning or very important note

Crystal Reports 2008 Designer 1 Workshop Introduction

ÂŠ2008 Vision Harvest, Inc.

NOTES

Crystal Reports 2008 Designer 1 Workshop Lesson 1: Creating a Simple Report

ÂŠ2008 Vision Harvest, Inc.

Lesson 1 Creating a Simple Report

Crystal Reports 2008 Designer 1 Workshop Lesson 1: Creating a Simple Report

ÂŠ2008 Vision Harvest, Inc.

Lesson Objectives After completing this lesson, you will be able to: Use the Design and Preview windows to build reports The Design window is the main window for building a report. Become productive in putting together the basic items on the report, and then use the Preview window to see the results of your work Place data fields on your report Learn how to insert fields on the report Size and move objects Learning how to manipulate the objects on the report gives you control over the look and feel of the report Use guidelines to align objects Guidelines provide an easy method for moving and aligning objects. Become proficient at manipulating the guides and snapping objects to them Create text objects Add titles and other text to reports by creating a text object Save and Preview the report Learn about the Preview view and how to navigate through the report. Set up the report to save automatically. Understand what happens when you refresh the data in the report

Crystal Reports 2008 Designer 1 Workshop Lesson 1: Creating a Simple Report

ÂŠ2008 Vision Harvest, Inc.

Starting the Crystal Reports Program You can start Crystal Reports in one of three ways: Click the Start button | All Programs | Crystal Reports 2008 menu. Click on Crystal Reports 2008 If you prefer to use the My Computer icon or Windows Explorer, open either one of these windows. Expand or open the C:\Program Files\BusinessObjects\ BusinessObjects Enterprise 12.0\win32_x86 folder, then double click crw32.exe If you have a desktop icon for Crystal Reports 2008, you can double click the desktop icon to open the Crystal Reports program

Starting a New Report When you first open Crystal Reports, the program helps you to begin creating a report by presenting the Start Page. On this page you have two groups of options â&#x20AC;&#x201C; START A NEW REPORT and MY RECENT REPORTS.

Crystal Reports 2008 Designer 1 Workshop Lesson 1: Creating a Simple Report

ÂŠ2008 Vision Harvest, Inc.

START A NEW REPORT Under START A NEW REPORT you can use the Wizards to create a standard report; cross tab report; mailing label report or a report against an OLAP cube. The Report Wizards are a series of dialog boxes that assist you in creating a specific type of report by doing some of the work for you. You will learn how to use a Report Expert later in this class. You can also choose a Blank Report which is what you will do in this lesson. My Recent Reports If you have been working with reports you will see them listed here or you can choose to open a report that you have stored on your system. Choosing open report will take you to windows explorer where you can navigate through your folders to find the report. If you click the option, Crystal takes you to the Database Expert screen in order to select the reportâ&#x20AC;&#x2122;s data source. Choosing a Data Source Whenever you create a report, the first thing you have to do is select a data source. Crystal uses a dialog box called the Database Expert to present all possible data sources and allow you to choose one or more for a report.

Crystal Reports 2008 Designer 1 Workshop Lesson 1: Creating a Simple Report

ÂŠ2008 Vision Harvest, Inc.

The My Connections option displays the server or data file to which you are currently connected or have connected to previously. If you just opened Crystal Reports for the first time, this option indicates no items found Create New Connection is the folder that contains all possible data connections available for nearly any kind of database including Oracle, SQL Server and Access

Crystal Reports 2008 Designer 1 Workshop Lesson 1: Creating a Simple Report

Access/Excel (DAO) - Allows a direct connection to MS Access and other files using Microsoft’s Data Access Objects and not ODBC ADO.NET (XML) - Connects to ADO.NET datasets and also support connecting to DLL’s that return datasets Database Files - Use this option to connect directly to PC data files that can be accessed through a physical file location from the C: drive or other mapped network drive. This option connects to Access, Paradox, Btrieve, DBase and Foxpro JDBC (JNDI) - Java Database Connectivity (JDBC) enables a connection to databases that require Java only connections. In most cases this connection is usefull when Crystal Reports is embedded in a Java application. ODBC (RDO) - The ODBC/RDO (Open Database Connectivity/Remote Data Objects) folder is typically used for server-based databases like Oracle, Sybase, Informix, and MS-SQL Server OLAP - Use this option when creating reports using On-Line Analytical Processing data sources OLE DB (ADO) - This is a Microsoft data connection similar to ODBC in its concept and requires the use of an OLE DB provider, which is similar to an ODBC driver Salesforce.com - Use this connection to create reports for data stored in Salesforce.com accounts. This connection requires an internet connection and proper user credentials for the Salesforce.com account. Universes - You can create a report from a Business Objects universe. Crystal Reports lets you select a universe and design a query from it using a tool called the Business Objects Query Panel XML and Web Services – Allows reports to be created from an XML data source file or from an XML Web Service More Data Sources - This option allows access to other data sources using native drivers and is dependant on the options selected as part of the installation process NOTE: You may be asked for the Install CD if the driver was not installed for the database you have selected. In Crystal Reports 2008, not all drivers are installed by default. If you see this message and do not have the CD, contact your system administrator.

Crystal Reports 2008 Designer 1 Workshop Lesson 1: Creating a Simple Report

ÂŠ2008 Vision Harvest, Inc.

Repository shows a list of data source connections that have been stored in the repository, usually stored SQL queries. Opening the repository folder will first ask you to connect to Business Objects Enterprise. If you have the rights to do this then you will see the repository. You can then select a SQL command or Business View if there are any in the repository

Choosing a Data Source To choose a data source, click the Create a New Connection folder and select the data source you want, for this class select Access/Excel (DAO) and provide all required information the pop-up dialog window requires. Choosing the Access/Excel (DAO) data connection will activate the Access/Excel (DAO) dialog window which provides the ability to select important database information such as the file name and location, type and login credentials. For this class set the Database Type to Access and the Database Name to point to the Northwind 2008.mdb file provided with the course class files. No login information is required.

Crystal Reports 2008 Designer 1 Workshop Lesson 1: Creating a Simple Report

ÂŠ2008 Vision Harvest, Inc.

After finding the Northwind 2008.mdb file and selecting it, click the Finish button. The next screen will prompt for a username/password if applicable. Respond to this screen and click Finish. Now the database connection is established and the Database Expert reappears. The Database Expert now shows the new connection along with available items that can be selected for inclusion into the report. Some databases are able to determine which tables are available based on user login ID and therefore not all data tables may be shown.

Crystal Reports 2008 Designer 1 Workshop Lesson 1: Creating a Simple Report

Depending on the database being accessed Commands, Tables, Views and Stored Procedures will be shown and available for use in the report. It is important to understand the differences between each of these items. Commands allow for custom Structured Query Language (SQL) to be used as a data source. This means developers can now build complex queries using an advanced SQL building tool like Toad or SQL Navigator and port the completed code into Crystal Reports for use in the report Tables are physical containers of data and are the only items that contain data and are sometimes referred to as “physical tables” Views do not contain data and are sometimes referred to as “logical tables”. These tables are just queries which pull data from the physical tables. Views are usually created by the Database Administrator to make database reporting easier for the enduser to write reports Stored Procedures are very similar to views in that they do not contain data. They are sometimes referred to as “procs”. The differences between Views and Procs are in the complexity of the query code used and the programming language used To add a table to the report, expand the Tables folder and then click the move right button to add the table into the right column labeled Selected Tables. Once all the tables are selected click the OK button. For this class, we are going to use an Microsoft Access database file named Northwind 2008 . This sample database is included with the course class files. 15

Crystal Reports 2008 Designer 1 Workshop Lesson 1: Creating a Simple Report

ÂŠ2008 Vision Harvest, Inc.

Exercise 1.0 - Start a New Report 1.

Click the button, choose All Programs | Crystal Reports 2008 | Crystal Reports 2008 The application opens and displays the start page.

Click the option from the START A NEW REPORT column The Database Expert dialog box appears

Click the to the left of Create New Connection The list of available data sources displays.

Find the Access/Excel (DAO) folder and double-click it This activates the Access/Excel (DAO) dialog window. Please note double-clicking only works on the first database connection created. Afterwards you must double-click the Make New Connection item.

With the Access/Excel (DAO) dialog window open, make sure the Database Type: is set to Access and the Database Name: is pointed the Northwind 2008.mdb data file included with the course class files. Click

Now that the connection is established, verify the connection is now listed in the My Connections folder. Do this by collapsing and expanding the folder or RIGHT click the folder and choose Refresh From the My Connections folder you will now see the new connection just created.

Expand the Tables folder and select the Customers table. Click on

The Customers table will now appear on the right list labeled Selected Tables:. Click The Crystal Reports Design window displays on the screen.

Crystal Reports 2008 Designer 1 Workshop Lesson 1: Creating a Simple Report

ÂŠ2008 Vision Harvest, Inc.

The Main Components of the Design Window The Design view in Crystal Reports contains all the report creation features needed to build a report. Take a moment to look at the screen and identify the features of the Design window.

The window has the standard features of a typical Windows-based program. The following allow you to execute commands and perform tasks in Crystal Reports: The Menu Bar contains all the commands available in Crystal Reports. As with all Windows programs you can open the menus by clicking with the mouse or by pressing ALT + the underlined letter for the menu you want The Toolbars contain buttons that are shortcuts to menu commands. You can view the function of a button by moving the mouse pointer over the button. When you do this, a Tool Tip displays the button name and the Status Bar at the bottom of the screen displays the function of the button TIP: You can open and close the toolbars by RIGHT clicking in the toolbar area. To hide a toolbar, click the check mark to the left of the toolbar name. To show a toolbar, click the toolbar name. Below the tool bars are Report tabs for each report you have open plus the Start Page tab. Clicking on the start page tab will take you to the following dialog:

Crystal Reports 2008 Designer 1 Workshop Lesson 1: Creating a Simple Report

ÂŠ2008 Vision Harvest, Inc.

In addition to the START A NEW REPORT and MY RECENT REPORTS resources the Start Page includes a live web page including numerous links to information, news and resources available on the Business Objects web site. There is a Design tab for each report you have open. The Design tab indicates you are in Design view. Once you have previewed the report, there is a Preview tab as well. You can easily switch between Design view and Preview view by clicking the appropriate tab The Status Bar displays at the bottom of the Design and Preview screens and shows information about selected objects. If no object is selected, the Status Bar shows information about the toolbar button to which the mouse is pointing, information about the report itself, or help information The default Design window is divided into five sections. Each section corresponds to a specific part of the final, printed report. Section names display at the left of the Design window.

Each section has a bar at the bottom of the section that defines the height of the section. When you place the mouse pointer over a section bar, the pointer changes to this image. Using this pointer, you can drag the bar to make the section taller or shorter.

Crystal Reports 2008 Designer 1 Workshop Lesson 1: Creating a Simple Report

ÂŠ2008 Vision Harvest, Inc.

Exploring the Toolbars Crystal Reports has six toolbars. Standard - Contains buttons for basic editing commands such as Save, Open, Preview, Cut, Copy, Paste, Undo, and Help Formatting - Has buttons for formatting commands such as Bold, Italic, Underline, Alignment, and Number formats. Most of these buttons are not active unless you have an object selected Insert Tools - Includes buttons for adding various report objects into the report, such as Text Objects, Grouping, Summaries, Cross-Tabs Lines and Boxes. These buttons are generally active and available for all reports Expert Tools - Provides buttons for modifying and formatting such items as Charts and Maps using an Expert wizard. These buttons may or may not be active depending on the button and the object it represents Navigation Controls - Offers buttons for refreshing the report data and for moving around your report. These buttons will not be active unless in Preview mode External Command - Displays icons to access available, registered third-party applications designed to work within Crystal Reports. Add-in applications are registered within Crystal Reports simply by adding the applicationâ&#x20AC;&#x2122;s DLLs in the Addins directory of the Business Objects install folder. Additional information can be found in the Crystal Reports .Net Developers Guide

Exercise 1.1 - Explore the Design Window 1.

To begin, open the Field Explorer by clicking the

When you have the Design window in front of you, take a few moments and practice these steps before adding data fields: a)

icon

Move the mouse pointer across the Toolbars, reading the tool tips that appear when you touch the pointer on the buttons

b) Run your mouse across the options on the Menu Bar, just above the Toolbar, click once to read the menu choices to familiarize yourself with the terms used by Crystal Reports c)

Place the mouse pointer over the Section boundaries and stretch them up and down

Crystal Reports 2008 Designer 1 Workshop Lesson 1: Creating a Simple Report

Managing Resources with Explorers Managing resources are done through three Explorer components. Field Explorer - Use the Field Explorer to insert all types of fields into the report. The Field Explorer lists the seven types of fields you can insert into a report: Database, Formula, SQL Expression, Parameter, Running Total, Group Name, and Special fields. With the icons at the top of the Field Explorer, you can manipulate the fields in the list

Repository Explorer - This powerful feature offers the report developer the chance to save several kinds of objects for re-use in subsequent reports. Although formulas may not be stored in the repository, many other useful items can be. Items which can be saved are: О

Formatted Text Objects

Graphic Images

Custom Functions

Commands (Queries)

NOTE: The Repository Explorer can only be accessed via the Repository, which is now incorporated into Enterprise. If a user tries to access this feature, a login prompting window will display.

Crystal Reports 2008 Designer 1 Workshop Lesson 1: Creating a Simple Report

Report Explorer - Another very useful feature is the Report Explorer. This tool represents all the sections and objects in the report in a tree-node fashion. In addition to making it easier to find a particular object or section, this explorer allows a user to select an item or items within the group tree for editing purposes. Simply right click on the item to view the editing options available

The explorers can easily be turned on or off for viewing, but are normally docked to the right of the report design area. To “un-dock” an explorer, simply drag it by the title bar to the left of the report or RIGHT click and choose “Undock”. To view an explorer, pull down the View menu to see a list of the three available explorers.

Crystal Reports 2008 Designer 1 Workshop Lesson 1: Creating a Simple Report

ÂŠ2008 Vision Harvest, Inc.

Placing Fields on the Report As you look at the Field Explorer, you will notice there are several categories of object types with each category being represented by an icon to the left of the name. Expand the Database Fields folder to see available tables and fields. Expand a table to see available fields contained in that table. The Field Explorer is automatically docked to the right of the design area. If you prefer to have it floating (un-docked) or in another location, just drag it by its title bar. If you change your mind, just reposition it back to the docking area. The Field Explorer can also be docked to the left of the design area. And finally, the Field Explorer can also be closed any time and re-opened by clicking the icon. There are several ways to place fields onto the report. The Field Explorer dialog box provides access to all the fields in the table(s) you selected when beginning the report. Using the Field Explorer dialog box, you can place a field in the report in one of three ways: Select the field in the list, then click the Insert Fields report where you want the field to appear

button, and then click in the

Drag the field to the report. Release the mouse button when the pointer is pointing where you want the field to appear Right Click on the field and chose Insert to Report from the Short Cut Menu, then click in the report where you want the field to appear Typically, you would place fields in the Detail section of a report. When you place a field in the Detail section, Crystal Reports adds a Field Header directly above the data field in the Page Header section. The field defaults to the actual size of the field in the database or to the size of the field name, whichever is longer. This is very helpful for identifying the field at a glance. WARNING: If you place a field in any section other than the Detail section, Crystal Reports does NOT create a Field Header in the page header section of the report. Even if you later move the field to the Detail section, the Field Header is not created automatically.

NOTE: If you delete the field object from the report, Crystal Reports will normally remove the Field Header from the report as well.

Crystal Reports 2008 Designer 1 Workshop Lesson 1: Creating a Simple Report

ÂŠ2008 Vision Harvest, Inc.

Exercise 1.1 - Place Fields in the Detail Section of the Report 1.

Make sure the Field Explorer is open. If it is not click the Standard Toolbar

icon in the

Expand the Database Fields node, find the Customers table and expand it to see a list of available fields

Select the CustomerID field in the list of fields

Click the button on the Field Explorer toolbar The mouse pointer appears as a : symbol when it is pointing to a location where you cannot place a field.

Move your mouse into the Details section at the left side of the report and LEFT click again Crystal Reports places the field in the details section and the field name in the Page Header section.

Using the same method, add the CompanyName field to the right of CustomerID

Use the Drag and Drop method to add the City field

LEFT-mouse drag the Region field to the Details section after the City field

Your design area should look similar to the example below:

NOTE: In the Field Explorer, you should see a green check mark on the icon to the left of each field you add to the report.

Crystal Reports 2008 Designer 1 Workshop Lesson 1: Creating a Simple Report

ÂŠ2008 Vision Harvest, Inc.

Selecting and Sizing Objects Once a field is placed on a report, it is good practice to resize it to a proper character length. When Crystal inserts the field, it is the same size as the data field in the database, or the size of the field name, whichever is larger. To resize the field to a size you want, you must select it first. When a field is selected, a blue dot appears at the center of each of the four sides surrounding the object. These are called sizing handles. When you place the mouse pointer directly over a handle, the pointer changes to a two-headed arrow. Use this mouse pointer to drag the border of the object to change the size.

Since the data field and the field name appear in the report as the same size, you may often need to select both objects, so you can resize them at the same time. However, if you resize the data field the field header will also resize, whereas if you resize the field header the data field will not resize. To select multiple objects, you can use one of two methods: Click the first object to select it. Then, while pressing the SHIFT key or the CTRL key, click other objects Using the LEFT mouse button, drag a marquee around the objects you want to select. Make sure the mouse pointer is NOT pointing to an object when you begin to drag. The marquee you drag selects any object touching the marquee or contained within the marquee. This process is sometimes referred to as â&#x20AC;&#x153;lassoingâ&#x20AC;? objects

Exercise 1.3 - Size a Field Using the Resize Handles 1.

Select the CompanyName field in the detail section

Point to the sizing handle on the right side of the selected object The mouse pointer changes to a double-headed arrow when you are pointing to the correct location.

Drag to the left to make the data and header fields about 90% of their original size Notice that Crystal provides a line and shading on the ruler so you can easily see exactly how wide you are making the objects. Also notice how the field header in the Page Header section resized itself to match the data field it is associated with. This last feature reduces time spent matching field headers to data fields in terms of size.

Crystal Reports 2008 Designer 1 Workshop Lesson 1: Creating a Simple Report

You can also resize fields to be the same size as another field using the Size command. To do this, simply multiple select the fields you want to size plus the field that is the size you want to match. RIGHT click the field that is the size you want, and then choose Size from the Shortcut menu. Crystal can make all selected fields the same width, height, or overall size as the field on which you RIGHT clicked.

Exercise 1.4 – Resize a Field to Match Another Field 1.

Select and resize the field header labeled “City” in the Page Header section. Resize the field header to be about 1.5” long, which should make it shorter Notice how the data field “City” doesn’t resize to match its associated field header. This gives us added flexibility in sizing our field headers to be different sizes than their associated data fields.

Multiple select the City data field and its title, then RIGHT click the header field Be sure you RIGHT click on the header field and not the data field located in the Details section.

Point to Size in the shortcut menu, and then choose Same Width Crystal makes both the data field and the field header the width of the longer object.

Browsing Field Data If you are not familiar with the data contained in a field, you might not know what size the field needs to be on the report. Crystal Reports lets you preview the data contained in a field to help you determine how big a field should be. To view the data in a field, select the field in the Field Explorer dialog box, and then click the Browse button in the Field Explorer toolbar. If the Field Explorer dialog box is not open, you can open it by clicking the Field Explorer button on the Toolbar. You may also right click on a field and select to Browse data.

Crystal Reports 2008 Designer 1 Workshop Lesson 1: Creating a Simple Report

Exercise 1.5 - Browse Field Data 1.

Make sure the Field Explorer is still open; if it isn’t click the Field Explorer button in the toolbar to open the Field Explorer. Select the Region field in the Data Fields list

Click the Browse Fields button The Browse Field Data dialog box opens.

When you are finished viewing the field data, click Close to close the Browse dialog box. 3.

Multiple select the Region field and its field title. Resize them to make both smaller When you are finished, click away from the objects to deselect them However, what if you can’t see the right edge of the fields to resize them?

Select View|Zoom... and select the Fit One Dimension option. Click OK

Now, drag the resize handle to resize the Region field so it fits on the page

Reset the Zoom dialog box to a Magnification Factor: 100%

NOTE: The Browse dialog box, by default, displays the first 500 unique values in the database.

TIP: You can also open the Browse dialog box by RIGHT clicking the field in the report and choosing Browse Field Data... Be sure you RIGHT click the data field in the Details section, not the field header object.

Crystal Reports 2008 Designer 1 Workshop Lesson 1: Creating a Simple Report

ÂŠ2008 Vision Harvest, Inc.

Moving and Aligning Objects To move a field, you must first select it. Once selected, position the mouse directly over the object until you see a four-headed arrow. With this arrow, you can move the field by dragging it. To drag both a data field and its field name at the same time, just multiple select the fields, and then drag with the four-headed arrow. You can even drag fields from one section of the report to a different section. You can also move a data field and its field name by simply moving the data field. The only problem with moving fields like this is they sometimes get out of alignment with their titles. However, the shortcut menu has an Align command that works similarly to the Size command you have already used. Just multiple select the fields you want to align, then RIGHT click the field you want to align to. Choose Align from the shortcut menu and then choose to align on the left side, the right side, the center, or to the closest grid point.

Exercise 1.6 - Move and Align an Object 1.

Move the City field header to the left, closer to the CompanyName field Notice only the title moved. Now it is out of alignment with the field itself.

Multiple select the City field and its field header. Then RIGHT click on the field header

Choose Align, then Lefts from the shortcut menu Crystal aligns the field and its title on the left edge of the title.

TIP: You can also move or size an object with the keyboard. To move an item, select it, and then use the arrow keys on the keyboard to move it. If Snap to Grid is turned on, the arrows move the item one grid point. If it is turned off, the arrow keys move in very small increments. This is referred to as NUDGING an object. To resize using NUDGE, press and hold down the SHIFT key while pressing an arrow key.

Crystal Reports 2008 Designer 1 Workshop Lesson 1: Creating a Simple Report

Using Guides and Guidelines to Move and Align Objects Although you can simply drag an object to move it, an easier method is to move fields and their field names using the guidelines. Guidelines are triangular buttons with attached dashed lines that appear on the ruler bar at the top of the Design window. You can use these guidelines to easily move and align objects. Crystal Reports inserts guidelines automatically in these situations: Whenever you insert a field in the Details section of a report, the program automatically creates a guideline at the left edge of the field frame and snaps the field and field title to it. You can move both the field and the field name, keeping them aligned, by dragging the guideline marker in the ruler If you summarize a field, the program snaps the summary to the same guideline as the field you summarized to assure proper alignment When you RIGHT click the shaded areas to the left of a section and choose the Arrange Lines command, the program automatically creates one or more horizontal guidelines in the section and snaps the fields to them When you move an object by dragging it with the four-headed arrow, you may move it off the guideline. If you do this, the object is no longer snapped to the guide and does not move with the guide. You can re-snap the object, or snap any other object to any guide by moving the left or right edge of the object so it touches the guide. When an object is snapped to a guideline, small red marks appear on the edge of the object where it touches the guideline.

You can also work with guidelines manually. Crystal Reports lets you add guidelines and delete them whenever you need to. To add a guideline to the horizontal ruler in any section, click in the ruler where you want the guideline to appear To add a guideline to the vertical ruler, simply click in the ruler where you want the guideline to appear To delete a guideline, drag its triangular button (Head) off the ruler TIP: You can snap a guideline to a field by moving the guideline to the edge of the field or by moving the field’s edge to the guide. TIP: Guidelines are much easier to work with if you can see the “tails”, that is, the dashed lines that drop down from the ruler. To turn on the tails, choose File/Options… Click the Show Guidelines in Design command, and then click the OK button.

Crystal Reports 2008 Designer 1 Workshop Lesson 1: Creating a Simple Report

Exercise 1.7 - Work with Guidelines 1.

First, turn on the tails for the guidelines. Choose File/Options…

Check on Guidelines in the Design View options of the Layout tab. Also check on the Grid option and then click OK Crystal displays the dashed lines extending down from the guideline markers in the ruler. By making the Grid viewable, it is easier to see field alignment.

Point to the guideline marker for the Region field and drag it to the left. Position it right after the City field

Since you previously moved the City field by dragging it with the four-headed arrow, you unsnapped it from its guideline. Using the now unused guideline, move the guide head to line up the guide tail with the left edge of the City fields Be sure to look for the red marks to see if the field is snapped to the guideline.

Move the guideline so the Region field and its field name are positioned just to the right of the City field

Now you need to add another field. Make sure the Field Explorer is still open

Insert the LastYearsSales field to the right of the Region field Notice the guideline is automatically inserted on the right for numeric fields.

For purposes of this exercise, delete the guideline that was originally used to align the field by dragging its marker off the ruler

The Last Year’s Sales field is a number. Numbers usually look better if they line up on the right. Therefore, you need to create a new guideline for this field. Click on the horizontal ruler at the 7” mark Crystal creates a new guideline there.

10.

Multiple select the Last Year’s Sales field and its field name, and then drag the objects so their right edges touch the new guideline

11.

Using the LastYearsSales guide, reposition the fields to the right of the Region fields

Crystal Reports 2008 Designer 1 Workshop Lesson 1: Creating a Simple Report

Exercise 1.8 - Use the Arrange Lines Command to Align Field Titles Horizontally 1.

Click in the Page Header section at the left of the screen with the RIGHT mouse button A shortcut menu opens.

Choose Arrange Lines Guidelines appear wherever a field is located in the section and sometimes even where they don’t appear.

Make sure all the field headers are lined up on a single guideline. Move any titles to the guideline if they are not

If you see additional guidelines in the vertical ruler, delete them by dragging them off the ruler

Move the vertical guideline to position the field titles where you want them

WARNING: Be careful when you are moving a guideline. If the mouse pointer is not positioned in the ruler when you release the mouse button, you will delete the guideline. If this happens, just create a new guideline by clicking in the ruler where you want it to appear or click the Undo button.

Creating Text Objects In addition to adding fields to a report, you can also add text objects. A text object is an object in which you can type any text you wish. It is not related to the underlying database in any way. Typically, you use text objects as titles or identifiers for summary information. To create a text object, choose Insert/Text Object from the menus or click the Insert Text Object button on the Toolbar. The mouse pointer resembles a “crosshairs” from which you can either LEFT click onto the report to add the default sized text box, or draw a custom sized text box for entering text using the LEFT mouse button. When you first insert a text object, it is in Edit mode. You see a flashing insertion bar inside the object. You can type text at this point. When you are finished adding text, click away from the object to finish editing. You cannot end the editing by pressing the ENTER key. This only adds another line to the text in the object. TIP: You can tell if you are in Edit mode by looking for the flashing insertion bar. Crystal Reports also displays a small ruler above the object when you are in Edit mode. If you need to make corrections to an object after you have exited Edit mode, double click the object to return to Edit mode.

Crystal Reports 2008 Designer 1 Workshop Lesson 1: Creating a Simple Report

ÂŠ2008 Vision Harvest, Inc.

Exercise 1.9 - Insert a Text Object 1.

Click the Insert Text Object button The mouse pointer displays as a + (crosshairs) symbol until you point into the report.

Click in the Report Header section to add the object The text box appears with a flashing insertion bar.

Type Customer Sales

Click away from the text object to finish editing

Resize the object if necessary to view the entire text on one line

Saving the Report As with any software, it is a good idea to save your reports frequently in Crystal Reports so you do not lose work you have done. To save a report, you can choose File/Save from the menu or click the Save button on the Toolbar. The first time you save a report, Crystal Reports opens the Save As dialog box. In this box, you can specify a folder location for the report and give the report a filename. The six icons on the left give you one click access to common locations.

Crystal Reports 2008 Designer 1 Workshop Lesson 1: Creating a Simple Report

ÂŠ2008 Vision Harvest, Inc.

The Desktop icon lets you save this report your Windows Desktop My Documents opens the Windows My Documents folder on Windows 2000/XP systems or the user Documents directory on Windows Vista The Favorites folder opens the list of favorite folders you have specified in Internet Explorer. If you have a folder where you want to keep all of your reports, add it to the Favorites folder, then it is easily accessible from here The CR.COM option provides the ability to publish the report to a specific CR.COM (crystalreports.com) account. The crystalreports.com connectivity module must be installed to use this feature Enterprise lets you save your report to an Enterprise folder My Connections offers a list of saved connections to either crystalreports.com or Enterprise. This makes it easier to publish to multiple locations without having to con figure connection settings each time Once you have saved and named a file, the Save command automatically overwrites the previous version of the file with the revised version. Crystal Reports does not prompt you to overwrite. If you want to preserve the previous version of the report, you must use the Save As command on the File menu to give the revised report a new name.

Autosaving the Report Crystal Reports has an Autosave feature that automatically saves the report for you in a time frame you specify. To turn on the Autosave feature, choose File/Options from the menu and then select the Reporting tab. You can check mark the Autosave Reports After command, then enter the number of minutes between saves.

Crystal Reports 2008 Designer 1 Workshop Lesson 1: Creating a Simple Report

ÂŠ2008 Vision Harvest, Inc.

Exercise 1.10 - Save the Report 1.

Click the Save button on the Toolbar Crystal Reports opens the Save As dialog box.

Select the My Documents button

Create a new directory and name it CR2008

Name the report Basic2008

When finished, click the Save button

Crystal Reports 2008 Designer 1 Workshop Lesson 1: Creating a Simple Report

ÂŠ2008 Vision Harvest, Inc.

Previewing the Report When you have saved the report, you can see how it looks with collected data by using the Preview command. To preview the report for the first time, choose File/Print or click the Print Preview button on the toolbar. Crystal Reports creates a Preview tab and displays the report similar to how it will look when printed. Once you have created the Preview tab, you can switch between Design and Preview views by clicking the appropriate tab.

In the toolbars area you should see the Navigation toolbar. Usually it will be located in the bottom row to the very right.

Use the

button to refresh the data

Use the and buttons to move through the report one page at a time. Use the or to display the first or last page of the report The button stops the processing of the report and displays the report with whatever data it has gathered up to the point you stopped the processing

Crystal Reports 2008 Designer 1 Workshop Lesson 1: Creating a Simple Report

ÂŠ2008 Vision Harvest, Inc.

The page indicator in the middle of the arrow buttons indicates the page you are viewing and the total number of pages in the report. Crystal formats the report only to display requested pages to save time when previewing the report. If you see a + sign after the total page count, that means that Crystal has not formatted all the pages of the report, so the total count is not known. To get the actual total page count, go to the last page of the report

Refreshing the Data When you preview a report, Crystal Reports queries the underlying database, pulls the records you requested, then saves the retrieved records in a combination of active memory and disk files. Thereafter, Crystal Reports works with the same dataset on the local machine rather than requerying the database. Once you have pulled the initial set of records, Crystal Reports can work very quickly because it does not have to re-query the database each time you want to preview the results of your work. If you want Crystal Reports to re-query the database, you can choose to refresh the data. Crystal Reports automatically refreshes the data in certain circumstances: When you add additional fields to the report When you add criteria that causes Crystal Reports to select additional records from the database When you add a formula that uses a field not currently being used in the report If you want Crystal Reports to automatically discard the saved data and pull new data from the database whenever you open the report, you can specify for Crystal to do this. Choose File/Options, select the Reporting tab, and then check the Discard Saved Data on Open command. You can refresh the data manually whenever you want by choosing Report/Refresh Report Data, or by clicking the Refresh button on the Toolbar

Crystal Reports 2008 Designer 1 Workshop Lesson 1: Creating a Simple Report

ÂŠ2008 Vision Harvest, Inc.

Exercise 1.11 - Preview the Report 1.

Click the Print Preview

button

Click the page navigation buttons to view different pages of the report

Click the navigation buttons to view the first and last pages of the report Notice the change in the total page count after you have navigated to the last page of the report.

Click the Close button on the Preview tab to close it

Click the Print Preview button to restore the tab

Click the Design tab to return to Design view. Click the Save button again to save the report

Using the Status Bar The Status bar offers additional value beyond providing descriptions of selected objects. When in Preview mode, the Status bar shows: Data and time of last database refresh Number of records retrieved and saved Zoom controls - The Zoom control can be used in both Design and Preview mode. Features include the ability to quickly view the whole page, width only or to set the percentage dimension NOTE: When you preview the report, you may see a blank white area on the left of the screen along with Groups, Parameters and Find options. This is the Preview Panel area. You will learn more about using this later. For now, you can turn this off by clicking the Toggle Preview Panel button on the Standard toolbar.

Crystal Reports 2008 Designer 1 Workshop Lesson 1: Creating a Simple Report

ÂŠ2008 Vision Harvest, Inc.

Getting Help You can obtain help on Crystal Reports by choosing Help, Crystal Reports Help or by pressing the F1 key.

The left side of the screen provides help in locating the information you want. Contents shows a table of expandable subjects through which you can look for information. Think of this as a table of contents Index provides a search method that looks through screen titles. Just type the keyword you want. Crystal Help interactively displays topics with the keyword in the title Search is a word search option that searches help screen text. Again, type the keyword or words you want, and then click the List Topics button to see a listing of screens that contain your keyword Favorites display a list of topics you have added as favorites TIP: If you need help on functions in the Formula Editor, look them up by the function name or by the general category to which they belong. For example, search for the function name ReplicateString. If you cannot find the function by searching for the name, then look up the general category to which it belongs. You cannot locate For Loop by searching for the name, but you can find it by searching for its category, Control Structures.

Crystal Reports 2008 Designer 1 Workshop Lesson 1: Creating a Simple Report

Challenge Exercise – Creating a Basic Report Create a report that lists orders with important date and payment information. 1.

Select File Menu|New|Blank Report from the Menu bar Note if you click on the New Report button by default you will be taken into the Standard Report expert.

Create this report as a Blank Report

In the Database Expert window, open the My Connections folder, the Northwind 2008 database connection, Tables and then add the Orders table. Click OK

Add the following fields to the Details section: OrderID, OrderDate, RequiredDate, ShippedDate and OrderAmount The OrderID and OrderAmount fields are number fields. Notice how Crystal Reports provides guidelines on the right edges of these fields. This is because number fields are right-aligned by default.

Browse the OrderDate field to see what the date looks like. When you are finished, close the Browse box Notice the field shows both the date and the time. It needs to be about 1.5” long to display all the data.

Resize the OrderDate field to be 1.5” long

Using the Size command on the shortcut menu, resize the RequiredDate and the ShippedDate fields to be the same width as OrderDate Notice how the field headers automatically resize the same as the data fields.

Use guidelines to position the fields so they look good to you

Add a text box to the Report Header section. Type Unpaid Orders

10.

Save the report. Name it Unpaid Orders. Preview the report then re-save it after you have previewed

11.

When finished, close the report

Crystal Reports 2008 Designer 1 Workshop Lesson 1: Creating a Simple Report

ÂŠ2008 Vision Harvest, Inc.

The Preview view of the report should look similar to the following illustration:

Crystal Reports 2008 Designer 1 Workshop Lesson 1: Creating a Simple Report

ÂŠ2008 Vision Harvest, Inc.

NOTES

Crystal Reports 2008 Designer 1 Workshop Lesson 2: Formatting Features

ÂŠ2008 Vision Harvest, Inc.

Lesson 2 Formatting Features

Crystal Reports 2008 Designer 1 Workshop Lesson 2: Formatting Features

ÂŠ2008 Vision Harvest, Inc.

Lesson Objectives After completing this lesson, you will be able to: Use the Template Expert Choose a pre-formatted template. Itâ&#x20AC;&#x2122;s the fastest way to make dynamite reports Format objects Learn the best methods for controlling the formatting of the fields. Learn to use the Format Painter to copy and apply format properties Add lines and boxes to reports and format them Create professional looking reports by adding separator lines or boxes to surround groups. Format drawing objects to match the look and feel you wish Add and format graphics Graphic images add zip to reports. Crystal Reports imports most graphic image formats so you can dress up the printed output Set the page orientation and margins of the report Choose whether to print the report on a Landscape or a Portrait oriented page and learn how to define margin settings Place fields within Text Objects Combine fields with text quickly and easily. Crystal Reports trims field data automatically when you put a field into a text object, so it is simple to create text based documents in a report Format all or part of an object Format an entire object, a single character, or a field within a text object to call attention to important information Insert Special Fields into the report Tell Crystal Reports to automatically print the date and time, page numbers, titles and comments and refresh dates. You can quickly add these special fields from the menu bar

Crystal Reports 2008 Designer 1 Workshop Lesson 2: Formatting Features

Quick Formatting with the Template Expert Crystal Report’s Template Expert contains several pre-formatted report styles that can quickly make your report into a professional looking printout. It applies borders, shading, lines, arrangement and other options to your report. To open the Template Expert, choose Report/Template Expert… or click the Template Expert button in the Experts toolbar. The Template Expert applies multiple commands to your entire report. Even with the complexity of the process, you can undo this command. In the Template Expert, you can select a style from the list on the left. The image at the rights shows you an example of the style applied. Click OK to apply the style to the report.

The Template Expert gives you the ability to create your own customized templates either using pre-existing reports or creating special template reports using Template Field Objects. The considerations are complex and not possible to cover in this class. However, Creating Custom Templates is covered in detail in the Designer 2 workshop.

Crystal Reports 2008 Designer 1 Workshop Lesson 2: Formatting Features

Exercise 2.0 – Apply a Template Using the Template Expert 1.

You should still have the Basic2008.rpt report open. If you do not, choose File, then select the report from the list at the bottom of the menu

Go to Preview view and make sure the report is saved Look at the Save button in the toolbar. If it is grayed out, then the report is saved. If not, click the button to save the report.

Click the Template Expert

Select each of the templates to see what they look like, and then choose the one you like and click OK

Sometimes automatic styles do not provide the look you want for your report. To revert the report back to its original state, repeat step 3 and in the Template Expert dialog box select the radio button to Undo the current template and then click OK The dialog box closes and the report is reverted back to its original state

button

WARNING: Using the “Undo the current template” option only reverts back one template instance, which means if you have tested different templates, you will need to close the report without saving the report. Therefore, it is advisable to save the report before using this feature.

Crystal Reports 2008 Designer 1 Workshop Lesson 2: Formatting Features

ÂŠ2008 Vision Harvest, Inc.

Formatting Objects You can format objects with greater control by using the Format Editor. The easiest method for opening the Format Editor is to RIGHT-MOUSE click on the object you wish to format. RIGHT click any report object or multiple selected objects, then choose Format Textâ&#x20AC;Ś Depending on the object being formatted, the option may read Format Graphic..., Format Field... and so on. In any case, choosing this option will open the Format Editor. The Format Editor can also be opened once a object is selected by clicking the Format Editor button.

The Format Editor displays different choices depending on the type of object and the type of field you chose to format. The next few pages show a table of tabs you may see in the Format Editor and the types of fields for which they appear.

Crystal Reports 2008 Designer 1 Workshop Lesson 2: Formatting Features

Option Common Tab

ÂŠ2008 Vision Harvest, Inc.

Format Editor Tabs and Options Description

Object Name

This item is used to create a name for the object and is very useful when the object is viewed in the Repository Explorer, Report Explorer and when defining Report Parts hyperlinks. Only letters and numbers are allowed for the name. Spaces or other special characters will NOT be accepted Tool Tip Text Allows you to display help text when moving the mouse over the object Read Only This disables the ability to apply additional formatting changes, but is not intended to provide report security Suppress if Duplicated Causes the object to NOT display duplicate values in a column of data Suppress* Causes the object to NOT display on the report in Preview Suppress Embedded Field When checked, this option suppresses any blanks in fields embedded in the text field. Blank Lines This is often useful when displaying an address where the address line 2 may be blank Lock Position and Size This makes it impossible to move or resize the object Can Grow* For objects with variable lengths, this option allows the object to grow vertically to accommodate all the text. You can define a limit to the growth by typing a number in the Maximum Lines text box Text Rotation Use the Text Rotation options to rotate text within the object 90 or 270 degrees Horizontal Alignment Aligns the text left, centered, right or justified for non-text fields such as number fields CSS Class Name Use this field to specify a class name for the report object youâ&#x20AC;&#x2122;re formatting. The class name can be used in your CSS to indicate a unique style for the selected report object. This feature is meant for web delivered reports Repeat on Horizontal This format option allows you to repeat objects, such as text objects, for reports that Pages have horizontal page breaks. The feature will greatly enhance reports containing a multi-page crosstab Display String This format option allows you to use the formula language to customize the formatting of the field. Use a formula to display number, currency, date, time, date and time, Boolean, or even string fields as a custom string. This type of conditional formatting is not limited to database fields; you can use it for running totals, formulas, parameter fields, summaries, and so on Keep Object Together Prevents the object from being split with a page break in the printed report Close Border on Page Break If the object is split with a page break and has a border, checking this option closes the border at the end of the page

Crystal Reports 2008 Designer 1 Workshop Lesson 2: Formatting Features

Option Number Tab Style* Currency symbol (system default) Customize

Use Accounting Format

Suppress if Zero* Decimals Rounding* Reverse Sign for Display

Allow Field Clipping*

Decimal Separator Thousand Separator Leading Zero

Format Editor Tabs and Options Description This list displays several pre-defined styles for the number. To apply a style, select it in the list, then click OK Click here to display a currency symbol on the number. You can display a Fixed symbol, which displays the symbol at the left edge of the field, or a Floating symbol, which display the symbol directly to the left of the number Click this button to display an additional dialog box to customize the number style. Listed below are the options available for customized numbers Custom Style Number Tab When you select this option, three defaults are set automatically. The negative symbol used, and its position, is determined by the Windows Regional Settings (it will be either the minus sign or the brackets). The dash will be automatically chosen for “Show Zero Values as”. Zero values are automatically displayed as dashes (-) in your report, and the currency symbol is fixed on the left side Hides the data if the value is zero Truncates the number to the number of decimals you define Rounds the number to the number of decimals you define When this box has a check mark, Crystal truncates the number in fields where the number is longer than the length of the field. For example, if the field is set to display 6 characters, and the number is 5,407.65, the field displays 407.65. If there is no check mark, then the field displays ###### to indicate that the field is not long enough to display the number. This is the default When this box has a check mark, Crystal truncates the number in fields where the number is longer than the length of the field. For example, if the field is set to display 6 characters, and the number is 5,407.65, the field displays 407.65. If there is no check mark, then the field displays ###### to indicate that the field is not long enough to display the number. This is the default Sets the character for the decimal separator Sets the character for the thousand separator Includes a zero before the decimal point for numbers between zero and one. Example: 0.12.

Custom Style Currency Tab Show Zero Values As Enable Currency Format One Symbol Per Page Position Currency Symbol

This list lets you define how zero values display. Options are <Default Format>, - or 0. It is also possible to input your own values. Turns display of currency symbol on or off. You can choose to display the symbol at the left of the field by clicking the Fixed radio button Displays the currency symbol on only the first item on each page Displays a list of all available positions for the currency symbol Lets you type the symbol you want to use for the currency symbol. $ is the default

Crystal Reports 2008 Designer 1 Workshop Lesson 2: Formatting Features

Option Font Tab Font* Style Size* Color Effects Character Spacing Exactly

ÂŠ2008 Vision Harvest, Inc.

Format Editor Tabs and Options Description Sets the font face for the object Defines regular, bold, italic or bold and italic styling Sets the size of the font for the object. This also provides the ability to specify fractional point sizes such as 10.5, 12.5 and so on Sets the color for the text in the object Turns on or off strikethrough and underline features for the object Use this option to specify the value that each character in your selected font occupies. The value is defined as the distance in the number of points measured from the start of one character to the start of the next. When you change the character spacing, you change only the spacing between adjacent characters, not the font size of the characters

Border Tab Line Style* Tight Horizontal Drop Shadow Border Color* Background Color

Defines the style of the line independently for each side of the object Trims the box to the size of the data in the object Adds a shadow to the lower right of the object Defines the color of the border line and drop shadow if added Defines the color of the background of the object

Date/Time Tab Style* Customize Order Separator Date Type Calendar Type Format*

Order Day of Week Link separators to date order Separators* Encl Position

This list displays several pre-defined styles for the date and time. To apply a style, select it in the list, then click OK Click this button to display an additional dialog box to customize the number style Custom Date/Time Tab Specifies the order of the date and time. There are four choices: Date Time, Time Date, Date, and Time Defines the separator between the date and time Custom Style Date Tab Sets the date format based on settings of Short Date, Medium Date or Long Date If you are using Windows NT/95/98/2000 your operating system may support more than one calendar type. If so, choose the calendar type applicable Defines the display of the month, day and year. Select display formats from the dropdown lists

Sets the order of the three date sections: year, month, day or day, month, year or month, day, year Displays the day of the week before or after the date and allows special formatting option when chosen Select this option if you want any separators you specify to be considered when sorting date fields Defines the separator characters placed between the three date sections Select Square or Round brackets to enclose the day of the week Choose whether the day of the week is leading or trailing 48

Crystal Reports 2008 Designer 1 Workshop Lesson 2: Formatting Features

Option Time Tab

Format Editor Tabs and Options Description

Use System Default Format When you select this check box, the program, by default, uses the date formats from the Regional Settings dialog box in the Windows’ Control Panel 12 Hour/24 hour Chooses to use a 12-hour clock or a 24-hour clock (military time) Hour: Defines the display for hours, minutes or seconds. Select display formats from the Minute: drop-down lists Second:

Paragraph Tab First Line Left Right Line Spacing Reading Order Text Interpretation

Horizontal Alignment

Enter the distance in inches (or the default measurement for your report) that you want the first line of the paragraph to be indented from the left margin of the page Enter the distance in inches (or the default measurement for your report) that you want the paragraph to be indented from the left margin of the page Enter the distance in inches (or the default measurement for your report) that you want the paragraph to be indented from the right margin of the page Enter the spacing you want for lines as a multiple of the font size you are using, or as an exact number of points Use the options in this area to define whether your text is to be read from left to right or from right to left Use this list to select the type of pre-formatted data (string or memo fields only). Crystal Reports extracts information about the font, size, style, color, and paragraph and displays the data or text with the specified attributes. Your choices are: • “none” to interpret the string or memo field or imported text as if it contains plain text • “RTF Text” to interpret the string or memo field or imported text as if it contains Rich Text Format data • “HTML Text” to interpret the string or memo field or imported text as if it contains HTML data Use this option to align text left, right, center or justified. This option is on the Paragraph tab for text fields, but on the common tab for non-text fields

Picture Tab Crop From Scaling Size Reset

Defines area of the picture to NOT display. This area is cut off from the picture Lets you reduce or enlarge the picture based on scale percentage Defines the actual size of the picture’s width and height Resets the picture format to the defaults specified in the Original size display box

Graphic Location

Provides ability to specify an URL or Disk file location for the graphic

Crystal Reports 2008 Designer 1 Workshop Lesson 2: Formatting Features

Option Boolean Tab Boolean Text

Format Editor Tabs and Options Description Sets the display of the Boolean text

Hyperlink Tab No Hyperlink* A Website on the internet Current Website Field Value An E-mail Address A File Current E-mail Field Value Report Part Drilldown Another Report Object Hyperlink Information

This is the default option meaning there are no hyperlinks defined for this object Selecting this option allows the object to have a link to a static website address This option creates a link to a website defined in the data field This option creates a “mailto:” command for submitting an email based on an address specified Select this option to create a hyperlink to a file on a specific computer or networked computer. Use the formula button to create a file path based on a field value This option creates a mailto command for submitting an email based on an address contained in a data field. This option only works with the DHTML Viewer and is used to allow drilldown navigation between Report Parts within the same report This option lets you define a hyperlink path for linking objects in the same or different reports This feature will vary in its options based on the selection(s) made in the Hyperlink Type or DHTML viewer options

Subreport Tab Subreport Name On-demand Subreport On-demand Subreport Caption Subreport Preview tab Re-import when opening

Suppress Blank Subreport

This item is used to create a name for the subreport and is very useful when the subreport is used in the Report Explorer and when defining Report Parts hyperlinks Select this check box to create a hyperlink to a subreport This is used in conjunction with the “On-demand Subreport” option and allows the caption hyperlink to be conditionally set Click the X+2 button to enter text that the user will see to identify the hyperlink to the subreport Select this check box to import the subreport from the original file whenever you open the main report. If you have made changes to the original file, your subreport is automatically updated when opening the main report When a subreport doesn’t return data, it will be suppressed if this option is checked on

Crystal Reports 2008 Designer 1 Workshop Lesson 2: Formatting Features

Format Painter Another formatting option available is the Format Painter, which is similar to format painters in standard office suites. The Format Painter allows you to copy a large number of formatting properties from one previously formatted object to other objects with a click of the mouse. The object containing the formatting you copy from is called the source object. You may only select one source object at a time. To apply the copied format, you simply click the Format Painter button on the Standard Toolbar or choose Format|Format Painter from the menu or right click and choose Format Painter from the pop-up menu. As you move the cursor over other objects, called target objects, you will either see the paintbrush , which means you can apply the format to the object or , which means you cannot apply the formatting from the source object to the target object. For example, you cannot copy formatting from a bitmap object to a database field. Data types are important with the Format Painter. If you use the Format Painter from a numeric source object to an alpha object then not all the formatting options will be copied. Formats like font color; background color and borders that are independent of the datatype will be copied but specific data type formatting like number of decimal places will only be copied if painting an object of the same data type. To use the Format Painter on multiple objects hold down the Alt key and the paintbrush icon will remain as you click on multiple objects. DOUBLE-CLICK the Format Painter button to also paint several objects. Note: Turn off the Format Painter by pressing the ESC key. If you have used the Highlighting Expert to conditionally format a field, the Format Painter will not copy the format properties. However, if you have used conditional formatting via the X+2 buttons, the format properties will be copied. Conditional formatting and the Highlighting Expert are covered later in the course.

Exercise 2.1 - Format a Single Field 1.

Return to Basic2008.rpt and select the report title in the Report Header with the text “Customer Sales”. RIGHT click the report title, then choose Format Text… The Format Editor dialog box is displayed.

Using the above table, set the following formats: a)

Name the text object ReportTitle

b) Add a double line border c)

Add a drop shadow

d) Change the border color to a color of your choice e)

Change the background color to a color of your choice

Change the title font and font size 51

Crystal Reports 2008 Designer 1 Workshop Lesson 2: Formatting Features

Resize the field to display all the title text on one line

Select the Last Year’s Sales field

RIGHT click the field, choose Format Field…

Making sure the Display Currency Symbol check box is checked on and set to Fixed, choose the style with a thousands separator, but no decimals then click OK

Exercise 2.2 - Format Multiple Fields 1.

Multiple select the field titles in the Page Header section This is usually easier to do from Design view.

RIGHT click one of the titles Notice that the shortcut menu is different from when you have one field selected.

Choose Format Objects… The Format Editor dialog box opens.

Change the font color and make the titles bold

Remove the underline on the titles, and then click OK

Make sure all the titles are left aligned, except CustomerID and LastYearsSales, which should be right-aligned

When you are finished, deselect the titles You might need to resize some titles to display all the bold text.

Preview the report

Exercise 2.3 - Format Fields Using the Format Painter 1.

Select the column heading CustomerId

Increase the Font size by one point

Make the heading italic and colored red

Using the Format Painter, copy the formatting to the other column headings

Select the LastYearsSales field. Using the Format Painter, copy the formatting to the Customer Id field. Notice the Customer Id field now has 2 decimal points and a currency symbol. This is not what you want so click the undo button to remove the formatting

Using the File|Save As command, save this report and name it Format.rpt

Crystal Reports 2008 Designer 1 Workshop Lesson 2: Formatting Features

Inserting Lines and Boxes A valuable feature of Crystal Reports is its ability to integrate graphics into reports to make them more attractive and useful. This lesson discusses how to add lines and boxes as separators or to draw attention to particular figures or groups.

Drawing a Line To draw a line, choose Insert/Line from the menu or click the Insert Line button in the Insert Toolbar. The mouse pointer changes to the shape of a pencil. Drag the pencil to draw a line wherever you wish (under a title, sub-total, grand total etc.). When you select and place the pointer over the line, the mouse pointer changes to a 4-directional arrow. This mouse pointer allows you to move the line just like any other object on the report. A selected line has handles at each end. To size the line, simply point to a handle to see a two-headed arrow, click and drag the arrow either direction to shorten or lengthen the line. If you wish to change the style of the line, select it, then choose Format/Format Line… from the menu. An even faster way to format the line is to RIGHT click it, then choose Format Line…

Crystal Reports 2008 Designer 1 Workshop Lesson 2: Formatting Features

ÂŠ2008 Vision Harvest, Inc.

Exercise 2.4 - Insert and Format a Line 1.

Make sure you still have the Format.rpt report open. Click the Design tab to return to Design view

You need to have some space under the field titles in the Page Header. If you do not, drag the Page Header border down to make some space

Click the Insert Line button The mouse pointer changes to the shape of a pencil.

Drag a line in the Page Header under the field titles the width of the report

RIGHT click the line, choose Format Lineâ&#x20AC;Ś

Choose a style, width and color that you like

Click OK

Save the report (Format) and preview the report

Drawing a Box Drawing boxes is much like drawing lines. You can click the Insert Box button on the Insert Tools Toolbar or choose Insert/Box from the menu system. Again, you get a pencil mouse pointer. Drag with the pencil to draw a box. Be careful what sections you are working in when drawing a box. If you want a box around only a group header, make sure you do not release the mouse button while pointing into any other section. On the other hand, Crystal Reports does let you draw boxes around multiple sections so you can enclose objects in different sections within a single box. TIP: It is always better to work in Design view when drawing lines or boxes. You can more easily see the sections in which you are working.

Crystal Reports 2008 Designer 1 Workshop Lesson 2: Formatting Features

ÂŠ2008 Vision Harvest, Inc.

To move or resize the box, select it first. Then, just like any other object, move it by dragging with the 4-dimensional arrow, or resize it by dragging one of the handles. Just like a line, you can format the box by RIGHT clicking it while it is selected.

Suppose you want to call special attention to the Last Yearâ&#x20AC;&#x2122;s Sales of each customer. You can shade the cell by enclosing it in a box, then specifying a background color. You could also just put a background color on the field object itself, but that would display each specific value with shading, like this:

What you want is a continuous shaded area around all the values plus the field title. For that, you have to draw a box.

Crystal Reports 2008 Designer 1 Workshop Lesson 2: Formatting Features

Exercise 2.5 - Insert and Format a Box 1.

Make sure you are in Design view

Click the Insert Box

Point the pencil above and to the left of the LastYearsSales title in the Page Header section

Drag down and to the right until the pencil is pointing to the right of the LastYearsSales field in the Details section. Continue to drag the mouse down into the bottom of the Report Header section and then release the mouse button

RIGHT click on the box (you must be pointing to the box border), and then choose Format Box…

Choose a Silver border color

Choose a Silver fill color, click OK

Save the report (Format) and preview the report The box should surround the Last Year’s Sales field and its title continuously, like this:

Preview Mode

button on the Insert toolbar

Design View

Crystal Reports 2008 Designer 1 Workshop Lesson 2: Formatting Features

ÂŠ2008 Vision Harvest, Inc.

Inserting Graphics Adding pictures, logos or other graphics images to a report can increase the attention the report receives. Crystal Reports allows you to add graphics to any Section of the report. To insert a picture, choose Insert/Picture from the menu bar or click on the Insert Picture button on the Insert toolbar. The Open dialog box displays where you can choose the appropriate graphics file. Once you open the file, the mouse pointer appears with a shadow border the size of the graphics image you selected. Simply click in the report where you want the picture to be inserted. When the picture is in the report, you can format it, move it or resize it just like any other object.

Exercise 2.6 - Insert a Graphic 1.

Return to Design view

Scroll to the right if necessary to see the space in the Report Header to the right of the title object

Click the Insert Picture

You should be in the directory where you saved the sample class files. In the Images folder, double click the Northwind2008Logo.jpg file Crystal Reports returns you to the report. The mouse pointer has a shadow box attached that is the actual size of the graphics image.

Click in the Report Header to the right of the title to place the graphic Crystal Reports places the graphic where you click.

Save the report (Format) and preview the report. Make whatever changes are necessary

button on the Insert toolbar

The report should look like the following illustration:

Crystal Reports 2008 Designer 1 Workshop Lesson 2: Formatting Features

ÂŠ2008 Vision Harvest, Inc.

Working with the Page Commands It may be that your report needs to be landscape instead of portrait, which is the default, or you may want to change the margins. To change the layout of a page you choose File|Page Setup.

In the Page Option selection you choose the size of paper (letter, legal etc), unit of measurement and orientation (Portrait or Landscape). You can also define margins for a report. By default, Crystal sets the margins to the narrowest possible settings available on the printer. Usually, these result in margins .25â&#x20AC;? wide or less. If the Adjust automatically radio button is checked then the margins change as you change orientation and also paper size. A new feature in Crystal Reports 2008 is the ability to set margins programmatically. If not using a formula, simply type the margin based on the unit of measurement system. If you want a margin to be 1/4th inches, type .25. If using centimeters or pixels, enter the appropriate number. If using a formula, the formula must return a measurement unit referred to as a TWIP (TWentieth of an Inch Point). The number of TWIPs returned by the formula will be converted to a margin measurement based on the selected unit of measurement. Below is a helpful conversion table:

Unit of Measurement

TWIPs (Approximately)

1 Inch 1 Centimeter 1 Millimeter 1 Pixel

1440 567 56.7 Dependent on screen pixel size

Crystal Reports 2008 Designer 1 Workshop Lesson 2: Formatting Features

Exercise 2.7 – Set Margins for a Report You are going to create a report that is formatted as a letter. The narrow default margins do not look appropriate for this type of report. Therefore, you need to change them to 1” margins. 1.

Start a new report using the File/New/Blank Report menu option

Open the My Connections folder, then open the Northwind 2008 database, if necessary

Add the Employees table, and then click OK

Choose File/Page Setup…

Type 1 in the Left Margin: text box, then press TAB

Continue to type 1 in each of the other boxes, pressing TAB after each entry

Click OK when finished

Leave the report open

Crystal Reports 2008 Designer 1 Workshop Lesson 2: Formatting Features

ÂŠ2008 Vision Harvest, Inc.

Working with Text Objects You have already learned how to create a text object, but you have not yet explored the power of text objects and how flexible these objects can be in Crystal Reports. In this lesson, you are going to go into more depth, learning how to place fields inside Text Objects and how to import formatted text. You are also going to see how to format parts of a text object.

Adding Fields into a Text Object Placing fields inside text objects lets you merge database data with text that you type on a report. Crystal Reports makes it simple to create things like form letters or other text based documents with this ability. To add a field to a text object, you must first create the text object. Place it on the report in the appropriate section and make sure you are in Edit mode (flashing insertion bar in the text box). Now you can simply drag existing fields from the report into the text object. You can also insert fields from the Field Explorer dialog box. As you drag the field into the text object or point to the text object to click to insert the field, the mouse pointer drops the attached field once the mouse is inside the text object. TIP: Crystal Reports inserts a field into a text object at the location of the insertion bar. Before you click or release the mouse button, be sure that the insertion bar is located where you want the field to be inserted. You can move the insertion bar by moving the mouse. WARNING: The mouse pointer MUST be shaped like the arrow without the field visibly attached when you release the mouse button or the field is not included as part of the text object. The biggest advantage to adding fields to text objects is that Crystal Reports automatically trims the field for each value it inserts. This way there is no extra space at the end of the field when you print the report. To work with this feature, you are going to create an employment update letter that looks like the following:

Crystal Reports 2008 Designer 1 Workshop Lesson 2: Formatting Features

Exercise 2.8 - Insert a Data Field into a Text Object 1.

Click the

button. Add the text object at the left edge of the Details section

Type “Dear” in the text box, then press the SPACEBAR

LEFT-MOUSE drag the FirstName field in the Field Explorer dialog box into the new text box Be sure the mouse pointer is shaped like an arrow without a box attached.

Release the field into the text object Crystal Reports adds the field into the text object after the space.

Click into the text object, and then press the SPACEBAR

Follow the same procedure to add the LastName field Crystal Reports adds the LastName field to the text object.

Click in the text object after the LastName field and type a colon “:” Then click away from the text object to exit the text edit mode

Resize the text object to be about 6” wide

RIGHT-MOUSE click the text object and choose Format Text...

Crystal Reports 2008 Designer 1 Workshop Lesson 2: Formatting Features

10.

From the Common tab in the Format Editor check on the Can Grow option and then click OK

11.

Double click the text object to return to Edit Mode. Position the flashing insertion bar at the end of the line of text after the colon. Press ENTER twice, then type, “ Our records indicate your hire date as” then press the SPACEBAR

12.

Add the HireDate field to the text object

13.

Click in the text object, press the SPACEBAR, then type “with a current salary of”, then press the SPACEBAR

14.

Add the Salary field followed by a period and a space

15.

Click in the text object, then type “We also have the following information about your education:”, then press the SPACEBAR

16.

Add the Notes field to the text object The Notes field is a memo field containing information about the employee’s educational history.

17.

Complete the closing to the letter as you would like

18.

Save the report and name it Update.rpt. Preview the report Notice that there is no extra space between the first name and last name because Crystal Reports automatically trims the fields. You should also notice there is a problem with the longer entries.

TIP: You can import pre-formatted, text-based objects from an existing file onto your report. To do this, simply double click the text object where you want to import the text, or create a new text object, then right click on it to bring up the shortcut menu. From the shortcut menu, choose Insert from File. Select the file where you want from the open dialog box, then click the Open button. The object is imported from the file onto your report and replaces the original object.

Crystal Reports 2008 Designer 1 Workshop Lesson 2: Formatting Features

Formatting Part or All of an Object You can format an entire text object or you can apply a different format to the fields. You can use the Format Editor to make these changes, or, if the formatting choice is one located on the Format toolbar, you can use the Format toolbar buttons and drop-down lists to change how the text looks. If you want to format the entire text object, you already know how. Select it, then choose the appropriate options from the Format toolbar or RIGHT click it and choose Format Object… If you want to format only a field within the text object, you must select just the field. To do this, you must be in Edit Mode. Remember, when you are in Edit Mode, there is a flashing insertion bar in the text box and a small ruler above or below the text object. To get to Edit Mode, double click the object. You can highlight individual characters in the object to format them by dragging the mouse pointer over them just as you would highlight words in any word processing program. You can highlight a field within a text object by clicking it one time while in Edit Mode.

Exercise 2.9 - Format All and Part of an Object 1.

Return to Design view. Select the entire text object you added

Change the font to Times New Roman, 12 pt. using the Format toolbar drop-down lists Notice that all the field contents are formatted.

Double click the text object to enter Edit Mode You should see a flashing insertion bar in the field and a small ruler.

Click the HireDate field once The field becomes highlighted.

RIGHT click the highlighted field, then choose Format {Employees.HireDate}...

Scroll down in the Style list then choose March 01, 1999. Click OK

Right click the highlighted field, then choose Text Formatting… On the Font Tab, click on the Drop-down Style: list, and then choose Bold. Click OK

Deselect the text box. Save the report (Update) and preview it You should see the hire date as a text based date in bold letters.

NOTE: It is strongly recommended to return to design view when formatting a field within a text object.

Crystal Reports 2008 Designer 1 Workshop Lesson 2: Formatting Features

Inserting Special Fields Crystal Reports contains several special fields, unrelated to the fields in the database, which you can use to make the report more useful. To insert a special field, first make sure the Field Explorer is open. In the Field Explorer, click the icon beside Special Fields node to see the fields of that type. You can insert a special field using one of the same four techniques you use to insert database fields. You can also place special fields in text objects to combine text with the special field information.

Special Fields Special Field

Description

Content Locale

Displays the locale setting of the machine that your report is running on (the machine’s locale is usually found in the Control Panel’s Regional Settings) Displays the ID number of the current Business Objects Enterprise user if one exists Displays the name of the current Business Objects Enterprise user if one exists

Current CE User Id Current CE User Name Current CE User Time Zone Data Date Data Time Data Time Zone File Author File Creation Date File Path and Name Group Number Group Selection Formula Horizontal Page Number Modification Date Modification Time Page N of M Page Number Print Date Print Time Print Time Zone Record Number Record Selection Formula Report Comments Report Title

Displays the current time zone of the current Business Objects Enterprise user if there is one Prints the date when the data in the report was last refreshed Prints the time when the data in the report was last refreshed Prints the time zone in which the data was last retrieved (refreshed) Automatically prints the Author in the File / Summary Info window along with other information about the report for future reference Inserts the date the report was created Inserts the file path and name of the current report Allows the numbering of each group in the group header or group footer Inserts the Group Selection Formula used for the report in the Group Selection Formula Editor Numbers the horizontal pages created by report objects, such as cross-tabs and OLAP grids, that can expand over a number of horizontal pages Shows the date when the report was last saved based on the PC’s calendar/clock Shows the time when the report was last saved based on the PC’s clock Insert the page number with a total page count, i.e., Page 1 of 5 Automatically prints the page number on each page Takes the date from your PC’s calendar/clock and prints it on the report Takes the time from your PC’s clock and prints it on the report Prints the time zone in which the report was last printed Assigns a number to each record in the report and acts as a counter field Inserts the Record Selection Formula that was created for the report in the Record Selection Formula Editor Automatically prints any comments you make about the report in the File / Summary Info window along with other information about the report for future reference Automatically prints the report title in the File / Summary Info window along with other information about the report for future reference

Crystal Reports 2008 Designer 1 Workshop Lesson 2: Formatting Features

Special Fields Special Field Selection Locale

Description Shows the Preferred Viewing Locale (PVL) at the time of the last data refresh

Total Page Count

Calculates and prints the total pages in the report

WARNING: Using Page N of M or Total Page Count will increase the processing time of the report. NOTE: Information for the special fields: Report Title, Report Comments and File Author comes from the document Properties dialog box Summary tab. You can open this dialog box by choosing File/Summary Info.

Exercise 2.10 - Insert a Special Field in the Report 1.

Return to Design view. Choose File/Summary Info

Type Update Report in the Title text box, then click OK

Click the Field Explorer button on the Standard toolbar to open the Field Explorer if it is not already open. Click the expand icon beside Special Fields category. Insert the Report Title special field in the Report Header section

Format the Report Title special field to be bold with a 12 pt. Font

Insert the File Path and Name special field in the Page Header on the far left side

Exercise 2.11 - Insert Special Fields in Text Objects 1.

Click the Insert Text Object button, and then insert the text object in the Page Header section, upper right area

Type the text “Report Print Date:” then press the SPACEBAR. Insert the Print Date special field in the text box after the colon and space. Press ENTER. Type the text “Data Collection Date:” and then press the SPACEBAR. Insert the Data Date special field after the colon and space

Resize the text box so the text displays on two lines (make it taller and wider)

Insert the Page N of M special field in the Page Header above the File Path and Name special field

Format all objects in the Page Header to be italicized

Save the report (Update) and preview it. Make any changes necessary. Save and close the report

Crystal Reports 2008 Designer 1 Workshop Lesson 2: Formatting Features

Challenge Exercise – Formatting Features Format the Unpaid Orders.rpt report created in the challenge exercise for the second lesson. 1.

To open the Unpaid Orders report, choose File, and then select the report from the list at the bottom of the menu

Click once on the report title text object in the Report Header to select it Blue slash lines (also called the selection border) and sizing handles appear around the object.

Format the title to be centered in its text object. Add a double lined border, a drop shadow, and any border and background color you choose. Make the text bold and 14pt

Multiple Select the Field Titles in the Page Header section. Make them bold and not underlined

Make sure you have enough space under the field titles in the Page Header and draw a line under the field titles the width of the report. Format the Line with any style, width and color you choose

Insert the Northwind Traders logo in the Report Header to the right of the title object

Remove the thousand separator and decimal values from the Order ID field HINT: click the Comma button in the formatting toolbar to turn off the comma and the Decrease Decimals button to remove decimal values.

Add a fixed currency symbol to the Order Amount field

Format the Order Date, Required Date and Ship Date to display the date only with the full name of the month, two digit day and four-digit year

10.

Use guidelines to position the fields so they look good to you

11.

Use the Save As… command to save the report. Name it Formatted Unpaid Orders.rpt. Preview the report

12.

When finished, close the report

Crystal Reports 2008 Designer 1 Workshop Lesson 2: Formatting Features

ÂŠ2008 Vision Harvest, Inc.

The finished report should look similar to the following example:

Crystal Reports 2008 Designer 1 Workshop Lesson 2: Formatting Features

ÂŠ2008 Vision Harvest, Inc.

NOTES

Crystal Reports 2008 Designer 1 Workshop Lesson 3: Selecting Specific Records from the Database

ÂŠ2008 Vision Harvest, Inc.

Lesson 3 Selecting Specific Records from the Database

Crystal Reports 2008 Designer 1 Workshop Lesson 3: Selecting Specific Records from the Database

ÂŠ2008 Vision Harvest, Inc.

Lesson Objectives After completing this lesson, you will be able to: Select Specific Records Using the Select Expert You can quickly limit the records pulled from the database with the Select Expert Use Multiple Criteria to Select Records Use of multiple criteria gives you the maximum flexibility in reporting on just the data you want. Expand and Limit the Record Set Suppose you find you did not get all the data you need. Alternatively, maybe you have too much data. Crystal Reports makes it easy to limit the data further or expand the record set to include more information View the Selection Formula Begin to understand the language behind Crystal Reports. Look at and make minor modifications to the formula used to select records

Crystal Reports 2008 Designer 1 Workshop Lesson 3: Selecting Specific Records from the Database

Filter Types of the Select Expert Filtering records provides you with a way of limiting the data on which you report. For example, suppose you needed a report listing all of the USA customers for Northwind Traders. Without any data restriction, the report would contain all customers in the database rather than just those from the USA. Therefore, you need to filter the data to only report on those records meeting the condition. To filter records in Crystal Reports, you use the Select Expert. The Select Expert can be used to develop filters against the following selection types: Record - This filter type is the only type to be converted to Structured Query Language (SQL) syntax for evaluation by the database. Group - Group selection is a “Print Time” feature allowing a report to be further filtered based on subtotals or other grouping information Saved Data - A new feature in Crystal Reports, this filter type allows additional filtering against data returned by the database. Any filters of this type do not cause a database refresh. The Saved Data filter is used in conjunction with parameters to facilitate additional local user interaction without needing a live data connection

Database Filtering with the Select Expert To use the Select Expert you specify the field containing the data on which you want to select, choose an operator to limit the data in the way you want, then type or select the criteria defining the record set. The Select Expert makes all this easy by providing drop-down lists from which you can make choices. To open the Select Expert, choose the Select Expert/Record… command from the Report menu or click the Select Expert button in the Experts toolbar and from the droplist choose Record.

Crystal Reports 2008 Designer 1 Workshop Lesson 3: Selecting Specific Records from the Database

If you did not have any fields selected in the report when you opened the Select Expert, the Choose Field dialog box is displayed. From here, you can choose the field containing the data on which you want to filter. For class, you want to display only customers from the USA. This information is contained in the Country field. Once you select the field, click OK and the Select Expert displays:

You use the Select Expert dialog box to apply certain conditions to the data field you just chose. You could choose to include only customers from certain states. On the other hand, perhaps you are working with revenue figures and only want the report to show top income earning individuals on the sales force. The Select Expert is the tool to accomplish this. The Select Expert dialog box initially displays the “is any value” operator. The available operators and their explanations are listed in the following table: For the opposite of any operator choose the “is not…” option. This allows you to apply the negative of any criteria you choose.

Select Expert Operators Operator

Definition

any value

Any value indicates no conditions are applied to the data in this field. All records display in the data field You can choose a certain value and only those data elements matching that value exactly display in the field If you want to see records with one of several values, choose this operator. The Select Expert then provides you with a list in which you can add the values you want the records to match If you want to see all records with a value less than a certain number, use this operator. For example, suppose you want all sales less than $50,000. Choosing this option displays all values of $49,999.99 and less Same as the above except includes the stated value. For example, suppose you want all sales less than or equal to $50,000. Choosing this option displays all values of $50,000.00 and less If you want to see only records with a value more than a certain number, this operator is helpful. For example, suppose you want to view all records representing home health nursing visits of more than 50. Choosing this operator displays all records with values of 51 or more Same as the above except includes the stated value. For example, suppose you want to view all records representing home health nursing visits of 50 or more. Choosing this operator displays all records with values equal to 50 or more than 50

equal to one of less than

less than or equal to greater than

greater than or equal to

Crystal Reports 2008 Designer 1 Workshop Lesson 3: Selecting Specific Records from the Database

Select Expert Operators Operator

Definition

between

This operator selects records falling between certain values. For example, call center agent talk time which falls between 2 minutes and 6 minutes, or hourly wages between $4.75 and $10.00 This option allows you to select only records beginning with a certain character or set of characters. For example, you could select only regions starting with “C”. You would get California, Colorado, and Connecticut DOS characters like ‘?’ or ‘*’ can be used with this operator to select records with similar, but not identical values. You may enter ‘D*’ to display reports to include Denver, Dallas, Des Moines and Düsseldorf For Date or Date/Time fields, you can use this operator to determine if the date falls within a specified date range. For example, you can check to see if the date is in the current year-todate or in the last full month, etc This powerful option allows you to create selection statements that are more complex than the simple comparison operators allow

starts with

in the period

Formula

NOTE: Available operators are specific to the type of field selected to filter on. For example, the pull down menus for numeric or currency fields will only display numeric operators. Some of the operators such as Equal To also provide a negative option: Not Equal To or Does Not Start With. After you select an operator, the Select Expert opens an additional text box to allow you to select or type the criteria for the field. This drop-down list only displays the first 500 values in the database. Therefore, the value you want may NOT be in the list. If not, you can type any value into the text box. The Select Expert selects records correctly whether the criteria is typed or selected.

Exercise 3.0 - Select Specific Records 1.

Open the Format.rpt report. Click the Design tab to return to Design view Make sure there are no objects selected on the report

To restrict the data to see only those customers from the United States you need to select on the Country field. Click the Select Expert button on the toolbar and choose the Record option The Choose Field dialog box displays.

Scroll down and find the Country field. Click it one time to select it, then click OK The Select Expert opens with the Country tab.

Drop down the operators list and select is equal to

Drop down the blank criteria list and select USA, then click OK

Using the Save As… command, save this report and name it USA Customer Sales.rpt You may see a dialog box prompting you the saved data is incomplete. This box is asking if you want to refresh the data in the report before you save. Since generating or refreshing the data is a possible place to get program errors, you should always answer NO to this prompt. Save the report design first, and then generate the data when you preview the report.

Crystal Reports 2008 Designer 1 Workshop Lesson 3: Selecting Specific Records from the Database

ÂŠ2008 Vision Harvest, Inc.

Click No

Preview the report You should now only see 13 records in the report (there were 86 before) and the Region field displays two digit state initials. The Preview View should look similar to the following:

TIP: You can open the Select Expert with the field you want for selection already displayed. To do this, RIGHT click on the field you want to use for selection, choose Select Expert Recordâ&#x20AC;Ś from the short-cut menu. Crystal Reports creates a tab for the selected field, retaining any select options you have already set.

Crystal Reports 2008 Designer 1 Workshop Lesson 3: Selecting Specific Records from the Database

Selecting Records with Multiple Criteria You may need to set more than one criteria to select just the records you want. You can accomplish this quickly with the Select Expert. Once the Select Expert dialog box is open, you can click the New tab or the New button to add a new tab with the additional field you need. Once the Select Expert adds the tab, choose the operator and criteria just as you did on the first tab. If you discover you need to change the criteria you have set for certain data fields, you can easily do it using the Select Expert. Open the Select Expert. If a data field was previously used for selection or if you had a field selected in the report, it will show up on a Select Expert tab. You can then modify the operator or criteria. To delete a field from the selection statement, click its tab, and then click the Delete button. Suppose you want to see all the records for the Customers in the USA that have more than $100,000 in sales last year. You can add the additional criteria easily with the Select Expert.

Exercise 3.1 - Add Criteria with the Select Expert 1.

Return to Design view if necessary, and then select the LastYearsSales field

RIGHT click the LastYearsSales field The shortcut menu opens.

Choose Select Expert Record… The Select Expert dialog box displays with the Last Year’s Sales field on its own tab for selection criteria to be entered.

Drop-down the operator list and choose is greater than Notice there is also an operator for is greater than or equal to. If you want to include the starting value, choose this operator.

Type 10000 in the criteria text box

Click OK

Save (USA Customer Sales) and preview the report

Crystal Reports 2008 Designer 1 Workshop Lesson 3: Selecting Specific Records from the Database

ÂŠ2008 Vision Harvest, Inc.

Crystal prompts you to make a choice between using the saved data and refreshing the data. If the data you want is completely contained within the saved record set, you can use the saved data. Otherwise, you have to refresh the data. If you are not sure, choose to refresh.

Since you currently have all customers in the USA and you want only those USA customers with sales greater than $10,000, you can use the saved data. Click Used Saved Data . The report should display only three records.

Viewing and Editing the Select Formula Suppose now you want to view all the records for customers in the USA or customers with sales greater than $10,000. The statement you just created in the Select Expert displays records for all USA customers and with a Last Yearâ&#x20AC;&#x2122;s Sales greater than $10,000. The two statements give substantially different results in terms of the number of records displayed. With an and statement, a record must meet both criteria to be selected. An and statement connecting multiple criteria always gives the same number or fewer records than a single criteria. You can see this very easily within the and statement you just added to the selection criteria. With an or statement, a record can meet either criteria to be selected. An or statement always displays the same number or more records than a single criteria. You can view the select statement and make edits to it by clicking the Show Formula button in the Select Expert.

Crystal Reports 2008 Designer 1 Workshop Lesson 3: Selecting Specific Records from the Database

To change the current criteria from an “and” statement to an “or” statement, you must edit the formula. When you are finished, Crystal Reports must refresh the data because it will be selecting more records than are currently in the data set. You will end up with more records than you currently have in the report.

Exercise 3.2 - View and Edit the Record Selection 1.

From Preview view, click the The Select Expert opens.

Click the Show Formula button

In the formula, delete the word “and” and replace it with “or”

Click the Hide Formula button Notice that the Select Expert is now grayed out a Composite Expression message appears. The Select Expert is not able to process “or” statements due to the possible complexity of the filter logic. “Or” statements are allowed, but not processed by the Select Expert.

Click OK Crystal Reports prompts asking whether you want to refresh the data or use existing data. You must refresh to get the additional records you want.

Click the Refresh Data button Look in the Status Bar. You should now have 28 records in the report. If you look through the list of customer in the report, you see customers NOT in the USA. However, they should have sales greater than $10,000.

button and choose the Report option

Crystal Reports 2008 Designer 1 Workshop Lesson 3: Selecting Specific Records from the Database

Exercise 3.3 - Delete Selection Criteria 1.

From the Experts toolbar, click the Select Expert

Delete the first line in the formula and the word “or” This removes the filtering based on the LastYearsSales.

Click the Hide Formula button The Select Expert is reactivated. The LastYearsSales tab no longer has the value 10000 and the option reverts to “is any value”.

Make sure the LastYearsSales tab is the top tab, click the Delete button, and then click OK

Click the Used Saved Data button

Save the report (USA Customer Sales) Now you should be back at 13 records.

and choose the Record option

Crystal Reports 2008 Designer 1 Workshop Lesson 3: Selecting Specific Records from the Database

ÂŠ2008 Vision Harvest, Inc.

Case Sensitive vs. Case Insensitive In Crystal Reports, record selection is automatically case insensitive so any refresh of data sees character matches, regardless of capitalization, and updates them. If you have a customer contact name listed as Senator Snooze in one record and senator Snooze in a newer record, both records will be found. This option is found under the File/Options dialog box, Database tab. To make Crystal Case Sensitive, click the box labeled Database Server is Case Insensitive to remove the check mark. This changes the setting for all future reports if you want to change it for existing reports then select File/Report Options under the File Menu.

Crystal Reports 2008 Designer 1 Workshop Lesson 3: Selecting Specific Records from the Database

ÂŠ2008 Vision Harvest, Inc.

Record Selection Formula Templates Another way to create a record selection is with a formula. To create a Record Selection Formula, open the Report menu and choose Edit Selection Formula, then Record. This opens the Record Selection Formula Editor dialog box. Record Selection Formulas can be entered using the full range of report fields, operators, and functions. Just like the Select Expert, the record selection formula limits the data displayed in the report. In fact, when you work with the mouse, pointing and clicking in the Select Expert, you are actually creating a record selection formula. You have even seen it by clicking the Show Formula button. The Crystal Reports User Guide and Online Help file contains Record Selection Formula Templates to be used as a guide for creating your own formulas. The templates are meant to be examples of what could be done, not necessarily the best selections. You can find these templates by opening the Crystal Reports Report Help program from the Help menu or by pressing F1. Expand the Record Selection option, then expand Using formula templates, then expand Record Selection Formula templates. You can select and copy any formula from these help screens into your selection formula using the standard Windows copy and paste commands. NOTE: Formulas are covered in detail later in the course.

Crystal Reports 2008 Designer 1 Workshop Lesson 3: Selecting Specific Records from the Database

ÂŠ2008 Vision Harvest, Inc.

Challenge Exercise â&#x20AC;&#x201C; Selecting Specific Records To work with record selection, you are going to use the Challenge Exercise report, Formatted Unpaid Orders.rpt, from the last lesson. Currently the report lists all orders; you are going to filter the data to list only the unpaid orders. Each time you change the selection criteria, you should try to decide on your own if you have to refresh the data or not. If you are not sure, answers to the question are found on the page following this exercise. TIP: You can open the Select Expert right from Preview view. You do not have to return to Design view to select records. Look for the toolbar button or RIGHT click the field you want to use for selection. 1.

Open the Formatted Unpaid Orders report. Click the Design tab.

Select the OrderAmount field and open the Select Expert The OrderAmount field is a Currency field and most operators are available

Choose is greater than or equal to from the operators list, and then type the value $1000.00. Click OK and Preview the report. Do you have to refresh the data?

Change the selection to view all orders for less than $1000 Do you have to refresh the data? In theory, yes. However, you will get data returned because Crystal has not yet thrown away the filtered records. It is always best assume the records are not there and refresh when you think you should get additional records.

Modify the selection criteria to view all records with an order amount over $1,000 and where the order was placed on or after 01/01/2008. Do you need to refresh the data? The record selection formula should look like: {Orders.OrderAmount} >= $1000.00 and {Orders.OrderDate} >= DateTime (2008, 01, 01, 00, 00, 00)

Change the selection criteria to see all records for order amount over $1,000 or placed on or after 01/01/2008. Do you need to refresh the data? The record selection formula should look like: {Orders.OrderAmount} >= $1000.00 or {Orders.OrderDate} >= DateTime (2008, 01, 01, 00, 00, 00) 6.

Reset the selection criteria to view all orders $1,000 or greater. Remove the criteria for the OrderDate. Do you need to refresh the data?

Save the report as Selected Orders Over 1000.rpt and close the report

Crystal Reports 2008 Designer 1 Workshop Lesson 3: Selecting Specific Records from the Database

ÂŠ2008 Vision Harvest, Inc.

Helpful Hints for the Previous Exercise For step 3, you do not have to refresh the data. All records from the database are currently included in the report. You can select only those orders over $1,000 from the saved data. When you complete this step and preview the report, you should have 419 records. For step 4, you must refresh. You are asking for records that are not currently in the data set. The previewed report should contain 411 records. For step 5, you should refresh the saved data. This is because of the selection to the OrderAmount field and not the OrderDate .When you preview, you should see 143 records. For step 6, you do not need to refresh the data. At the end of this step, the report should have 411 records.

Crystal Reports 2008 Designer 1 Workshop Lesson 3: Selecting Specific Records from the Database

ÂŠ2008 Vision Harvest, Inc.

Challenge Exercise â&#x20AC;&#x201C; Selecting a Range of Values In this exercise, you will create a listing of orders. You only want to see orders for the first quarter of 2008. 1.

Start a new report using the As a Blank Report option button

Open the My Connections folder, open the Northwind 2008 database if necessary

Add the Orders table, and then click OK

Add the following fields to the Details section: OrderID, OrderAmount, and OrderDate

Add a Text Object to the Report Header with the title Orders for 1st Quarter 2008, format the title any way you like

Format the OrderID to display without the thousand separator and no decimals, the OrderAmount field to have a fixed currency symbol, and the OrderDate to display like 03/01/1999

With the OrderDate field selected, click the Select Expert button and choose the Record option Notice that the Select Expert opens with a tab for the Order Date field already in place.

Drop down the operator list and select the is between operator Notice that Crystal provides you with two criteria boxes for the beginning and the end of the range.

Drop down the top criteria list and choose 1/1/2008 12:00:00AM

10.

Drop down the lower criteria list and choose 3/31/2008 11:59:59PM All of the times in the criteria list will show as 12:00:00 AM. You will need to edit it to read 11:59:59PM.

With this database, making a time distinction is not important since all times are the same. However, it is good practice to ensure all records are collected if the times are different. Also, Crystal is inclusive of the values, meaning the beginning date/time and ending date/time are included. 11.

Save the report and name it Orders for First Quarter 2008.rpt

12.

Preview the report. You should see 182 records.

13.

Save the report and close it

Crystal Reports 2008 Designer 1 Workshop Lesson 3: Selecting Specific Records from the Database

NOTES

ÂŠ2008 Vision Harvest, Inc.

Crystal Reports 2008 Designer 1 Workshop Lesson 4: Grouping and Sorting Data

ÂŠ2008 Vision Harvest, Inc.

Lesson 4 Grouping and Sorting Data

Crystal Reports 2008 Designer 1 Workshop Lesson 4: Grouping and Sorting Data

ÂŠ2008 Vision Harvest, Inc.

Lesson Objectives After completing this lesson, you will be able to: Decide when and why to use Groups Learn to make intelligent decisions about grouping and summarizing Create a Group You can easily create and define groups with Crystal Reports. Grouping helps you provide well organized, easy to read reports Sort records within a Group Arrange records within a group in ascending or descending order Navigate the Report Using the Group Tree Quickly go to any portion of the report using the Group Tree Group on Dates and Times Crystal lets you define how groups on Date fields are subdivided. Learn to group by day, week, month, quarter, year or other subdivisions Summarize Groups With the Summary, Subtotal, and Grand Total features perform a number of different calculations on the groups in a report. Your reports can provide information at a glance in a professional format Calculate Percentages Learn how to calculate a percentage of one group within a larger group, or the percentage of the grand total each group contributes towards Specify the Order of Groups Using the Group Sort Expert Arrange groups dynamically based on summary information for the group. Design reports quickly showing the highest or lowest group based on a significant indicator

Crystal Reports 2008 Designer 1 Workshop Lesson 4: Grouping and Sorting Data

When and Why to Group Records Once you have selected the fields and records for the report, it is time to consider whether you want to group the records. A good method for determining if you should group, and the fields on which to group, is to listen for the word “by” in the business requirement statements. For example, suppose the individual requesting the report says, “I want to see records by product and then broken down by order date, quarterly.” This statement identifies products as the first group, and order date as the sub-group. Order date will also be conditionally grouped within each business quarter rather than by individual dates. By grouping records, you will be able to perform summary operations, such as “How many products were sold…” or “total sales per quarter…”. If you have summary requirements for the report, you have another good indicator you are going to have to perform grouping. Crystal Reports contains many grouping, sorting and summarizing options allowing you to create and arrange groups according to your specific needs. You have the ability to sort groups in ascending or descending order, specify your own order, or keep records in their original order in the database. You can sort records within the group as well. Crystal Reports contains several summary functions for totaling, averaging, and performing statistical analyses on groups. Summarized fields can be displayed according to their operation or as a calculated percentage. In addition, Crystal Reports has the Group Sort Expert allowing you to group records, then organize the groups based on the highest or lowest values in the summarized field(s).

Creating a Group To create a group, begin with the Insert/Group… command or click the Insert Group the Insert Tools Toolbar.

button on

The Insert Group dialog box lets you choose the field on which you want to group and define sort and other options.

Crystal Reports 2008 Designer 1 Workshop Lesson 4: Grouping and Sorting Data

ÂŠ2008 Vision Harvest, Inc.

Group and Sort Direction There are five sort choices used to determine the direction for the group.

Group Sort Choices Term

Definition

in ascending order

Alphabetic characters are expressed from A-Z. Numeric characters are displayed 1-9 or lowest to highest. Dates are organized oldest to most recent Alpha characters are sorted from Z to A, and numbers show 9-1 or highest to lowest. Dates are organized most recent to oldest The records display just as they are ordered in the original database You define the order in which the groups appear. This choice also allows you to define groupings with other names besides the field values and to combine sets of values into a single group Allows the user to determine at run time to have the groups sorted in ascending, descending or original order. Specified order is not available for this feature

in descending order in original order in specified order

Using a formula for group sorting

Customize Group Name Field Under options you can choose to customize your group name when you create or modify your group. When a group is inserted into a report, the group name is displayed in the group tree and in the group header. By default, the group name field shows the value from the database. However, you may not want to display the group name or you may want to display it differently.

Crystal Reports 2008 Designer 1 Workshop Lesson 4: Grouping and Sorting Data

ÂŠ2008 Vision Harvest, Inc.

There are two choices to customize a group name: Choose From Existing Field Use a Formula as Group Name For example, you could group on the CustomerID, but display the CompanyName as the group name. Alternatively, you could group on the CustomerID, but use a formula to display both the CustomerID and the CompanyName in the group name. WARNING: You cannot include a formula automatically evaluated in the WhilePrintingRecords pass. For example, you cannot include a running total field, summary field or another group name field. This concept is explained in detail in the Designer 2 Workshop.

In addition to sort and group name options, there are also three check boxes for the following features: Keep group together assures page breaks do not separate groups WARNING: Only use this option if the groups are small. If you turn on this feature and the first group in the report is longer than one page, Crystal Reports moves the entire group to the next page. The first page of the report is blank.

TIP: Blank first pages can be prevented by making sure the option Respect Keep Group Together On First Page is unchecked. Go to File>Report Options to find this option. New Page After provides the ability to set a page break after a certain number of groups have displayed For large groups, you may want the group header to print at the top of each new page to identify the group. The Repeat group header on each new page feature does enables this automatically

Crystal Reports 2008 Designer 1 Workshop Lesson 4: Grouping and Sorting Data

ÂŠ2008 Vision Harvest, Inc.

Once a group is created, Crystal Reports adds two sections to the report, a Group Header section and a Group Footer section. Objects in these sections print once at the beginning and end of each new group on the report. In addition, by default, Crystal Reports automatically adds the group name in the group header. This feature may be turned on or off in the File Options dialog box, Layout tab.

Another way to create and modify groups is using the Group Expert. The Group Expert button can be found on the Experts Toolbar. The group expert enables you to create a number of groups at one time and easily change group order.

Crystal Reports 2008 Designer 1 Workshop Lesson 4: Grouping and Sorting Data

ÂŠ2008 Vision Harvest, Inc.

In the screen shot above you will notice the Country field has been selected as a group. To select a group, simply highlight the field in the available field column and click the arrow to add the field as a group. To change the group order, use the up and down arrows in the top right corner. To remove a group, simply click the remove arrow. Clicking on the options for the selected group will take you to the Group options tab as detailed above.

Exercise 4.0 - Create a Group 1.

Open the Format.rpt report

Edit the title to read Customer Sales by Country and Region

You are going to insert a group on the Country field. Return to Design view so you can see the changes grouping makes to the design of the report

Click the Insert Group

Click the drop-down arrow on the top list box. Select the Country field This step assumes you are using the Insert Group option. Leave all of the other options set to their defaults.

Click OK Notice that Crystal Reports adds the Group Header #1 and Group Footer #1 to the report. Also, note the field Group #1 Name added to the Group Header.

Using the Save As command save this report as Grouping.rpt If you see the Saved data is incomplete prompt, click No.

Preview the report

button or use the Group Expert

The grouped report should look like the following example:

on the Experts toolbar

Crystal Reports 2008 Designer 1 Workshop Lesson 4: Grouping and Sorting Data

Modifying Groups Suppose the report definition requires the Countries to be listed in descending order. To edit a group, RIGHT click in the gray area of the Group Header #1 section, then choose Change Group… from the shortcut menu.

Exercise 4.1 - Edit a Group 1.

If Crystal Reports is setup to see the Section Names in Preview, you can do this right from the Preview view. If not, return to Design view. RIGHT click the Group Header #1 section heading The Group shortcut menu opens.

Choose Change Group…

From the Group Common dialog box, change the sort direction from ascending to descending

Click OK

Save (Grouping) and preview the Report If you see the Saved data is incomplete prompt, click No.

Creating Multiple Groups in a Report Now that you know how to create a group, you should know Crystal lets you create as many groups as you want in the report. Crystal creates groups so they are nested inside one another. That is, each successive group is completely contained inside the previous group. Crystal numbers the Group Header and Footer sections to distinguish between the groups.

TIP: To see which group number is which field, you can point to the Group Header or the Group Footer. A tool tip appears indicating the table and field name for this group. You also see –A or –D or –S or –O to indicate the sort order of the group.

Crystal Reports 2008 Designer 1 Workshop Lesson 4: Grouping and Sorting Data

Exercise 4.2 – Add Another Group to Create Multiple Level Grouping 1.

You are going to group on the Region field, but first you want to reorder the groups in ascending order. Just click the Undo button to undo the last command

Return to Design view, and then click the Insert Group

Drop down the top list and choose the Region field, then click OK

Since you are grouping on Region, you no longer need to see the field in the Details section. Select the Region field and then press DELETE to delete the item. Also, remove the guideline used by the field Notice that the column heading is deleted as well.

Move the Last Year’s Sales field and its title to the left to position it after the City field

Move the shaded box to the left so it surrounds the Last Year’s Sales field again Remember that you must click on the border of the box to move it.

Resize the Line object in the Page Header section to be flush with the right edge of the shaded box

Save (Grouping) and preview the report. It should look as follows:

button

Crystal Reports 2008 Designer 1 Workshop Lesson 4: Grouping and Sorting Data

ÂŠ2008 Vision Harvest, Inc.

Using the Preview Panel A new feature in Crystal Reports 2008 is the inclusion of the Preview Panel. This panel provides user interaction while the report is in Preview mode using slider navigation methods that are similar to that found in Microsoft Outlook 2007. The primary interactive tools available are: Group Tree - This tool allows a navigation tree to find the location of a group in the report. This tool is described in further detail below Parameter Panel - This panel allows users to format and filter reports using parameter objects. Use of this panel is described in detail in the Designer 2 Workshop Find Tab - This tab offers advanced search capabilities against data contained in the report. All fields are searched for possible matches and searches can match case, or not. If searching against numeric or currency values, you must type your entry to match the formatted value. For example, if a currency field displays as $1,234.00 you must enter $1,234 to match the first four numbers. Typing 1234 will not find a match

Using the Group Tree to Navigate the Report The easiest way to navigate a report is by using the Group Tree â&#x20AC;&#x201C; Smart Navigation feature. The Group Tree appears at the left of the screen in the Preview window in the Preview Panel. It works like a directory tree, showing a heading for each group in the report. Just like Windows Explorer, if a group contains another group, an expand icon appears before the group. You can click the to expand the group and show the subgroups. The changes to a when a group is expanded. When you see the group you want to view in the report, click the group name in the Group Tree. Crystal Reports displays the chosen group. This option makes moving around a large report, quick and simple. TIP: To turn the Preview Panel on and off, click the Toggle Preview Panel dard toolbar.

button in the Stan-

Reordering Groups Crystal Reports makes it possible to have sophisticated reports with nested groups. For example, you might want to group by Country, then by Region within the Country, then by City within the Region. Crystal Reports nests the groups as you create them. So, if you create the Country group, it becomes Group #1. If you create the City group next, it becomes Group #2, etc. If you make a mistake or change your mind about group order, you can change the order of the groups easily from Design view.

Crystal Reports 2008 Designer 1 Workshop Lesson 4: Grouping and Sorting Data

ÂŠ2008 Vision Harvest, Inc.

To change the group order, just click the Group Header or Group Footer section name in Design view and drag the group to the new location. The mouse pointer looks like a grabber hand as you drag. The Group Expert is also a place to reorder groups and may be easier. You simply use the arrow keys to move the groups into the order you would like as shown below.

Sorting Records within a Group Once you have created groups on the report, you may want to order the records in the group in a meaningful sequence. If you do not specify a sort order, Crystal Reports displays the records in the order in which they appear in the database. Using the Record Sort Order dialog box, you can list the records in ascending or descending order on a specific field. There are two ways to open the Record Sort Order dialog box: click the Sort Order button on the Experts Toolbar, or choose Report/Record Sort Expert from the menu bar.

Crystal Reports 2008 Designer 1 Workshop Lesson 4: Grouping and Sorting Data

ÂŠ2008 Vision Harvest, Inc.

button. The order in which you To sort on a field, select it in the left list, and then click the add fields is the order in which they will be sorted. If you change your mind, simply highlight the fields you donâ&#x20AC;&#x2122;t want in the right list, and click the

button.

Once a field is added to the right list, define its sort order by clicking the Ascending or Descending radio button with the field highlighted. You may choose ascending order for one field and descending order for the other.

Exercise 4.3 - Sort Records within a Group NOTE: You can complete this exercise from either the Design view or the Preview view. 1.

Click the Sort Order

Select the City field in the left list

Click the

Make sure the Ascending radio button is toggled on

Click OK

Save the report (Grouping) and preview the report The customers are now in alphabetical order by city within each region.

Suppose you want to quickly view the USA group. Click the USA group name in the Group Tree Crystal Reports immediately takes you to the beginning of the group.

button

TIP: If you like the Group Tree, but it takes up too much space on the screen, you can drag the bar that separates the Preview Panel area from the report. Point to the bar that divides the two areas. The mouse pointer changes to a double line with a double-headed arrow. Drag to the left to make the Preview Panel area smaller.

Crystal Reports 2008 Designer 1 Workshop Lesson 4: Grouping and Sorting Data

Using the Sort Control Starting in the Crystal Reports 2008 version, it is now possible to add sorting controls to the report so that users can resort groupings and sorted fields with a simple click of the mouse. To add a sort control, you must open the Sort Control dialog. To open this dialog simply select Insert/Sort Control.

A separate control can be added for each grouping and/or record sort field previously defined in the report. Selecting and item in the list and choosing OK will create an object on the report similar to a Text box. Just type some meaningful text, go to preview and begin using the control.

Exercise 4.4 - Sort Records using the Sort Control 1.

From Design view select Insert/Sort Control... to open the Sort Control dialog

Select the A - Customers.City item in the list and then click OK Your mouse changes to a “cross-hair” image similar to when inserting a text box

Place the cross-hair mouse in the Page Header area directly above the City field header and click A text box appears in the edit mode.

Type City in the Sort Control and then click away to close the object

Delete the original City field header and move the City Sort Control into it’s place

Use the Format Painter to apply one of the other field header’s formatting. Apply any resizing necessary

Save (Grouping) and Preview the report

Try changing the City sort from ascending to descending and then back again

Crystal Reports 2008 Designer 1 Workshop Lesson 4: Grouping and Sorting Data

Summarizing Groups One of the primary reasons for using groups on the report is to summarize data by the group. After you have grouped and sorted records, summaries provide calculated information on fields in the group. Summary information displays in the Group Footer Section and can perform a number of different calculations. Summaries can be performed on any type of data, but calculations on string data are limited to counting the records and selecting the frequency of the records. On numeric data, summaries can perform sums, averages, minimums, maximums, counts or statistical analysis. Crystal Reports is intelligent and only gives choices appropriate for the type of field you are summarizing. To summarize a field, choose one of these three options after you have grouped the data in the report. Highlight the field you wish to summarize, then: Click the Insert Summary

button on the Insert Toolbar

RIGHT click the field and choose Insert/Summary… from the submenu Choose Insert/Summary… from the menu bar

The Insert Summary dialog box lets you select a type of calculation and indicate the field on which the records are grouped. Notice even though the field you chose is selected, you have the option of selecting another field to summarize. Crystal Reports performs the summary on the selected field. WARNING: Summaries are based on groups. If you choose the Insert Summary… command with no groups in the report, Crystal Reports allows you to choose a field on which to group and inserts the group automatically or to simply insert a Grand Total rather than a group summary. 98

Crystal Reports 2008 Designer 1 Workshop Lesson 4: Grouping and Sorting Data

ÂŠ2008 Vision Harvest, Inc.

Summary Operators Operator Minimum Maximum Count Distinct count Nth largest, N is: Nth smallest, N is: Mode Nth most frequent, N is:

Sum Average Sample variance Sample standard deviation Population variance Population standard deviation Correlation with Covariance with Weighted average with Median Pth percentile, P is:

Description Text Operators Calculates the lowest value in each group Calculates the highest value in each group Counts all the values in each group Counts the number of distinct (unique) values in each group Lets you specify a number N, then calculates the Nth largest value in the group Lets you specify a number N, then calculates the Nth smallest value in the group Identifies the most frequently occurring value Lets you specify a number N, then calculates the Nth most frequently occurring value in the group Numeric Operators Sums the values in each group Averages the values in each group Calculates the square of the sample standard deviation A statistical test of how the values in a set deviate from the mean or average value for that set Calculates the square of the population standard deviation A statistical test of how the values in an entire population deviate from the mean or average value for that population Calculates the correlation of this field with another field in the report or database Calculates the amount of variance between this field and another field in the report or database Calculates an average weighted by the number of times a value occurs in the group Displays the value of the middle record in the group Lets you specify a percent, then calculates the value that is that percentile of the values in the group

Crystal Reports 2008 Designer 1 Workshop Lesson 4: Grouping and Sorting Data

Additional Summary Options Each summary has to be created individually. Additional options on the Summary dialog are: Summary location – This is an important area for determining which grouping a summary will be created for or for creating grand totals. Use the drop-down list to specify what section the summary/grand total is for Add to all group levels - Checking on this option will duplicate the summary objects for all group levels as well as the Report Footer section Show as a percentage of - This option can be used to calculate the percentage total of one group within a larger grouping. For example, the report you are using in class can show the percentage of last years sales for each country compared to the total for all countries. Once the option is checked, you can choose the total or group on which you want the comparison to be based Insert across hierarchy - If a report contains hierarchical groupings, this option inserts an identical summary for all sub-groups within the primary hierarchical group

Exercise 4.5 - Insert a Summary to Sum Last Years Sales for the Country 1.

Click the Design tab

Select the Last Years Sales field in the Detail section

Click the Insert Summary button Or RIGHT click the field and choose Insert/Summary… Or Choose Insert/Summary… from the menu bar.

Click the Calculate this summary: drop-down arrow Notice all the summary options are available since this is a number field.

Select Sum

In the drop-down list in the Summary Location area, select Group #1: Customers. Country - A

Click OK Notice the field appearing in Group Footer #1.

Repeat steps 2 through 6 for both Group #2 and for the Grand Total…

Save the report (Grouping) and preview the report

100

Crystal Reports 2008 Designer 1 Workshop Lesson 4: Grouping and Sorting Data

ÂŠ2008 Vision Harvest, Inc.

The Summaries and Grand Total fields in Design view looks like the following illustration:

The Grand Total Report in Preview view (last page) looks like the following illustration:

WARNING: If you choose to insert a Grand Total by RIGHT clicking, be sure you RIGHT click on the field in the Detail section, NOT on the summary field in the Group Footer.

101

Crystal Reports 2008 Designer 1 Workshop Lesson 4: Grouping and Sorting Data

ÂŠ2008 Vision Harvest, Inc.

Exercise 4.6 - Format the Totaled Report 1.

Return to Design view. Add a text object in the Group Footer #1 section to the left of the Last Years Sales summary field

Type Total Sales for the Country: in the text box. Format the text object to be bold and right aligned. Resize as necessary

For the region total, copy the Total Sales for the Country: text object to Group Footer #2. Select the object, click the Copy button. Click the Paste button

Click in Group Footer #2 to the left of the summary field to place the object in the section

Double click the pasted object to enter Edit Mode and change the text to Total Sales for the Region:

Click the Paste button again, then click in the Report Footer to the left of the Grand Total field

Edit the pasted object and change the text to Grand Total Sales:

Save the report (Grouping) and preview the report. When finished, close the report

Grouping Data in Date/Time Intervals One common variation on groups is the need to group on a Date/Time field. Suppose you were given the business requirements for a new report detailing the Monthly Orders for 2007. You would need to group this report on the OrderDate field. When a Date/Time field is selected in the Insert Group dialog box, an additional drop-down list appears called The section will be printed:. The default setting calls for the group to be divided on each unique value in the field. However, you can choose to divide the group by month, by day, by quarter, by year, etc.

Exercise 4.7 â&#x20AC;&#x201C; Create Group on Date/Time Field 1.

Start a new report using the As a Blank Report option button

Open the My Connections folder, and then open the Northwind 2008 database, if necessary

Add the Orders table, then click OK

Add the following fields to the Details section: OrderID, OrderDate and OrderAmount

Select the OrderDate field and click the Select Expert

102

button and choose Record

Crystal Reports 2008 Designer 1 Workshop Lesson 4: Grouping and Sorting Data

Drop down the operator list and select the is between operator

Drop down the top criteria list and choose 1/1/2007 12:00:00AM

Drop down the lower criteria list and choose 12/31/2007 12:00:00AM, then click OK.

Click the Insert Group

10.

Click the drop-down arrow on the top list box. Select the Order Date field, if necessary The sort direction should be Ascending.

11.

Click the drop-down on the third list box and select for each month. Click OK

12.

Add a Text Object in the Report Header with the text Monthly Orders for 2007 Format the title to show a single line border and a drop shadow. Make the text bold, 16pt. font, and centered

13.

Save the report as Monthly Orders for 2007.rpt. Preview the report Notice the value in the Group Name Field and in the Group Tree.

button

Once you have inserted a group, Crystal adds a group name object for the group. The object contains the field value from the database. This group name field can be formatted like any other object in the report.

Exercise 4.8 – Format the Group Name Field 1.

Return to Design view by clicking on the Design tab

RIGHT click the Group #1 Name field in the Group #1 section, choose Format Field… from the shortcut menu The Format Editor opens on the Date/Time tab.

Since the title states Monthly Orders for 2007, the Group Name field only needs to display the full name of the month. There is not a default style for the full name of the month so you will need to create one. Click the

button

Click the Order: drop-down arrow and choose Date This step is optional since the time value doesn’t display anyway.

Click the Date tab. Drop down the Month: list and choose March. Choose None for the day and the year Notice the sample box at the bottom of the Format Editor displays the month name only.

Click OK to close the Custom Style dialog box. Click OK again to close the Format Editor

Make the Group Header Section taller. Multiple select the field titles, then move them from the Page Header to the bottom of Group Header #1

Save the report (Monthly Orders for 2007) and preview the Report. It should look as follows:

103

Crystal Reports 2008 Designer 1 Workshop Lesson 4: Grouping and Sorting Data

Calculating Percentages You can calculate a group’s percentage of a larger group. One example would be to calculate the percentage of LastYearsSales for each customer based on the country. Percentages can also be calculated for Grand Totals, such as the percentage each country contributed towards all sales. To calculate a percentage, first select the field to be summarized and click the Insert Summary button. The first section of the Insert Summary dialog box is completed as for any other summary. To calculate the percentage, click the check box for Show as a percentage of command. After you toggle the check box to have a check mark, you can choose the total or group on which you want the comparison to be based. NOTE: The calculating percentages option is only available when the summary operation returns a numeric result.

Exercise 4.9 – Calculate a Percentage 1.

Return to Design view of the Monthly Orders for 2007 report

RIGHT click the Order Amount field in the Details section, choose Insert/Summary… from the shortcut menu

The calculation is already set to sum the Order Amount field. The default Summary location is set to Grand Total…, change the selection to Group #1…

Toggle the check mark on for the Show as a percentage of command The list box should show Grand Total: Sum of Orders Amount, which is what you want. This calculates each month’s percentage of all the orders for the total year. You should see the following dialog.

104

Crystal Reports 2008 Designer 1 Workshop Lesson 4: Grouping and Sorting Data

ÂŠ2008 Vision Harvest, Inc.

Click OK to close the Insert Summary dialog box

Save and preview the report. Click 2/2007 on the Group tree and scroll up to see the percentage for January

When finished save this report (Monthly Orders for 2007). The preview should look as follows

105

Crystal Reports 2008 Designer 1 Workshop Lesson 4: Grouping and Sorting Data

Ordering Groups Based on Their Subtotals Using the Group Sort Expert The Group Sort Expert is a tool used to place groups in a specific order, based on subtotals. Therefore, in order to use this tool, groupings with summaries must exist or the tool will be disabled. There are six sort options available to use. They are: No Sort – This is the default setting for all reports where the Group Sort Expert dialog box has not been used. All groups will be sorted based on the group name and not on any summary value All - This option sorts the groups based on a summary value Top N, Bottom N – Allows you to design a report showing the highest or lowest performing accounts, regions, countries etc. In some cases, the lowest numbers in a report represent success: for example, accident rates. Both options require N to be specified and any groups not within the N value will be aggregated together in a group called others. You can choose whether to display this group or not Top Percentage, Bottom Percentage – Are very much like the Top N/Bottom N options except you specify a percentage value for display in the report. For example, you can use this feature to show the top 30% of customers by Last Year’s Sales Depending on which option you choose, the dialog box displays options for selecting a summary field, entering the number you want to view, and for deciding what to do with other groups outside the number of groups or percentage value you requested. To apply a group sorting g to the groups in the report, choose Report/Group Sort Expert... or click the Group Sort Expert button on the Expert Tools bar.

106

Crystal Reports 2008 Designer 1 Workshop Lesson 4: Grouping and Sorting Data

NOTE: Crystal Reports applies the group sort order after the first pass of data handling. The first pass includes all the grouping, sorting and totaling done to the data. The Group Sort Expert settings are applied after that. This is important if you have grand totals in the report and choose not to include groups outside the Top N, Bottom N, Top Percentage or Bottom Percentage groups. Grand Totals total all the data, not just the data in these special groups.

Exercise 4.9 - Sort Groups with Group Sort Expert 1.

Suppose you want to see the top five countries in the grouping report, based on the amount of their last year’s sales. Open the Grouping.rpt report, go to Design view

Choose Report/Group Sort Expert… Or Click the Group Sort Expert button

On the Country tab, drop down the For this group sort list and choose Top N The where N is: and include Others boxes appear in the dialog box.

Drop down the based on list and choose Sum of Customers.LastYearsSales

Leave the value 5, as is, in the where N is: box

Make sure there is a check mark in the include Others check box

Click OK

Using the Save As command, save this report as Top 5.rpt

Preview the report. Note the sum of sales for each group The groups should be arranged in descending order with the highest last year’s sales as the first group.

10.

Click the Last Page button, and then look at the Grand Total figures

11.

Click the Group Sort Expert

12.

Remove the check mark from the include Others check box, then click OK

13.

Save (Top 5) and preview the report Notice that you now only see the top five groups. The remaining groups no longer appear.

14.

Click the Last Page button Notice even though not all records appear on the report, the Grand Total represents all record data. Using Running Totals will solve this problem. See the Running Totals lesson of the Designer 2 Workshop.

15.

Close the report

107

button

Crystal Reports 2008 Designer 1 Workshop Lesson 4: Grouping and Sorting Data

Challenge Exercise – Grouping, Sorting, Summarizing To further practice grouping, you are going to edit the Orders for First Quarter 2008 report from the last challenge exercise. 1.

Open the Orders for First Quarter 2008.rpt report. Click the Design tab

You are going to create nested groups on the OrderDate field to show the orders by month and then by day so you no longer need the OrderDate field. Select the field and delete it. Delete the guideline as well

Insert the first group on the OrderDate field. Format the section to be printed for each month

Insert a second group on the Order Date field. This time, format the section to be printed for each day

Format the Group #1 Name to show only the full name of the month. Format the Group #2 Name to show only the full name of the day of the week and the day number with a “ - ” separating the two items

Move the field titles from the Page Header to the Group Header #2

Sum the OrderAmount field for each month and day and add a grand total HINT: This can be done in one step by checking on the “Add to all group levels” option

Format each of the summary fields and grand total to have a fixed currency symbol

Format the report to add a top single line border to the summary fields for the Day group (Group #2). Add a top double line border to the summary fields for the Month (Group #1)

10.

Add text boxes to identify the number in each group footer. Use text that makes sense to you. Also, add an identifying text box for the Grand Totals in the Report Footer

TIP: Once you have created the text box in the Day group footer, select and copy it (CTRL + C). Then click into the Month group footer and paste it (CTRL + V). Then click in the Report Footer and paste it again. Adjust the text likewise. 11.

Format the grand total field to be bold and a size 12 font

12.

Save the report as Orders for First Quarter 2008 Grouped and Summarized.rpt. Preview the report, but do not close it

108

Crystal Reports 2008 Designer 1 Workshop Lesson 4: Grouping and Sorting Data

ÂŠ2008 Vision Harvest, Inc.

Design view for the Orders for First Quarter 2004 Grouped and Summarized report should look like the following illustration:

TIP: After you have added the line borders for the summarized fields, you may find that you have trouble aligning the text and the border. Try multiple selecting all of the fields (in the column straight up and down) and the field title. RIGHT click on the one placed where you like it the most and chose Align, Rights. The same thing can be done across the page with Align Tops.

109

Crystal Reports 2008 Designer 1 Workshop Lesson 4: Grouping and Sorting Data

Challenge Exercise – Calculating Percentages Rather than show the summed order amounts for each day and month, the summaries can be changed to show the percentages instead. 1.

Return to Design view of the report

RIGHT click the summary field in Group Footer #2

Choose Edit Summary from the Shortcut Menu

Toggle on Show as a percentage of, and then make sure the drop-down box shows Group #1: Sum of OrderAmount

Click OK

Select the Month summary in the Group Footer #1. Change the summary operation to Show as a Percentage of Grand Total: Sum of OrderAmount

Click OK

Adjust the text in the text objects to reflect the change The text should indicate something like “Percent of monthly orders” and “Percent of total orders”.

Save the report as Orders for First Quarter 2008 as Percentages

The Orders for First Quarter 2008 as Percentages report should look like the following illustration:

110

Crystal Reports 2008 Designer 1 Workshop Lesson 4: Grouping and Sorting Data

ÂŠ2008 Vision Harvest, Inc.

NOTES

111

Crystal Reports 2008 Designer 1 Workshop Lesson 4: Grouping and Sorting Data

ÂŠ2008 Vision Harvest, Inc.

NOTES

112

Crystal Reports 2008 Designer 1 Workshop Lesson 5: Combining Multiple Tables

ÂŠ2008 Vision Harvest, Inc.

Lesson 5 Combining Multiple Tables

113

Crystal Reports 2008 Designer 1 Workshop Lesson 5: Combining Multiple Tables

ÂŠ2008 Vision Harvest, Inc.

Lesson Objectives

After completing this lesson, you will be able to: Understand Relational Database Concepts Work more intelligently with a background understanding of the data for the report and how it is organized Comprehend Linking and Why It Is Necessary Know why you must link tables together and how to do it Add Multiple Tables to Reports Relational databases store data in multiple tables for more flexibility. Learn how to add more than one table to a report Use the Database Expert to add tables and link them Let Crystal Reports help you add and link tables with Smart Linking

114

Crystal Reports 2008 Designer 1 Workshop Lesson 5: Combining Multiple Tables

ÂŠ2008 Vision Harvest, Inc.

Understanding Tables, Records, and Fields Understanding how to set up and use databases fully is a complex process. It is not the focus of this course to go into great detail about databases. However, if you have little or no experience working with databases, there are a few basic terms and concepts you should understand. The bullets below should be sufficient for you to feel comfortable working with Crystal Reports. There are essentially three divisions in a database: Tables, Records and Fields: Tables store records in fields. For example, the customer order records are all stored in the Orders table Fields (Columns) store specific values. These may be entered or captured by some information gathering application like an accounting system. Maybe the information being captured is for a customer order and the database holds the order date, invoiced amount and order ID number. These pieces of information are stored as fields Records (Rows) are groups of fields all relating to the same item. For example, all information pertaining to the same order is stored in one record and that record can be uniquely identified by its order ID number Employees

Table Name Field or Column Names

EmployeeID

Records or Rows

FirstName

LastName

1 Nancy

Davolio

2 Andrew 3 Janet 4 Margaret

Fuller Leverling Peacock

Learning about Linking When Crystal Reports has to pull data from more than one table, there needs to be a link between the tables. The link connects the field or fields which uniquely identifies each record in one table and ties it to the related record(s) in another table. For example, suppose you have an Orders table uniquely identifying each order by its Order ID, and specifies the customer who placed the order, the order date, the ship date, etc. You also have an OrderDetails table listing the line items on each order, the item price and the quantity ordered. You need to have some way to identify the Order ID for each line item, so you can track what items were ordered and when. The OrderDetails table should also have an Order ID field.

115

Crystal Reports 2008 Designer 1 Workshop Lesson 5: Combining Multiple Tables

ÂŠ2008 Vision Harvest, Inc.

When you link these two tables together on their common field (Order ID), Crystal Reports can then pull accurate data regarding which line items were ordered on each Order ID, who ordered them, their quantity, price, and order date. NOTE: The fields used to create the link must be the same data type in both tables. For example: you cannot link a string field to a number field You can create several types of links. Different types of links are called join types. Although there are several join types, for the purpose of this class, you will learn about the four most common join types: Inner joins pull only records with matching records in both tables Left-outer joins pull ALL records from the left table and only matching records in the right table Right-outer joins pull ALL records from the right table and only matching records in the left table Full-outer joins pull ALL field values from the left table and ALL field values from the right table regardless of having matching information Below you see two tables related by a common field, the Dept ID field. Assume the Dept ID field is a number field and you can already see it has the same name in both tables. You wish to create a report displaying the Employee name and the Department description for the employee. Employee Table Name Dept ID Sarah 10 Sam 20 Savannah 30 Shannon 40 Zeke

Department Table Dept ID Department 10 Accounting 20 Shipping 30 Service 40 Admin 50 Tech Support

If you did not link the tables at all, Crystal Reports would not know which department goes with which employee. You would get each employee name displayed five times with each department name after the employee name. Employee Sarah Sarah Sarah Sarah Sarah Sam Sam etc...

Department Accounting Shipping Service Admin Tech Support Accounting Shipping etc...

116

Crystal Reports 2008 Designer 1 Workshop Lesson 5: Combining Multiple Tables

ÂŠ2008 Vision Harvest, Inc.

You can see links must be created if data pulled from multiple tables is to be meaningful.

Jo i er

Department Accounting Shipping Service Admin

In n

Employee Sarah Sam Savannah Shannon

If the two tables are joined with an inner join, Crystal Reports only pulls records matching in both tables. Since Markâ&#x20AC;&#x2122;s Dept ID does not match any record in the Department table it is not returned. Since Department 50 is not in the Employee table, it is not returned.

Jo i er ut O ft-

Department Accounting Shipping Service Admin

Employee Sarah Sam Savannah Shannon Zeke

If you link the two tables with a left-outer join, you get a different set of records. A left-outer join i ht Si gives all the records in the left table and any matches there are iin th the right. Since the Dept ID 50 is not in the employee table, it is not returned, however, the record for Mark in the employee table is selected because left-outer joins automatically select all records from the left table. Crystal Reports would display the record set below:

er ut -O ig ht

Department Accounting Shipping Service Admin Tech Support

Employee Sarah Sam Savannah Shannon

Jo i

nd would give you the following record A right-outer join is just the opposite of a left-outer join and set:

Tech Support 117

er ut -O ll

Department Accounting Shipping Service Admin

Employee Sarah Sam Savannah Shannon Zeke

Jo i

A full-outer join is similar to combining both a left-outer and a right-outer into one join typ type. A fullouter join would give you the following record set:

Crystal Reports 2008 Designer 1 Workshop Lesson 5: Combining Multiple Tables

ÂŠ2008 Vision Harvest, Inc.

Once you have determined which fields you need in a report and the tables in which they are contained, you must also think about the type of join you need to pull the records required by the report. Determine if you want an inner, left-outer or right-outer join. Inner joins are, by far, the most common and simplistic. If you need to use an outer join, the order in which you place the tables is important.

Join Types Join Options

Description

Inner Left-outer

Displays record set containing matching information from all tables Displays record set containing all field values from the left table regardless of having matching information in the right table Displays record set containing all field values from the right table regardless of having matching information in the left table Displays record set containing all field values from the left table and all field values from the right table By selecting this option, the link being created will not necessarily be sent to the SQL statement. Only if fields have been selected from a table or the table is used to join to another table with utilized fields will this option send the linking criteria to the SQL statement This option enforces the link to the right table. If a field is used from the right table and not from the left, the SQL statement will still require both tables be referenced for record retrieval. However, if the fields from the left table are the only ones utilized in the report, the SQL statement will only reference the left table for record retrieval This option enforces the link from the left table. If a field is used from the left table and not from the right, the SQL statement will reference both tables for record retrieval This option enforces the link regardless of the source location of the fields used in the report An equal link retrieves a record set where the linked field value has exact matches in both the left and right tables A greater than link retrieves a record set where the linked field from the left table is greater than the linked field value in the right table A greater than or equal link retrieves a record set where the linked field from the left table is greater than or equal to the linked field value in the right table A less than link retrieves a record set where the linked field from the left table is less than the linked field value in the right table A less than or equal link retrieves a record set where the linked field from the left table is less than or equal to the linked field value in the right table A not equal link retrieves a record set where the linked field value from the left table does not equal the linked field value in the right table. The SQL syntax for Not Equal is dependent on the ODBC data source driver. It is referenced as either != or <> depending on the database

Right-outer Full-outer Not Enforced

Enforced From

Enforced To

Enforced Both Equal link [ = ] Greater Than link [ > ] Greater Than or Equal link [ >= ] Less Than link [ < ] Less Than or Equal link [ <= ] Not Equal link [ != ]

118

Crystal Reports 2008 Designer 1 Workshop Lesson 5: Combining Multiple Tables

ÂŠ2008 Vision Harvest, Inc.

Adding Multiple Tables to a Report The Database Expert is where you add tables to your report and link them. Within the Database Expert, there is a tree view of all the data sources you can use with Crystal Reports and their corresponding tables.

The Database Expert appears when a new report is started for the user to select the data source and tables for the report. It can also be selected from the Database Menu. button. The tables selected To add tables to the report, highlight the table and click the will appear in the right list box, labeled Selected Tables. Once all of the tables are added to the report click

TIP: To add tables quickly using the Database Expert, multiple select them using your mouse and the SHIFT or CONTROL key to highlight all of the needed tables, and then click the Move Over

button to add them all at once.

When multiple tables have been added to the report from the Database Expert, the Links tab automatically appears in the Database Expert. The Links tab in the Database Expert is the tool used to link tables in a report. Crystal Reports uses Smart Linking to link tables together based on common fields. Smart Linking automatically chooses links for your tables based on common fields in tables or indexed fields. You can also manually add a link between two tables by dragging a field name from one table to the other. The application will draw a new link arrow between the tables. The link is represented by a line with or without an arrow, depending on the join type. This is referred to as a link line. It is good practice to clear all links and do your own linking.

119

Crystal Reports 2008 Designer 1 Workshop Lesson 5: Combining Multiple Tables

You can turn off Smart Linking for your reporting environment under File/Options/Database tab as shown below. It is on by default but we recommend you turn it off and manually create the links you need for most production databases. The reason is most production databases change common (linkable) field names from one table to the next. For instance: the Customers table may have the field CustomerID_PK to identify the customer ID. “PK” as a field name suffix is commonly used to denote this field is a Primary Key. Primary Key means the field is indexed (sorted) and their are no duplicate values. The Orders table may have CustomerID_FK to identify the customer ID. “FK” as a field name suffix is commonly used to denote the field is a Foreign Key. Foreign Key means the field is indexed, but duplicates do exist. This means Customers.CustomerID_PK links to Orders.CustomerID_FK, but Crystal’s Smart Linking feature will not link the two fields automatically because the names are different and you will need to do this manually. Another reason Smart Linking does not work well with production databases is that many tables have common fields not meant for linking, such as date/time stamps. Smart Linking will link these fields and you will be forced to unlink them.

120

Crystal Reports 2008 Designer 1 Workshop Lesson 5: Combining Multiple Tables

ÂŠ2008 Vision Harvest, Inc.

The Database Expert can be opened manually from the Database menu or you can use the Database Expert button.

TIP: If you did not add all the tables you need from the Database Expert when you first created the report, you can add them by re-opening the Database Expert and select the missing tables. Suppose you need to create a report using the Northwind 2008 database. This report must show the orders by supplier and by product. You need to see the OrderID, OrderDate, Unit Price and Quantity of each item. Before you begin this report, you should be familiar with the tables and how they are set up. The illustration above displays the tables you will use in this report. Notice the Suppliers table and the Products table can be linked on the Supplier ID field. The Products table and the OrderDetails table have Product ID as the common field. Finally, the OrderDetails table and the Orders table can be linked on the Order ID field.

121

Crystal Reports 2008 Designer 1 Workshop Lesson 5: Combining Multiple Tables

ÂŠ2008 Vision Harvest, Inc.

Exercise 5.0 - Create a Report with Linked Tables 1.

Start a new report using the As a Blank Report option button

Open the My Connections folder, and then open Northwind 2008 database, if necessary

Add the Suppliers, Products, OrderDetails and Orders tables in this order, then click OK The Links Tab is activated, showing the tables selected for this report.

Click OK to close the Database Expert

Go to the Database menu and select Database Expert

Remove the OrderDetails and Orders tables

Click on the Links tab Enlarge the window to make the next few steps easier to complete.

Click on the Clear Links button and respond Yes you are sure you want to clear the Links

Click and drag the Products table to the right of the Suppliers table

10.

From the Suppliers table, click and drag from the SupplierID field to the Product table, SupplierID field. Then release the mouse button

11.

Right-click over the link line and select Link Options. Confirm your Link Options dialog box looks like the one below

12.

Click OK.

13.

Click on the Data tab and add OrdersDetails table. Click the Links tab to confirm it is added and the linkage is correct: OrderDetails.ProductID

14.

Products.ProductID

Finally, add the Orders table and close the Database Expert and click OK. 122

Crystal Reports 2008 Designer 1 Workshop Lesson 5: Combining Multiple Tables

ÂŠ2008 Vision Harvest, Inc.

15.

Add the following fields to the Detail section: OrderID, OrderDate, UnitPrice, and Quantity

16.

Group the report on the SupplierName. Add a second group on the ProductName

17.

Save the report and name it Linking.rpt. Preview the report

The report should be similar to the illustration below:

WARNING: If you are using a data file connection, instead of an ODBC connection, you must create the link on an indexed field.

123

Crystal Reports 2008 Designer 1 Workshop Lesson 5: Combining Multiple Tables

Challenge Exercise – Using the Database Expert to Link Tables Suppose you need to create a report listing employees. You need to include the employee ID, name, address information, current position and salary. The work information is contained in the Employee table, while personal information, including address, is in the Employee Addresses table. The two tables have a common field, Employee ID. 1.

Start a new report using the As a Blank Report option button

From the My Connections folder, open Northwind 2008 database and add the Employees table and the EmployeeAddresses table then click OK

Verify the link is correct in the Link tab, click OK

Add the Employee ID field to the Details section

Create a text object in the Details section to right of the Employee ID field

Click away from the text object to close it. Then right-click on the text object and choose Format Text… and turn on Can Grow in the Common tab This step will make it easier for us to see all the different items we are going to place in the text object.

In the text box, insert the address information putting the FirstName and LastName fields on the first line separated by a space. Put the Address1 field on the second line and the Address2 field on the third line. Put the City, Region, and PostalCode fields on the fourth line, adding a comma and spaces so it looks like an address. Add the Country field on the last line. The text box should look like the following when it is finished:

Add the Title and Salary fields to the Details section to the right of the address text object

Save the report as Employees.rpt and then preview Notice there are some employees with blank lines in the address text object. This is a result of the Address2 field being blank or null. Also there is one address (Nancy Davolio) with a Line Feed (carriage return) embedded in the address field, which forces the Address 1 field to return two lines. The next step will eliminate the blank lines in the text object.

10.

Right-click on the text object containing the address information and select Format Text… and check on Suppress Embedded Field Blank Lines

11.

Click OK. Format the report to look like the example on the next page

12.

Save the report (Employees), preview and close it

124

Crystal Reports 2008 Designer 1 Workshop Lesson 5: Combining Multiple Tables

ÂŠ2008 Vision Harvest, Inc.

125

Crystal Reports 2008 Designer 1 Workshop Lesson 5: Combining Multiple Tables

ÂŠ2008 Vision Harvest, Inc.

NOTES

126

Crystal Reports 2008 Designer 1 Workshop Lesson 6: Creating and Using Formulas

ÂŠ2008 Vision Harvest, Inc.

Lesson 6 Creating and Using Formulas

127

Crystal Reports 2008 Designer 1 Workshop Lesson 6: Creating and Using Formulas

Lesson Objectives After completing this lesson, you will be able to: Understand and use Crystal Reports formula syntax Without being a programmer, learn to manipulate fields in many useful ways Use the Formula Workshop Use this new interface to better manage your formula building work Use the Formula Editor Easily create complex formulas. Add Fields, Functions, and Operators to formulas by double clicking Create formulas to perform number calculations Perform powerful calculations to numeric fields Create date formulas Add and subtract dates and display parts of a date Create string formulas Trim fields, select specific portions of a field, convert numbers and dates to text, concatenate and perform other text functions Use If … Then … Else statements in formulas Tell Crystal Reports to make decisions and perform different operations as results of those decisions Add Bookmarks to Formulas Make long formulas easier to navigate through. Add bookmarks to identify locations you need to edit frequently, or major sections of a formula, or the beginning of each if…then… else statement

128

Crystal Reports 2008 Designer 1 Workshop Lesson 6: Creating and Using Formulas

ÂŠ2008 Vision Harvest, Inc.

Understanding Crystal Formula Syntax without Being a Programmer The power of formulas in Crystal Reports is immense. A formula is a symbolic statement of manipulations you want performed on certain data before it is printed on your report. For example, if your report is to contain a {file.Quantity} field and a {file.Unit Price} field, you may want to create a Total Price formula telling Crystal Reports to multiply the value of the {file.Quantity} field and the value of the {file.Unit Price} field and then print the result. You can use formulas to calculate numeric values, compare one value to another, select alternative actions based on the comparison, join multiple text strings into a single string, and a multitude of other purposes. Formulas are used: to calculate information you can not obtain directly from database fields to compare data to join text with data to convert data from one form to another to enhance the formatting options with text strings to perform a number of other things for customizing reports Crystal Reports also has more than 300 pre-written functions to calculate new values for reports. Functions are built-in procedures used to evaluate, make calculations on, or transform data. When you specify a function, Crystal Reports performs the set of operations built into the function without you having to specify each operation separately. In this way, a function is a kind of shorthand making it easier and less time consuming for you to create reports. The numerous pre-written functions add a great deal of flexibility for manipulating database data. Virtually any value you want displayed on a report can be calculated if the necessary data is available in the tables and the mathematical principals you follow are sound. However, before you start working with formulas you need to understand the rules or syntax needed to create effective formulas. You have the option of using either Crystal or Basic syntax. Crystal syntax is the formula language included in all prior and current versions of Crystal Reports. Basic Syntax is similar to Visual Basic except it has specific extensions for handling reporting. Most formulas can be written in either syntax, and reports can contain formulas written in either Basic or Crystal syntax or both. This allows the user to choose the formula syntax they are most familiar with and provides the added benefit of new functions, operators and control structures inspired by Visual Basic. Formulas are composed of distinct parts which must be inserted using syntax rules. Syntax means certain characters must be used at certain times and places in the formula in order for the formula to be properly read. The following table lists parts of a formula and syntax rules associated with each:

129

Crystal Reports 2008 Designer 1 Workshop Lesson 6: Creating and Using Formulas

Formula Syntax Rules Parts of a Formula

Syntax Rules

Fields Other Formulas/Objects

Fields are always shown enclosed in curly braces. For example: {table.FIELDNAME} You can add formulas within other formulas. The formula name must be preceded by an @ symbol and then surrounded by curly braces. For Example: {@FormulaName}. Other object types follow a similar pattern:

Functions

Numbers

Text Operators

// Capitalization Returns and spaces

Parameters = {?ParameterName} SQL Expressions = {%ExpressionName} Running Totals = {#RunningTotalName} Crystal Reports contains a long list of arithmetic functions (average, variance, standard deviation, etc.) plus other functions to modify dates, strings, etc. Always enter functions with the FunctionName followed by one or more arguments placed in parentheses and separated by commas. For Example: FunctionName (argument1, argument2,etc...) Numbers must be entered with no commas. For Example: 3000 or 30000 or 300000. Decimals are allowed, but optional (3000.00 or 3000.99). A currency symbol ($) can be used to make the value recognized by Crystal as currency. This will force Crystal to apply currency formatting automatically ($3000.99) Text must be added within single or double quotation marks. For Example: “ABC” will output ABC, but ‘ ”ABC” ’ will output “ABC” Crystal Reports contains several operators to manipulate data. Common operators are + - * and / % < > = etc. Syntax rules allow you to place a space before and after an operator or omit spaces Comments may be added to formulas by beginning the line with //. If you create complex formulas, it is a very good idea to comment them Formulas are NOT case sensitive. Caps and lower case are considered the same The Formula Editor ignores hard returns (ENTER) or spaces used to make the formula more readable. Use returns and spaces to make the formula easier for you to read. Do not insert a return between any quotations. This is one area Crystal Reports returns an error

130

Crystal Reports 2008 Designer 1 Workshop Lesson 6: Creating and Using Formulas

About the Formula Workshop The Formula Workshop serves the purpose of being the central work area for building formulas in Crystal Reports. We build formulas to manipulate data, perform calculations and so on. To create formulas we use the Formula Workshop. This formula building tool is divided into two separate components. The first component, or Formula Editor, is used to build formulas using various resources such as data fields, functions and operators. The Formula Editor has been the primary component for building formulas in Crystal Reports since version 3.0. The main change is it is now bundled into the Formula Workshop. The other component is the Formula Expert, which serves the purpose of enabling developers to create custom functions for reuse in current and future reports. Think of custom functions the same way you think of standard functions. For example, if you wanted a function to convert data in a field to proper case, you would simply use the standard function ProperCase(str) to convert the data. This function is simple to use and is bundled with Crystal Reports. On the other hand, suppose you have complex groupings requiring the evaluation of several fields and comparisons. You could copy and paste the formula between the many reports that include the grouping, or you could build a function which resides in the global repository for reuse at any time. However, the repository is available only with BusinessObjects Enterprise. In this class, we work exclusively with the Formula Editor, which will meet 100% or more of your needs. We work with the Formula Expert to create custom functions in the Designer 2 Workshop.

Using the Formula Workshop You are going to create formulas using the Formula Editor. The Formula Editor is a dialog box with a workspace to build the formulas. It also has three convenient lists allowing you to add fields from the report and underlying tables, functions, and operators. The Formula Editor also has a syntax checker which reviews the formula for syntax errors. If errors in syntax exist, the Formula Editor highlights the remaining code it can’t evaluate and provides help information to guide you in correcting the error. Formulas are fields, just like fields from the database. Therefore, to create a formula, you use the Field Explorer dialog box. To open the Field Explorer dialog box, choose Field Explorer… from the View menu, or click the Field Explorer button from the Standard Toolbar.

131

Crystal Reports 2008 Designer 1 Workshop Lesson 6: Creating and Using Formulas

ÂŠ2008 Vision Harvest, Inc.

You can also select the Formula Workshop button on the Expert Tools bar mula Workshop from where you can create any type of formula.

. This opens the For-

From the Field Explorer dialog box, you can edit existing formulas, create new ones, rename formulas, or delete them. To create a new formula, select Formula Fields: then click the New button. All formulas must have a name, and you can enter the name in the Formula Name dialog box.

When you click OK, the Formula Editor opens inside the Formula Workshop.

To understand the Formula Workshop and Formula Editor interfaces, we must first break them down into components. First we will look at the Formula Workshop components and then finally the Formula Editor components.

132

Crystal Reports 2008 Designer 1 Workshop Lesson 6: Creating and Using Formulas

ÂŠ2008 Vision Harvest, Inc.

The Formula Workshop has three major components. The first component is the toolbar found at the very top of the workshop. This toolbar has several useful items which make navigation within formulas and between formulas more user-friendly.

The second Formula Workshop component is the Workshop Tree. This component allows the developer to move more easily between formulas, record selection, custom functions, SQL expressions and section formatting formulas.

The third component of the Formula Workshop can be either the Formula Editor or the Formula Expert. Once again for this class we will concentrate on just the Formula Editor. The Formula Editor is the area where formulas are developed using field references, function and operators.

Using the Formula Editor The three lists at the top of the Formula Editor allow you to view and select fields, functions, and operators. The Fields list contains not only fields in the tables for the report, but also any formulas you may have created, group names, summary fields, running totals, and parameters set up for selection. The many functions available in Crystal Reports are listed in the second box with syntax indicators displayed. The third list contains operators. Not only does Crystal Reports contain the standard numeric operators, but you can also use comparison operators, string operators, boolean operators, conversion operators, and variable declarations.

133

Crystal Reports 2008 Designer 1 Workshop Lesson 6: Creating and Using Formulas

ÂŠ2008 Vision Harvest, Inc.

Functions and operators are organized into groups. Just click the in front of the category name to expand the category and see the functions or operators in that category.

To make it easy to customize the Formula Editor, each of the windows at the top of the dialog box has its own close button and expand button. In addition, you can resize any of the windows by dragging its border.

The area at the bottom of the Formula Editor displays the formula. You can type in this box or add items from any of the three lists by double clicking the item you want. To avoid syntax errors, it is recommended you add items from the lists as much as possible, rather than typing. To assist you, the Formula Editor has its own toolbar with buttons to perform actions in a single mouse click.

The Formula Editor Toolbar

The Formula Editor Toolbar contains only those functions used within the formula creation process. Some key functionality, such as the Save and Close options have been moved to the Workshop frame. These functions are still available to use while creating and modifying formulas. Once a formula is finished, click the Check button in the toolbar. If the syntax is correct, click the Save button. The formula then becomes a field, just like a field from the database. You can add it to the report, move and resize it and format it just like any other field. TIP: YES, you can edit multiple formulas from within the Formula Editor. Use the Formula Workshop Tree by simply clicking on the item you wish to edit. Once the item is clicked the Formula Editor displays the formula code for that item and is ready for any editing necessary. You can also use the New button to create new ones. Be sure to save the existing formula with the Save button before you open another.

134

Crystal Reports 2008 Designer 1 Workshop Lesson 6: Creating and Using Formulas

Performing Simple Number Calculations To begin working with formulas, you are going to do some simple number calculations. Performing calculations on numeric data is usually a straightforward process. However, there are two special rules you need to remember: Always write the formula on a piece of paper and plug-in some sample numbers. Check for accuracy using a calculator. Pay attention to the Order of Operation rule, which states: multiplication and division are processed first, addition and subtraction are processed last and anything in parentheses is processed before everything else. The order of operation rule can be very important in calculations. For example, suppose you wanted to know the percentage discount between the listed price and the actual price. You might write a formula like: List Price – Purchase Price / List Price 200 – 150 / 200 200 – 0.75 = 199.25 The result is obviously in error. The formula calculates the division first based on the order of operation rule. By applying parentheses to separate pieces of the formula, you can force the Formula Editor to calculate the formula in the order you wish: (List Price – Purchase Price) / List Price (200 – 150) / 200 50 / 200 = .25

135

Crystal Reports 2008 Designer 1 Workshop Lesson 6: Creating and Using Formulas

Before you can do any calculation formulas, you need to set up a new report based on the Northwind 2008 database. The business requirements for completing this report are as follows: you need a report giving you, by Product, a listing of customer orders for 1st quarter 2007. For each order you want to see the suggested retail price, the discount percentage, the actual sales price, quantity ordered and the line total for the order. The line total is the quantity times the unit price. The discount percentage is the retail price minus the unit price divided by the retail price times 100. The report will look something like this:

The information is in three different tables: Orders, OrdersDetail and Products. Orders and OrdersDetail are linked on the OrderID field. OrdersDetail and Products are linked on the ProductID field.

Exercise 6.0 - Create a Calculated Field to Determine a Discount 1.

Start a new report using the Blank Report option button

From the My Connections folder, open Northwind 2008 database and add the Products, OrderDetails and Orders tables. Click on the Links tab and make sure the tables are properly joined. Click OK to close the Database Expert

Group on the Product Name field

Using the Select Expert, set the Order Date to “is between” 1/1/2007 12:00:00AM and 3/31/2007 12:00:00AM

Add the RetailPrice field to the Detail section at the left margin

Make sure the Field Explorer dialog box is open, and then select Formula Fields:. Click the New button

Type Discount for the name of the formula, then click OK 136

Crystal Reports 2008 Designer 1 Workshop Lesson 6: Creating and Using Formulas

Type an opening parenthesis “(“ in the main formula text area

Locate the Products.RetailPrice field in the Report Fields Tree list on the left, and then double click it to add it to the formula

10.

Click the in front of the Arithmetic category in the Operators list on the right. Double click the subtract operator “–“ to add it to the formula Note: It may be necessary to expand the Operator Tree first.

11.

Double click the OrderDetails.UnitPrice field from the Report Fields list

12.

Type a closing parenthesis “)”

13.

Double click the percent operator “%” in the Operators list

14.

Double click the Products.RetailPrice field to add it The formula should look like the following: ({Products.RetailPrice} - {OrderDetails.UnitPrice}) % {Products.RetailPrice} This is a formula for calculating a percentage rate between two numbers. The % operator does the same thing as divide then multiply times 100.

15.

Click the Check button to check the syntax of the formula If you entered the formula correctly, Crystal Reports displays a dialog box indicating that there were no errors. If there is an error, Crystal Reports indicates what the error is. Click OK. The flashing insertion bar appears where the error has occurred.

16.

Click the Save button This will save the formula only.

17.

Click the Save and Close button This will close the Formula Workshop. The formula will now be listed in the Field Explorer and may be used in the same way as a database field. Please note, you may perform step 17 by pressing CTRL + S simultaneously. This keystroke combination will save the formula and close the Formula Workshop at the same time.

18.

Remember that a formula is a field. Once you create it, treat it like any other field. Add the formula to the report to the right of the RetailPrice field. Format the Discount field as a %. Also, set it so it suppresses the zero values. Remember to select customize the number to find the suppress if zero option

19.

Round the Discount formula to zero decimal places

20.

Add the UnitPrice and Quantity fields to the report after the Discount field

21.

Save this report and name it Number Formulas.rpt. Preview the report

137

Crystal Reports 2008 Designer 1 Workshop Lesson 6: Creating and Using Formulas

ÂŠ2008 Vision Harvest, Inc.

Exercise 6.1 - Create a Field to Calculate the Quantity Times the Unit Price 1.

Create a new formula named Line Total

Using the Formula Editor, create the following formula: {OrderDetails.UnitPrice} * {OrderDetails.Quantity} This formula is a simple multiplication of two fields.

Insert this formula in the Details section and then summarize it using the Sum operator. Insert a summary for Group #1

Save the report (Number Formulas) and preview it

In the next formula, you are going to start using the Report Fields list more extensively. It might help if you become familiar with some of the symbols indicating the types of available fields. The following table lists the symbols you might see in the fields list and what they represent:

Formula Editor Fields List Symbols Symbol

Description Report fields (this is the group heading for all the fields in the report) Database (this is the group heading for all the data sources added to the report) Table (this is the group heading for all the tables in the attached database) Fields Group Names Formulas SQL Expressions Summary Fields Running Totals Parameters

138

Crystal Reports 2008 Designer 1 Workshop Lesson 6: Creating and Using Formulas

Exercise 6.2 - Create Formulas to Calculate a 6.5% Sales Tax and to Total the Sub total and the Sales Tax 1.

Create a new formula and name it Sales Tax. This formula is going to multiply the summary field for the @Line Total formula times the sales tax amount. It looks like this: Sum ({@Line Total}, {Products.ProductName}) * .065 Remember that the @Line Total formula is available in the Fields list. The Sum of the Line Total summary field is also available, since the summary is a field in the report. Therefore, you can add the entire first part of this formula by double clicking it in the Fields list.

Double click the multiply operator “*” from the Operators list

Type .065

Press CTRL + S to save and close the formula, then add the Sales Tax field to the Group Footer #1 under the Line Total column Performing this keystroke combination will also check the formula for errors and provide the option of returning to the Formula Workshop to make any fixes if an error is found.

Create a new formula named Total

Using the Formula Editor create the following formula: Sum ({@Line Total}, {Products.ProductName}) + {@Sales Tax} This formula is adding the summary field for the product group and Sales Tax formula together.

Add this field to the Group Footer #1 under the Sales Tax field

Take a moment to add labels to the formulas placed in the Group Footer #1

Save the report (Number Formulas) and preview. Close the report

139

Crystal Reports 2008 Designer 1 Workshop Lesson 6: Creating and Using Formulas

Manipulating Dates with Formulas Most reports include one or more date fields. A report with employee information may contain the date the person was hired. Sales and Accounting reports may have dates of transactions or accounting periods. You can manipulate these date fields with formulas to provide an additional field with useful information. The employee report might show the number of months and days since the hiring date. To see how formulas can work with dates, you are going to create an employment history report using the Employees table in the Northwind 2008 database. The report is going to give a listing of employees with their last name, age, an employment history statement, 20-year anniversary date and whether or not the employee is considered to be a career employee. The report will look something like this:

Date Functions Functions

Definition

CurrentDate ToText (x,y)

This function will produce a date based on your computer system’s date This versatile function can, among other things, convert a date value into a text value. It also allows us to format the value to display any or all portions of the date. See a complete listing of format options in the “ToText” on-line help section This function enables dates to be incremented by nearly any interval type such as days, weeks, months, years or hours. The return value is a date

DateAdd (intervalType,nIntervals, startDateTime) DateDiff (intervalType,nIntervals, startDateTime,endDateTime) ChrW (x)

Use this function to find out the interval period between two date or date time fields. Some interval periods available are: Day (d), Month (m), Year (yyyy). Several other types are available. The help file has a complete listing This Character function accepts recognizable ASCII code characters. The most usable in Crystal Reports are 13 for carriage returns, 8 for space and 9 for tabs

140

Crystal Reports 2008 Designer 1 Workshop Lesson 6: Creating and Using Formulas

Exercise 6.3 - Create Date Formulas 1.

Start a new report using the As a Blank Report option button, the Northwind 2008 database and the Employees table

Add the LastName field to the Detail section. Reduce its width by 40%

Create a formula called Age. The formula should look like the one below: DateDiff (“yyyy”,{Employees.BirthDate},CurrentDate) The DateDiff and CurrentDate function is in the Date/Time category.

Add this formula to the report to the right of the LastName field

Save the report as Date Formulas.rpt. Preview the report

Next, you are going to use the ToText(x,y) function to convert information from the date to text. The text is then combined with typed text to provide employment history. This ToText function has two parameters: the field containing the data you want to convert, and the format to which you want to convert it.

ToText(x,y) Character Formatting Character

Description

d dd ddd dddd M MM MMM MMMM yy yyyy # 0 , . h hh H HH m mm s ss t, tt

Day of month without leading zero for single digits Day of month with leading zero for single digits Day of week as a three-letter abbreviation Full name of day of week Month without leading zero for single digit Month with leading zero for single digit Month as three-letter abbreviation Full name of month Last two digits of year Full four digits of year Forces spaces into return string Defines significant digits and adds zeros if number is too small Indicates where thousands separators are placed in the string Indicates where decimal separators are placed in the string Hours without leading zero for single digits (12 hour clock) Hours with leading zero for single digits (12 hour clock) Hours without leading zero for single digits (24 hour clock) Hours with leading zero for single digits (24 hour clock) Minutes without leading zero for single digits Minutes with leading zero for single digits Seconds without leading zero for single digits Seconds with leading zero for single digits Single or multiple character am/pm string 141

Crystal Reports 2008 Designer 1 Workshop Lesson 6: Creating and Using Formulas

Exercise 6.4 - Use a Formula to Convert Dates to Text 1.

Create a new formula and name it Employment History

Double click the FirstName field in the Report Fields list on the left

Type + “ joined Northwind on “ + Do not forget to add spaces before and after the words since concatenation does not provide spaces.

To add the ToText (x, y) function, click the for the Strings category in the middle Function Tree list. Then click the for ToText. Then double click the ToText (x, y) function Crystal leaves the insertion bar where you need to add the field name.

Double click the {Employees.HireDate} from the Report Fields tree field to add it

Move the insertion bar after the comma, then type “MMMM d, yyyy.”

Finish creating the formula using the above techniques. The final formula should look like this: {Employees.FirstName} + “ joined Northwind on “ + ToText({Employees.HireDate}, “MMMM d, yyyy.”) + ChrW(13) + “This was on a “ + ToText({Employees.HireDate}, “dddd.”) The MMMM d, yyyy format returns a date similar to March 1, 1999. The dddd format returns the day of the week such as Friday. Notice we are also able to place the periods within the ToText function formatting argument.

Add this field to the report to the right of the Age formula This formula displays best if it is sized tall enough to display three lines.

Save the report (Date Formulas) and preview it

TIP: Because you used the ToText function concatenated with other text, Crystal Reports trimmed the date fields automatically. However, if you want to trim a field in a formula you can use the Trim (string) function. Crystal Reports will display the field without extra space at the end.

142

Crystal Reports 2008 Designer 1 Workshop Lesson 6: Creating and Using Formulas

Exercise 6.5 - Create a Formula to Calculate a Date 1.

Create a new formula and name it 20 Year Anniversary

Using the Formula Editor create the following formula The DateAdd(intervalType,nIntervals,startDateTime) function is listed in the Date/Time function category. DateAdd (“yyyy”,20,{Employees.HireDate}) This Date function creates a date type field.

NOTE: To better understand the interval type syntax and how it differs from the ToText function, look up the DateAdd function from the Crystal online help. 3.

Add the formula to the report to the right of the Employment History formula

Save the report (Date Formulas) and preview it

Creating Boolean (True/False) Formulas Crystal Reports uses Boolean formulas to show whether a particular criterion in a report is met or not met. The output of the field is simply True or False. For example, suppose you have a field that contains the number of days students attended classes within a school year. Suppose you want to know the number of students who attended sufficient days to qualify the school for the federal funding connected to attendance. You could create a formula like this: {@school days attended} > 120 (If 121 days were the federal requirement for funding and if 550 students were enrolled, the results would be 446 True and 54 False.)

Exercise 6.6 - Create a Boolean Formula 1.

Create a new formula and name it Career Employee

Using the Formula Editor create the following formula: CurrentDate - {Employees.HireDate} > 2000 This formula shows Crystal’s ability to subtract one date from another and return the number of days as a number

Add the formula to the right side of the report

Save (Date Formulas) and preview the report. Close the report Crystal Reports displays the value “True” for any employees with over 2000 days employment.

TIP: You can format Boolean fields to display as True/False, as T/F, as Yes/No, as Y/N, or as 1/0.

143

Crystal Reports 2008 Designer 1 Workshop Lesson 6: Creating and Using Formulas

ÂŠ2008 Vision Harvest, Inc.

Creating String Formulas To learn how to use string formulas, you are going to create the report displayed below. The business requirements for this report are to create an alphabetical listing of each employee and their contact phone number. The report should contain the employeeâ&#x20AC;&#x2122;s ID number, first initial and last name, address, home phone, plus the emergency contact phone number. The report will look something like this:

The information is in two tables: Employees and EmployeeAddresses. You are going to create the report using several formulas. For added practice, you are going to use different methods in the formulas to show the flexibility of Crystal and itâ&#x20AC;&#x2122;s ability to accomplish the same task using different methods. Crystal Reports uses String Formulas to do calculations on fields containing strings or text. For example, suppose the database contains a field called Customer.FirstName and a field called Customer.LastName. You might need to display this information as First initial and the Last Name in a single field object. You can write a string formula creating a new field displaying the first initial connected to the last name. Something similar to the following example: 1 - N. Davolio

144

Crystal Reports 2008 Designer 1 Workshop Lesson 6: Creating and Using Formulas

You are going to use the following operators and functions in the string formula exercises:

String Formula Operators and Functions Definition

Operator/Function + or & “ ” or ‘ ’

Concatenate operator is used to string together text fields Quotes are used to surround text in order to signify the text between the quotes is text and is to be taken literally Subscript operators are placed immediately after a field name and are used to signify which characters you want from the field’s value. Ranges are common, such as 1 to 3 or 7 to 11. However, specifying a single character is not uncommon. Such as [3] to signify the third character of the field The ToText function is used to convert Number, Currency, Date, Time, and DateTime values to text strings. Multiple arguments (x, y) vary to support the type of values being converted. Such as, with a number field x represents the value being converted, and y represents the number of the decimal places needed. You are only required to enter the value for x. All others are optional IsNull is used to check to see if a field contains a value. When used alone it returns a Boolean value of True or False. This function is often used with If Then Else operators as the test portion If Then Else operators allow you to do different things in a formula based on conditions. In common language, it might look this… If a particular field is blank Then print another field, Else print the original field Length is a string function used to count the number of characters in a string value. It is sometimes used when a formula requires a value to be a certain length The Picture function is used to display a string value in a set format. Such as converting a string of numbers to a typical telephone number format (XXX)XXX-XXXX

[]

ToText

IsNull

If Then Else

Length(str) Picture(str,picture)

Exercise 6.7 – Start the Emergency Contact Report 1.

Begin a new report using the As a blank report option. Add the Employees and EmployeeAddresses tables from the Northwind 2008 database

Add a Text Object to the Report Header with the title Employee Contact Listing Format the text as you wish.

Save the report as String Formulas.rpt

The first formula is going to display the EmployeeID connected to the first initial of the first name and the last name. The EmployeeID field is a number field and the first and last name fields are string fields. In formulas, you cannot concatenate fields with different data types. Therefore, the first thing you need to do is convert the number field to a string field so it can be concatenated to the rest of the text.

145

Crystal Reports 2008 Designer 1 Workshop Lesson 6: Creating and Using Formulas

To pull the first initial from the first name field, you are going to use the Subscript operator [ ]. This operator allows you to display only certain characters in the field based on their position number counting from the left. The formula looks like this: ToText({Employees.EmployeeID},0,””) + “ - “ + {Employees.FirstName}[1] + “. “ + {Employees.LastName} The + sign in the formula is a concatenation operator. The concatenation operator joins text with field information. Crystal Reports does not add any spacing with the concatenation operator. You must add spacing by including spaces as text in the formula.

Exercise 6.8 - Create a Formula to Display the Employee Name 1.

Create a new formula named Employee

Click the for the Strings category in the Function Tree list. Click the for the ToText functions. Double click the ToText(x, y, z) function to add it to the formula

Insert the {Employees.EmployeeID} field from the Employees table as the first argument Add the field inside of the open parenthesis and to the left of the comma.

Move the insertion bar to the right of the first comma and type a zero (0) This tells Crystal the number should display with no decimal places.

Move the insertion bar to the right of the second comma and type “” Two quotes with no space means: empty string. This tells Crystal the number should display with no thousands separator.

Move the insertion bar outside the closing parenthesis and type: + “ – ” +

Add the FirstName field from the Employees table

Click the for the Strings category in the Operator Tree list on the right. Then double click the Subscript (x [y]) operator

Type 1 between the inserted square brackets

10.

Move the insertion bar outside the ending square bracket, and then add the concatenate operator (+). Type “. ” Be sure to add a space after the period in the quotes.

11.

Concatenate again, and then add the LastName Field to the end of the formula

12.

Check, then save and close the formula. Add it to the left side of the Details section Resize the field and its title to be 2” wide.

13.

Resize the Detail section to be a little over 1” tall

14.

Save the report (String Formulas) and preview it. When finished, return to Design view The first record should look like this: 1 – N. Davolio.

146

Crystal Reports 2008 Designer 1 Workshop Lesson 6: Creating and Using Formulas

The next formula you want to create is for the home address. The EmployeeAddresses table contains two address fields, Address1 and Address2. Some employees have data only in the Address1 field; some have data in both fields. If an employee’s address is only in the Address1 field, just display it; if the employee’s address is in both fields, you want to display both fields separated with a comma and a space. Notice the use of the word “if” in the above statements. When you have conditions like this, Crystal Reports needs to make a decision and do different things based on the decision. The If…then… else statement allows a formula to take different actions based on the results of a test. After the If… you enter a test. After the Then… you tell the formula what to do if the test is true. After the Else… you tell the formula what to do if the test evaluates to false. Stated simply, it looks like this: if test then true else false In this case, if the field Address2 contains data, you want Crystal Reports to print Address1, then a comma and a space, then Address2. If the field is empty, you want to print only Address1. The formula looks like this: If IsNull({EmployeeAddresses.Address2}) then {EmployeeAddresses.Address1} else {EmployeeAddresses.Address1} + “, “ + {EmployeeAddresses.Address2} The IsNull function lets you to make decisions based on whether a field is empty or contains data. It is located in the Print State category in the Functions list.

Exercise 6.9 - Create a Conditional Formula 1.

Create a new formula named Home Address

Click the in front of the Control Structures category in the Operator Tree list. Double click if x then y else z The Formula Editor adds the if…then…else statement to the formula and places the flashing insertion bar after the if.

Click the in front of the Print State category in the Function Tree list. Double click the IsNull ( ) function

From the Field Tree list, double click the Address2 field to add it to the IsNull function.

Press the RIGHT ARROW key to position the cursor after the word “then”

Double click the Address1 field, then press ENTER

Press the RIGHT ARROW key until the flashing insertion bar is after the word “else”

Enter the rest of the formula using the fields, the concatenate operator, and the typed text. The final formula should look like this: If IsNull({EmployeeAddresses.Address2}) then {EmployeeAddresses.Address1 else {EmployeeAddresses.Address1} + “, “ + {EmployeeAddresses.Address2}

Check the formula. If it is correct, save and close it

147

Crystal Reports 2008 Designer 1 Workshop Lesson 6: Creating and Using Formulas

10.

Add the Home Address formula to the right of the Employee formula

11.

Resize the Home Address and its text box to be about 2.2” wide

12.

Save and preview the report. When finished, return to Design view

The Design View of the report should look like the following illustration at this point:

To make this exercise more interesting, let’s suppose we would like to also include the City, Region, Postal Code and Country fields in the Home Address formula and would like this information to display like so: Address1, Address2 City, Region Postal Code Country There are a number of issues we must address in order for this formula to return the desired output. The first issue is providing hard-coded carriage returns. This can be accomplished using the ChrW() function using the number 13 as the ASCII code – ChrW(13). Another issue we need to handle occurs when a field has no value or is empty, as seen by the Home Address formula. The Region field for some of the addresses is null (empty) and when we add the Region field to the formula, the formula will fail and return a completely blank address. There are several methods for handling null values in reports. Solution 1 - We can continue to test for null values on fields where this condition may exist using the IsNull() function Solution 2 - We can turn on the setting, “Convert Database Null Values to Default”…, found in File|Report Options… This setting will convert any null values to an actual value such as zero for number fields or blank for string fields. Since we have already used the IsNull function earlier in the formula, we won’t use this solution. Turning on this feature renders all IsNull () tests useless since there are no longer any null values WARNING: Not all database systems support this option

148

Crystal Reports 2008 Designer 1 Workshop Lesson 6: Creating and Using Formulas

Solution 3 - A new feature introduced in Crystal Reports XI is the ability to change, on a formula-by-formula basis, the way Crystal Reports handles untested null values. In the Formula Workshop Toolbar there is a drop-list giving you options on how you want nulls handled. The default (Exceptions For Nulls) forces Crystal to except out (do nothing) when an untested null value occurs. This is why formulas show blank when encountering untested nulls. The other choice is to provide default values to untested null values and not except out. Setting the drop-list to Default Values for Nulls will only apply to fields not specifically tested for null. For fields being used with the IsNull () function, the value remains null so the test will work properly. One last consideration on this feature: specifying Exception for Null is only applied to that specific formula. No other formulas are affected

Another the remaining issue is how to treat the multiple If…Then…Else statements in an efficient manner. Consider the current formula code of: If IsNull({EmployeeAddresses.Address2}) then {EmployeeAddresses.Address1} else {EmployeeAddresses.Address1} + “, “ + {EmployeeAddresses.Address2} If we simply try to concatenate the City, Region Postal Code fields onto the end of the above statement, we end up only applying these fields when the Else condition is met and not the Then condition. To solve this problem, simply surround the entire If…Then…Else statement in parentheses. The result is the statement in the parentheses is output and the additional fields are concatenated onto the output statement regardless of which condition is met. Such as: (If IsNull({EmployeeAddresses.Address2}) then {EmployeeAddresses.Address1} else {EmployeeAddresses.Address1} + “, “ + {EmployeeAddresses.Address2}) + ChrW(13) + {EmployeeAddresses.City} + “, “ + {EmployeeAddresses.Region} + “ “ + {EmployeeAddresses.PostalCode} + ChrW(13) + {EmployeeAddresses.Country}

Exercise 6.10 – Edit the Home Address formula 1.

Open the formula called Home Address

Place parentheses around the entire formula Please refer to the paragraph above on why we do this.

At the end of the last line add “+”

We will now add the first carriage return. Expand the String categories in the Function Tree list. Find ChrW(x) and double click it

With the insertion bar in the function’s argument type “13” with no quotes. RIGHTARROW key to the end of the line and add “+”. Press enter to go to the next line

149

Crystal Reports 2008 Designer 1 Workshop Lesson 6: Creating and Using Formulas

Add {EmployeeAddresses.City} + “, “ + to this next line Remember to use the Field Tree list to find the field.

We now wish to add the Region field

Place a “+” at the end of the line. Press enter to go to a new line

We now can add the Postal Code, ChrW(13) and Country to the remaining formula. The final formula should look as follows: (If IsNull({EmployeeAddresses.Address2}) then {EmployeeAddresses.Address1 else {EmployeeAddresses.Address1} + “, “ + {EmployeeAddresses.Address2}) + ChrW(13) + {EmployeeAddresses.City} + “, “ + {EmployeeAddresses.Region} + “ “ + {EmployeeAddresses.PostalCode} + ChrW(13) + {EmployeeAddresses.Country} It is important to remember concatenation doesn’t provide spaces, so we must add spaces where needed. The ChrW(13) functions on their own lines only for visual effect.

10.

Check, Save and Close the formula

11.

Preview the report Notice the new lines are not displayed. This is because the formula field truncates any text that will not fit into the field display.

12.

Resize the formula field to a height large enough to display three lines of text

13.

Save the report (String Formulas) and preview Be sure you preview all pages to see if the formula is working correctly.

14.

Notice some of the addresses are no longer visible. This is because for some addresses the Region field is null. Edit the formula and from the Formula Workshop Toolbar change the Exceptions For Nulls drop-list to Default Values For Nulls

15.

Save the formula and report and then preview You will now see all addresses are now visible.

The final formula is going to display the Home Phone number field in a set format. Each employee’s phone number is stored in a string field with no formatting. The values are alphanumbers which are either 9 or 10 characters in length. A 10-digit phone number looks like this: XXXXXXXXXX. Employee contacts are located throughout the world. Because of this, the phone numbers do not all look the same. Some are nine characters and need to be formatted like this: XX-XXX-XXXX. Others are 10 characters and need to be formatted like this: (XXX) XXX-XXXX. To handle the formatting of the phone number we will first determine the number of characters in the field using the Length function. Length(Employee.HomePhone) will return the length of the string. Then, depending on the length, you can determine the format required for the field.

150

Crystal Reports 2008 Designer 1 Workshop Lesson 6: Creating and Using Formulas

Once you have determined the length of the field, there are two ways you can do the formatting: The first method uses the Subscript operator to break apart the field. It then adds the other characters by simply concatenating them to the elements of the phone field. The formula looks like this: if Length({Employees.HomePhone}) = 10 then “(“ + {Employees.HomePhone}[1 to 3] + “)” + {Employees.HomePhone}[4 to 6] + “-“ + {Employees.HomePhone}[7 to 10] else if Length(Employees.HomePhone) = 9 then {Employees.HomePhone}[1 to 2] + “-“ + {Employees.HomePhone}[3 to 5] + “-“ + {Employees.HomePhone}[6 to 9] else {Employees.HomePhone} This method uses operators that you are familiar with, but it is long and complex The second method uses the Picture function to set the formatting. Picture(string, format) uses X’s to designate characters from the string. The Picture(string,format) function uses the x as a placeholder for data elements in the string while the literal characters entered will be displayed in formatting the output. The formula looks like this: if Length({Employees.HomePhone}) = 10 then Picture({Employees.HomePhone} , “(XXX)XXX-XXXX”) else if Length({Employees.HomePhone}) = 9 then Picture({Employees.HomePhone} , “XX-XXX-XXXX”) else {Employees.HomePhone} This method is shorter, but you have to know how to use the Picture ( ) function to be able to utilize it

151

Crystal Reports 2008 Designer 1 Workshop Lesson 6: Creating and Using Formulas

Exercise 6.11 - Create a Formula to Format the Contact Phone Number Field Based on the Number of Characters 1.

Start a new formula named Home Phone. Add the if x then y else z operator to the formula

Click the in front of the Strings category in the Function Tree list. Double click the Length (str) function to add it to the formula text

Insert {Employees.HomePhone} field inside the parentheses

Move the flashing insertion bar outside the parentheses, then type = 10

Move the flashing insertion bar to the right of the word ‘then’ Press the ENTER key followed by the Tab key

Insert the Picture(string, picture) function from the Additional Functions/samp 1 (u25samp.dll) category, then include the remaining arguments. The result is shown below: Picture({Employees.HomePhone} , “(XXX) XXX-XXXX”)

Now you need to check for {Employees.HomePhone} fields that are nine characters in length and set the formatting for them. This part of the formula is very similar to what you already have. Instead of retyping it, you can copy it, and then change the parts that are different. Press CTRL + A to select the entire formula

Press CTRL + C to copy the formula. Move the flashing insertion bar after the word “else” then press ENTER

Paste the copied text on the new line using CTRL + V Change the second half of the formula as shown below: if Length({Employees.HomePhone}) = 9 then Picture({Employees.HomePhone} , “XX-XXX-XXXX”)

10.

Move the flashing insertion bar after the second “else” then press ENTER

11.

Double click the {Employees.HomePhone} field to add it to the end of the formula If the length is not 9 or 10 characters, Crystal simply prints the field without formatting.

12.

Check then save and close the formula. Add the formula to the right side of the report after the Home Phone field. Format the report like the example below

13.

Save the report (String Formulas) and preview

152

Crystal Reports 2008 Designer 1 Workshop Lesson 6: Creating and Using Formulas

The report should look like the following illustration. (Browse through the report to see how the different phone numbers are formatted – there should be examples of 7, 9, and 10 digit numbers):

TIP: It is possible to add functions or operators using the Auto Complete feature. To see a subset of functions or operators starting with a specific letter, simply type the letter and then press Ctrl+Spacebar. A list will appear with available items to choose from. Typing more letters will narrow the choices. If there is only one choice, Crystal will automatically select the function.

TIP: An expanded feature of Auto Complete is the ability to see a list of report objects by typing a “{“ in the Formula Editor. The list of objects includes tables, fields, formulas, parameters, running totals and sql expressions.

153

Crystal Reports 2008 Designer 1 Workshop Lesson 6: Creating and Using Formulas

Using Bookmarks to Navigate Through Formulas The formula editor Bookmark feature allows the user to navigate through long or complex formulas. Insert a book as a place holder when moving through a formula. Bookmarks can be used to mark: Frequently edited parts of a formula. With a bookmark, you can find them easily Major sections of a formula. Mark the beginning of a major section to find it quickly The beginning of if…then…else statement. This lets you jump rapidly to the next condition Bookmarks are easy to use. Just place the flashing insertion bar on the line where you want the bookmark, then click the Toggle Bookmark button in the toolbar. This button toggles bookmarks on and off. When you toggle a bookmark on, the Formula Editor places a blue circle to the left of the line.

Once you have created bookmarks, you can use the Next Bookmark and Previous Bookmark buttons to move between bookmarks in the formula. You can click the Clear All Bookmarks button to remove all the bookmarks in the formula. It is important to note that bookmarks used in a formula are not saved. When the Formula Workshop Editor is closed, all bookmarks are removed. At this point, you have not created any formulas that are very long or complex. Nevertheless, you can use the bookmark feature in a shorter formula to see how it works.

Exercise 6.12 – Navigate through a Formula with Bookmarks 1.

From Preview view, RIGHT click any field in the Home Phone column. Choose Edit Formula… from the Shortcut menu.

Position the insertion bar on the first line with the word “if” line

Click the Toggle Bookmark button A blue ellipse appears to the left of the line.

Add a bookmark to the beginning of the second “if” line

Use the Next Bookmark between the bookmarks

Click the Clear All Bookmarks close the formula

Save the report (String Formulas) and close it

button and the Previous Bookmark

154

button to move

button to delete all the bookmarks, then save and

Crystal Reports 2008 Designer 1 Workshop Lesson 6: Creating and Using Formulas

Challenge Exercise – Creating String Formulas To practice creating formulas you will create a report reflecting Customer Orders. The report will be grouped by CompanyName and OrderID. You only want to see the orders for 2007 and the customers in the USA. The Customer information needs to include the contact’s first initial and last name. In addition, if the address has data in two fields, you want to print both separated by a comma. The order group needs to include the orderdate, the shipdate and the number of processing days. You need to see, by order ID, each product ordered, how many, and a total price. You want a subtotal for each order and for each customer. Finally, if the customer has ordered more than $20,000 you want to mark that customer as an Excellent customer. If they have ordered more than $5,000, mark them as a good customer. If they have ordered less than $5,000, flag them as needing attention. 1.

Begin a new report as a blank report. Add the Customers, Orders, OrderDetails, and Products tables

Select for only orders in 2007 and only customers in the USA. Use the OrderDate field and the between operator for the year selection

Group on CompanyName. Create a second group on OrderID

Add the following fields to the Details section: ProductName, UnitPrice, and Quantity

For the Contact Name, you need to display only the first initial, then the Last Name. Create a formula called Contact that uses the subscript operator to display only the first initial and concatenate it to the Last Name. Try to create the formula on your own. If you need help, refer to the examples at the end of this lesson

Create a formula named Address with an If…then…else statement to print the Address1 and Address2 fields. If the Address2 contains data, print Address1 plus a comma and space, then Address2. If not, just print Address1. You need to use the IsNull() function. If you need help, refer to the end of this lesson

Resize Group Header 1 (CompanyName), add a text object and set the formatting to Can Grow. Add the required objects and text as shown below:

Make all the text bold and resize the text object to display all the data

155

Crystal Reports 2008 Designer 1 Workshop Lesson 6: Creating and Using Formulas

ÂŠ2008 Vision Harvest, Inc.

Challenge Exercise - Doing Calculations 1.

Create a Line Total formula to multiply the Quantity by the Unit Price

Place this field in the Details section after the Quantity field

Insert a summary on the Line Total formula and sum it for Group#1 and Group#2

Format the summaries to have two decimal places, with a floating currency symbol if necessary This should be the default and may not be necessary.

Challenge Exercise - Subtracting Dates 1.

In the OrderID group header, you need to add information regarding orders and shipping, and the number of processing days. Add a text object to Group Header #2 below the Group # 2 Name field

Resize the Text Object to about 2.5 inches and then format to Can Grow

In the new Text Object, type Invoice Number: plus a space. Click and drag the Group #2 Name field into the text object and drop it into place

Type Order Date: then a space

Insert the OrderDate field after the text, and then press ENTER

Type Shipped Date: then a space

Insert the ShippedDate field, and then press ENTER

Type # Process Days:, then a space

Create a formula, called Process Days subtracting the OrderDate from the ShippedDate. Insert that formula in the text object after the space

10.

Format the text field to display dates only (no time values) and format the Process Days formula to display with no decimals. Resize the text field to display all lines

11.

Move the field titles from the Page Header section into the Group Header #2 section below the order information

12.

Save the report. Name it Customer Orders.rpt. Preview the report

156

Crystal Reports 2008 Designer 1 Workshop Lesson 6: Creating and Using Formulas

Challenge Exercise – Using Nested If Statements 1.

Finally, you would like to flag each customer as to their status: Excellent, Good or Needs Attention. This is based on 2007 sales. If the customer has over $20,000 in sales, they are excellent customers. If they have between $5,000 and $20,000, they are good customers. Otherwise, they need attention. Create a formula called Customer Type with a nested If statement to satisfy this condition

Place the field in the right hand side of the Group Header #1 (CompanyName) section.

Format the report so it looks similar to the following illustration

Save the report (Customer Orders) and preview. Close it when you are finished

The report will look similar to the following illustration, but additional formatting has been applied: (Check throughout the report to be sure the Customer Type formula is working correctly.)

Help with the Previous Exercise Contact formula {Customers.ContactName} [1] + “. “ + Mid({Customers.ContactName},InStr ({Customers.ContactName},” “) + 1) The Mid (Str,Start,Length) function a common technique for sub scripting string values. The Length argument is optional and when not used the Mid function continues evaluating to the end of the string. The InStr (Str1,Str2) function is used for finding the location of a specific text value, in this case we wanted to know the position of the space separator between the FirstName and LastName fields.

157

Crystal Reports 2008 Designer 1 Workshop Lesson 6: Creating and Using Formulas

Address formula If IsNull ({Customers.Address2}) Then {Customers.Address1} Else {Customers.Address1} + “, “ + {Customers.Address2} Line Total formula {OrderDetails.Quantity} * {OrderDetails.UnitPrice} Process Days formula {Orders.ShippedDate} - {Orders.OrderDate} Customer Type formula If Sum ({@Line Total}, {Customers.CompanyName}) >= 20000 Then “Excellent” Else If Sum ({@Line Total}, {Customers.CompanyName}) >= 5000 Then “Good” Else “Needs Attention” Customer Info formula This formula was not part of the Challenge exercise, but could be used to replace Group Header # 1 text object: “Contact: “ + {Customers.CompanyName} + ChrW (13) + {@Contact} + ChrW (13) + {@Address} + ChrW (13) + {Customers.City} + “, “ + {Customers.Region} + “ “ + {Customers.PostalCode}

158

Crystal Reports 2008 Designer 1 Workshop Lesson 6: Creating and Using Formulas

ÂŠ2008 Vision Harvest, Inc.

NOTES

159

Crystal Reports 2008 Designer 1 Workshop Lesson 6: Creating and Using Formulas

ÂŠ2008 Vision Harvest, Inc.

NOTES

160

Crystal Reports 2008 Designer 1 Workshop Lesson 7: Conditional Formatting

ÂŠ2008 Vision Harvest, Inc.

Lesson 7 Conditional Formatting

161

Crystal Reports 2008 Designer 1 Workshop Lesson 7: Conditional Formatting

ÂŠ2008 Vision Harvest, Inc.

Lesson Objectives After completing this lesson, you will be able to: Use the Section Expert to format sections Learn how to insert page breaks, underlay sections, and do other section formatting Format sections on certain conditions Increase your control over section formatting by setting some options to occur only under certain conditions Format objects in a report conditionally Create formulas to change font, background color and/or borders under certain conditions Apply multiple conditional formats Set more than one condition on an object in the report Use the buttons to conditionally format fields Expand conditional formatting beyond the ability of the Highlighting Expert. Use formulas to conditionally format fields other than numbers and to perform more complex data comparisons.

162

Crystal Reports 2008 Designer 1 Workshop Lesson 7: Conditional Formatting

Formatting Sections In addition to the Format Editor for formatting fields, Crystal Reports has a Section Expert you can use to format sections of the report. The Section Expert has options for hiding sections, adding pages before or after, keeping sections together, etc. To open the Section Editor, choose Report/Section Expert… or RIGHT click the section name on the left side of the screen, then choose Section Expert… or click the Section Expert button on the Expert tools toolbar.

The left of the dialog box lists the available sections. You can set the formatting for any section in the report from this one location. Just select the section, and then check the options you want. The Common tab has several unrelated features. The Paging tab allows you to set page breaks and reset page numbering. It also has new feature (Crystal Reports 2008) to control the page orientation for a specific section.The Color tab lets you set the background color for the section. The following table indicates section format options and their descriptions:

163

Crystal Reports 2008 Designer 1 Workshop Lesson 7: Conditional Formatting

Item Hide (Drill-Down OK)

Suppress (No Drill-Down) Print at Bottom of Page

Keep Together

Suppress Blank Section Underlay Following Sections

Format with Multiple Columns

Read-only

Relative Positions

Reserve Minimum Page Footer New Page Before / New Page After

Reset Page Number After Orientation Background Color

Section Formats Description Common Tab This option hides a section during printing, but allows a user to open the section while in preview by double clicking on a group summary value relating to the hidden section Suppress works like hide except the section is permanently suppressed and drill-down capabilities are not allowed Turning on this value forces the section to print as far down the page as possible. The section cannot be placed lower on the page than the next section to print beneath it This option refers to keeping a section together in relation to page breaks. If there is not enough room on the page to print the entire section, then the entire section is moved to the next page When all fields in a section are blank, the section does not display When you choose this feature, the section prints on the page at the same spot the section below it begins to print. This is a great tool for displaying a group graph next to the group data This option allows you to have data display in columns instead of straight down the page. Checking this option will create a new tab labeled “Layout”. The Layout tab offers formatting options for multiple columns. Only applies to Details section When you want to lock the section from any addition formatting, select this item. Selecting this option will also suppress all formatting options for this section Use this option to lock an object next to a grid-object such as a crosstab. If the crosstab grows, any object next to it will be moved in relationship to the size of the crosstab

Paging Tab This option only applies to the page footer section. Use this option when you need to gain space by removing any unused page footer space This is how you establish page breaks. You can choose to set the page break before or after the section prints. For New Page after you can also specify a page break after a certain number of records have displayed. This feature is only available to the Details section This choice resets the page numbers to 1 after a section has been printed Sections can now be forced to print in either landscape or portrait Color Tab The background color defines a color for the fill for each section. The background fill is not adjustable and always adjusts itself to the width of the page and the defined height of the section

164

Crystal Reports 2008 Designer 1 Workshop Lesson 7: Conditional Formatting

ÂŠ2008 Vision Harvest, Inc.

Suppose you received some new business requirements for the sales report you created earlier. The new requirements indicate each country should be on its own page. Since page breaks are section formats, you need to open the Section Expert to set the feature.

Exercise 7.0 - Set Page Breaks in the Group Header 1.

Open the Grouping.rpt report you created earlier in class

RIGHT click the section name for the Group Header #1 section, then choose Section Expertâ&#x20AC;Ś Or Click the Section Expert button in the toolbar The Section Expert opens. You do not have to return to Design view to do this formatting.

Make sure the Group Header #1 section is selected

Click the New Page Before check box to add a check mark

Click OK

Using the Save As command, save the report and name it Conditional.rpt. Preview it Notice the first page of the report is blank except for the title and graphic image. Crystal has put a page break before the first group, making the first page blank except for the items n the Report Header

Formatting Sections Conditionally In previous lessons, you used the Format Editor to format lines, boxes, fields and text objects. In this lesson, you have learned to format sections. However, there will be times when you need to format only under certain conditions. For example, you just added a page break to the group header, but you do not want the page to break before the first group, leaving the first page blank. In the Format Editor and other dialog boxes, you see check boxes next to many options. When you click the box to add a check mark, you are turning on absolute formatting. If you check the box, the format is applied to all values in the field. If the box is not checked, the format is not applied. Some dialog boxes also contain drop-down list boxes that apply formats. Color formats are a good example of this. Again, these drop-down lists apply absolute formats to all values in the field. However, many formatting features have formula buttons next to them. You can click this button to open the Format Formula Editor and use a formula to tell Crystal Reports when to apply the format. In other words, either checking or not checking the box, or choosing from a drop-down list, absolutely turns a feature on or off. Adding a Boolean or Attribute formula by clicking this button conditionally turns the feature on or off. There are two situations to consider when you apply conditional formatting to a section or a field: check mark formats (toggle the format on or off ) or list/text box formats that have multiple choices.

165

Crystal Reports 2008 Designer 1 Workshop Lesson 7: Conditional Formatting

For the check mark type formats, there are only two situations, on and off. For conditional statements, there are also only two situations, true and false. Therefore, for these types of formats, you do not have to type an entire if…then… else statement. You only have to type the test. Crystal will turn the format on if the test evaluates to true and NOT turn the format on if it evaluates to false Drop-down lists and text boxes involve more than two choices, possibly an infinite number of things you can do. With these types of formats, you must type an if… then…else statement for the conditional format and specify what you want Crystal Reports to do if the test is true or false

When you click the button, the Formula Editor opens. After you have accepted a formula, Crysy to the format dialog box and the button displays in maroon instead of blue tal Reports returns you with a tilted pencil

. This indicates that a condition has been set on the feature.

For this report, you want to conditionally format the Group #1 section to not break before the first group. Ask yourself, what does the first group in the report have, that none of the other groups have? The answer is, it has the first record in the report. Therefore, you can tell Crystal Reports to insert the page break in the group header, but not on the first record. Crystal Reports has a function called OnFirstRecord that identifies the first record in the report. If you create a formula indicating NOT OnFirstRecord, you tell Crystal Reports to format only for groups not containing the first record.

Exercise 7.1 - Conditionally Format the Group Header Section 1.

Return to Design view. RIGHT click the Group Header #1 section name and choose Section Expert…

Click the button after the New Page Before check box The Formula Editor opens.

Type Not, then press the SPACEBAR

Open the Print State category in the Function Tree list

Double click the OnFirstRecord function

Save and close the formula, then click OK

Save the report (Conditional) and preview the report. The first region should now be on the first page of the report

166

Crystal Reports 2008 Designer 1 Workshop Lesson 7: Conditional Formatting

The Conditional report has several countries which either don’t have regions or only one region. This creates what appears to be a duplication of subtotals. The report would appear more clear in it’s presentation if the Region subtotals were suppressed when this condition exists. Additionally, for those Countries that don’t have a region, we want to suppress the Group #2 Header. To achieve this goal we will need to conditionally suppress Group #2 Footer section.

Exercise 7.2 - Conditionally Suppressing a Section 1.

From Design, open the Section Expert and make sure Group Footer #2 is selected

Click the Workshop

Expand the Summary function category in the functions list, expand the Count sub-category and then double click in the second item: Count (fld,condFld)

Build the following formula:

button for the Suppress (No Drill-Down) option to open the Formula

DistinctCount ({Customers.Region},{Customers.Country}) < 2 We have manually created a summary that counts the number of regions for Group #1. If there are less than two Regions, the formula returns a True value and suppression is turned on. We use the DistinctCount function to deal with the situation where multiple customers exist for one or null regions. The DistinctCount function ensures we don’t count the same region or null region more than once. 5.

Save the formula and test it by returning to Preview mode

Now that we have the Group #2 Footer conditionally suppressed, we want to now suppress Group # 2 Headers that don’t display a Region name since the Region field is null. Return to Design view and open the Section Expert for the Group #2 Header section

For the Suppress (No Drill-Down) open, click the formula:

button and enter the following

IsNull({Customers.Region}) This formula will return a True anytime the Group #2 is based on null Region field. 8.

Save the formula, close the Section Expert and return to Preview The Group #2 Header section should now be suppressed for those groups without an actual Region

167

Crystal Reports 2008 Designer 1 Workshop Lesson 7: Conditional Formatting

Conditionally Formatting Fields Just as you conditionally format a section, you can conditionally format a field. You have already done some conditional formatting with the Section Expert. With formulas, you can conditionally format any type of field and you can make any kind of test you need, as long as you can specify the condition using the Formula Editor. To apply conditional formatting using a formula, open the dialog box containing the format you want to apply, and then click the x+2 button to open the Formula Editor. Type a test only, if the format is a check mark, or type an if…then…else statement if the format is a drop-down list or a text box. TIP: You can conditionally format the font, background color, or border by using the Highlighting button on the Experts toolbar. This feature makes conditional formatting easier than writing formulas. However, it does have limitations. Because of the limited set of features we no longer include it in this lesson. If you want more information on the tool, please reference in the Crystal Reports help file. In the previous lesson we created the Number Formulas report. Suppose we want to make the following changes: The Discount formula should display a “-” when the discount is zero (0), but not show the percentage symbol The Product Name (Group #1 Name field) should have a shaded background when there were any orders with a quantity 0f 45 items or more For any record with 45 or more items ordered, the font should be red

Exercise 7.3 - Conditionally Display a Percent Symbol 1.

Open the Number Formulas report created in the Creating and Using Formulas lesson

Select the Discount formula located in the Details section. RIGHT-MOUSE click and select Format Field...

Select the Customize... button

Un-check the Suppress if Zero option and set the Show Zero Values as: drop-list to “-”

Click OK to save your work and OK again to close the Format Editor and then Preview Notice the new format of the Discount formula shows the “%” symbol on all values, including zero values. We want to suppress the “%” for zero values, but show it when there is a value.

Repeat Steps 2 and 3 and select the Currency Symbol tab in the Custom Style dialog

168

Crystal Reports 2008 Designer 1 Workshop Lesson 7: Conditional Formatting

ÂŠ2008 Vision Harvest, Inc.

Click the X+2 button for Enable Currency Symbol option The Formula Workshop - Format Formula Editor opens. Notice the commented values in the formula text area. Anytime you see this, you are being shown the possible valid attributes your formula can output. This also means you will need to write an If...Then...Else formula.

Enter the following formula syntax: If {@Discount} = 0 Then crNoCurrencySymbol Else crFloatingCurrencySymbol Crystal treats percentage symbols the same as it does currency symbols.

Save and close the formula, continue selecting OK until all dialog windows are closed and then Preview You should now see the Discount formula properly formatted

Exercise 7.4 - Conditionally Display a Background Fill 1.

Return to Design view and select the Group #1 Name field in the Group Header #1 section. RIGHT MOUSE click and select Format Field:

Select the Border tab and then select the X+2 button next to the drop-down list for the Background: The Formula Workshop opens. It is important to understand this X+2 button is related to the color drop-list and not the Background: check box. This means our formula must out-

put a color. 3.

Create the following formula: If Maximum ({OrderDetails.Quantity},{Products.ProductName}) >= 45 then crSilver else crNoColor This formula looks for the highest (maximum) quantity value in each ProductName group and compares it to the criteria. We use the crNoColor function to ensure no other default colors are applied. In the case of background fills the default color is black.

Save and close the formula, choose OK and Preview You will find some Group Name fields are shaded and some are not. Make sure the shading occurs only when it is suppose to.

169

Crystal Reports 2008 Designer 1 Workshop Lesson 7: Conditional Formatting

Exercise 7.5 - Conditionally Formatting Font Colors for Multiple Fields 1.

Return to Design view multiple select all the data fields in the Details section

RIGHT-MOUSE click on any of the selected fields and choose Format Objects...

Select the Font tab and click on the X+2 button to the right of the Color: drop-down list

Create the following formula: If {OrderDetails.Quantity} >= 45 then crRed This is a record level evaluation, so a summary function is not required. Also notice there isn’t an Else operator. The default color for fonts is Black, so we don’t have to put in an Else.

Save and close the formula and Preview The report should look similar to the example below:

170

Crystal Reports 2008 Designer 1 Workshop Lesson 7: Conditional Formatting

Challenge Exercise – Section Formatting and Conditional Formatting You have already created a sales report for the first quarter of 2004. Suppose you want each month to start on a new page. You also want the currency symbol to appear for only the first record in each daily group. The conditional formatting you do for this report is similar to, but not exactly the same as what you did during class exercises. See if you can determine the correct formulas on your own. 1.

Open the Orders for First Quarter 2008 Grouped and Summarized.rpt report. Return to Design view.

Open the Section Expert and add a new page AFTER the Group Footer #1 section.

Preview the report. You should see the grand total by itself on the last page of the report.

Using the same logic you used in the class exercise, apply a conditional format to turn off the page break after the last group in the report. If you need help, refer to the next page for the correct formula.

Next, you want to print the currency symbol conditionally to display only on the first record in each daily group. Remember the exact dates in the OrderDate field for each record in a daily group are the same, but when the group changes the date is different. Because of this, you can specify the {Orders.OrderDate} <> Previous({Orders.OrderDate}) to determine the first record in each group. You must also use the OnFirstRecord function to specify the first record in the report. Separate the two conditions with an OR operator. Again, if you need help with the formula, refer to the next page for the correct formula. You must RIGHT click the OrderAmount field, and then choose Format Field… Click the Customize button on the Number tab, then click the Currency Symbol tab, then click the Symbol: command to apply the formula.

after the Enabled Currency

Once the report’s conditional formatting is working correctly, save the report as Orders for First Quarter 2008 Conditionally Formatted.rpt and close it.

Help for the Previous Exercise: Conditional Page Break Formula Not OnLastRecord Conditional Currency Symbol Formula If OnFirstRecord or {Orders.OrderDate} <> Previous ({Orders.OrderDate}) Then crFixedCurrencySymbol Else crNoCurrencySymbol

171

Crystal Reports 2008 Designer 1 Workshop Lesson 7: Conditional Formatting

ÂŠ2008 Vision Harvest, Inc.

The finished report should look similar to the following illustration (Be sure to check to see if each month starts on a new page):

172

Crystal Reports 2008 Designer 1 Workshop Lesson 7: Conditional Formatting

ÂŠ2008 Vision Harvest, Inc.

NOTES

173

Crystal Reports 2008 Designer 1 Workshop Lesson 7: Conditional Formatting

ÂŠ2008 Vision Harvest, Inc.

NOTES

174

Crystal Reports 2008 Designer 1 Workshop Lesson 8: Creating Summary Reports and Charts

ÂŠ2008 Vision Harvest, Inc.

Lesson 8 Creating Summary Reports and Charts

175

Crystal Reports 2008 Designer 1 Workshop Lesson 8: Creating Summary Reports and Charts

ÂŠ2008 Vision Harvest, Inc.

Lesson Objectives After completing this lesson, you will be able to: Create a one page summary report Make succinct, easy to read summary reports with all the information on a single page Drill Down in the summary to view the detail Allow users to view the underlying data by double clicking the summary number for which they want details Produce meaningful graphs Charting is a great way to get your point across to others. Create graphs quickly with the Chart/Graph Expert Format graphs Add pizzazz to your graphs by controlling the formatting

176

Crystal Reports 2008 Designer 1 Workshop Lesson 8: Creating Summary Reports and Charts

ÂŠ2008 Vision Harvest, Inc.

Creating a Summary Report When you create a report, you want to produce an attractive product easy to read and understand. You should avoid reports which look like those ancient green bar reports, with row after row of data. Generally, reports only need to show the bottom line numbers or summary figures. People only want to see the rest of the records when the summary value needs to be validated. In these situations, you need to have a summary report and hide the underlying data. Crystal Reports makes creating a summary report an easy process to perform, once the report has been grouped and summarized. The shortcut menus for each section contain a Suppress option to hide a section completely if you do not need it at all. There is also a Hide (Drill Down OK) option to hide a section, but allows the viewer to double click a summary value to view the underlying data making up the value. Once you have hidden the sections you do not wish to see in preview, you have a summary report. If you were summarizing sales by month, the summary report might look something like this:

177

Crystal Reports 2008 Designer 1 Workshop Lesson 8: Creating Summary Reports and Charts

ÂŠ2008 Vision Harvest, Inc.

To create a summary report and work with drill down, you are going to create a new report. This report is a monthly sales order report by day of week for 2007.

Exercise 8.0 - Create a Summary Report 1.

Create a new report. In the Database Expert, select the Orders table.

Add the OrderID, OrderDate and OrderAmount fields to Details section.

Filter the records to select only 2007 orders based on the OrderDate field.

Create the first group of the report to display monthly orders. Use the Orderdate field as the source for the group and use the Options tab to customize it as follows: a)

Set The Section Will be Printed option to For each Month.

b) On the Options tab, turn on the option Customize the Group Name Field and select the option Use a Formula as Group Name. Select the X+2 button and use the following formula as the group name: MonthName (Month ({Orders.OrderDate})) 5.

The second group is a little more complex and requires three steps to achieve the final result. The group needs to display the data for the days of the week, Sunday through Saturday. a)

Step 1: In order to do this you must first create a formula to organize the day of the week into values 1 to 7 since Group by Day of Week is not a grouping option in the group set-up list. Create a formula called Week Day with the following structure: DayOfWeek ({Orders.OrderDate})

b) Step 2: Create a group based on the Week Day formula you just created. View the report. Notice that the report displays the days of the week as values 1 to 7. This is not a meaningful display of the data; so further modification of the group is needed. c)

Step 3: Format Group #2 to display the day of week name instead of values 1 to 7. To accomplish this you will need to change the group. Right-click the section name Group Header #2 and select Change Groupâ&#x20AC;Ś In the Option tab check on Customize Group Name Field. Additional options become enabled, select Use a Formula as Group Name option and then click on the X+2 button. Add the following formula to the Formula Editor: WeekdayName ({@Week Day})

Insert summaries for both groups and a grand total.

Copy the group name fields from the group headers and paste them to their respective group footers and to the left of the summaries.

Format OrderDate field in the Details section to display the date only.

Make Group Header #2 bigger and move the Field Headers into the section below 178

Crystal Reports 2008 Designer 1 Workshop Lesson 8: Creating Summary Reports and Charts

the Group Name field 10.

We are now ready to hide several sections in order to make the report a summary report. Using the Section Expert hide the following sections: a)

Group Header #1

Group Header #2

c) Details d) 11.

Group Footer #2

Save the report as Summary.rpt and preview the report. The report should look similar to the illustration on the previous page.

Applying the Drill-Down Feature The powerful benefit of using the Hide feature is you can drill down to view the data supporting the summary. When you double click a summary number to perform a drill down, Crystal Reports opens a new tab to display the detailed data. You can have multiple levels of drill-down tabs in the report, one for each level of grouping. Several drill-down tabs can be created. Once Crystal Reports creates a tab, you can save it with the report by simply saving the report. If you only wanted to verify the summary data and no longer need the tab, you can delete it by clicking the Delete button in the Drill-Down tab area. If you perform any section formatting, Crystal Reports warns you that you are about to lose the drill-down tab, usually, you will select OK. Drill-down tabs are easy to recreate.

Exercise 8.1 – Drill-Down to View the Detail 1.

Move the mouse pointer around on the Preview screen. When you are pointing to any of the group names or summary fields, the pointer changes shape to a magnifying glass.

With the magnifying glass pointer, double click the group name for May. Crystal Reports creates a new tab with the summary information for the day of week May.

Double click on Tuesday. Crystal displays a tab with the customer details for those orders in May 2007 which were placed on a Tuesday.

To close the tabs, click the button in the navigation area. Close both the tabs for Tuesday and May. Notice the second drill-down into Group #2 is not very attractive. Crystal doesn’t know we don’t want to see this information at this level. In the next exercise we will address the solution to this problem.

179

Crystal Reports 2008 Designer 1 Workshop Lesson 8: Creating Summary Reports and Charts

ÂŠ2008 Vision Harvest, Inc.

Point to the Grand Total figures in the Report Footer. Notice the mouse pointer does not display as a magnifying glass. You cannot drill down on grand totals, only on summary totals or subtotals.

Save the report (Summary) but do not close it.

Applying the DrillDownGroupLevel In the previous exercise some of the drill-down group headers were not very attractive. Using the DrillDownGroupLevel function we are able to test the drill down level being displayed and make formatting changes accordingly. This is an important enhancement for drill down reports with multiple groupings. We can use the output of this function to conditionally suppress sections depending on the level being displayed. We will now use this function to correct the display issues we experienced in the previous exercise.

Exercise 8.2 â&#x20AC;&#x201C; Incorporating Conditional Formatting in Drill-down reports 1.

Create a new formula named Drill-Down Level

In the Function Tree list, expand the Print State category and scroll down to the bottom of the category to locate DrillDownGroupLevel. Double click on it.

Save, close and place the formula once in the Group #1 Footer and once in the Group #2 Header. Preview report. Notice the numbers at each of the levels within the group section. Knowing the level number gives us a value to test for when applying conditional suppression. Top level is always 0.

We can now apply the conditional suppression to the Group #2 Header. Right-click in the section area for the Group #2 Header and select Section Expert.

Click the

In the formula text area apply the following formula:

button next to the Suppress (No Drill-Down) option.

DrillDownGroupLevel = 1 Whenever the group level is equal to 1 for the Group #2 Header, suppress is turned on and the section will be suppressed. 7.

Save the formula and preview the report. You should now see improved formatting when drilling down into Group # 2 as shown in the example below.

Save the report (Summary) but do not close it.

180

Crystal Reports 2008 Designer 1 Workshop Lesson 8: Creating Summary Reports and Charts

ÂŠ2008 Vision Harvest, Inc.

Producing Charts After creating a comprehensive report where records have been grouped and summarized, you may wish to illustrate the information with Charts. Charts are simple to create and usually, but not always, require at least one group be defined. To create a chart you can choose Insert /Chartâ&#x20AC;Ś or click the Insert Chart button on the Insert Tools Toolbar. After you drop the chart frame on the report, you either see the default chart or the Chart Expert dialog box.

If you have:

You will see:

Action to take:

No groups in the report

The Chart Expert data tab

Groups with no subtotals

The Chart Expert data tab

Groups with subtotals

The default chart. A 2D vertical bar chart displaying the group subtotals

Complete the steps for the expert and click OK Complete the steps for the expert and click OK Right click on the chart and select Chart Expert to make changes to the chart

181

Crystal Reports 2008 Designer 1 Workshop Lesson 8: Creating Summary Reports and Charts

ÂŠ2008 Vision Harvest, Inc.

If the Chart Expert dialog box displays, it will assist you in creating the chart.

The Chart Expert is a tabbed process. As you complete each step, you can click the next tab to continue to the next step. The first step is to choose the chart type. Crystal Reports has several types of charts as you can see from the above illustration. The most common charts are bar or pie charts. Use bar charts to compare like data. Pie charts are usually used to show pieces of a whole, such as sales figures for regions in the entire country. Each chart type has subtypes letting you further refine the chart. Once you select the type, click the appropriate button on the right to choose the desired subtype. Using the Data tab, you can chart data based on group summary information, or based on details in the records within a group, or based on information summarized in a cross-tab, or on OLAP data. Depending on which button you choose in the Layout section, you see different choices in this tab. However, each set of choices is going to require you to decide which field data to chart.

182

Crystal Reports 2008 Designer 1 Workshop Lesson 8: Creating Summary Reports and Charts

Chart Data Options Option or Item

Description

Layout section

The chart can be set to display data from four sources: • Group - the chart displays summary field values. • Advanced - the chart displays individual record values for fields or formulas • Cross-tab – the chart displays values from a cross-tab. • OLAP – the chart displays data from an OLAP cube

On Change of:

This box defines the field chosen as a condition for plotting values in the chart. Points are plotted whenever the value in this field changes This box shows the value in the specified field. In other words, this box determines the size of the pie pieces, height of the bar, etc

Show

183

Crystal Reports 2008 Designer 1 Workshop Lesson 8: Creating Summary Reports and Charts

ÂŠ2008 Vision Harvest, Inc.

The Axes tab will allow you to set the options for gridlines, axis data range values or the number of data divisions. The axes tab will not be available for all chart types.

Next is the Options tab. Here is where you can show data labels and/or values, customize settings for marker or bar size. There are also legend controls for displaying, hiding and changing its location.

184

Crystal Reports 2008 Designer 1 Workshop Lesson 8: Creating Summary Reports and Charts

ÂŠ2008 Vision Harvest, Inc.

The next tab is the Color Highlight tab. Options on this tab allow you to format the chart colors based on chart value fields.

The Text tab lets you add and format text objects in the chart. Crystal adds some default text automatically. To delete or modify the default text, click the Auto-text check mark for the one you want to change, then delete the text or edit it in the associated text box.

185

Crystal Reports 2008 Designer 1 Workshop Lesson 8: Creating Summary Reports and Charts

To help you understand the fields you need for charting data, consider the following example. Suppose you have a report showing last year’s sales for customers. The report is grouped on Region with a total sales summary field in the group footer. If you want to chart the total Last Year’s Sales by region, use the Insert Chart button. You would enter the Region field into the On Change Of: text box. Then enter the Sum of Last Year’s Sales field from the Group Footer in the Show: text box. The chart would display a bar for each region indicating the total sales for each region. The region name would appear below each bar. From this same report, you could chart the total sales for each customer, even though you do not have a field calculating that value. Choose the Advanced layout to accomplish this. You would place the Customer Name field in the On Change Of: box and the Order Amount field from the Details section in the Show Value(s): list. The chart would show a bar for each customer indicating the sum total of that customer’s sales. The customer name appears below each bar. Once you are finished defining the chart, click the OK button. Crystal Reports updates the chart with the options you have selected. Some repositioning and sizing may need to be done. NOTE: If you are in Crystal Reports’ Design Window, the chart data does not look quite right. This is because the Design Window only shows you a sample of the chart. Once you go to Crystal Report’s Preview Window, the chart changes to represent the actual report data.

Exercise 8.3 - Create a Bar Chart 1.

You should still have the Summary.rpt report open. Return to Design view.

Now, you are ready to create the chart. Click the Insert Chart chart frame into the Report Header section.

Right click the chart and select Chart Expert... and then click the Type tab.

Select Bar in the Chart Type: list.

Turn on the option to Use Depth Effect.

On the Data tab make sure the Orders.OrderDate field shows in the On change of: box, and the Sum of Orders.OrderAmount field shows in the Show: box. Then click the Text tab.

Turn off the Auto-text check mark for the Title: then type Monthly Sales Orders.

Turn off the Auto-text check mark for Group Title and Data Title and delete the text in the boxes next to the options.

Click OK in the Chart Expert dialog box. Notice the chart displays sample information, not the data you charted

10.

Save the report (Summary) and preview.

186

button and drop the

Crystal Reports 2008 Designer 1 Workshop Lesson 8: Creating Summary Reports and Charts

The chart should look like the following illustration:

Editing Charts Once a chart is inserted on the report, it is an object like any other. You can move, resize or edit it by selecting it first. When you RIGHT click the chart, the Chart Expert… menu choice reopens the Chart Expert dialog box for you to make changes. You can also add a border around the entire chart by selecting the Format Chart… menu choice.

Exercise 8.4 - Edit a Chart 1.

Make sure the chart is selected.

RIGHT click the chart, then choose Format Chart…

Add a single line border around the chart and turn on the Drop Shadow feature. Choose OK when finished

RIGHT click the chart again, then choose Chart Expert…

Click Pie in the Chart type: list. Make sure the Use Depth Effect is checked.

Click OK.

You can also drill down on a chart. Just like summaries, charts represent the calculated data of detailed information. Click any piece of the pie or any bar, etc., to drill down to see the details.

Exercise 8.5 - Drill Down from the Chart 1.

Double click on any piece of the pie to view its data.

When finished looking at the tab, click the button on the Drill-Down tab.

Save the report (Summary).

187

Crystal Reports 2008 Designer 1 Workshop Lesson 8: Creating Summary Reports and Charts

Formatting Charts To format a chart you right-click the chart and choose the required formatting option. Additionally, objects in the chart can be repositioned or sized while viewing the report in the preview mode. There are five primary tools useful in formatting a chart: Chart Expert – There are several options available in this formatting tool. However, we use this mostly to define the chart type (such as pie or bar), what data the chart will use to display and the chart display titles Format Chart – This option allows us to change the border style and any hyperlinks Format Background – Apply color, patterns, gradient, textures or pictures to the back ground of the chart. The border tab allows us to add and format a border around the chart. These options are different from those under Format Chart – Border Chart Options – This tool allows us to define how the information is displayed and is the most versatile of all the tools. Chart Options has seven tabs (some tabs are inactive for some chart types) : Appearance – Allows us to set the appearance of the bars or slices of a pie chart, including overlap, gap width and 3D effects Titles – This option allows us to quickly modify or add titles that were inserted when the chart was first created Data Labels – We can display data labels on the chart and modify their locations and format Legend – This option lets us format the legend layout and border style and the layout of the text and markers Gridlines – This option allows us to format the grid display for chart types which use a grid to represent numerical data such as bar or line charts, but not for pie charts. There are several options in this tool which provide us the ability to display the grid in many ways Axes – The options on this tab allow us to hide or display vertical and horizontal axes (not available for all chart types). For certain charts, a Dual Axes option will be displayed Multi-Axes – Some chart types allow the ability to render a chart to include two numeric axes. This option is only active if the Dual Axes option is checked on in the Axes tab Auto Arrange – This is a simple tool which when selected will attempt to rearrange the chart into a cleaner view. It looks at the data being displayed and makes a decision on how to better position value labels or resize the grid. This is a useful tool to apply once most other formatting options have been implemented. With any auto tool, it is a good idea to save your report before applying auto arrange although it can be undone

188

Crystal Reports 2008 Designer 1 Workshop Lesson 8: Creating Summary Reports and Charts

Using the Chart Options We are now ready to make formatting changes to the chart. When you RIGHT click a chart, then choose Chart Options, Crystal opens the Chart Options dialog box.

Here you can customize several different features on the chart. The features and options in this dialog box vary according to the type of chart you have selected.

Exercise 8.6– Modify the Chart Options 1.

RIGHT click the chart, then choose Chart Options.

On the Appearance tab, change the pie tilt, depth and rotation. Notice that the sample chart on the left changes to reflect your choices.

Try exploding the pie, but when you are finished, reset it to 0.

Click the Data Labels tab. Make sure Show Labels and Show Leader Lines are checked and Outside Slices is selected. Click the Show Values check box to turn on display of values. Then select the Outside Slices option if necessary. Click OK when finished.

Save the report (Summary) but do not close it.

NOTE: Depending on the type of chart you have defined, you may see other choices in the Chart Options menu. For example, if you have a Bar chart, there is a Grid… option.

189

Crystal Reports 2008 Designer 1 Workshop Lesson 8: Creating Summary Reports and Charts

Modifying Individual Objects in the Chart At this point in our chart development, we are ready to format individual items in the chart. It is important to recognize the chart as a series of individual objects with their own set of formatting options and settings. By selecting an item, those options will become enabled for our use. In this next exercise we want to format the label values, change the chart title and detach an individual pie slice.

Exercise 8.7 – Modify the Chart Options 1.

Select one of the month labels for an individual slice and then right-click. Choose Format Data Label…

A Formatting dialog box opens with several options to choose from. Make the font size 8pt, make the font bold and change the color to a color of your choice. Click OK when done. To change the font, the selected item must be the label and not the piece of pie. To change the pie slice color, the selected item must be the slice of pie. Notice the changes applied to all slice labels and not just the one selected.

Note: The option Edit Axis Label… allows us to change the label for an individual slice or bar. The change is reflected in the Legend, but does not change the value in the report itself.

190

Crystal Reports 2008 Designer 1 Workshop Lesson 8: Creating Summary Reports and Charts

Select one of the value labels and then right-click. Choose Format Data Label… Click on the Number tab. Change the Category to Currency, Decimal Places to 0 and $ Windows System Default for the currency symbol. Click OK when finished.

Viewing the chart in Preview, select the Monthly Sales Orders title box. Notice by selecting it, it is now highlighted and we can move or resize it to our requirements. Right-click the title and choose Format Title…

In the Text tab dialog box change the text to 2007 Monthly Sales Orders. Click on the Font tab any font adjustments necessary. Click OK.

Save the report (Summary).

191

Crystal Reports 2008 Designer 1 Workshop Lesson 8: Creating Summary Reports and Charts

Applying Chart Templates Chart Templates are an excellent way to quickly format a chart or to save a format you have defined and apply it to new charts. To save a chart as a template, right-click the chart and choose Save as Template. A Save As dialog box opens and it prompts you to save the chart to a specific directory called User Defined. It is important to use this default directory since this is the directory Crystal will look to when applying your saved templates. To apply a chart template to an existing chart, RIGHT click the chart, and then choose Load Template… from the shortcut menu. To see the templates you have defined, go to the User Defined category and you will see any templates you have saved. The other categories contain several basic chart types.

Exercise 8.8 – Save a Chart as a Template 1. RIGHT click the chart, then choose Save as Template… 2. The Save As dialog box opens prompting you for a file name for the chart template. Name the chart Pie Chart1. Notice the file is being saved to: C:\Users\<user>\AppData\Roaming\Business Objects\2008\ChartSupport\Templates\ User Defined This is the default location on Windows Vista. Windows 2000/XP will be slightly different. 3. Click Save when done and return to Preview.

192

Crystal Reports 2008 Designer 1 Workshop Lesson 8: Creating Summary Reports and Charts

Exercise 8.9 – Choose a Chart Template 1.

RIGHT click the chart, then choose Load Template…

Select one of the chart types in the Basic category. Click OK.

Don’t like the results? It is easy to fix. Click the Undo

Right-click the chart and then select Chart Expert and change the chart to a doughnut chart. Notice the chart no longer reflects your formatting changes.

RIGHT click the chart again, and choose Load Template…

Click the User Defined category. Select the template we saved earlier. Click OK when done. The chart should now reflect the template formatting we defined earlier.

Save the report (Summary).

193

button on the toolbar.

Crystal Reports 2008 Designer 1 Workshop Lesson 8: Creating Summary Reports and Charts

Challenge Exercise – Creating a Summary Report Create a summary report from the first quarter orders report you created in an earlier challenge exercise. 1.

Open the Orders for First Quarter 2004 as Percentages.rpt report.

Move the text box and the summary field in Group Footer #1 to the right side of the report.

Move the Group #1 Name field from the Group Header #1 into the Group Footer #1 section. Resize the field to be about 1” wide.

Move the column titles from the Group Header #2 section into Group Header #1, and then delete the second group. HINT: RIGHT click the group header, and choose Delete Group.

Summarize the OrderAmount field using the Sum function for Group #1. Add a text box to the left of the summary field that reads Total Monthly Orders:.

Format the summary field with a top single line border. Format the text box to be bold and right aligned.

Hide the Group Header #1 section and the Details section.

Save this report and name it Orders for First Quarter 2004 Summarized.rpt.

Challenge Exercise - Charting Now create a pie chart showing the order totals on the pie pieces and the percentages in the legend. 1.

Click the Insert Chart

Drop the chart frame into the Report Header.

Right click on the chart and select Chart Expert.

Create a pie chart on the change of OrderDate, showing the Sum of the Order Amount.

Add the title text Order Totals by Month and a subtitle For the 1st Quarter 2008.

Using the various formatting options available to you, format the chart to look like the sample on the next page.

Save the report (Orders for First Quarter 2008 Summarized) and preview the report.

button .

194

Crystal Reports 2008 Designer 1 Workshop Lesson 8: Creating Summary Reports and Charts

ÂŠ2008 Vision Harvest, Inc.

195

Crystal Reports 2008 Designer 1 Workshop Lesson 8: Creating Summary Reports and Charts

ÂŠ2008 Vision Harvest, Inc.

NOTES

196

Crystal Reports 2008 Designer 1 Workshop Lesson 9: Exporting Your Reports within Your Organization

ÂŠ2008 Vision Harvest, Inc.

Lesson 9 Exporting Your Reports within Your Organization

197

Crystal Reports 2008 Designer 1 Workshop Lesson 9: Exporting Your Reports within Your Organization

ÂŠ2008 Vision Harvest, Inc.

Lesson Objectives After completing this lesson, you will be able to: Understand export formats and destinations Learn the ins and outs of exporting a Crystal Reports report to another format Use a Web Format for Crystal Reports One of the great advantages of Crystal Reports is how it works with the Internet. Export reports to HTML format, and open them with a Web browser. New to version 11 is the ability to print preview in HTML format View a Report with a Web Browser Learn how to view and look at reports using a Web Browser

198

Crystal Reports 2008 Designer 1 Workshop Lesson 9: Exporting Your Reports within Your Organization

Understanding Export Formats and Destinations One of the major hurdles any Report Developer faces after completing the report creation process is to distribute the report to those that requested it or need to read it. The biggest hurdle is most people don’t have Crystal Reports software on their desktop and therefore cannot read the report. Alternatives include purchasing additional copies of Crystal Reports and loading the software on several computers – a costly and not necessarily appropriate response. The reports can be hardcopy distributed. This is certainly not preferable, but easy to do. Wasting natural resources aside, hardcopy distribution is rarely an effective use of a company’s resources. So, what can you do? One popular and effective option is to distribute a report in a format easily recognizable by commonly installed software applications like MS-Word, Excel, Lotus, ASCII and even HTML. And best of all, whatever file formats you choose can be sent to a network drive location or email account. Exporting a file to a network drive location is effective because it is simple and anyone with access to that network drive can view the file. One drawback to this option however, is it forces the end user to seek out the file and make use of it. If this is a concern and you want the file or report delivered directly to the end user, you may want to consider sending the file to their e-mail account. Crystal Reports can utilize the following e-mail systems: Microsoft Mail Microsoft Outlook Microsoft Exchange Lotus Domino Mail Lotus Domino (version 3.0 or higher) Simply select the e-mail system in the Destination: list box in the Export dialog box. Crystal Reports prompts you to log on to the e-mail system if you are not already logged on. It opens a new message and attaches the report in the format you selected. All you have to do is choose the recipients, and send the message. NOTE: There is some addition setup required when using Lotus Domino. Be sure to read the help file before exporting to this destination. Other destination options besides email applications include: Application – Depending on the format the report is being exported to, Crystal will open the application that supports that format immediately after creating the file in a temporary directory. If the file is being exported to PDF, the Adobe Acrobat Reader is opened to display the file Disk File – This destination allows the user to select a directory for where the created file will be copied Microsoft Exchange – For those companies using an Exchange server, reports can be exported into private or public folders

199

Crystal Reports 2008 Designer 1 Workshop Lesson 9: Exporting Your Reports within Your Organization

You can export a report by choosing File/Export/Export Report from the menu bar or by clicking the Export button on the Standard Toolbar.

The Format list box lists several export format choices, but may not include all possible formats. Depending on specific applications loaded on the client machine, other export options may appear. Other word processing programs such as WordPerfect or a spreadsheet programs such as Lotus may be listed if installed on the client machine. If the Export format is not installed as part of the standard installation you will see a number 1 beside the export format and a message indicating the selected DLL’s will be installed before the export continues. If you select OK you will be asked for the Install CD to continue with the export. The following table lists the standard export options available:

Export Formats Format

Description

Adobe Format (PDF)

Crystal Reports can export into this widely popular format. This format is also the best in retaining the original look and feel of the report Crystal Reports Format This is Crystal Reports native format. It can only be read by Crystal Reports or through (RPT) special viewers provided by Enterprise or Report Application Server (RAS). Special programs written in Visual Basic or other COM based programming languages can also include runtime viewers capable of reading this format HTML 3.2 HTML 3.2 You can export reports to a Web format to be viewed by anyone with a Web Browser using the HTML 3.2 – best for older browsers designed for this standard HTML 4.0 You can export reports to a Web format to be viewed by anyone with a Web Browser using HTML 4.0 – best for newer browsers and provides better formatting MS Excel 97-2003 This export option is greatly enhanced to provide better formatting retention than in the previous versions. Use this option when you want to retain as much of the look and feel of the report as possible MS Excel 97-2003 Another enhancement to exporting to Excel and a new option as well. Use this option (Data only) when you are more interested in exporting data and not lines, boxes, charts and maps. This format will not export any data not displayed in the report. Therefore, any data in hidden or suppressed sections will not display

200

Crystal Reports 2008 Designer 1 Workshop Lesson 9: Exporting Your Reports within Your Organization

ÂŠ2008 Vision Harvest, Inc.

Export Formats Format

Description

MS Word (97-2003)

This export options lets anyone with Word for Windows view the report. Word users can also view reports exported with the Rich Text Format option MS Word (97-2003) This export option creates output optimized for editing the Word document as opposed to Editable accuracy of placement of output ODBC This format allow you to export reports to any ODBC data source. For more information on exporting to an ODBC data source, look up Exporting to an ODBC data source in Crystal Reports Help. There is a wealth of information here. This option is not installed by default Record style (columns Use this format when you want raw data exported to an ASCII fixed record length format no spaces) with no spaces. An excellent choice for moving data into older applications. This option is not installed by default Record style (columns Use this format when you want raw data exported to an ASCII fixed record length format with spaces) with spaces. An excellent choice for moving data into older applications. This option is not installed by default Report Definition This is a wonderful choice if you want to get a text-based description of your report. The report definition tells you all you need to know about each section and the section formats, each object and the object formats, and any conditional formats. It also lists any formulas and displays the selection formula for records or groups. This option is not installed by default Rich Text Format (RTF) A very clean (retains formatting and graphics) format and especially useful for when you are using other word processors besides MS-Word Separated Values (CSV) This is another ASCII format except it separates the field values with commas (Comma Separated Values or CSV). An excellent choice for moving data into older applications or spread sheet programs

Tab-separated text (TTX)

Yet another ASCII format using tab spaces to separate the field values

Text

The export dialog box includes several plain text formats able to export the data from the report, but loses formatting. You are also required to provide information about lines per page and characters per inch The Crystal Reports XML export option lets you create raw XML data from your Crystal Reports files. With this data, you can apply a style sheet and format for use in another application Objects can be copied from Crystal Reports and pasted into other applications such as MSWord or MS-Excel. This includes items such as charts and maps

XML (Extensible Markup Language) Ctrl+C/Ctrl+V

The destinations you have available depend upon the resources on your system, so you can choose to send directly to an application, to a disk file, or a network drive if appropriate, or to an e-mail system if you have one. Similarly to the export formats you may also see a number 1 beside the destination indicating additional DLLâ&#x20AC;&#x2122;s need to be installed before the export can take place. After choosing what you want for these two options, click OK. If you have chosen to export to Excel (either option), Word or Text you will see additional dialogs as shown below, outlining format options for those exports.

201

Crystal Reports 2008 Designer 1 Workshop Lesson 9: Exporting Your Reports within Your Organization Adobe Acrobat (PDF)

Microsoft Excel 97-2003 (XLS)

Microsoft Excel 97-2003 Data only (XLS)

202

ÂŠ2008 Vision Harvest, Inc.

Crystal Reports 2008 Designer 1 Workshop Lesson 9: Exporting Your Reports within Your Organization

ÂŠ2008 Vision Harvest, Inc.

Microsoft Word (97-2003)

Text (TXT)

There is a new option in version XI allowing you to set defaults for each of the dialogs above and is found under File|Export|Report Export Options... This enables you to set the defaults for the formatting dialog set above.

203

Crystal Reports 2008 Designer 1 Workshop Lesson 9: Exporting Your Reports within Your Organization

ÂŠ2008 Vision Harvest, Inc.

Using the PDF Format for Crystal Reports Exporting a report to a format that can be viewed by anyone anywhere usually means exporting to the Adobe PDF file format. Besides the advantage of using a format that can be viewed with a free reader, this format also presents a precision formatting conversion which makes the report an exact replica of what is seen viewing with Crystal Reports. A new feature in Crystal Reports XI R2 made it possible to export to PDF and convert groupings in to PDF Bookmarks. This is the first time a format exists that replicate the Group Tree navigation benefit.

Exercise 9.0 - Export a Report to HTML Format 1.

Open the Conditional.rpt report you created earlier in class

Choose File/Export/Export Report or Click the Export button to open the Export dialog box

Change the Format: setting to PDF. Change Destination: set to Application Changing the Destination to Application will automatically open the PDF. This assumes you have Adobe Acrobat Reader installed

Click OK Crystal Reports displays the Export to Directory window.

This window provides options for specifying page ranges and for creating bookmarks from the group tree. Check on Create bookmarks from group tree and then click OK

Adobe Acrobat Reader should open and display the report. Scroll through pages to see how closely the formatting in PDF compares to what is seen in Crystal Reports

Open the Bookmarks navigation in Adobe Acrobat Reader, expand Canada and then click on BC Notice you are taken to the page displaying the BC region. This mirrors the functionality of the Group Tree in Crystal Reports.

Save the PDF into your Documents/CR2008 (Vista) or My Documents (2000/XP). Name it PDF Export

204

Crystal Reports 2008 Designer 1 Workshop Lesson 9: Exporting Your Reports within Your Organization

ÂŠ2008 Vision Harvest, Inc.

HTML Preview New to version XI is the HTML Preview. This enables you to see how your reports will look when published to the Web. In the same way as the preview button, you can toggle between design and HTML preview. This saves the time of exporting g to HTML and previewing in a browser. To view the report in HTML format, click the HTML Preview button. NOTE: To use this function you need to be logged onto Business Objects Enterprise or enter the location of your Report Application Server in the File|Options dialog.

Exporting to Windows Applications Besides exporting reports into PDF or HTML pages, you may want to export into other commonly used Windows application such as Microsoft Excel or Word. TIP: Choosing Disk File as the export destination saves the report to the disk or diskette you have specified in the Export to Directory dialog box. To view the exported report, first open the application then locate the file. Choosing Application as the export destination exports the report to a Temp file in the specified format, and then opens the file in the appropriate application for you.

Exercise 9.1 â&#x20AC;&#x201C; Export to a Windows Application 1.

Return back to the Conditional.rpt report, if necessary. Click the Export button on the Standard toolbar

Drop down the Format: list and choose Microsoft Excel 97-2003. Drop-down the Destination: list and choose Application. Click OK There are several options for exporting to Excel.

Do not make any changes in this dialog box; just click OK Crystal exports the file and opens it directly in Excel. Notice the file name is a temporary designator starting with a tilde ~. You can save and rename the file from Excel if you want. Perform this exercise several times while making various option setting changes to see how the exported report is affected.

205

Crystal Reports 2008 Designer 1 Workshop Lesson 9: Exporting Your Reports within Your Organization

Click one of the daily subtotal fields If you look in the formula bar at the top of the screen, you can see Excel did not translate the summaries as part of the output.

Close Excel without saving the document and repeat above steps except choose Microsoft Excel 97-2003 Data-only as the format option Notice using this option, summary functions are actually created as long as the option “Use worksheet functions to represent subtotal fields in report” is selected – this is not the default.

Close Excel without saving the document

Export again using the Microsoft Excel 97-2003 Data-only, but this time check on the Use worksheet functions for summaries and the Show group outlines options Although there are some formatting irregularities, the summary fields retain a summary calculation field and a collapsible grouping tree is now shown.

Exporting to a Report Definition Format It is always a good idea to keep documentation on the reports that you create. Several months from now, if you need to know what you did on a certain report, documentation can help you remember. Alternatively, if someone else takes over responsibility for a report, the documentation can help him or her know what you did. A fast, easy way to document your report is to export it to a report definition format. The report definition format is a text file that contains all the information about what is contained on the report and how things are formatted. To create a report definition, choose Report Definition in the Format: box in the Export dialog box. The result is a text file that can be read using the standard Windows Notepad program.

Exercise 9.2 – Create a Report Definition File 1.

Using the Conditional report open, click the Export

Drop down the Format: list and choose Report Definition. Drop down the Destination: list and choose Application. Then click OK

The report opens in the Notepad application If the file does not open in the application or if you set disk file as the destination, go to either Windows Explorer or My Computer. Double click the file to open it in the Notepad application. By default, Crystal names the file the same as the original report, but with a .txt extension. It chooses to put the file in the Temp folder.

Scroll through the file to read the information available. When finished, close Notepad

206

button

Crystal Reports 2008 Designer 1 Workshop Lesson 9: Exporting Your Reports within Your Organization

NOTES

207

ÂŠ2008 Vision Harvest, Inc.

Crystal Reports 2008 Designer 1 Workshop Lesson 9: Exporting Your Reports within Your Organization

NOTES

208

ÂŠ2008 Vision Harvest, Inc.

Crystal Reports 2008 Designer 1 Workshop Lesson 10: Using the Report Wizards

ÂŠ2008 Vision Harvest, Inc.

Lesson 10 Using the Report Wizards

209

Crystal Reports 2008 Designer 1 Workshop Lesson 10: Using the Report Wizards

ÂŠ2008 Vision Harvest, Inc.

Lesson Objectives After completing this lesson, you will be able to: Identify the Four Report Wizard Default formats Depending upon the type of report needed there are four different default formats available. Each format is unique with different options that produce different results Create a report using a Report Wizard Follow the step-by-step instructions using the Standard Report Wizard to create a fast and efficient report Create a Mailing Label report using a Wizard Create mailing labels using the Mailing Label Wizard

210

Crystal Reports 2008 Designer 1 Workshop Lesson 10: Using the Report Wizards

ÂŠ2008 Vision Harvest, Inc.

What are the Report Wizards? When creating a new report, users have the option of starting with a blank report, as in previous lessons, or of using one of the four Report Wizards. These wizards, similar to wizards in Microsoft products, are fast and efficient tools for report creation. However, they are not recommended for individuals who do not understand the business requirements, groupings, sorting, and summary issues needed in the report. The Report Wizards takes you step-by-step through the process of creating a report. From the Start tab you can choose one of the four wizards as shown below. Clicking the new button when in Crystal Reports will take you through the Standard Report Wizard by default.

NOTE: Selecting the Blank Report option opens the Database Expert.

211

Crystal Reports 2008 Designer 1 Workshop Lesson 10: Using the Report Wizards

ÂŠ2008 Vision Harvest, Inc.

Report Wizards Name

Description

Report Wizard (Standard)

The most generic of all the wizards. Provides a typical report format. The Standard Report Wizard has the following dialog boxes: Data, Fields, Link, Grouping, Summaries, Group Sorting, Chart, Record Selection, and Template Creates a report containing a cross-tab object (similar to a spreadsheet). The Cross-Tab Report Creation Wizard has the following dialog boxes: Data, Link, Cross-Tab, Chart, Record Selection and Grid Style This wizard creates a report with multiple columns for printed on all types of mailing labels. The Mailing Labels Report Creation Wizard has the following dialog boxes: Data, Link, Fields, Label, and Record Selection The OLAP Report Creation Wizard lets you create a report in which your OLAP data is displayed as a grid object similar to a Cross-Tab Report. The OLAP Report Creation Wizard has the following dialog boxes: OLAP Data, Rows/Columns, Slice/Page, Style and Chart

Cross-Tab

Mail Label

OLAP

Each wizard guides you through the creation of a report by providing a series of dialog boxes. The step-by-step process includes (but is not limited to) choosing a data source, linking tables (if more than one is used in the report), specifying grouping, summarizing, filtering records, creating charts and choosing a style for the report. Many of the wizards have common dialog boxes as well as those unique to that specific type of report.

Create a Report Using the Standard Report Creation Wizard The Data Dialog Box Once a Report Wizard is selected and opened from the START A NEW REPORT list, the Wizard appears with the Data dialog box open. The Data dialog box is used to select the data source for the report.

212

Crystal Reports 2008 Designer 1 Workshop Lesson 10: Using the Report Wizards

ÂŠ2008 Vision Harvest, Inc.

Reports can be created from any data source available to Crystal Reports and the Data dialog box works exactly like the Database Expert. Find the desired table or tables on the left and move them over to the list box on the right. Click Next to move to the next dialog box. TIP: When more than one table is chosen a Links dialog box appears for setting all the linking options.

The Fields Dialog Box Use the Fields dialog box to select the fields containing the data needed for the report. If you are not sure of the correct field, use the Browse Data feature for a snapshot of the data. Once a field is added to the report, the fields can be reordered using the priority arrows on the upper right hand side.

The Grouping Dialog Box Use the Grouping dialog box to determine how the records are grouped and sorted when they are printed in the report. This will create a Group Header and a Group Footer sections in the report. Choose from two sort directions, in ascending order or in descending order. You also have the ability to create nested (multiple) groups. The Grouping dialog box is an optional setting in the report and it is not necessary to create a group in your report. If you do not create a group, the next two dialog boxes become unavailable.

213

Crystal Reports 2008 Designer 1 Workshop Lesson 10: Using the Report Wizards

ÂŠ2008 Vision Harvest, Inc.

The Summaries Dialog Box The Summaries Dialog box is used to add summary information to the report. By default, all numeric type fields chosen earlier are added to the summarized field section for you. If you will not need to sum on any of the default fields, simply remove them from the summarized fields section and add any additional needed fields. This dialog box has the same summary operators as the Insert Summary dialog box. Grand Totals are added to display the same summary operation automatically. This is an optional dialog box and can only be used if a group is designated in the Grouping dialog box.

214

Crystal Reports 2008 Designer 1 Workshop Lesson 10: Using the Report Wizards

ÂŠ2008 Vision Harvest, Inc.

The Group Sorting Dialog Box The Group Sorting dialog box is used just like the Sort Group Expert for ordering groups based on their summarized values. This is an optional dialog box and can only be used when at least one group and summary are designated on the Grouping and Summaries dialog box.

The Chart Dialog Box The Chart dialog box is used to create a chart to visually enhance your report. This is an optional dialog box setting. This is a limited charting tool and only provides options for chart type and data to chart. Additional formatting must be applied after using the Report Creation Wizard.

215

Crystal Reports 2008 Designer 1 Workshop Lesson 10: Using the Report Wizards

ÂŠ2008 Vision Harvest, Inc.

The Record Selection Dialog Box The Record Selection dialog box is an optional dialog box used to determine the data restriction needed for the report. The field on which you want to filter is selected from the Available Fields and added to the Filter Fields section. Once a field is added, a list box with the available operators appears. The Record Selection dialog box has the same functionality as the Select Expert.

The Template Dialog Box Finally, the Template dialog box is used to determine the formatting style of the report. There are several different styles from which to choose, with an example of each. You can also use your own custom defined templates.

216

Crystal Reports 2008 Designer 1 Workshop Lesson 10: Using the Report Wizards

ÂŠ2008 Vision Harvest, Inc.

When you are finished designing the report, you may click Finish or click the Back button to go back and make any correction necessary. The Report Creation Wizard is only available in the initial creation of the report and you will need to make future changes using other tools in Crystal Reports. Create a North America Customers report using the Standard Report Creation Wizard. The report needs to show the Customer ID, Customer Name, City, Region, and Last Years Sales by Country. Summarize the Last Years Sales field by Country and for the entire report.

Exercise 10.0 â&#x20AC;&#x201C; Create a Report Using the Standard Report Creation Wizard 1.

From the Start page choose to create a report using the Report Wizard (Standard)

From the Data dialog box find the Northwind 2008 database and add the Customers and Orders tables to the Selected Tables: list

Click Next

The Link dialog window appears. Confirm Orders table links properly to the Customers table and then click Next

Add the CustomerID, CompanyName, City, Region and LastYearsSales fields to the Fields to Display: list, and click Next

From the Grouping dialog box, add the Country field to the Group By: list, and then click Next

In the Summaries dialog box, the CustomerID field shows in the Summarized Fields: list. You only want to summarize the LastYearsSales field for the report. Highlight the CustomerID field and click the Remove button

Make sure the operator for the Last Years Sales summarized field is set to Sum, and then click Next until you are at the Record Selection dialog box You do not need to make any changes to the Group Sorting or Chart dialog boxes.

In the Record Selection dialog: add the Country field to the Filter Fields: list, change the drop-list to is one of and then add Canada, Mexico and USA to the list box. Click Next when done

10.

From the Template dialog, select any template from the Available Templates listing. The example shown below uses the Table Grid template

11.

Click Finish The report opens in Preview View.

12.

Save the report as North America Customers.rpt

217

Crystal Reports 2008 Designer 1 Workshop Lesson 10: Using the Report Wizards

ÂŠ2008 Vision Harvest, Inc.

The Preview View of the Top Customer Report should look like the following:

TIP: Once you have created a report using the wizard, it can be modified just like a custom report. You also have the ability to change the style of the report by using the Template Expert.

218

Crystal Reports 2008 Designer 1 Workshop Lesson 10: Using the Report Wizards

ÂŠ2008 Vision Harvest, Inc.

Challenge Exercise â&#x20AC;&#x201C; Create a Report Using a Report Wizard Create a report using the Northwind 2008 database. This report is to generate mailing labels for all customers in the USA. 1.

From the start page choose the Mailing Label Wizard

Add the Customers table

Go to the Fields dialog box and add the following fields: CompanyName, Address1, Address2, City, Region and PostalCode

Go to the Labels dialog box and select Address(Avery 5160) Take a moment to notice all the different label options and that once selected, all the label sizing information is adjusted to match the label.

Go to the Record Selection dialog box and filter the report to only show customers in the USA

When finished, save and preview the report. Save the report as USA Customer Mailing List.rpt Notice how the wizard creates a separate Detail section for each line for the mailing label information.

This mailing label report could be better formatted by creating a text object and placing City, Region and Postal Code in it. Delete these fields and place them in a text object in Details d

Save and preview the report Notice how Crystal Reports removes any blank lines where there is no Address2 information by suppressing each detail section if blank.

219

Crystal Reports 2008 Designer 1 Workshop Lesson 10: Using the Report Wizards

ÂŠ2008 Vision Harvest, Inc.

The Preview View of the USA Customer Mailing List report should look like the following:

220

Crystal Reports 2008 Designer 1 Workshop Lesson 10: Using the Report Wizards

ÂŠ2008 Vision Harvest, Inc.

NOTES

221

Crystal Reports 2008 Designer 1 Workshop Lesson 10: Using the Report Wizards

ÂŠ2008 Vision Harvest, Inc.

NOTES

222

Crystal Reports 2008 Designer 1 Workshop Appendix A: Mapping

ÂŠ2008 Vision Harvest, Inc.

Appendix A Mapping

223

Crystal Reports 2008 Designer 1 Workshop Appendix A: Mapping

ÂŠ2008 Vision Harvest, Inc.

Lesson Objectives After completing this lesson, you will be able to:

Understand map types and layouts Learn to use Crystalâ&#x20AC;&#x2122;s four map layouts and five map types Create and insert a map Use the Map Expert to add geographic charts to your reports Format a map Create professional reports with by editing map styles, defining map colors, rearranging map layers, and adding titles to maps

224

Crystal Reports 2008 Designer 1 Workshop Appendix A: Mapping

Understanding Maps The Mapping feature permits you to add professional looking geographic maps to your report. Maps can help you analyze data and report trends more efficiently. Maps are great any time you want to compare data by geographical region or location. For example, you could create a map that shows sales by region. Once the map is in the report, you can drill down on a region to view underlying data, choose a map type to analyze the data, or adjust the appearance and organization of the map using the Analyzer tab.

Map Layouts The Map Expert has four layouts, each of which is useful for mapping certain types of data: Advanced layout – The Detail layout is useful if you need to map multiple values, rather than only one. You choose a field or multiple fields to show, on change of some geographic field Group layout – The Group layout is simple and shows summary information on change of geographic groups. You must have grouped the report on some geographic field, and then summarized some field within the group Crosstab layout – The Crosstab layout is appropriate when mapping on a crosstab object OLAP layout – Use the OLAP layout when mapping on an OLAP grid NOTE: Notice these layout options are similar to the layouts available when you create charts.

Map Types The Map Expert also provides five basic types of maps. Each of these is suitable for a different kind of data analysis. Before you create a map, you should consider the type of data you want to analyze, and choose the map type best suited for the data. For example, if you want the map to display one data item for each geographic division, then you might use a Ranged, Dot Density, or Graduated map. If you want the map to display more than one value for each geographic division, you could use a Pie Chart map or a Bar Chart map. Crystal provides the following five map types: Ranged – A Ranged map breaks the data into ranges, assigns a specific color to each range, and then codes each geographic area on the map by color to display the range. Crystal has four ways to define ranges: О

Equal Count – assigns intervals in such a way that the same number of regions, or as close as possible, appear in each interval. Each color on the map is as evenly distributed as possible. The numeric quantity of the ranges may not be equal

Equal Ranges – assigns intervals that are numerically equal. The number of regions may not be equal with this option

225

Crystal Reports 2008 Designer 1 Workshop Appendix A: Mapping

Natural Break – assigns intervals using an algorithm that attempts to minimize the difference between the summary values and the averages of the summary values for each interval

Standard Deviation – assigns intervals in such a way that the middle interval breaks at the average of the data values, and the intervals above and below the middle range are one standard deviation above or below the mean

Dot Density – A Dot Density map displays a dot for each occurrence of a specified item. For example, you might create a world map showing one dot for each customer. On the map, you can easily get an overall impression of the distribution of your customers. This type of map is not good for conveying specific information, but it is an excellent method for viewing distribution of an item Graduated – A Graduated map is similar to a Ranged map. It shows a symbol to indicate ranges. The symbol, a circle by default, is proportional in size to the value of the item it represents. Ranged maps are typically used when the geographic areas represented have distinct boundaries, such as states in the United States. Graduated maps work better when values are linked to points rather than precise areas, such as sales by city. A Graduated map uses sized symbols, rather than colors to indicate ranges of values Pie Chart – Pie Chart maps display a pie chart over each geographic area. The pie chart represents data items that make up a whole. For example, you might have a pie chart indicating sales for types of accessories by region. Each pie would have a slice for each type of accessory. The pie for each region indicates sales of accessory types as percentages of all accessory sales Bar Chart – Bar Chart maps are similar to pie chart maps, except using bar charts instead of pie charts. Typically, you would use a bar chart for items that may or may not total 100%, or, for unrelated items As with charts, where you place a map is important. It you put it in the report header or footer, the map includes data for the entire report. If you place it in a group header or footer, it includes data only for that group.

226

Crystal Reports 2008 Designer 1 Workshop Appendix A: Mapping

ÂŠ2008 Vision Harvest, Inc.

To create a map, choose Insert/Mapâ&#x20AC;Ś or click the Map Expert

button on the Expert toolbar.

Similar to charting, the Map Expert is a tabbed process, where you can define the options you want for your specific map. The layouts are available at the left of the first tab. You also define the data source on the first tab. The second tab lets you select the type of map you want and set specifications for each type.

227

Crystal Reports 2008 Designer 1 Workshop Appendix A: Mapping

The third tab is where you can define the legend and add a map title.

Exercise A.0 – Create a Map Showing North American Customer Sales 1.

You already have a report showing sales by country. Suppose you would like to add a map to it to graphically display those sales. Open the Conditional.rpt report and return to Design view

Delete the line in the Page Header section. Then move the column titles from the Page Header section into the Group Header #1 section below the Group #1 Name field

Resize the box used to highlight the Last Year’s Sales values so the top of the box begins in the Group #1 Header

Add a text object to the Group Header #1. Type Detailed Sales Information for and add the Group Name Field to the text object. Format the text object to be bold and 12 pt. Adjust size as necessary

Delete Group #2

Hide Group Header #1, Details, and Group Footer #1 sections

Now you are ready to create the map. Click the Map Expert

On the first tab, the map should be set by default to appear once per report. Set Geographic field to Country, On change of to Country, and Map values to Sum of Last Year’s Sales. Click the Type tab

228

button on the toolbar

Crystal Reports 2008 Designer 1 Workshop Appendix A: Mapping

ÂŠ2008 Vision Harvest, Inc.

Make sure the Ranged button is selected. Leave the Number of intervals: box set to 5. Drop down the Distribution method: list and choose Equal Ranges The Map Expert will break by country.

10.

Select colors of your choice for the lowest and highest intervals

11.

Click the Text tab

12.

You need to do some specific things with the legend. In the Legend title section, click the Specify radio button. Then type Last Yearâ&#x20AC;&#x2122;s Sales in the Title: text box and type Segmented by Country in the Subtitle: text box

13.

Click OK Crystal splits the Report Header into two sections, placing the map into Report Header A and the report title and company logo into Report Header B.

14.

Save the report. Name it Customer Report with Map. Preview the report

The report should look similar to the following:

229

Crystal Reports 2008 Designer 1 Workshop Appendix A: Mapping

Formatting the Map The map does what you specified, but it would look better if it showed the entire world instead of cropping the sides. Once a map is created, there are a number of commands you can use the change the way it looks. Crystal provides the ability to zoom in and out on sections of the map. There are also panning capabilities. These commands are located on the shortcut menu for the map. You can also format borders, move and resize a map like any other object.

Exercise A.1 – Format the Map 1.

Resize the map to be the width of the report and make it about 4” tall

From Preview, RIGHT click the map, then choose Zoom In The mouse pointer changes to a magnifying glass with a + symbol.

Click the center of Europe on the map, twice should be enough Notice how the view gets closer with Europe centered in the view? If you zoom in too much, RIGHT click the map and choose Zoom Out.

RIGHT click the map, and choose Pan The mouse pointer changes to a black double-headed arrow.

Drag the map with the arrow until Europe is centered in the map area

RIGHT click the map, and choose Format Map…

Set a single line border around the map and add a drop shadow

Save the report as Customer Report with Europe Map.rpt , but do not close it

Drilling Down on Maps Not only is mapping a great visual way to present and compare data, it is also a powerful analysis tool. You can drill down on sections of a map to view the underlying details if you want.

Exercise A.2 – Drill Down on a Map 1.

Make sure you are in Preview view with the map visible. Right-mouse click the map and chose Select Mode. Double click France Crystal opens the drill down tab with the detailed information about that group.

To delete the drill down tab, click the button in the on the drill down tab

230

Crystal Reports 2008 Designer 1 Workshop Appendix A: Mapping

ÂŠ2008 Vision Harvest, Inc.

Advanced Formatting of Maps Crystal Reports provides several options for formatting and customizing the appearance of the map. You can zoom, pan and center the map, change the map type, edit the title, rearrange the layers of the map, resolve data mismatches, and use the Map Navigator.

Changing the Geographic Map Sometimes the mapping program displays a map different from what the data indicates. For example, if you create a report with North American countries, then add a map, the map displays only the United States. On the other hand, you might decide you want to change the geographic area displayed in the map. You can change geographic maps by RIGHT clicking the map and choosing Resolve Mismatchâ&#x20AC;Ś

Just pick the map you want and click the OK button.

231

Crystal Reports 2008 Designer 1 Workshop Appendix A: Mapping

Using the Map Navigator The Map Navigator is a miniature version of the map you are currently viewing. It allows you to zoom in or out or to pan with more control. You can RIGHT click the navigator to issue these commands, just as you did in the Preview view of the map. For more control, use the highlighted box in the navigator. Move the box by dragging from the middle of the box. Resize the box (to zoom) by dragging the border of the box.

Exercise A.3 – Change the Map and Use the Map Navigator 1.

RIGHT click the map and choose Resolve Mismatch…

Choose Europe, and then click OK

Using the highlighted box in the Navigator, position and zoom the map so Europe is centered in the map. You can use the above illustration for a guide to about where the box needs to be positioned

When you are finished, you no longer need to see the Navigator. Click the Close button for the Map Navigator

232

Crystal Reports 2008 Designer 1 Workshop Appendix A: Mapping

Changing Map Layers Map layers define the features that display on maps. For example, on the map of Europe, you can choose to display or not display the capitals, the major cities, the cities, the highways and the background countries of Europe. You can also define the zoom specifications for display.

Exercise A.4 – Define the Map Layers 1.

RIGHT click the map again, and then choose Layers…

Suppose you want to see the stars representing the major cities of the Europe. Select Europe Capitals in the Layers: list. Click the Display button

Click the Display within Zoom Range check box to turn it off, then click OK When you turn off Display within Zoom Range it causes the Cities to display on the map regardless of how far in or out you are zoomed.

Click OK again Notice that you now see stars for each European capital city.

Resolving Data Mismatches You should notice when you point to a country, the name of the country and the sales amount appear in a tool tip box. However, when you point to England, only the country name appears. Also, notice that England appears in the light green color that represents countries with no value for sales. Yet, England is in the report and does have some sales. The reason why it is not represented on the map is that there is a data mismatch between the name of the country in the database and the name of the country in Crystal’s map program. Because the map program is not identifying the word “England,” the map program does not include it. You can resolve these mismatches by RIGHT clicking the map and choosing Resolve Mismatch… and clicking on the Resolve Mismatch tab. In the Data Mismatch dialog box, choose the name from the database that you want to match in the Assign this Field Name: list. Then find the map keyword that matches in the To this Map Name: list and select it. Click the Match

button to complete the assignment.

WARNING: Not all the countries display in the keywords list. There are some you might expect to find, but have no keywords available. At this time, there is no workaround for this problem.

TIP: You can find the keyword for a country by pointing to the country on the map. The name of the country as defined by the map program, appears in a tool top.

233

Crystal Reports 2008 Designer 1 Workshop Appendix A: Mapping

Exercise A.5 – Resolve the Data Mismatches in the Map 1.

RIGHT click the map, and then choose Resolve Mismatch… and then select the Resolve Mismatch tab

In the list on the left, select UK, and then in the list on the right, select United Kingdom. Then click the Match button Notice other countries are not currently matched, but this is due to the fact we are using a Europe map which excludes these other countries.

When finished, click OK

Save and close the report. Preview the map changes.

234

Crystal Reports 2008 Designer 1 Workshop Appendix A: Mapping

ÂŠ2008 Vision Harvest, Inc.

NOTES

235

Crystal Reports 2008 Designer 1 Workshop Appendix A: Mapping

ÂŠ2008 Vision Harvest, Inc.

NOTES

236

Crystal Reports 2008 Designer 1 Workshop Appendix B: Before You Begin

ÂŠ2008 Vision Harvest, Inc.

Appendix B Before You Begin

237

Crystal Reports 2008 Designer 1 Workshop Appendix B: Before You Begin

ÂŠ2008 Vision Harvest, Inc.

Lesson Objectives After completing this lesson, you will be able to: Understand and define the purpose of the report Decide the overall purpose of your report. Who will view it? What do they need to see? What decisions will be made from the data viewed Define the layout for the report What will the title be? Do you need information in the header and footer on each page? Do you need summaries or other totals Find the data Where is the data that you need to use in the report? Is it in a PC database file or on a client/server database? Does the data exist or does it need to be calculated Organize the data on the report Should you sort or group your data? Do you only want to see certain records from the database? If so, which ones? Do you want to call attention to certain information? If so, how will it be identified List the fields and tables needed for the report Create a list of the fields needed for the report and the tables in which those fields are located Use the Workbench to organize your reports Manage your reports using folders for easy report maintenance

238

Crystal Reports 2008 Designer 1 Workshop Appendix B: Before You Begin

ÂŠ2008 Vision Harvest, Inc.

Defining the Purpose of the Report Before you ever open the Crystal Reports application, you should do some planning on paper. A structured approach to planning a report will make the report creation process shorter and easier, with less rewriting and editing. Essential to this process is clear and frequent communication with the user of the report, if that is someone other than you. Remember that a report is a management tool. It should help the reader quickly understand the relationships and/or essential elements contained in the raw data. Good reports present the correct data in a well-organized, easy-to-read manner. If the data is incorrect, or, if it is difficult to locate information, then the decision-making process can be slower or ineffective decisions may be made. A good starting place is to write out, in one or two sentences, the purpose of the report. A purpose statement assists you in keeping your focus. It gives the report a goal. Think about or ask about what information the report is intended to convey and how it will be used. Ask the user what decisions will be made based on the information in the report. Often, users may not know exactly what they want. Nevertheless, with your expertise in Crystal Reports, you can help them get exactly what they need. Here are some examples of report purpose statements: The purpose of this report is to show monthly sales of each product and the total dollar amount of each product sold The purpose of this report is to keep track of inventory and quickly call attention to items that need to be reordered The purpose of this report is to provide demographic information on our customers and suppliers in order to determine if we need to expand our supplier base The purpose of this report is to compare year-to-date sales with last yearâ&#x20AC;&#x2122;s year-to-date sales to gauge how we are doing compared to last year. The information needs to be presented region by region You should also be aware of who is going to read the report. Often, different people at different levels of responsibility in the organization read the same report, but for different reasons. If this is the case, you need to organize the report so that each level of information is clear and easy to find. A common example would be a company sales report. The sales representatives might use the report to evaluate individual sales performance and do comparisons to other representatives The regional sales manager might also use the report for comparisons of the sales reps in his/her region. But this person would also want to know how his/her region compares with other regions or what percentage of total company sales are represented by his/her region The national sales director might use the report to evaluate regional managerâ&#x20AC;&#x2122;s performance. It could also be used to compare sales to forecasts and determine if company-wide adjustments are needed 239

Crystal Reports 2008 Designer 1 Workshop Appendix B: Before You Begin

ÂŠ2008 Vision Harvest, Inc.

The Vice President of Sales might use this report to project manufacturing needs, warehouse locations, sales campaigns, etc Write out all these needs and formulate them into your purpose statement. If you can design the report to meet the needs of all these people, you will save yourself development time and better serve your company as a whole.

Determining the Layout of the Report The layout of the report is the plan for what information appears and where. What should the report title be? Plan a title that encompasses all uses of the information or the most detailed use of the information. Write out the title. Is there information that should appear on each page of the report, such as the file name, author name, or page numbers? Will the users want to know how current the information is? Make a list of the items you need to include. What pieces of information need to display at each level of the report? Do you need to summarize, total, calculate averages or percentages or perform any statistical analyses? If so, which information needs to be calculated and how do you want to divide the report for the calculations? For example, in the sales report mentioned above, you might need to calculate total sales by each sales representative, also by each region, also by country, and grand total for the entire company. Write listings of what you need to see at each level of the report including totals and grand totals. Also, include the information by which you need to divide the report.

Finding the Data It is critical to know, before you begin report development, where the data you need is located. You should know if the database you need to use is a PC data file or an SQL/ODBC data source. Where is the database located and is there an ODBC connection already set up on your computer to access the database? If you do not know the answer, contact your IT professional, database administrator or possibly even your coworkers to get this information. Next, decide which pieces of information you need to see at the greatest level of detail in your report. Write down these pieces of information. Once you have determined the data you need for details, you can add this to all the other information you have listed for the report. You can then use this list to determine which fields you need to access for the report. As you look through your list of pieces of information needed, you may see four types of information: Data that is taken directly from fields in the database Data that is calculated from fields in the database (such as summary totals) Information that is typed directly into text objects in the report (such as a title) Information that is generated automatically, but not from the database (such as page numbers) 240

Crystal Reports 2008 Designer 1 Workshop Appendix B: Before You Begin

ÂŠ2008 Vision Harvest, Inc.

Go through this list and determine which pieces come directly from fields in the database, then make a list of the field names you are going to need. After you list the fields, make a list of the table or tables that contain these fields. Obviously, you are going to have to be somewhat familiar with the database to do this. If you do not know the database well, talk with your database administrator and see if you can obtain a data map or data dictionary that tells you where specific pieces of information are stored. In addition to knowing field names, you should also become familiar with field types or data types. Many of the functions and commands used in Crystal Reports are designed to work with a specific type of data. You need to become comfortable with the types of data contained in your database. Most fields are one of the following data types: string, number, currency, date, time, date/time, Boolean, or memo.

Organizing the Data for the Report Now that you know what data you need and where it is located, what you have to pull from the database, what you can type, and what you have to calculate, you need to put all this together. In other words, you need to organize it all, so you know what will print where on the report. Each report that you create is divided into sections and the sections determine where things print on the report. The sections display in the Design view of Crystal Reports and print in the same order in which they display. Each section has its own characteristics that determine where objects print on the final report.

Report Header Objects in this section print only one time at the beginning of the report. You might use this section for the report title (if you only wanted it to print on the first page) or for charts or maps that represent all the data in the report. Page Header This section prints the objects it contains at the top of each page of the report. This is a good location for print dates, page numbers, file name and author, or any other information that the user would want to see on each page.

241

Crystal Reports 2008 Designer 1 Workshop Appendix B: Before You Begin

ÂŠ2008 Vision Harvest, Inc.

Group Header If you decide to divide the information in the report, you do it by creating groups. The items in this section appear at the beginning of each new group. Typically, this section contains the name of the group or other group identifying text and/or charts or cross-tabs that represent data group by group. Details This section contains the detail records for the report and gets repeated for each record pulled from the database. This is where you want to put the items that represent the details on the report. Group Footer This section prints at the end of each group in the report and only displays when the report has been divided into groups. Here is where the group subtotals and summaries appear and any other information you want included at the end of each group. Report Footer Objects placed in this section print only once at the end of the report. It is the perfect place for grand totals or other summaries for the entire report. Often it is used for charts, maps or cross-tabs that display information for the entire report. Page Footer Like the Page Header, this section appears on each page of the report, only this one displays at the bottom of the page. Use it for page numbers, printed dates or other information that the user needs to see on every page of the report. Now you can design your report on paper, placing each piece of data where it belongs. It can even be helpful to indicate the sections at the left of the paper to help you remember which objects to put into each section. Basically, draw a picture of what the report should look like. You are not going to do this planning in class because there is not time. Your class time is best spent learning the Crystal Reports program. However, this is a highly recommended process for you to follow when you begin to create your own reports. If you plan your reports on paper first, it will save you time, work and aggravation later.

The Workbench A new feature available since Crystal Reports XI is the Workbench. As you are thinking about report design you should also be thinking about report organization. Which reports should be grouped logically together etc. The workbench helps you with this kind of project organization as it allows you to group reports in folders according to your preference. To view the workbench select View|Workbench or click on the workbench button on the toolbar.

242

Crystal Reports 2008 Designer 1 Workshop Appendix B: Before You Begin

ÂŠ2008 Vision Harvest, Inc.

With the workbench you can add projects to the Workbench â&#x20AC;&#x201C; these are basically folders for your reports. You can also add reports to the workbench. Finally, you can add Object Packages to the Workbench. Object Packages are objects from the Business Objects Enterprise system. Another feature of the workbench is the Dependency Checker. The Dependency Checker allows you to check an individual report or a project (series of reports) for the following: Report Part Hyperlink errors Repository Object Errors Formula Compilation Errors

243

Crystal Reports 2008 Designer 1 Workshop Appendix B: Before You Begin

ÂŠ2008 Vision Harvest, Inc.

NOTES

244

Crystal Reports 2008 Designer 1 Workshop Appendix C: Setting Default and Report Options

ÂŠ2008 Vision Harvest, Inc.

Appendix C Setting Default and Report Options

245

Crystal Reports 2008 Designer 1 Workshop Appendix C: Setting Default and Report Options

ÂŠ2008 Vision Harvest, Inc.

Lesson Objectives After completing this lesson, you will be able to: Change the default layout for Design View and Preview View You can set up the views so they have the features you use most frequently. Change the views to show or hide guidelines, show rulers, display short section names, and many more items Set the default folder for reports Make it easy to locate your reports by setting the default folder. Whenever you save a report, Crystal Reports automatically displays the correct folder Set default formats for different types of fields Save work by setting default formats to those you use the most frequently Set the default font for different types of fields Improve your efficiency by setting default fonts to the ones you like the best

246

Crystal Reports 2008 Designer 1 Workshop Appendix C: Setting Default and Report Options

ÂŠ2008 Vision Harvest, Inc.

Setting the Default Layout for Design and Preview Views After you become comfortable developing reports with Crystal Reports, you may come to prefer a certain look and feel for the Design and Preview Views. The Layout tab in the Options dialog box allows control over the appearance for the Design and Preview screens. To open the Options dialog box, choose File/Options...

The Options dialog box contains many tabs in addition to the Layout tab. This lesson only covers those options that are the most critical in day-to-day work, but has a table addressing all elements in all the tabs. The Layout tab is the first tab in the series. TIP: Many of these options also appear in the Report Options dialog box. The Options dialog box changes the default values for the program; the Report Options dialog box changes the options for the current report only.

247

Crystal Reports 2008 Designer 1 Workshop Appendix C: Setting Default and Report Options

The following table lists each tab and their options in the Options dialog window:

Layout Options Option

Description

Design View options

These options toggle display of items in Design or Preview views. Turning these properties on or off reduces or increases the amount of space available to view the report: • Rulers provide measurements at the top and left of the screen • Guidelines make moving and aligning fields easier • Grid displays a series of dots that are very useful in positioning and aligning several objects • Tool Tips appear with the name of the objects when you leave the cursor pointer over the object for a few seconds • Short Section Names provide more space by abbreviating the section headings: for example, Report Header is abbreviated to RH • Show Hidden Sections always display hidden or suppressed sections in Design view. Not turning this option will force the section to be compressed and un-viewable in design view

Preview options

• • • • •

• • •

Grid Options

Field Options

Free-Form Placement

Rulers provide measurements at the top and left of the screen Guidelines make moving and aligning fields easier Grid displays a series of dots that are very useful in positioning and aligning several objects Tool Tips appear with the name of the objects when you leave the cursor pointer over the object for a few seconds Section Names provide provides a display of abbreviated section names while in Preview. Having this option on provides a great mechanism for debugging a report. Seeing a problem and which section it belongs to aids resolving report issues Page Breaks in Wide Pages show pages on one page even when the page is too large to print on one page Pages Start With by default, will display Preview pages in Full Size. This section lets you change that setting Display Preview Panel displays the Group Tree automatically when a report is first previewed when option is turned on

These options can turn on and off the Snap feature. You can also set the grid size in this section. When Snap to Grid is checked, field and guideline placement snaps to the closest grid point These options define how Crystal Reports works with fields: • When you check the Show Field Names box, Crystal Reports displays field names in the field instead of XXX’s and 555’s • By default, Crystal Reports automatically puts field titles in the Page Header for each field inserted in the Detail section. If you do not want this, un-check Insert Detail Field Titles • Check the Insert Group Name with Group box to place a Group Name field into the Group Header when the Group is first created This item allows fields to be placed freely anywhere in the report

248

Crystal Reports 2008 Designer 1 Workshop Appendix C: Setting Default and Report Options

Database Tab Tables and Fields

Data Explorer

Advanced Options

Use this option to specify how you want table and field names to appear, by name, description (field type) or both. Also Designates if Tables and Fields will sort alphabetically throughout the program Tables, Views, System Tables, Synonyms, Stored Procedures: When you select the individual check boxes, the program allows reporting on that data source. The Table, View and Stored Procedures options are selected by default • Use Indexes or Server for Speed - This defaulted option tells Crystal to use the available indexes/servers to speed up the record selection process. Un-checking this option may slow processing time • Perform Grouping on Server - This option tells Crystal to perform grouping on the server (server-side processing). Server-side processing performs the majority of its processing on the server rather than at your computer. Note: Server-side processing works only for reports based on SQL data sources. This option is not selected by default • Database Server is Case-Insensitive - This option tells Crystal to ignore case when searching for strings in your SQL data. For example, a case-sensitive search for “smith” returns only “smith.” With this option turned on, the query will return “smith”, “SMITH”, and “Smith” without regard to case. This option is selected by default • Select Distinct Data for Browsing - This option is for the Browse Data command. When selected, you see the first 500 distinct (unique) records. If it is not selected, browsing returns the unique values in the first 500 records • Perform Query Asynchronously - Provides the ability to intermittently cancel any running queries sent to the database server. Turned off, the user must wait for the data request to finish completely • Verify on First Refresh - When the report is opened and refreshed for the first time, Crystal will verify the database table and field attributes match with the report attributes of these objects • Verify Stored Procedures on First Refresh - When the report is opened and refreshed for the first time, Crystal will verify the database stored procedures attributes match with the report attributes of these objects • Verify When Database Driver Upgraded - This option checks for any upgrades for the database drivers and upgrades the report automatically. This option is turned on by default • Automatic Smart Linking - This option is on by default and tells Crystal to link tables automatically as soon as you have more than one table in a report. Turning this off means you need to specify links between two tables

Formula Editor Tab Text Options Color

Formula Language

The Formula Editor tab allows you to customize formatting options for the text you create in the Formula Editor or the SQL Expression Editor This option enables you to change the color of elements in the Element list. Elements are Comments; keywords; text and text selection. You can change either the foreground or the background colors. You can change the foreground or background colors of the Text and Text Selection elements only if you clear the “Use Windows system color” option first You can choose the language for your formulas either Crystal or Basic. Crystal is the default syntax

249

Crystal Reports 2008 Designer 1 Workshop Appendix C: Setting Default and Report Options Null Treatment

UFL Support

This option enables you to decide how null values are treated in your formulas. You can choose between Exceptions For Nulls and Default Values For Nulls. Exceptions For Nulls is selected by default. If you choose Exceptions For Nulls, the program treats a null value as an exception and halts formula evaluation when it encounters such a value. If you choose Default Values For Nulls, the value that the program assigns varies depending on the data type that you are using in your formula This option enables you to decide whether User Functions are shown in the Formula Editor. You can choose between C/COM UFLs Only, Java UFLs Only, and No UFLs. C/COM UFLs is selected by default

Reporting Tab Enterprise Settings

•

Update Enterprise Report Properties on Save - Updates Enterprise properties when the report is saved • Update Connected Repository Objects on Open - Select this check box to update repository objects used in your report with the latest version from the repository when opening a report in the designer Reading Data • Convert Database NULL Values to Default - Converts any null database field values to a real value such as zero for numeric fields or blank for string fields • Convert Other NULL Values to Default - Converts any other null values such as those from formulas or running totals to zero for numeric fields and blank for string fields • Always Sort Locally - Forces a local sort over any sort provided by the database Save Data With Report This defaulted option tells Crystal to automatically save the database data with each report. Otherwise, the program only saves the design template of the report, and will need to retrieve the data each time you run the report Discard Saved Data On This option tells Crystal to discard saved data when loading reports. When you open a reOpen port, the saved data is discarded and new data is retrieved automatically Re-import Subreport Crystal updates any subreports in the main report from the original subreport report file, On Open whenever you open the main report Suppress Printing if No With this option turned on reports cannot print if the database does not return any records. Records Selected This prevents situations where there is no data, but you still get the titles and page headers printed Show all Headers on With this option turned on, drill-down views include the headers for all sections above the Drill Down drill-down records Autosave Reports After This option tells Crystal you want reports to be saved automatically as you work on them ... Minutes You can set the minutes in the Minutes box Display Alerts on If a report contains any Report Alert conditions, Crystal will run the alert on every refresh Refresh and display if any alerts exist Save Preview Picture Checking this item on will create a thumbnail image of the reports first page and save it in the report. This feature is useful when using Crystal Enterprise as it has the ability to display this image on a web page Retain Original Image Select this option to have images in your report keep their original color depth when they Color Depth are viewed. Otherwise, all images are converted to 8 bits per pixel before being displayed. The advantage of selecting this option is that it lets Crystal Reports render images as exact copies of the originals. Crystal Reports, however, will then store more information about the images, which may lead to larger files and slower response time in loading reports

250

Crystal Reports 2008 Designer 1 Workshop Appendix C: Setting Default and Report Options

Fields Tab Field Format

The Fields tab gives you the ability to set the default appearance of each field as it is placed in the report. When you click one of the buttons, the Format Editor dialog box appears. The Format Editor has the same options you see when formatting an existing field For instance, suppose in your company all dates need to show in long format (ex. March 29, 2007). However, all dates in your database are stored in Date/Time fields that Crystal brings in with the default format of 03/29/00 12:00:00AM. It would be much easier to set the default format for all Date/Time fields to display as needed, rather than having to format the fields individually, every time they are used in a report

Fonts Tab Default Fonts

Use this tab to set all fields or to a default font of your preference. This option includes various non-database field objects such as Summaries, Group Name Fields and even Chart titles

Smart & HTML Preview Tag Web Server

Virtual Directory Viewing Page

Enable HTML Preview when not logged onto Business Objects Enterprise Web Server Name

Platform

URL

RAS Server Name

Enter the HTTP address of the web server you want to use. When you choose the View or Refresh smart tag for a Crystal report object, this web server is queried for the appropriate report Enter the name of the virtual directory you’ve created on your web server. This directory contains your viewing page. The field defaults to crystalreportviewers11 Enter the name of the .asp or .jsp page you’ll use to display your Crystal report. The pages crsmarttag.asp and crsmarttag.jsp are created by default if the Report Application Server (RAS) is detected when you install; you can find them at \Program Files\Common Files\Common Files\Business Objects\3.0\crystalreportviewer11 Select this option if you want to preview reports in HTML format but outside of the Business Objects Enterprise system. This is known as un-managed reports. Selecting this option means you need to enter information about your RAS and web server You need to enter the name of the web server (default value is localhost) for previewing your reports in HTML. When you choose HTML Preview for a report that is open in Crystal Reports, this web server is contacted to communicate with your Report Application Server Choose the platform of your web server installation. Choices include COM which changes the extension of the preview page to .asp; Java which changes the extension of the preview page to .jsp; .NET which changes the extension of the preview page to aspx Enter the HTTP address of the virtual folder on your web server that contains the preview web page. Crystal Reports uses this page to display the viewer it needs for the HTML preview. By default, this field is constructed from the information that you add in the previous fields Default value is localhost or enter the name of the RAS server

Dependency Checker Compile Formulas

When you select to check for dependencies and this option is checked Crystal compiles all the formulas in the report to generate a list of errors Compile SQL ExpresWhen you select to check for dependencies and this option is checked Crystal compiles all sions the SQL Expressions in the report to generate a list of errors Compile Custom Func- When you select to check for dependencies and this option is checked Crystal compiles all tions the Custom Functions in the report to generate a list of errors

251

Crystal Reports 2008 Designer 1 Workshop Appendix C: Setting Default and Report Options Validate hyperlink URLs Verify Database

ÂŠ2008 Vision Harvest, Inc.

Crystal Reports validates all hyperlink URLs and generates an error message when a hyperlink can not be processed Crystal verifies the database and returns a message if the database has changed or can not be accessed Crystal checks all the custom functions in the report and makes sure they are in the Business Objects Enterprise Repository. This checking only happens if you are logged onto the Central Management Server Crystal checks all the repository linked text objects and bitmaps in the report and makes sure they are in the Business Objects Enterprise Repository. This checking only happens if you are logged onto the Central Management Server Crystal checks that report objects exist and are located in the same report section

Check repository custom functions Check repository objects Check report part hyperlinks

Check subreports Crystal Reports checks that all subreports that are to be re-imported exist in the speciwhich are re-imported fied location on open

Exercise C.0 - Set the Layout Options 1.

Choose File/Optionsâ&#x20AC;Ś from the menus

If necessary, click the Layout tab

Check to be certain the Rulers are set to show in both Design and Preview views

If necessary, check the Guidelines box to show guidelines in Design view

Click on Grid box to display the grid in Design view

Click the Show Short Section Names check box in the Design view

Click OK Notice the section names in Design view are shorter. Also, notice that you can see the grid in the background.

252

Crystal Reports 2008 Designer 1 Workshop Appendix C: Setting Default and Report Options

ÂŠ2008 Vision Harvest, Inc.

Exercise C.1 - Set Field and Font Defaults 1.

Open the Options dialog box and go to the Fonts Tab

Click the

Set the Font to a Bold style and turn off the Underline

Click OK

Go to the Fields Tab

Click the

Go to the Number tab of the Format Editor and click

Select the Decimals and change so that default is no decimals

Click OK twice to close the Custom Style and Format Editor dialog boxes

10.

Repeat steps 5 â&#x20AC;&#x201C; 9 for the

11.

Click the

12.

On the Date/Time tab, select the date format for a textual date without the time: for example, December 27, 2004

13.

Click OK twice to close the Format Editor and the File Options dialog box and accept your changes

button

button to open the Format Editor

button button

253

Crystal Reports 2008 Designer 1 Workshop Appendix C: Setting Default and Report Options

ÂŠ2008 Vision Harvest, Inc.

The above settings are set for all reports; but there are some options that you can set for a single report. Select File|Report Options to see those settings as shown below:

254

Crystal Reports 2008 Designer 1 Workshop Appendix C: Setting Default and Report Options

ÂŠ2008 Vision Harvest, Inc.

NOTES

255

Crystal Reports 2008 Designer 1 Workshop Appendix C: Setting Default and Report Options

ÂŠ2008 Vision Harvest, Inc.

NOTES

256

Crystal Reports 2008 Designer 1 Workshop Appendix D: Pre-Qualification Test for Designer 2 Workshop

ÂŠ2008 Vision Harvest, Inc.

Appendix D Pre-Qualification Test for Designer 2 Workshop

257

Crystal Reports 2008 Designer 1 Workshop Appendix D: Pre-Qualification Test for Designer 2 Workshop

ÂŠ2008 Vision Harvest, Inc.

Why Should You Pre-qualify yourself for the Designer 2 Workshop? The Crystal Reports 2008 Designer 2 Workshop is designed for the experienced Crystal Reports user. It covers the more complex and powerful features of the program. The class discusses very technical issues and includes several fast-paced, comprehensive exercises. Our instructors have years of experience helping students to get the most out of their training. However, it is not always possible in a high level workshop to assist students with basic tasks. In order to maximize the benefit you receive from the Crystal Reports Designer 2 Workshop, you should come into the training with a certain skill level. We assume all students who sign up for this workshop have a comfortable level of understanding in regards to relational databases. Students should also be able to perform the following tasks in Crystal Reports: Create a new report, insert fields, save the report and refresh the data. Add multiple tables to the report using the Data Explorer. Use the select expert to pull only the data needed from the database. Insert groups and summarize fields within the group. Perform formatting tasks. Create, save and use formulas in the report. If you would like to evaluate for yourself whether this is the correct level course, take our self-evaluation exercise. The exercise consists of writing a report in Crystal Reports. When you are finished, compare your report to the one our experts have done to see how well you understand the basic features of Crystal Reports. You can obtain the exercise and the results report from the Designer 1 Workshop class files. If you have trouble creating the report or getting the correct results, or if you cannot perform some of the above-mentioned tasks, then we strongly recommend that you take the Crystal Reports Designer 1 Workshop before attending the Designer 2 Workshop. By assuring you are well prepared for the Designer 2 Workshop, you maximize the benefits you receive from this in-depth look at advanced report writing.

Pre-qualification Exercise Using the Northwind 2008 database available included with the class files, create a report invoicing customers for each order. The report needs to show the customer billing address, the Invoice#, the sales person, the order date, the requested date, the ship date and the company shipping the order. The Details section should list the product ID, the product name, product category, the unit price, the quantity, and the line item total. Select only those orders with an ship date between January 1, 2008 and March 31, 2008. Group on the CustomerID, then on the OrderID. Display each order on its own page. Sum the line item total amount for each OrderID. Calculate the sales tax for the order at 6% and total the subtotal and sales tax.

258

Crystal Reports 2008 Designer 1 Workshop Appendix D: Pre-Qualification Test for Designer 2 Workshop

ÂŠ2008 Vision Harvest, Inc.

You will need to pull data from the following tables: Customers Orders Employees Shippers

Products OrderDetails Categories

The tables joins should look like the example below, but Smart Linking might link incorrectly, so double check.

You will need to create the following formulas: Line Item Total that multiplies the unit price times the quantity for each product ordered Sales Tax that multiplies the sum of each line item total times 6% Order Total that adds the sum of each line item total and the sales tax

259

Crystal Reports 2008 Designer 1 Workshop Appendix D: Pre-Qualification Test for Designer 2 Workshop

You will need to create the following text boxes: О

An address box with the text To: then the company name, the address, the city, region, postal code and country

An employee box with the employee first name, a space, then the last name

An invoice box with invoice date

An order information box containing the order date, required date, shipped date and shipped via fields

Format the report to look like the following:

260

Crystal Reports 2008 Designer 1 Workshop Appendix D: Pre-Qualification Test for Designer 2 Workshop The Design view should look similar to the following example:

261

ÂŠ2008 Vision Harvest, Inc.

Crystal Reports 2008 Designer 1 Workshop Appendix D: Pre-Qualification Test for Designer 2 Workshop

NOTES

262

ÂŠ2008 Vision Harvest, Inc.

Crystal Reports 2008 Designer 1 Workshop Appendix E: The Northwind 2008 Database

ÂŠ2008 Vision Harvest, Inc.

Appendix E The Northwind 2008 Database

263

Crystal Reports 2008 Designer 1 Workshop Appendix E: The Northwind 2008 Database

ÂŠ2008 Vision Harvest, Inc.

The Northwind 2008 database used for class is a Microsoft Access database file accessed through the Access/Excel (DAO) connection method. The tables used in class for this database are listed above. Lines between tables indicate where links can be created between the tables. There may be other linkable fields between tables. This illustration shows the most likely ones.

264

Crystal Reports 2008 Designer 1 Workshop Appendix E: The Northwind 2008 Database

ÂŠ2008 Vision Harvest, Inc.

NOTES

265

Crystal Reports 2008 Designer 1 Workshop Appendix E: The Northwind 2008 Database

ÂŠ2008 Vision Harvest, Inc.

NOTES

266

Crystal Reports 2008 Designer 1 Workshop Appendix F: Installation and System Requirements

ÂŠ2008 Vision Harvest, Inc.

Appendix F Installation and System Requirements

267

Crystal Reports 2008 Designer 1 Workshop Appendix F: Installation and System Requirements

ÂŠ2008 Vision Harvest, Inc.

Installing the Application Crystal Reports provides a step-by-step process to install the application to your system. When the installation disk is placed into the CD tray of your system, the application will automatically begin installing providing the auto play feature has not been disabled on your PC. If this feature has been disabled, locate the setup.exe file to begin the installation process. During the installation, your system will be scanned for outdated files. You will be prompted to update these files with the ones provided. During the installation, the operating system of your PC will be detected. If Crystal Reports requires an updated version of your operating system, you must first update before the application will install. (See section below for the supported OS.) Once you have entered your key code and accepted the license agreement, you will be prompted to select the type of installation you would like to perform. Crystal Reports provides Typical and Custom installation options. A Typical installation will install the primary components of Crystal Reports, such as the report designer. A Typical installation does not automatically install all exporting options, all data access drivers, and several developer components are also excluded. The Custom installation option will allow you to pick and choose the components you would like to install. It will also indicate the amount of disk space required to install the feature selected. Crystal Reports installation also follows Microsoftâ&#x20AC;&#x2122;s approach to installation on demand. This means that some of the features will not be installed until you have attempted to utilize the feature. There will be a momentary waiting period while the feature installs. Some features may require the installation CD to be installed. Due to registry changes and system file updates, administrator privileges are required to install the application onto Windows NT, 2000, XP and Vista computers.

Minimum Installation Requirements Crystal Reports has been tested and approved to be used on the following operating systems. Windows Vista Windows 2003 Server Windows XP Professional SP2 Crystal Reports requires a 450 MHz or higher Pentium compatible CPU. A minimum memory requirement to install the application is 128 MB. It is recommended that the system has 256 MB for a trouble-free installation. 4G Maximum. Crystal Reports requires 1 GB of free disk space to properly install. 1.5 GB recommended

268

Crystal Reports 2008 Designer 1 Workshop Appendix F: Installation and System Requirements

NOTES

269

ÂŠ2008 Vision Harvest, Inc.

Crystal Reports 2008 Designer 1 Workshop Appendix F: Installation and System Requirements

NOTES

270

ÂŠ2008 Vision Harvest, Inc.

Crystal Reports 2008 Designer 1 Workshop Appendix G: Installation and System Requirements

ÂŠ2008 Vision Harvest, Inc.

Appendix G Function and Operator Locations

271

Crystal Reports 2008 Designer 1 Workshop Appendix G: Installation and System Requirements

ÂŠ2008 Vision Harvest, Inc.

Following is a list of Functions available in the middle list in the Formula Editor: Major Categories are listed in bold Minor Categories are listed in italics Function names are listed in regular text Math Abs(x) Sgn(x) Int(x) Round Round(x) Round(x, #places) RoundUp RoundUp(x) RoundUp(x, # places) Truncate Truncate (x) Truncate (x, #places) MRound MRound(x) MRound(x, multiple) Ceiling Ceiling(x) Ceiling(x, multiple) Floor Floor(x) Floor(x, multiple) Remainder(num, denom) Sin(x) Cos(x) Tan(x) Atn(x) Pi Sqr(x) Exp(x) Log(x) Rnd Rnd Rnd(seed) Summary Sum Sum(fld) Sum(fld, condFld) Sum(fld, condFld, cond) Average Average(fld) Average(fld, condFld) Average(fld, condFld, cond)

Summary (continued) StdDev StdDev(fld) StdDev(fld, condFld) StdDev(fld, condFld, cond) PopulationStdDev PopulationStdDev(fld) PopulationStdDev(fld, condFld) PopulationStdDev(fld, condFld, cond) Variance Variance(fld) Variance(fld, condFld) Variance(fld, condFld, cond) PopulationVariance PopulationVariance(fld) PopulationVariance(fld, condFld) PopulationVariance(fld, condFld, cond) Maximum Maximum(fld) Maximum(fld, condFld) Maximum(fld, condFld, cond) Minimum Minimum(fld) Minimum(fld, condFld) Minimum(fld, condFld, cond) Count Count(fld) Count(fld, condFld) Count(fld, condFld, cond) DistinctCount DistinctCount(fld) DistinctCount(fld, condFld) DistinctCount(fld, condFld, cond) Correlation Correlation(fld) Correlation(fld, condFld) Correlation(fld, condFld, cond) Covariance Covariance(fld) Covariance(fld, condFld) Covariance(fld, condFld, cond) WeightedAverage WeightedAverage(fld) WeightedAverage(fld, condFld) WeightedAverage(fld, condFld, cond) 272

Crystal Reports 2008 Designer 1 Workshop Appendix G: Installation and System Requirements Summary (continued) Median Median(fld) Median(fld, condFld) Median(fld, condFld, cond) PthPercentile PthPercentile(fld) PthPercentile(fld, condFld) PthPercentile(fld, condFld, cond) NthLargest NthLargest(fld) NthLargest(fld, condFld) NthLargest(fld, condFld, cond) NthSmallest NthSmallest(fld) NthSmallest(fld, condFld) NthSmallest(fld, condFld, cond) Mode Mode(fld) Mode(fld, condFld) Mode(fld, condFld, cond) NthMostFrequent NthMostFrequent(fld) NthMostFrequent(fld, condFld) NthMostFrequent(fld, condFld, cond) PercentOfSum PercentOfSum(fld, condFld) PercentOfSum(fld, condFld, cond) PercentOfSum(fld, innerCondFld, outerCondFld) PercentOfSum(fld, innerCondFld, innerCond, outerCondFld) PercentOfSum(fld, innerCondFld, outerCondFld, outerCond) PercentOfSum(fld, innerCondFld, innerCond, outerCondFld, outerCond) PercentOfAverage PercentOfAverage(fld, condFld) PercentOfAverage(fld, condFld, cond) PercentOfAverage(fld, innerCondFld, outerCondFld) PercentOfAverage(fld, innerCondFld, innerCond, outerCondFld) PercentOfAverage(fld, innerCondFld, outerCondFld, outerCond) PercentOfAverage(fld, innerCondFld, innerCond, outerCondFld, outerCond)

273

ÂŠ2008 Vision Harvest, Inc.

Summary (continued) PercentOfMaximum PercentOfMaximum(fld, condFld) PercentOfMaximum(fld, condFld, cond) PercentOfMaximum(fld, innerCondFld, outerCondFld) PercentOfMaximum(fld, innerCondFld, innerCond, outerCondFld) PercentOfMaximum(fld, innerCondFld, outerCondFld, outerCond) PercentOfMaximum(fld, innerCondFld, innerCond, outerCondFld, outerCond) PercentOfMinimun PercentOfMinimum(fld, condFld) PercentOfMinimum(fld, condFld, cond) PercentOfMinimum(fld, innerCondFld, outerCondFld) PercentOfMinimum(fld, innerCondFld, innerCond, outerCondFld) PercentOfMinimum(fld, innerCondFld, outerCondFld, outerCond) PercentOfMinimum(fld, innerCondFld, innerCond, outerCondFld, outerCond) PercentOfCount PercentOfCount(fld, condFld) PercentOfCount(fld, condFld, cond) PercentOfCount(fld, innerCondFld, outerCondFld) PercentOfCount(fld, innerCondFld, | innerCond, outerCondFld) PercentOfCount(fld, innerCondFld, outerCondFld, outerCond) PercentOfCount(fld, innerCondFld, innerCond, outerCondFld, outerCond) PercentOfDistinctCount PercentOfDistinctCount(fld, condFld) PercentOfDistinctCount(fld, condFld, cond) PercentOfDistinctCount(fld, innerCondFld, outerCondFld) PercentOfDistinctCount(fld, innerCondFld, innerCond, outerCondFld) PercentOfDistinctCount(fld, innerCondFld, outerCondFld, outerCond) PercentOfDistinctCount(fld, innerCondFld, innerCond, outerCondFld, outerCond)

Crystal Reports 2008 Designer 1 Workshop Appendix G: Installation and System Requirements Summary (continued) BooleanConditions On any change On change to yes On change to no On every yes On every no On next is yes On next is no Date Conditions For each day For each week For each two weeks For each half month For each month For each quarter For each half year For each year Time Conditions For each second For each minute For each hour For each AM/PM Financial ACCRINT ACCRINT(issue,firstInterest,settlement, rate,parValue,frequency) ACCRINT(issue,firstInterest,settlement, rate,parValue,frequency,basis) ACCRINTM ACCRINTM(issue,maturity,rate,parValue, basis) ACCRINTM(issue,maturity,rate,parValue, basis) AmorDEGRC AmorDEGRC(cost,purchaseDate, firstPeriod,salvage,period,rate) AmorDEGRC(cost,purchaseDate, firstPeriod,salvage,period,rate,basis) AmorLINC AmorLINC(cost,purchaseDate, firstPeriod,salvage,period,rate) AmorLINC(cost,purchaseDate, firstPeriod,salvage,period,rate,basis) CoupDayBS CoupDaysBS(settlement,maturity, frequency) CoupDaysBS(settlement,maturity, frequency,basis)

274

ÂŠ2008 Vision Harvest, Inc.

Financial (continued) CoupDays CoupDays(settlement,maturity, frequency) CoupDays(settlement,maturity, frequency,basis) CoupDaysNC CoupDaysNC(settlement,maturity, frequency) CoupDaysNC(settlement,maturity, frequency,basis) CoupNCD(settlement,maturity,frequency) CoupNum(settlement,maturity,frequency) CoupPCD(settlement,maturity,frequency) CumIPMT(rate,nPeriods,presentValue,start, ` end,type) CumPRINC(rate,nPeriods,presentValue,start, end,type) DB DB(cost,salvage,life,period) DB(cost,salvage,life,period,month) DDB DDB(cost, salvage, life, period) DDB(cost, salvage, life, period, factor) Days360 Days360(start,end) Days360(start,end,method) DISC DISC(settlement,maturity,price, redemption) DISC(settlement,maturity,price, redemption,basis) DollarDE(fraction,base) DollarFR(decimal,base) Duration Duration(settlement,maturity,rate,yield, frequency) Duration(settlement,maturity,rate,yield, frequency,basis) Effect(rate,periods) FV FV(rate, nPeriods, payment) FV(rate, nPeriods, payment, presentValue) FV(rate, nPeriods, payment, presentValue, type) FVSchedule(value,rates)

Crystal Reports 2008 Designer 1 Workshop Appendix G: Installation and System Requirements Financial (continued) IntRate IntRate(settlement,maturity,purchase, redemption) IntRate(settlement,maturity,purchase, redemption,basis) IPmt IPmt(rate, period, nPeriods, presentValue) IPmt(rate, period, nPeriods, presentValue, futureValue) IPmt(rate, period, nPeriods, presentValue, futureValue, type) IRR IRR(values) IRR(values, guess) ISPMT(rate,period,nPeriods,value) MDuration MDuration(settlement,maturity,rate, yield,frequency) MDuration(settlement,maturity,rate, yield,frequency,basis) MIRR(values, financeRate, reinvestRate) Nominal(rate,periods) NPer NPer(rate, payment, presentValue) NPer(rate, payment, presentValue, futureValue) NPer(rate, payment, presentValue, futureValue, type) NPV(rate, values) OddFPrice OddFPrice(settlement,maturity,issue, first,rate,yield,value,frequency) OddFPrice(settlement,maturity,issue, first,rate,yield,value,frequency,basis) OddFYield OddFYield(settlement,maturity,issue, first,rate,purchase,redemption, frequency) OddFYield(settlement,maturity,issue, first,rate,purchase,redemption, frequency,basis) OddLPrice OddLPrice(settlement,maturity,last,rate, purchase,redmption,frequency) OddLPrice(settlement,maturity,last,rate, purchase,redmption,frequency,basis)

275

ÂŠ2008 Vision Harvest, Inc.

Financial (continued) OddLYield OddLYield(settlement,maturity,last,rate, purchase,redemption,frequency) OddLYield(settlement,maturity,last,rate, purchase,redemption,frequency,basis) Pmt Pmt(rate, nPeriods, presentValue) Pmt(rate, nPeriods, presentValue, futureValue) Pmt(rate, nPeriods, presentValue, futureValue, type) PPmt PPmt(rate, period, nPeriods, presentValue) PPmt(rate, period, nPeriods, presentValue, futureValue) PPmt(rate, period, nPeriods, presentValue, futureValue, type) Price Price(settlement,maturity,rate,yield, value,frequency) Price(settlement,maturity,rate,yield, value,frequency,basis) PriceDisc PriceDisc(settlement,maturity,rate, value) PriceDisc(settlement,maturity,rate, value,basis) PriceMat PriceMat(settlement,maturity,issue,rate, yield) PriceMat(settlement,maturity,issue,rate, yield,basis) PV PV(rate, nPeriods, payment) PV(rate, nPeriods, payment, futureValue) PV(rate, nPeriods, payment, futureValue, type) Rate Rate(nPeriods, payment, presentValue) Rate(nPeriods, payment, presentValue, futureValue) Rate(nPeriods, payment, presentValue, futureValue, type) Rate(nPeriods, payment, presentValue, futureValue, type, guess)

Crystal Reports 2008 Designer 1 Workshop Appendix G: Installation and System Requirements Financial (continued) Received Received(settlement,maturity,value, rate) Received(settlement,maturity,value, rate,basis) SLN(cost, salvage, life) SYD(cost, salvage, life, period) TBillEq(settlement,maturity,rate) TBillPrice(settlement,maturity,rate) TBillYield(settlement,maturity,rate) VDB VDB(cost,salvage,lifetime,start,end) VDB(cost,salvage,lifetime,start,end, factor) VDB(cost,salvage,lifetime,start,end, noSwitch) VDB(cost,salvage,lifetime,start,end, factor,noSwitch) XIRR XIRR(values,dates) XIRR(values,dates) XNPV(rate,values,dates) YearFrac YearFrac(start,end) YearFrac(start,end,basis) Yield Yield(settlement,maturity,rate,purchase, redemption,frequency) Yield(settlement,maturity,rate,purchase, redemption,frequency,basis) YieldDisc YieldDisc(settlement,maturity,purchase, redemption) YieldDisc(settlement,maturity,purchase, redemption,basis) YieldMat YieldMat(settlement,maturity,issue,rate, price) YieldMat(settlement,maturity,issue,rate, price,basis) Strings Length(str) Trim(str) TrimLeft(str) TrimRight(str) UpperCase(str) LowerCase(str) ProperCase(str)

276

ÂŠ2008 Vision Harvest, Inc.

Strings (continued) StrReverse(str) NumericText(str) ToNumber(x) ToText ToText (x) ToText (x, y) ToText (x, y, z) ToText (x, y, z, w) ToText (x, y, z, w, q) ToWords ToWords(x) ToWords(x, #places) ToWords(x, #places, formtype) ReplicateString(str, #copies) Space(x) InStr InStr(str1, str2) InStr(start, str1, str2) InStr(str1, str2, compare) InStr(start, str1, str2, compare) InStrRev InStrRev(inputString, findString) InStrRev(inputString, findString, startPosition) InStrRev(inputString, findString, startPosition, compare) StrCmp StrCmp(str1, str2) StrCmp(str1, str2, compare) Mid Mid(str, start) Mid(str, start, length) Left(str, length) Right(str, length) Val(str) ChrW(x) AscW(str) Filter Filter(inputStrings, searchString) Filter(inputStrings, searchString, include) Filter(inputStrings, searchString, include, compare)

Crystal Reports 2008 Designer 1 Workshop Appendix G: Installation and System Requirements Strings (continued) Replace Replace(inputString, findString, replaceString) Replace(inputString, findString, replaceString, startPosition) Replace(inputString, findString, replaceString, startPosition, count) Replace(inputString, findString, replaceString, startPosition, count, compare) Join Join(list) Join(list, delimiter) Split Split(inputString) Split(inputString, delimiter) Split(inputString, delimiter, count) Split(inputString, delimiter, count, compare) Roman Roman(value) Roman(value,form) URLEncode URLEncode(inputString) URLEncode(inputString, encodingScheme) URLEncode(inputString, encodingScheme,neverEncode) URLEncode(inputString, encodingScheme,neverEncode, alwaysEncode) URLEncode(inputString, encodingScheme,characterSet) URLEncode(inputString, encodingScheme,characterSet, neverEncode) URLEncode(inputString, encodingScheme,characterSet, neverEncode,alwaysEncode) URLDecode URLDecode(inputString) URLDecode(inputString, encodingScheme) URLDecode(inputString, encodingScheme,characterSet) Date/Time CurrentDate CurrentTime CurrentDateTime

277

ÂŠ2008 Vision Harvest, Inc.

Date/Time (continued) Date Date(dateTime) Date(YYYY, MM, DD) Time Time(dateTime) Time(HH, MM, SS) DateTime DateTime(date, time) DateTime(YYYY, MM, DD) DateTime(YYYY, MM, DD, HH, MM, SS) DateValue DateValue(number or string) DateValue(dateTime) DateValue(YYYY, MM, DD) TimeValue TimeValue(number or string) TimeValue(dateTime) TimeValue(HH, MM, SS) DateTimeValue DateTimeValue(number or string) DateTimeValue(date, time) DateTimeValue(date) DateTimeValue(YYYY, MM, DD) DateTimeValue(YYYY,MM,DD,HH, MM,SS) DateSerial(year, month, day) TimeSerial(hour, minute, second) IsDate(string or number) IsTime(string or number) IsDateTime(string or number) Year(x) Month(x) Day(x) DayOfWeek DayOfWeek(date) DayOfWeek(date, firstDayOfWeek) Hour(x) Minute(x) Second(x) MonthName MonthName(month) MonthName(month, abbreviate) WeekdayName WeekdayName(weekday) WeekdayName(weekday, abbreviate) WeekdayName(weekday, abbreviate, firstDayOfWeek) Timer

Crystal Reports 2008 Designer 1 Workshop Appendix G: Installation and System Requirements

Date/Time (continued) DateAdd(intervalType, nIntervals, startDateTime) DateDiff DateDiff(intervalType, startDateTime, endDateTime) DateDiff(intervalType, startDateTime, endDateTime, firstDayOfWeek) DatePart DatePart(intervalType, inputDateTime) DatePart(intervalType, inputDateTime, firstDayOfWeek,) DatePart(intervalType, inputDateTime, firstDayOfWeek, firstWeekOfYear) First Day of Week Constants UseSystem Sunday Monday Tuesday Wednesday Thursday Friday Saturday First Week of Year Constants UseSystem FirstJan1 FirstFourDays FirstFullWeek ShiftDateTime(inputDateTime, inputTimeZone,outputTimeZone)

Date Ranges (continued) Next 91To365Days Calendar1stQtr Calendar2ndQtr Calendar3rdQtr Calendar4thQtr Calendar1stHalf Calendar2ndHalf LastYearMTD LastYearYTD

Date Ranges WeekToDateFromSun MonthToDate YearToDate Last7Days Last4WeeksToSun LastFullWeek LastFullMonth AllDatesToToday AllDatesToYesterday AllDatesFromToday AllDatesFromTomorrow Aged0To30Days Aged31To60Days Aged61To90Days Over90Days Next30Days Next31To60Days Next61To90Days

Type Conversion CBool(number or currency) CCur(number, currency or string) CDbl(number, currency,string or boolean) CStr CStr(x) CStr(x, y) CStr(x, y, z) CStr(x, y, z, w) CStr(x, y, z, w, q) CDate CDate(number or string) CDate(dateTime) CDate(YYYY, MM, DD) CTime CTime(number or string) CTime(dateTime) CTime(HH, MM, SS)

278

Arrays MakeArray(x, …) UBound(array) Sum(x) Average(x) StdDev(x) PopulationStdDev(x) Variance(x) PopulationVariance(x) Maximum(x) Minimum(x) Count(x) DistinctCount(x) Ranges HasLowerBound(x) HasUpperBound(x) IncludesLowerBound(x) IncludesUpperBound(x)

Crystal Reports 2008 Designer 1 Workshop Appendix G: Installation and System Requirements Type Conversion (continued) CDateTime CDateTime(number or string) CDateTime(date, time) CdateTime(date) CDateTime(YYYY, MM, DD) CDateTime(YYYY, MM, DD, HH, MM, SS) Programming Shortcuts Choose(index, choice1, …) IIF(expression, truePart, falsePart) Switch(expr1, value1, expr2, value2, …) Evaluation Time BeforeReadingRecords WhileReadingRecords WhilePrintingRecords EvaluateAfter(x) Print State CountHierarchicalChildren (groupLevel) CurrentPageOrientation HasValue (param-fld) Previous(fld) Next(fld) IsNull(fld) PreviousIsNull(fld) NextIsNull(fld) PageNumber TotalPageCount PageNofM RecordNumber GroupNumber RecordSelection GroupSelection InRepeatedGroupHeader OnFirstRecord OnLastRecord DrillDownGroupLevel HierarchyLevel(groupingLevel) Document Properties PrintDate PrintTime PrintTimeZone ModificationDate ModificationTime DataDate DataTime DataTimeZone ReportTitle

279

Print State (continued) ReportComments Filename FileAuthor FileCreationDate CurrentCEUserName CurrentCEUserID GroupingLevel (fld) CurrentCEUserTimeZone ContentLocale Alerts IsAlertEnabled(alertName) IsAlertTriggered(alertName) AlertMessage(alertName) Additional Functions 2000 (u212000.dll) DateTo2000(Date,Number) DTSTo2000(DateTimeString,Number) dts (u2ldts.dll) DTSToDate(DateTimeString) DTSToTimeString(DateTimeString) DTSToSeconds(DateTimeString) exch (u2lexch.dll) ExchGetID(address) ExchGetOrganization(address) ExchGetSite(address) ExtractString(orig, startStr, endStr) EventNumber(eventNum) ExchGetPath(Path) ByteToText(byte) finra (u2lfinra.dll) FRCurrentRatio(CurrentAssets, CurrentLiabilities) FRQuickRation(CurrentAssets, Inventories, CurrentLiabilities) FRDebtEquityRatio(TotalLiabilities, TotalEquity) FREquityVsTotalAssets(TotalEquity, TotalAssets) FRNetProfitMargin(NetProfit, Sales) FRGrossProfitMargin(GrossProfit, Sales) FROperatingProfitMargin( OperatingProfit, Sales) FRInterestCoverage(CashFlow, InterestExpenses) FRCashFlowVsTotalDebt(CashFlow, TotalDebt)

Crystal Reports 2008 Designer 1 Workshop Appendix G: Installation and System Requirements Additional Functions (continued) FRReturnOnEquity(NetProfit, TotalEquity) FRReturnOnNetFixedAssets(NetProfit, NetFixedAssets) FRReturnOnTotalAssets(NetProfit, TotalAssets) FRReturnOnInvestedCapital(NetProfit, TotalBankDebts, TotalEquity) FRReturnOnCommonEquity(NetProfit, PreferredDividend, CommonEquity) FREarningsPerCommonShare( NetProfit, PreferredDividend, NumOfCommonShare) FRAccRecTurnover( AccountReceivable, Sales, NumOfDays) FRInventoryTurnover(Inventory, Sales, NumofDays) FRPriceEarningsRatio(MarketPrice, EarningsPerShare) FRDividendYield(Dividend, MarketPrice) samp1 (u2lsamp1.dll) Now Picture(string, picture) LooksLike(string, mask) Soundex(string) 2000 (u25200.dll) DateTimeTo2000(DateTime, Number) dts (u25dts.dll) DTSToDateTime(DateTimeString) DTSToTimeField(DateTimeString) DateTimeToDate(dateTime) DateTimeToTime(dateTime) DateTimeToSeconds(dateTime) azalea (u2I2000.dll) Several Barcoding functions

280

ÂŠ2008 Vision Harvest, Inc.

Crystal Reports 2008 Designer 1 Workshop Appendix G: Installation and System Requirements

Following is a list of Operators available in the right list in the Formula Editor: Major Categories are listed in bold Function names are listed in regular text Arithmetic Add (x + y) Subtract (x – y) Multiply (x * y ) Divide ( x / y) Integer divide (x \ y) Percent ( x % y ) Modulus ( x mod y) Negate (-x) Exponentiate (x ^ y) Conversion To currency ($x) Comparisons Equal (x = y) Not equal (x <> y) Less than (x < y) Greater than (x > y) Less or equal (x <= y) Greater of equal ( x>=y) Strings Concatenate (x + y) Concatenate (x & y) Subscript (x [y]) In string (x in y) Insert Empty String (“”) Ranges In range (x in y) Make range (x to y) Left endpoint excluded range (x_to y) Right endpoint excluded range (x to_y) Both endpoints excluded range (x_to_y) Up to (upTo x) Up to but not including(upTo_x) Up from (upFrom x) Up from but not including(upFrom_x) Boolean Not (not x) And (x and y) Or (x or y) Xor (x xor y) Eqv (x eqv y) Imp ( x imp y)

281

Arrays Make array ([x, …]) Subscript (x [y]) In array (x in y) redim x [n] redim preserve x [n] Pattern Starts with (x startsWith y) Like pattern (x like y) Control Structures if x then y else z select x case a: y default: z for i := a to b step c do z exit for while x do y do x while y exit while option loop maxIterations Other Parentheses ( (x) ) Assignment (x := y) Comment (//) Date-time literal (#…#) Scope Local Global Shared

Crystal Reports 2008 Designer 1 Workshop Appendix G: Installation and System Requirements Variable Declarations BooleanVar x := y; NumberVar x := y; CurrencyVar x := y; DateVar x := y; TimeVar x := y; DateTimeVar x := y; StringVar x := y; NumberVar range x := y to z; CurrencyVar range x := y to z; DateVar range x := y to z; TimeVar range x := y to z; DateTimeVar range x := y to z; StringVar range x := y to z; BooleanVar array x := [y, z, …]; NumberVar array x := [y, z, …]; CurrencyVar array x := [y, z, …]; DateVar array x := [y, z, …]; TimeVar array x := [y, z, …]; DateTimeVar array x := [y, z, …]; StringVar array x := [y, z, …]; NumberVar range array x := [a to c, y to z, …]; CurrencyVar range array x := [a to c, y to z, …]; DateVar range array x := [a to c, y to z, …]; TimeVar range array x := [a to c, y to z, …]; DateTimeVar range array x := [a to c, y to z, …]; StringVar range array x := [a to c, y to z, …];

282

Crystal Reports 2008 Designer 1 Workshop Appendix G: Installation and System Requirements

NOTES

283

ÂŠ2008 Vision Harvest, Inc.

Crystal Reports 2008 Designer 1 Workshop Appendix G: Installation and System Requirements

NOTES

284

ÂŠ2008 Vision Harvest, Inc.

Crystal Reports 2008 Designer 1 Workshop Appendix H: Glossary

ÂŠ2008 Vision Harvest, Inc.

Appendix H Glossary

285

Crystal Reports 2008 Designer 1 Workshop Appendix H: Glossary

Conditional Formatting Constant

Glossary The data field formatting option that can only be turned on or off based on the presence or absence of an attribute. See also, Conditional Formatting An active database is a database that has been selected for use in a report ASPs are web pages that run under Microsoft’s Internet Information Server (IIS) version 3.0 and later. They are dynamic web pages that can be viewed from many common web browsers. You can use Crystal’s Automation Server, the ActiveX Controls, and Active Data Driver to create active web sites that display reports from an Internet or intranet site Crystal Reports Add-ins let you create a report directly from Microsoft Excel or Microsoft Access An operation that summarizes data. This is often used with an SQL query to combine records and summarize a field or fields A data field is inserted into the Column section when first clicking on the field and then clicking this option A field is inserted into the row section when first clicking on the field and then clicking this option Aliases are pointers that tell the program where it should look for a database field. You can use alias names on tables in Crystal and on table and fields in Crystal Dictionaries An option in the Report Gallery box that allows the user to open existing reports An option in the Select Expert box. If you leave “any value” in the box, you’ve chosen not to apply any conditions to the data We can define a background color for each section. The background fill is not adjustable and always adjusts itself to the width of the page and the defined height of the section An option in the Select Expert box. Choose records that fall between certain values; perhaps call center agent “talk time” that falls between 2 minutes and 6 minutes or hourly wages between $4.75 and $10.00 A Boolean expression is an expression that defines a logical relationship between two or more items. This type of expression always results in a TRUE or FALSE answer A Browser is an application that enables viewing of documents in HTML format Crystal formulas are not case specific. Caps and lower case are considered the same Boxes found in many formatting and editing options that allow the user to turn a feature “On” or “Off ” Columns are fields that will display as a group across the Cross-Tab A column is the display of data from a single field or formula. Columns run down the page To concatenate is to join two or more text strings together to form a single continuous string. The concatenate operator is the plus sign + In an If…then…else statement the condition is the If part of the formula. It is a test to be performed. The results of the test determine whether Crystal performs the Then tasks or the Else tasks Conditional formatting is formatting that applies on if certain situations occur. For example, you might format a number field to display in red if the number is negative A value that is fixed and unchanging in a formula

Control Sort

An object used to allow a user to control the sort order on a group or field

Absolute Formatting Active Database Active Server Pages

Add-ins Aggregate Function Add Column Add Row Alias Another report Any Value Background Color Between

Boolean expression Browser Case Sensitive Check Boxes Columns Column Concatenate Condition

286

Crystal Reports 2008 Designer 1 Workshop Appendix H: Glossary

Glossary Cross-Tabs Control Structures

Current Date Database Data Source Data Date Data File

Data Time Debug Details Section Dictionary

Dynamic Link Library Equal Join Evaluation time

Export

Fields

Flat Formula For loop Formatting Toolbar Formatting Formula

A report type option that produces Cross-Tab reports to show relationships between different data fields Control structures control the flow of logic in a formula. You use them to build formula conditions that, if met, trigger specific consequences or repeat a sequence of actions under certain conditions. Control structures are if…then…else, for loops, while loops, and select expressions This function will produce a date based on your computer system’s date Any of a variety of information gathering tools. The tables used in Crystal Reports are contained within databases A particular set of tables, queries, dictionaries or stored procedures that provide data for a report Prints the date that the data in the report was last refreshed Found in the Report Gallery after selecting Custom reports. This connection is used for PC based databases run from the C: drive or from a server. Examples include MS-Access, Paradox, Btrieve, Dbase and FoxPro Prints the time that the data in the report was last refreshed Eliminates errors that occur when you run a formula The Details section of a report contains a row for each record returned from the database Found in the Report Gallery. Used to create reports from a Crystal Dictionary or from a Crystal Query. Dictionaries act as a micro-view of the database. They allow tables and fields to have meaningful names A Dynamic Link Library (DLL) is a file that contains Windows functions. DLLS are used by developers to extend the capabilities of Windows applications Refers to linking tables within the Visual Linking Expert. An Equal Join requires that to join a record from the left table, there must be an equal match in the right table Evaluation time refers to the stage in the reporting process when a formula is evaluated. The three evaluations time functions are: BeforeReadingRecords, WhileReadingRecords and WhilePrintingRecords To export is to distribute the report to a disk file or through e-mail to a different format. Crystal allows you export to a number of spreadsheet, word processing, database and HTML formats Fields are accumulations of data about a particular subject stored within a table in a database. Fields are selected from tables and placed on the report in a variety of ways, the most basic being, from the Field Explorer box early in the development of your report. In the formula creation process, Fields are always shown within “curly braces” A formula that does not reference any database field For/Next loops enable you to evaluate a sequence of statements multiple times. for i := a to b do c Available for use in both the Design and Preview windows. You can choose to display it or hide it. Used to quickly format an object. A fast way to format both text and numbers Allows the user to change the appearance of the report based on a wide variety of criteria A formula is a statement that performs some calculation or modification to data from the database or constant value

287

Crystal Reports 2008 Designer 1 Workshop Appendix H: Glossary

Formula Editor Free-Form Placement Functions

Glossary The dialog box used to create and edit formulas. It contains tools for inserting fields, functions and operators into a formula, for checking formula syntax, for typing formulas An option that allows you to place fields freely on your report rather than forcing fields to be placed at fixed points A built-in procedure or subroutine used to evaluate, calculate or transform data.. Us functions as a kind of short hand for complex operations. The syntax for functions is as follows: FunctionName (argument, argument, argument)

Grand Total Greater than

A grand total is the summary of all values in a column, for the entire report An option in the Select Expert box. Using this choice selects only values greater than the criteria you set Grid The grid is an underlying network of points that you can use to help you line up objects on a report Group A group is a set of records that are related to each other in some way. You specify a field on which to group and Crystal Reports puts all records with the same value in that group Group Header and The Group Header and Footer are sections that appear at the beginning and end of each Footer group in the report Guidelines Non-printing lines that you can use to align, move and resize objects in the report Hierarchical Grouping Enables you to group data in a report to show hierarchical relationships. When you group data hierarchically, you sort information based on the relationship between two fields Highlighting Expert The Highlighting Expert is used to change the font, border or background attributes of an object under certain conditions Hyperlink An interactive link to a web site, another file, an e-mail address, the current field value, or another Crystal Report file Index An index is a file that identifies the location of each record in a database. Since this is a small file and can be searched or sorted quickly, an index file makes locating fields a quicker process. Just like using an index in a manual makes locating a specific topic quicker If Then Else If …Then...Else… Formulas describe conditions that control the display of data. In common language it might look like this…If the Last Year’s Sales figure is less than $50000 Then make the value bold and italicized, Else (otherwise) make no changes. These formulas are particularly helpful to bring attention to significantly higher or lower performance or to risk factors associated with specific values InStr This function allows us to find the position of a particular character. For example InStr (“abc-def”,”-”) would test the string to find the position for the “-” character and would return the number 4. This is a great tool for extracting a group of characters from a field when the starting or ending position is variable from field to field Keep Together This option refers to keeping a section together in relation to page breaks. If there isn’t enough page left to print the section, then rather than having the page break occur in the middle of the section, the entire section is carried over to the next page Last Modification Date Shows the date when the report was last modified (new tables added, etc.) based on the PC’s calendar/clock Last Modification Time Shows the time when the report was last modified based on the PC’s clock

288

Crystal Reports 2008 Designer 1 Workshop Appendix H: Glossary

Less than

Linking Map Expert Mid

Multi-column Nesting Null Object Frame

One of

One-to-Many Relationship Operators

Order of precedence

Outer Join

Page Header and Footer Parameter field

Glossary An option within the Select Expert box. Perhaps you want to see all sales less than $50,000. Choosing this option will display all values of $49,999.99 and less (including negative DOS characters like ‘?’ or ‘*’ can be used with this option to select records with similar, but not identical values. You may enter ‘is like D*’ to display records that include Denver, Dallas, Des Moines or Dusseldorf A method of bringing two or more databases together within a single report and joining tables of data with at least one matching field The Map Expert is a powerful tool for organizing your data by placing geographic maps on a report. You can drill down on these maps to see underlying data Applies to formula creation. This function works much the same way as Subscript [], and is a common function in other programming type application. It allows us to specify which character position we want to extract from a field value An option in the Report Gallery as you prepare to develop a new report. Useful for creating mailing labels or other multi-column text documents Nesting means to use one If… Then… Else statement inside another Null means there is no data in that cell in the database. NOTE: it does NOT mean that the value is zero An object frame is a rectangular cursor that appears as an aid to placing database fields and formulas on your report. When you move the object frame to a location on the report, then click the mouse button, Crystal Reports places the object in the location you specified. This can also be used to refer to the rectangular box that indicates the location of an on-demand subreport in a report Found in the Select Expert box. If you want to see several specific values (but not others) you can choose “one of” and then enter just those values you want to see such as 5000, 7000, 10,000. The “is not” button can be used here as well One-to-many refers to a situation occurring in linked database tables in which one record in a table can be matched to many records in another table Special symbols that describe an operation or an action to take place between two or more values. Operators are + - * and / <> = etc. Syntax rules allow you to place a space before and after and operator or omit spaces Order of precedence The order of precedence is a set of rules that determines the order in which arithmetic operations take place in a formula. Multiplication and division occur before addition and subtraction Applies to joining tables. There are Left Outer Joins and Right Outer Joins. A Left Outer Join selects all the records from the left table and any matching ones from the right table. A Right Outer Join selects all the records from the right table and any matching tables from the left These sections define items that will appear at the top and bottom of each page of a report A special kind of field that prompts the user for a value. Use of parameter fields lets you change the options for the report at run time on the fly

Page Orientation

Used to specify if a report or a section of the report is landscape or portrait

Pick List

A list for a user to select a value for a parameter. You can import pick lists from any text file

289

Crystal Reports 2008 Designer 1 Workshop Appendix H: Glossary

Glossary Print Date Print Time Population standard deviation Population variance Range of Values

Report Header and Footer Report Gallery Refresh

Replicate String Report Comments Reset Page Number After Rows Row Ruler

Running Totals Sections

Section Expert Select Statement

Select Expert Server-side processing

Takes the date from your PC’s calendar/clock and prints it on the report Takes the time from your PC’s clock and prints it on the report This is a statistical test of how the values in an entire population deviate from the mean or average value for the population This is the square of the population standard deviation This will limit the amount a bar can represent. If the value being graphed exceeds the limit then the bar stops at the limit you chose. This option does not limit items from being represented, but instead limits the amount being graphed Sections that define items to print only one time, at the beginning or end of a report The Report Gallery is a dialog box that appears when you begin a new report. It allows you to define how you are going to create the report A Toolbar option that allows the user to require an update to the report, using the most recent data stored in the database. The button resembles a lightning bolt. There are also certain changes made to the report that automatically generate a “Refresh” of the data Applies to creating formulas. This function allows us to represent integer values with symbols such as an “*” Automatically places any comments you make about the report in the File/ Summary Info window along with other information about the report for future reference Like setting page breaks we can reset the page numbers back to 1 after a section has been printed Rows are fields that will display as a group down a Cross-Tab A row is the display of data from a single record in the database. Rows run horizontally across the page The ruler provides a visual reference for positioning and resizing objects on the report. It provides measurements and shows where guidelines are located. You can view the ruler in either Design or Preview view or both These totals are calculated on a record-by-record basis. They total all records in the report or group and can be formatted to work conditionally Each Section corresponds to a specific part of the final, printed report. You will be inserting data fields, text and pictures into different Sections. The Section names are seen in the Design Screen on the left side of the window. Examples are Report Header, Page Header, Details, Report Footer, Page Footer etc. A dialog box used to apply formats to the sections of the report rather than to individual fields The Select statement is similar to an If statement but allows for multiple choices in a manner that is more clear and less repetitive. select x case a: y default: z A powerful Crystal tool that allows the user to determine how data will be displayed on the report. It is most easily accessed by clicking the button on the Tool Bar Server-side processing is a feature that allows you to set up reports that perform the majority of their processing on the server. These reports push only relevant details to your computer, thus saving time and memory

290

Crystal Reports 2008 Designer 1 Workshop Appendix H: Glossary

Glossary Smart Linking

Sort SQL/ODBC

Standard Deviation String Formulas Strings Subreports Subtotals Summary Fields Suppress Switch function

Syntax Text Object Toolbar

Total Top N

Total Page Count ToText

Truncate

A very useful feature of Crystal Reports that automatically links common tables within databases. Users can allow Smart Linking to accomplish links or joins or, decide to make the links “manually” The process of organizing the records of the report in a particular order. You can sort in ascending or descending order on any field in the Details section SQL stands for Structured Query Language; a frequently used programming language used to “speak” to the database. ODBC stands for Open Database Connectivity. This connection is used typically for server-based databases like Oracle, Sybase, Informix, and MSSQL Server. ODBC was developed by Microsoft as a “translator” to overcome the variations in dialects between databases. It allows Crystal to communicate with almost any database Standard deviation is the square root of the variance. This is a statistical test of how various values in a set of values deviate from the mean or average value for the set String Formulas are used to perform functions on fields that cannot be acted upon by mathematical formulas. They are useful for modifying how text is displayed on a report A string is a series of textual characters. The characters may be letters, numbers or special characters A subreport is any report within another report. It has all the characteristics of a report except that it cannot contain another subreport Once you have broken a report down into groups, it is possible to have subtotals displayed at the bottom of each group A field that determines the sum, average, minimum, maximum, or count of values in a group. Summary fields are based on a field in the Details section of the report Found within the Section Expert. Suppress works like hide except that the section is permanently suppressed and drill-down capabilities are not allowed The argument list for Switch consists of pairs of expressions and values. Switch evaluates the expressions from left to right, and returns the value associated with the first expression to evaluate to True A set of rules that specifies the proper way to use functions and operators in a formula A text object is an object in a report that can contain typed text, fields from a database or formula fields A quick way to select functions that can also be found in the Menu Bar. Use these as a shortcut to all the features. When holding your pointer over the boxes In the Toolbar you will see their function. It will also show in the Status Bar A total is a sum of values. Subtotals and grand totals are different types of totals Top N is a function that allows the user to make the report display only the highest or lowest values. It is often used to create a group that displays the top sales figures or the lowest accident rate Calculates and prints the total pages in the report This versatile function can, among other things, convert a date value into a text value. It also allows us to format the value to display any or all portion of the data. See a complete listing of format options in the “To Text” on-line help section This function will “cut off ” decimal values based on the “#places” specified. If you specify 0 places then the number 7.59 would be displayed as 7. Notice that truncating a number doesn’t perform any rounding

291

Crystal Reports 2008 Designer 1 Workshop Appendix H: Glossary

Underlay Following Sections Variance View Options

Visual Linking Expert X+2 Boxes

Wizards Wildcard Word Wrap

Glossary Rather than having the section print in a linear order, a section can start printing on the page at the same spot the section below it begins to print. This is a great tool for displaying a group graph next to the group data Variance is the square of the standard deviation. It is a measure of the amount by which all values in a group vary from the mean (average) value in the group These options allow control over how much “space” is allowed for either the Design or the Preview portions of the report. Turning these properties “On” or “Off ” will reduce or increase the space. The user decides which view is most useful for the task A powerful tool to create the desired joins between tables These boxes, seen in the formatting functions, allow the user to set criteria for display of certain selected data. Clicking an “X+2” box accesses a formula editor where the user has the opportunity to set the conditions for the display of the data On the Start Page, choose one of four wizards designed to assist report writers in building reports quickly and efficiently A wildcard is a character that takes the place of any character or characters. The ? wildcard takes the place of a single character. The * wildcard takes the place of multiple characters Word wrap is a property of text boxes by which Crystal Reports moves a word to the next line when it is too long to fit on the current line

292

Crystal Reports 2008 Designer 1 Workshop Appendix H: Glossary

ÂŠ2008 Vision Harvest, Inc.

NOTES

293

Crystal Reports 2008 Designer 1 Workshop Appendix H: Glossary

ÂŠ2008 Vision Harvest, Inc.

NOTES

294

Crystal Reports 2008 Designer 1 Workshop Index

ÂŠ2008 Vision Harvest, Inc.

Index

295

Crystal Reports 2008 Designer 1 Workshop Index

ÂŠ2008 Vision Harvest, Inc.

Favorites 32, 37, 38, 59, 83, 102, 124, 136 Field Explorer 19, 20, 22, 23, 25, 131, 26, 29, 64, 65, 132, 137, 136, 293 File Options 90, 253 Font 48, 191, 253 For Loop 37 Format Chart 187, 188 Format Editor 45, 51, 64, 81, 103, 163, 165, 251, 253 Format Painter 42, 38, 64, 128, 130, 131, 132 Formatting Sections 163, 165 Formatting Toolbar 293 Formula Editor 3, 37, 131, 80, 131, 132, 133, 134, 132, 131, 139, 294 Formula Editor Toolbar 134, 131 Formula Expert 131 Formula Workshop 128 Frame 295

About Crystal Reports 2 Alias 28 Align 27, 28, 30 Arrange Lines 30

B Bar Chart map 225 Basic Syntax 129 Between 292 Bookmark 154, 188 Boolean formula 165 BooleanVar 282 Border 46 Browse 25

C G

Calculations 135, 156 Chart 176, 181, 182, 183, 186, 181, 187, 188, 189, 190, 192, 193, 194, 212, 215, 217, 225, 226, 251 Chart Expert 181, 182, 186, 181, 187, 188, 193, 194 Chart Options 188, 189, 190 Concatenate 145, 146, 281, 292 Conditional Formatting 161, 171, 75, 292 Control Structures 15, 75, 180 Create a New Connection 13 Criteria 292, 282, 78 Crosstab 282, 251 Crystal Dictionaries 22

D Database Expert 10, 14, 16, 38, 14, 114, 119, 121, 122, 124, 136, 178, 211, 213 DateTimeVar 282 DateVar 47, 49, 149, 191 Default 18, 21, 148, 186, 230 Design Window 17, 19, 187 Details Section 293 Dictionary 226, 180 Dot Density map 176 Drill Down 180

E E-mail 50 Equal Count 225 Equal Join 293 Equal Ranges 225, 229 Evaluation Time 279 Expert 3, 199, 292, 293, 294, 295, 296, 297, 298 Expert Tools Toolbar 90 Exporting 197

Getting Help 37 Global 281 Graduated map 225, 189, 212 Graphics 57 Grid 27, 90, 91, 37, 64, 294 Group 3, 91, 20, 36, 94, 95, 215, 101, 110, 139, 173, 179, 186, 194, 87, 213, 294 Group Expert 90, 91, 94, 95, 99, 100, 101 Group Footer 90, 105, 95, 100, 101, 102, 107, 178, 179 Group Header 90, 37, 86, 105, 106, 107, 94, 96, 95, 103, 105, 294 Group Sort Expert 86, 106, 94, 107, 94 Group Tree 36, 29, 30, 31, 248, 252 Guidelines 8, 294

H Help 2, 37, 80, 157, 171, 201 Hide 164, 177, 179, 194, 228 Highlighting Expert 3, 51, 162, 168, 294 HTML 50, 243, 294

I If Then Else 145, 294 Insert Summary 100, 104, 214 Insert Tools Toolbar 54, 56, 57, 87, 181 Installation 267, 268 IsNull 145, 279

J Join Types 118

L Layout 29, 90, 164, 182, 183, 240, 247, 248, 252 Left Outer Join 295 296

Crystal Reports 2008 Designer 1 Workshop Index Legend 188, 48, 49 Line 12, 122, 119, 212 Link 48, 114, 115, 122, 120, 123, 293 Linking 114, 119, 122, 123, 249, 293, 295, 297, 298 Links 119, 213 Links Tab 122 Local 281

M Map Expert 224, 225, 227, 228, 229, 295 Map Navigator 231, 232 Mapping 223, 225 Maps 225, 230, 231 Margins 59

N Natural Break 226 Navigation 94 New Page After 164 New Page Before 165 NUDGE 27 NumberVar 282

ÂŠ2008 Vision Harvest, Inc.

S Scope 281 Section Expert 162, 163, 165, 166, 171, 72, 296, 297 Sections 162, 163, 164, 165, 71, 296, 298 Select Expert 70, 50, 75, 82, 84, 46, 72, 292, 294, 295, 296 Selecting 24, 25, 26, 81, 83, 164, 211, 251 Selection Criteria 78 Size 86, 24, 25, 27, 38, 25 Smart Linking 42, 51, 47, 249 Sort 24, 46, 200 Special Fields 240, 297, 129, 130 SQL 226, 297, 12, 20, 26, 15 SQL/ODBC 145, 194 Standard Deviation 282, 129 Standard Toolbar 23, 282 StringVar 86 Subreports 46 Subscript 129 Summarize 297 Suppress 164

Object Frame 295 ODBC 3, 13, 12, 13, 16, 15, 225, 297 OLAP 295 One-to-Many 2

Template 42, 43, 44, 192, 193, 212, 216, 217, 218 Template Expert 42, 43, 44, 218 Text Object 30, 31, 60, 61, 83, 103, 145, 297 TimeVar 282 Toolbars 17, 19 TopN 87

Page Breaks 165, 248 Page Footer 164, 25, 296 Page Header 22, 24, 228, 241, 242, 248, 295, 296 Page Number 64, 164, 296 Parameter 20 Percentage 104, 106, 107 Pick List 257 Placement 65

Underlay 164, 298

Web Format 198 Wizard 210, 211, 212, 215, 217, 219

Queries 20

R Record Sort Expert 22 Report Explorer 20, 21, 22 Report Footer 98, 100, 102, 46, 247, 254 Report Gallery 11, 22, 245, 247, 211, 212 Report Header 31, 38, 82, 210, 83, 103, 252, 165 Report Options 79, 49, 210 Report Wizard 27, 296 Repository 295 Repository Explorer 20

V Variable 282 Visual Basic 2 Visual Linking Expert 293, 298

X X+2 50, 51, 178, 298 Xtreme Sample Database 14, 83, 102, 121, 122, 124, 140, 145, 217, 219, 263, 264

Z Zoom 26, 230, 233

297

Crystal Reports 2008 Designer 1 Workshop Index

ÂŠ2008 Vision Harvest, Inc.

298

Crystal Reports 2008 Designer 1 Workshop Index

ÂŠ2008 Vision Harvest, Inc.

NOTES

299

Crystal Reports 2008 Designer 1 Workshop Index

ÂŠ2008 Vision Harvest, Inc.

NOTES

300