Crystal Reports® 10 For Dummies®

 

by Allen G. Taylor

 

 

 

About the Author

Allen G. Taylor is a 30-year veteran of the computer industry and the author of over 20 books, including SQL For Dummies, Access 2003 Power Programming with VBA, Database Development For Dummies, and SQL Weekend Crash Course. He lectures nationally on databases, innovation, and entrepreneurship. He also teaches database development internationally through a leading online education provider and teaches digital circuit design locally at Portland State University. You can contact Allen at allen.taylor@ieee.org.

 

Dedication

This book is dedicated to my daughter, Valerie Joy Taylor, who is indeed a joy as well as being a psychologist and world traveler.

 

Author’s Acknowledgments

Many people have contributed to the quality and content of this book. I would particularly like to recognize Jaylene Crick of Business Objects for her helpfulness, my acquisitions editor, Terri Varveris, for her overall management of the project, and my editor, Nicole Sholly, for keeping me honest.

I also appreciate the continued support of my family and the interest of my friends and colleagues. It would not have been possible to complete a project of this magnitude without the support of those close to me.

 

Publisher’s Acknowledgments

We’re proud of this book; please send us your comments through our online registration form located at www.dummies.com/register/.

Some of the people who helped bring this book to market include the following:

Acquisitions, Editorial, and Media Development

Associate Project Editor: Nicole Sholly

Acquisitions Editor: Terri Varveris

Senior Copy Editor: Barry Childs-Helton

Technical Editor: Wiley-Dreamtech India Pvt Ltd

Editorial Manager: Kevin Kirschner

Media Development Manager: Laura VanWinkle

Media Development Supervisor: Richard Graves

Editorial Assistant: Amanda Foxworth

Cartoons: Rich Tennant, www.the5thwave.com

Composition

Project Coordinator: Courtney MacIntyre

Layout and Graphics: Andrea Dahl, Carrie Foster, Denny Hager, Joyce Haughey, Kristin McMullan, Lynsey Osborn, Heather Ryan

Proofreaders: Andy Hollandbeck, Carl William Pierce; TECHBOOKS Production Services

Indexer: TECHBOOKS Production Services

Publishing and Editorial for Technology Dummies

Richard Swadley, Vice President and Executive Group Publisher

Andy Cummings, Vice President and Publisher

Mary C. Corder, Editorial Director

Publishing for Consumer Dummies

Diane Graves Steele, Vice President and Publisher

Joyce Pepple, Acquisitions Director

Composition Services

Gerry Fahey, Vice President of Production Services

Debbie Stailey, Director of Composition Services

Contents

Title

Introduction

About This Book

Conventions Used in This Book

What You’re Not to Read

Foolish Assumptions

How This Book Is Organized

About the Web Site

Icons Used in This Book

Where to Go from Here

Part I : Reporting Basics

Chapter 1: Transforming Raw Data into Usable Information

Major Features of Crystal Reports 10

The Four Editions of Crystal Reports 10

Viewing a Report

Chapter 2: Create a Simple Report Right Now!

Starting Crystal Reports 10

Creating a Report with the Blank Report Option

Troubleshooting a Report That Doesn’t Look Quite Right

Printing a Report

Chapter 3: Report Design Guidelines

Audience

Purpose

Content

Interfacing the Report to a Database

What Should the Report Look Like?

Chapter 4: Reporting Overview

Creating a Report with Report Creation Wizard

Starting with a Blank Report

Connecting Your Report to Its Data Source

Part II : Moving Up to Professional Quality Reports

Chapter 5: Pulling Specific Data from a Database

Get Data Quickly with Select Expert

Using Formulas to Retrieve Data

Using Parameter Fields to Retrieve Data at Runtime

Troubleshooting Tips

Chapter 6: Sorting, Grouping, and Totaling Result Sets

Sorting Report Data

Grouping Related Items

Calculating Percentages

Drilling Down for Detail

Keeping Track of Things with Running Totals

