Crystal Reports Encyclopedia Volume 1: Professional XI Reports

Make your reporting projects a success with this comprehensive, tutorial

based guide. The Crystal Reports Encyclopedia gives you over 600 pages
of thorough report design coverage. Whether you're just learning Crystal
Reports, or you are a seasoned expert, there is something here for you.

New users will quickly get up to speed on reporting fundamentals. Each

chapter starts out teaching you the introductory concepts. New features are
added in stages to make learning easy. Step-by-step tutorials throughout
the chapters let you practice creating specific reports that you can put to
use immediately. Grow beyond the basics and learn to use sub-reports,
cross-tab objects, and dynamic cascading prompts. A Crystal/Basic syntax
reference guide will have you quickly writing custom formulas.

 Advanced tutorials at the end of a chapter show you the tricks that professional report writers use. See how
cross-tab reports are generated internally and learn how to override cross-tab data with custom functions. Use
the Content Locale function to create international reports. Maximize SQL performance with advanced
queries for joining tables. Find out when views are faster than stored procedures, and learn how to update the
database as your report prints!

Chapter 1 - Introducing Crystal Reports

Learn what's new in Crystal Reports XI and the new Crystal Reports R2 release. The tutorial teaches you how
to create your first report and understand the Report Expert dialog boxes. The Two-Pass Report Processing
Model is explained in full detail so you understand the internal workings of Crystal Reports.

What's New in Crystal Reports XI

Crystal Reports has exciting new features in it over Crystal Reports 10. In fact, until XI was released, many of you
reading this book had previously been using version 8.5 and never upgraded because the changes for version 9 and 10
weren't significant enough. But with the new features in this latest release, many people have upgraded to XI and are
looking forward to the enhancements. The following sections give you an overview of the new features and list which
chapters have more information about them.

Dynamic and Cascading Prompts

If you are someone who has been using Crystal Reports 8.5 and are just now upgrading to XI, I wouldn't be surprised to
find out that Dynamic and Cascading Prompts are what sold you on the upgrade. Dynamic and Cascading Prompts
(DCPs) are a milestone upgrade over the static prompts of previous versions.

DCPs give you two benefits. The first benefit is that the list of values is linked directly to the data source.
When the underlying data changes, the list of values is updated to reflect the latest information. The second
benefit is that the prompts are linked together. The value(s) chosen in one prompt effect the list of values in
the following prompt. The user only sees a list of values directly related to their previous choices.

DCPs improve the usability of parameters by insuring that the data is always current and that the user only
sees choices relevant to what they are doing. Chapter 4 covers DCPs in the advanced section.

Dynamic Graphic Location

If you wanted to include dynamic graphics in previous version of Crystal Reports, you either had to embed the images
into binary fields within your database or write an application that programmatically loaded the images into memory
and placed them on the report. There was no easy way to display images stored on the hard drive of your computer on
a report. XI makes dynamic graphic locations possible. By referencing an image via a file path or URL, reports can load
new images on the fly and display them on the report. Dynamic images are covered in Chapter 8.

New Export Features

Exporting report output is a very common task. So common, that you often find yourself exporting a report in the
same way each time. Rather than require you to set the same options each time, Crystal Reports now lets you save
default values for the export options. Setting default values makes it faster to export reports and you don't have to
remember which options are best.

A new RTF export format lets you export reports so that it is easier to edit the final output. This is ideal for
reports that will be used for data entry forms. You could export to RTF prior to XI, but the format was
optimized for precise formatting. Editing the RTF wasn't very practical. You now have a choice between
exporting that is optimized for editing or optimized for precise formatting. Exporting is covered in Chapter 14.

Sorting
The sorting of groups and Top N/Bottom N reports now have a Formula Workshop button associated with them.
Conditional formatting formulas can be used to change the sort order. By integrating this with parameters, the user
gains more control over their report output. Sorting and grouping is in Chapter 3.

Hierarchical Grouping Improvements

Hierarchical reports used to be very limited in how the final output looked. Each column was formatted using an
indented tree format and sometimes the report wasn't easy to read. XI gives you an improved set of formatting
functionality that increases how much control you have over a report's final output. You control the indentation of
report objects and the grouping sections. This is covered in Chapter 3.

HTML Preview
A new tab in the report designer lets you view reports in their HTML format. This gives you immediate feedback about
how accurately your reports will appear when viewed as a web page. HTML preview is only available when using
Business Objects Enterprise or the Report Application Server. These versions of Crystal Reports are not covered in the
book.

Dependency Checker
The Dependency Checker verifies error checks a report and lists all problems found. This consists of checking the
syntax of formulas, validating hyperlinks, validating database fields, and verifying subreports. This is covered in
Chapter 5.

Workbench
The Workbench makes it easy to organize multiple reports into folders. By organizing reports into specific categories
you can easily find the reports you need and manage them. You can also use tools such as the Dependency Checker
across all the reports in a folder. The Workbench is covered later in this chapter.
Application Designer Improvements
Programmers have many new improvements to look forward to after installing XI. Programmers now have more
control over their reports, better integration of Java functions, client-side web printing, and improved report design.
Although this book doesn't include programming specific topics, this is covered in extensive detail in Volume 2 of this
series, �.NET 2005�.1

Note

If you are using Crystal Reports for .NET 2005, you need to install
Crystal Reports XI Release 2. Crystal Reports XI was released
almost a year prior to .NET 2005 being released and they are not
compatible with each other. Thus, there is a new version of Crystal
Reports XI that is compatible with .NET 2005. You need to
download and install XI Release 2 from the Business Objects Dev
Zone website.

Tutorial 1-1. Printing Your First Report

This lesson walks you through the steps of creating a new report. You will use the Report Creation Wizard to
make this as easy as possible. When you are finished, you will have created and printed out a simple report
that shows employee names.

1. Open Crystal Reports and make sure you are on the Start Page.
2. Click on the Standard Report Wizard link.
3. This is the first option under the New Reports column in the middle of the page. After clicking on this
link, the Standard Report Creation Wizard opens. This wizard walks you through the steps of creating
a new report. The first step is to select the data source.
4. Double-click on the Create New Connection folder to expand it.
5. Double-click on the Access/Excel (DAO) selection. This opens the Access/Excel (DAO) dialog box
that lets you find the file to use as a data source.
6. Click on the button to the right of the Database Name textbox and navigate to the Xtreme.mdb
database. It is located at:

C:\Program Files\Business Objects\Crystal Reports 11\Samples\en\Databases\xtreme.mdb.

7. Click the Ok button to select this database.

8. Click the Finish button to close the dialog box.
9. Expand the Tables selection to see the tables in the database.
10. Click on the Employee table name to select it.
11. Click on the Right arrow to move the Employee table to the Selected Tables list.
12. Click on the Employee Addresses table name to select it.
13. Click on the Right arrow to move the Employee Addresses table to the Selected Tables list.

14. Click the Next button at the bottom to save the tables. The Link dialog appears showing you the
field(s) the two tables are joined by. In this example the Employee Id field links the Employee table to
the Employee Address table. You'll learn more about linking tables in Chapter 11 on databases.
15. Click the Next button to accept the default link. The Fields dialog appears showing all the fields from
each table. Moving fields from the Available Fields list to the Fields to Display list on the right adds
the fields to the report.
16. Click on the following fields and then click on the Right arrow button to move them to the Fields to
Display list: Employee Id, Last Name, First Name, City, and Region.

17. Click the Next button to save these fields. The Grouping dialog appears showing the fields that were
selected in the previous step. We will look at grouping and sorting records in Chapter 3. Click the Next
button for now. The Record Selection dialog determines which records from the database get
displayed. We will cover this in more detail later in the book and not make any changes for this
tutorial.
18. Click the Next button to move forward.

