Crystal Reports® 2008 For Dummies®
Published by
Wiley Publishing, Inc.
111 River St.
Hoboken, NJ 07030-5774
www.wiley.com
Copyright © 2008 by Wiley Publishing, Inc., Indianapolis, Indiana
Published by Wiley Publishing, Inc., Indianapolis, Indiana
Published simultaneously in Canada
No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher for permission should be addressed to the Legal Department, Wiley Publishing, Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, (317) 572-3447, fax (317) 572-4355, or online at http://www.wiley.com/go/permissions.
Trademarks: Wiley, the Wiley Publishing logo, For Dummies, the Dummies Man logo, A Reference for the Rest of Us!, The Dummies Way, Dummies Daily, The Fun and Easy Way, Dummies.com, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates in the United States and other countries, and may not be used without written permission. Crystal Reports is a registered trademark of Business Objects, SA. All other trademarks are the property of their respective owners. Wiley Publishing, Inc., is not associated with any product or vendor mentioned in this book.
LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: The publisher and the author make no representations or warranties with respect to the accuracy or completeness of the contents of this work and specifically disclaim all warranties, including without limitation warranties of fitness for a particular purpose. No warranty may be created or extended by sales or promotional materials. The advice and strategies contained herein may not be suitable for every situation. This work is sold with the understanding that the publisher is not engaged in rendering legal, accounting, or other professional services. If professional assistance is required, the services of a competent professional person should be sought. Neither the publisher nor the author shall be liable for damages arising herefrom. The fact that an organization or Website is referred to in this work as a citation and/or a potential source of further information does not mean that the author or the publisher endorses the information the organization or Website may provide or recommendations it may make. Further, readers should be aware that Internet Websites listed in this work may have changed or disappeared between when this work was written and when it is read.
For general information on our other products and services, please contact our Customer Care Department within the U.S. at 800-762-2974, outside the U.S. at 317-572-3993, or fax 317-572-4002.
For technical support, please visit www.wiley.com/techsupport.
Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in electronic books.
Library of Congress Control Number: 2008927910
ISBN: 978-0-470-29077-4
Manufactured in the United States of America
10 9 8 7 6 5 4 3 2 1
Allen G. Taylor is a 30-year veteran of the computer industry and the author of over 20 books, including SQL All-in-One Desk Reference For Dummies, 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 microprocessor architecture locally at Portland State University. You can contact Allen at allen.taylor@ieee.org.
This book is dedicated to my daughter, Jenny Taylor Warren, who has given me much excellent advice, as well as two outstanding grandsons.
Many people have contributed to the quality and content of this book. I would particularly like to recognize my Acquisitions Editor, Kyle Looper, for his overall management of the project and for keeping me honest, and my Project Editor, Rebecca Senninger, for the day-to-day coordination of the project.
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.
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 and Editorial
Project Editor: Rebecca Senninger
Acquisitions Editor: Kyle Looper
Senior Copy Editor: Teresa Artman
Technical Editor: Howard Hammerman
Editorial Manager: Leah Cameron
Editorial Assistant: Amanda Foxworth
Senior Editorial Assistant: Cherie Case
Cartoons: Rich Tennant (www.the5thwave.com)
Composition Services
Project Coordinator: Lynsey Stanford
Layout and Graphics: Reuben W. Davis, Alissa D. Ellet, Melissa K. Jester, Christine Williams
Proofreaders: Susan Moritz, Toni Settle
Indexer: Infodex Indexing Services Inc.
Publishing and Editorial for Technology Dummies
Richard Swadley, Vice President and Executive Group Publisher
Andy Cummings, Vice President and Publisher
Mary Bednarek, Executive Acquisitions Director
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
Title
Introduction
About This Book
Conventions Used in This Book
What You’re Not to Read
Foolish Assumptions
How This Book Is Organized
Icons Used in This Book
Where to Go from Here
Part I : Reporting Basics
Chapter 1: Transforming Raw Data into Meaningful Information
Major Features of Crystal Reports 2008
The Lone Edition of Crystal Reports 2008
Viewing a Report
Chapter 2: Create a Simple Report Right Now!
First Things First: Finding the Sample Database
Starting Crystal Reports 2008
Creating a Report with the Blank Report Option
Printing a Report
Where to Go from Here
Chapter 3: Report Design Guidelines
Defining an Effective Report Design
Defining Your Audience
Defining the Report’s Purpose
Choosing Content for Your Report
Choosing the Report Appearance
Chapter 4: Starting Your Report
Using the 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 When Retrieving Data
Chapter 6: Sorting, Grouping, and Totaling Report Data
Sorting Report Data
Adding Sort Controls to a Report
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
Formatting with the Section Formatting Menu
Using Section Expert for Easy Section Formatting
Placing Groups Where You Want Them
Hiding Details with Summary and Drill-Down Reports
Generating Barcodes
Creating Mailing Labels
Saving Money on Postage with a ZIP Sort
Chapter 8: Enhancing Your Report’s Appearance
Absolute Formatting
Conditional Formatting Using the Format Editor
Creating Emphasis with Highlighting Expert
Adding Pictures to Your Report
Aligning Preprinted Forms
Adding Text from a File
Formatting Options
Special Fields Contain Report Metadata
Raising a Red Flag with Report Alerts
Using Report Templates to Save Time and Effort
Part III : Advanced Report Types and Features
Chapter 9: Displaying Your Top Ten (Or Top N) with Group Sort
Sorting Groups Based on Performance
Going with the Percentages
A Choice of Group Sorts
Troubleshooting Group Sort Problems
Chapter 10: Adding Formulas to Reports
Formula Overview and Syntax
Lessening the Workload with Functions
Creating a Custom Function in Formula Workshop
Changing and Deleting Formulas
Data Types
Variables in Formulas
Control Structures
Chapter 11: 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 12: Combining Report Elements with OLE
Overview of OLE
Static OLE Objects
Embedded OLE Objects
Linked OLE Objects
Embedding or Linking an Object Taken from a File
Integrating Shockwave Flash Objects into Your Reports
Chapter 13: Creating and Updating OLAP Reports
What’s OLAP, and Why Might You Need It?
OLAP Reporting with Crystal Reports
Updating an OLAP Report
Formatting Data in an OLAP Report
Changing Data Appearance in an OLAP Report
Chapter 14: Using Cross-Tab Reports to Mine Your Data
Creating a Cross-Tab Object to Summarize All Report Data
Summarizing the Contents of a Group with a Cross-Tab
Making Calculations within a Cross-Tab Row or Column
Enhancing the Appearance and Readability of a Cross-Tab Object
Chapter 15: Enhancing Reports with Charts
Using Chart Expert
Creating a Chart
Choosing the Best Chart Type for Your Data
Different Chart Layouts for Different Data Types
Seeing How Chart Placement Affects the Data It Represents
Troubleshooting Chart Problems
Chapter 16: Adding Geographic Detail with Maps
Choosing the Right Crystal Reports Map
Creating a Map Step by Step
Troubleshooting Map Problems
Chapter 17: Interactivity Features
Crystal Xcelsius Overview
Adding Xcelsius Capability to a Report
Enhancing a Report with the Tasteful Use of Flash
Part IV : Crystal Reports in the Enterprise
Chapter 18: Crystal Reports Server
Connecting to Data Sources
Formatting Reports with Crystal Reports Developer
Providing Platform Services
Useful Management Tools
Application Services
Web Services
Viewing and Interacting with Reports
Chapter 19: BusinessObjects Enterprise Repository
Adding Folders to Your Repository
Storing Your Valuables in BusinessObjects Enterprise Repository
Using Repository Objects in a Report
Modifying a Repository Object
Deciding Whether to Update Reports Automatically
Deleting Objects from the Repository
Chapter 20: Navigating with Report Parts
Understanding Report Parts Navigation
Using Report Parts to Navigate a Report
Part V : Publishing Your Reports
Chapter 21: Sending Your Reports Out into the World
Printing Your Report
Faxing a Report
Exporting a Report
Troubleshooting Output Problems
Chapter 22: Displaying Reports Online
Exporting to a Static HTML Page
Adding a Hyperlink to a Report
Distributing Reports via crystalreports.com
Integrating with salesforce.com
Publishing to crystalreports.com with Crystal Reports Desktop Publisher
Interactive Report Viewing with Crystal Reports Viewer
Chapter 23: SQL Expression Fields
Creating an SQL Statement
Modifying an SQL Statement
Part VI : The Part of Tens
Chapter 24: Ten Things to Do Before You Create a Report
Identify the Users
Interview the Users
Get Agreement on the Report’s Appearance
Arbitrate Conflicting Demands
Nail Down the Project Scope
Nail Down the Project 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
Decide Whether to Include Charts or Maps
Chapter 25: Ten Ways to Give Your Reports More Pizzazz
Use the Correct Fonts
Use Color Tastefully
Enclose Text in Boxes
Emphasize Boxes 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
Crystal Reports 2008 is the latest in a long and celebrated series of report writers for personal computers. Crystal Reports is by far the bestselling report writer package in the world, even though you might not know it by name. (It’s been bundled with many of the most popular applications without being acknowledged by name. For example, a version of Crystal Reports is currently bundled into Microsoft’s Visual Studio .NET.) However, Crystal Reports 2008 is available as a standalone product. And if you want to produce a high-quality report quickly, Crystal Reports is the top choice to do the job.
Crystal Reports 2008 For Dummies is a book that gets you using Crystal Reports quickly and effectively. This book 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 might 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 while you read about them.
Anyone who might be called upon to produce a report based on database data can profit from the information contained in this book. It’s also valuable for managers who might never produce a report but who oversee 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.
When an instruction in the book refers to a command path — for instance, “Choose File⇒Save” — that simply means to click your mouse button on File on the main menu, and then choose Save from 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. This is a monospaced font. Crystal Reports executes code that you enter as formulas or SQL statements.
You can certainly choose to read this book from cover to cover, working through the examples, although you don’t have to. Regardless of whether you read it all the way through in order, think of this book as a handy quick reference by your side when you want to perform a particular operation you haven’t used in a while (or ever).
An upcoming section of this introduction explains the icons you’ll normally run across, and there is a good reason for paying attention to each of them. However, consider yourself exempted from the requirement to read anything that appears by a Technical Stuff icon.
I’ve never met you, but I have to make a few assumptions about you and what you know. For example, 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 Microsoft Word, you already know almost all there is to know about navigating 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 that 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.
This book contains six major parts. Each part contains several chapters.
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.
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 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, illustrate points with charts and maps, and create stunning visuals with Xcelsius and Flash. With these tools, you can produce reports fit for the eyes of the organization’s CEO.
BusinesObjects 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 (LAN), or on the World Wide Web. BusinessObjects Enterprise is also the home of the Repository, which is a great place to store formulas or custom functions so they can be used again.
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. The Crystal Reports SQL Expression Fields 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 the Microsoft .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.
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.
.jpg)
Now you’re ready to start finding out about using Crystal Reports 2008 to create professional-quality reports based on data stored in your databases. After all, Crystal Reports 2008 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.
In this part . . .
There’s data in them thar databases, where it’s not doing anyone any good by itself. And your manager wants a coherent report, based on that data, on her desk by the close of business today. What should you do? Panic? Consider joining the Foreign Legion?
You don’t need to do anything that drastic. The chapters in this part tell you how to quickly crank out the report your boss so desperately needs. This part offers you just what you need to know to put the needed information on the boss’ 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.