Troubleshooting Sorting, Grouping, and Totaling Problems

Chapter 7: Mastering Report Sections

Changing the Size of a Section

Placing Groups Where You Want Them

Hiding Details with Summary and Drill-Down Reports

Creating Mailing Labels

Saving Money on Postage by Doing a Zip Sort

Chapter 8: Formatting Your Reports

Absolute Formatting and Conditional Formatting

Highlighting Expert Creates Emphasis

Adding Pictures to a Report

A Trick for Aligning Preprinted Forms

Adding Text from a File

Formatting Options

Special Fields Contain Report Metadata

Raising a Red Flag with Report Alerts

Report Templates Save Time and Effort

Part III : Advanced Report Types and Features

Chapter 9: Displaying Your Favorite Hit Parade with Group Sort

Sorting Groups Based on Performance

Going with the Percentages

A Choice of Group Sorts

Troubleshooting Group Sort Problems

Chapter 10: Making Correlations with Cross-Tab Reports

Creating a Cross-Tab Object to Summarize All Report Data

Summarizing the Contents of a Group with a Cross-Tab

Enhancing the Appearance and Readability of a Cross-Tab Object

Chapter 11: Adding Formulas to Reports

Formula Overview and Syntax

Lessening the Workload with Functions

Creating a Custom Function Using Formula Workshop

Changing and Deleting Formulas

Data Types

Variables in Formulas

Control Structures

Chapter 12: Creating Reports within a Report

Combining Unrelated Reports

Linking a Subreport to a Primary Report

On-Demand Subreports Boost Efficiency

Passing Data Between Reports

Troubleshooting Subreport Problems

Chapter 13: Combining Report Elements with OLE

Overview of OLE

Embedding or Linking a File as an OLE Object

Embedding or Linking an Object Taken from a File

Editing OLE Objects

Chapter 14: Creating and Updating OLAP Reports

What’s OLAP, and Why Might I Need It?

OLAP Reporting with Crystal Reports

Chapter 15: Enhancing Reports with Charts

Choosing the Best Chart Type for Your Data

Different Chart Layouts for Different Data Types

A Chart’s Placement Affects the Data It Can Represent

Using Chart Expert

Troubleshooting Chart Problems

Chapter 16: Adding Geographic Detail with Maps

Crystal Reports Maps

Creating a Map Step by Step

Troubleshooting Map Problems

Part IV : Crystal Reports in the Enterprise

Chapter 17: Crystal Enterprise Components

Understanding Business Views

The Three Muska-Tiers

The Business Tier

Crystal Enterprise Admin Launchpad

Crystal Management Console

Crystal Enterprise User Launchpad

Crystal Enterprise Web Desktop

Crystal Configuration Manager

Crystal Import Wizard

Crystal Publishing Wizard

Chapter 18: Crystal Repository

Storing Your Valuables in Crystal Repository

Using Repository Objects in a Report

Modifying a Repository Object

Updating Reports Automatically Using Connected Repository Objects

Deleting Objects from the Repository

Chapter 19: Maintaining Security

Restricting Access

Easing the Security Burden

Controlling Access to Specific Reports

Chapter 20: Navigating with Report Parts

Understanding Report Parts Navigation

Using Report Parts to Navigate

Chapter 21: Crystal Analysis 10

Digging Deeper into OLAP

Creating a Crystal Analysis Report

Part V : Publishing Your Reports

Chapter 22: Distributing and Viewing Reports

Printing Your Report

Faxing a Report

Exporting a Report

Troubleshooting Output Problems

Chapter 23: Displaying Reports Online

Exporting to a Static HTML Page

Adding a Hyperlink to a Report

Chapter 24: SQL Commands

Creating an SQL Statement

Adding an SQL Statement to a Repository

Modifying an SQL Statement

Part VI : The Part of Tens

Chapter 25: Ten Things to Do Before You Create a Report

Identify the Users

Interview the Users