The Template dialog appears so you can choose from a list of pre-determined report formats to make a
report look good. Crystal Reports comes with over a dozen different styles to choose from. As you
click on each template, the preview area to the right shows an example of what the report would look
like.
19. You can select one of the templates to automatically format your report to look a certain way. In this
example click on the No Template option to keep things simple. You can go back later and play with
the other templates. Crystal Reports creates the report and automatically previews it for you.

Overall, the report doesn't look too bad. One thing you might notice right away is that the Business
Objects logo is displayed in the report header. Of course, in your own report you will customize the
report header to have you own corporate logo, but we'll discuss how to do that later. Another thing to
notice is that the report title is simply called Report1. This isn't a very descriptive name and you
definitely need to change it.

20. On the application menu, select File | Summary Info. This opens the Document Properties dialog.
21. Enter Employee List as the title.
 22. Click the Ok button to update the report tab.
23. On the application menu select File | Print to print the report. The report will print out on your default
printer.
24. Save report as

Employee List.rpt.

25. If you want to make more changes to the report's design, click on the Design tab above the report. This
shows each section of the report and the report objects in each section. The details of modifying the
report design are covered throughout the book.

Chapter 2 - Learning the Report Designer

See how to set the designer's properties and how to setup the report's basic structure. Secondly, you learn how
to add and manipulate report objects within the designer. The tutorials give you practice creating and
modifying the many report objects.
Setting Designer Defaults
When working with reports, there are certain aspects that you want to be the same for every report. For example, you
might like to have report objects snap to the grid so that it's easier to line up objects next to each other. Crystal
Reports lets you set certain properties of the report designer so that they are the same for all reports. The primary
areas of the designer that you can control are the design area and the formatting properties of certain fields. To
change these defaults select the menu items File | Options. It shows the Options dialog box in Figure 2-1.

Figure 2-1. Report designer Options dialog box.

The Options dialog box might look a little overwhelming at first glance. There are eight different tabs to choose from
and each tab has many options listed on it. To make it easier to understand, we are only going to look at the options
that affect the Report Designer. We'll look at the other options later in the book for the sections that they apply to.
Table 2-1 shows a description of each tab.

Table 2-1. Option dialog box tab descriptions.

Tab Name Description

Layout Sets the defaults for the report designer and the report preview.

Changes how the report interfaces with the database and sets advanced optimization properties.
Database
This is discussed in Chapter 11.

Sets the default programming language and code formatting properties. This is discussed in Chapter
Formula Editor
5.

Reporting Specifies how report data is treated. For example, it determines how to handle Null values,
 whether to save report data with the report, etc. This is discussed in various areas throughout the
book.

Set the default formatting of each type of data. You can have strings formatted differently than
Fields
numbers. This is discussed in this chapter.

Similar to the Fields tab, the Fonts tab sets the default formatting for different types of data. For
Fonts example, you can specify a default format for summary fields that is different than the format of
group name fields.

Smart Tag & HTML Sets up the properties used by Office XP smart tags and previewing reports in HTML format. This
Preview applies to Business Objects Enterprise and is not discussed in this book.

Dependency Determines which aspects of a report the dependency checker should analyze. Discussed in
Checker Chapter 5.

Setting the Default Format Options

Not all formatting options apply to each type of field. For example, the String object doesn't need to specify the
default date format. Thus, the Date tab is only shown for the Date object. Setting the default properties for some
objects is more useful than for other objects. For example, the String object lets you set properties such as Suppress,
Can Grow, Text Rotation, and the line styles of the border. Each of these properties is really only used in unique
circumstances and therefore isn't something that generally gets set as a default property. On the other hand, the way
you format a Date object is usually the same throughout all your reports. For example, a company might have a policy
that all dates show the month using three letters and the year is four digits. This isn't going to change so it's a good
idea to set it as the default. The same applies to how numbers and Boolean values are displayed.

The second formatting tab within the Options dialog box is the Fonts tab. It lets you set the default font style
depending upon what the field represents. You can set the font for Summary fields, Group Name fields, Field
Titles, etc. For example, it's very common for a Summary Fields to have different font properties than the
Group Name Field. Setting the default font properties saves you time when designing reports.
Figure 2-4. The Fonts tab formats different field objects.

Note

The Default formatting is only applied to an object when it is

dropped onto the report. If you later change the default
formatting, these changes are not applied to objects already on
the report. To apply the default settings to an existing object, you
have to delete the object and then add it back to the report.

Chapter 3 - Sorting and Grouping

Being able to sort records in either ascending or descending order is a fundamental reporting skill. Sorting
makes it easy for a user to quickly find a particular piece of data buried within a large report. This chapter has
ten tutorials that quickly get you up to speed on every aspect of sorting and grouping data.

Reports can be sorted on a single field or on multiple fields. Sorting on multiple fields is only useful when the
sort field has duplicate values in it. To resolve which one should be listed first, the value in the second field is
used to resolve the conflict. An example of this type of report is an employee report that sorts by name. The
first sort field is the last name. When there are duplicate last names, the second sort field, the first name, is
used. When sorting on multiple fields, you have to specify the order that the fields get sorted in.

To select a field for sorting, either drag and drop it onto the Sort Fields window on the right or click on the
arrow buttons to move it over. The order in which you add the fields determines which one gets priority in the
sort order. The first field listed becomes the primary sort field. The next field is the secondary field, and so on.
When there are duplicate values in one of the fields, then the next field on the list is used to resolve the
conflict. This continues for all the sort fields whenever there are duplicates. If a field has a duplicate value
without another field after it, then the one that gets listed first isn't always predictable.

The bottom of the dialog box is where you set whether the field is sorted in ascending or descending order.
There are two radio buttons that set the sort order and only one can be selected at a time. Since there multiple
fields can be shown in the Sort Fields list and there is only one place to set the sort order, both radio buttons
apply to the currently selected field. To set the sort order of a field, first click on the field you want and then
click on the sort order.

Let's look at the example sort order shown in Figure 3-c. The primary sort field is Country. The secondary
field is Region and this is followed by the Customer Name.

Figure 3-c. Sorting records on Country, Region and then Customer Name.

The report first lists all the countries that start with the letter "A". The country Australia has duplicate values,
so the report performs a secondary sort on the Region field. The region New South Wales is duplicated so the
final sort order is by Customer Name.

If you later determine that you need to change the sorting order, modify it using the same steps mentioned
earlier.

Tutorial 3-1. Creating a grouping report

To practice working with groups, let's modify the Employee List report created in Chapter 1. The report lists each
employee on a line and we are going to modify it so that it first groups by the region and then by the city. Within this
grouping it sorts by last name.

1. Open the Employee List report you created in Tutorial 1-1.

2. Select the menu items Report | Group Expert. This opens the Group Expert dialog box.
3. In the Available Fields list click on the Employee_Addresses.Region field and drag it to the Group By list.
4. Click on the Employee_Addresses.City field and drag it to the Group By list.
5. Click the OK button. The dialog box closes and the two groups have been added to the report.
 6. In the report designer, move the report object Group #2 Name a little to the right so that it is offset from
Group #1 (the Region).
7. Click the OK button to save your changes. Your report's design should look similar to Figure 4-e. The report
preview is in Figure 4-f.

Figure 4-5. Employee List with groupings.

Figure 4-6. Employee List preview.

One unusual aspect about this report is that the first few records don't have any data in the Region field and all you
see are the two cities listed (London and Nottingham). It would be nice to classify these records so that they are
separate from the records that have data in the Region field. Soon we'll look at options for customizing the grouping
format as well as fixing problems such as this.

