- Shopping Bag ( 0 items )
Now that Crystal Reports is sold as a stand-alone product for Windows, Mac, and UNIX ...
Now that Crystal Reports is sold as a stand-alone product for Windows, Mac, and UNIX users, you need a copy of Crystal Reports 6 For Dummies at hand to convert your drab-looking databases into stunning, professional-looking reports smoothly. Here are all the tips and tricks you need in order to format, sort, group, and print database records to company reports or Web sites, plus tried-and-true techniques for adding pictures, logos, and OLE objects to jazz up your reports.
Even Seagate Executive Vice President Greg Kerfoot agrees that "this is the book Seagate Crystal Reports users everywhere have been waiting for!"
In This Chapter
Your boss comes in to your office in a rush stating that she needs asales by salesperson report right away! Now what do you do? Remain calm. You need to ask the person, boss or not, requesting a report the following ten questions. Ten is not a magic number, but Dan Gookin made the Part of Tens famous in Dummies books, so here I go.
Is the person requesting this report allowed to access the data? Does the report contain any confidential information?
While writing down the purpose of the report may seem like a useless exercise, it is very important. Doing so allows you to focus on what should or shouldn't be in the report and how the information should be displayed. This question also allows report recipients to know if this is the report that they want to see or if they should go look for another one.
Anytime you write a goal or purpose, you know where you are going with the report. If you don't know where you are going, how do you know if you have arrived?
More and more, companies and institutions are careful about security issues, such as who sees what data. In a hospital, a corrections facility, a school, or a business, some information may not be available to all people. In a company, a person may have to have a security clearance to see certain data. Some financial data may only be distributed to certain people. Some information may be company private or proprietary. Keep these security issues in mind when you create a report.From what databases, views, or tables do you need to include information in this report, and what fields do you want to include in the report?
Data for a report may come from different sources. Record the databases, tables, or views that hold that data you need. You may need to know the exact directories that hold information. You may need to know what network to access. You may need a password to obtain access to some data.
If you have more than one table involved, linking will be needed.
Women only? Over 60 years old? Transactions for March?
Many reports are run on a monthly, quarterly, or yearly basis. You need to know this information before you create your report. If you don't know, you may be in a position where you are waiting for 15 minutes for a report preview to display because you are looking at data for the last ten years. Whether you are looking for data from a certain region, a certain gender, or a certain time period, you're better off if you can reduce the number of records in your report. The Select Expert can be helpful to you.
Find out if the data should be grouped by region, by date, or by alphabet. Some reports may have several groups. A report could be grouped by state, then by gender, and then by age.
Usually the two choices are ascending order or descending order. In addition you will want to know if the data should be sorted in ascending or descending order by amount, alphabetically, or some other criteria. As with groups, you may sort alphabetically by state and then sort by amount within those groups.
Another feature of Crystal Reports is that you can create your own style of grouping called custom groups. For example, if you want to group regions by their geographic location, feel free. For more information on this feature, please see the online help.
Monthly totals? Grand totals? Averages?
Find out if you need to count the records in the report, get a running total, add summaries at the end of the groups, and other information. Consider whether you want summaries after every group.
Record the text you want to display in each of these report sections. Decide on which pages you want this text to display. You may want to display some text on the first page, but not on the others. Find out if any other text is required. You may need to describe some of the summary calculations. You may need to add some quotes or expressions.
You can make data stand out by using flags, special formatting, or conditional formatting. If the person for whom you're creating the report wants the data to stand out, find out how.
This step is for the more sophisticated presentation quality report. Will the report be printed? Will the report be distributed on a network? Do you have a color printer? You can make totals greater than 10,000 appear in red. If you are presenting in black-and-white, then you want to make totals greater than 10,000 appear in reverse image. Based on the report's purpose you can make the critical data stand out.
You may have a network, where all the reports are distributed to an Exchange folder or to Lotus Notes. You may want reports uploaded automatically to a company Intranet page. Perhaps you are distributing a report to a group who does not have access to Crystal Reports. Find out if you want to send them a compiled report or if you need to export the report so it can be read by another software product. Table 16-1 gives you some ideas for report format and distribution.
Table 16-1 Report Distribution or Showing Your Brilliance to the World!
|Crystal Report*||Exchange Folder|
|Compiled Report**||Uploaded to HTML (Web site)|
|An Existing Application|
* Receiver has access to Crystal Reports.
** Receiver does not have access to Crystal Reports.
Again, the security issue raises its head. Can anybody see the information in this report, or is it company private, proprietary, or confidential? You're better off to find this information out before you distribute the report.
You should get into the habit of finding out the due date for activities assigned to you. The person requesting the report may want to see and approve the report before you send it out. Find out if the report has a critical deadline.
Before you start creating the new report, draw a picture of the report with all the titles, columns, groups, and so on. Get the drawing approved. Then you will have a guideline made for creating the report. Here is how you might make the report look:
If you follow these steps, even in an oblique way, the stress and strain of creating reports is greatly reduced...
- How to Use This Book
- If You Have Used Crystal Reports Before
- What Is in This Book
- Part I: What You Need to Know to Survive
- Part II: Manipulating Records
- Part III: Formatting and Formulas for Success
- Part IV: Putting On Some Finishing Touches
- Part V: Creating Specific Types of Reports
- Part VI: Disseminating Reports without a Hitch
- Part VII: The Part of Tens
- Icons Used in This Book
- Off You Go!
- Chapter 1: Setting the Table
- In the Beginning, a Table
- Form Follows Function
- Table for . . . One or More?
- Plan the Report
- What makes a report?
- Report distribution
- Chapter 2: Creating a Simple Report
- Starting Crystal Reports
- The Report Design Tab
- Inserting Data Fields
- Taking a Preview
- Inserting a Second Field
- Browsing Data
- Changing the field length in the Design Tab
- Changing field length in the Preview Tab
- Moving a Field in the Preview Window
- Using a Guideline to Position Objects
- Aligning a Field Name with a Field
- Using Guidelines in Preview
- Other guidelines using grids
- Turning on the grid
- Using the Report Gallery Experts
- Stepping out with an Expert: A Standard Report
- Why you should use an Expert
- Chapter 3: Crystal Reports: Basic Skills
- Opening a Saved Report
- Inserting Text Objects
- Adding a text object
- Previewing the text object
- Editing a text object
- Adding a border and drop shadow to the title
- Adding other fields
- Moving a field in a report
- Adding a Number Field
- Aligning Columns and Headers
- Getting the Numbers Formatted
- Moving from Page to Page in a Report
- Magnifying the Page
- Chapter 4: Selecting Records
- What Is Selecting Records?
- The Select Expert
- Saved or refreshed data
- Refreshing the report on demand
- Select Expert options
- Selecting records on more than one field
- Removing Record Selection
- Chapter 5: Sorting and Grouping Records
- Sorting Records
- Breaking Ties
- Inserting Groups
- Viewing Groups in the Design Window
- Deleting or undoing a group
- Deleting a group
- Inserting a Total
- Inserting a numeric grand total
- Inserting a grand total for text
- Defining other calculations
- Changing a Group
- Doing a TopN Sort
- Chapter 6: Graphing Data
- Creating and Inserting a Graph
- Creating a Group Graph
- Modifying a Graph
- Moving the graph to the header (no change)
- Moving the graph
- Resizing a graph
- Adding a border
- Customizing your graph with PGEditor
- Using the PGEditor (Crystal Chart)
- Changing graph colors
- Changing a font size
- Detaching a slice of pie
- Undoing changes
- Saving the pie chart
- Deleting a graph
- Drilling Down on a Graph
- Adding a special effect
- Changing the Graph Type
- Applying the graph gallery
- Looking at graph examples
- Using a Graph Template
- Saving a graph as a template
- Applying the template
- Applying Graph Templates Stored in PGEditor
- Chapter 7: Using the Crystal Formula Language
- What Is a Formula?
- Acquainting Yourself with the Parts of a Formula
- Opening the Formula Editor
- The Fields box
- The Functions box
- The Operators box
- The Formula text box
- The Select button
- The Check button
- The Accept button
- The Browse Field Data button -- for accuracy
- Syntax 101
- Going Down the Road to Creating a Formula
- Eliminating Blank Records
- Creating a Formula
- Check please!
- Accepting the formula
- Editing a Formula
- Adding a Formula That Totals by Group
- Adding a Formula That Calculates a Percentage of Total
- Working with Text Strings
- Changing Numbers to Words
- Going on a Date
- Using If-Then-Else Formulas
- Modifying an if-then-else formula
- Nested if-then-else formulas
- Understanding Boolean Formulas
- Creating a Record Selection Formula
- Chapter 8: Using Conditional Formatting
- Absolute versus Conditional Formatting
- Using On or Off Properties
- Using Attribute Properties
- Another conditional format
- Still another conditional format
- Deleting a conditional format
- Chapter 9: Formatting Sections of a Report
- Changing the Size of a Section
- Automatically sizing a section
- Looking at the shortcut menu
- Formatting Sections with the Section Expert
- Using the Common tab
- Free-Form Placement
- Hide (Drill-Down Okay)
- Suppress (No Drill-Down)
- Print at Bottom of Page
- New Page Before
- New Page After
- Reset Page Number After
- Keep Together
- Suppress Blank Section
- Underlay Following Sections
- Format Groups with multiple column
- Using the Color tab
- An example of a conditional format formula
- A conditional formula to color group results
- Chapter 10: Creating Presentation-Quality Reports
- Quickly Formatting a Report
- Zooming In on the Report
- Working with Special Fields
- Undo command
- Special fields defined
- Inserting a Special Field
- Adding a Record Number field
- Adding information to the Report Footer
- Formatting Special fields
- Formatting a Date field
- Combining a text object with a Special field
- Inserting Lines and Boxes
- Formatting a line
- Boxing records in a group
- Adding a drop shadow to the title
- Drawing a Box around an Object
- Text Objects Extra
- Adding the Can Grow option
- Editing text within a text object
- Inserting a Picture or Logo into the Report
- Inserting an OLE Object
- Using Auto Arrange to Format Reports
- Chapter 11: Creating a Cross-Tab Report
- Identifying the "By" Word
- Creating a Cross-Tab Object in a New Report
- Inserting a Cross-Tab into an Existing Report
- Removing the grid
- Adding a second summary field to the example Cross-Tab
- Creating a Cross-Tab using the Cross-Tab Expert
- Chapter 12: Creating a Summary Report
- Creating a Summary Report
- To Drill or Not to Drill, That Is the Question
- Chapter 13: Linking to Other Databases
- Linking Concepts
- Normalizing a database
- Keying primarily
- Working with Links
- Moving a table
- Looking at field properties
- Browsing through the fields
- Closing the Table Description dialog box
- Looking at the links
- Exploring the Visual Linking Expert buttons
- Using link options
- Creating a new report
- Creating links
- Deleting a linked table
- Adding tables in the Visual Linking Expert dialog box
- Creating the report
- Using SQL Joins
- Adding tables via ODBC
- Using SQL join types
- Using the equal join
- Using a left outer join
- Using a right outer join
- Using a greater join or greater or equal join
- Using a less join or a less or equal join
- Using a not equal join
- Chapter 14: Distributing Reports
- Understanding an Export File
- Exporting Reports
- Choosing a file format
- Choosing the report destination
- Exporting a report with saved data
- Opening a saved report
- Exporting to a Lotus Notes Database
- Exporting to an Exchange Folder
- Exporting to Excel format
- Exporting to Microsoft Word
- Canceling an export
- Comparing the Word Document with Crystal Reports
- Exporting to an ODBC data source
- Setting up an ODBC Data Source
- Mailing Your Report
- Faxing Your Report
- Compiling a Crystal Report
- Using the Report Distribution Expert
- Printing a Compiled Report
- Web Reporting
- Exporting to HTML
- Checking your Web page
- Chapter 15: Setting Your File Options
- Environment Settings: The File Options Dialog Box
- The Layout Tab
- View Options
- Grid Options
- Preview Pages
- Field Options
- Free-Form Placement
- The New Report Tab
- The Fields Tab
- The Fonts Tab
- The Reporting Tab
- The Database Tab
- The SQL Tab
- Chapter 16: Ten Questions to Ask before You Create a Report
- What is the purpose of the report?
- From what databases, views, or tables do you need to include information in this report, and what fields do you want to include in the report?
- Do you want all the records in the report or a subset?
- How do you want the data grouped?
- How do you want the data sorted?
- What summary calculations do you want in the report?
- What text do you want to appear in the:
- Do you want certain data to stand out?
- How should the report be distributed and to whom?
- When do you need to see this report, and when should it be distributed?
- Is this how the report should look?
- Chapter 17: Ten Tricks to Enhance Reports
- Use a predictable format
- Allow generous white space
- Position report headings and page numbers in the same place for every report
- Make data easy to understand
- Place and align columns appropriately
- Keep columns consistent
- Use column headers strategically
- Visually group data
- Add graphs to make your reports more descriptive
- Add graphics to your report to make it visually interesting
In This Chapter
When you create a report, you may or may not want every record in the database to be in the report. You can limit the number of records, and you can specify which records to include. This chapter gives you the lowdown on selecting records.
When you are generating a report, you have to access an existing table in order to create the report. A problem arises when the table consists of thousands of records. You may want a report that consists of records from a specific geographic area, records from a certain sales division, or records only of the products in which you are interested. That is the primary reason to use record selection. This load is not so noticeable in the Design Tab, but a large number of records greatly affects performance in the Preview Tab. The folks at Seagate Software anticipated your needs and built in a way for you to select only a few records in order to design the report or to have only the records that fit the report criteria.
The Select Expert is a tool that walks you through the process of selecting the records you want to include in the report. Think of the process as filtering the data in the field. If the data is of a certain size, it passes through the filter to be included in the report. If not, it is not included in the report.
Open the Select Expert by clicking the Select Expert button on the toolbar. You can also open the Select Expert by choosing Report-->Select Expert. The Choose Field dialog box then appears, as in Figure 4-1.
If you have a database field selected prior to clicking the Select Expert, you bypass the Choose Field database and go directly to the Select Expert by using the field you highlighted.
Notice two things in this dialog box. First, because I have a report open on the screen, Crystal Reports lists the fields that are part of the report, as possible candidates for record selection. Second, Crystal Reports also displays field names from the source database table, with good reason. The record selection process is not restricted to the fields in the report. You can use any field from the table as the filter. So even though your report may include the fields you want, you can restrict the records included using an entirely different field. Most of the time, though, you use a field that is in the report as the record filter.
In the following example, I have opened a report that includes a field that has sales numbers. I am going to use that field to restrict which records are included in the report.
To use the Select Expert:
If you had a database field selected prior to clicking the Select Expert, you bypass the Choose Field database and go directly to the Select Expert by using the field that you highlighted.
The Select Expert dialog box appears.
Crystal Reports asks you whether you want to use saved data or refreshed data. In most cases, select refreshed -- which means that Crystal Reports rereads the data in the table you are using for the report.
Those are the basic steps. Now see what happens to a report when you use the Select Expert. In my report, which includes four fields, one is named Last Year's Sales. Say that you want to restrict the records to those that have a number greater than $50,000 in Last Year's Sales. Here are the steps:
In my case, I have opened the report named ch3.
The Select Expert dialog box opens, as shown in Figure 4-2.
Browsing data here lets you double-check that this is the field on which you'd like to base your record selection. Press Done when finished.
So looking at the next drop-down list, find the option to make the selection work. Figure 4-4 shows the drop-down list opened.
greater thansyntax, which is inserted into the field.
At this point, the criteria reads
LAST YEARS SALES IS GREATER THAN.
Note: If you select the greater than or less than option, notice you get a check box
or equal to. When this is selected, you will also include on your report all records that match the value. In this example, when you turn on the
or equal to check box, you include on the report records that have Last Year's Sales amount of exactly $50,000 or higher.
So far so good. When you enter the
greater than syntax, Crystal Reports opens an edit box to the right. This box is for entering a number, completing the criteria. Crystal Reports allows you to take a look at the data again: Click the down arrow at the far right of the edit box. (This method is the alternative way of getting browse data.) The data appears, as in Figure 4-5.
The final step is to enter the number. You have two ways to do so.
In this example, no number is exactly 50,000, so I type the value. In Figure 4-6, I have entered 50000 into the field. Note that you should not include a comma as a separator. Crystal Reports does not interpret the comma as part of a number. Notice that Crystal Reports adds the cents!
The criteria reads
LAST YEAR'S SALES IS GREATER THAN 50000. The subject-verb agreement may be suspect, but the criteria syntax is correct.
Crystal Reports pops up a dialog box that asks an important question. In a moment, I explain the reason for what I tell you to do here. The dialog box you see asks whether the report should use the current set of saved records, or refresh the data. Figure 4-7 shows this dialog box.
If you are in the Preview Tab, pressing Refresh does just that and no further input is required from the user, but if you are in the Design Tab, we don't ask you if you want to save or refresh until you ask to go to the Preview Tab.
Figure 4-8 shows the new, highly filtered report.
In order not to burden the computer system where your data resides and to speed up report creation, Crystal Reports only reads the data from the tables you have included in a report when necessary. Now, on your home computer, you may not think that this feature would be a big deal. It probably is not. However, in a setting in which you are creating reports from a shared database, such as a network with SQL servers, having Crystal Reports reading the shared database at every turn would slow your report-building process.
So why does Crystal Reports sometimes ask the immortal question: Use saved data? Crystal Reports has some built in smarts and won't bug you for an answer to this question when it knows it is necessary to get more data for the report by going back to the database. It is only when Crystal Reports is not sure if rereading the database is necessary that it asks you to decide. And how do you determine how to answer the question?
If you are narrowing your record selection, click Use Saved Data. For example, perhaps when you first built your record selection you included all records with sales greater than 0. If you change the record selection to sales greater than 50,000, you can select Use Saved Data because you are narrowing the record selection.
If you are not positive that you are narrowing the record selection, click Refresh Data just to be safe.
Crystal Reports considers printing to be of any kind: Print previewing, actual printing to hard copy, or saving the report to a file.
Because Crystal Reports saves the records with the report, and time may pass between the time you create the report and the time you plan to use it again, Crystal Reports provides a button to cause it to re-read the records for the report. To refresh report data, do one of the following:
Crystal Reports provides an indicator for you so that you know exactly when you last refreshed your report data. In Figure 4-10, you can see the numbers that indicate the most recent update.
Record selection and case sensitivity
Record selection is case sensitive. What does this mean for your reports? If the abbreviation for California is entered as CA, ca, or Ca in your database, what records would be included in your report if your record selection is Equal to "CA"? The report would only include those records where the Region is exactly "CA" and won't include any other version (ca, Ca, or cA). This is one reason you may want to use the Browse Field Data button in the Select Expert -- to find out how the data is stored.
When working with SQL/ODBC data, you have the ability to select whether or not you want record selection to be case sensitive or insensitive. By default the option is not selected and your SQL/ODBC record selection will be case sensitive. The Case-Insensitive SQL Data option (available under File-->Options-->Database or from File-->Report Options) is where you set whether you want a case-sensitive or case-insensitive record selection. This option will only be available when your database server supports case insensitivity.
If the report was refreshed several weeks ago, Crystal Reports displays the date and time of the last refresh exactly.
Now that you have an idea of how the filtering process works, Table 4-1 presents other ways in which you can filter records. Remember, you do not have to use a field that is in the current report; any field in the database table works.
Table 4-1 Filtering Parameters
|What It Does|
||When you see this option, you have no record selection for the field. It's the same as saying, "Give me all records on the report, I don't care about the sales amount."|
||Filters records so that only an exact match passes through. For example, if you want to see only records that are from California, the field data is equal to CA (assuming the name for California is entered as an abbreviation).|
||Allows you to specify records that match values from a series. So you can enter
||Allows you to filter records in which the field value is less than a value you choose, or greater than a value of your choice. So you can use this filter to cut off records that are at the extremes.|
||With either the greater than or less than, you will get a check box where you can determine if you want to include the equal to value itself on the report.|
||Selects those records that fall into a range that you want. You can enter between 20,000 and 75,000 to get only those records.|
||Selects records using a text field. So if you want all records that have a field entry beginning with the letter S, this is the filter to use. Further, if you want to find records that begin with SON, you can do so by entering the three characters.|
||Although the world now sees computers through Windows 95, in the background are still ways to use tricks that are from the DOS era. For example, the entry D*G (an asterisk) filters records that have any entries in the field that begin with D and end with G. So the words DOG, DOUG, DARING, and DECIDING all pass through this filter. The asterisk is called a wildcard character because it matches any character and any number of characters. This works with text data only. Another type of wildcard is the "?" which is a question mark. It will match any character, but only one at a time. So, the D?G filter would retrieve records like DOG, DIG. The word DOING would not be retrieved because more than one letter appears between the D and G, not just one.|
||Creates complex filters or filters that do not fit the format of the other filter tools. Chapter 7 is devoted to the Crystal Reports formula language, and the lessons there can be used here to select records. Figure 4-11 shows the formula for a filter. The Formula Editor portion of the dialog box is opened by clicking Show Formula (which then changes to Hide Formula). Even if you have not specified a formula overtly, Crystal Reports creates a formula for every type of record selection that you create. Note in Figure 4-11 that you have a button to access the Formula Editor. In Chapter 7, I explain the concepts of using formulas.|
||Conducts date range searches. Suppose that you are trying to create a report for a recurring date range. This selects records for which the value in the date field falls within the date range specified. When you select this condition, the dialog box displays a scroll list of all Crystal Reports date ranges. Select the range you want from the list. Include all records in which the date falls within the calendar first quarter of the year. Dates from January 1 to April 30 (including January 1 and April 30) will be included; all other dates will be excluded. Note: The
||But what if you want to include all records except those where the region is CA? After you choose an option in the second box other than Any Value, you can go back to the first box and change is to is not. This option is available for any option other than
With a beginning grasp of the record selection process on a single field, the next question is how to use two or more fields to create a compound record selection. An example is a report that includes only records from a certain region and with sales over a certain amount. You have two ways to approach this type of selection. You can create the first filter and execute it, and then reopen the Select Record Expert and add the second filter. Or you can create both filters in one step. The advantage of the two-step process is that you can check your work step-by-step by previewing the records at each step to make certain that they are what you want.
To create both filters in one step:
The field name already appears in the dialog box.
Note: You may have noticed that this is a different way of getting to the Select Expert with the field you want to work with. If you have a database field selected on your report when you go to the Select Expert, Crystal Reports assumes that is the field you want to work with. A great little shortcut.
In this example, filter the records so that the report includes only CA (California) with sales from last year greater than 10,000:
Crystal Reports opens the Choose Field dialog box, from which you can select a field already in the report or any field from the table.
greater thanfilter, and enter a value of 10000.
The completed two-field criteria appear in Figure 4-13.
In the Select Expert, click the Show Formula button to see the entire record selection. I've let Crystal Reports do most of the work. I just dropped down a few boxes, and Crystal Reports has translated this into a formula your database will understand. Sometimes you will want to view this formula to see the Big Picture -- how the record selection looks as a whole.
Crystal Reports filters the records and displays those meeting the criteria in the Preview Tab as shown in Figure 4-14.
This tool is powerful and can be used in many ways to fine-tune the reports you create, so that only the records you want are included. Adding a third or fourth filter follows the same procedure as adding a second.
After selecting records for a specific report you can save the report or print it. If you want to use the same table to create a different report, but need to have access to all the records, you can remove the filter. To remove a Select Expert filter:
The Select Expert dialog box appears.
Crystal Reports asks you if you want to use saved data or refresh the data.
Crystal Reports re-runs the filter and adds back any records that were previously excluded.
The Select Expert is meant to make your life simple. Just point and click to create your record selection. This will probably be sufficient for most reports, but you can get more sophisticated if you need to. See Chapter 7 for more info.