Be a Whiz and Take a Quiz Crystal Report Creation Example

Workshop Overview

This two session workshop is designed for the beginner to intermediate Seagate Crystal Reports user who needs to become proficient in creating and modifying reports. Topics include, database basics, creating a basic report, record selection, sorting, grouping and summarizing, basic queries, exporting to other formats, and formulas.

Who should attend
Blackbaud product users who need to become proficient in creating and modifying reports within our organization.

Prerequisites
Attendees must have a working knowledge of Windows and basic knowledge of database concepts such as tables, fields and records.

Crystal Reports is a report writer software program owned by Seagate.

Blackbaud, under agreement with Seagate, bundles Crystal Reports with their database programs.

Version 7 is the most recent version of Crystal Reports sold by Seagate. Blackbaud currently bundles version 6 of Crystal Reports with their products. Blackbaud’s Registrar’s Office and Raiser’s Edge products use the 16 bit version of Crystal Reports while the Accounting for Nonprofites uses the 32 bit version.

Crystal Reports is an industry standard report writing tool.

A database is a collection of related information or data.

Every electronic database stored in a computer has a specific file name, for example stdinformation.mdb.

A database consists of a single or multiple, related tables. Every table in a database has its own name, for example tblAcademics.

Every table consists of several fields.  Each field has its own unique name, for example strFirstName, strLastName, intAge, dtmEnrollmentDate, curTuition, etc.

Every database field has at least the following attributes:

1. Length in characters.
2. Type of data that the field will store.

The basic field types are:

1. String (text and digits not participating in mathematical calculations)
2. Number
3. Currency
4. Date and Time
5. Boolean (Yes/No, True/False)

A record consists of all the information entered in fields for one item in a database. When you enter information in a database you create or update records.

Multiple tables in the same database are joined (related) together by using a common field on every table. This common field is called the primary key field. The primary key must be unique for each record and must contain identical information for each record in every table. An example of a primary key will be the student ID field.

Tables in databases are usually sorted (ordered) in some fashion. For example a list of names appearing in alphabetical order by last name.

A query is a database mechanism that allows you to locate and extract from a database only the information you need (a subset of the data). This way you can view specific fields and records from one or more tables that fulfill certain criteria you set. A query can be saved with the database for future use.

Internally a database software program saves information in a way that cannot be read by humans (in binary form) or by other database programs, except the one that created it. In order to share databases with other programs, you need to create an export file from the source database program in a specific file format that matches the format of the targeted program. For example create an export from Registrar’s Office (source) to be imported into Crystal Reports (target).

In order for your export to be successful, you need to know the file formats the target program accepts (import) and the type of file formats your source program can create (export) and match them.

In most cases you do not want to export an entire database to another program. Instead you create a query that contains only the records and fields of you choice and then you assemble the results of your query to an export file.

Technical Note: All Blackbaud databases in use at Chaminade are SQL (Structured Query Language) databases. Blackbaud password protects the SQL databases in order to protect the data from accidental corruption due to user actions. In other words, you cannot open directly a SQL database using Crystal Reports or issue SQL commands. Blackbaud with all their software provides the Query modules in order  to facilitate record selection, and the Import/Export modules for data exchange.

About Report Writers

Reports in general are management tools that help us quickly grasp the essential elements and relationships found in data in order to make effective decisions. An effective report must present the right data in a logical and organized manner.

A report writer such as Crystal Reports, is a software tool that enables you to create presentable and meaningful reports by using data from a database. In a report writer you can specify the fields you want included and organize the data by grouping and sorting the records. You can also include a variety of calculation on your data, graphs, pictures, etc. Report writers employee similar techniques and tools with desktop publishing and spreadsheet software.

Some common characteristics of report writers:

• They only read information from a database.
• They cannot write information back to the database.
• They organize and format information in order to output it on a monitor or printer.
• They need records from a database in order to function in a meaningful way.

In a typical report most of the data will originate directly from database fields. It might also include:

1. Data produced from calculations on existing database fields.
2. Data placed directly on the report using text objects such as titles, labels, graphs, etc.

Sample Data

A formula is a symbolic statement that manipulates the data in a report.

A formula consists of database fields, operators indicating the type of calculation to be performed, and functions that work as hybrid operators.

Formulas allow you to put data on a report that does not exist in any of the data fields.

Every formula you create needs a unique name. Crystal Reports uses the formula name as the field header in the report. All formulas inserted into the design view of a report have an @ sign in front of them (remember that Parameter Fields have a ? preceding them).

In a formula you can use any field from any table and, if you have created groups in your report, they can also be used in formulas as well as any group subtotals or summaries.

