Ohio University Computer Services Center

Ohio University Computer Services Center August, 2002

“Crystal Reports Introduction” Quick Reference Guide Open Crystal Reports From the Windows Start menu choose Programs and then Crystal Reports.

Creating a Blank Report

Once you have chosen to create a New Crystal Report document, the Data Explorer dialog box opens. If you are accessing a database, choose Data base files by either • Double-Clicking the Database Files Folder or • Clicking on the Beside the Database Files Folder Choose the correct database from the file in the same manner.

This is the window that will pop up automatically when Crystal Reports begins. Choose the option to create a blank report. Crystal Reports offers you 3 options at this time.

You now have access to the tables located within the database. Choose which tables you would like to use for your report.

Choose the As a Blank Report option and then click OK.

When a table has been selected correctly, a green checkmark will appear on the inserted table.

Choosing a Data Source

Click on Close in the Data Explorer window. You are ready to begin formatting your report.

The first thing you must do once you have created a report is to choose the data source.

Note: If your computer is already configured to access the OU data warehouse then it will show up under the ODBC (Open Database Connectivity) folder and can be found by clicking the Icon in front of the ODBC folder and choosing ORDJ.

Ohio University Technical Support 593-1222

1

Choosing Fields for Report Now that you have chosen your data source, it is time to design your report.

The Browse Field Data dialog box will display on the screen showing three things: 1. The actual data 2. The size of the data 3. The type of data

You should see the Field Explorer window on your screen.

Inserting Fields into a Report

To open the database you must either: • Double-Click the Database Fields or • Click on the beside the Database Fields

There are several options for inserting fields into a report. The options are: • Select the field you would like to insert then

Once the data base is opened you then • Click on the plus icon beside the database you would like to pull fields from. • Select the fields of data you wish to use in your report. Your database fields should look like this (Fields are circled):

• • •

•

Your report is divided into five sections. 1. The Report Header 2. The Page Header 3. The Details 4. The Page Footer 5. The Report Footer

Browsing Field Data Sometimes you may want to examine the size of the data within a field before actually inserting it into a report. To do this: • Select the field you would like to browse. •

Click the Browse Data icon

on the Field click the Insert Fields icon Explorer. Select the field, press the enter key, and then click in the report where you would like the field to appear. Double click on the field, and then click in the report where you would like the field to appear Drag the field to the report. When your pointer is located exactly where you would like the field to be placed, release the mouse button. Right click on the field and choose the option of Insert from the Short Cut Menu. Then, click where you would like to place the field in the report.

When you are inserting fields you typically want to place them in the Details section of the report. When a field has been successfully inserted into

.

2

your report the field will display with a green checkmark .

You may also choose the field with the size that you desire all of your selected fields to be and change all of your selected fields to that size. Crystal allows you to do this using the shortcut menu.

Once you have placed a field in the Details section, you will notice that Crystal Reports immediately places a field header in the Page Header section.

Do this by: • Selecting the fields you wish to resize • Right clicking on the field that has the size you desire all of your selected fields to be • Choose Size, and Same Width, Same Heights, or Same Size

Moving and Aligning Fields To move a single object you must: • Select the object you wish to move and position the mouse directly over the object until a four-headed arrow appears. • Left click the arrow and move the object to the desired location.

Resizing Report Fields

When moving objects one at a time, the fields in your report tend to get out of alignment with their titles. Crystal allows you to align multiple objects by using the shortcut menu.

Resizing fields is important in order to maximize the space on your report. You resize a field by: • Selecting the field you wish to resize, and sizing handles appear at the center of each of the four sides. • •

First you must select your objects by either: • Clicking the first object you wish to align and then pressing SHIFT or CTRL while you select the other object/s you wish to align. • Drag a marquee around the objects you wish to align. Make sure that your mouse pointer is not pointing to an object when you begin dragging. The marquee you drag selects any field that it touches or contains.

Move your mouse over a sizing handle and a double-headed arrow appears. Left-click on the arrow and resize the field to the size you desire.

When you resize a field located in the Details section of your report, you also want to ensure that the field header (located in the Page Header section) resizes with it.

Once you have selected the objects you wish to align • Right click on the field with the desired alignment. • Choose Align from the shortcut menu, and then choose to align on left, right, center, etc… Note: These are the same processes you use for resizing report fields.

