by Allen G.Taylor
Discover how Crystal Reports 10 can make your data come to Life!
Create Interactive Reports that look good and make sense
 |
| Crystal Reports 10 For Dummies |
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.
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.
Introduction
Crystal 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.
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.
Screenshot
Purchase Now !
Just with Paypal
Just with Paypal
Product details
Price
|
|
|---|---|
File Size
| 10,213 KB |
Pages
|
385 p |
File Type
|
PDF format |
ISBN
| 0-7645-7137-0 |
Copyright
| 2004 by Wiley Publishing, Inc |
Contents at a Glance
Introduction
Part I: Reporting Basics
Chapter 1: Transforming Raw Data into Usable Information
Chapter 2: Create a Simple Report Right Now!
Chapter 3: Report Design Guidelines
Chapter 4: Reporting Overview
Part II: Moving Up to Professional Quality Reports
Chapter 5: Pulling Specific Data from a Database
Chapter 6: Sorting, Grouping, and Totaling Result Sets
Chapter 7: Mastering Report Sections
Chapter 8: Formatting Your Reports
Part III: Advanced Report Types and Features
Chapter 9: Displaying Your Favorite Hit Parade with Group Sort
Chapter 10: Making Correlations with Cross-Tab Reports
Chapter 11: Adding Formulas to Reports
Chapter 12: Creating Reports within a Report
Chapter 13: Combining Report Elements with OLE
Chapter 14: Creating and Updating OLAP Reports
Chapter 15: Enhancing Reports with Charts
Chapter 16: Adding Geographic Detail with Maps
Part IV: Crystal Reports in the Enterprise
Chapter 17: Crystal Enterprise Components
Chapter 18: Crystal Repository
Chapter 19: Maintaining Security
Chapter 20: Navigating with Report Parts
Chapter 21: Crystal Analysis 10
Part V: Publishing Your Reports
Chapter 22: Distributing and Viewing Reports
Chapter 23: Displaying Reports Online
Chapter 24: SQL Commands
Part VI: The Part of Tens
Chapter 25: Ten Things to Do Before You Create a Report
Chapter 26: Ten Ways to Give Your Reports More Pizzaz
Index
Table of Contents
Introduction...........1
About This Book........1
Conventions Used in This Book ............2
What You’re Not to Read.............2
Foolish Assumptions .................2
How This Book Is Organized..............3
Part I: Reporting Basics .......3
Part II: Moving Up to Professional Quality Reports ..........3
Part III: Advanced Report Types and Features...........3
Part IV: Crystal Reports in the Enterprise ............4
Part V: Publishing Your Reports.............4
Part VI: The Part of Tens ..........4
About the Web Site.......5
Icons Used in This Book.................5
Where to Go from Here...........5
Part I: Reporting Basics.................................................7
Chapter 1: Transforming Raw Data into Usable Information . . . . . . . . .9
Major Features of Crystal Reports 10..........................................................10
Formatting a report..............................................................................10
Enhancing a report with formulas and custom functions...............11
Getting visual with charts and maps .................................................11
Displaying a report...............................................................................11
Distributing a report ............................................................................12
Supplying Crystal Reports with data .................................................12
The Four Editions of Crystal Reports 10.....................................................12
The Standard Edition ...........................................................................13
The Professional Edition .....................................................................13
The Developer Edition.........................................................................13
The Advanced Edition .........................................................................14
Viewing a Report ............................................................................................14
Reading a printed report .....................................................................14
Viewing a report with Crystal Reports ..............................................14
Viewing a report on your local area network or the Web ...............16
Chapter 2: Create a Simple Report Right Now! . . . . . . . . . . . . . . . . . . .17
Starting Crystal Reports 10...........................................................................18
Creating a Report with the Blank Report Option.......................................19
Allocating more space to the layout ..................................................22
Giving the report a title .......................................................................23
Choosing the fields that will appear in your report.........................25
Improving the readability of page headers .......................................26
Previewing the report ..........................................................................27
Page footers carry useful information...............................................29
Wrap things up with a report footer ..................................................30
Recording helpful information about your report ...........................31
Troubleshooting a Report That Doesn’t Look Quite Right.......................32
Printing a Report ............................................................................................32
Chapter 3: Report Design Guidelines . . . . . . . . . . . . . . . . . . . . . . . . . . . .33
Audience..........................................................................................................33
Purpose ...........................................................................................................34
Content............................................................................................................35
Interfacing the Report to a Database...........................................................35
Connecting to Microsoft Access.........................................................36
Connecting to other data sources......................................................37
What Should the Report Look Like? ............................................................38
Making a good first impression ..........................................................39
Deciding how best to present the information.................................39
Should the report include graphs, charts, or pictures? ..................40
Style communicates meaning, too .....................................................40
Does the report convey the message?...............................................41
Chapter 4: Reporting Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .43
Creating a Report with Report Creation Wizard ........................................43
Creating a standard report..................................................................44
The rest of the Report Creation wizards ...........................................49
Starting with a Blank Report.........................................................................49
Connecting Your Report to Its Data Source................................................50
Accessing database files directly .......................................................51
Linking to ODBC data sources ............................................................51
Retrieving data from Crystal SQL Designer files ..............................52
Reporting on data in OLE DB data sources.......................................53
Creating customized data access with Crystal Dictionaries ..........53
Which interface should you use? .......................................................53
Part II: Moving Up to Professional Quality Reports ........55
Chapter 5: Pulling Specific Data from a Database . . . . . . . . . . . . . . . .57
Get Data Quickly with Select Expert............................................................57
Using Formulas to Retrieve Data..................................................................63
Using Parameter Fields to Retrieve Data at Runtime ................................65
Troubleshooting Tips ....................................................................................71
Chapter 6: Sorting, Grouping, and Totaling Result Sets . . . . . . . . . . . .73
Sorting Report Data .......................................................................................73
Sorting based on multiple fields.........................................................75
Sorting and performance.....................................................................78
Grouping Related Items.................................................................................79
Calculating Percentages ................................................................................82
Drilling Down for Detail .................................................................................84
Keeping Track of Things with Running Totals............................................86
Troubleshooting Sorting, Grouping, and Totaling Problems....................90
Sorting problems and how to solve them .........................................90
Unusual grouping options ...................................................................91
Getting the right totals ........................................................................92
Chapter 7: Mastering Report Sections . . . . . . . . . . . . . . . . . . . . . . . . . . .93
Changing the Size of a Section......................................................................93
Vertical spacing between lines ...........................................................93
The Section Formatting menu ............................................................96
Common tab of Section Expert...........................................................98
Color tab of Section Expert ...............................................................100
Placing Groups Where You Want Them.....................................................103
Starting each group at the top of its own page ..............................103
Printing totals at the bottom of a page............................................104
Restarting page numbering at the beginning of each group.........105
Hiding Details with Summary and Drill-Down Reports ...........................105
Creating Mailing Labels ...............................................................................106
Saving Money on Postage by Doing a Zip Sort .........................................109
Chapter 8: Formatting Your Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . .111
Absolute Formatting and Conditional Formatting ...................................111
Absolute formatting overview ..........................................................112
Conditional formatting ......................................................................116
Highlighting Expert Creates Emphasis......................................................118
Adding Pictures to a Report .......................................................................119
A Trick for Aligning Preprinted Forms ......................................................120
Adding Text from a File ...............................................................................121
Formatting Options......................................................................................121
Special Fields Contain Report Metadata ...................................................123
Raising a Red Flag with Report Alerts .......................................................123
Report Templates Save Time and Effort ...................................................127
What’s a template? .............................................................................127
How do you use a template in a report?..........................................127
Applying a template to an existing report.......................................128
Applying a template to a report you’re creating ............................129
Part III: Advanced Report Types and Features .............131
Chapter 9: Displaying Your Favorite Hit Parade with Group Sort . . .133
Sorting Groups Based on Performance .....................................................133
Going with the Percentages ........................................................................142
What if you want the top seventeen instead of the top five? .......144
A Choice of Group Sorts..............................................................................146
Troubleshooting Group Sort Problems .....................................................147
Chapter 10: Making Correlations with Cross-Tab Reports . . . . . . . .149
Creating a Cross-Tab Object to Summarize All Report Data ..................149
Summarizing the Contents of a Group with a Cross-Tab ........................153
Enhancing the Appearance and Readability of a Cross-Tab Object ......157
Changing the width and height of cross-tab cells ..........................158
Formatting entire rows and columns...............................................158
Formatting individual fields ..............................................................159
Suppressing selected cross-tab data ...............................................159
Printing cross-tabs that span multiple pages .................................160
Chapter 11: Adding Formulas to Reports . . . . . . . . . . . . . . . . . . . . . . . .161
Formula Overview and Syntax ...................................................................161
Lessening the Workload with Functions ...................................................162
Creating a Custom Function Using Formula Workshop ..........................162
Formula Editor....................................................................................165
Formula Expert ...................................................................................166
SQL Expression Editor .......................................................................168
Selection formulas..............................................................................169
Formatting formulas...........................................................................171
Changing and Deleting Formulas ...............................................................172
Data Types ....................................................................................................173
Simple data types ...............................................................................173
Range data types ................................................................................175
Array data types .................................................................................175
Variables in Formulas ..................................................................................176
Declaring a variable ...........................................................................176
Assigning a value to a variable .........................................................176
Control Structures........................................................................................177
If-Then-Else..........................................................................................177
Select Case ..........................................................................................178
For loop................................................................................................178
While Do loop .....................................................................................179
Do While loop .....................................................................................180
Chapter 12: Creating Reports within a Report . . . . . . . . . . . . . . . . . . .181
Combining Unrelated Reports ....................................................................181
Underlay formatting for side-by-side location of subreport.........185
Drilling down in a subreport .............................................................186
Linking a Subreport to a Primary Report..................................................187
On-Demand Subreports Boost Efficiency..................................................191
Passing Data Between Reports...................................................................192
Troubleshooting Subreport Problems.......................................................193
Chapter 13: Combining Report Elements with OLE . . . . . . . . . . . . . . .195
Overview of OLE...........................................................................................195
Static OLE objects ..............................................................................196
Embedded objects and linked objects ............................................196
Embedding or Linking a File as an OLE Object ........................................197
Embedding or Linking an Object Taken from a File.................................199
Editing OLE Objects.....................................................................................199
Chapter 14: Creating and Updating OLAP Reports . . . . . . . . . . . . . . . .201
What’s OLAP, and Why Might I Need It?....................................................201
Who uses OLAP?.................................................................................201
Creating multidimensional views .....................................................202
OLAP Reporting with Crystal Reports ......................................................203
Creating a three-dimensional report................................................203
Updating an OLAP report ..................................................................209
Chapter 15: Enhancing Reports with Charts . . . . . . . . . . . . . . . . . . . . .211
Choosing the Best Chart Type for Your Data ...........................................211
Side-by-side bar chart ........................................................................211
Stacked bar chart ...............................................................................213
Line chart ............................................................................................213
Area chart............................................................................................213
Pie chart ..............................................................................................214
Doughnut chart...................................................................................215
3-D riser chart .....................................................................................216
3-D surface chart ................................................................................216
XY scatter chart..................................................................................216
Radar chart .........................................................................................217
Bubble chart .......................................................................................218
Stock chart ..........................................................................................218
Numeric axis chart .............................................................................219
Gauge chart .........................................................................................221
Gantt chart ..........................................................................................221
Funnel chart ........................................................................................222
Different Chart Layouts for Different Data Types ....................................223
A Chart’s Placement Affects the Data It Can Represent..........................224
Using Chart Expert.......................................................................................225
Whirlwind tour of the Chart Expert dialog box..............................225
Creating a chart ..................................................................................229
Drilling down from a chart ................................................................231
Changing a chart.................................................................................232
Troubleshooting Chart Problems ..............................................................232
Selecting data to make a chart readable and meaningful .............232
The placement of chart elements matters......................................233
Chapter 16: Adding Geographic Detail with Maps . . . . . . . . . . . . . . .235
Crystal Reports Maps..................................................................................235
Map layouts.........................................................................................236
Map types............................................................................................236
Map placement ...................................................................................238
Creating a Map Step by Step.......................................................................238
Creating an advanced layout map....................................................238
Creating a Group layout map............................................................240
Creating a Cross-Tab layout map .....................................................241
Creating an OLAP layout map...........................................................242
Including maps in subreports...........................................................243
Changing maps ...................................................................................243
Troubleshooting Map Problems.................................................................243
Part IV: Crystal Reports in the Enterprise ....................245
Chapter 17: Crystal Enterprise Components . . . . . . . . . . . . . . . . . . . . .247
Understanding Business Views ..................................................................247
The Three Muska-Tiers................................................................................248
The Business Tier.........................................................................................248
Data Connection .................................................................................248
Dynamic Data Connection.................................................................251
Data Foundation .................................................................................253
Additional objects ..............................................................................257
Crystal Enterprise Admin Launchpad .......................................................260
Crystal Management Console.....................................................................262
Crystal Enterprise User Launchpad ..........................................................263
Crystal Enterprise Web Desktop ................................................................263
Crystal Configuration Manager ..................................................................264
Crystal Import Wizard .................................................................................265
Crystal Publishing Wizard...........................................................................265
Chapter 18: Crystal Repository . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .269
Storing Your Valuables in Crystal Repository ..........................................270
Adding folders to your Repository ..................................................270
Adding text and bitmapped objects to the Repository.................270
Adding custom functions to the Repository...................................273
Adding SQL commands to the Repository......................................273
Using Repository Objects in a Report .......................................................273
Modifying a Repository Object ..................................................................274
Updating Reports Automatically Using Connected
Repository Objects ..................................................................................275
Deleting Objects from the Repository.......................................................277
Chapter 19: Maintaining Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .279
Restricting Access........................................................................................279
Logon tokens.......................................................................................280
Sessions and session tracking ..........................................................280
Primary authentication......................................................................281
Secondary authentication and authorization.................................281
Protecting the environment ..............................................................282
Web activity auditing .........................................................................282
Combating malicious logon attempts..............................................282
Easing the Security Burden.........................................................................283
Single Sign-On .....................................................................................283
Active trust relationship....................................................................283
Controlling Access to Specific Reports .....................................................283
Creating groups to control access ...................................................283
Granting rights to control access.....................................................284
Chapter 20: Navigating with Report Parts . . . . . . . . . . . . . . . . . . . . . . .285
Understanding Report Parts Navigation ...................................................285
Using Report Parts to Navigate ..................................................................287
Using the Report Parts Drill-down method.....................................287
Using the Another Report Object method ......................................293
Chapter 21: Crystal Analysis 10 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .295
Digging Deeper into OLAP...........................................................................295
Creating a Crystal Analysis Report............................................................296
Using a blank application ..................................................................296
Using the Application Experts ..........................................................298
Part V: Publishing Your Reports .................................305
Chapter 22: Distributing and Viewing Reports . . . . . . . . . . . . . . . . . . .307
Printing Your Report....................................................................................307
Faxing a Report.............................................................................................308
Exporting a Report.......................................................................................309
Troubleshooting Output Problems............................................................310
Chapter 23: Displaying Reports Online . . . . . . . . . . . . . . . . . . . . . . . . .311
Exporting to a Static HTML Page ...............................................................311
Adding a Hyperlink to a Report..................................................................314
Chapter 24: SQL Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .317
Creating an SQL Statement .........................................................................318
Adding an SQL Statement to a Repository................................................321
Modifying an SQL Statement.......................................................................322
Part VI: The Part of Tens ............................................325
Chapter 25: Ten Things to Do Before You Create a Report . . . . . . . . .327
Identify the Users .........................................................................................327
Interview the Users......................................................................................328
Arbitrate Conflicting Demands...................................................................328
Nail Down the Project’s Scope....................................................................328
Nail Down the Project’s Schedule ..............................................................328
Verify That the Necessary Data Is in the Database..................................329
Determine How the Report Will Be Viewed ..............................................329
Determine the Best Report Type for the Users’ Needs ...........................329
Get Agreement on the Report’s Appearance............................................330
Decide Whether to Include Charts or Maps .............................................330
Chapter 26: Ten Ways to Give Your Reports More Pizzaz . . . . . . . . . .331
Use the Correct Fonts..................................................................................331
Use Color Tastefully.....................................................................................332
Enclose Text in Boxes..................................................................................332
Emphasize Objects with Drop Shadows....................................................332
Produce a Consistent Appearance with Templates.................................333
Add an Image ................................................................................................333
Add a Chart ...................................................................................................333
Add a Map .....................................................................................................333
Combine Two Objects with an Underlay...................................................334
Separate the Summary from the Details with Drill-Down .......................334
Index........................................................................335
●▬▬▬▬▬❂❂❂▬▬▬▬▬●
●▬▬❂❂▬▬●
●▬❂▬●
●❂●
═════● ●═════
