5 User’s Guide. - California

Crystal Reports™ 8.5

User’s Guide

Seagate Software IMG Holdings, Inc.

915 Disc Drive

Scotts Valley

California, USA 95066

© 2001 Seagate Software Information Management Group Holdings, Inc., 915 Disc

Drive, Scotts Valley, California, USA 95066. All rights reserved.

Seagate, Seagate Software, Crystal Reports, Crystal Enterprise, Crystal Analysis,

Seagate Info, Seagate Holos and the Seagate logo are trademarks or registered

trademarks of Seagate Software Information Management Group Holdings, Inc.

and/or Seagate Technology, Inc. All other trademarks referenced are the property

of their respective owner.

Documentation Version 1. Febuary 2001.

No part of this documentation may be stored in a retrieval system, transmitted or

reproduced in any way, except in accordance with the terms of the applicable

software license agreement. This documentation contains proprietary information

of Seagate Software IMG Holdings, Inc., and/or its suppliers.

Crystal Reports User’s Guide i

Contents

Chapter 1: Welcome to Crystal Reports

About Crystal Reports ....................................................................... 2

About this guide ................................................................................ 3

Chapter Contents .................................................................................................. 3

Online help ........................................................................................................... 7

Product registration ............................................................................................... 8

Technical Support ................................................................................................. 8

Command, button, and key conventions ........................................... 9

Chapter 2: What’s New

Web reporting ................................................................................. 12

Increased value of the Crystal Enterprise Web Component Server ....................... 12

Complete XML support for the Web .................................................................... 13

Accomplish more with faster processing .............................................................. 14

Improved interactivity with parameters ................................................................ 14

Customizable DHTML Report Viewer ................................................................. 14

Optimize report performance with Crystal Reports documentation ...................... 15

Log on to APS folders from Crystal Reports .......................................................... 15

Display web reports independent of server location ............................................ 15

Extend reporting abilities with additional applications ......................................... 15

Report Designer features ................................................................ 15

Create Report Alerts for instant notification ......................................................... 15

Export data to PDF, XML, RTF, and improved HTML ........................................... 16

Access additional data sources ............................................................................ 16

Copy report objects to other applications ............................................................ 16

Summarize data across hierarchical groups ......................................................... 16

Enhance formulas with new and improved functions ........................................... 16

Customize default font options ............................................................................ 17

Visualize data faster with improved charting and mapping .................................. 17

ii Crystal Reports User’s Guide

Analyze data types quickly .................................................................................. 17

Save preview pictures by default ......................................................................... 17

Developer features .......................................................................... 17

Embed the Crystal Reports Designer Control ....................................................... 17

Utilize the unified RDC object model .................................................................. 17

Re-distribute the ASP Web Report Server ............................................................. 18

Access XML data at run time ............................................................................... 18

Facilitate administration with the License Manager ............................................. 18

Report Designer Component 8.5 ......................................................................... 18

RDC run time distribution ................................................................................... 18

Lotus Domino sample applications ...................................................................... 19

Additional information .................................................................... 19

Chapter 3: Installing Crystal Reports

Installing Crystal Reports ................................................................ 22

Installation requirements ................................................................ 22

Installing on a local machine from CD ............................................ 23

Installing to and from a network server .......................................... 24

Installing Crystal Reports to a network ................................................................. 25

Installing Crystal Reports from a network ............................................................. 26

Customizing your installation ......................................................... 27

Installing Crystal Enterprise ............................................................ 29

Getting Started .................................................................................................... 30

Running the installation ....................................................................................... 30

Chapter 4: Quick Start

Learning how to use Crystal Reports ............................................... 34

Sample reports .................................................................................................... 34

Sample data - Xtreme.mdb .................................................................................. 35

Report Experts ................................................................................. 36

Crystal Reports User’s Guide iii

Standard .............................................................................................................. 37

Form Letter .......................................................................................................... 38

Form ................................................................................................................... 38

Cross-Tab ............................................................................................................ 38

Subreport ............................................................................................................ 38

Mail Label ........................................................................................................... 38

Drill Down .......................................................................................................... 38

OLAP .................................................................................................................. 39

Quick start for new users ................................................................ 39

Before you begin ................................................................................................. 40

Creating the report .............................................................................................. 40

Record Selection ................................................................................................. 53

Grouping and sorting .......................................................................................... 57

Completing the report ......................................................................................... 60

Quick start for advanced users ....................................................... 61

Chapter 5: Report Design Concepts

Basic report design .......................................................................... 68

Deciding on the content of the report ............................................ 68

Stating the purpose .............................................................................................. 68

Determining the layout of the report .................................................................... 69