You can select multiple objects to resize by either: • Clicking the first object you wish to resize and then pressing SHIFT or CTRL while you select the other object/s you wish to resize or • Drag a marquee around the objects you wish to resize. Make sure that your mouse pointer is not pointing to an object when you begin dragging. The marquee you drag selects any field that it touches or contains.

Viewing the Report Once you have placed information within the report you are able to view what it currently looks like. Viewing your report early on helps to determine how you will proceed in perfecting and

Once you have selected the multiple items, you will notice that the resizing handles appear on the item last selected. Resizing this item will resize all that you have selected.

3

enhancing the appearance of the report. To view your current report you must: • •

on the Select the Print Preview icon Standard Toolbar. A new tab is created called Preview.

•

Wait for your report to generate and view it!

Some reports may take several minutes to generate if the data fields are large. If you would like to stop your report from fully processing in order to quickly view a portion of it you can. Do this by: •

Clicking on the Print Preview prepare to view your report

•

Click on the Stop icon in the upper right hand corner of the report screen. Browse your report.

•

icon to

This will stop your report from processing further. Once you determine that the report is producing the information you desire and you want to go ahead and generate it fully you must: • •

•

in the Click the Refresh Data icon Standard toolbar. You will be given the following prompt:

Select OK to proceed with refreshing the data. Wait for your report to finish processing and browse the report.

4

Formatting objects within your report can be done by using the Format Editor.

Styling the Report Using the Report Style Expert The style expert contains many pre-formatted styles. To open the Report Style Expert you must either: • Choose Format/Report Style Expert from the Menu bar or •

Click on the Report Style Expert icon located in the Supplementary toolbar.

When you open the Report Style Expert, Crystal Reports warns you that it is not possible to undo the commands initiated by using the Report Style Expert. The warning appears below:

In order to open the Format Editor, you must right click on the object you wish to format and select, depending on what type of object you have selected, Format Field, Format Text, or Format Object. Whatever you have, the Format … option opens the Format Editor.

Note: Because of this, it is recommended that you save your document before you make changes using the Report Style Expert. If you do this, you can always revert to the original report. Click Yes and the Report Style Expert window appears.

The Format Editor dialog box presents several different formatting options depending on the type of field you are attempting to format.

Adding Lines and Boxes to Your Report Crystal allows you to precisely add a line or a box to your report. Add them by •

• The left column lists the style options you have to choose from. Select one and the image appears in the graphic on the right. Click OK to apply the style to the report.

or the Insert Clicking the Insert Line icon Box icon on the Supplementary Toolbar. Note: Your mouse pointer changes to the shape of a pencil. Draw the line or box with the mouse pointer.

Edit the line or box by right clicking on them and choosing the Format Line or Format Box option from the menu.

Inserting a Graphic into Your Report

Formatting Objects

Insert a graphic file into your report by:

5

• • •

The orientation section is in the lower right hand side of the Print Setup dialog box.

Clicking the Insert Picture icon on the Supplementary Toolbar. Choose the location of your graphic and click Open. Click on your report to determine the destination of your graphic.

Changing Margins Crystal, by default, sets the report margins to the narrowest possible settings available on the printer. To change report margins, chose File from the Menu bar, and Page Setup.

You may resize or move the graphic just as you would any object in the report.

Auto-Arranging the Report Auto-arranging the report does three specific things: 1. It trims the fields in your report to the shortest possible length. 2. It specifies a consistent amount of spacing between the fields. 3. It centers everything between the report margins. To apply auto-arranging to your report: •

•

Click on the Auto-Arrange icon in the Supplementary Toolbar. Note: Crystal warns you that this command cannot be undone (like the Style Expert Command). Click OK and the report is auto-arranged.

Type in the settings you desire and click OK.

Inserting Text Objects Insert text into your report by: •

Working with Page Commands Changing page Orientation

•

You can choose the page orientation (Portrait or Landscape) by clicking on File from your Menu bar and Printer Setup.

Clicking the Insert Text icon on the Standard Toolbar. Note: The text box appears at the end of your mouse pointer. Click the location in the report where you would like the text box to appear.

Inserting Special Fields Crystal Reports contains several special fields that are unrelated to fields within the database. The special fields included are the following:

6

• Special Field Page Number Total Page Count Page N of M Report Title Report Comments File Path and Name File Author File Creation Date Print Date Print Time Data Date Data Time Modification Date Record Number

Group Number Record Selection Formula

Group Selection Formula