Formatting Groups
The default layout for groups is that they print one after the other. When a group footer is printed, the subsequent
group's header is printed right after it. Some report requirements require changing this default behavior. For example,
an invoicing report is designed such that it is grouped by client and each client gets their own invoice. For this report
you will put the client information in the group header section and force the group header to be printed at the top of a
new page. The summary values appear at the bottom of the page. The group footer is set to always print at the bottom
of the page.
The formatting options for groups are in the Section Expert. The Section Expert is discussed in detail in
Chapter 8 but for now we are going to look at which options are beneficial for using with groups.

To get to the Section Expert dialog box, right-click on the group section and select Section Expert. This opens
the Section Expert dialog box which lists all the sections along the left side and the section properties are
listed to the right. The properties of interest here are Print at Bottom of Page, New Page Before, New Page
After, Reset Page Number After, and Keep Together.

Print at Bottom of Page

This forces a section to always print at the bottom of the page even if the detail records stop printing in the middle of
the page. This is commonly set for the Group Footer section. It is useful for reports that print summary information
(sub-totals, disclaimers, logos, etc.). It is also useful for form letters where the bottom of the page must have exact
placement.

New Page Before / New Page After

These two properties are used to force the groups onto individual pages. This is so that you can split the reports apart
and distribute them independently. For example, a report grouped on Employee Id can be broken apart and each
employee can be given their own report without seeing any information about the other employees.

You can either set the New Page Before property for the Group Header or set the New Page After property for
the Group Footer. Both give you the same results of splitting the groups onto separate pages. The only
problem is that sometimes these options cause a blank page to be printed either at the beginning of the report
or at the end of the report. I address this more fully in the Advanced Tutorials section because it requires
writing formulas.

Reset Page Number After

This sets the page number back to 1 after the Group has been printed and is usually associated with the Group Header.
It insures that the page number for each group starts at 1. Just like the New Page Before property, this is useful for
reports that are separated and distributed.

Keep Together
The Keep Together property forces a section to stay on the same page. For example, if the Group Header has the Keep
Together property enabled and the entire header can't fit on the page, then it will be started at the top of the next
page. The important thing to note is that this only applies to an individual section and it won't take other sections into
account.

The Keep Together property may cause a little confusion because there is another property which has similar
characteristics and also has a similar name. It is the Keep Group Together property. Let's look at what each
property does and how they differ.

The Keep Group Together property looks at the entire group (its header, details and footer) and tries to keep
them on the same page. If the entire group can't fit on the remainder of the current page, then the group is
started at the top of the next page. This is clearly different than the Keep Together property because it only
applies to a single section and the Keep Group Together property looks at multiple sections.

It's important to note that the Keep Group Together property is in a totally different dialog box than the Keep
Together property. The Keep Group Together property is in the Change Group Options dialog box (select
Report | Group Expert | Options).
There are some rules associated with the Keep Group Together property. If a group starts in the middle of a
page and it can't be fully printed by the end of the page, then it starts printing at the top of the following page.
If a group requires more than a full page to print, then it will start printing at the top of the following page and
continue onto the next page. The Group Header will not be printed on the additional pages unless the property
Repeat Group Header on Each Page is set (also in the Change Group Options dialog box).

Chapter 4 - Selecting Records with Parameters

This chapter first shows you how to filter data using the Select Expert and then shows you how to create
advance filters using parameters. Parameters are covered in great detail because they are also used within
report formulas and for custom formatting. The effective use of parameters within record selection formulas
and custom formatting lets you create dynamic reports that are tailored to each specific user.

Using the Select Expert

To open the Select Expert, select the menu items Report | Select Expert. The other method is clicking on the Select
Expert button on the Toolbar.

If this is the first time the Select Expert has been run for the report, it shows the Choose Field dialog
box. As you can see in Figure 4-1, it simply lists all the fields available. The fields already used on the report
are shown first and they are followed by every available field from the report's data source. A field from either
of these groups can be selected.

Figure 4-1. The Choose Field dialog box.

Once you select a field from this dialog box and click the OK button, the dialog box is not shown again. Instead, you are
always taken to the Select Expert dialog box shown in Figure 4-2.
Figure 4-2. The Select Expert dialog box.

There are two tabs in the Select Expert dialog box. The first is titled with the field that was just selected in the Choose
Field dialog box. The second tab is titled . The tab with the field name has a dropdown box for selecting the filter
criteria. This is set to is any value. By default, reports don't have any filters turned on and every record gets selected.
Click the dropdown box to view available filtering options and select the one you want. The dialog box in Figure 4-2
shows all the options in this dropdown box. This list is the textual equivalent of the basic comparison operators. They
are described in detail in Table 4-1.

Table 4-1. Comparison operators for filtering data.

Criteria Description

Is Any Value Every record is selected. This is the same as not specifying a record selection formula.

Is Equal To The field must exactly match a specified value.

Is Not Equal To All records where the field does not match the specified value are selected.

Lets you build a list of acceptable values. As long as the field's value matches any one of the values in
Is One Of
the list then the record is selected.

Similar to Is One Of except that the record is selected if the field's value doesn't match any of the
Is Not One Of
values in the list.

Is Less Than The value must be less than the value specified.

Is Less Than or
The value must be equal to or less than the value specified.
Equal To

Is Greater Than The value must be greater than the value specified.

Is Greater Than
The value must be equal or greater than the value specified.
or Equal To

You specify a range of values and the field's value must be within this range. It can also be equal to the
Is Between endpoints of the range. For example, if the start and end points are 100 and 200, then the following
values would be acceptable: 100, 101, 199, 200.

Is Not Between The value must be outside the range. For example, if the start and end points are 100 and 200, then
 the following values would be acceptable: 1, 99 and 201.

Selects string fields that start with one or more characters. For example, a value of "B" would match
Starts With
the names "Brian" and "Barry".

Does Not Start Selects string field that do not start with the characters specified. For example, a value of "B" would
With match the names "Lynn" and "Karen" because they do not start with the letter "B".

Let's you use wildcards for specifying the matching criteria. Using wildcards is discussed more later in
Is Like
the chapter.

Is Not Like The opposite of Is Like.

Formula: Lets you type in the formula directly. Useful when the formula is too complex for the available criteria.

If you are setting a filter using a field that is a Boolean data type, you are given two new criteria: Is True and
Is False. These criteria do exactly what they imply. They select records where the field has a value of either
True or False.

Selecting a DateTime field gives you two additional options listed with the comparison operators. These
options let you select dates based on sophisticated date ranges. For example, you can select fields that fall
within the last 7 days or select fields that are in the first quarter of the calendar year.1 See Figure 4-3 for a list
of the available functions.

Figure 4-3. Available date range functions.

After selecting a comparison method, the right side of the dialog box changes so that you can enter the value to
compare the field to. With the majority of the comparisons, only a single dropdown box is shown. For example,
comparisons such as Is Equal To or Is Greater Than compare the field to a single value.
Selecting Records Tutorials

Tutorial 4-1. Selecting Records

Let's put what we learned about selecting records to use. In this tutorial we will set a filter on two fields: Country and
Last Year's Sales. For Country we want to select a specific set of countries to print (Canada, England, and USA). For the
Last Years Sales field we only want to see records with a sales amount over $50,000.

1. Open the Crystal Reports sample report Group.rpt. This report demonstrates grouping data on the Country
and Customer Name fields. It shows the previous year's sales by customer and their percentage of the total
sales.
2. Save the report as "Select Expert Tutorial.rpt".
3. Click the menu items Report | Select Expert.
4. The Choose Field dialog box opens for you to select the field to filter on. Select the Customer.Country field.
5. The Select Expert dialog box opens with the Customer.Country tab shown. For the comparison type, select Is
One Of from the dropdown box.
6. In the second dropdown box, select each of the following values: Canada, England and USA. This selects all
records that are from one of those three countries.
7. Create a second filter field by clicking on the tab and select the field Customer.Last Year's Sales. Click the Ok
button to go back to the Select Expert dialog box.
8. For this field we want all sales that are greater than $25,000. For the condition select Is Greater Than or Equal
To. In the second dropdown box type in $25,000.
9. To make sure you've entered both conditions properly, click on the Show Formula button in the bottom right
hand corner. Compare your formula to the following:

{Customer.Country} in ["USA", "England", "Canada"] and

{Customer.Last Year's Sales} >= $25000.00

10. Make any necessary corrections to your formula if there is a mistake.

11. Preview the report and it should look like the following. Notice that each sales amount is greater than $25,000.
Creating Parameters
The first part of this chapter showed how filtering data lets you customize a report to fit a specific user's needs. The
only drawback to this approach is that you have to know in advance what that user's needs are when you are
designing the report. And if different users have different needs, then you either have to save a separate copy of the
report for each variation or the user needs to know how to customize the report themselves. Neither of these options
is the most practical approach. A much better solution is to create a filter that uses parameters to prompt the user for
their input prior to printing the report. This lets you create one report that satisfies many users.

In their most basic form, report parameters are simply a means of asking the user questions about what they
want printed. When the user opens the report he or she is prompted with each question and the answers are
used to carry it out. Some examples of typical questions are, "What sales district do you want to report on?"
and "What is the minimum sales amount you want to see?" Questions such as these let the user take a report
that was designed to be used by multiple people and only view the data that he or she is interested in. The
biggest benefit is that you don't need to save a separate copy of the report for each user. New parameter values
can be entered each time the report is viewed. Figure 4-5 shows a parameter prompting the user for a report
date and the print range.
Figure 4-5. Parameter prompt dialog box.

In this example the user is being prompted to enter two parameters. The first being an Order Date and the second is a
date range. You can see a calendar displayed on the screen as well. Crystal Reports lets the user enter dates by typing
them in directly or picking the date from this calendar control.

Tip

This dialog box is fully customizable. You can change the fonts and
colors by modifying a single file. This is discussed at the end of the
chapter in the Advanced Tutorials section.

Note New to Crystal Reports XI is the ability for parameters to present the user a list of live data values to
choose from. In previous version of Crystal Reports the user could only choose from a static list of values that
you created in advance. The values weren't able to reflect recent changes to the database. Crystal Reports XI
now gives this powerful new feature that connects the value list to the data source. This is discussed in more
detail later in the chapter.

Chapter 5 - The Formula Workshop

Crystal Reports comes with a powerful set of tools that let you build sophisticated formulas for creating
reports. You can perform tasks such as conditional formatting, specialized calculations, selecting records and
report alerts. The next three chapters are a thorough training manual for creating and programming formulas.

Writing Formulas with the Formula Workshop

Formulas are referenced in different ways depending upon which part of the report is using them. But they all
share the same editor. The Formula Workshop is the command center for entering and editing formulas.
Getting a solid understanding of how to use the Formula Workshop is essential before getting into the details
of writing formulas. The Formula Workshop is shown in Figure 5-1.

Figure 5-1. The Formula Workshop window.

The Formula Workshop consists of five separate windows. The left window along the side is the Workshop
Tree. It shows every formula being used in the report and is categorized by the type of formula. The three
windows along the top present the library of fields and functions used for writing formulas. Within these three
windows is every available database field, built-in function, and operator. The bottom window is the
Definition area where you write and edit formulas. It is like a fancy Notepad editor where you enter text and it
highlights different keywords.

Using the Toolbars

Before getting into the details of what each window does and how to use it, let's first explore the toolbars
along the top and the buttons on them. Each group of buttons has a different way of assisting you with writing
formulas.

Displaying Windows

These buttons make minor modifications to the windows within the Formula Workshop. The first button
toggles the categories and items to be either sorted or unsorted. Sorting the categories makes it easier to find
an item in the tree. Clicking the button a second time returns them to their natural order.

The last three buttons hide one of the three other windows. The three windows that they hide are the Report
Fields window, Functions window, and the Report Operators window. Clicking the button a second time
displays the window again.

There are some other buttons that work with the windows, but they are scattered in different locations around
the toolbars.
This button toggles the display of the Workshop Tree window along the left side. It is located above the other
three buttons.

The Show Formatting Formulas button toggles the display of formatted report objects within the Workshop
Tree window. This makes it easy to see which report objects have conditional formatting. For example, when
you initially open the Formula Workshop window and look in the Workshop Tree window, under the
Formatting Formulas node is a list of every report section. Clicking on each section expands that node and
shows every report object in that section. The objects that have conditional formatting can be expanded one
more level to see those formulas.

Of course, not every report object has conditional formatting formulas associated with it. When you click the
Show Formatting button, the report objects that don't have any conditional formatting formulas are hidden.
This makes it easy for you to look at the report sections and quickly see how much conditional formatting has
been used and look at the details. If this is a report you haven't worked on in a while (or a report that someone
else created), then this button helps you quickly understand what is happening behind the scenes.

The Use Expert/Editor button opens the Formula Expert. When clicking this button, all the windows except
the Workshop Tree are removed and the Formula Expert appears in their place. It helps you create formulas
based on a custom function. The Formula Expert discussed in more detail in Chapter 6.

Creating New Formulas

Figure 5-2. Menu items for the New Formula button.

The New button creates a new formula. When you click on it, it shows you a list of the different types of formulas and
you click on the type you want to create. If you've been working on an existing formula and haven't saved it yet, you
are asked if you want to save it before continuing.

Editing Code

These five buttons assist you with writing formulas. The first button checks the syntax of the formula. If it
finds an error in the formula, it prompts you with a message box telling you if there is a syntax error or not.
You can check the syntax after you are finished writing the formula or you can do it after each line. Checking
the syntax after each line lets you find a problem and correct it before going forward. This is good when you
are just learning how to write formulas and you aren't confident of what you are doing yet. It's also useful if
you are writing a complex formula and you want to double check that you didn't make a typo.
The next two buttons are the Undo and Redo buttons. You are probably familiar with these from other
programs such as Microsoft Word or Excel. The Undo button removes the last change you made. The Redo
button puts that change back if you decide that you shouldn't have clicked the Undo button. It's like an Undo-
The-Undo button.� 

The last two buttons help you find information. The magnifying glass browses data of a field. This is the same
as the Browse Data button we saw with the report experts. It is only available when a field in the Report
Fields window is selected.

The last button is the Find button. As you might expect, it finds the specified text within the formula and
highlights it. But that is just one part of its functionality. What is a really nice feature is that it not only
searches your formula, but it searches the text within any of the windows. Look at Figure 5-3 to see what the
Find dialog box looks like.

Figure 5-3. The Find dialog box lets you do more than just search within the formula.

Notice in the center of the dialog box that it has radio buttons that specify where to search for the text. It finds
text in Fields, Functions, Operators, and Edit Text (the Definition area). If you've ever tried to find a function
name in the Functions tree, it can be confusing to figure out which node the function is listed under. The Find
dialog box finds it for you. And if you only know part of the name then it does partial text finds as well.