Finding the data .................................................................................................. 70

Manipulating the data ......................................................................................... 71

Determining printing area characteristics ............................................................ 72

Developing a prototype on paper ................................................... 73

Chapter 6: Introduction to Reporting

Report creation options .................................................................. 76

Report Experts ..................................................................................................... 76

Another report ..................................................................................................... 76

iv Crystal Reports User’s Guide

New report .......................................................................................................... 76

Report data sources ........................................................................ 77

Database ............................................................................................................. 77

Queries ............................................................................................................... 78

Dictionaries ......................................................................................................... 78

Choosing data sources and database fields ..................................... 79

The Data Explorer ............................................................................................... 79

The Field Explorer ............................................................................................... 81

About the report design environment ............................................. 82

Design tab ........................................................................................................... 82

Preview tab ......................................................................................................... 85

Creating a new report ..................................................................... 88

Selecting the data source ..................................................................................... 88

Linking multiple tables ........................................................................................ 90

Placing data on the report ................................................................................... 91

Formatting data ................................................................................................... 98

Record selection .................................................................................................. 98

Grouping, sorting, and summarizing data ............................................................ 99

Using the drill-down option on summarized data ................................................ 99

Using the zoom feature ..................................................................................... 100

Inserting page headers and footers ..................................................................... 101

Adding a title page to the report ........................................................................ 101

Adding summary information to the report ........................................................ 103

Beyond basic reports ..................................................................... 103

Chapter 7: Designing Optimized Web Reports

Overview ...................................................................................... 106

Scale with Crystal Enterprise Reports ............................................ 107

Installing, configuring, and upgrading ............................................................... 107

Crystal Reports User’s Guide v

Making the right design choices .................................................... 108

Using faster report formats ................................................................................. 108

Choosing between live and saved data .............................................................. 109

Designing summary reports ............................................................................... 110

Using subreports carefully ................................................................................. 111

Streamlining your reporting environment ..................................... 112

Selecting the fastest database and connection ................................................... 112

Using table indexes ........................................................................................... 112

Improving table-linking choices ........................................................................ 113

Using thread-safe database drivers .................................................................... 114

Using stored procedures for faster processing .................................................... 114

Using enhanced record selection formulas ................................... 115

Pushing down record selection—an example .................................................... 115

Record selection performance tips ..................................................................... 116

Strategies for writing efficient record selection formulas .................................... 117

Incorporating parameter fields into record selection formulas ............................ 118

Using SQL expressions where appropriate ......................................................... 120

Improving grouping, sorting, and totaling ..................................... 121

Performing grouping on server .......................................................................... 121

Benefits of grouping on server—an example ...................................................... 121

Using SQL Expressions for groups, sorts, and totals ........................................... 122

Using SQL Expressions for Case Logic ............................................................... 123

Inserting summary and running total fields where possible ................................ 123

Chapter 8: Record Selection

Selecting records ........................................................................... 126

Using the Select Expert ...................................................................................... 127

Using formulas .................................................................................................. 128

Interaction of the Select Expert and the Selection Formula Editor ....................... 129

vi Crystal Reports User’s Guide

Using formula templates ............................................................... 130

Record selection formula templates ................................................................... 130

Choosing a formula template ............................................................................. 132

Pushing down record selection to the database server .................. 133

Troubleshooting record selection formulas .................................. 134

Correcting selections that do not generate data ................................................. 135

Chapter 9: Sorting, Grouping, and Totaling

Sorting data ................................................................................... 138

Understanding sort options ................................................................................ 138

Sorting single and multiple fields ....................................................................... 139

Grouping data ............................................................................... 140

Creating custom groups ..................................................................................... 142

Sorting records within groups ............................................................................ 144

Group selection ................................................................................................. 144

Grouping data in intervals ................................................................................. 148

Grouping by the first letter of a company name ................................................. 151

Grouping data hierarchically ............................................................................. 153

Editing groups ................................................................................................... 155

Summarizing grouped data ........................................................... 156

Ordering groups by summarized values ............................................................ 158

Selecting top or bottom N groups ...................................................................... 158

Subtotaling .................................................................................... 160

Subtotaling data ................................................................................................ 160

Extending prices and subtotaling the extensions ................................................ 162

Percentages ................................................................................... 164

Calculating a percentage .................................................................................. 164

Group headers .............................................................................. 165

Creating group headers ..................................................................................... 165

Crystal Reports User’s Guide vii

Suppressing group headers ................................................................................ 168

Drilling-down on group headers ........................................................................ 169

Chapter 10: Running Totals

Understanding running totals ........................................................ 172

How running totals work ................................................................................... 172

Creating running totals ................................................................. 173