Description Automatically prints number on each page Calculates and prints total report pages. Inserts page number with a page count (ex: Page 2 of 6). Places the report title as indicated in File/Summary Info. Places the report comments as indicated in File/Summary Info. Inserts the file path and name of the report Inserts the author as indicated in File/Summary info Inserts the date the report was created Takes the date from your PC’s calendar Takes the time from your PC’s clock Prints the date the data was last refreshed Prints the time the data was last refreshed Shows the date the report was last saved Assigns a number to each record in the report and acts as a counter field Allows numbering of each group in the group header or footer Inserts Record Selection formula as created in the Record Selection Formula Editor Inserts Group Selection Formula as created in the Group Selection Formula Editor.

•

To add a special field, open the Field Explorer by: •

Clicking the Insert Fields icon Standard Toolbar.

on the

7

beside the Special Click the plus icon Fields to see the fields. Insert the special field in the same manner that you would a database field (See Page two of this document).

Selecting Specific Records

Once your field is selected you must choose the conditions you would like to apply to the sort. The Select Expert initially displays the “is any value” operator. The operators included within the Crystal Reports Select Expert tool are the following:

Selecting Using a Single Criteria Filtering records allows you to limit the data in your report based on certain conditions. To filter records in Crystal Reports, you use the Select Expert. To open the Select Expert, you must either: • Choose the Select Expert command from the Report Menu, or •

Choose the Select Expert icon Standard Toolbar

from the

Operator

Definition

any value

Indicates that no conditions are applied to the data in this field. All records display in the data field. Choose a certain value and only those elements matching that value exactly display in the field. Displays records with one of several values. Displays records less than a certain value. Same as above but includes the stated value. Displays records greater than a certain value. Same as above but includes the stated value. Selects records that fall between two certain values. Selects records that begin with a certain character or set of characters. DOS characters like “?” or “*” can be used to select records with similar but not identical values. For Date or Date/Time fields, use this operator to determine if a date falls within a specified range. Allows you to create selection statements that are more complex than simple comparison operators.

equal to

If you do not have any fields selected in the report when you initially opened the Select Expert then the Choose Field dialog box will appear:

one of less than less than or equal to greater than greater than or equal to between starting with like

From here you must: • Select the field you would like to sort from • click OK and the Select Expert dialog box appears:

in the period

Formula

Note: Operators are specific to the type of field selected to filter on. The pull down menus for numeric or currency fields will only display numeric currency operators. Note: If you already have a field selected prior to opening the Select Expert, Crystal Reports assumes that is the field you would like to sort from and automatically takes you to the Select Expert dialog box.

8

Selecting Using Multiple Criteria Sometimes it is necessary to sort using more than one criterion. This can be done through the Select Expert by: • Clicking on the New tab or New button.

•

Note: The Show Formula button becomes a Hide Formula button once the selection formula is displayed.

Select the field you wish to include in the sort and click OK. This inserts another tab into the Select Expert dialog box.

When you choose to sort using more than one criterion, the Select Expert only chooses the data meeting all criteria by creating an “and statement” within the selection formula. If you wish to view records meeting one criteria or the other then it is necessary to change your selection formula to include an “or statement”.

Editing Selection Criteria Once you have created selection criteria you may need to change it for one reason or another. This can be done simply by: • Opening the Select Expert tool • Clicking on the tab containing the information you would like to edit. • Edit your data and click OK.

And Statement • Records must meet all criteria to be selected • Always gives the same number or fewer records than when sorting with a single criteria Or Statement • Records can meet any of the criteria to be selected • Always displays the same number or more records than when sorting with a single criteria

Note: Once you click OK, Crystal may prompt you with the following:

If the data you want is completely contained within the saved report, you can use saved data. Otherwise, you must refresh the data. If you are unsure, ALWAYS choose to refresh the data.

To change the criteria from an and to an or statement, you must: • Manually edit the formula by changing and to or • Click OK • Refresh your data. Note: Crystal asks you if you would like to use saved data or if you prefer to refresh your data. You must refresh your data to get the additional records you are asking for.

Viewing and Editing the Select Formula You can view the selection formula and edit it by clicking the Expert.

button in the Select

9

Using Formula Templates You may also choose to insert formulas into your report. This can be done by: • Clicking in the Report menu • Choosing the Edit Selection Formula option • Choose the Record option

The Crystal Reports Online Help should now be displayed. To access the formula templates from the Online Help you must:

The Record Selection Formula Editor opens. Formulas can be entered using the full range of report fields, functions, and operators. Crystal Reports has several formula templates built into the User Guide and Online Help file. These templates can be used as guides in creating your own formulas. Open the Crystal Reports Help by doing one of the following: • Pressing F1 key on your keyboard • Selecting Crystal Reports Help from the Help menu •

• • •

located in the Clicking on the Help icon Standard toolbar and clicking anywhere on the report.

Expand the Record Selection option Expand the Using formula templates option Expand Record selection formula Templates

You can select and copy any formula from these help screens into your selection formula using copy and paste commands.

10

Keep Group Together – Assures that page breaks will not separate groups. Use this option ONLY if your groups are small. If you turn this feature on and you have a group that is longer than one page, Crystal will move the entire group to the next page.

Grouping and Sorting Creating a Group Once you have determined that you would like to insert a group into your report, you must either: • •

Repeat Group Header – If you have large groups, you may want the group header to appear at the top of each new page to identify the group. This feature will do this automatically.

Press the Insert Group icon located on the Supplementary toolbar or Choose the group command in the Insert menu.

When a group is created Crystal adds two additional sections to the report. 1. A Group Header 2. A Group Footer Note: Objects in these sections print once at the beginning and end of each new group on the report. Crystal automatically adds the name of the group in the group header as well.

Once your group is created you can edit it by: • Right clicking on the gray area of the Group Header #1 section and • Choosing the Change Group option from the shortcut menu.

The Insert Group dialog box appears. This box gives you the option to choose the field in which you want to group as well as allowing you to define sorting and other options.

Creating Multiple Groups

There are four choices used to determine the sort order of your group. They are: 1. In ascending order 2. In descending order 3. In original order 4. In specified order

Crystal lets you create as many groups as you like within a report. Multiple groups are nested inside one another. Each successive group is contained completely in the group previous to it. Crystal numbers the Group Header and Footers to distinguish between the multiple groups.

In addition to the sort choices, there are two additional check boxes for the following features:

11

box, you can list the records in ascending or descending order on a specific field. Open the Record Sort Order dialog box by either: • •

Clicking the Sort Order icon located on the Standard toolbar, or Opening the Report menu and choosing Sort Records option

Using the Group Tree The easiest way to navigate through your groups is by the Group Tree feature.

The Record Sort Order dialog box opens. To sort on a field, select it from the left list, and then click button. The order in which you the add fields is the order in which they will be sorted. If you would like to delete a field the select it and click the The Group Tree appears at the left hand side of the screen in the preview window. It works like a directory tree, showing a heading from each group in the report. If a group contains another group, a appears before the group. Clicking plus icon on this will show the subgroups.

button.

Summarizing Group Data Once you have grouped and sorted the records within your report, you may wish to insert summary data. Inserting summaries is a very helpful tool. Summary information displays automatically in the Group Header of the report and can perform numerous calculations.

When you see the group you want to view in the report, click the group name in the Group Tree. Crystal then displays the chosen group. Using this makes it easier to maneuver around a large report.

Summaries can be performed on any type of data. On string data, summaries are limited to: • Counting • Selecting the frequency

Reordering Groups It may be necessary to change the order of your groups once they are created. To change the order of your groups you must: • Return to Design View • Click on the Group Header or Footer • Drag the Group to the location you wish

On numeric data, summaries can calculate: • Sums • Averages • Minimums • Maximums • Counts • Statistical Analyses

Sorting Records within a Group

Crystal only gives choices that are appropriate for the type of data you are summarizing.

If a sort order is not specified, Crystal displays the records in the order in which they appear in the database. Using the Record Sort Order dialog

12

To summarize the report (after the report has been grouped) you must do one of the following: • • •

Click the Insert Summary icon located in the Standard toolbar. Right click on the field (within the Details region) and choose Insert Summary from the submenu. Select the field you wish to summarize and then click on the Insert menu and choose Summary.

The Insert Summary dialog box will appear. Crystal does not allow you to choose the field on which to summarize from the Insert Summary dialog box. You must select the field before you choose to insert a summary. Note: Summaries are based on groups. If you insert a summary without first creating a group then Crystal will automatically insert a group into your report.

13

the field on which to group and Crystal puts all records with the same value in that group.

Glossary Absolute Formatting – The data field formatting option that can only be turned on or off based on the presence or absence of ad attribute.