Arbitrate Conflicting Demands

Nail Down the Project’s Scope

Nail Down the Project’s Schedule

Verify That the Necessary Data Is in the Database

Determine How the Report Will Be Viewed

Determine the Best Report Type for the Users’ Needs

Get Agreement on the Report’s Appearance

Decide Whether to Include Charts or Maps

Chapter 26: Ten Ways to Give Your Reports More Pizzaz

Use the Correct Fonts

Use Color Tastefully

Enclose Text in Boxes

Emphasize Objects with Drop Shadows

Produce a Consistent Appearance with Templates

Add an Image

Add a Chart

Add a Map

Combine Two Objects with an Underlay

Separate the Summary from the Details with Drill-Down

Introduction

C rystal Reports 10 is the latest in a long and celebrated series of report writers for personal computers. Crystal Reports is by far the best-selling report writer package in the world, even though you may have never heard of it. In the past, it has been bundled with many of the most popular applications without being acknowledged by name. (A version of Crystal Reports is currently bundled into Microsoft’s Visual Studio .NET, and Crystal Reports 10 is sold as a standalone product.) If you want to produce a high-quality report quickly, Crystal Reports is the top choice to do the job.

About This Book

Crystal Reports 10 For Dummies is an introductory level book that gets you using Crystal Reports quickly and effectively. It covers all the major capabilities of Crystal Reports but doesn’t bog you down in intricate detail. The objective is to give you the information you need to produce the types of reports that most people need most of the time. I also get into some out-of-the-ordinary report types that you might be called upon to generate on occasion.

Use this book as a handy reference guide. Each chapter deals with an individual feature that you may need at one time or another. Pull out the book, read the chapter, and then do what you need to do. In many cases, step-by-step procedures walk you through commonly needed operations. You might find it worthwhile to put the book beside your computer and perform the operations as you read about them.

Anyone who may be called upon to produce a report based on database data can profit from the information contained in this book. It’s also valuable to managers who may never personally produce a report, but have oversight of people who do. This book tells you what’s possible, what you can do easily, and what takes a little more effort to accomplish. This knowledge can help you estimate how long it should take to produce reports of various types.

Conventions Used in This Book

When an instruction in the book says, for instance, File⇒Save, it means to click the left mouse button on File in the main menu, and then click Save on the submenu that drops down from it.

Anything you see that is printed in a monospaced font is code, or something you’ll run across in the course of programming a database (field names, for example). This is a monospaced font. Crystal Reports executes code that you enter as formulas or SQL statements.

What You’re Not to Read

You can read the book through from cover to cover, working through the examples, although you don’t have to. Whether you read it all the way through or not, you can use it as a quick reference when you want to perform a particular operation that you have not used in a while.

There’s another whole section of this introduction that explains the icons you’ll normally run across, and there is a good reason for paying attention to each of them. There is, however, one icon that you get to skip: Consider yourself exempted from the requirement to read anything that appears by a Technical Stuff icon.

TechnicalStuff

Material next to one of these icons may be interesting to techies like me (there must be some of you out there) but generally is not necessary for a full understanding of how to use Crystal Reports.

Foolish Assumptions

I’ve never met you, but because we’re going to be together for a while, I’ll make a few assumptions about you and what you know. I assume that you know how to use a personal computer and that you’re somewhat familiar with Microsoft Word. If you know how to navigate around Microsoft Word, you already know almost all there is to know about navigating around Crystal Reports. The user interfaces of the two products are similar.

I assume that you’ve seen directory trees before, such as those extensively used in Microsoft Windows. You know that if you see a plus sign (+) to the left of a node that shows a folder (or other) icon, it means you can click the plus sign to expand that node, to see what the node contains. Crystal Reports treats directory trees in a similar way.

I assume you know how to perform a drag-and-drop operation with your mouse. In Crystal Reports, when you click an object and start dragging it, your progress is shown by a rectangular placement frame. When you release the mouse button to drop the item, the placement frame is replaced by a duplicate of the item that you dragged.