Creating running totals in a list ......................................................................... 173

Creating running totals for a group .................................................................... 174

Creating conditional running totals ................................................................... 175

Creating running totals in a one-to-many linking relationship ............................ 177

Creating running totals using a formula ........................................ 178

Chapter 11: Multiple Section Reports

About sections .............................................................................. 182

Working with sections .................................................................. 182

Inserting a section ............................................................................................. 182

Deleting a section ............................................................................................. 183

Moving a section ............................................................................................... 183

Merging two related sections ............................................................................. 184

Splitting and resizing sections ....................................................... 185

Splitting a section .............................................................................................. 185

Resizing a section ............................................................................................. 186

Using multiple sections in reports ................................................. 187

Keeping variable length objects from overwriting each other ............................. 187

Formatting objects conditionally ....................................................................... 188

Alternating background colors for rows ............................................................. 189

Eliminating blank lines when fields are empty ................................................... 189

Adding blank lines conditionally ....................................................................... 191

viii Crystal Reports User’s Guide

Form letters ................................................................................... 191

Working with text objects .................................................................................. 191

Creating a form letter using a text object ........................................................... 193

Printing conditional messages in form letters ..................................................... 197

Chapter 12: Formatting

Formatting concepts ..................................................................... 200

Using the Report Design Environment .......................................... 200

Design solutions ................................................................................................ 200

Section characteristics ....................................................................................... 201

Making an object underlay a following section ................................................. 201

Pre-printed forms ............................................................................................... 203

Hiding report sections ....................................................................................... 203

Hiding report objects ......................................................................................... 204

Placing text-based objects ................................................................................. 206

Placing multi-line, text-based objects ................................................................ 208

Importing text-based objects from a file ............................................................. 209

Spacing between text-based objects .................................................................. 209

Overflow Field Representation .......................................................................... 216

Selecting multiple objects .................................................................................. 217

Free-form placement ......................................................................................... 217

Vertical placement ............................................................................................ 220

TrueType fonts .................................................................................................. 222

Page margins ..................................................................................................... 223

Default printer ................................................................................................... 223

Printer drivers .................................................................................................... 225

Formatting properties ................................................................... 226

Working with absolute formatting ................................................ 226

Adding borders, color, and shading to a field .................................................... 227

Crystal Reports User’s Guide ix

Changing your default field formats ................................................................... 227

Adding and editing lines ................................................................................... 229

Adding and editing boxes .................................................................................. 230

Adding shapes to a report .................................................................................. 231

Using conventional accounting formats ............................................................. 232

Using white space between rows ...................................................................... 235

Working with conditional formatting ............................................ 237

Conditional on or off properties ........................................................................ 237

Conditional attribute properties ......................................................................... 238

Changing conditional fonts ............................................................................... 240

Creating footers after the first page .................................................................... 241

Using the Highlighting Expert ............................................................................ 242

Undo/Redo activities ......................................................................................... 244

Chapter 13: Charting

Charting concepts ......................................................................... 248

Charting overview ............................................................................................. 248

Chart layouts ..................................................................................................... 248

Chart types ........................................................................................................ 249

Where to place a chart ...................................................................................... 251

Drill-down with charts ...................................................................................... 251

Drill-down with legends .................................................................................... 251

Creating charts .............................................................................. 252

Charting on details or formula fields (Advanced layout) ..................................... 252

Charting on summary or subtotal fields (Group layout) ...................................... 254

Charting on Cross-Tab summaries (Cross-Tab layout) ........................................ 256

Charting on an OLAP cube (OLAP layout) ......................................................... 257

Working with charts ..................................................................... 259

Editing charts using the Chart Expert .................................................................. 259

Editing charts on the Analyzer tab ..................................................................... 259

x Crystal Reports User’s Guide

Using the zooming features with bar and line charts ......................................... 260

Auto-arranging charts ........................................................................................ 261

Changing chart size and position ....................................................................... 261

Changing the border of a chart .......................................................................... 262

Using the underlay feature with charts .............................................................. 263

Chapter 14: Mapping

Mapping concepts ......................................................................... 266

Mapping overview ............................................................................................ 266

Map layouts ...................................................................................................... 266

Map types ......................................................................................................... 267

Where to place a map ....................................................................................... 269

Drill-down with maps ....................................................................................... 270

Creating maps ............................................................................... 270

Mapping on details fields (Advanced layout) ..................................................... 270

Mapping on group fields (Group layout) ............................................................ 274

Mapping on Cross-Tab summaries (Cross-Tab layout) ........................................ 275

Mapping on an OLAP cube (OLAP layout) ........................................................ 277