Crystal Reports always checks the validity of your formulas. If it finds a syntax error, it gives you the option to make corrections. The fact that Crystal Reports does not indicate any syntax errors in a formula does not necessarily indicate that your formula will produce the desired results.

Crystal Reports ignores uppercase or lowercase as well as carriage returns in formulas.

Crystal Reports treats any formula as an object, and wherever you place the formula in the report is where the results of the formula are displayed.

• Fields: {tblDemographics.strLastName}
• Numbers: (1, 2, 3.24, 3/5).

Note: When using numbers in formulas, do not enter any commas or currency symbols.

• Text strings: "Honors", "Student is in danger of failing the class."

Note: All literal text must be enclosed in single or double quotes.

• Operators: ( +, -, *, / )

Note: A formula can use a variety of operators such as arithmetic, logical, comparison, conditional, etc.

• Functions: TrimRight(x), Length(x).

Note: You need to supply the appropriate arguments inside the parenthesis of every function you use.

• Group Field Values: Average(fld,condFld)
• Other Formulas: @9thGradeTotal

• { } French or curly brackets surround database tables, formulas and parameter fields: {tblDemographics.strLastName}, {@TotalTuition}, {?Grade}

• ( ) Parenthesis are placed around the arguments of a function:

Round(x, # places)

• [ ] Square brackets are used to enclose the subscript of an array of values:
  {tblDemographics.strLastName}[1]

 Parentheses are also used to control the order in which formula elements are evaluated (order of precedence).

Order of Precedence or how formulas are evaluated:

Order of precedence for formula calculations follows basic math rules.

• Multiplication and division are performed first from left to right, then addition and subtraction.
• Parenthesis have a higher precedence that any arithmetic operation and redirect the order of calculation.
• When a formula contains another formula, the embedded formula is evaluated first.

• Creation of calculated fields for a report,
  {tblFinance.intTotalTuition}/10
  Calculates the monthly tuition.

• Formatting report text,
  UpperCase({tblDemographics.strLastName})
  Changes all characters of a student’s last name to uppercase.

• Extracting a portion of a text string,
  {tblDemographics.strLastName}[1]
  Extracts the first letter of a student’s last name.

• Extracting a portion of a date field,
  Month({tblFinance.dEnrollment})
  Finds the month of a student’s enrollment date.

You may add comments or reminders to your formulas by preceding them by two back-slashes. For example:
//This formula produces the total projected school enrollment for the year 2002.

Functions

All functions consist of the name of the function followed by a pair of parenthesis. Inside the parenthesis you put the necessary arguments.

For example: SUM(field1,field2).

Crystal Reports provide you with a variety of pre-made functions for your use when you manipulate numbers, strings, date/time, arrays, etc. Be aware that functions can have the same name but different number and type of arguments (polymorphism).

For example: Average(field), or Average(field, condFld), or Average(field, condFld, cond), or Average(x).

The If-Then-Else conditional formulas

You use the if –then- else type of formulas to compare values and, depending on the results of the comparison, execute one or more alternatives.

Both actions that may be taken after evaluating the condition must be the same type of action. In other words, if the result of the Then is to print a string, the option for the Else must be a string also.

If the condition fails the if-then test, 0(zero) is returned for numerics and "" (null string) for text.

Examples:

if {tblDemographics.intGrade}=11 then "Junior" else "Senior"

Note: Remember that there is no such thing as an empty or null value for the Time or DateTime data types.

Boolean formulas

A Boolean formula returns one of two possible answers: True or False.

In Crystal Reports you usually use Boolean formulas as a basis for another action, such as record selection or conditional formatting.

For example the Boolean formula {tblDemographics.strSex}= "F" will evaluate to either true or false.

Although we can use the Select Expert to select records in a report, if the records are not all typed with the same case (West Hills versus West hills), the Select Expert will fail to give us accurate record results because it is case sensitive.

In the above example we could use the following Boolean formula:

UpperCase ({tblDemographics.strCity}) = "WEST HILLS"

This way you will be sure that all cities will be changes into upper case before evaluation.

 The Help menu of Crystal Reports contains extensive help on functions. It also contains a detailed Functions Index in alphabetical order. You can find the necessary formula examples or functions there and copy/paste and modify for use in your reports. You can also copy a formula from one report and paste it to another.

Conditional Formatting

Crystal Reports can evaluate fields and, based on the result, format fields in different formats. For example you can have every negative number on your report print in red color.

Wherever you see a button with the icon of a pencil and x-2 (Conditional Formatting button), you have the opportunity to add conditional formatting to that particular portion of the report.

For example: if{tblDemographics.strSex}="M" then Blue else Pink

Home Page Back to M.I.S.

Last Updated: October 31, 2005
Yannis Grammatis