The Find feature also replaces existing text with new text. But this is only enabled for the Edit Text option
(you aren't about to start renaming Crystal functions!) If you want to rename a formula name that is referenced
within another formula, remember from before that you don't have to do this with Crystal Reports. When you
rename the formula within the Field Explorer window, Crystal Reports automatically searches throughout the
entire report and updates all formula references to use the new name.

Chapter 6 - Built-In Functions

The Formula Workshop in Crystal Reports gives you the ability to write very powerful formulas. In addition
to writing your own formulas, Crystal Reports has dozens of built-in functions that decrease the amount of
work you have to do.

String Functions
The ability to modify and concatenate strings is a powerful feature of many programming languages, and
Basic syntax doesn't disappoint. This section breaks out the different categories of string functions and
summarizes how they work. The categories are: Analyzing a String, Parsing Strings, and Manipulating
Strings.
Throughout this section, many of the functions listed use one or both of the arguments called compare and
start. Rather than repetitively list their descriptions throughout the chapter, they are explained here for your
reference.

The compare argument determines when string comparisons are supposed to be case sensitive. If compare is
0, the search is case-sensitive. If it is 1, the search is not case-sensitive. Case sensitivity means that even if two
strings consist of the exact same letters, they will still be treated as different strings if one is upper-case and
the other is lower-case. For example, �Joe� would not be the same as �joe�. The compare argument is
optional. If it is left out, the comparison defaults to 0 (case sensitive).

The start argument tells the function to process characters starting at a specified position. Any characters that
are prior to that position are ignored. This argument is optional. If it is left out, then the function is performed
for the entire string.

Converting Data Types

Basic syntax is a type safe language that requires all constants and variables in the same formula to be of the
same data type. It also requires you to pass constants and variables as arguments using the exact data type that
the formula expects. Even though the data types in Basic syntax are fairly simple, you still have to make sure
that they are compatible. Fortunately, this shouldn't cause problems because there are functions to convert
between the different data types. Table 6-5 lists these conversion functions.

Table 6-5. Conversion Functions

Conversion Function Description

CBool(number), CBool(currency) Convert to Boolean.
CCur(number), CCur(string) Convert to Currency.
CDbl(currency), CDbl(string), Convert to Number. Equivalent to
ToNumber(). See the section
CDbl(boolean) �Formatting Values for Output�.
CStr() Convert to String. Equivalent to
ToText().
CDate(string), CDate(year, month, Convert to Date.
day), CDate(DateTime)
CTime(string), CTime(hour, min, Convert to Time.
sec), CDate(DateTime)
CDateTime(string), Convert to DateTime.

CDateTime(date),

CDateTime(date, time),

CDateTime(year, month, day)

CDateTime(year, month, day, hour, Convert to DateTime.

min, sec)
ToNumber(string), Convert to a Number.
ToNumber(boolean)
ToText() Convert to String. Same as CStr().
IsDate(string), IsTIme(), Test a string for being a valid date/time.
IsDateTime()
IsNumber(string) Test a string for being a valid number.
ToWords(number), Convert a number to its word equivalent.
 ToWords(number, decimals)

Most of the above functions are very simple. Pass the function a field/variable of one data type and it returns
the equivalent in the other data type. The CBool() function takes a number or currency value and converts it to
Boolean True or False. Any non-zero value is converted to True and zero is converted to False. When it is
displayed on a report, it prints the words �True� or �False�.

The CCur() function takes a number or string and converts it to the Currency data type. When converting a
string, it can have formatting characters in it (�$�, �,�, etc.) and it will still be converted properly.

The CDbl() and ToNumber() functions are equivalent. Pass each a value and it gets converted to a number.

The CDate(), CTime() and CDateTime() are all similar. Pass them a string and it gets converted to the proper
data type. The string parser for this function is very sophisticated. It lets you pass strings as diverse as �Jan
19, 1991�, �5/26/1998� and �2002, Feb 04�. You can also pass numbers as individual arguments for
representing the different parts of a date and time. See Table 6-5 for the various argument options.

When converting a string to a date or number, you run the risk of raising an error if the string isn't in the
expected format. You can avoid this by testing the strings validity before converting it. The IsDate() and
IsNumber() functions do this for you. They return True if the string can be properly converted. If not, they
return False. For example, here is a function that converts a string to a date, but only if it is a valid date.

If IsDate({Invoice.ExpirationDate}) Then

��� Formula = CDate({Invoice.ExpirationDate})

End If

The ToWords() function takes a number and converts it to its equivalent in words. This is similar to writing a
dollar amount on a check and then spelling out the full amount in words. It prints the decimal portion as
�##/100�. You can set the number of decimals it displays by passing a number to the second argument,
which is optional. Notice in the second example how it only displays one decimal place and it rounds it up to
the next higher number.

'Demonstrate the ToWords() formula

Formula = ToWords(123.45)� 'Result is �one hundred twenty-three 45 / 100�

Formula = ToWords(123.45,1) 'Result is �one hundred twenty-three and 5 / 100

Math Functions
Table 6-8. Math Functions

Function Name Description

Abs(number) Return the absolute value.
Fix(number, decimals) Return a number with a specified number
of significant digits.
Int(number),� numerator \ Return the integer portion of a fractional
denominator number.
Pi 3.14...
 Remainder(numerator, Return the remainder of dividing the
denominator), numerator by the denominator.
numerator Mod denominator Return the remainder of dividing the
numerator by the denominator.
Round(number, decimals) Round up a number with a specified
number of significant digits.
Sgn(number) Return a number's sign.
Sqr(number), Exp(number), The standard arithmetic functions.
Log(number)
Cos(number), Sin(number), The standard scientific functions.
Tan(number), Atn(number)

Most of these functions perform basic mathematical functionality. There are only a couple of interesting
points to notice. Working with whole numbers and decimals is done numerous ways. The Fix() and Round()
functions take a fractional number and truncate it to a specified number of digits. The Round() function will
round up to the nearest decimal. The number of decimals to display is optional and the default is zero. The
Int() function is similar to the Round() function except that it only returns the whole number and will round
down to the nearest whole number. Table 6-9 shows how the three functions will return a different number
depending upon the decimal portion and whether the number is positive or negative.
Table 6-9. Examples of Truncating Decimals

Function 1.9 -1.9

Fix() 1 -1
Round() 2 -2
Int() 1 -2

If you want to get the whole number and you have the numerator and denominator available. You can use the \
operator to perform integer division. This does the division and only returns the integer portion of the result.
The Mod operator and Remainder() function return the remainder after doing the division.

'Demonstrate the integer division and the Mod operator

Formula = 10 \ 3'Returns 3

Formula = 10 mod 3'Returns 1

Formula = Remainder(10, 3)'Returns 1

Chapter 7 - Writing Formulas

Crystal Reports gives you the option to program formulas in either Crystal syntax or Basic syntax. This
chapter teaches you how to program with both Basic syntax and Crystal syntax. Dozens of code samples show
you exactly how to write code that you can put in your own reports today.

Formula Fundamentals
To learn how to program with Basic syntax, you want to start with the fundamental tasks that might seem pretty
mundane. There are certain aspects that you need to know even if you only plan on writing some very simple
formulas. By understanding the basic structure of formula, you can build on this knowledge to write more complex
logic. This section on the fundamentals of writing formulas covers Case Sensitivity, Writing Comments, Returning a
Value, Using Data Fields, Declaring Variables, and Data Types.

Case Sensitivity
When a language is case sensitive, it considers identical words to be different when the letters use a different case.
Basic syntax is not case sensitive. The variable FirstName is the same as the variable firstname. Although these two
variables are syntactically equivalent and can be used interchangeably, it is recommended that you keep the case
consistent so that your program is easier to read.

Crystal syntax IS case sensitive. When using variables in your code, you have to make sure that the case of
each letter in the variable matches with how it was declared.

Returning a Value
Formulas are always used to return a value. The data types returned must be simple and can be Number, Currency,
String, Boolean, Date, Time and DateTime. You cannot return complex data types such as range or array.

Formulas return a value by assigning the value to the Formula variable. Formulas must always return a value.
The following code returns the value True.
Formula = True If a formula has multiple statements that return a value, all the assignments must be of the
same data type. Although there is no way to specifically tell Crystal Reports the data type to return, the
compiler will compare all the assignments and check them for consistency. As an example of what not to do,
see the following example.

�The following IS NOT VALID due to the different data types returned and produces an error
If Age > 65 Then
    Formula = �Retired�
Else
    Formula = Age
End If

To return a value with Crystal syntax, put the value on a line by itself. Nothing else should appear on the line. This can
be as simple as assigning a value to a variable, or the result of a calculation. When a formula has multiple calculations
in it, the last one in the formula is used. The following code returns True if the employee received a bonus. If not then
False is returned.

If {Employee.Bonus} > 0 Then

    True
Else
    False

Referencing Report Fields

Writing formulas requires referencing all types of data from your report and the tables that the report uses. The types
of data that can be referenced consist of running totals, functions, formulas, and table fields. The syntax for
referencing a field is to put curly brackets around it. In addition to that, each type of field has a special character which
designates its type. Some examples follow.

Formulas are referenced by putting @ in front of their name.

{@Formula}

Parameters are referenced with a ? in front of the name.

{?Parameter}

Running total fields are referenced by putting # in front of the name.

{#RunningTotal}

Table fields are referenced by separating the table name and the field name with a period between the two. Spaces
are allowed.

{Customer.First Name}

Group fields use the field name with the GroupName() formula.

GroupName({Table.GroupField})

Summary fields pass the field name and the group field as parameters to the summary function.

Sum({Table.FieldName}, {Table.GroupName})
Declaring Report Variables
Variables store a piece of data so that it can be used again within the function. For example, if a calculation is
performed multiple times in a formula, then you can store the result in a variable and reference that variable when
you need it. This improves performance because you don't have to repeat the calculation. Variables are also used to
pass data between formulas and even to sub-reports. By declaring a variable as Global or Shared, it can be used by
other functions and sub-reports. If you couldn't share variables, then you would have to duplicate the same calculation
in multiple formulas.

To use a variable in a formula, you first have to declare it. This consists of giving it a name and specifying its
data type. Declaring a variable follows the format of declaring the variable using the Dim keyword followed
by the variable name. Use the As keyword to specify the data type.

Dim var As dataype

Crystal syntax is the opposite of Basic syntax. It lists the variable scope and data type before the variable name.

Local datatype var

The first time a formula is called, all of the variables are automatically assigned their default values. For example, a
Number variable is assigned the value 0. See Table 7-1 for a list of the default values for each data type.

Table 7-1. Data Type Default Values

Basic Data Type Crystal Data Type Default Value

Number NumberVar 0

Currency CurrencyVar $0

Boolean BooleanVar False

String StringVar ��

Date DateVar Date(0,0,0) � The Null Date value 00/00/00

Time TimeVar No default value. Null.

DateTime DateTimeVar No default value. Null.

A variable's scope determines which formulas have access to that variable. You can set the scope so that a
variable can only be used within the formula it's declared in, or you can make it available to the rest of the
report. There are three operators that are used to declare scope:

1. Local/Dim: The variable can only be seen within the current formula. The variable is private to that formula
and can't be used anywhere else in the report. This is the default scope if you don't specify it.
2. Global: The variable can be seen within any formula inside the same report. Sub-reports do not have access to
the variable.
3. Shared: Similar to Global, but the variable can also be seen within sub-reports.

Dim HireDate As Date

Shared AffiliateCities() As String

CAUTION
 In Crystal syntax, the default scope is Global, not Local. You
should be aware of this since this is the exact opposite of how
Basic syntax handles the scope when it isn't declared.

Crystal Report Data Types

Simple Data Types

Crystal Report's Basic syntax supports the standard typical data types that we expect in a language: Boolean, Number,
Currency, String, DateTime, Date, and Time.

Notice that rather than have a large number of numeric data types such as integer, double, etc., there is simply
a single data type called Number (Crystal syntax uses NumberVar). There is no need to worry about whether
the number is fractional or what its largest value is. One data type handles all cases.

The Currency data type is treated the same as a Number data type with a few exceptions:

 Currency can only have two decimal places. If assigned a number with more than two decimal places, it rounds
up to the nearest penny.
 Currency automatically gets formatted as a monetary value. This eliminates the overhead of you having to
format the variable whenever it gets printed.
 Since Currency is a different data type, it must be converted to Number to be used in mathematical
assignments using non-currency variables. See the section "Converting Data Types' for more information.

Strings use the double quote, ", to specify a string literal. A character is represented by a string of length one.
Referencing a position within a string is Base 1. Thus, if you want to refer to the first character in a string, you
would use an index of 1. The maximum length of a string constant is 65,534 characters. Information on using
the Basic syntax built-in string functions is in Chapter 6.

�Demonstrate assigning a string constant to a variable

Dim Var As String

Var = "This is a string"

Dates are a little unusual in that there are three different data types available. The Date type can only store a date and
the Time type can only store a time. It's preferable to use these data types if you don't need both values stored in a
variable. If you do need both types in the same variable, use the DateTime type. Designate a DateTime constant by
surrounding it with the # character.

Dim MyBirthday As DateTime

MyBirthday = #5/23/1968#

Chapter 8 - Advanced Formatting Techniques

Once you've mastered the basics of creating reports, it's time to learn some of the finer points of report design.
Crystal Reports gives you numerous formatting options for printing report sections for specialized purposes.
For example, you can create specialized reports such as labels or multi-column reports. You also have the
option of creating multiple sections within a report and format each section individually. This one feature
gives you an almost unlimited number of ways to make a report fit different scenarios and customize it for
individual users. The first part of this chapter covers how to format Sections to create professional reports for
any circumstance. You are given lots of creative examples of how to use Sections in real world reports.

Figure 8-2. Section Expert button.

The Section Expert lists every section in the report and the formatting options available. The formatting
options are represented by checkboxes because they are either enabled or disabled. Beside each option is a
Formula Workshop button that uses conditional formatting to set the property value based upon a formula.
This gives you flexibility for setting when a formatting option is turned on because the formula can use data
from the record currently being printed. Every time a section is printed, the formula is evaluated and its result
determines whether the formatting option should be applied. This was described in more detail in Chapter 5.

When selecting the section from the list on the left, the formatting options on the right change depending upon
which section was selected. The applicable properties are enabled and the other properties are grayed out. The
list of formatting options doesn't change (for the most part), but you are prevented from choosing the ones that
don't apply. Table 8-1 lists the different formatting options for sections.

Table 8-1. The formatting options for sections.

Formatting Option Description

Free-Form Allows you to place report objects anywhere in the section without being confined to the
Placement positioning of the grid lines.
Hide (Drill-Down
Doesn't show the section, but allow the user to drill-down into the data.
OK)
Suppress (No Drill-
Doesn't show the section. Drill-down is not allowed.
Down)
Print at Bottom of
Force the section to always print at the bottom of the page.
Page
New Page Before Force a page break before the section prints.
New Page After Force a page break after the section prints.
Reset Page Number
Reset the page number counter back to 1 after the section prints.
After
Keep Together Keep the section together on the same page.
Suppress Blank
If there is no data in the section, do not print it.
Section
Underlay Following Print the current section on top of the following sections. Proper alignment is critical so
Sections that objects don't overlap each other.
Format with Creates mailing labels and newspaper column style reports. This is only listed for the
Multiple Columns Details section.
Locks the positioning and formatting of all report objects in that section. You can't move
Read-Only
the objects or modify their properties.
Sets objects within a section to keep their horizontal position relative to a grid object. If
Relative Positions the grid object grows to the right, the other objects will shift accordingly. Primarily used
with Cross-Tab reports.
Reserve Minimum Maximizes the amount of space available for printing report details by minimizing the
Page Footer amount of space reserved for the Page Footer.
Advanced Report Formatting with Sections

Hiding and Suppressing Sections

Hiding a section is used for drilling-down on detail records. This presents the user with a much smaller report because
the groups only show summary information. They can look at the detail information by double clicking on the group
header. This creates a new tab in the viewer with the detail information being displayed inside.

Suppressing a section is done when you don't want the user to see the information in it nor do you want them
to be able to drill down into it. Of course, this leads to the question that if you don't want the user to see the
information then why did you add the section? Suppressing sections is usually used in conjunction with
conditional formatting. The Formula Workshop is used to turn this option on or off depending upon other data
that the report has access to. For example, if this is sensitive data, then you would only let administrators see
the detail information. All other users would have the detail section suppressed and they would only be able to
see the summary information. This effectively lets you use the same report for different users and different
purposes.

Printing Sections at the Bottom of a Page

Printing sections at the bottom of the page is useful when printing reports that are one page long and have summary
data listed at the bottom. An example is an invoice where the bottom of the page prints the total amount due. Invoices
also print the aging schedule of past due balances at the bottom of the page. Another example is a form letter that
requires authorized signatures of certain parties. Printing sections at the bottom of the page is done by adding a group
to the report and checking the Print at Bottom of Page option for the Group Footer.

Note

Although the Details section has the Print at Bottom of Page

option available, it has no effect. The detail records always print
one after the other from top to bottom.

Forcing a Page Break

It is very common to want groups to appear by themselves. Data in each group is listed separately from the other
groups. Forcing page breaks makes data from each group appear on their own pages. The group is used to identify
where one report ends and the next one starts. An example is a report that has to be broken apart and distributed to
multiple people.

Page breaks can be forced to occur either before or after a section. Unfortunately, each option has the problem
of always printing an extra blank page. If you force a page break before a group header, then the first page of
the report is blank. If you force a page break after the group footer, then the last page is blank. The way
around this is to use one of two built-in functions in the conditional formula: OnFirstRecord or OnLastRecord.
By doing a Boolean Not in the formula, it temporarily turns page breaks off for the section. For example, if
you wanted to force a page break after the group footer, use the following formula (using Basic syntax) in the
New Page After format option: Formula = Not OnLastRecord This formula returns True for every record
leading up to the last record. Thus, there is always a page break after the group footer. Once the last record is
printed, this formula returns False and the option to force a page break is turned off. The last page will not
have a page break printed after it.

Resetting the Page Number

Resetting a page number back to Page 1 makes the page appear as if it is the first page in the report. This is good to
use in combination with forcing a page break after a section. For example, for the Group Footer, set the both of the
properties New Page After and Reset Page Number After to True. When you distribute the pages of the report to
different people, each person will have a report that starts on page 1. They won't know that the pages they received
were part of a much larger report that was broken apart.

Keeping Sections Together

Since it is common to print a lot of data in a section and since it is very hard to control exactly where a section is
printed on a page, sections are commonly split across pages. A report can start printing a section at the bottom of the
page but not have enough room to print all of it and print the remaining portion of the section on the next page. If it is
important that all the information within a section be printed together, check the Keep Together option. Before the
section is printed, it is analyzed to see whether it fits on the page. If it doesn't fit, a page break is forced and the
section starts printing at the top of the next page.

When using this formatting option with groups, the report tries to fit the entire group (including the header and
footer) onto the page. If the group is larger than one page, a page break is forced and the group gets printed on
the next page.

Suppressing Blank Sections

Printing sections that don't have any data leaves blank rows in the report. This makes a report look unprofessional
because of the gaps that seem to randomly occur. To fix this, set the option Suppress Blank Section to skip over
sections that don't have any data. This option is used most frequently in conjunction with creating multiple report
sections. This is covered in more detail later in the book, �Adding Multiple Sections�.

Chapter 9 - Charting Data

Being able to visualize data can have a tremendous impact on the reader. Compared to just printing raw
numerical data, adding a chart to a report makes it possible for readers to quickly grasp the important
relationships between data. Many times reports are used with proposals to sell a reader on an idea or plan.
Adding a colorful chart can sell your idea more effectively than a dry report filled with endless numbers. This
chapter shows you what types of charts are available as well as how to modify their appearance so that they
can make your report more appealing and quickly get your message across.

Chapter 9
Being able to visualize data can have a tremendous impact on the reader. Compared to just printing raw numerical
data, adding a chart to a report makes it possible for readers to quickly grasp the important relationships between
data. Many times reports are used with proposals to sell a reader on an idea or plan. Adding a colorful chart can sell
your idea more effectively than a dry report filled with endless numbers. This chapter shows you what types of charts
are available as well as how to modify their appearance so that they can make your report more appealing and quickly
get your message across.

Choosing the Proper Chart

Charts are used to make it easy to compare sets of data. The visual aspect of a chart lets the reader immediately
recognize things such as the differences in quantity, percentage of the whole or numerical trending. Crystal Reports
gives you 16 different types of charts to choose from. Table 9-1 lists the types and shows a sample picture of each.
Table 9-1. Example chart types.

Chart Type Sample Chart Type Sample

Bar Radar

Line Bubble

Area Stock

Pie Numeric Axis

Doughnut Gauge

3D Riser Gantt

3DSurface Funnel

XY Scatter Histogram

With the wide variety of reports available, it can be hard to decide which one to use. Of course, you want one
that looks good, but more importantly is that it is appropriate for the data you are displaying. Certain chart
styles are more effective at presenting certain types of data than others. Making this decision can be a bit
tough at first. Table 9-2 lists each chart type and shows where it is effective. This table gets you started in the
right direction for choosing the proper chart.

Table 9-2. Effectiveness of different chart styles.

Chart Style Effective at ...

Bar Chart Comparing the differences between items and events.

Comparing items and events against the same scale without relation to time.

Showing relationships between sets of data using grouping.

Note: The X-axis is generally non-numeric. When it is numeric, the interval between isn't relevant.

Line Chart/Area
Comparing continuous data over a period of time against a common scale.
Chart

Tracking movement over time.

 Examining trends between two or more sets of data.

Note: The X-axis represents a unit of time.

Pie/Doughnut
Visualizing the percent of the whole.
Chart

Examining relationships as part-to-whole.

Note: There is only a single axis being represented. Thus, only one value is being charted.

3D Riser/Surface
Showing trends with relationship to time.
Chart

Note: It uses a three dimensional surface to make it easy to analyze a large quantity of data.

X-Y Scatter Chart Charting a large quantity of values without relation to time.

Finding groups of data where there is a large percentage of similar data points.

Comparing data sets in a star pattern. The importance/relationship of each data set is determined by
Radar Chart
having the target value start at either the center of the axis or the outside.

Comparing the significance of data points by looking at the size of each bubble compared to the other
Bubble Chart bubbles. Similar to the X-Y chart, but with a third data point. The third data point determines the
bubble's diameter. Each bubble is proportional to the value compared to the other data points.

Analyzing stock values. Shows the trading range for the day as well as first trade and last trade
Stock Chart
amounts.

Gauge Plotting one or more data points using a dial format.

Represents a number by its position on the dial.

Plots activities in a business plan. Shows how long an activity took to complete as well as its
Gantt
relationship to the other activities.

Each activity must have a start date and stop date associated with it.

Funnel Shows stages in a sales process.

Histogram Shows how data is distributed with relationship to the mean value.

Makes it easy to visualize the pattern within a large number of data points.

Table 9-2 has a lot of information in it, so let's try to make it easier to digest by looking at a table that
highlights the important aspect of each chart. Table 9-3 lists different aspects you have to consider when using
a chart and lists which one applies to which chart.

Table 9-3. Common chart characteristics.

Chart Time Show % # of Data

Bar N N Small/Med
Line Y N Small/Med

Area Y Y Small/Med

Pie/Doughnut N Y Small/Med

3D Surface Y N Small/Large

X-Y Scatter N N Large

Radar N N Small/Large

Bubble N N Small/Large

Stock Y N Small/Large

Gauge N N Small

Gantt Y N Small/Large

Funnel Y Y Small/Med

Histogram N N Large

To use Table 9-2 and 9-3, think about why you are using a chart to present your data. Ask yourself what is the
message you are trying to convey to the reader. Scan the list of reasons why one chart is more effective than
the other charts. Once you see a description that best matches your purpose, select that type of chart.

For example, assume you have a report that prints the annual sales for each division in a corporation. The
message you are conveying is which division had the largest sales volume as well as which division had the
lowest sales. Three charts that are good at comparing different data sets is the bar chart, the line chart and the
X-Y chart. The bar chart immediately looks good because it is effective at comparing differences between
items. The line chart also compares data, but it does so over a period of time. This doesn't apply here because
the data is within the same time period (i.e. the same year). So the line chart is not a good choice. The X-Y
chart compares data, but it is done with respect to two data points. In other words, both the X and Y axis must
represent numerical data. The sales report is charted with the sales volume and the division name. Since the
division name isn't numerical data, it can't be used with the X-Y chart. The best choice for the division annual
sales report is the bar chart.

Let's build on this example by saying that you are given a new requirement where the report has to be
modified so that it is now a drill-down report. It currently shows the annual sales per division and it needs to
be modified so that you can drill-down on a division and see its monthly sales. This helps the reader determine
if the division had a particular month that was exceptionally better than the other months or if the division was
consistently improving.

The purpose of this chart is very similar to the first chart. You want to tell the reader how the total sales
compare to each other. But this example has a slight variation: you are now charting for a single division and
the individual months are being compared to each other. You are working with data that changes over a period
of time and looking for the trend. The only reason we didn't use a line chart in the first example was because
the data didn't relate to time. This example does relate to time and it is also looking at the sales trend. So the
line chart is an excellent choice for presenting the monthly sales figures.

Adding Charts
There are two ways to add a chart to a report: with the Report Expert and with the Add Chart Expert. If you are
creating a report from scratch and you know in advance that it will use a chart, you can use the Report Expert to add
the chart during the initial report creation. If you decide to add a chart later, right-click on the report and select Insert
| Chart. This creates a floating rectangle that is attached to the mouse. As you move the mouse around, the rectangle
moves with it. If a section doesn't allow a report to be placed there, then the cursor changes to a circle with a line
through it to tell you that you can't place it there. When you move the mouse over a section that does allow charts,
the cursor changes back to the standard mouse cursor. Put the cursor in the section where you want to place the chart
and click the mouse button to drop it there.

After placing the chart in the appropriate section, either the Chart Expert appears or a default chart is shown.
If the Chart Expert doesn't appear automatically, then display it by right-clicking on the chart and select Chart
Expert.

Figure 19-1. Chart Expert dialog box.

This dialog box has six tabs that are shown by default: Type, Data, Axes, Options, Color Highlight and Text. Each tab is
discussed in detail in the following sections.

Selecting a Chart with the Type Tab

The Type tab selects the type of chart to display. As we discussed in the previous sections, there are over a dozen
different types of charts to choose from. As you click on each chart type in the list, examples are shown in the window
to the right. Each example corresponds to a slight variation that you can choose from. The variation usually relates to
how the values are plotted on the chart or their relationship to each other. For example each value could be plotted
along the y-axis using its value, or it could be plotted as a percentage of the whole.

Charts that use an X-Y axis format have an option button that selects whether it is a vertical or horizontal
chart. Vertical is chosen by default.

Chapter 10 - Creating Cross-Tab Reports

Cross-tab reports are a powerful way to create summaries of data in a spreadsheet style format. They generate
summary data in a grid where the rows and columns represent groups of data. This provides the user with a
report format that is easy to read and uses a small footprint on the page. After this chapter you should have a
good understanding of how to use cross-tab reports and you'll also get some practice with creating them.

Understanding Cross-Tab Reports

Cross-tab reports format groups of data into an easy to read grid format. This grid format is very similar to the way a
spreadsheet represents data. It lets the user visually analyze the data in a way that makes it easy to compare values in
one group against the values in another group. Before we see what a cross-tab report looks like, let's look at a typical
report that summarizes data within groups. After looking at Figure 10-1, we'll see it reformatted as a cross-tab report.

Figure 10-1. Grouping by Product and Quarter.

This report has two grouping fields. The outermost group is by Product Type and the innermost group is the Order
Date grouped quarterly. The group header for the Order Date is the first date in the period. The detail records show
you the Employee Id, Order Date, and Quantity. There are two sub-totals of the quantity. The first occurs on the
change of quarter and the next is on the change of product type.

This is a pretty standard grouping report and it shares a common problem with other grouping reports: the
sub-total amounts are spread out across multiple pages. This makes it hard to compare numbers because they
aren't consolidated into a single page.1 A user reading this report will find that they are continuously flipping
pages to see how the sales of one product compare to the sales of another product.

Re-writing this report as a cross-tab report eliminates this problem. Figure 10-2 shows the same report in
cross-tab format.
Figure 10-2. Cross-tab report by Product and Quarter.

The cross-tab is much easier on the eyes. The outermost group field on the original report, Product Type, is
represented on each row of the grid. The two columns represent the innermost group field, Order Date grouped by
quarter. These columns span horizontally along the page. Although the original report has many detail records, these
are ignored when generating the rows and columns of the crosstab. Only the values of the grouping fields are listed.
This cross-tab report took two grouping fields and made them the X-axis and Y-axis of the grid.

Creating a Cross-Tab Object

The name "Cross-Tab Report" is a little misleading. It makes it sound like the whole report only shows the cross-tab
grid and that no other data is printed. This isn't true. A cross-tab report refers to a report that has a cross-tab object in
one of its sections. The rest of the report is just a typical report. This object is similar to the other report objects on a
report. It has properties that let you modify its fields and how it's formatted.

There are two ways to add a cross-tab object to your report. On an existing report, right-click on the report and
select Insert | Cross-Tab. This changes the mouse to the shape of a rectangle. Drag and drop the rectangle onto
the report where you want the cross-tab object to be shown. An empty cross-tab object, shown in Figure 10-4,
is displayed as a placeholder.

Figure 10-4. The Cross-Tab Expert placeholder.

The second way create a crosstab object is to choose the Cross-Tab Report Wizard from the Start Page. After selecting
the data source and tables to pull data from, you are presented with the Cross-Tab Expert where you select the fields
that are shown in the rows, columns, and summary fields. The Cross-Tab Expert is discussed next.

Modifying the Cross-Tab Properties

After getting the empty cross-tab object on the report, you need to modify its properties so that it knows what data to
display and how to display it. Right-click on the cross-tab object and select Cross-Tab Expert. This opens the Cross-Tab
Expert shown in Figure 10-5.
Figure 10-5. The Cross-Tab Expert dialog box.

CAUTION

The Cross-Tab Expert shown in Figure 10-5 is what you'll see when
you right-click on an existing cross-tab object and select Cross-Tab
Expert. It is very similar to the Cross-Tab Expert dialog box you see
when creating a new report using the Cross-Tab Report Wizard,
but not exact. The wizard's dialog box is missing a few key
buttons. The New Formula and Edit Formula buttons are not
available. Secondly, the Group Options and Change Summary
buttons are missing. The primary drawback to this is that the
Change Summary button lets you show a percentage of the overall
total in the summary field. The dialog box presented by the report
wizard box only let's you choose from the basic summary
functions. The Group Options button lets you customize the
sorting order of the groups. Thus, if you want to create new
formulas, have advanced summaries, or customize the groups,
then you have to wait till the wizard is finished and go back and
edit the cross-tab