Group Header and Footer – The Group Header and Footer are sections that appear at the beginning and end of each group in the report.

Between – An option in the Select Expert box. Choose records that fall between certain values.

If Then Else – These formulas describe conditions that control the display of data. These formulas are helpful to bring attention to high or low performance. Example: If last year’s enrollment was less that 1000 Then make the value bold and italicized, Else (otherwise) make no changes.

Boolean Expression – A Boolean expression is an expression that defines a logical relationship between two or more items. This type of expression always results in a TRUE or FALSE, YES or NO answer.

Keep Together – This option refers to keeping a section together in relation to page breaks. If there isn’t enough page left to print the section, then rather than having the page break occur in the middle of the section, the entire section is carried over to the next page.

Check Boxes – Boxes found in many formatting and editing options that allow the user to turn a feature ON or OFF. Conditional Formatting – Conditional Formatting is formatting that applies only if certain situations occur. Example: Formatting a numerical field in red if it is negative.

Last Modification Date/Time – Shows the date/time when the report was last modified based on the PC’s clock.

Data Source - A particular set of tables, queries, dictionaries, or stored procedures that provide data for a report.

Null – There is no data within the cell, not that the data value is zero. Operators – Symbols that describe an operation or an action to take place between two or more values. Operators are + - * and / < > = etc… Syntax rules allow you to place a space before and after an operator if you wish.

Data Date – Prints the date that the data in the report was last refreshed. Data Time – Prints the time the report data was last refreshed.

Order of Precedence – A set of rules that determines the order in which arithmetic operations are performed within a formula. Note: Multiplication and Division before Addition and Subtraction.

Data Type – A data type is a classification of data that appears in a field or formula. Data is one of the following types: string, currency, number, data, date/time, time, or Boolean. Details Section - Details contains a row for each record returned from the database.

Page Header and Footer – These sections define items that will appear at the top and bottom of each report page.

Export – Exporting is to distribute the report to a disk file or through email to a different format.

Print Date/Time – This inserts the date/time you are printing the report, based on your PC clock.

Fields – Fields are accumulations of data about a particular subject stored within a database table. Fields are selected and placed on reports in a variety of ways. In formula creation, fields are always shown within {curly braces}.

Report Header and Footer – These sections define items that will appear at the beginning and end of the report.

Group – A group is a set of records that are related to each other in some way. You specify

Report Gallery – A dialog box that appears when you begin to create a new report that allows you to define how you are going to create the report.

14

Sections - The sections of your report are the names seen in the Design Screen on the left side of the window. Report Header, Page header, Details, report Footer, and Page Footer. Select Expert – Allows you to determine how data will be displayed on your report. Strings – A series of textual characters that are letters, numbers, or special characters. Summary Fields – A summary field determines the sum, average, minimum, maximum, or count of values in a group. Summaries are based on a field within the details section of the report. Suppress – Found within the Section Expert. Suppress works like a hide feature but the section is permanently suppressed and drill-down capabilities are not allowed. TopN – A function allowing users to make the report display only the highest or lowest values. Total Page Count – Calculates and prints the total pages in the report. ToText – A versatile function often used to convert a date value into text.

15

Standard Toolbar Buttons

Section Expert Select Expert

The Standard toolbar contains icons for basic editing commands.

Sort Order

Create a New Report

Insert Chart

Open a Report

Insert map

Save

Find

Print

Zoom Control

Print Preview

Help

Formatting Toolbar

Export Refresh

The Formatting toolbar contains various formatting commands. Most of these icons are not active unless you have a specific object selected. (Example: The Currency icon will only be active on numeric fields)

Cut Copy Paste

Font Face Undo Font Size Redo Increase Font Size Insert Hyperlink Decrease Font Size Insert Field Italics Insert Text Object Underline Insert Summary Align Left Report Expert Align Center

16

Align Right

TopN Expert

Currency

Object Properties

Thousands Separator

Link Expert

Percent Increase Indent Decrease Indent Highlighting Toggle Group Tree Toggle Supplementary Toolbar

Supplementary Toolbar

The Supplementary toolbar displays at the bottom of your Crystal Reports screen, and can be activated by clicking the Toggle Supplementary toolbar icon

on the Formatting toolbar.

Insert Line Insert Box Insert Picture Auto-Arrange Report Insert Group Insert Subreport Insert Cross-Tab

17