How This Book Is Organized

This book contains six major parts. Each part contains several chapters.

Part I: Reporting Basics

Part I introduces you to Crystal Reports and the art of report creation. You find out what a report should accomplish and what it should look like. Then you fire up Crystal Reports and use it to create a simple report based on data held in a database.

Part II: Moving Up to Professional Quality Reports

You can do many things beyond the basics to make reports more focused, more readable, and easier on the eye. This part gives you the information you need to do all those things.

Part III: Advanced Report Types and Features

Part III gets into serious report creation. With the information in this part, you can zero in on exactly the data you want and display it in the most understandable way. You’ll be able to nest one report within another, pull report elements from multiple non-database sources, present multidimensional data in OLAP cubes, and illustrate points with charts and maps. With these tools, you can produce reports fit for the eyes of the organization’s CEO.

Part IV: Crystal Reports in the Enterprise

Crystal Enterprise is a companion product to Crystal Reports that controls and secures the distribution of reports. With it you can make your reports accessible to people on your local area network, or on the World Wide Web. Crystal Enterprise’s new Business Views capability enables report developers to custom tailor a report based on the interests of the people who will be viewing it. There can be multiple different Business Views of a single report. Crystal Enterprise is also the home of the Crystal Repository, which is a great place to store formulas, custom functions, or Business Views, so they can be used again later.

Part V: Publishing Your Reports

After you create a report, you’ll want to make it available to the people who need it. Crystal Reports makes it easy for you to distribute your report for viewing, whether to colleagues in your organization or to Internet users around the world. In addition, you can publish your reports using traditional methods. You can print it; export it to a file, or fax it to people far away. After you complete report development, distribution is easy.

Although Crystal Reports does a great job when used all by itself, you can also incorporate it into applications written in a computer language. Crystal Reports’ SQL Commands facility gives you direct control over the data in a report’s underlying database. Because a version of Crystal Reports is included as an integral part of Microsoft’s .NET application development environment, you can incorporate the power of Crystal Reports into applications you write in Visual Basic, Visual C++, Visual C#, or any language compatible with the .NET framework. This gives the applications you write the sophistication of the world’s leading report writer.

Part VI: The Part of Tens

It’s always good to remember short lists of best practices. That’s what the Part of Tens is all about. Listed here are pointers that help you produce outstanding reports with minimum effort, in the shortest possible time.

About the Web Site

This book has an accompanying Web site where you can find sample reports from the book, some exercises related to the sample reports, and links to sites for related information. To access the Web site, go to www.dummies.com/go/crystalfd.

Icons Used in This Book

Tip

Tips save you a lot of time and keep you out of trouble.

Remember

Pay attention to the information marked by this icon — you may need it later.

TechnicalStuff

As mentioned earlier, Technical Stuff is detail that I find interesting and you may also. But if you don’t, no big deal. It is not essential to gain an understanding of the topic being discussed. Skip it if you like.

Warning(bomb)

Heeding the advice that this icon points to can save you from major grief. Ignore it at your peril.

Where to Go from Here

Now you’re ready to start finding out about creating professional quality reports based on data stored in your databases, using Crystal Reports 10. Crystal Reports 10 is the latest version of the most popular report writer in the world. You can use it to quickly whip out simple reports, or you can take a little longer and generate a world-class executive report.

Part I

Reporting Basics

In this part . . .

T here’s data in the database, where it’s not doing anyone any good. Your manager wants coherent information, based on that data, on her desk by the close of business today. What should you do? Panic? Consider joining the Foreign Legion?

There’s no need to do anything drastic. The chapters in this part quickly tell you how to crank out the report your boss so desperately needs. It won’t have all the bells and whistles that you find out about in other parts of this book, but it puts the needed information on the boss’s desk before the lights go out tonight. And you’ll start to build your reputation as a person who can deliver the goods when the pressure is on.