Mapping on subreports ...................................................................................... 278

Working with maps ....................................................................... 279

Editing maps using the Map Expert .................................................................... 279

Editing maps on the Analyzer tab ...................................................................... 279

Changing the map title ...................................................................................... 280

Changing the map type ..................................................................................... 280

Changing map layers ......................................................................................... 281

Resolving data mismatches ................................................................................ 282

Changing the geographic map ........................................................................... 283

Changing map size and position ........................................................................ 284

Changing the border of a map ........................................................................... 284

Using the underlay feature with maps ............................................................... 285

Crystal Reports User’s Guide xi

Zooming, panning, and centering maps with the Map Navigator ....................... 286

Hiding and showing the Map Navigator ............................................................ 288

Chapter 15: OLE

OLE overview ................................................................................ 290

OLE terminology ............................................................................................... 290

Types of OLE objects ......................................................................................... 291

General OLE considerations .............................................................................. 291

Inserting OLE objects into reports ................................................. 292

How OLE objects are represented in a report ............................... 293

Editing OLE objects in reports ....................................................... 293

In-place editing ................................................................................................. 293

Dynamic OLE menu commands ........................................................................ 293

OLE and the Insert Picture command ................................................................ 294

Working with static OLE objects ................................................... 294

Working with embedded vs. linked objects ................................... 295

Embedded objects ............................................................................................. 295

Linked objects ................................................................................................... 296

Chapter 16: Cross-Tab Objects

What is a Cross-Tab object? .......................................................... 300

Cross-Tab example ........................................................................ 301

Report of order data - no sorting/grouping ......................................................... 301

Report of order data - grouped by region ........................................................... 302

Report of order data - grouped by product ......................................................... 302

Report of order data - grouped by region and product ....................................... 303

Order data in a Cross-Tab object ....................................................................... 303

Creating a Cross-Tab report .......................................................... 304

Formatting Cross-Tabs .................................................................. 314

xii Crystal Reports User’s Guide

Changing width and height of Cross-Tab cells ................................................... 314

Formatting background color of entire rows/columns ........................................ 314

Formatting fields individually ............................................................................ 315

Formatting several fields at one time ................................................................. 315

Suppressing Cross-Tab data ............................................................................... 315

Printing Cross-Tabs that span multiple pages ..................................................... 316

Chapter 17: Creating and Updating OLAP Reports

OLAP reporting with Crystal Reports ............................................ 318

OLAP Grid Objects ........................................................................................... 318

Creating an OLAP report ................................................................................... 318

Updating an OLAP report .................................................................................. 325

Using the Informix MetaCube SQL Method for reporting ................................... 326

Chapter 18: Distributing and Viewing Reports

Distributing Reports ...................................................................... 338

Printing a report ................................................................................................ 338

Faxing a report .................................................................................................. 338

Exporting a report .............................................................................................. 339

Working with Web Folders ................................................................................ 346

Working with Enterprise folders ......................................................................... 346

Chapter 19: Report Alerts

About Report Alerts ..................................................................... 350

Working with Report Alerts .......................................................... 350

Creating Report Alerts ....................................................................................... 350

Editing Report Alerts .......................................................................................... 353

Deleting Report Alerts ....................................................................................... 353

Viewing Report Alerts ........................................................................................ 353

Referring to Report Alerts in formulas ................................................................ 354

Crystal Reports User’s Guide xiii

Chapter 20: XML

XML overview .............................................................................. 356

XML elements, tags, and structure ..................................................................... 357

XML Schema ..................................................................................................... 357

Document Type Definition ................................................................................ 357

Style sheets ....................................................................................................... 358

XML in Crystal Reports ................................................................. 358

Accessing XML data .......................................................................................... 359

Exporting to XML format .................................................................................... 363

Customizing XML report definitions .................................................................. 365

XML output specifications ................................................................................. 370

Chapter 21: Creating Reports from Excel and Access

Overview of the Crystal Reports Add-Ins ...................................... 374

About the Microsoft Excel Add-In ...................................................................... 374

About the Microsoft Access Add-In ................................................................... 374

Working with the Microsoft Excel Add-In ..................................... 375

Using the Crystal Report Wizard to create a report in Excel ............................... 375

Working with the Microsoft Access Add-In .................................. 377

Using the Crystal Report Wizard to create a report in Access ............................ 378

Chapter 22: Using Formulas

Formulas overview ........................................................................ 382

Typical uses for formulas .................................................................................. 384

Formula components and syntax ................................................... 385

Formula components ....................................................................................... 385

Formula syntax .................................................................................................. 386

Specifying formulas ....................................................................... 386

Working with the Formula Editor ....................................................................... 387