Crystal Reports XI User’s Guide Crystal Reports XI
Copyright Copyright © 2004 Business Objects. All rights reserved. Trademarks Business Objects, the Business Objects logo, Crystal Reports, and Crystal Enterprise are trademarks or registered trademarks of Business Objects SA or its affiliated companies in the United States and other countries. All other names mentioned herein may be trademarks of their respective owners. Patents Business Objects owns the following U.S.
Contents Chapter 1 Introduction to Crystal Reports XI 21 About Crystal Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22 About this guide . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 Online help . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 Business Objects information resources . . . . . . . . . . . . . . . . . . . . . . .
Contents Chapter 4 Quick Start 45 Learning how to use Crystal Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46 Sample data - Xtreme.mdb . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46 Report Creation Wizards . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46 Standard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47 Cross-Tab . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Contents The Field Explorer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85 About the report design environment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87 Design tab . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87 Preview tab . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90 HTML Preview tab . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Contents Adding items to the repository . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123 Adding a text object or bitmap image . . . . . . . . . . . . . . . . . . . . . . . . . 123 Adding a custom function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124 Adding a command . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125 Using repository objects in reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Contents Record selection performance tips . . . . . . . . . . . . . . . . . . . . . . . . . . 145 Strategies for writing efficient record selection formulas . . . . . . . . . . 146 Incorporating parameter fields into record selection formulas . . . . . 148 Using SQL expressions where appropriate . . . . . . . . . . . . . . . . . . . . 150 Improving grouping, sorting, and totaling . . . . . . . . . . . . . . . . . . . . . . . . 151 Performing grouping on server . . . . . . . . . . . . . . . . . . . . . .
Contents Grouping data hierarchically . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183 Editing groups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 189 Summarizing grouped data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 189 Ordering groups by summarized values . . . . . . . . . . . . . . . . . . . . . . . 190 Selecting top or bottom N groups or percentages . . . . . . . . . . . . . . .
Contents Splitting and resizing sections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217 Splitting a section . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217 Resizing a section . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217 Using multiple sections in reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 218 Keeping variable length objects from overwriting each other . . . . . .
Contents Free-form placement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 252 Vertical placement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 254 Inserting character and line spacing . . . . . . . . . . . . . . . . . . . . . . . . . . 255 Setting fractional font sizes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 256 TrueType fonts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Contents Chart layouts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 282 Chart types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 283 Where to place a chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 286 Drill-down with charts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 286 Drill-down with legends . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Contents Changing the map title . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 311 Changing the map type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 311 Changing map layers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 312 Resolving data mismatches . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 313 Changing the geographic map . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Contents Report of order data—grouped by product . . . . . . . . . . . . . . . . . . . . 333 Report of order data—grouped by region and product . . . . . . . . . . . 334 Order data in a Cross-Tab object . . . . . . . . . . . . . . . . . . . . . . . . . . . . 334 Creating a Cross-Tab report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 336 Working with Cross-Tabs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 342 Showing values as percentages . . . . . . . .
Contents Quick reference to query filter operators . . . . . . . . . . . . . . . . . . . . . . . 364 Chapter 19 Creating and Updating OLAP Reports 367 OLAP reporting with Crystal Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . 368 OLAP grid objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 368 Creating an OLAP report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 369 Specifying the data source . . . . . . . . . . . . . .
Contents The Report Part Drilldown option . . . . . . . . . . . . . . . . . . . . . . . . . . . . 400 The Another Report Object option . . . . . . . . . . . . . . . . . . . . . . . . . . . 403 Hyperlinks displayed in the viewers . . . . . . . . . . . . . . . . . . . . . . . . . . 408 Using smart tags . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 409 Chapter 21 Report Alerts 411 About Report Alerts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Contents Deleting the formula specification . . . . . . . . . . . . . . . . . . . . . . . . . . . . 431 Debugging formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 431 Debugging evaluation time errors . . . . . . . . . . . . . . . . . . . . . . . . . . . . 432 Debugging tutorial . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 432 Chapter 23 Parameter Fields and Prompts 437 Parameter and prompt overview . . . . . . . . . . . . . .
Contents Applying conditional formatting using parameter fields . . . . . . . . . . . 462 Creating a report title using parameter fields . . . . . . . . . . . . . . . . . . . 463 Specifying single or ranges of values . . . . . . . . . . . . . . . . . . . . . . . . 464 Incorporating a parameter into a formula . . . . . . . . . . . . . . . . . . . . . . 465 Defining sort order using parameter fields . . . . . . . . . . . . . . . . . . . . . 466 Defining entry type and format using the Edit Mask . . . . . . .
Contents Indexed tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 487 Linking tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 489 Link from and link to . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 490 Link relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 490 Performance considerations in one-to-many links . . . . . . . . . . . . . . .
Contents Pre-pass 1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 528 Pass 1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 528 Pre-pass 2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 529 Pass 2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 529 Pass 3 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Contents Setting accessible preferences for BusinessObjects Enterprise . . . . . 557 Accessibility and customization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 557 Resources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 559 Appendix D Business Objects Information Resources 561 Documentation and information services . . . . . . . . . . . . . . . . . . . . . . . . . 562 Documentation . . . . . . . . . . . . . . . . . . . . . . . .
chapter Introduction to Crystal Reports XI
1 Introduction to Crystal Reports XI About Crystal Reports About Crystal Reports Crystal Reports is designed to work with your database to help you analyze and interpret important information. Crystal Reports makes it easy to create simple reports, and, it also has the comprehensive tools you need to produce complex or specialized reports. Create any report you can imagine Crystal Reports is designed to produce the report you want from virtually any data source.
Introduction to Crystal Reports XI About this guide About this guide This guide includes procedures for typical reporting tasks such as placing fields, formatting reports, and sorting records. It also contains information on more specific areas of interest such as advanced formula creation and accessing different types of data. Use this guide as a reference for your basic reporting needs as well as an introduction to new concepts in report creation.
1 Introduction to Crystal Reports XI About this guide 24 Crystal Reports User’s Guide
chapter What’s New in Crystal Reports XI
2 What’s New in Crystal Reports XI Introduction Introduction This section provides a high-level overview of the components, features, and benefits that are provided by the latest release of Crystal Reports. The major themes for Crystal Reports XI include: • • • • “Powerful report authoring” on page 26. “Improved usability” on page 27. “Expanded application-development capabilities” on page 28. “Integration with BusinessObjects Enterprise XI” on page 32.
What’s New in Crystal Reports XI Improved usability Parameterized sorting Group sort order and top or bottom N reports can now have their sort values driven by a formula. This new feature means that you can now use parameters to control sort order and the N value of a top or bottom N report. The result is a single report that is more flexible and can meet the needs of more users, reducing the total number of reports for you to create and support.
2 What’s New in Crystal Reports XI Expanded application-development capabilities Drag-and-drop charts and cross-tabs New drag-and-drop charts and cross-tabs introduce intelligent charting and cross-tab design by letting Crystal Reports automatically create a chart or cross-tab that is based on the data in your report. Chart and cross-tab design is now faster and easier.
What’s New in Crystal Reports XI Expanded application-development capabilities Report Application Server (RAS) Report Application Server XI contains many new features. Notable among these is dynamic prompting and cascading lists of values, an editable RTF export format, single-sign on support, complete programmatic control for subreports, and dynamic image locations.
2 What’s New in Crystal Reports XI Expanded application-development capabilities Report Designer Component (RDC) The new features in version XI of the RDC are primarily focused on format compatibility with reports created in Crystal Reports XI. This focus includes maintaining compatibility with applications created in previous versions of the RDC. New APIs • You can now retrieve the fully qualified database table name for a Table item in a report.
What’s New in Crystal Reports XI Expanded application-development capabilities New APIs • • • Full programmatic control for exporting has been added. For managed reports and unmanaged reports that run in-process RAS, Single Sign-On is supported for major databases. Significant speed improvements have been made when connecting Crystal Reports .NET view-only applications to BusinessObjects Enterprise. Enhanced deployment options • • There is now one merge module that works for all Crystal Reports .
2 What’s New in Crystal Reports XI Integration with BusinessObjects Enterprise XI Integration with BusinessObjects Enterprise XI Integration between the Crystal Reports Designer and BusinessObjects Enterprise has been substantially enhanced in the following ways: • The Repository Explorer has been enhanced to allow for easier logon and easier browsing of Enterprise objects and repository items. • The Repository Explorer can open the BusinessObjects Enterprise Central Management Console (CMC).
Crystal Reports User’s Guide chapter Installing Crystal Reports XI
3 Installing Crystal Reports XI Installing Crystal Reports XI Installing Crystal Reports XI The Crystal Reports Installation Wizard works with Microsoft Windows Installer to guide you through the installation process. The Installation Wizard automatically recognizes your computer’s operating system and updates files as required. This chapter provides step-by-step instructions for installing Crystal Reports and shows how to customize your installation.
Installing Crystal Reports XI Installing Crystal Reports on a local machine Installing Crystal Reports on a local machine If you are installing Crystal Reports on a computer running any of the supported operating systems (see Minimum installation requirements above), you must have Administrator privileges. The installation process creates registry entries and may update some system files that require Administrator rights.
3 Installing Crystal Reports XI Creating an installation point and installing from a network server 5. Choose the type of installation that you want to perform: • • 6. Typical installs the most common application features. Custom enables you to choose the features that you want installed, to specify where they will be installed, and to check the disk space required by each feature. For details, see “Customizing your installation” on page 38.
Installing Crystal Reports XI Creating an installation point and installing from a network server Creating an installation point for Crystal Reports This procedure must be performed by a network administrator who has write access and network privileges. When this procedure is complete, end users will be able to access Setup.exe from the network to install Crystal Reports onto their local machines.
3 Installing Crystal Reports XI Customizing your installation 3. Read and accept the License Agreement to proceed with the installation. 4. In the User Information dialog box, type your name, organization, and the product activation keycode. Tip: You may need to contact your Administrator for the product activation keycode. 5. Click Next. The Select Installation Type dialog box appears. 6. Choose the type of installation that you want to perform: • • 7.
Installing Crystal Reports XI Customizing your installation The icons in the feature tree indicate whether the feature and its subfeatures will be installed or not: • • • • A white icon means that the feature and all its subfeatures will be installed. A shaded icon means that the feature and some of its subfeatures will be installed. A yellow 1 means that the feature will be installed when required (installed on demand).
3 Installing Crystal Reports XI Customizing your installation Use the following table to determine your installation options for each feature or subfeature: Type of feature installation Description of what is installed Will be installed on local hard drive • • Installs the feature on the local hard drive. Entire feature will be installed on local hard drive • Installs the feature and all of its subfeatures on the local hard drive.
Installing Crystal Reports XI Running a silent installation Running a silent installation A silent installation is one that you run from the command line to automatically install Crystal Reports on any machine in your system, without the installation program prompting for information during the installation. To install silently, you run an msiexec command from the command line. The command must include a series of parameters that provide information for installation settings and directory paths.
3 Installing Crystal Reports XI Running a silent installation The following table lists the most common parameters used in a silent installation. To use a parameter, place it on the command line after the msiexec command and the path for the installation files. Note: Parameters that are not listed in this table may be available, but they have not been tested. Untested parameters are not supported.
Installing Crystal Reports XI Upgrading Crystal Reports components Installation parameter Description EXCLUDEDOTNETFILES Specifies whether the .NET developer components should be installed. For example, EXCLUDEDOTNETFILES=1 The default value is 0, which indicates that the .NET developer components should be installed. The value 1 indicates these components should not be installed. EXCLUDEJAVAFILES Specifies whether the Java developer components should be installed.
3 Installing Crystal Reports XI Upgrading Crystal Reports components This side-by-side behavior applies to Crystal Reports and the various Software Development Kits (SDKs). Crystal Reports XI for Visual Studio .NET can be used in side-by-side mode at runtime (that is, applications written using Crystal Reports 10 for Visual Studio .NET will continue to run with Crystal Reports 10 for Visual Studio .NET assemblies).
chapter Quick Start
4 Quick Start Learning how to use Crystal Reports Learning how to use Crystal Reports You can teach yourself how to use Crystal Reports by choosing from the methods available in this chapter: • You can study the sample reports and sample database included with Crystal Reports. • You can use the detailed descriptions and instructions in the “Quick start for new users” on page 48.
Quick Start Report Creation Wizards Each wizard guides you through the creation of a report by providing a series of screens. Many of the wizards have screens unique to a specific type of report. For example, the Mailing Labels Report Creation Wizard has a screen that allows you to specify the type of mailing label you want to use.
4 Quick Start Quick start for new users the dimensions you want to include in the grid. Next you filter the report data and choose the style of the grid object, which you can also customize. Finally, you can define labels for your grid and insert a chart, if you wish. This is only a brief overview of the four Report Creation Wizards available in Crystal Reports. Quick start for new users The following tutorial has been designed to give you confidence when creating your first report.
Quick Start Quick start for new users This tutorial has been designed using Microsoft Windows 2000. Screen shots may vary slightly if you are using a different platform. If you are not familiar with the Crystal Reports environment, review “Formatting” on page 231, which describes working with the grid, free-form placement, using guidelines, and formatting activities. Creating the report 1. Click the Start Page tab if the Start Page is not already visible in Crystal Reports.
4 Quick Start Quick start for new users Note: • • 2. You have to use the Open dialog box to find the Xtreme database. If you wish to see database and server properties after you have selected a database, right-click the database node in the Database Expert and select Properties from the shortcut menu. Expand the Tables node of the Xtreme.mdb connection to see a list of tables. Because you are dealing only with customers in this tutorial, you will select the Customer table. 3.
Quick Start Quick start for new users Note: Depending on how it appeared when you last used Crystal Reports, the Field Explorer dialog box might be docked or in floating mode. For more information about docking explorers, see “Opening and docking explorers” on page 114. Report sections The Design tab is divided into five sections: Report Header (RH), Page Header (PH), Details (D), Report Footer (RF), and Page Footer (PF).
4 Quick Start Quick start for new users u To insert a field 1. Expand the Database Fields node in the Field Explorer dialog box and expand a table. 2. Highlight a field name by clicking the name once. When you highlight a field name, you can review a subset of the values for that field as well as the field type and size by right-clicking the field and selecting Browse Data from the shortcut menu. 3. Click the Customer Name field and drag it into the Details section of the report.
Quick Start Quick start for new users The Design tab should look similar to this: Understanding fields Before going any further, take a look at the field you just placed in the Details section: • • • • • • First of all, the object frame indicates that when the report is printed, a field value will appear where the box is positioned.
4 Quick Start Quick start for new users Adding additional fields Next, you will insert two additional fields in the report. This time, however, you will use the Ctrl-click combination to add them at the same time. u To add additional fields 1. Highlight the City field in the Field Explorer dialog box, press the Ctrl key on your keyboard, and then highlight the Country field. Release the Ctrl key. If you scroll through the field list, you will notice that both fields remain selected.
Quick Start Quick start for new users Reviewing your work Now let’s see how the report looks with three fields placed and positioned. 1. Click Print Preview on the Standard toolbar to activate the Preview tab. The screen should look similar to this: Note: The first time you preview a report, you must click Print Preview on the Standard toolbar to activate the Preview tab. The Preview tab appears to the right of the Design tab.
4 Quick Start Quick start for new users is used for editing the text object. If you click an empty area of the report or a field object, the standard Design tab ruler appears. 3. Click once on the border of the text object to select it for resizing. Handles appear on all sides of the object. 4. Move the cursor over the right sizing handle of the text object and increase the width by about 1 inch. You may need to scroll to the right and continue resizing. 5.
Quick Start Quick start for new users Adding summary information The next step is to add summary information to your report. Adding summary information allows you to specify the author, title, and subject of the report, as well as any keywords or comments related to the report. When you add summary information, users can find information related to the report quickly. u 1. To add summary information On the File menu, click Summary Info.
4 Quick Start Quick start for new users 5. Position the object frame in the upper left-hand corner of the Page Header (PH) section of the report and release the mouse button to place the object. 6. Click the Preview tab or Refresh to review your changes. The report title object now displays the title that you entered in the Title text box of the Document Properties dialog box. Formatting objects Now you can format the report title.
Quick Start Quick start for new users Adding a field heading As you can see, the Contact Name field is the only field without a heading. In this section you will create a heading. u 1. To add a field heading Click Insert Text Object on the Insert Tools toolbar. 2. Insert the text object in the Page Header section directly above the Contact Name field. 3. Click Underline on the Formatting toolbar, and then type Contact Name into the text object. 4. Click your cursor outside the text object.
4 Quick Start Quick start for new users The report should now look similar to the following: Congratulations! You have just created a basic listing report. You will continue to refine this report throughout the rest of this tutorial. Record Selection Crystal Reports allows you to limit or restrict the records that are to be included in a report. In this section you will learn how to: • • Select the records you want included in the report. Save a report, including the selection criteria.
Quick Start Quick start for new users The Choose Field dialog box appears. This dialog box lists all the fields currently in the report in the Report Fields section and then lists all fields that are available from each table in the database fields section. 4. Since you are going to base record selection on the country field, select Country in the Fields list and click OK. The Select Expert appears.
4 Quick Start Quick start for new users 6. Click the arrow on the empty list. A list of all the country values appears. Select USA from the list. The sentence now reads: Select all records where a customer’s country is equal to USA 7. Click OK to return to the Design tab. 8. Click the Preview tab to review the results of your work. The Change In Record Selection Formula dialog box appears. 9. Click Refresh Data. 10.
Quick Start Quick start for new users Balancing field spacing Now that the Country field has been deleted, there is a large amount of white space between the City and Contact Name fields. You might be satisfied with the spacing as it stands, but it might be more readable if the columns were better balanced across the page. u 1. To balance field spacing Return to the Design tab. Select the Contact Name field and its field heading by using the Ctrl-click combination. 2.
4 Quick Start Quick start for new users Grouping and sorting Data in reports can be grouped and sorted in a variety of ways. Sorting and grouping tools provide a great deal of flexibility for customizing reports. Grouping the report In many reports you need to break the data into groups in order to make it easier to read and to understand. Crystal Reports lets you do this easily. For this customer list, you will group the customers by region and then sort the customers alphabetically within each group.
Quick Start Quick start for new users 6. If the group tree is not visible, select Toggle Group Tree on the Standard toolbar to see the groups included in the report. You can view the group of interest by clicking on the group name in the Group Tree. For example, to see the Texas customer group, click TX in the Group Tree. The program jumps to the Texas group, displaying that group in the Preview tab.
4 Quick Start Quick start for new users The Record Sort Expert appears. The Available Fields list box displays all fields currently on your report and all the fields in the data source. You can choose to sort based on any of these fields. The Sort Fields list box displays the fields that are already sorted in the report. Since the region field has already been sorted, the sorting you are about to do will be within each region, and not for the entire report. 2.
Quick Start Quick start for new users Completing the report You have just one step left to complete the report. A company logo needs to be added to the first page of the report. Inserting a company logo In this section, you will place a company logo at the top of the first page of the report. u 1. To insert a company logo While on the Design tab, click Insert Picture on the Insert Tools toolbar. The Open dialog box appears. 2. Choose a bitmap logo (.bmp) file and click Open.
4 Quick Start Quick start for advanced users Related topics: • For information about distributing reports (printing, exporting, and so on) see the printing topics in “Formatting” on page 231 and “Printing, Exporting, and Viewing Reports” on page 383. Quick start for advanced users If you are an experienced Windows user who wants to get right into the program, follow these steps to set up a report for the first time. If you’re not an experienced user, check the “Quick start for new users” on page 48.
Quick Start Quick start for advanced users u 1. To work with report elements on the Design tab Each of the default report areas contains a single section. To add additional sections, click Section Expert on the Expert Tools toolbar and use the Section Expert to add the desired sections. Once you have added sections to an area, you can move, merge, and delete them in the Section Expert. See “Designing with guidelines” on page 246. 2.
4 Quick Start Quick start for advanced users Note: • • • 6. The field names and field types can be viewed by selecting the Show Field Names check box on in the Options dialog box (Layout tab). The program automatically places field titles in the Page Header section unless the Insert Detail Field Headings check box is not selected in the Options dialog box (Layout tab).
Quick Start Quick start for advanced users u To use other reporting features 1. If you want to format a field, change the placement or width of a field, or insert a summary, click the field to select it. Handles appear on the top, bottom, and sides of each selected field: • • • To change the placement of the field(s), use the mouse to drag the field placement frame to its new position. To change the width of the field, use the mouse to drag the right or left handle.
4 Quick Start Quick start for advanced users 8. To insert a cross-tab object in a report, click Insert Cross-Tab on the Insert Tools toolbar and set up the cross-tab in the Cross-Tab Expert when it appears. See “Cross-Tab Objects” on page 329. 9. To create a parameter field (a field that prompts you for a value whenever you retrieve data for a report), click Field Explorer on the Standard toolbar, then select Parameter Fields in the Field Explorer dialog box when it appears.
chapter Report Design Concepts
5 Report Design Concepts Basic report design Basic report design The purpose of this chapter is to suggest a structured approach to preparing a Crystal report. This approach includes the following elements: • • Deciding on the content of the report. Developing a prototype on paper. This section has been designed to provide a conceptual understanding of the reporting process.
Report Design Concepts Deciding on the content of the report Who is going to read the report? A single report is often used by many individuals. A detailed, company-wide sales report, for example, may be used by sales representatives, the regional sales manager, the national sales manager, and the Chief Operating Officer (COO).
5 Report Design Concepts Deciding on the content of the report • Crystal Reports can generate information such as the print date or page numbers. Finding the data What data do you want to use in the report? Do you know the type of database you are reporting from? Will you be reporting off a data file, SQL/ODBC, a Business View, or a Dictionary? If you do not know, ask the database administrator in your organization for help in setting up the database type and location of the data.
Report Design Concepts Deciding on the content of the report What types of fields contain data? You should take the time to get to know the data type for data fields that will be used in your calculations. Since formula functions and operators work with specific kinds of data, it is important to recognize the data type you are working with, before you start any calculations. For example, some functions require numeric data, while others work with only string fields.
5 Report Design Concepts Deciding on the content of the report has shown no activity during the last month, during the last three months, or during some other defined period. To flag information, identify it and any conditions that will trigger the flagging. How do you want information flagged? You may want to flag items with an asterisk or some other symbol, or you may want a word to appear as a flag. In any case, you should write out flagging instructions so they are handy.
Report Design Concepts Deciding on the content of the report Page Header Objects placed in the Page Header area print at the beginning of each new page. • • Charts or Cross-Tabs cannot be placed in this section. Formulas placed in this area are evaluated once per page, at the beginning of each new page. Group Header Objects placed in the Group Header area print at the beginning of each new group. • • Charts and Cross-Tabs placed in this area contain data just for the group.
5 Report Design Concepts Developing a prototype on paper Developing a prototype on paper While a paper prototype is useful regardless of your level of expertise with Crystal Reports, it is particularly valuable when you are first learning the program. With the paper prototype in hand, you can put your full effort into learning and using the commands, rather than into trying to design and learn at the same time. u To design a paper prototype 1.
chapter Introduction to Reporting
6 Introduction to Reporting Report creation options Report creation options Each time you create a new report, you have three options: • • • Use a Report Creation Wizard. Use another report as a model. Create a report from scratch. You will probably use each option at some time. Report Creation Wizards The Report Creation Wizards help create reports as quickly as possible and many new users and developers alike prefer to create the majority of their reports using them.
Introduction to Reporting Choosing data sources and database fields The Blank Report option was chosen for the “Quick start for new users” on page 48, because the process of creating a report from scratch most fully illustrates the basics of reporting. Choosing data sources and database fields Crystal Reports makes it simple to select data sources and database fields by providing easy-to-use functionality in the Database Expert dialog box and the Field Explorer dialog box.
6 Introduction to Reporting Choosing data sources and database fields The Create New Connections folder contains subfolders for many popular data sources. Among these, you’ll find: • • • • • Access/Excel (DAO) Database Files ODBC (RDO) OLAP OLE DB (ADO) Note: The data source options available in the Create New Connections folder depend on the data access components selected during installation. For a brief description of each of these folders and subfolders, see “Selecting the data source” on page 94.
Introduction to Reporting Choosing data sources and database fields • Rename repository object Use this option to rename an existing SQL command in the BusinessObjects Enterprise Repository. • Options Use this option to set the global options that appear on the Database tab of the Options dialog box. • Refresh Use this option to refresh the list of available data sources in the Database Expert. Links tab Database tables are linked so records from one database match related records from another.
6 Introduction to Reporting Choosing data sources and database fields The toolbar offers these functions: • Insert to Report Use this option to add a field to the report. You can insert more than one field at a time by selecting multiple fields, right-clicking, and choosing Insert to Report. Alternatively, to insert a field, you can drag and drop it in the Design or Preview tabs. • Browse Use this option to browse data for a database field, formula field or SQL expression field.
Introduction to Reporting About the report design environment Group Name Fields You can insert an existing Group Name field shown in the Field Explorer by right-clicking it and selecting Insert to Report. Unlike a formula field, parameter field or running total field, however, you cannot create a Group Name field through the Field Explorer. (A Group Name field is created when you insert a group.
6 Introduction to Reporting About the report design environment Design tab areas When you first begin creating a report, Crystal Reports automatically creates five areas in the Design tab. • Report Header This section is generally used for the report title and other information you want to appear at the beginning of the report. It can also be used for charts and cross-tabs that include data for the entire report.
Introduction to Reporting About the report design environment If you set up additional groups, the program creates new group areas between the Details area and the existing Group Header and Group Footer area(s). Like the original areas, each of these newly added areas can contain one or more sections. By default, they each contain a single section. Identifying and working with areas and sections By default, each area contains only a single section.
6 Introduction to Reporting About the report design environment Section ruler. Whenever a new section is added, the program creates a ruler for that section. See “Using multiple sections in reports” on page 218. Other Design tab capabilities There are several other capabilities built into the Design tab. With the Design tab, you can: • • • • Resize a section by dragging its boundary. See “Resizing a section” on page 217. Split a section (create two sections from one) by clicking its left boundary.
Introduction to Reporting About the report design environment Crystal Reports provides two views for previewing a report: • • Standard view Group Tree view Standard view In standard view, the report is displayed a page at a time. Using the navigation buttons in the Preview tab, you can move to the beginning or end of the report, or you can move backward and forward through the report one page at a time.
6 Introduction to Reporting About the report design environment Group Tree view The Group Tree view can be shown or hidden using Toggle Group Tree on the Standard toolbar. The Group Tree view presents a split screen: • • The right pane displays the report. The left pane displays a high level outline of the report, showing the hierarchy of groups and subgroups in a familiar tree format. The Group Tree normally displays the names of the groups and subgroups you created in your report.
Introduction to Reporting About the report design environment • • • • The Preview tab has a single vertical ruler at the left of the tab rather than the individual section rulers seen in the Design tab. The functionality of the ruler is the same. The Preview tab identifies report sections in the shaded area to the left of the data. With a quick look you can tell which report section the data is printing from.
6 Introduction to Reporting Creating a new report HTML Preview tab To see an HTML rendering of your Crystal report as it will appear when published to the web, click HTML Preview on the Standard toolbar. When you need to make sure that your report design is appropriate and correct over the web in a zero-client environment, the HTML Preview option lets you see an instant rendering of your report to HTML without leaving the Crystal Reports design environment.
Introduction to Reporting Creating a new report 2. Use the tree view in the Available Data Sources list of the Data screen to select your data source: • Current Connections This folder shows a list of data sources you are currently connected to. • Favorites This folder shows a list of data sources you commonly use and have maintained in your Favorites list. • History This folder shows a list of data sources you have used recently. The last five data sources used are displayed.
6 Introduction to Reporting Creating a new report • OLE DB (ADO) This option shows a list of OLE DB providers you have already configured for use. You can also specify a Microsoft Data Link file to use. Note: The data source options available in the Create New Connections folder depend on the data access components selected during installation. Adding tables After selecting the data source, you can add one or more tables to base your report on. u To add a table 1.
Introduction to Reporting Creating a new report Crystal Reports automatically links tables by name or key when possible. 4. To create links manually, drag a field from one table to a field in another table. If successful, a link line is created. If unsuccessful, a message is issued. Note: You can link tables by table name or by foreign key information. 5. If you’ve deleted links and you want to recreate them automatically, click Link. 6. Click OK when finished.
6 Introduction to Reporting Creating a new report Placing data on the report Placing data on a report is a very important task. You need to know what type of data should be placed on the report and where on the report it should be placed. Database fields Much of the data placed on a report are database fields, displaying data as it is stored in the database. For example, in the “Quick start for new users” on page 48, the Customer Name, City and Country fields are placed on the report.
Introduction to Reporting Creating a new report u 1. To create an SQL Expression field On the Standard toolbar, click Field Explorer. The Field Explorer dialog box appears. 2. Scroll down to SQL Expression Fields and highlight it. Click New. 3. Enter a name in the Name box, and then click OK. The SQL Expression Name dialog box appears. The Formula Workshop appears with the SQL Expression Editor active. 4. Type the expression in the SQL Expression Editor.
6 Introduction to Reporting Creating a new report Running total fields To display a total that evaluates each record and provides a running sum of all the values in a field (or all the values in a certain set of values), a running total field needs to be created and placed in the report. If the first three values in a field were 2, 4, and 6, a running total would print 2, and then 6 (the sum of 2 + 4), and then 12 (the sum of 2 + 4 + 6).
Introduction to Reporting Creating a new report start for new users” on page 48, text objects are used to easily display the two contact name database fields as one object, to insert a column heading for the concatenated contact name, and to insert a title in your report. u 1. To insert a text object On the Insert Tools toolbar, click Insert Text Object. An empty object frame appears. 2. Position the text object where you want it to appear in the report.
6 Introduction to Reporting Creating a new report 2. Select the desired picture file from the file list and click Open to return to the report. An object frame appears with the picture inside, ready to be positioned. 3. Position the picture object where you want it to appear in the report and left-click once. BLOB (Binary Large Object) fields A BLOB field is a database field whose data consists of Binary Large Objects—such as bitmap graphics, images, OLE objects, metafiles, and so on.
Introduction to Reporting Creating a new report Hyperlink fields You can select a report object on the Design or Preview tab and create a hyperlink to another location. The hyperlink is saved with your report and is available to other users as a way of viewing additional information. Note: Crystal Reports also lets you create hyperlinks with Relative URLs, so your web reports retain independence from their location on any particular server. u 1.
6 Introduction to Reporting Creating a new report • A File Select this option to create a hyperlink to a file on a specific computer or networked computer. Use the formula button to create a file path based on a field value. You can also use a hyperlink of this type to run an EXE with a command-line parameter. Enter the path and application name, and follow it by the parameter for the command.
Introduction to Reporting Creating a new report Record selection Record selection, the task of paring down the data to include only the data required for your report, is a crucial step in report creation. You will rarely want a listing of all the information in a database. Most often you will be interested in only the sales in a given time period or for a certain product, an so on. For example, a sales report may be designed to only include sales from one product line for the last calendar month.
6 Introduction to Reporting Creating a new report averages, counts, and other group (aggregate) values. For example, in a sales report you may want to calculate an average of sales per state (average summary on the sales field) and calculate the number of products sold in the state (distinct count of the product name field). Using the drill-down option on summarized data You can drill down on your data, to show the data behind individual groups, using the Drill-down cursor.
Introduction to Reporting Creating a new report 25% 50% 100% It is helpful to view reports at low magnifications in order to get an overall picture of the layout of your report. Views at higher magnifications focus attention on the details of the report. Inserting page headers and footers You can use page headers and footers by placing the information in the Page Header or Page Footer sections of the Design tab.
6 Introduction to Reporting Creating a new report • Information to appear at the bottom of every page goes in the Page Footer (PF). Text, fields, or formulas can be used in these sections just as in the Details section. Related topics: • “Creating footers after the first page” on page 274 Adding a title page to the report Crystal Reports provides a quick, easy way to add a title page to a report by selecting Report Title from the Special Fields in the Field Explorer dialog box.
Introduction to Reporting Exploring reports and working with multiple reports Adding summary information to the report There may be times when you want to include non-printing comments with a report (a personal note to the report recipient, a note to explain more thoroughly the data on which the report is based, a report title, a comment about some particular data on the report, and so on).
6 Introduction to Reporting Exploring reports and working with multiple reports The Report Explorer The content of the Report Explorer represents the content of the report in a tree view. The root node is the report itself, while the first-level nodes represent the report’s sections. Within each section, the report’s fields and objects are listed. Any item you select in the tree view will be selected in the report (in either Design or Preview modes).
Introduction to Reporting Exploring reports and working with multiple reports Note: • • Items connected to a BusinessObjects Enterprise Repository are shown with the repository icon beside them. Items with hyperlinks are shown as blue text with a solid underline. Items with Report Part hyperlinks are shown as green text with a dashed underline. The Workbench In the Workbench, you can create projects that contain one or more reports.
6 Introduction to Reporting Exploring reports and working with multiple reports • Check Dependencies Use this option to initiate the Dependency Checker. The option is available for individual reports or for folders of reports. Crystal Reports begins checking the report(s) immediately and displays the results in the Dependency Checker. For more information, see “The Dependency Checker” on page 112. Shortcut Menus You can right-click any item in the Workspace area of the Workbench to see a shortcut menu.
Introduction to Reporting Exploring reports and working with multiple reports Errors listed in the Dependency Checker show the following information: • Error type. Icon Meaning Success. The Dependency Checker has verified that there are no errors in the file. Warning. The Dependency Checker has found something in the file that might be a problem. You can verify the warning, but you don’t have to fix it to have the report to run correctly. Error.
6 Introduction to Reporting Exploring reports and working with multiple reports • Options Use this option to open the Dependency Checker tab of the Options dialog box. Use this tab to select the conditions that you want to check for when you run the Dependency Checker. For more information about the options, search the Crystal Reports Online Help for the topic “Dependency Checker tab (Options dialog box).
Introduction to Reporting Beyond basic reports Click the tabs in the amalgamated explorer to change focus from one explorer type to another; the name in the title bar changes accordingly to show you which explorer you’re currently using.
6 Introduction to Reporting Beyond basic reports 116 Crystal Reports User’s Guide
chapter BusinessObjects Enterprise Repository
7 BusinessObjects Enterprise Repository What is the BusinessObjects Enterprise Repository? What is the BusinessObjects Enterprise Repository? The BusinessObjects Enterprise Repository is the central location for you to store and manage your report objects. Data definitions such as custom functions and custom SQL commands can also be stored and maintained in the BusinessObjects Enterprise Repository.
BusinessObjects Enterprise Repository What is the BusinessObjects Enterprise Repository? Work flow Although there are many ways to use the BusinessObjects Enterprise Repository, this sample work flow will acquaint you with some of the things you might want to do: • Set up folders for your repository The Repository Explorer displays the contents of your BusinessObjects Enterprise Repository as a tree with folders and subfolders. You can add and rename folders as you like.
7 BusinessObjects Enterprise Repository Accessing the BusinessObjects Enterprise Repository Accessing the BusinessObjects Enterprise Repository The repository supplied by Crystal Reports is set up when you install. You don’t have to do anything additional before you use the repository. u To open the BusinessObjects Enterprise Repository 1. Start Crystal Reports and open a report. 2. Click the Repository Explorer button on the Standard toolbar.
BusinessObjects Enterprise Repository Accessing the BusinessObjects Enterprise Repository Toolbar The Repository Explorer’s toolbar provides buttons with tool tips. These buttons let you add new folders, search for items, and so on: • Change view settings Use this option to open the View Settings dialog box. Use this dialog box to limit the type of repository items displayed in the Repository Explorer. You can also select options to sort multiple items by name or by type.
7 BusinessObjects Enterprise Repository Adding folders to the repository Note: If a report that you want to manage returns an error, check with a BusinessObjects Enterprise administrator to ensure that the CMC Access URL is configured correctly. For more information, see the BusinessObjects Enterprise Administrator’s Guide. u To schedule a managed report through the repository 1.
BusinessObjects Enterprise Repository Adding items to the repository Note: You cannot add new folders to the Enterprise Items node and its child nodes. These folders are controlled by BusinessObjects Enterprise. 2. Name your new folder and hit the Enter key. 3. To add a subfolder, right-click your new folder and click New Folder on the shortcut menu. 4. Name your new subfolder and hit the Enter key.
7 BusinessObjects Enterprise Repository Adding items to the repository You could have added the text object or bitmap image without dragging and dropping it. 1. Select a text object or bitmap image in your report and click Add to Repository from the shortcut menu. The Add Item dialog box appears. Notice that, because you did not drag the object to a specific repository folder, this dialog box contains an area to select a location from. 2. In the Name field, create a name for your object or image.
BusinessObjects Enterprise Repository Using repository objects in reports Adding a command 1. In the Selected Tables area of the Database Expert, select the command you want to add to the repository. 2. Right-click the command and select Add to Repository. 3. In the Add Item dialog box, specify a name and repository location for the command. You’ll find the command in the BusinessObjects Enterprise Explorer, the Database Expert, and the Set Datasource Location dialog box.
7 BusinessObjects Enterprise Repository Using repository objects in reports Adding a text object or a bitmap image to a report 1. Click the Repository Explorer button on the Standard toolbar. Tip: Another way to do this is to click the Repository Explorer option under the View menu. 2. Log on to BusinessObjects Enterprise if you have not done so already. For information about how to log on to BusinessObjects Enterprise, see “Working with Enterprise folders” on page 395. 3.
BusinessObjects Enterprise Repository Modifying objects in the repository Tip: If the BusinessObjects Enterprise Explorer doesn’t open immediately, double-click Make New Connection. 3. In the BusinessObjects Enterprise Explorer, expand folders until you find the command you want to add, and then click Open. You’ll see your command in the Available Data Sources area of the Database Expert. When the command is in this area, you can added it to your report as you would add any other data source.
7 BusinessObjects Enterprise Repository Updating connected repository objects in reports 4. Drag the modified text object from the report back its location in the Repository Explorer. When you drop the text object, the Add or Update Object dialog box appears. 5. Click Update. 6. In the Modify Item dialog box, make any changes you require and click OK. 7. Right-click the text object in the repository and click Properties from its shortcut menu.
BusinessObjects Enterprise Repository Deleting items from the repository Deleting items from the repository Any object you store in the repository can be deleted from that repository without removing it from the reports that use it. Once you remove an object from the repository, it is removed for all users. Note: • • u 1. When you delete an object from the repository, it appears to remain connected in the reports that use it.
7 BusinessObjects Enterprise Repository Using Undo in the repository 130 Crystal Reports User’s Guide
chapter Designing Optimized Web Reports
8 Designing Optimized Web Reports Overview Overview Whether you distribute your enterprise reports over a local network, a corporate Intranet, or the Web, you can use Crystal Reports’ powerful, built-in performance features to deliver web reporting speed.
Designing Optimized Web Reports Scale with BusinessObjects Enterprise Key strategies for optimizing web reports • • • • • As your company grows, Crystal Reports and BusinessObjects Enterprise can grow along with you. For details, see “Scale with BusinessObjects Enterprise” on page 133. For tips and considerations on how to create fast, interactive reports, see “Making the right design choices” on page 135.
8 Designing Optimized Web Reports Scale with BusinessObjects Enterprise • Versioning Versioning works closely with scheduling to store “instances” of a report. Versioning not only reduces the number of database hits required to serve users, but also allows you to keep an archive of report instances for historical reference. • Clustering BusinessObjects Enterprise incorporates proven clustering and loadbalancing technology to help you deliver a highly available, reliable information delivery system.
Designing Optimized Web Reports Making the right design choices • • • • processes scheduled reports against the database, and the Page Server processes on-demand reports against the database. The Page Server also formats individual pages of report instances when users view them. When a scheduled report is processed, all date and time formulas are evaluated by the Job Server in order to generate the report instance. When you simply view the resultant report instance, none of the formulas is re-evaluated.
8 Designing Optimized Web Reports Making the right design choices • • When users need real-time reporting of live data over the Web, useroriented reports respond quickly and communicate efficiently with the database server. Reports become more useful, because each user customizes the report’s contents, thereby creating a reporting solution specific to his or her particular decision-making problem.
Designing Optimized Web Reports Making the right design choices To ensure the efficiency of real-time reporting, read all of the suggestions in this chapter. These topics, however, are of particular importance: • • • “Taking advantage of on-demand subreports” on page 139 “Performing grouping on server” on page 151 “Incorporating parameter fields into record selection formulas” on page 148 Saved data Reports with saved data are useful for dealing with data that isn’t continually updated.
8 Designing Optimized Web Reports Making the right design choices Designing summary reports Designing and distributing summary reports is a relatively easy way to ensure that users quickly find the data they need over the Web. A summary report can include as much data as any other report. However, by hiding a summary report’s Details section, you avoid overwhelming users with data they may not immediately need.
Designing Optimized Web Reports Making the right design choices Taking advantage of on-demand subreports If your report has a section that handles a large number of records, you can put that section into an on-demand subreport. An on-demand subreport appears as a hyperlink in the primary report. When you open the primary report, no data is retrieved for the on-demand subreport until you drill down on the hyperlink.
8 Designing Optimized Web Reports Making the right design choices Linking tables instead of linking subreports Whenever possible, coordinate your report data by linking database tables on the Links tab of the Database Expert, rather than by linking regular subreports—that is, subreports that aren’t on-demand subreports—to the main report. Since each subreport is run as a separate report, linked tables often have a performance advantage.
Designing Optimized Web Reports Streamlining your reporting environment When reports are processed by the Page Server, data is shared between users of a report when these conditions are met: • • • • Users view a report using the same database logon information. Users view a report using the same parameters. Users view a report using the same page layout options. Users view a report using the same locale settings.
8 Designing Optimized Web Reports Streamlining your reporting environment Using table indexes You can also improve the way your data is organized within the database. For optimum processing speed, report off indexed fields on your SQL database. Using table indexes is an easy way to increase the speed of data access and to reduce the time it takes for Crystal Reports to evaluate data. For a complete description of how table indexing works, see “Indexed tables” on page 487.
Designing Optimized Web Reports Streamlining your reporting environment Related topics: • • • For complete details on table-linking scenarios, see “Linking tables” on page 489. For general information on record selection formulas, see “Selecting records” on page 156. For advanced record selection strategies, see “Using enhanced record selection formulas” on page 144.
8 Designing Optimized Web Reports Using enhanced record selection formulas Using stored procedures for faster processing A stored procedure is a compiled SQL program, consisting of one or more SQL statements, which resides and runs on your SQL server. Although stored procedures can take time to set up properly, they can be incredibly powerful. The advantages of using stored procedures are most noticeable when reporting off large sets of data, or when running reports that demand long, complex calculations.
Designing Optimized Web Reports Using enhanced record selection formulas Pushing down record selection—an example This example demonstrates the benefits of writing record selection formulas that can be pushed down to the database server. In the Orders table of the Xtreme sample database, there are 2192 records, of which 181 have order dates prior to 2001. Suppose you want to report on only those records. On the one hand, you could use this record selection formula: Year ({Orders.
8 Designing Optimized Web Reports Using enhanced record selection formulas • In record selection formulas, avoid data type conversions on fields that are not parameter fields. For example, avoid using ToText( ) to convert a numeric database field to a string database field. • You are able to push down some record selection formulas that use constant expressions. PC Databases • • You can only push down record selection on indexed fields. You can only push down AND clauses (not OR).
Designing Optimized Web Reports Using enhanced record selection formulas To open the Formula Workshop to modify record selection, click the Report menu, point to Selection Formulas, and then select Record from the submenu. Consideration 2 Any selection formula that is of the form: DataBaseField SupportedOperator ConstantOrParameterExpression can be pushed down. Of course DataBaseField is just a database field. SupportedOperator is any of =, <>, <, <=, >, >=, StartsWith, Like or In.
8 Designing Optimized Web Reports Using enhanced record selection formulas Consideration 5 When using multiple expressions that follow the above considerations, separate them with AND and OR operators—you can also use NOT. You can have several of each, and you can use parentheses to give priority. For example: {Orders.Order ID} < Minimum({?number range}) Or {Orders.Order Amount} >= 1000 (IsNull({Customer.Region}) Or {Customer.Region} = "BC") And {Customer.
Designing Optimized Web Reports Using enhanced record selection formulas Now click the Design tab. 2. On the View menu, click Field Explorer. 3. In the Field Explorer, right-click Parameter Fields and select New from the shortcut menu. The Create New Parameter dialog box appears. 4. For the Name of the parameter, type SalesQuota. 5. Click the Type list and select Number. 6. In the Options area, go to the Prompt Text option and type What was last year’s sales quota? 7.
8 Designing Optimized Web Reports Using enhanced record selection formulas 58 records are returned for your report, instead of the 270 records that were returned before you put the parameter field into a record selection formula. By enhancing this report, you’ve retrieved all the information you needed and, at the same time, you’ve ensured that the fewest number of records is transferred from the database server.
Designing Optimized Web Reports Improving grouping, sorting, and totaling Finally, if your database supports Case Logic, and your report needs to summarize an If-Then-Else formula calculation, replace the formula with an SQL Expression field. In such cases, SQL Expression fields enable Crystal Reports to perform the report’s grouping on the server. For more information, see “Using SQL Expressions for Case Logic” on page 153.
8 Designing Optimized Web Reports Improving grouping, sorting, and totaling Benefits of grouping on server—an example The following example describes a typical reporting scenario wherein grouping on server can significantly reduce the amount of unnecessary data transferred from the database server. u To decrease data transfer by grouping on server 1. Open the sample report Group.rpt (located in the Feature Examples folder).
Designing Optimized Web Reports Improving grouping, sorting, and totaling Moreover, by enabling the Perform Grouping on Server option, you’ve ensured that the initial processing is completed on the database server. Consequently, only the necessary records are transferred to the report. For more information on server-side processing, see “Server-side processing” on page 516.
8 Designing Optimized Web Reports Improving grouping, sorting, and totaling Inserting summary and running total fields where possible Where possible, avoid creating formulas with global variables to calculate summaries or running totals. Instead, create summaries by clicking the Insert menu and then selecting the appropriate command (Subtotal, Grand Total, or Summary).
chapter Record Selection
9 Record Selection Selecting records Selecting records When a field is selected to appear on a report, field values from every record in the active table(s) are printed by default. In many cases, you may not want to include all the values, but only a subset of those values. For example, you may want to include: • • • Records only for a specific group of customers. Records for a specific range of account numbers out of the total number of records in the database.
Record Selection Selecting records • • If the table has a Postal Code field, you could base your record selection on the range of ZIP codes that apply to California (Postal Code is between n and N). If the table has an Area Code field, you could base your record selection on California Area Codes (Area Code is one of x, y,....z). Note: • • If the Area Code is stored in the telephone number field, you will not be able to do this same record selection using the Select Expert based on the Area Code.
9 Record Selection Selecting records u To set up record selection using the Select Expert 1. On the Expert Tools toolbar, click Select Expert. The Select Expert dialog box appears. Note: If you click the Select Expert button without first highlighting a field in your report, the Choose Field dialog box appears. Highlight the field on which you want to base record selection and click OK. The Select Expert appears. 2. Use the drop-down lists to enter your selection criteria for the indicated field. 3.
Record Selection Selecting records Related topics: • • • For sample record and group selection templates, see “Using formula templates” on page 160. For complete instructions on creating formulas, see “Using Formulas” on page 417. For advanced record selection strategies and performance tips, see “Using enhanced record selection formulas” on page 144. Interaction of the Select Expert and the Formula Editor The Select Expert and the Record/Group Selection Formula Editor are interactive.
9 Record Selection Using formula templates 5. Use the Select Expert to change your selection formula. 6. Review the updated formula by clicking the Show Formula button again. 7. To make changes to the formula, click the Formula Editor button in the expanded Select Expert and use the tools in the Formula Workshop to make your formula changes. Note: Selection formula components that do not fit any of the fixed criteria in the Select Expert will not be translated.
Record Selection Using formula templates Selecting records using numbers Single values {file.FIELD} > 99999 Selects those records in which the value in the {file.FIELD} field is greater than 99999. {file.FIELD} < 99999 Selects those records in which the value in the {file.FIELD} field is less than 99999. Range of values {file.FIELD} > 11111 and {file.FIELD} < 99999 Selects those records in which the value in the {file.
9 Record Selection Pushing down record selection to the database server Selecting records using preset date ranges The preset date ranges can be used to create selection formulas similar to these: {file.DATE} in LastFullMonth Selects those records in which the date found in the {file.DATE} field falls within the last full month. (If the month is May, this selects all records with an April date.) not({file.DATE} in LastFullMonth) Selects all records except those in which the date found in the {file.
Record Selection Troubleshooting record selection formulas Since database servers are usually faster machines than workstations, it is to your advantage to specify record selection formulas that can be processed by the server in the first stage. Doing so minimizes record selection on the local machine in the second stage. This process is commonly known as “pushing record selection down to the database server.
9 Record Selection Troubleshooting record selection formulas 5. Print the report and verify that the data in those fields which are referenced in the selection formula print satisfactorily. Make certain that all the data prints. For example, if there are x total records in the database you should have x records printing for each of the referenced fields. This establishes a baseline against which you can compare the results of printing with the selection formula. 6.
Record Selection Troubleshooting record selection formulas 9. Once the selection formula with two selectors activated is working properly, add a third selector, then a fourth, and so on, until you have tested each selector in the selection formula. Correcting selections that do not generate data You may encounter a situation in which you create a record selection formula and, while header and footer information prints on your report, no detail information appears.
9 Record Selection Troubleshooting record selection formulas Another formula which performs much the same functions is the following: "BOB" in UpperCase({customer.CONTACT FIRST NAME}) Unwanted spaces appear in selection formula Spaces are characters, and when you include spaces in the search key of a record selection formula, the formula looks for records with the exact match in the selected field, spaces and all. For example, the following formula: "Mr . " in {customer.
chapter Sorting, Grouping, and Totaling
10 Sorting, Grouping, and Totaling Sorting data Sorting data Sorting means placing data in some kind of order to help you find and evaluate it. When you first insert a database field into your report, the data within the fields appears in the order in which it was originally entered into the database. Locating information in this kind of report is difficult. It is much easier to review or find information when you can see it sorted in a logical format.
Sorting, Grouping, and Totaling Sorting data Field Type Sort Order Currency fields numeric order Number fields numeric order Date fields chronological order DateTime fields chronological order same-date values sorted by time Time fields chronological order Boolean comparison fields False values (0) True values (1) Null values null values non-null values Note: If sorting and grouping are performed on the database server, sort order may vary when Unicode or UTF-8 data is used.
10 Sorting, Grouping, and Totaling Sorting data In multiple field sorts, the Report Designer first sorts the records based on the values in the first field selected, putting them in ascending or descending order as specified. When two or more records have the same field value in the first sort field, the program then sorts those records based on the value in the second sort field. For example, if you choose to sort first by the {customer.COUNTRY} field and then by the {customer.
Sorting, Grouping, and Totaling Grouping data 7. As you add each field to the Sort Fields list, specify the sort direction. 8. Click OK when finished. Records are sorted based on the values in the Sort Fields list. Grouping data Grouped data is data that is sorted and broken up into meaningful groups. In a customer list, for example, a group might consist of all those customers living in the same Zip Code, or in the same Region.
10 Sorting, Grouping, and Totaling Grouping data u To group data 1. On the Insert menu, click Group. The Insert Group dialog box appears. 2. Select the field you want the data grouped by from the top drop-down list. 3. Select the sort direction from the second drop-down list. Note: If you want to use a formula for the sort order of your group, see “Sorting groups conditionally” on page 174. 4. If you want to show a different value in the group header, click the Options tab.
Sorting, Grouping, and Totaling Grouping data Related topics: • • “Grouping data in intervals” on page 180 “Creating group headers” on page 197 Creating custom groups Usually data is sorted based on the values from a field in the report. Sometimes, however, you may not want to group data based on the values found in one of the fields on your report. For example: • The field you want to group on does not exist.
10 Sorting, Grouping, and Totaling Grouping data 3. Select in specified order as your sort option from the second drop-down list. 4. On the Specified Order tab, enter the name of the group in the Named Group field. 5. Click New. 6. In the Define Named Group dialog box, use the drop-down lists to select the data to be part of the group. 7. Click the tab to add more selection criteria to your specified group, if necessary. 8. Click OK. 9.
Sorting, Grouping, and Totaling Grouping data 3. Create a string parameter that contains the sorting options that you want your users to see. For this example, create a parameter called Sort Order that has two values: Ascending and Descending. Tip: For information about how to create parameters, see “Creating a parameter with a static prompt” on page 446. 4. Click OK to save your parameter. 5. On the Insert menu, click Group. 6.
10 Sorting, Grouping, and Totaling Grouping data The Record Sort Expert appears. Note: Sort fields that begin with “Group” specify that the sort was done automatically when the data was grouped. 2. Highlight the field you want the records within the groups sorted by and click the > arrow to add it to the Sort Fields list. Tip: The order of the fields in the Sort Fields box is the order by which data will be sorted. 3. Specify the sort direction. 4. Click OK when finished.
Sorting, Grouping, and Totaling Grouping data Using the Select Expert The Select Expert can be used to select groups of records in the same way that you select individual records. When you are setting up group selection criteria, instead of basing the selection criteria on standard fields, as you do for record selection, you base the criteria on group name fields or summary fields.
10 Sorting, Grouping, and Totaling Grouping data Note: If you have not already previewed the report or refreshed the data, there will not be any data saved with the report. Without the data, the program cannot calculate group values; thus, no values appear when you click the arrow in the right drop-down list. In this situation, you will have to type in the values you want. If you want real values to work with, you will need to preview your report first.
Sorting, Grouping, and Totaling Grouping data u 1. To correct a group selection formula Use Xtreme.mdb to create a report that includes the following fields: {customer.CUSTOMER NAME} {customer.REGION} {orders.ORDER ID} {orders.ORDER AMOUNT} For each order, the report shows the customer that placed the order, the region in which that customer is located, the order ID number, and the amount of the order. 2. Group the report by the {customer.REGION} field. 3. Insert a summary that subtotals the {orders.
10 Sorting, Grouping, and Totaling Grouping data Grouping data in intervals You may want to group your data into intervals. Age groups, time periods, and sales categories are some of the interval groupings that can be created. In this example, you will rank customers by the amount of business they did in the previous year. This example uses specified order grouping. This kind of grouping lets you specify the records that will be included in each group.
Sorting, Grouping, and Totaling Grouping data 6. Type “Less than $10,000” in the Group Name field. This is the name that will appear as the Group Name field value for the group. 7. Since the first group is to contain only those records that have a Last Year’s Sales figure of less than $10,000, set the fields so your condition reads: 8. Click OK to return to the Specified Order Tab. 9. Click New. is less than 10000 The Define Named Group dialog box reappears. 10.
10 Sorting, Grouping, and Totaling Grouping data Grouping by the first letter of a company name You might want to break your data into groups based on the first letter of the company name. For example, in a customer list you might want all the “A” customers in a group, then all the “B” customers, and so forth. To do this requires the use of a formula. Do not worry if you are unfamiliar with formulas. This text will show you what formula you need here and how to enter it.
Sorting, Grouping, and Totaling Grouping data You return to your report with the data grouped by the formula field as specified. The data is broken into groups based on the first letter in the customer’s name. The formula provides a live header for every group. For more information on live headers, see “Live headers” on page 198.
10 Sorting, Grouping, and Totaling Grouping data Employee (child) Supervisor (parent) Teresa Thomas Gareth Thomas Valerie Thomas Gillian Mina Frances Gillian Ruth Gillian Margaret Mina Paul Margaret Charles Margaret The Employee and Supervisor fields contain overlapping data that implies a hierarchical relationship. There are 15 unique employee names, with four of these names also appearing as supervisors (Mina, Thomas, Gillian, and Margaret).
Sorting, Grouping, and Totaling Grouping data u To group data hierarchically 1. Create or open a report that contains the data you want to group and sort hierarchically. 2. On the Insert menu, click Group. 3. In the Insert Group dialog box, select the field that you want to use as the basis of your hierarchy (the child field). For example, if you want to see the hierarchical structure of a company’s employees, select the employee field. 4. Select in ascending order.
10 Sorting, Grouping, and Totaling Grouping data The group you created is added to the report. 7. On the Report menu, click Hierarchical Grouping Options. 8. In the Available Groups list of the Hierarchical Options dialog box, select the group you want to organize hierarchically. 9. Select the Sort Data Hierarchically check box. 10. In the Parent ID Field list, select the field by which you want the Instance ID Field organized.
Sorting, Grouping, and Totaling Grouping data 11. In the Group Indent field, enter the amount you want to indent for each subgroup. Note: The value that you enter in the Group Indent field affects all other objects that are in the same area as your hierarchical group. For example, if your report contains a salary field on the same line as the name of the employee, the salary field is also indented when you use the employee field to create a hierarchical group.
10 Sorting, Grouping, and Totaling Grouping data The report is now grouped by employee name and further sorted to show the supervisor hierarchy. You can see that Mina, whose employee data had no supervisor data associate with it, has been sorted at the top of the list. Under Mina’s name is each supervisor who reports to her; and under each of those supervisors is a list of employees who reports to him or her.
Sorting, Grouping, and Totaling Summarizing grouped data Editing groups u 1. To edit a group On the Report menu, click Group Expert. 2. In the Group By list of the Group Expert dialog box, select the group you want to edit. 3. Click Options. 4. In the Change Group Options dialog box, edit the group as necessary. 5. Click OK to close the Change Group Options dialog box and again to close the Group Expert dialog box. The report reflects the changes you have made to the group.
10 Sorting, Grouping, and Totaling Summarizing grouped data u To summarize grouped data 1. On the Insert menu, click Summary. The Insert Summary dialog box appears. 2. Select the desired field to summarize from the Choose the field to summarize list. 3. Select a summary operation from the Calculate this summary list. 4. Select a location in which to place the summary from the Summary location list. Tip: You can create a new group for you report by clicking the Insert Group button. 5.
Sorting, Grouping, and Totaling Summarizing grouped data 2. Click the tab for the group you want to sort. 3. Select the All option from the drop-down list on the left. 4. Choose the summary that you want to base your selection on from the “based on” drop-down list on the right. The “based on” drop-down list on the right is for those cases in which you have multiple summaries within a single group section.
10 Sorting, Grouping, and Totaling Summarizing grouped data 1. Create a report and summarize the data as desired. When you summarize the data, the program breaks the data into groups and summarizes each group. • • 2. With top N groups, you are instructing the program to display those groups that have the highest summary values (Top N). With bottom N groups, you are instructing the program to display those groups that have the lowest summary values (Bottom N).
Sorting, Grouping, and Totaling Summarizing grouped data 6. Click the Include Others, with the name check box and enter a name if you want to group all the other records into a single group. 7. Select Include ties to accommodate groups whose summarized values are equal.
10 Sorting, Grouping, and Totaling Subtotaling 6. In the Group Sort Expert, select the type of group sort that you want. Choose any option except No Sort or All. 7. Click the Conditional Formula button next to the N or Percentage value field. 8. In the Formula Workshop, enter the parameter field that you created in step 3. 9. Click Save and close to return to your report. 10. Click OK to save your group sort. 11.
Sorting, Grouping, and Totaling Subtotaling 3. Click Insert Group. The Insert Group dialog box appears so you can specify the group you want to add to your report. 4. Choose the field you want the data grouped by, specify a sort direction and then click OK when finished. 5. On the Insert Summary dialog box, select the group you just created from the Summary location list and then click OK. The values in each group are now subtotaled.
10 Sorting, Grouping, and Totaling Percentages The Insert Summary dialog box appears. 9. Click Insert Group and create a group on the {orders.ORDER DATE} field. 10. Choose for each week as the group interval. Note: “The section will be printed” drop-down box will not be activated until you choose the Order Date field. 11. Click OK to return to the Insert Summary dialog box. 12. Select the group you just created from the Summary location list and then click OK.
Sorting, Grouping, and Totaling Group headers 4. Select the summary location. Note: The summary location cannot be Grand Total (Report Footer) when you are calculating a percentage. 5. Click the Show as a percentage of check box. 6. Select the group you want the percentage based on. You can choose to show a percentage of a group within another group, or show a percentage of the grand total. 7. Click OK. The summary percentage field is added to your report.
10 Sorting, Grouping, and Totaling Group headers • • “Live headers for groups based on a formula” on page 200 “Headers for custom groups” on page 200 Standard headers A standard header is a block of text that is used to identify each group in a rather generic kind of way. “Customer,” “State,” and “Monthly Orders” are all examples of this kind of header.
Sorting, Grouping, and Totaling Group headers Group name only The easiest live header to create is one based on identifying the value of the group field. u 1. To create a live header by group name only On the View menu, click Field Explorer. 2. In the Field Explorer dialog box, expand the Group Name Fields folder. 3. Select the Group Name field that matches the group you are working with and drag it into the Group Header section for that group.
10 Sorting, Grouping, and Totaling Group headers Live headers for groups based on a formula When you create a group and use a formula field as a sort-and-group-by field, the program automatically creates a group name field based on the value returned by the formula. For example, if you create this formula: {customer.CUSTOMER NAME}[1] and then group on the formula, the program will group your data based on the first letter in the Customer Name field.
Sorting, Grouping, and Totaling Group headers Suppressing group headers You have the option to hide group headers in your report. u 1. To suppress group headers Right-click the group header and select Format Field. 2. In the Format Editor, on the Common tab, click the Suppress check box. 3. Click OK. Tip: To show the group header again, clear the Suppress check box.
10 Sorting, Grouping, and Totaling Group headers 202 Crystal Reports User’s Guide
chapter Running Totals
11 Running Totals Understanding running totals Understanding running totals Running total fields are similar to summary fields but allow more control over how the total is calculated and when it is reset. Running total fields are specifically suited to perform the following totaling functions: • • • • • Show values of a total accumulate as it is calculated record by record. Total a value independent of the report’s grouping. Total a value conditionally.
Running Totals Creating running totals Details All records up to and including the current record Group Footer All records up to and including the last record in the current group Page Footer All records up to and including the last record on the current page Report Footer All records in the report Creating running totals Creating running totals in a list Running totals are totals that can be displayed on a record by record basis.
11 Running Totals Creating running totals 8. In the Reset section of the dialog box, click Never (this gives you a running total that never resets; that is, the running total continues throughout the report). 9. Click OK to save the running total field. The program returns you to the Field Explorer dialog box. 10. Insert the running total field in the Details section of the report, just to the right of {orders.ORDER AMOUNT}.
Running Totals Creating running totals 7. Select sum from the Type of summary list. 8. In the Evaluate section of the dialog box, click For each record. 9. In the Reset section of the dialog box, click On change of group and accept the default group name. 10. Click OK to save the running total field. You return to the Field Explorer dialog box. 11. Place the running total field in the Details section of your report, just to the right of the {orders.ORDER AMOUNT} field.
11 Running Totals Creating running totals 3. Sort the records based on the {customer.CUSTOMER NAME} field. 4. On the View menu, click Field Explorer. The Field Explorer dialog box appears. 5. Select Running Total Fields and click New. The Create Running Total Field dialog box appears. 6. Enter the name “USTotal” in the Running Total Name box. 7. Highlight {Customer.
Running Totals Creating running totals Creating running totals in a one-to-many linking relationship A one-to-many linking relationship occurs in linked tables when a single record in one table can be matched with many records in another table. For example, a one-to-many link might occur when you link a customer table to an orders table. It wouldn’t be unusual in such a case for each customer in the primary table to have many orders in the second (lookup) table.
11 Running Totals Creating running totals using a formula 9. In the Evaluate section of the dialog box, click On change of field and add the {Customer.CUSTOMER NAME} field from the Available Tables and Fields box. 10. In the Reset section of the dialog box, click On change of group and choose Group #1: Customer.Customer Name - A. 11. Click OK to save the Running Total field. 12. Place the running total in the Group Footer section. Compare the running total amount with the subtotal amount for each group.
Running Totals Creating running totals using a formula 5. Enter the following into the Formula box: WhilePrintingRecords; CurrencyVar Amount; Amount := Amount + {orders.ORDER AMOUNT}; 6. Click the Save and close button on the Formula Workshop. 7. Place this formula in the Details section of your report, just to the right of the {orders.ORDER AMOUNT} field. 8. On the Insert menu, click Group and group the report on the {customer.CUSTOMER NAME} field. 9.
11 Running Totals Creating running totals using a formula Note: This formula prints the same value that @RunningTotal prints as the running total for the last record in each group. But since it is printing it in the Group Footer section, it acts as a group subtotal, not as a running total. On your report, each row in the running total column displays the current record value added to the previous values.
chapter Multiple Section Reports
12 Multiple Section Reports About sections About sections Crystal Reports provides five design areas to use when building your report: • • • • • Report Header Page Header Details Report Footer Page Footer Each area contains a single section when you first create a new report. You cannot delete any of these original sections but you can hide them or add to them. Once you have added sections, you can delete them, move them in relation to other similar sections, or merge related sections together.
Multiple Section Reports Working with sections 2. Highlight the section you want to insert a section after. For example, to add another Details section, highlight the existing Details section. 3. Click Insert. A new section is inserted immediately below the highlighted section. Deleting a section 1. Click Section Expert on the Expert Tools toolbar. The Section Expert appears with a list of all the sections in the report.
12 Multiple Section Reports Working with sections 2. With Section (C) highlighted, click the up arrow twice. The data originally in Section (C) is moved to Section (A). The data in the other sections is moved down. Note: • • • You can only move a section up or down within an area. The letters that identify the sections describe their relative (as opposed to original) position. Thus, if you move a “C” section up, it becomes a “B” section. It loses its original “C” designation.
Multiple Section Reports Splitting and resizing sections 4. With Section (B) highlighted, click Merge and Section (C) will be merged with Section (B) to form one section. 5. Rearrange the objects as needed. Splitting and resizing sections A section can be split into two or more sections and/or resized easily in the Design tab. Splitting a section 1. Move the pointer over the left boundary of the section you want to split. 2. When the pointer becomes a Section Splitting cursor, click the boundary.
12 Multiple Section Reports Using multiple sections in reports Resizing a section to remove white space If you have one or more objects in a section and you want to resize the section to remove unnecessary white space, right-click in the shaded area to the left of the section in the Design and Preview tabs and choose Fit Section from the shortcut menu. The program automatically resizes the section, moving the bottom boundary of the section to the baseline of the bottom object in the section.
Multiple Section Reports Using multiple sections in reports • Adding blank lines under specific conditions (see “Adding blank lines conditionally” on page 220). Once you understand the power of multiple sections, you will discover even more ways to produce the effects you want.
12 Multiple Section Reports Using multiple sections in reports Eliminating blank lines when fields are empty It is very common to have two address lines in a customer table, one for street address (Address 1), and one that can be used for a suite number or mail stop (Address 2). Address 1 usually contains a value, but Address 2 is often blank.
Multiple Section Reports Form letters This formula divides each record number by 5. If the division produces a remainder, it suppresses the blank section. But if the division produces no remainder, a zero (which it will for every fifth record printed), the program prints the second section, thus inserting a blank line. Note: To insert a blank line under different conditions, you can modify your formula appropriately. See “Working with conditional formatting” on page 270.
12 Multiple Section Reports Form letters In this mode, you can resize the object by dragging any of the resizing handles, or you can move it by placing the cursor inside the object and dragging it to a new location. You can also insert fields in this mode, but you cannot insert text. You put a text object into the move/resize mode by clicking it when it is inactive.
Multiple Section Reports Form letters Note: It is critical that you see the Drag and drop cursor before placing the field. If it does not appear, you might place the field so that it overlays the text object instead of being inserted into it. It may appear to be inside the text object, but if you move the object the field will not move with it. • The insertion point is tied into the Drag and drop cursor.
12 Multiple Section Reports Form letters 4. Click the text object frame to put the object in move/resize mode. 5. Drag the resizing handle on the right side of the object to the right edge of the Design tab. This will make the object about eight inches wide, the approximate width of a page. You may have to stop resizing, scroll the window, and resize some more to accomplish this. 6. Double-click inside the text object to place it in edit mode. It is now ready for you to begin your work.
Multiple Section Reports Form letters 3. Press Enter twice to insert some white space between the date and the inside address and to move the insertion point down within the text object. Tip: You might have to resize the Details section and the text object if you have not selected the Can Grow option for the text object in the Format Editor. Creating an inside address 1. To create the inside address, drag database fields into the text object from the Customer table in the Field Explorer dialog box. 2.
12 Multiple Section Reports Form letters Creating a salutation 1. Press Enter four times to move the insertion point down. 2. Type in the word “Dear” and a space (do not include the quotation marks). 3. In the Field Explorer dialog box, highlight the Contact Title field from the Customer table and drag it into the text object, placing it immediately after the space. 4. Insert a space. The program positions the insertion point immediately after the space. 5.
Multiple Section Reports Form letters Creating the letter body 1. Now type “Your company” (do not include the quotation marks) and type a comma followed by a space. 2. Drag the Customer Name field into the text object and place it at the insertion point, just after the space. 3. Type a comma, followed by a space. 4. Type the following text (do not include the quotation marks): “helped make this year an outstanding year for Xtreme Mountain Bikes, Inc.
12 Multiple Section Reports Form letters It should look similar to this: Printing conditional messages in form letters It is likely that you will want to print conditional messages in form letters. For example, you may want to encourage customers with available credit to buy more and those who are over their credit limit to bring their accounts down below the limit once again. You can create both of these letters within a single report. u To create a conditional message 1.
Multiple Section Reports Form letters Now, when a record indicates available credit, the buy more letter will print. When the account is over the credit limit, the over limit letter will print. And when the customer is right at the credit limit, nothing will print at all.
12 Multiple Section Reports Form letters 230 Crystal Reports User’s Guide
chapter Formatting
13 Formatting Formatting concepts Formatting concepts This section explains how to format a report. Formatting refers to changes you can make to the layout and design of a report, as well as the appearance of text, objects, or entire report sections. You can use formatting to do many things, including: • • • • • Dividing sections of a report. Calling attention to certain data. Changing the presentation of dates, numbers, Boolean values, currency values, and text strings. Hiding unwanted sections.
Formatting Using a template By default, the sample templates shipped with Crystal Reports are installed at \Program Files\Business Objects\Crystal Reports 11\Templates. 4. If you want to apply a template based on an existing Crystal report, click Browse. 5. In the Open dialog box, select a Crystal report (.rpt) file and click Open. The report is added to the list of Available Templates.
13 Formatting Using a template 2. Choose Undo the current template and click OK. The chosen template’s features are removed and your report reverts back to the formatting it had when you first opened it. Note: To remove a template, you must use this option; the Undo command is not available from the Edit menu. Reapplying the last template selected If you want to reapply the last template you selected during a session of Crystal Reports, you can simply select an option on the Template Expert.
Formatting Using the Report Design Environment Tip: You can select multiple Template Field Objects and apply your formatting choices to all of the objects. 4. Specify the formatting for your Template Field Object as you require. For information about how to apply formatting, see “Working with absolute formatting” on page 260. For each Template Field Object you create, a special formula field is created. You can see this formula field in the Formula Workshop.
13 Formatting Using the Report Design Environment • • • • • • • • • Selecting multiple objects Free-form placement Vertical placement Inserting character and line spacing Setting fractional font sizes TrueType fonts Page margins Default printer Printer drivers Section characteristics A report consists of several sections, including the Report Header, Page Header, Group Header, Details, Group Footer, Page Footer, and Report Footer. Each report section is made up of a series of lines.
Formatting Using the Report Design Environment Making an object underlay a following section Using this example, you can make the Xtreme logo (Xtreme.bmp) underlay multiple sections. This procedure is similar to the one for inserting a company watermark to serve as a report background. To make an object underlay a following section, first place the object in the section above the section you want it to underlay.
13 Formatting Using the Report Design Environment u To make the picture underlay the following section 1. On the Report menu, click Section Expert. The Section Expert dialog box appears. Tip: Another way to do this is to click the Section Expert button on the Expert Tools toolbar. 2. In the Sections list, click Page Header, then select the Underlay Following Sections check box. 3. Click OK to preview the report again.
Formatting Using the Report Design Environment Multiple columns Instead of having your data print straight down the page, you can set up multiple columns and have the data flow from column to column. u 1. To create a multiple-column report Open the report you want to format with multiple columns. 2. On the Report menu, click Section Expert. Tip: Another way to do this is to click the Section Expert button on the Expert Tools toolbar. 3.
13 Formatting Using the Report Design Environment Suppress (No Drill-Down) The Suppress property also hides a section when you run the report. Unlike the Hide property, however, you cannot apply the Suppress property, then drill down to reveal the section contents. This property can be applied absolutely, or conditionally using a formula. This is useful for writing form letters.
Formatting Using the Report Design Environment Suppress If Zero (Number tab) Tip: To find this option, click the Number tab of the Format Editor, then click the Customize button. The Suppress If Zero property prevents a value from printing if it is a zero value. The value does not print, but the space in which it would have printed remains. To remove the blank space, select the Suppress Blank Section check box in the Section Expert. Note: This will only work if there are no other objects in the section.
13 Formatting Using the Report Design Environment Placing text-based objects When a text-based object is placed on a report, the object is represented by an object frame. The height of the object frame is based on the height of the font. The width, however, is determined differently, depending on the object you are working on.
Formatting Using the Report Design Environment 3. On the Common tab, select the Can Grow check box. 4. Click OK to save your changes. The object is then formatted to print on multiple lines. If the text prints wider than the object, the text wraps onto additional lines. Preventing breaks in non-spacing text inside an object For text strings that do not contain spaces, such as single words, the text string is broken at the edge of the object frame before the line starts to wrap. u 1.
13 Formatting Using the Report Design Environment Note: The suppression of embedded field blank lines is designed to remove blank lines within a text object if the text object contains an entirely blank field alone on a line followed by a carriage return. u To suppress blank lines in embedded fields 1. Open your report in the Design tab, and click the desired text object— that is, the text object that causes blank lines to show for some records.
Formatting Using the Report Design Environment So, when possible, place multi-line text-based objects at the bottom of a section. If they require more lines to print, the section expands downward to accommodate the growth, and they do not endanger other objects. Importing text-based objects from a file Using Crystal Reports, you can import a formatted, text-based object from an existing file onto your report. u 1.
13 Formatting Using the Report Design Environment these coordinates, not between them. You can then space data on your report and align objects as needed. If you attempt to place an object between grid coordinates, the object “snaps” to the grid; that is, the object automatically moves to the nearest set of row and column coordinates. Each report contains a design grid. You can select the grid on or off, as well as set it to different sizes when required. By default, the grid is not selected.
Formatting Using the Report Design Environment Viewing guidelines You can view guidelines on the Design and Preview tabs by selecting the view options in the Options dialog box. u 1. To view guidelines On the File menu, click Options. The Options dialog box appears. 2. On the Layout tab, in the Design View area, select the Guidelines check box and/or the Guidelines check box in the Preview area. 3. Click OK to save your changes.
13 Formatting Using the Report Design Environment 2. To position a guideline, drag its arrowhead along the ruler to the desired location. 3. To delete a guideline, drag its arrowhead away from the ruler. Note: If you select the Snap To Grid option, you can only insert or move guidelines in grid increments. Snapping objects to guidelines To snap an object to a guideline, drag the report object onto the guideline until the object’s edge sits atop the guideline.
Formatting Using the Report Design Environment You can tell if a text-based object is snapped to a horizontal guideline by looking for the special indicators positioned on either side of the object directly at the baseline (as circled in the image below) in Design view. If the object is snapped to a vertical guideline, the special indicators appear along the side of the object.
13 Formatting Using the Report Design Environment u To resize objects using guidelines 1. Create a guideline. 2. Snap one side of the object to that guideline. 3. Create a second guideline to the right of the object. Note: The guideline should not be touching the object. 4. Click the object to activate the sizing handles. 5. Drag the resizing handle over to the second guideline so that the object snaps to the guideline. 6.
Formatting Using the Report Design Environment 4. In the Indentations area, you can indent the first line of the paragraph; indent every paragraph line from the left margin; and indent every paragraph line from the right margin. Note: Only indentation values within the range of the field or object width are accepted. 5. Click OK to save your changes.
13 Formatting Using the Report Design Environment Selecting multiple objects You can select multiple objects, including text, field, chart, map, bitmap, OLAP grid, Cross-Tab and OLE objects, to format them together. Once you have selected multiple objects, you can move, align, size, cut, and copy and paste them as a group. You can also change their font, color, and paragraph style. Objects are moved, aligned, and sized based on a “main” object, which is usually the last object you select.
Formatting Using the Report Design Environment When a section is formatted with free-form placement, all objects can be placed anywhere on that section. Crystal Reports places objects within a section based on their absolute coordinates. These coordinates determine the vertical placement of objects on your report. What this means is that you control the vertical placement of single-line, text-based objects rather than the printer driver.
13 Formatting Using the Report Design Environment Note: The value is a measurement from the upper-left corner of the section; so, the greater the value, the farther down the page the object prints. If you are distributing reports that have sections formatted with the Free-Form Placement option turned off, and you want the objects in those sections to be surrounded by a border or formatted with lines, it is better to modify the objects’ border properties than to insert lines and boxes.
Formatting Using the Report Design Environment Note: • • • If text rotation is left at 0 degrees, your report is horizontally formatted, left to right. For text rotation of text-based objects, the Can Grow option that prevents the truncation of text inside an object is automatically cleared. For more information on the Can Grow option, see “Preventing the truncation of text inside an object” on page 242.
13 Formatting Using the Report Design Environment Setting fractional font sizes On the Font tab of the Format Editor, you can select a fractional font size for database fields and text-based objects on your report. u To set fractional font sizes 1. Right-click the field or object you want to format to bring up the shortcut menu. 2. On the shortcut menu, click Format Field. The Format Editor dialog box appears. 3. Click the Font tab. 4.
Formatting Using the Report Design Environment Page margins Setting specific margins Crystal Reports gives you the option of setting margins to meet your specifications. u 1. To set specific margins On the File menu, click Page Setup. The Page Setup dialog box appears. 2. Change the default page margins to fit your needs. 3. Click OK to save your changes. Note: All margins are calculated from the paper edge. Thus, a left margin of .
13 Formatting Using the Report Design Environment For example, an HP Laser III printer is being installed on two different operating systems. • • With Microsoft Windows 98, the printer name can be changed so that HP Laser III is Front Reception Printer, but the printer driver will be listed as HPPCL5MS.DRV. With Microsoft Windows NT, the printer name is also referenced and can be changed by the user, but the printer driver is always WINSPOOL.
Formatting Using the Report Design Environment Printer drivers Updating printer drivers In order to maintain performance, Crystal Reports queries the printer driver for each of the font elements (font metrics), such as average character height, character width, height of the ascenders and descenders, internal leading, and so on. A problem may develop if using an older printer driver that does not return the font metrics accurately.
13 Formatting Formatting properties For the most part, this situation cannot be avoided. The goal of the report distributor is to design reports that accommodate printer driver dependency and still print consistently using different printer drivers. To do this, Crystal Reports provides several design solutions. If taken into account when creating your report, these solutions can ensure proper printing and distribution for your report in almost any environment.
Formatting Working with absolute formatting The Format Editor dialog box appears. 3. Click the Border tab. 4. Select the line style, color, and background color of the field. 5. Click OK to save your changes. Making a report, section, area, or object read-only You can make a report, section, area, or object read-only so it can’t be formatted. When you set this option, choices in the Format Editor become inactive.
13 Formatting Working with absolute formatting Locking an object’s size and position You can lock the position of the selected report object so it can’t be moved. When you set this option, you cannot drag the object in the Report Designer and the Size and Position command becomes inactive. u To lock an object’s size and position 1. Select the object whose size and position you want to lock. 2. On the Formatting toolbar, click Lock Size/Position.
Formatting Working with absolute formatting u To set standard default formats for Date, Time, and Date and Time fields 1. On the File menu, click Options. 2. In the Options dialog box, click the Fields tab. 3. To open the Format Editor, click the button appropriate to the field you want to format (Date, Time, or Date and Time). Note: If you click the Date and Time button in the Format Editor, then any subsequent changes will affect “Date and Time” fields only.
13 Formatting Working with absolute formatting 8. Click OK in the Format Editor dialog box. 9. To format another type of field, click the appropriate button in the Options dialog box. Otherwise, click OK to return to Crystal Reports. Now, when you add Date, Time, or Date and Time fields to your reports, Crystal Reports should use the customized format that you created.
Formatting Working with absolute formatting u To add boxes to a report 1. On the Insert Tools toolbar, click Insert Box. 2. Use the pencil cursor to draw the box where desired. u 1. To edit boxes on a report Right-click the box you want to format to bring up the shortcut menu. 2. On the shortcut menu, click Format Box. The Format Editor dialog box appears. 3. On the Box tab, make the desired changes to the box. 4. Click OK to save your changes.
13 Formatting Working with absolute formatting Using conventional accounting formats As a way of supporting the conventions used in the accounting profession, Crystal Reports lets you decide on how to display the currency symbol, negative values, and zero values on your financial reports. You can also set up your report to reverse the signs for credit and debit amounts. u To use accounting conventions in a report 1.
Formatting Working with absolute formatting 5. Select the Reverse Sign for Display check box to reverse the signs for debit and credit amounts in your financial reports. 6. Click OK to save your changes. 7. Click OK again to return to your report. Repeating report objects on horizontal pages Some report objects, such as cross-tabs and OLAP grids, can expand horizontally over a number of pages.
13 Formatting Working with absolute formatting Numbering horizontal pages A Special Field called Horizontal Page Number lets you number horizontal pages. u To number horizontal pages 1. In the Field Explorer, scroll down to Special Fields and expand the node by clicking it. 2. Choose Horizontal Page Number from the list and insert it in your report. Note: The Repeat on Horizontal Pages option is automatically turned on when you insert the Horizontal Page Number field this way.
Formatting Working with absolute formatting When the Resizing cursor appears, drag the section boundary downward to add extra white space. Note: White space can also be added to a section by right-clicking the shaded area to the left of the section and selecting Insert Line from the shortcut menu. The program resizes the section automatically, adding the amount of space necessary to hold a line of typical database fields.
13 Formatting Working with conditional formatting Deleting white space by suppressing a section If an entire section is blank (for example, if you are not putting anything into the Page Footer section of the report), you can eliminate the unnecessary white space that the Page Footer would occupy by suppressing the section in the Section Expert. u To delete white space by suppressing a section 1. On the Report menu, click Section Expert. The Section Expert dialog box appears.
Formatting Working with conditional formatting Conditional on or off properties A conditional on or off property tests to see if a condition has been met. It is on if the condition is met, off if the condition is not met. There is no middle ground. Use Boolean formulas for this kind of formatting. Crystal syntax example condition Basic syntax example formula = condition The program tests each value to see if it meets the condition and it returns a “yes” or “no” answer.
13 Formatting Working with conditional formatting Note: Always include the Else keyword in conditional formulas; otherwise, values that don’t meet the If condition may not retain their original format. To retain the original format of values that don’t meet your If condition, use the DefaultAttribute function.
Formatting Working with conditional formatting Use a multi-condition If-Then-Else formula for this kind of conditional formatting. Changing fonts conditionally For memo or string fields that are based on conditions such as a parameter value, you can change the font, font style, size, and color for these fields using the Format Editor. u 1. To change fonts conditionally Right-click the field you want to format to bring up the shortcut menu. 2. On the shortcut menu, click Format Field.
13 Formatting Working with conditional formatting u To conditionally change the X position of an object 1. Right-click the field that you want to move conditionally, and select Size and Position. 2. Click the Conditional Formula button next to the X position value field. 3. In the Formula Workshop, enter your conditional X position formula text. For example, to move orders that were shipped late to a second column, enter formula text such as this: If (Orders.
Formatting Working with conditional formatting Basic syntax example: formula = PageNumber = 1 This formula suppresses the page footer on the first page, but not on any of the other pages. 6. Click Save and close. Note: 7. • If there is an error in the formula, a message box appears, asking if you want to save anyway. If you click No, a second message box is displayed, detailing the error. • If there is no error in the formula, you are returned to the Section Expert.
13 Formatting Working with conditional formatting • • • • Highlight a cross-tab or OLAP cell based on row and column heading values. Enter values using your locale-specific number format (such as 1,224.23 for North American users). Enter dates numerically or textually (January 12, 2001, or Jan 12, 2001). Undo highlighting quickly. When you need to conditionally format report fields, the Highlighting Expert is quicker and easier to use than the Formula Workshop.
Formatting Working with conditional formatting 2. In the Highlighting Expert, click New to create a new conditional formula with default settings. 3. In the Item editor area, click the Value of list and select the field that you want to base your condition on. The field chosen here is the field upon which your condition is based; this field need not be the field that is being formatted. To create a condition based on the values of the field that is being formatted, select “this field” from the list.
13 Formatting Working with conditional formatting 8. Use the Priority arrows to specify the order in which you want Crystal Reports to apply your conditions. For details, see “Setting highlighting priorities” on page 278. 9. Click OK to return to your report. Setting highlighting priorities The Priority buttons in the Item list area of the Highlighting Expert allow you to set priorities for your formulas.
Formatting Using the Format Painter The Undo and Redo buttons have lists that allow you to undo or redo a number of changes at one time. • To undo an action, click Undo on the Standard toolbar. The first time the button is clicked, it reverses the most recent change made to the report. Each additional time the button is clicked, it reverses the next most recent change. To undo several actions at once, click the arrow button to display the list of actions. Select the series of actions you wish to undo.
13 Formatting Using the Format Painter • When using a “Date and Time” field as your source, a target field’s date or time properties are changed; the reverse is also true (that is, a Date field or a Time field used as your source also affects the date and time properties of a “Date and Time” field). u To copy and apply formatting 1. Select a source object or field in your report and click Format Painter. Tip: You can also select Format Painter from the shortcut menu. Note: • • 2.
chapter Charting
14 Charting Charting concepts Charting concepts Charting overview Crystal Reports enables you to include sophisticated, colorful charts in your reports. You can use charts any time you want to improve the usefulness of a report. For example, if you have a sales report grouped by Region with a subtotal of Last Year’s Sales for each region, you can quickly create a chart that will display Sales per Region. You can chart on the following: • • • • Summary and subtotal fields.
Charting Charting concepts Advanced Use the Advanced layout when you have multiple chart values or when you do not have any group or summary fields in the report. The Advanced chart layout supports one or two condition fields: with these condition fields, you can create a 2-D, 3-D, or pie chart. Other specific functions with the Advanced layout include: • • • • Values can be grouped in ascending, descending, or specified order, as well as by Top N or Sort totals. Values can be plotted for each record.
14 Charting Charting concepts • Stacked bar chart A Stacked bar chart displays data as a series of vertical bars. This type of chart is best suited for representing three series of data, each series represented by a color stacked in a single bar (for example, sales for 1997, 1998, and 1999). Line A line chart displays data as a series of points connected by a line. This type of chart is best suited for showing data for a large number of groups (for example, total sales over the past several years).
Charting Charting concepts XY Scatter An XY Scatter chart is a collective of plotted points that represent specific data in a pool of information. The XY Scatter chart allows the user to consider a larger scope of data for the purpose of determining trends. For example, if you input customer information, including sales, products, countries, months, and years, you would have a collective of plotted points that represents the pool of customer information.
14 Charting Charting concepts Gantt A Gantt chart is a horizontal bar chart often used to provide a graphical illustration of a schedule. The horizontal axis shows a time span, while the vertical axis shows a series of tasks or events. Horizontal bars on the chart represent event sequences and time spans for each item on the vertical axis. You should use only date fields when creating a Gantt chart.
Charting Creating charts Drill-down with legends If the chart consists of one or more group fields, you can use the chart legend to drill down on individual groups. Double-click the drill-down cursor on the markers and text in the legend to view the details about that section of the chart. Creating charts When you insert a chart in a report, you may see one of the following options: • A chart object frame in the Report Header. Once you have placed the chart frame, the Chart Expert dialog box appears.
14 Charting Creating charts Value The Advanced chart uses the value to indicate what information is plotted as the points on your chart. For example, to create a chart showing last year’s sales for your customers, the Last Year’s Sales field would be the value. u To chart on a details or formula field 1. On the Insert menu, click Chart. An object frame appears in the Report Header area. Tip: Another way to create a chart is to click the Insert Chart button on the Insert Tools toolbar. 2.
Charting Creating charts The arrow buttons on the Chart Expert dialog box allow you to move fields from one list to the other. Single arrows move only the selected field; double arrows move all fields at the same time. 7. Add the database fields you want to use as values to the Show value(s) list. 8. If you do not want Crystal Reports to automatically summarize the chart values for a formula field, select the Don’t summarize check box. 9.
14 Charting Creating charts 4. Click the Data tab. 5. In the Layout area, click Group, if it is not already selected. 6. In the Data area, in the On change of list, click the group field you want to base your chart on; then, in the Show list, click the summary field you want to display on your chart. 7. If the Axes and Options tabs appear, you can customize some of the chart’s properties, such as the scaling of the axes, the legend, and the data points. 8. Click the Text tab. 9.
Charting Creating charts Click the chart subtype that best illustrates your data. See “Chart types” on page 283. 5. Click the Data tab. 6. In the Layout area, click Cross-Tab, if it is not already selected. 7. In the Data area, in the On change of list, click the group field you want to base your chart on. 8. If necessary, in the Subdivided by list, click a secondary row or column you want to base your chart on. 9. In the Show list, click the summary field you want to display on your chart. 10.
14 Charting Working with charts 4. On the Type tab, in the Chart type list, select a chart type. Then click the chart subtype that best illustrates your data. See “Chart types” on page 283. 5. Click the Data tab. 6. In the Layout area, click the OLAP button, if it is not already selected. 7. In the Data area, in the On change of list, click the field you want to base your chart on. 8. If necessary, in the Subdivided by list, click a secondary row or column you want to base your chart on.
Charting Working with charts Editing charts using the Chart Expert Editing charts with the Chart Expert allows you to return to the expert in which you designed your chart. You can modify many of your original choices, such as the type of chart to display, the data on which the chart is based, and so on. u 1. To edit a chart using the Chart Expert Right-click the chart to bring up the shortcut menu. 2. On the shortcut menu, click Chart Expert. 3.
14 Charting Working with charts Editing charts using other menu items u To apply a new template 1. Right-click your chart and select Load Template from the menu. 2. The Custom templates dialog box appears. The options on the Custom tab represent directory locations under \Program Files\Common Files\Business Objects\3.0\ChartSupport\Templates where custom chart files are stored. Note: The custom charts are available only if you selected Custom Charting when installing Crystal Reports.
Charting Working with charts Using the zooming features with bar and line charts On the Preview tab, you can find commands for zooming bar charts and line charts within your report. You have the ability to zoom in and out on these chart types at any time, with each time being referred to as instance-specific. If you decide to save the instance of the chart that has been zoomed in or out, you must save the data with the report. u 1.
14 Charting Working with charts Formatting charts Changing the border of a chart 1. On the Design or Preview tab, right-click the chart to bring up the shortcut menu. 2. On the shortcut menu, click Format Background. 3. On the Format Editor dialog box, click the Border tab to see its options. 4. Change the line style, color, or thickness. 5. Click OK to save your changes. Crystal Reports returns you to the report and implements your changes. Conditionally formatting a chart 1.
Charting Working with charts Changing the chart’s legend text 1. On the Preview tab, click the text in your chart’s legend to select it. 2. Right-click the legend text and select Edit Axis Label from the shortcut menu. 3. In the Label Aliasing dialog box, add the text you want to see in the Displayed Label field. 4. Click OK. Tip: Be sure to select the text and not the entire legend.
14 Charting Working with charts Crystal Reports returns you to the report. Your chart will now underlay the sections below it. 5. If necessary, move or resize the chart.
chapter Mapping
15 Mapping Mapping concepts Mapping concepts Mapping overview With Crystal Reports, you can include geographic maps in reports. Maps help you analyze report data and identify trends more efficiently. For example, you could create a map that shows sales by region. You would then be able to: • • • Use one of the five map types to analyze the data. Adjust the appearance and organization of the map (allowing you to better identify trends). Drill down on the map regions to view underlying data.
Mapping Mapping concepts Map types The Map Expert also provides five basic map types, each suitable for a different strategy of data analysis. When deciding which map type best fits your report, you should consider the type of data you want to analyze. For example, if you want the map to display one data item for each geographic division (city, state, country, and so on), then you might use a Ranged, Dot Density, or Graduated map.
15 Mapping Mapping concepts interval may or may not be equal, depending on the individual regions and their summary values. • Natural break This option assigns intervals using an algorithm that attempts to minimize the difference between the summary values and the average of the summary values for each interval.
Mapping Mapping concepts and an office with a sales figure of $20,000 might have a small circle. So, a Graduated map provides a more efficient representation of point data (e.g. Cities) than a Ranged map does, and it uses sized symbols rather than colors to distinguish variations in the values of the items it represents. Pie Chart A Pie Chart map displays a pie chart over each geographic area. The pie charts represent data items that make up a whole.
15 Mapping Creating maps Where to place a map When you choose where to place the map, you determine the amount of data that will be included in the map. For example, if you place the map in the Report Header section, the map includes data for the entire report. If you place it in a Group Header or Group Footer section, it displays group-specific data. This choice will also determine whether the map prints once for the entire report, or many times (once for each instance of a given group).
Mapping Creating maps Value The Advanced layout uses the value to indicate what information is mapped when the area on the map is highlighted. For example, to create a map showing last year’s sales for the countries, the Last Year’s Sales field would be the value. u 1. To map on a details field On the Insert menu, click Map. Tip: Another way to do this is to click the Insert Map button on the Insert Tools toolbar. The Map Expert dialog box appears. 2.
15 Mapping Creating maps 5. Add the database field you want to appear with each change to the On change of field. 6. Add the database fields you want to use as values to the Map values list. 7. If you do not want Crystal Reports to automatically summarize the map values for a formula field, select the Don’t summarize values check box. 8. Click the Type tab. 9. Click the map type that best illustrates your data (Ranged, Dot Density, Graduated, Pie Chart, or Bar Chart). See “Map types” on page 301.
Mapping Creating maps Mapping on group fields (Group layout) To map on a group, you can use the Group layout, in which you show a summary (such as Last Year’s Sales) on change of a geographic field (such as Region). In order to create a map using the Group layout, you must have at least one group and at least one summary field for that group. u 1. To map on a group On the Insert menu, click Map. Tip: Another way to do this is to click the Insert Map button on the Insert Tools toolbar.
15 Mapping Creating maps Mapping on Cross-Tab summaries (Cross-Tab layout) With the Cross-Tab layout, you can map on a Cross-Tab summary field. For example, with a Cross-Tab that shows the total number of items of a product sold in each region of the United States, you may want to include a map that shows the percentage of the total sales of the product provided by each region. In order to create a Cross-Tab map you must first have a Cross-Tab in your report.
Mapping Creating maps 12. In the Legend area, you can click one of the following options: • • • Full legend to show a detailed legend on your map. Compact legend to show a condensed legend on your map. No legend to exclude the legend from your map. 13. If you click Full legend, click Made by map to have Crystal Reports create a legend title based on your map, or click Specify to enter your own legend title and subtitle. 14. Click OK.
15 Mapping Working with maps 8. In the Options area, apply formatting options to your map. 9. Click the Text tab. 10. In the Map title field, enter a title for your map. 11. In the Legend area, you can click one of the following options: • • • Full legend to show a detailed legend on your map. Compact legend to show a condensed legend on your map. No legend to exclude the legend from your map.
Mapping Working with maps Editing maps using the Map Expert Editing maps with the Map Expert allows you to return to the expert in which you designed your map. You can modify many of your original choices, such as the type of map to display, the data on which the map is based, and so on. From either the Design or Preview tab, you can open the Map Expert and make your modifications. u 1. To edit a map using the Map Expert Right-click the map to bring up the shortcut menu. 2.
15 Mapping Working with maps 5. Click OK to save your changes. Crystal Reports returns you to the Preview tab and implements your changes. Changing map layers In Crystal Reports, maps are made up of a number of layers. You can stack these layers on top of each other to create a more detailed map. For example, you might want to see the names of major cities in every country; to do this, you could add a layer that contains city names.
Mapping Working with maps 4. In the Properties area, set the properties for each map layer, specifying whether the layer is visible and whether it is automatically labeled. The Visible option specifies whether the layer appears. The Automatic Labels option specifies whether a predefined label appears (for example, labels for the names of major world cities). 5. If necessary, click Display to open the Display Properties dialog box.
15 Mapping Working with maps Changing the geographic map If you prefer to have your data values presented with a different geographic map, specify your changes using the Change Map dialog box. u To change the geographic map 1. On the Preview tab, right-click the map to bring up the shortcut menu. 2. On the shortcut menu, click Resolve Mismatch. The Resolve Map Mismatch dialog box appears, displaying the name of the current map you are using, and a list of replacement maps that you can choose from. 3.
Mapping Working with maps Panning a map 1. On the Preview tab, right-click a map to bring up the shortcut menu. 2. On the shortcut menu, click Pan. 3. Drag the panning cursor to the desired map section. Centering a map 1. On the Preview tab, right-click a map to bring up the shortcut menu. 2. On the shortcut menu, click Center Map. Hiding and showing the Map Navigator The Map Navigator provides a small-scale version of the currently displayed map, so that you can select areas you’d like to pan.
15 Mapping Working with maps Formatting Maps Changing the border of a map 1. On the Design or Preview tab, right-click the map to bring up the shortcut menu. 2. On the shortcut menu, click Format Map. 3. On the Format Editor dialog box, click the Border tab to see its options. 4. Change the line style, color, background color, and add or remove a drop shadow from the map border. 5. Click OK to save your changes. Crystal Reports returns you to the report and implements your changes.
chapter OLE
16 OLE OLE overview OLE overview Object Linking and Embedding (OLE) enables you to insert objects (OLE objects) into a report from other applications (OLE server applications) and then use those applications from within Crystal Reports to edit the objects if necessary. If you were not using OLE, you would have to exit Crystal Reports, open the original application, change the object, return to Crystal Reports, delete the object originally inserted, and then insert the newly revised object.
OLE OLE overview • OLE Server Application An OLE server application is an application that allows its documents to be inserted into an OLE container document as OLE objects. Microsoft Word and Excel are examples of applications that are both OLE servers and OLE containers. That is, they can both create new OLE objects and they can contain OLE objects created elsewhere. • Server Document The server document is a file created in the server application that stores the original OLE object.
16 OLE Inserting OLE objects into reports • When you double-click a linked OLE object, the program opens the object’s server application with the object displayed and ready for editing. You cannot edit a linked object in place in Crystal Reports because you are working on the original object. Since the object could be linked to multiple documents, displaying the original in the server application limits access to one editor at a time.
OLE How OLE objects are represented in a report 8. Double-click the spreadsheet object. The menus and toolbars change to a combination of those from the OLE server application and Crystal Reports. You can now edit the object inplace. The object is an embedded object. Any changes you make to the object will not affect the original. 9. Click outside the object when you are finished. The menus and toolbars change back to those of Crystal Reports. Any changes you made while editing are saved.
16 OLE Working with static OLE objects Dynamic OLE menu commands When you have an OLE object selected, there is a submenu on the Edit menu that provides commands for the object. The name of this submenu reflects the type of the OLE object. The commands on the submenu also vary according to the type of object.
OLE Working with static OLE objects Note: Wide static OLE objects can span pages. This functionality may result in two or more horizontal pages in your report. If this do not want this effect, you should resize your static OLE object. As well, it is recommended that you do not use the Repeat on Horizontal Pages option for any static OLE object that will span several pages. u 1. To insert a static OLE object Open or create a report that you want to insert a static OLE object in. 2.
16 OLE Working with static OLE objects • Reports that use an absolute or a relative path for a dynamic static OLE object are not supported in a BusinessObjects Enterprise environment for this release. If you plan to publish reports that contain dynamic static OLE objects to BusinessObjects Enterprise, it is recommend that you use URL links for your OLE objects. u To make a static OLE object dynamic 1. Right-click the static OLE object that you inserted, and choose Format Graphic from the shortcut menu.
OLE Working with embedded vs. linked objects Working with embedded vs. linked objects Since embedded and linked objects each have different properties, it is important for you to consider the capabilities of each when deciding which OLE format to use. Embedded objects An embedded object can be created from within Crystal Reports or by using a file that already exists. If you use an existing file, the object is copied to the report.
16 OLE Working with embedded vs. linked objects Linked objects When a linked object is inserted into a report, Windows copies a snapshot of the data (not the data itself) from a file that already exists. The image of the object is added to your report along with a reference to the data used to create it. The actual data remains with the original file. When the object is activated from within a report, the original file is opened inside the application that was used to create it.
OLE Working with embedded vs. linked objects The second command from the bottom of the shortcut menu identifies this as a Linked Bitmap Image Object. 9. Double-click the object. Microsoft Paint or the graphics application you are using opens, displaying the original file. Any changes you make to the original will be reflected in the object that appears in your report. 10. Close Microsoft Paint or the graphics application that you are using and return to your report.
16 OLE Working with embedded vs.
chapter Cross-Tab Objects
17 Cross-Tab Objects What is a Cross-Tab object? What is a Cross-Tab object? A Cross-Tab object is a grid that returns values based on the criteria you specify. Data is presented in compact rows and columns. This format makes it easy to compare data and identify trends. It is made up of three elements: • • • Rows • The rows in a Cross-Tab run horizontally (from side to side). In the example above, “Gloves” is a row. The columns in a Cross-Tab run vertically (up and down).
Cross-Tab Objects Cross-Tab example value at the bottom of the “USA” column is four, the total number of products (gloves, belts and shoes) sold in the USA. Note: The total column can appear at the top of each column. • At the intersection of the Totals column (totals for the products) and the Totals row (totals for the countries) is a grand total. In the example above, the value at the intersection of the Total Column and Total Row is 12, the total number of all products sold in all countries.
17 Cross-Tab Objects Cross-Tab example This report presents details. Each row represents an individual order. There are many orders from each of the regions for different locks. But because there is no summary information, it is nearly impossible to get any useful information out of a report like this. The next logical step is to group the data in some way. You can group it by region, or by product line. The following section shows a look at both of these options.
Cross-Tab Objects Cross-Tab example Report of order data—grouped by product This report groups the data by product. Each group displays all the orders for a specific product. At first it appears that this might be useful, but then it becomes clear that each product group includes orders from several different regions. The information is helpful, and it brings you closer to your goal, but you are still a long way from having the information you need. Each product group contains orders for many regions.
17 Cross-Tab Objects Cross-Tab example Report of order data—grouped by region and product This report is the logical next step. If the By Region report contains multiple products in each region group, and the By Product report contains multiple regions in each product group, then it seems to make sense to combine the two. Doing that, you group first by Region and then by Product. Each group contains orders for one product for one region. But the data is all spread out and remains difficult to analyze.
Cross-Tab Objects Cross-Tab example Regions Total - one product in all regions Product names Total - one product in one region Total - all products in one region Total - all products in all regions In this Cross-Tab: • • • • • • Product names make up the row headings. Regions make up the column headings.
17 Cross-Tab Objects Creating a Cross-Tab report Creating a Cross-Tab report This section provides you with the steps to create a Cross-Tab object in a new report and how to add a Cross-Tab object to an existing report. Things to keep in mind when using Cross-Tab objects: • • • • You can have multiple rows, columns, and summarized fields. You can use print-time formulas as your rows or columns. You can use Running Total fields as your summarized field.
Cross-Tab Objects Creating a Cross-Tab report Modifying the links 1. In this example, check to make sure that the Customer table is linked to the Product table via the Orders and Orders Detail tables. If you do not like the linking that Crystal Reports has automatically suggested, change it by clicking Clear Links, and then select the fields that you would like to link together. 2. Click Next. The Cross-Tab screen appears.
17 Cross-Tab Objects Creating a Cross-Tab report 2. Change the default charting information to suit your report. For more information about creating a group chart, see “Charting on summary or subtotal fields (Group layout)” on page 289. 3. Click Next. The Record Selection screen appears. Selecting records Use this screen to apply selection conditions. This limits the results that are returned in your report to only those records that you are interested in. 1. Select the field to apply selection to.
Cross-Tab Objects Creating a Cross-Tab report 4. Click the Preview tab to view the report. u 1. To add a Cross-Tab to an existing report Open your report. This example uses the Group By Intervals.rpt included in the Feature Examples directory. 2. Click the Design tab. 3. On the Insert menu, click Cross-Tab. Tip: Another way to do this is to click the Insert Cross-Tab button on the Insert Tools toolbar. An object frame appears with the Arrow cursor as you drag the CrossTab object into the report. 4.
17 Cross-Tab Objects Creating a Cross-Tab report 8. Drag the field to the lower-left-hand corner of the Cross-Tab object, and click to release it. 9. Finally, drag Last Year’s Sales to the Insert Summary cells of the Cross-Tab, and click to release it. Tip: Notice that all of the cells change color when you position the field over them. 10. Click the Preview tab to see your Cross-Tab.
Cross-Tab Objects Creating a Cross-Tab report Defining the structure of the Cross-Tab Add fields to the Rows, Columns, and Summarized Fields areas. 1. From the Available Fields list, select Country; then click the > arrow next to the Rows area. The Country field is added to the Rows area. Tip: You can also add a field to the different Cross-Tab areas by selecting it and dragging it to the Columns, Rows, or Summarized Fields areas. 2.
17 Cross-Tab Objects Working with Cross-Tabs Finishing the Cross-Tab 1. Click OK. 2. On the Report menu, click Refresh Report Data. The updated report appears. Working with Cross-Tabs This section describes ways you can work with a Cross-Tab once you’ve added it to your report: • • • • • Showing values as percentages Abbreviating large summarized fields Customizing row/column labels Using running totals in Cross-Tabs Printing Cross-Tabs that span multiple pages Showing values as percentages 1.
Cross-Tab Objects Working with Cross-Tabs Abbreviating large summarized fields Because the values in a Cross-Tab’s summarized fields are often very large, Crystal Reports lets you abbreviate such values. Note: To complete this procedure, the report you’re working with must include the custom function called cdFormatCurrencyUsingScaling. The sample report called Custom Functions.rpt includes this function. Sample reports are located in the Crystal Reports directory under \Samples\En\Reports. 1.
17 Cross-Tab Objects Working with Cross-Tabs Note: You can create your own custom function, or you can copy and modify the provided sample to abbreviate a summarized field. In general, using a custom function is not a requirement of working with the Display String feature. You can write any Display String formula you need as long as its return value is a string. Customizing row/column labels By default, row and column labels are derived from the data on which you base your Cross-Tab.
Cross-Tab Objects Working with Cross-Tabs u 1. To total down columns Right-click the blank top-left area of a Cross-Tab and select Cross-Tab Expert from the shortcut menu. 2. On the Cross-Tab tab of the Cross-Tab Expert, make sure you have added a Running Total field to the Summarized Fields area. 3. Click OK to return to the Cross-Tab in your report. 4. Click Record Sort Expert and sort your Cross-Tab by the field you designated as a Column in the Cross-Tab Expert. 5.
17 Cross-Tab Objects Formatting Cross-Tabs Crystal Reports lets you designate report objects that don’t expand horizontally, such as text objects, field objects, OLE objects, charts, maps, lines, boxes, and so on, to be repeated on each additional horizontal page that a Cross-Tab creates. For more information, see “Repeating report objects on horizontal pages” on page 267. Formatting Cross-Tabs Crystal Reports has powerful formatting capabilities that can be applied to Cross-Tabs.
Cross-Tab Objects Formatting Cross-Tabs Formatting background color of entire rows/columns Use background colors to emphasize rows or columns within your Cross-Tab. 1. Right-click the blank top-left area of the Cross-Tab and select Cross-Tab Expert from the shortcut menu. The Cross-Tab Expert appears. 2. Click the Customize Style tab. 3. Click the row (in the Rows area) or column (in the Columns area), and select a color from the Background Color drop-down list. 4.
17 Cross-Tab Objects Formatting Cross-Tabs Suppressing Cross-Tab data This section provides you with the steps required to suppress data in your report. You can suppress: • • • Empty rows and columns. Row and column grand totals. Subtotals and their labels. u To suppress empty rows and columns 1. Right-click the blank top-left area of the Cross-Tab and select Cross-Tab Expert from the shortcut menu. The Cross-Tab Expert appears. 2. Click the Customize Style tab. 3.
Cross-Tab Objects Formatting Cross-Tabs 5. Click the Suppress Label check box to suppress the label associated with subtotal. 6. Click OK. Displaying summarized fields horizontally If you have two or more summarized fields in your Cross-Tab, you can display their values horizontally instead of vertically (the default). 1. Right-click the blank top-left area of the Cross-Tab and select Cross-Tab Expert from the shortcut menu. The Cross-Tab Expert appears. 2. Click the Customize Style tab. 3.
17 Cross-Tab Objects Formatting Cross-Tabs 350 Crystal Reports User’s Guide
chapter Building Queries
18 Building Queries Connecting to a universe Connecting to a universe You access the Business Objects Query Panel in Crystal Reports through the Database Expert. Once you have selected Universes from the Create New Connection node, you are prompted to log onto BusinessObjects Enterprise, after which you can select a universe and design your query. u To connect to a universe 1. In Crystal Reports, on the File menu, click New, and then click Blank Report. 2.
Building Queries Defining the data selection for a query u 1. To build a simple query In the Query Name field, enter a name for your query. Note: Crystal Reports uses the query name as the name of the SQL Command that it creates for your report. Once you have created your query in the Query Panel, you will see this name in the Available Data Sources and Selected Tables areas of the Database Expert. 2. Select an object in the left-hand pane and double-click it or drag it into the Result Objects pane.
18 Building Queries Editing an existing query Note: Objects cannot be defined directly in the Query Panel. Use the BusinessObjects Designer to define objects in a universe. Object Examples Description Dimension This object retrieves the data that provides the basis for analysis in a report. Dimension objects typically retrieve character-type data (for example, customer names, resort names, or dates). Detail This object provides descriptive data about a dimension.
Building Queries Viewing the SQL behind a query Viewing the SQL behind a query When you build a query, Crystal Reports automatically generates the SQL that corresponds to the query and saves it as a Crystal SQL Command object. For more information about SQL Command objects, search for “Defining an SQL Command” in the Crystal Reports Online Help. u To view the SQL when you create a query • In the Query Panel, click View SQL. The SQL dialog box appears; it contains the SQL that constitutes your query.
18 Building Queries Query filters and prompts u To add a predefined filter to a query 1. Double-click the objects you want to use in your report, or drag them to the Results Objects pane. For step-by-step instructions on selecting objects to build a query, see “Defining the data selection for a query” on page 352. 2. Drag a predefined filter to the Query Filters pane. When you run the query, the data corresponding to the query filters you selected is returned to the report.
Building Queries Query filters and prompts u 1. To create a prompt Select the object you want to apply a prompt on and drag it to the Query Filters pane. For example, if you want to allow users to specify the geographical region for the report, drag the Region field to the Query Filters pane. The Filter Editor appears. By default, the name of the filter is the name of the filtered object. 2. Select an operator from the list. See “Quick reference to query filter operators” on page 364.
18 Building Queries Query filters and prompts By default, the Query Panel combines the filters and prompts with the And operator. You can leave the And operator or change the operator to Or. See “Using And or Or to combine query filters” on page 363. 2. If necessary, change the operator to Or by double-clicking the And operator. Or now displays as the operator. The prompts appear when you click OK, or when you refresh the report data.
Building Queries Query filters and prompts u 1. To select objects for an advanced filter Select the object that you want to specify as a sub-set within the query definition. The object you select defines the aggregation context of any measures or measure filters you place on the advanced filter later. For example, to calculate and filter sales revenue results according to sales revenue by service line, select the Service Line object. 2. Drag the selected dimension to the Query Filters pane.
18 Building Queries Query filters and prompts u To add query filters to an advanced filter 1. Drag a predefined filter from the left-hand pane and drop the filter onto the white area at the bottom of the advanced filter. Or Drag an object onto the white area at the bottom of the advanced filter and define a custom filter using the Filter Editor.
Building Queries Query filters and prompts u 1. To name an advanced filter Double-click the advanced filter you want to name. The Advanced Filter Settings dialog box appears. 2. Type a new name in the Filter Name box, and then click OK to confirm the new name. The new name appears on the filter. You can also opt to exclude the values returned by the filter from the report.
18 Building Queries Query filters and prompts By default, the Query Panel combines the filters with the And operator. 2. Leave the And operator. Or Double-click the And operator to change the operator to Or. For an example of each operator and the difference between them, see “Using And or Or to combine query filters” on page 363. u To combine advanced filters with other filters or prompts 1. Create each filter.
Building Queries Query filters and prompts Related topics: • “Editing and removing query filters” on page 363 Using And or Or to combine query filters This table explains the difference between the And and the Or operators. Retrieve this data Example Select Data true for both filters. Customers who ordered supplies in Q1 and And in Q2 (the data you retrieve will include: customers who placed orders in both Q1 and Q2). Data true for any one of Customers who ordered supplies in: Q1 or the filters.
18 Building Queries Query filters and prompts u To remove a query filter • Drag the filter you want to remove and drop it onto the left-hand pane. The filter is removed from the query definition and no longer appears on the Query Filters pane. Related topics: • • “Creating query filters” on page 355 “Building prompts” on page 356 Quick reference to query filter operators The following table will help you select the operator you need to define a query filter.
Building Queries Query filters and prompts Values to retrieve Example Select Filter created Values that are different from the multiple values you specify. Don’t retrieve data for the following countries: the US, Japan, and the UK. Not in list Not in list 'US; Japan; UK' Values for which there is no value entered in the database. Retrieve customers without children (the children column in the database has no data entry).
18 Building Queries Query filters and prompts 366 Crystal Reports User’s Guide
chapter Creating and Updating OLAP Reports
19 Creating and Updating OLAP Reports OLAP reporting with Crystal Reports OLAP reporting with Crystal Reports Although relational databases such as SQL servers and PC databases are the most common sources of data, Online Analytical Processing (OLAP) and Multi-Dimensional Data are rapidly becoming the popular data-storage and analysis formats. Crystal Reports provides the same access and reporting features for OLAP data sources that it provides for relational data.
Creating and Updating OLAP Reports Creating an OLAP report Creating an OLAP report OLAP reports are created using the OLAP Report Creation Wizard or the OLAP Expert. Before you start creating your report, ensure you have the following information: • • • • • OLAP Type Server/Database name User ID Password Parameters (applies only to OLE DB for OLAP) Tip: You can return to the OLAP Report Creation Wizard by selecting the OLAP Report Settings option on the Report menu.
19 Creating and Updating OLAP Reports Creating an OLAP report Defining the structure of the grid Use the Rows/Columns screen to define how your data is structured within the grid. You can place the dimensions into either the Columns or Rows areas. Tip: It is also possible to drag and drop the dimensions into either the Rows or Columns area. 1. Select a dimension to appear in the report. 2.
Creating and Updating OLAP Reports Creating an OLAP report 8. Select a dimension in either the Rows or the Columns areas and click Create/Edit Parameter to create a parameter for use with the dimension. The Create Parameter Field dialog box appears. 9. Click OK. Note: Once you have created a parameter, access to the Member Selector dialog box is disabled for the dimension until you delete the parameter. 10. If you want to delete a parameter, select the appropriate dimension and click Delete.
19 Creating and Updating OLAP Reports Creating an OLAP report Add a page 1. Use the > arrow to add dimension(s) to the Page list. The Member Selector dialog box appears. 2. Expand the dimensions structure and select the appropriate fields. 3. Click OK. 4. Click Next. The Style screen appears. Add a parameter You can create a parameter field to link to any dimension by clicking the Create/Edit button.
Creating and Updating OLAP Reports Updating an OLAP report Note: The color of the style you select may not appear correctly if your screen resolution is set to 256 colors. Increase the resolution to correct this. 2. Click Next. The Chart screen appears. Inserting a chart 1. Select the kind of chart you want to add to your report from the options shown on the Chart screen. Tip: If you do not want to insert a chart, click Finish. 2. Add a title for your chart. 3.
19 Creating and Updating OLAP Reports Updating an OLAP report Before updating your report, it is important to ensure that it is compatible with the data. Some things to look for include: • • Removing dimensions contained in the report that are not in the cube. Removing fields referred to explicitly in the design of the report, such as a formula. Note: If a report contains subreports or grids, and the database used for them have changed name or location, you must update each subreport or grid.
Creating and Updating OLAP Reports Formatting data in an OLAP grid Updating the database location 1. In the Set Datasource Location dialog box, expand the Create New Connection folder in the “Replace with” area. 2. Expand the OLAP folder and search for the new cube location. Tip: You can double-click the Make a New Connection option to search for the cube in the OLAP Connection Browser. 3. Select the data source name, or an individual table, and click Update.
19 Creating and Updating OLAP Reports Formatting data in an OLAP grid To access the OLAP Expert, select the entire grid object by clicking one of its borders. Right-click the selected grid and, on the shortcut menu, click OLAP Expert. The OLAP Expert has two tabs not found in the OLAP Report Creation Wizard. Use the Customize Style and Labels tabs to format your OLAP grid.
Creating and Updating OLAP Reports Formatting data in an OLAP grid Creating an alias for a dimension You can create aliases to shorten long dimension names. This is useful when you plan on referring to a dimension in a conditional formatting formula (by using the GridRowColumnValue function). 1. On the Customize Style tab of the OLAP Expert, select the dimension. 2. In the Group Options area, enter an alias name in the Alias for Formulas field. 3. Click OK if you have finished customizing your grid.
19 Creating and Updating OLAP Reports Changing the view of OLAP data Changing the view of OLAP data When analyzing OLAP data in your report, there are several basic ways to change how the data is displayed in the grid. Note: The methods described here let you manipulate your OLAP grid directly from the Preview tab of Crystal Reports. You can also carry out the same and additional, more advanced, functionality on the Cube View tab.
Creating and Updating OLAP Reports Changing the view of OLAP data u 1. To change the display format for member names Right-click the dimension name. 2. On the shortcut menu, point to Display Members Using, and then click one of the display options: • • • • Caption Name Caption : Name Unique Name For information about names and captions, search the online help for “Changing member captions.” u 1.
19 Creating and Updating OLAP Reports Sorting and filtering OLAP grid data Sorting and filtering OLAP grid data The sorting capabilities of the OLAP grid enable you to order data by row and column values. If you want to limit the data based on particular field values, add one or more filters to the grid. Filters also allow you to perform top or bottom N analysis (by actual values and by percentage).
Creating and Updating OLAP Reports Sorting and filtering OLAP grid data Now the Report Designer disregards parent/child relationships between grid members and sorts on the basis of data value alone. (Frozen Goods still precedes Bakery, but Pastry precedes all others.) You can add up to three sorts to grid rows and up to three sorts to grid columns. In each case, the first sort takes precedence, and each subsidiary sort serves to further differentiate between grid data.
19 Creating and Updating OLAP Reports Adding calculations to OLAP grids Filtering data in an OLAP grid Use filters to exclude grid data that are not important, or to display only the data that you want to see. You can filter grid data by actual values, or you can choose to exclude or display the top or bottom N, or the top or bottom N%. Tip: • • To locate a filtered row or column, move the mouse pointer over the OLAP grid. When you reach a filtered row or column, the pointer turns into an X.
chapter Printing, Exporting, and Viewing Reports
20 Printing, Exporting, and Viewing Reports Distributing reports Distributing reports Crystal Reports enables you to distribute your report using a variety of methods. This section covers: • • • • Printing a report Faxing a report Exporting a report Working with Enterprise folders Printing a report u To print a report 1. On the File menu, select Print. Tip: Another way to do this is to click the Print button on the Standard toolbar. The Print dialog box appears. 2.
Printing, Exporting, and Viewing Reports Distributing reports 4. Choose the appropriate settings, and then click OK. Your fax application appears, prompting you to select a cover page and to fill in the appropriate fax information. Exporting a report Finished reports can be exported to a number of popular formats, such as spreadsheet, word processor, HTML, ODBC, and common data interchange formats. This makes the distribution of information easier.
20 Printing, Exporting, and Viewing Reports Distributing reports Preview tab. Acrobat format embeds the TrueType fonts that appear in the document. (Non-TrueType fonts are not supported.) This export format does not support Microsoft font-linking technology—which is used to provide support for some extended character sets such as Chinese HKCS. Therefore, the fonts used in the report must contain all of the required glyphs. These URI types are supported for hyperlinks: “http:”, “https:” and “mailto:”.
Printing, Exporting, and Viewing Reports Distributing reports To get the best output from Excel export formats, you should design your reports in an Excel-friendly way. For more information, refer to the Technical Brief called “Exporting to Microsoft Excel” found on the Business Objects Support site at: http://support.businessobjects.com/communityCS/TechnicalPapers/ scr_exportexcel.pdf.
20 Printing, Exporting, and Viewing Reports Distributing reports Record Style - Columns with spaces (REC) and Record Style Columns without spaces (REC) The record-style formats export report data as text. These formats export data only from Group and Details areas. The output contains one line per record in the database (for the report). The record-style formats are used mainly for data exchange.
Printing, Exporting, and Viewing Reports Distributing reports Text (TXT) Text format is a record-based format. Its output is plain text; therefore formatting is not retained. However, this format can preserve some of your report's layout. Text format assumes that a font of a constant dimension is used throughout the export. The Characters Per Inch (CPI) option specifies the number of characters that can be fit in a linear inch of horizontal space, and thus determines the dimension of the font.
20 Printing, Exporting, and Viewing Reports Distributing reports The sections below provide instructions on how to export a report to the Microsoft Excel (XLS) format for each of the different destination types. u To export to an application 1. Open the report you would like to export. 2. On the File menu, point to Export, and then click Export Report. Tip: Another way to do this is to click the Export button on the Standard toolbar. The Export dialog box appears. 3.
Printing, Exporting, and Viewing Reports Distributing reports Tip: Another way to do this is to click the Export button on the Standard toolbar. The Export dialog box appears. 3. Select the export format type from the Format drop-down list. In this case, choose Microsoft Excel (XLS). 4. Select Disk file from the Destination drop-down list. 5. Click OK. The Excel Format Options dialog box appears. For more information about this dialog box, see step 5 in “Exporting to an application” on page 389. 6.
20 Printing, Exporting, and Viewing Reports Distributing reports The Excel Format Options dialog box appears. For more information about this dialog box, see step 5 in “Exporting to an application” on page 389. 6. Change the formatting options as needed. 7. Click OK. The Choose Profile dialog box appears. Note: You will be prompted by the Microsoft Outlook Setup Expert if Microsoft Exchange, Microsoft Mail, or Internet E-mail is not set up on your machine. 8.
Printing, Exporting, and Viewing Reports Distributing reports The Excel Format Options dialog box appears. For more information about this dialog box, see step 5 in “Exporting to an application” on page 389. 6. Change the format settings as required. 7. Click OK. The Select Database dialog box appears. 8. Double-click the Lotus Domino server you would like to export your report to. The file name defaults. 9. Select the database you would like to export the report to. 10. Click OK.
20 Printing, Exporting, and Viewing Reports Distributing reports The Excel Format Options dialog box appears. For more information about this dialog box, see step 5 in “Exporting to an application” on page 389. 6. Change the formatting options as needed. 7. Click OK. The Send Mail dialog box appears. 8. Enter the address details, then click Send. The Exporting Records dialog box appears. Tip: Click Cancel Exporting to cancel the export process.
Printing, Exporting, and Viewing Reports Distributing reports Working with Enterprise folders Another way to distribute your reports is through BusinessObjects Enterprise. When you publish a report to BusinessObjects Enterprise, you can deliver it to end users via any web application—intranet, extranet, Internet or corporate portal. Crystal Reports facilitates the publication of reports through the Enterprise option found in the Open and Save As dialog boxes and through the Workbench.
20 Printing, Exporting, and Viewing Reports Distributing reports 4. Enter your user name and password. 5. Click the Authentication list to select the appropriate authentication type. Enterprise authentication requires a user name and password that is recognized by BusinessObjects Enterprise. LDAP authentication requires a user name and password that is recognized by an LDAP directory server. Windows Active Directory (AD) authentication requires a user name and password that is recognized by Windows AD.
Printing, Exporting, and Viewing Reports Viewing reports 3. If you have not already logged onto BusinessObjects Enterprise, do so now. For information about how to log onto BusinessObjects Enterprise, see “Opening a report in an Enterprise folder” on page 395. 4. When the Enterprise folders appear, select a folder to save your report in. 5. Enter a file name for your report. 6.
20 Printing, Exporting, and Viewing Reports Viewing reports Report Part Viewer The Report Part Viewer is a viewer that lets you display Report Parts without the rest of the report page. You can integrate this viewer into web applications so that your users see only specific report objects without having to see the rest of the report. For the most part, you set up the Report Part hyperlinks in the Report Designer, but you take advantage of their functionality in the report viewers.
Printing, Exporting, and Viewing Reports Viewing reports When you select the Report Part Drilldown option, the “Hyperlink information” area contains these options: • Available Fields The Available Fields area lists, as a tree view, all the sections in your report that contain report objects you can use for Report Part drill down. You select objects from this area and add them to the Fields to Display area.
20 Printing, Exporting, and Viewing Reports Viewing reports • • • Bitmaps Cross-tabs Text objects You cannot select the following report object types as destinations: • • • • Objects inside the Page Header or Page Footer. Lines or boxes. Subreports or any object inside a subreport. Entire sections (you must select the objects inside the section individually). You can add specific information in one of two ways: • • • You can type the object name(s) as they appear in the Report Explorer.
Printing, Exporting, and Viewing Reports Viewing reports object to one or more destination objects. When you have multiple destination objects, they must all reside in the same report section. The Report Part Drilldown option does not affect the DHTML page viewers since the option emulates the default Crystal Reports behavior for drill down (which the page viewers already support). Page viewers, however, do not limit which objects are displayed—they always show all report objects.
20 Printing, Exporting, and Viewing Reports Viewing reports The “Hyperlink information” area changes to show the fields available for this type of hyperlink. The Available Fields area shows only the sections and report objects you can select for drill down. In general, these objects include field objects, charts, maps, bitmaps, cross-tabs, and text objects contained in the next section down (that is, for example, objects in group two when the object you selected is in group one).
Printing, Exporting, and Viewing Reports Viewing reports For ease of identification, the program creates a section node in the Fields to Display area. This node contains the objects that you selected in the Available Fields area. 6. If you want to add another object to the Fields to Display area, select it in the Available Fields list and drag it into position. Note: • • 7.
20 Printing, Exporting, and Viewing Reports Viewing reports Work flow Because the Another Report Object option allows you to create hyperlinks between objects in different reports managed in BusinessObjects Enterprise, it requires more set up on the Hyperlink tab. This is an overview of the steps you have to complete to set up your hyperlink successfully. • • • Open the report that contains the object you want to be the destination object and copy it.
Printing, Exporting, and Viewing Reports Viewing reports The Paste Link button includes a list of options that you can use when pasting a link to the destination object. Depending on the type of link you are creating (for example, a link to a specific data context rather than a link to a generic data context), you may see these options: • Context Report Part This is the default option; it is selected when you click Paste Link without viewing the other list options.
20 Printing, Exporting, and Viewing Reports Viewing reports Note: If you choose the Design View Report Part option (or if you choose the Context Report Part option for an object copied from a report’s Design view) and your source and target reports have different data structures, you’ll see the Report Part Map Fields dialog box. Each field in the Unmapped Fields area represents a group in your source report. Use this dialog box to map these groups to fields in your target report.
Printing, Exporting, and Viewing Reports Viewing reports Scenario 1 If your source report contains data (that is, if the report is in Preview view), the Context Report Part data context (in the target report) is the source report’s Preview data context for all children of the selected field. For example, you might see this in the Data Context field: /Country[USA]/Region[*]. Otherwise, the program uses the source report’s Design view data context as the Context Report Part data context in the target report.
20 Printing, Exporting, and Viewing Reports Viewing reports • Strongly-Typed format: /Country[USA]/Product Class[Bicycle] Note: • You can add detail-level information in 0-based format: /USA/Bicycle/ChildIndex[4] • You can also use a wildcard to identify all instances: /USA/* • For cross-tab objects, you can use navigation on cells, columns, or rows. To define the data context, use the GridRowColumnValue formatting function. For example: "/" + GridRowColumnValue ("Supplier.
Printing, Exporting, and Viewing Reports Using smart tags Differences between how the viewers display hyperlinks Page viewers Report Part Viewer Another Report Object: Another Report Object: Navigates to destination objects • Navigates to destination objects and shows contents of the entire and shows only the destination page. objects. • Report Part Drilldown: Navigates (drills down) to the group and shows the complete contents of the group.
20 Printing, Exporting, and Viewing Reports Using smart tags Before you can use smart tags, web server options must be configured on the Smart Tag tab of the Options dialog box. As well, an .asp or .jsp page must be created for viewing report details. Normally, these tasks should be carried out by your system administrator.
chapter Report Alerts
21 Report Alerts About Report Alerts About Report Alerts Report Alerts are custom messages created in Crystal Reports that appear when certain conditions are met by data in a report. Report Alerts may indicate action to be taken by the user or information about report data. Report Alerts are created from formulas that evaluate conditions you specify. If the condition is true, the alert is triggered and its message is displayed. Messages can be text strings or formulas that combine text and report fields.
Report Alerts Working with Report Alerts 2. Click New. The Create Alert dialog box appears. 3. Enter a name for your new alert in the Name box. 4. Enter your alert message in the Message box. The Message box lets you enter a message to be used as a default. If you want the same message to appear every time your alert is triggered, enter it in the Message box. If, however, you want to use a formula so the message is customized with data elements, see the next step. 5.
21 Report Alerts Working with Report Alerts If an alert formula is based on a summary field, any recurring fields used must be constant over the summary field. For example, if you are grouping on Country and Region, you might create an alert such as: Sum ({Customer.Last Year’s Sales}, {Customer.Region}) > 10000 In this case, your formula can refer to either Country or Region, but not City or Customer Name since these are not constant.
Report Alerts Working with Report Alerts 4. Click OK to save your changes. Note: If the alert has already been triggered, editing it removes it from the Report Alerts dialog box. Deleting Report Alerts 1. On the Report menu, point to Alerts and then click Create or Modify Alerts. 2. In the Create Alerts dialog box, select the alert you want to delete and click Delete. The selected alert is removed from the Create Alerts dialog box.
21 Report Alerts Working with Report Alerts A new report tab is opened showing the report record(s) that triggered the alert. If the record is hidden, the record’s group is shown but drill down is not performed. Note: If you select more than one triggered alert before clicking the View Records button, the results are generated by performing a Boolean AND operation on the selected alerts. 4. To return to the Report Alerts dialog box, click the Preview tab. 5.
chapter Using Formulas
22 Using Formulas Formulas overview Formulas overview In many cases, the data needed for a report already exists in database table fields. For example, to prepare an order list you would place the appropriate fields on the report. Sometimes, however, you need to put data on the report that does not exist in any of the data fields. In such cases, you need to create a formula.
Using Formulas Formula components and syntax Basic syntax example: formula = Month ({Orders.Order Date}) Using a custom function To convert $500 from U.S. currency to Canadian: Crystal syntax example: cdConvertUSToCanadian (500) Basic syntax example: formula = cdConvertUSToCanadian (500) Formula components and syntax Formulas contain two critical parts: the components and the syntax.
22 Using Formulas Formula components and syntax Custom functions Example: cdFirstDayofMonth, cdStatutoryHolidays Custom functions provide a way to share and reuse formula logic. They can be stored in the BusinessObjects Enterprise Repository and then added to a report. Once in the report, custom functions can be used in the Formula Expert when creating formulas.
Using Formulas User Function Libraries in formulas Note: • • • Record selection and group selection formulas cannot be written in Basic syntax. Report processing is not slowed down by using Basic syntax. Reports using Basic syntax formulas can run on any machine that Crystal Reports runs on. Using Basic syntax formulas does not require distributing any additional files with your reports. Related topics: • • To learn about Basic syntax, see Creating Formulas with Basic syntax in the online help.
22 Using Formulas Specifying formulas Report formulas Report formulas are formulas that you create to stand alone in a report. For example, a formula that calculates the days between the order date and the shipping date is a report formula. Conditional formatting formulas Formatting formulas change the layout and design of a report, as well as the appearance of text, database fields, objects, or entire report sections. You format text through the Format Editor.
Using Formulas Specifying formulas Working with the Formula Workshop You can create many different kinds of formulas in the Formula Workshop. The workshop consists of a toolbar, a tree that lists the types of formulas you can create or modify, and an area for defining the formula itself. Note: Search formulas and Running Total condition formulas are created and maintained through the Search Expert and the Create (or Edit) Running Total Field dialog box.
22 Using Formulas Specifying formulas Tip: The Workshop Tree can be docked. By default, it appears docked on the left-hand side of the Formula Workshop, but you can manually dock it on the right-hand side. In free-floating mode, the Workshop Tree can be dragged to any location in the workshop. Working with the Formula Editor The Formula Editor is a component of the Formula Workshop. Use the Formula Editor to create and modify the content of formulas.
Using Formulas Specifying formulas Setting the default syntax When you open the Formula Editor, Crystal syntax appears as the syntax default. If you want to change the syntax default, select Options from the File menu, then click the Reporting tab. Choose the preferred syntax from the Formula Language drop-down list and click OK. When you access the Formula Editor, the syntax you selected appears as the default.
22 Using Formulas Creating and modifying formulas Creating and modifying formulas Creating a formula and inserting it into a report 1. On the View menu, click Field Explorer. 2. In the Field Explorer dialog box, select Formula Fields and click New. 3. In the Formula Name dialog box, enter the name you want to identify the formula by, and then click OK. The Formula Workshop appears with the Formula Editor active. 4. On the Expressions Editor Toolbar, choose either Crystal or Basic syntax.
Using Formulas Creating and modifying formulas Tip: Another way to do this is to click the Formula Workshop button on the Expert Tools toolbar. 2. Select Formula Fields in the Workshop Tree and click New. 3. In the Formula Name dialog box, enter the name you want to identify the formula by, and then click OK. The Formula Expert appears with the Formula Editor active. 4. Click Use Expert/Editor on the toolbar. The Formula Expert appears. 5.
22 Using Formulas Creating and modifying formulas 7. Click Save to save the formula to the Formula Fields folder of the Formula Workshop. You can now use this formula in your report just as you would use a formula you created in the Formula Editor. Editing formulas 1. On the View menu, click Field Explorer. 2. Right-click the formula you want to edit and choose Edit. The Field Explorer dialog box appears. The Formula Workshop appears with the Formula Editor active. 3.
Using Formulas Creating and modifying formulas Copying formulas from online help Since the formulas you develop using the Formula Editor are text, you can copy useful online formulas directly into the Formula Editor and then modify them to fit your needs. u 1. To copy formulas from online help On the Help menu, click Crystal Reports Help. The Crystal Reports online help appears. 2. Click the Index tab. 3. Enter formulas in the keyword field and click Display. 4.
22 Using Formulas Creating and modifying formulas u To copy a formula from one report to another 1. Select the formula field you want to copy in the report. 2. On the Edit menu, click Copy. 3. Open the report you want to copy the formula to. 4. Choose Paste from the Edit menu. 5. When the program displays the object frame, drag the formula to the new location. 6. To make changes to the formula, right-click the formula and choose Edit Formula from the shortcut menu.
Using Formulas Deleting formulas Deleting formulas When a formula is created and added to a report, the Report Designer: • • Stores the specification for creating the formula, using the name you assigned to it. Places a working copy of that formula at the point you specify in the report. A working copy is any occurrence of the formula in the report. In order to completely delete formulas, you must delete the specification and all working copies of the formula.
22 Using Formulas Debugging formulas Debugging evaluation time errors When the Formula Workshop is being displayed as a result of an evaluation time error, the Workshop Tree will contain a call stack. The root of the tree provides a description of the error which occurred. The nodes in the tree provide the names of the custom functions and/or formulas which were being evaluated when the error occurred. The custom function/formula at the top of the call stack is where the error was detected.
Using Formulas Debugging formulas • The following formula is the formula you will test for errors: If ({customer.CUSTOMER NAME} [1 to 2 ToText({customer,CUSTOMER ID}) [1] = ({customer.CUSTOMER NAME} [1] = 'Ro" ToText({customer.
22 Using Formulas Debugging formulas 8. Insert the corrected formula field to the right of the two data fields in the Details section of your report. 9. Click Print Preview on the Standard toolbar to check the values in the report and compare the fields to see if the field values returned by @Formula1 are correct. You will find “TRUE” listed next to the customer names that begin with “Bi” and “FALSE” next to all the others. Now you will check the other portions of the formula.
Using Formulas Debugging formulas 3. Click Check to test for errors. You will receive the following error message: The matching ' for this string is missing. 4. Correct the formula by changing the single quote (') before Ro to a double quote ("). 5. Click Check again. The formula should now be error-free. 6. Place the formula to the right of the @Formula2 field. 7.
22 Using Formulas Debugging formulas 2. Type the following in the Formula text box of the Formula Editor: If {customer.CUSTOMER NAME} [1 to 2] = "Bi" and ToText({customer.CUSTOMER ID}) [1] = "6" Then "TRUE" Else "FALSE" 3. Place the formula to the right of the @Formula4 field. You should see “TRUE” next to each customer whose name begins with Bi and Id begins with 6, and “FALSE” next to all Customer IDs that do not meet this criteria.
chapter Parameter Fields and Prompts
23 Parameter Fields and Prompts Parameter and prompt overview Parameter and prompt overview Parameters are Crystal Reports fields that you can use in a Crystal Reports formula. As a formula component, a parameter must have a value before the program can process the report. By using parameters in formulas, selection formulas, and in the report itself, you can create a single report that changes its behavior depending on the values entered by your users. Parameter fields can also be used in subreports.
Parameter Fields and Prompts Parameter and prompt overview • Number: Requires a numeric value. Example: Enter the customer identification number. • String: Requires a text answer. Example: Enter the region. • Time: Requires an answer using a time format Example: Display the total number of calls from 1:00pm-2:00pm. • • • Parameter field prompting text can be up to four lines long with approximately 60-70 characters per line (depending on character width, up to the 254 character limit).
23 Parameter Fields and Prompts Understanding dynamic prompts • A list of values can form a cascading prompt; that is, you can create a prompt whose final value is determined through a sequence of choices. For example, users might first be prompted to pick a country before the choices for region appear. The users might then need to pick a region before the choices for city appear, and so on.
Parameter Fields and Prompts Understanding dynamic prompts Feature Available when Crystal Available when Crystal reports are stored outside of reports are published to BusinessObjects Enterprise? BusinessObjects Enterprise? Populate lists of values from command objects. Yes No All lists of values that are used by managed reports are based on Business Views; however Business Views can themselves be based on command objects. In this way, lists of values can be indirectly based on command objects.
23 Parameter Fields and Prompts Understanding lists of values The supported viewers are as follows: • • • • • • • • ActiveX .NET Winform .NET Webform Java, COM, and JSF DHTML page viewers Java and COM Advanced DHTML Viewer The InfoView and Central Management Console scheduling interfaces in BusinessObjects Enterprise. The Report Designer Component (RDC). All editions of Crystal Reports XI. These components support the design of reports with dynamic prompts and cascading lists of values.
Parameter Fields and Prompts Understanding lists of values You can think of a list of values as the definition of the data needed to populate all levels of a cascading list. In Crystal Reports, a cascading relationship is defined by a single list-of-values object, not by multiple queries that are linked together by a common key. Although the list of values is defined as a single entity, data is not necessarily fetched from the data source in a single query.
23 Parameter Fields and Prompts Understanding lists of values Feature Unmanaged list of values Managed list of values Report fields Command objects Business Views At report-view time, prompt for additional information needed by the list of values. No Yes If the command object contains parameters, the value(s) are prompted for at report-view time. Yes If the Business View contains parameters, the value(s) are prompted for a report-view time. Display different values to different users.
Parameter Fields and Prompts Understanding lists of values Determining which list-of-values type to use Different reporting problems require different prompting solutions, depending on the amount of data in the lists of values. This table provides a rough guide to which list-of-values design you should use. Description Unmanaged list of values Managed list of values Report fields Command objects Business Views Single-level code tables.
23 Parameter Fields and Prompts Creating a parameter with a static prompt Lists of values and prompt groups contrasted Lists of values are the data part of a prompt; the values from your data that your users will see and select from. Prompt groups, on the other hand, are the presentation part of a prompt. Crystal Reports treats prompt groups are separate objects so that you can share the same list of values with different presentations.
Parameter Fields and Prompts Creating a parameter with a static prompt 5. Select the appropriate Type from the list. This example uses String. Note: When creating a parameter whose Type is either Date or DateTime, you can change the date format to suit your needs. For details, see “Changing your default field formats” on page 262. 6. From the Value Field list, select Country. 7. Click Actions and select Append all database values to move all of the countries in the sample database to the Values area.
23 Parameter Fields and Prompts Creating a parameter with a static prompt Note: If you don’t want to see the parameter field you dropped in your report, place it in a section you can suppress, such as a report header or footer. u To incorporate the parameter into the record selection 1. On the Report menu, click Select Expert. Tip: Another way to do this is to click the Select Expert button on the Expert Tools toolbar. The Choose Field dialog box appears. 2.
Parameter Fields and Prompts Creating a parameter with a dynamic prompt The report appears with the information for Brazil. With parameter fields, you can create a single report that can be customized quickly to meet a variety of needs. Creating a parameter with a dynamic prompt A dynamic prompt is one that changes on a regular or irregular schedule.
23 Parameter Fields and Prompts Creating a parameter with a dynamic prompt This is the text that appears in the “Enter prompt values” dialog box when the report is refreshed. This example uses “Select a Region.” 9. Click OK. 10. Return to the Field Explorer dialog box, and drag the Region parameter into your report. Note: If you don’t want to see the parameter field you dropped in your report, place it in a section you can suppress, such as a report header or footer.
Parameter Fields and Prompts Creating a parameter with a cascading list of values Creating a parameter with a cascading list of values A dynamic prompt can include a cascading list of values, which adds additional levels of selection for your users. For example, if you are prompting for a city value, but you also need to know which country and region that city comes from, you could create a dynamic and cascading prompt.
23 Parameter Fields and Prompts Creating a parameter with a cascading list of values 7. Ensure that New is selected in the Choose a Data Source area. Note: If your report already contains a list of values, or if you want to pick a list of values from your repository, you can select Existing and choose an existing list of values from the tree structure. 8. Click Insert. The program automatically expands the Value list. You use this area to define the fields that make up your cascading list of values.
Parameter Fields and Prompts Working with lists of values 14. Click OK. 15. Return to the Field Explorer dialog box, and drag the Supplier City parameter into your report. Note: If you don’t want to see the parameter field you dropped in your report, place it in a section you can suppress, such as a report header or footer. Working with lists of values Dynamic prompts use lists of values. You can create a list of values in either Crystal Reports or in the Business View Manager.
23 Parameter Fields and Prompts Working with lists of values u To share a common list of values within a report 1. Create a report that contains fields for Country, Region, and City. 2. In the Field Explorer, select Parameter Fields and click New. The Create New Parameter dialog box appears. 3. Enter a name for the parameter in the Name field. This example uses SupplierCity. 4. In the List of Values area, click Dynamic. 5. Enter prompting text for your prompt group in the Prompt Group Text field.
Parameter Fields and Prompts Working with lists of values Using separate value and description fields It is common in relational databases to make use of code fields that represent values. These codes are often numeric or text strings that cannot be read by your users. For such cases, you can create separate value and description fields in your list of values definition. You set the value field to the parameter; the description field appears in the prompting dialog box.
23 Parameter Fields and Prompts Best practices for prompting your database: Country, Region, and City. You need to return all three values in a single select statement. There is no need for an order by clause, because the prompting engine sorts the values according to your specifications. Note: It is not necessary to link your command object to your report fields. In general, command objects that you use only to populate lists of values do not need to be linked to your report.
Parameter Fields and Prompts Best practices for prompting To maximize performance and scalability of unmanaged reports, you should reuse list-of-value objects within a single report wherever possible. For information about how to do this, see “Sharing common lists of values within a report” on page 453. Managed reports Managed reports are reports that you store within BusinessObjects Enterprise.
23 Parameter Fields and Prompts Best practices for prompting Converting unmanaged reports to managed reports You can convert an unmanaged report to a managed report in the following ways: • • Publish one or more reports with the Publishing Wizard. • • Create a new report object from within the InfoView portal. Save the report to BusinessObjects Enterprise using the Crystal Reports Save As command. Create a new report object from within the Central Management Console portal.
Parameter Fields and Prompts Deleting parameter fields Deleting parameter fields There are several methods for deleting parameters within a report. The type of parameter you are deleting determines the method you can use. u 1. To delete a parameter that is not used in a formula On the View menu, click Field Explorer. The Field Explorer appears. 2. Expand the Parameter Fields folder and click the parameter you want to delete. 3. Click Delete.
23 Parameter Fields and Prompts Responding to parameter field prompts Note: If the parameter is used in more than one formula, it must be deleted from each formula. 4. Close the Formula Workshop. 5. Expand the Parameter Fields folder and click the parameter you want to delete. 6. Click Delete. Responding to parameter field prompts Previewing .a report for the first time When you preview a report for the first time, the Enter Prompt Values dialog box appears, prompting you for a value.
Parameter Fields and Prompts Advanced parameter features • • • • • • Enter Boolean values using the following format: TRUE or FALSE. Enter Number values exactly as they will appear in the field. Enter Currency values exactly as they will appear in the field. Enter Date values to match the format used on-screen. If the format is unspecified, enter as Date (Year, Month, Day). For example, Date (1997, 5, 21). To access the calendar, click the drop-down arrow beside the date.
23 Parameter Fields and Prompts Advanced parameter features • • “Defining sort order using parameter fields” on page 466. “Defining entry type and format using the Edit Mask” on page 467. Creating a parameter with multiple prompting values 1. On the View menu, click Field Explorer. 2. Select Parameter Fields and click New. 3. Enter a Name for your parameter. 4. Select a value type from the Type list. 5.
Parameter Fields and Prompts Advanced parameter features u 1. To apply conditional formatting using parameter fields Create the parameter field of the data type you need for the formula. 2. Create the formula and use the parameter field in place of the fixed value you would normally use. For example, to be prompted for all the customers whose last year’s sales were over a certain value, and to print their names in red, select the Last Year’s Sales field and click Format from the Expert Tools toolbar.
23 Parameter Fields and Prompts Advanced parameter features 7. Return to the Field Explorer, and then place the parameter field in the Page Header section of the report to have the title appear on every page, or in the Report Header section if you want the title to appear on only the first page of the report. Now, when you refresh the data, the program will prompt you for a report title. If you wish, you can change the title each time you run the report.
Parameter Fields and Prompts Advanced parameter features 2. If applicable, select the Min Length and/or Max Length options to designate the length of the field. • • For a Currency or Number parameter field, options for entering the “Min Value” and “Max Value” appear. For a Date, DateTime, or Time parameter field, options for entering the “Start” and “End” values appear. Incorporating a parameter into a formula 1. On the View menu, click Field Explorer. The Field Explorer appears. 2.
23 Parameter Fields and Prompts Advanced parameter features Defining sort order using parameter fields To set the sort order using parameter fields, you need to first create a formula that includes a parameter field and then sort based on that formula. For example, assume that you have a customer list report based on the Customer table. For each customer, you show the Customer Name, City, Region, Country, and Phone Number.
Parameter Fields and Prompts Advanced parameter features Defining entry type and format using the Edit Mask 1. On the View menu, click Field Explorer. 2. Select Parameter Fields and click New. 3. Enter the Name and value Type. 4. For a string parameter field, you can choose to enter an edit mask in the Edit mask field, rather than specifying a range. The Create New Parameter dialog box appears.
23 Parameter Fields and Prompts Advanced parameter features • • “>” (causes subsequent characters to be converted to uppercase). • “Password”. Allows you to set the edit mask to “Password,” you can create conditional formulas specifying that certain sections of the report become visible only when certain user passwords are entered. “\” (causes the subsequent character to be displayed as a literal). For example, the edit mask “\A” would display a parameter value of “A.
chapter Subreports
24 Subreports What are subreports? What are subreports? A subreport is a report within a report. The process for creating a subreport is similar to the process of creating a regular report. A subreport can have most of the characteristics of a report, including its own record selection criteria.
Subreports What are subreports? Linked Linked subreports are just the opposite; their data is coordinated. The program matches up the records in the subreport with records in the primary report. If you create a primary report with customer information and a subreport with order information and link them, the program creates a subreport for each customer and includes in that subreport all the orders for that customer.
24 Subreports What are subreports? • All of this parameter field manipulation takes place behind the scenes. You simply pick the fields that will link the primary report with the subreport and the program does the rest. The values are passed without the parameter field prompting you for a value.
Subreports Inserting subreports As a general rule, if you have indexed tables, linked indexed fields, or range limiting record selection criteria based on the indexed fields, the program needs to read the same number of records whether you are linking tables in a single report or using subreports. Since each subreport is run as a separate report, linked tables may have a performance advantage. See “Indexed tables” on page 487. Inserting subreports 1. On the Insert menu, click Subreport.
24 Subreports Inserting subreports 7. If you chose On-demand subreport (similar to a hyperlink), click the subreport preview tab to see your subreport. This tab is labeled with the name of your subreport. Note: Using on-demand subreports will increase the performance of reports that contain subreports. For information about creating a custom caption for the Subreport Preview Tab, see “Adding captions to on-demand subreports” on page 481.
Subreports Linking a subreport to the data in the primary report Updating subreports In order to maintain the most up-to-date subreports, you may want to reimport a subreport automatically when opening the main report. Note: Re-importing is available only for subreports that were created from a report file. Re-importing not only updates the data, but updates the formatting, grouping, and structure of the subreport if any changes have been made.
24 Subreports Linking a subreport to the data in the primary report To do this, you need to specify a field that is common to both the subreport and the primary report. With the Subreport Links dialog box, you create a link between the two common fields. Crystal Reports uses the link to match up records from the primary report to those in the subreport. The link makes certain that the “orders” data in the subreport sits on the same row as the corresponding “customer” data in the primary report.
Subreports Linking a subreport to the data in the primary report Note: The field type of the Containing Report field determines which subreport fields are visible. Because the Report Designer reads dates as either strings, dates, or date/time fields, you must make sure your subreport parameter field type matches the field type set up in Report Options in the main report for the field you want linked.
24 Subreports Combining unrelated reports by using subreports Combining unrelated reports by using subreports At times, you may wish to combine unrelated reports into a single report. For example, you may want to create a single report that presents: • • Sales grouped by sales representative. Sales grouped by item. While both reports deal with sales data, there is no real linear relationship between the reports. Subreports can be used to combine unrelated reports into a single report like this.
Subreports Using subreports with unlinkable data Note: Subreports can be placed side-by-side in the same Report Footer section. They will print next to each other at the end of the report. 5. Place the subreports into the Report Footer sections and they will print sequentially after the primary report.
24 Subreports Using subreports with unlinkable data While the return value is a valid Social Security Number, the fact that it comes from a formula prevents you from using the field to link to a Social Security Number field in another table. You can report on and coordinate the values in the two tables, however, by using a subreport. u To link to/from a formula field 1. Create the primary report using a table that includes the Social Security Number field. 2.
Subreports Creating an on-demand subreport Creating an on-demand subreport On-demand subreports can be especially useful when you want to create a report that contains multiple subreports. In this case, you can choose to have these subreports appear only as hyperlinks. The actual data is not read from the database until the user drills down on the hyperlink. This way only data for on-demand subreports that are actually viewed will be retrieved from the database.
24 Subreports Showing different views of the same data in a report 3. You can enter either an on-demand subreport caption or a preview tab caption by clicking the appropriate Formula button and opening the Formula Workshop. 4. Enter your formula in the Formula text box. Crystal syntax formula example: "More Information About " + {Customer.Customer Name} Basic syntax formula example: formula = "More Information About" + {Customer.Customer Name} Using the Xtreme.
chapter Understanding Databases
25 Understanding Databases Databases overview Databases overview Though there are hundreds of Database Management Systems (DBMS) available, Crystal Reports eliminates many of the differences once it connects to the actual database files. The process of working with database files, tables, fields, and records is much the same, regardless of the actual type of data being accessed. This chapter discusses several concepts and tasks common to working with database files.
Understanding Databases Databases overview Often, data in two different tables can be related by a common field. For example, a Customers table will have a Customer ID for each customer, and an Orders table will have the Customer ID of each customer who placed an order, demonstrating a relationship between tables. The two tables can be linked by a common field see “Linking tables” on page 489.
25 Understanding Databases Databases overview To solve this problem, the Report Designer uses aliases to refer to database tables and files. Aliases are pointers, internal devices that tell the program where it should look for a database field. Now, if you change the name or location of the database, you simply reset the pointer. See “Locating files” on page 486. The name of the alias does not change, so your formulas are not affected.
Understanding Databases Databases overview stores the same data in a different directory. In any of these events, you need to verify the location of the database files accessed by the report and reset the alias pointers to the new database location or name. The Verify Database command on the Database menu checks the alias pointers stored in a report file to verify that the database files expected are located in the indicated directories.
25 Understanding Databases Databases overview Note: Some DBMS applications do not support indexed tables. Refer to the documentation for your DBMS to find out if it supports indexes and how to create them. If your DBMS documentation does not mention indexed tables, it may not support them, and you should link tables based on common fields. The Links tab of the Database Expert can also help you determine if your tables include indexes.
Understanding Databases Linking tables Instead, you can create an index for the table based on the Customer field. Such an index might look like this: Customer Pointer to Order# Allez Distribution 10444 BG Mountain Inc. 10470 BG Mountain Inc. 10511 La Bomba de Bicicleta 10501 Mountain Toad 10488 Mountain Tops Inc. 10568 SFB Inc. 10495 Sierra Bicycle Group 10544 Sierra Bicycle Group 10579 Sierra Mountain 10485 In this index, information is organized by customers, not order numbers.
25 Understanding Databases Linking tables When you link, you are using a field that is common to both tables. Crystal Reports uses the link to match up records from one table with those from the other. In this example, the link assures that the data in each row of the report refers to the same order. Link from and link to When you link two tables, you link from one table to another table.
Understanding Databases Linking tables customer that has placed an order with the company. The Orders table contains information about orders that customers have placed. Since customers can place more than one order, there may be more than one record in the Orders table for each customer record in the Customers table. This is a one-to-many relationship.
25 Understanding Databases Linking tables Extended descriptions of chart columns The performance charts use the following columns: • Linking or Subreport Are you creating a report from linked databases or are you inserting a subreport and binding it to the data in your primary report? • Selection Formula Does your primary report include a record selection formula that sets range limits on the key (indexed) field in Table A? • Index A Is Table A on the field you are going to use indexed to match up t
Understanding Databases Linking tables PC Data Subreport Yes No No 2 2600 (2*2600) 5200 Subreport Yes No Yes 26 100 (26*100) 2600 Subreport Yes Yes Yes 2 100 (2*100) 200 SQL Data Linking/ Subreport Selection Formula Reads A For each A reads in B Total Records Read Linking No 26 100 (26*100) 2600 Linking Yes 2 100 (2*100) 200 Subreport No 26 100 (26*100) 2600 Subreport Yes 2 100 (2*100) 200 Data file considerations When working with data files, one-to-many links
25 Understanding Databases Linking tables • If there is an index on Table A, and the range limit selection condition is based on the indexed field ({customer.REGION} in this example), the program goes directly to the record it is seeking in Table A (the first CA record) and reads it. • • • • • For that record, the program locates the first matching record in Table B, using the Table B index.
Understanding Databases Linking tables Subreports and data files If your primary report is based on Table A, the subreport is based on Table B, and the records are linked, your primary considerations are as follows: • The number of subreports that are run by the program is determined by the index and the selection formula situation in the primary report: • If Table A is indexed, and if the primary report has a selection formula that passes down range limit conditions for the indexed field, the program ru
25 Understanding Databases Linking tables Subreports and SQL databases If you are creating a primary report based on Table A and a subreport based on Table B: • The number of subreports that are run is determined by the selection formula situation in the primary report: • If there is a selection formula and it passes down range limits on Table A, the program runs a subreport only for those records that satisfy range limit conditions (2).
Understanding Databases Linking tables In this situation, the program sees that it can pass down the condition before the And operator but not the condition after. Since the only records that will meet the second condition will have to meet the first as well, the program passes down the first condition, retrieves the data set that satisfies the condition, and then applies the second condition only to the retrieved data. The rule for AND situations is that the program passes down whatever conditions it can.
25 Understanding Databases Linking tables field. Since you know that Product 2 did not begin shipping until July of 1995, you can improve speed by limiting your report to orders placed in and after July 1995 using the selection formula. In such a case, the program uses the Order Date index to retrieve only those orders from July 1995 and afterward (a small subset of the entire database) and then searches for the occurrences of Product 2 in that subset, not in the entire database.
Understanding Databases Linking tables • When linking direct-access database tables using native drivers (nonSQL), the only join type available is Left Outer join. Changing the index used in linking When using the Smart Linking feature to link tables using a field that is a component of multiple indexes (two or more), Crystal Reports selects one of the indexes for the link. That index may or may not be the one you want to use.
25 Understanding Databases Linking tables In this case, the links will be processed first between the Credit/Customer tables, then between the Customer/Orders tables, and finally between the Orders/Orders Details tables. The Links Order dialog box shows you this default order and allows you to change the positions in the processing hierarchy as you require. Note: Different link orders may result in different data sets returned for use in your report.
Understanding Databases Linking tables Inner join An Inner join is the standard type of join. The result set from an Inner join includes all the records in which the linked field value in both tables is an exact match. For instance, you can use an Inner join to view all customers and the orders they have placed. You will not get a match for any customer who has not placed orders. Customer Table Customer Table Orders Table Customer ID Customer Name Order Amount 52 Allez Distribution 25141.
25 Understanding Databases Linking tables Customer Table Customer Table Orders Table Customer ID Customer Name Order Amount 58 La Bomba de Bicicleta 1956.20 60 Mountain Toad 24580.50 62 SFB Inc. 7911.80 63 Sierra Bicycle Group 19766.20 63 Sierra Bicycle Group 12763.95 64 Sierra Mountain 8233.50 54 Bicicletas Aztecas 55 Deely MTB Inc. Note: Left Outer and Right Outer joins are handled differently in the SQL language from other join types.
Understanding Databases Linking tables Customer Table Orders Table Orders Table Customer ID Order ID Order Amount 58 20 1956.20 60 16 24580.50 62 19 7911.80 63 28 19766.20 63 32 12763.95 64 14 8233.50 25 10320.87 Note: Left Outer and Right Outer joins are handled differently in the SQL language from other join types. If the database is accessed through ODBC, Crystal Reports uses ODBC syntax in the SQL statement.
25 Understanding Databases Linking tables Customer Table Orders Table Orders Table Customer ID Order ID Order Amount 60 16 24580.50 62 19 7911.80 63 28 19766.20 63 32 12763.95 64 14 8233.50 25 10320.87 65 66 Not Enforced When you select this option, the link you’ve created is used only if it’s explicitly required by the Select statement. Your users can create reports based on the selected tables without restriction (that is, without enforcement based on other tables).
Understanding Databases Linking tables Equal [=] link The result set from an Equal link includes all the records where the linked field value in both tables is an exact match. In the following example, the Customer table is linked to the Orders table by the Customer ID field. When the program finds a Customer ID in the Orders table that matches a Customer ID in the Customer table, it displays information for the corresponding records in both tables.
25 Understanding Databases Linking tables With this in mind, you can link the SalesRep table to the Manager table by the Salary field in each table using a Greater Than link: SELECT SalesRep.‘Last Name‘, SalesRep.‘Salary‘, Manager.‘Last Name‘, Manager.‘Salary‘ FROM ‘SalesRep‘ SalesRep, ‘Manager‘ Manager WHERE SalesRep.‘Salary‘ > Manager.
Understanding Databases Linking tables This statement might produce data such as this: SalesRep Table SalesRep Table Manager Table Manager Table Last Name Salary Last Name Salary Davolio $35,000.00 Fuller $32,000.00 Davolio $35,000.00 Brid $30,000.00 Davolio $35,000.00 Buchanan $29,500.00 Davolio $35,000.00 Martin $35,000.00 Dodsworth $48,300.00 Hellstern $45,000.00 Dodsworth $48,300.00 Fuller $32,000.00 Dodsworth $48,300.00 Brid $30,000.00 Dodsworth $48,300.
25 Understanding Databases Linking tables Manager Table Manager Table SalesRep Table SalesRep Table Last Name Salary Last Name Salary Brid $30,000.00 Dodsworth $48,300.00 Buchanan $29,500.00 Davolio $35,000.00 Buchanan $29,500.00 Dodsworth $48,300.00 Buchanan $29,500.00 Patterson $30,000.00 Martin $35,000.00 Dodsworth $48,300.00 Hellstern $45,000.00 Dodsworth $48,300.
Understanding Databases Linking tables Not Equal [!=] link The result set from a Not Equal link includes all records in which the linked field value in the primary table is not equal to the linked field value in the lookup table. This type of link can be used to find possible combinations of items when a table is joined to itself (a self-join). For example, a company can have a table listing all products they sell.
25 Understanding Databases Using SQL and SQL databases Using SQL and SQL databases Perhaps the most popular and most powerful database formats are DBMS applications based on the Structured Query Language (SQL).
Understanding Databases Using SQL and SQL databases An SQL query is an SQL statement designed specifically to request data from one or more SQL databases. Some SQL applications require that you type in an SQL query directly using a text editor, while others provide graphical user interfaces that lead you through the process of querying an SQL database. In the latter case, the application must create an SQL statement based on the information you provide.
25 Understanding Databases Using SQL and SQL databases Many modern computer applications are based on this client/server architecture. A simple client/server application has two parts: a server-based application that is located on a network server machine, and a client-based application that is located on a user’s workstation.
Understanding Databases Using SQL and SQL databases network. SQL client software usually consists of, at the very least, an SQL statement editor that you can use to write and execute SQL statements, and an underlying communications layer that works with the SQL server application over the network. When you execute an SQL statement, the client software passes the statement to the communications layer, which sends the statement over the network to the server software.
25 Understanding Databases Using SQL and SQL databases How does Crystal Reports use SQL? When you connect to an SQL database, Crystal Reports acts as an SQL client application, connecting to your SQL server through your network. When you design a report that accesses SQL data, Crystal Reports builds an SQL query. This query can be seen by choosing Show SQL Query from the Database menu. This SQL query is a representation of the SQL statement that Crystal Reports sends to the SQL server.
Understanding Databases Using SQL and SQL databases FROM The FROM clause indicates the sources of the database fields specified in the SELECT clause. FROM lists actual database tables that include the fields and records containing the requested data. The FROM clause generated by Crystal Reports precedes the name of each table with the alias it uses to identify the table in your report. The following example illustrates the FROM clause used with the SELECT clause: SELECT TABLEA.‘CUSTNAME‘, TABLEA.
25 Understanding Databases Server-side processing ORDER BY The ORDER BY clause specifies that the database records retrieved be sorted according to the values in a specific field. If you do not use the ORDER BY clause, the program retrieves records in the order in which they appear in the original database.
Understanding Databases Server-side processing • Lower transfer time from the server to the client. Here’s how server-side processing works: by using SQL pass-through technology to send an SQL statement to the database server and retrieve an initial set of data, Crystal Reports off-loads much of the data retrieval and sorting work onto the server system, thus freeing up local memory and resources for more important tasks.
25 Understanding Databases Server-side processing Note: • • When you drill-down on a hidden section of a report, with the processing being done on the server, connection to the server will be automatically initiated. If the client is disconnected from the server (for example, if you download a report onto your laptop and you work on it from a remote location), then drilling-down on data will produce an error since the database is not available.
Understanding Databases Mapping database fields To view the current SQL statement for the active tab, choose Show SQL Query from the Database menu. The Show SQL Query dialog box appears, displaying the SQL statement. Note: You can use the Formula Workshop to edit SQL expressions to be processed on the server. Enabling server-side processing 1. On the File menu, click Report Options. 2. Select Perform Grouping on Server on the Report Options dialog box.
25 Understanding Databases Mapping database fields • • The lower-left box displays the names of mapped report fields. When you map fields in the upper boxes, they appear in the lower boxes. The lower-right box displays the names of mapped database fields. When you map fields in the upper boxes, they appear in the lower boxes. For each database field that you have changed, highlight the report field and the database field in the upper sections and click Map.
Understanding Databases Mapping database fields Note: Report fields that are left unmapped are removed from the report. Using the Verify Database process When you choose Verify Database from the Database menu, the program checks the active databases and reports. If it detects changes, the report must be adapted to prevent errors.
25 Understanding Databases Mapping database fields the same name, the Map Fields dialog box does not appear, and you need to verify the database when you have finished setting the location. For more information, see “Using the Verify Database process” on page 521. Remapping altered database fields Use the Map Fields dialog box to remap existing report fields in the active database if they have been altered. u To remap an altered database field 1.
Understanding Databases Saved Data Indexes Saved Data Indexes You can increase the performance of a Crystal report by indexing its saved data. When you create a Saved Data Index on a particular field, Crystal Reports can more efficiently filter on that field. In particular, you will achieve considerable performance gains—especially in larger reports—by indexing fields that are referred to by record selection formulas.
25 Understanding Databases Saved Data Indexes Once you have created Saved Data Indexes, they work entirely in the background. Users don’t know that the saved data is indexed, and the grouping, sorting, or formatting of the report doesn’t change at all. The indexes merely allow Crystal Reports to locate particular records quickly, without passing through the saved data in its entirety.
Understanding Databases Unicode support in Crystal Reports For instance, don’t index a field such as “Last Year’s Sales,” whose values are likely to be distinct from one another. If you do so, a separate index is created for each and every value in the field. u 1. To index saved data Open your report in the Crystal Reports. 2. On the Report menu, click Report Bursting Indexes. 3. In the Saved Data Indexes dialog box, select the fields that you want indexed within the saved data. 4.
25 Understanding Databases For additional information 526 Crystal Reports User’s Guide
appendix Report Processing Model
A Report Processing Model Overview Overview Crystal Reports uses a three-pass reporting method to generate reports. The sections below describe what happens during each step of this process. To see a visual representation, refer to the flow-chart at the end of this appendix. What is a “pass”? A pass is a process that Crystal Reports uses each time the data is read or manipulated. Depending on the complexity of the report Crystal Reports may make 1, 2, or 3 passes over the data.
Report Processing Model Overview • Cross-Tab, chart, and map generation. Only Cross-Tabs, charts, and maps that are based entirely on database fields and recurring formulas are generated in Pass 1. If these objects include running totals and/or PrintTime formulas, they are generated in Pass 2. • Storage of saved data. After the totaling process is complete, all of the records and totals are stored in memory and to temporary files.
A Report Processing Model Overview totals and summaries are calculated during Pass 1, but the group selection formula filters the data again in Pass 2. Running total fields can be used instead of summaries to total data in reports with a group selection formula. Pass 3 In the third, and final pass, the total page count is determined. This applies to reports that use the total page count, or Page N of M special fields.
appendix Crystal Reports Error Messages
B Crystal Reports Error Messages Drive:\filename.extension Drive:\filename.extension This document could not be opened. It does not appear to be a Crystal Reports document. Context Occurs when attempting to open a Crystal report file that does not have an RPT extension. Reason This error occurs because the report has become corrupted. Resolution A backup copy of the report must be used in place of the corrupted copy. Drive:\test.rpt This document could not be opened.
Crystal Reports Error Messages Failed to open the connection. Details: [Database Vendor Code: ]. Context Occurs when refreshing or previewing a Crystal report that does not contain saved data. Resolution Ensure that the database connector used by the report is installed on all machines where the report can be run.
B Crystal Reports Error Messages Failed to open the connection. Details: [Database Vendor Code: ]. Failed to retrieve data from the database. Details: [Database Vendor Code: ]. Context Occurs when refreshing or previewing a Crystal report that does not contain saved data. Reason The report SQL queries that have been sent to the server cannot be executed, because an error was returned by the database server after it processed the queries.
appendix Creating Accessible Reports
C Creating Accessible Reports About accessibility About accessibility When you create Crystal reports for a large audience across the organization—and around the world—you need to account for the diverse needs of that audience. Report designers often create reports for specific languages, countries, job tasks, or work groups, but it is also important to consider the accessibility requirements of users.
Creating Accessible Reports About accessibility Accessible content is easier to export to simple formats that are more compatible with mobile phone browsers, personal digital assistants (PDAs), and other devices with low-bandwidth connections. Some people may not have a keyboard or a mouse. They may have a text-only screen, a small screen, or a slow Internet connection. Accessible design makes it easier for people with limited technology to access information.
C Creating Accessible Reports About accessibility Organizations and governments worldwide are adopting the accessibility recommendations of the W3C. In Australia, the Disability Discrimination Act includes standards for web site accessibility. Similar guidelines have been introduced in the United Kingdom and throughout Europe.
Creating Accessible Reports Improving report accessibility In the BusinessObjects Enterprise web desktop, the main user interface for working with reports through BusinessObjects Enterprise, the ability to log on and view reports is accessible for most users. However, other areas, such as new account sign up and scheduling, may not be accessible.
C Creating Accessible Reports Improving report accessibility other assistive devices may follow the order specified in the HTML. To make a report accessible, you must add objects to reports in the order that you want a screen reader to read them. For example, you place Quarter, Year, and Invoice fields in the Details section and then add the report title “Invoices by Quarter” to the Report Header.
Creating Accessible Reports Improving report accessibility Text The most common accessibility issue encountered by report designers is also one of the easiest to resolve: providing text-only versions of non-text objects. A non-text object is an object that conveys meaning through a picture or sound. Non-text objects include pictures, charts, graphical buttons, graphical representations of text, sounds, animations, and audio or video clips.
C Creating Accessible Reports Improving report accessibility Providing text-only alternatives If there are too many non-text objects on a report, or if you do not have the resources to integrate accessible design into all of your reports, then you can provide complete text-only alternatives. For reports that represent data using only charts and graphics, for example, you can provide a link to a text-only alternative that provides the same data in data tables and text objects.
Creating Accessible Reports Improving report accessibility Using punctuation To improve the logical flow of spoken text, you may need to add extra punctuation to create pauses. Without extra punctuation, screen readers may read several text objects as one continuous sentence, making the content difficult to understand. For example, information in data tables may be read without stop. To prevent this, you can break up information in data tables by inserting periods between fields.
C Creating Accessible Reports Improving report accessibility Finding the right balance between text and non-text objects Text equivalents are very flexible and often the best solution for accessibility, but they are not always necessary or preferred. Not all non-text objects require text equivalents. You need to include text alternatives only for non-text objects that provide information or navigation elements that the user cannot do without.
Creating Accessible Reports Improving report accessibility Contrasting colors Users with limited vision may be unable to distinguish between colors. To test the color contrast in your report, print or view a black and white copy. You should be able to distinguish between values or fields displayed in different colors (in a pie chart, for example). If you cannot distinguish between colors on the report, try different colors or use gray shading.
C Creating Accessible Reports Improving report accessibility Other common situations where color may be used to provide important information include: • Highlighting To highlight particular values in a table, do not change only the color of the value. If you highlight outstanding invoices in red, for example, they may look the same as the paid invoices to someone with limited vision. In the Highlighting Expert dialog box, change a font characteristic other than color, such as font style.
Creating Accessible Reports Designing for flexibility • • Use navigation consistently. Provide the opportunity to skip repetitive navigation links. Parameter fields When you include parameter fields in a report, make sure they are clear and simple. Although parameter fields can be a useful tool for providing accessible content, they can also introduce several accessibility concerns. It is important to test all parameter fields for accessibility.
C Creating Accessible Reports Designing for flexibility Using this parameter field, you can conditionally format objects, or conditionally suppress sections that address different access needs. Or you can provide different display options by using subreports. u To create an accessibility parameter field 1. In Crystal Reports, on the View menu, click Field Explorer. 2. In the Field Explorer, right-click Parameter Fields and click New. 3.
Creating Accessible Reports Designing for flexibility u 1. To apply accessible settings to font size conditionally Open the report in the Design tab of Crystal Reports. 2. In the Details section, right-click the field you want to conditionally format, and select Format Field. 3. In the Format Editor, click the Font tab. 4. Click the Formula button that corresponds to the Size list. The Format Formula Editor opens a new formula named Font Size. 5.
C Creating Accessible Reports Designing for flexibility u To suppress an accessible section 1. Right-click the left boundary of the section you want to suppress conditionally, and click Section Expert. 2. In the Section Expert, click the Formula button that corresponds to the Suppress (No Drill-Down) setting. The Format Formula Editor opens a new formula named Suppress (No Drill-Down). 3.
Creating Accessible Reports Improving data table accessibility Improving data table accessibility Large tables of data can be difficult to interpret if a person is using a non-visual means of accessing the web, such as a screen reader. People using screen magnifiers or the Zoom feature may also find data tables hard to navigate because they cannot see the table headings at all times.
C Creating Accessible Reports Improving data table accessibility As with all objects in reports, the order in which you place text objects on the report can affect accessibility. Screen readers read the objects in the order they were originally added. (For details, see “Placing objects in order” on page 539.) The correct placement order is critical when you add a text object that identifies the contents of a particular column in a data table.
Creating Accessible Reports Improving data table accessibility Labelling data tables conditionally Although adding text objects is relatively easy to implement, it does not address all accessibility concerns. Invisible text is read by screen readers, but does not help people with limited vision. You can allow the user to choose whether or not to display text descriptions in the data table by conditionally formatting or suppressing text objects.
C Creating Accessible Reports Improving data table accessibility Note: The report shown also uses the ?Access parameter field to enable the Can Grow option (also on the Common tab of the Format Editor) and increase the font size for people with visual impairments. When the user chooses No for the ?Access parameter field, the conditional formula suppresses the text objects, leaving spaces in the report in place of the text objects.
Creating Accessible Reports Improving data table accessibility @Last Name If {?Access}="Yes" then "Employee last name is " + {Employee.Last Name} + "." else {Employee.Last Name} @Salary If {?Access}="Yes" then {Employee.Last Name} + "'s Salary is " + ToText({Employee.Salary}) + "." else ToText({Employee.Salary}) Notice the added punctuation. The periods at the end of each formula improve screen reader legibility by creating a pause between fields.
C Creating Accessible Reports Accessibility and BusinessObjects Enterprise • • Ensure that headings provide enough information to clearly identify the values that they label. To test a table’s accessibility, read its headings and values in a linear fashion from left to right and from top to bottom. For example, if a report displays last and first name fields for each customer, it may read better if it displays first name followed by last name.
Creating Accessible Reports Accessibility and customization Setting accessible preferences for BusinessObjects Enterprise For the best accessibility support in BusinessObjects Enterprise, you need to set certain display preferences. For the BusinessObjects Enterprise web desktop, display objects in the Action view. The Action view is more accessible because it provides a text list of the available reports and does not use shortcut menus for report commands.
C Creating Accessible Reports Accessibility and customization “Resources” on page 559. The following list provides some common accessibility issues that may cause problems when you customize Crystal Reports or BusinessObjects Enterprise content. • Frames Frames should be clearly labelled, for easier identification and navigation. Provide text at the top of the frame that describes its purpose.
Creating Accessible Reports Resources • Applets and plug-ins If a report needs an applet, plug-in, or other application on the client machine in order to interpret page content, the plug-in or applet must follow accessibility guidelines. If you attach multimedia or other additional resource files to your report, such as PDF or Real Audio files, provide a link to install the required plugins or software, and ensure that the required software also meets accessibility design standards.
C Creating Accessible Reports Resources 560 Crystal Reports User’s Guide
appendix Business Objects Information Resources
D Business Objects Information Resources Documentation and information services Documentation and information services Business Objects offers a full documentation set covering its products and their deployment. Additional support and services are also available to help maximize the return on your business intelligence investment.
Business Objects Information Resources Customer support, consulting and training Send us your feedback Do you have a suggestion on how we can improve our documentation? Is there something you particularly like or have found useful? Drop us a line, and we will do our best to ensure that your suggestion is included in the next release of our documentation: documentation@businessobjects.com.
D Business Objects Information Resources Useful addresses at a glance Looking for the best deployment solution for your company? Business Objects consultants can accompany you from the initial analysis stage to the delivery of your deployment project. Expertise is available in relational and multidimensional databases, in connectivities, database design tools, customized embedding technology, and more. For more information, contact your local sales office, or contact us at: http://www.businessobjects.
Index Numerics 3-D Riser chart 284 3-D Surface chart 284 A absolute formatting 260 accessibility 536 and BusinessObjects Enterprise 556 and Crystal Reports 536 benefits of 536 design considerations 539 guidelines 537 resources 559 accounting conventions, using 266 adding calculations to OLAP grid 382 filters to OLAP grid 382 Alerts.
Index security 133, 134 versioning 134 BusinessObjects Enterprise Repository.
Index conditional formatting 270 for accessibility 548 using Highlighting Expert 275 with parameter fields 462 conditional group sorting 174 conditional messages, printing in form letters 228 conditional on/off properties 271 conditional running totals, creating for group 207 constant formulas 528 consultants, Business Objects 564 Context Report Part, user scenarios 406 contrast, color 544 Cross-Tab Report Wizard 47 cross-tabs 330 customizing row/column labels 344 formatting 346 individual fields 347 row/c
Index sample 46 selecting 49 thread-safe drivers 143 Date fields customizing 263 formatting 262 date ranges, for record selection 162 Date/Time fields customizing 263 formatting 262 dates, to select records 161 DBMS, SQL 512 default printer 257 deleting blank lines 220 sections 215 Dependency Checker, using 112 descending sort order 169, 171 design solutions 235 Design Tab 87 areas 88 compared to Preview Tab 92 horizontal guidelines 90 resizing sections 90 sections 51 splitting sections 90 vertical guideli
Index placing charts 286 data 98 database fields on 98 formula fields 98 maps 304 parameter fields 99 running total fields 100 special fields 100 SQL expression fields 98 text objects 100 printing characteristics 78 record selection 105 resizing fields 54 sections to add white space 268 sections to delete white space 269 selecting multiple objects 252 spacing between text-based objects 245 splitting and resizing sections 217 suppressing sections to delete white space 270 using Design Tab areas 88 HTML Prev
Index exporting destinations 389 format types for exporting 385 reports 385 to a disk file 390 to an application 389 to an Exchange Folder 391 to Excel 389 to fax 384 to Lotus Domino 392 to Microsoft Mail 393 F faxing reports 384 feedback, on documentation 563 field headings, inserting 59 field spacing, balancing 63 fields accessing BLOB data 102 alias 485 appearance 53 changing default formats 262 deleting 62 formatting individual in cross-tab 347 several at a time 347 inserting 51 additional 54 group nu
Index modifying lines 264 objects 58 OLAP grid 375 properties 260 setting highlighting priorities 278 several cross-tab fields at a time 347 using the Format Painter 279 formatting, and accessibility 543 Formula Editor 424 Formula Expert 426 formula fields charting on 287 inserting 98 linking to/from 479 Formula Workshop 423 Workshop Tree 423 formulas 418 adding 426 and running totals 154 and summaries 154 Boolean 271 components 419 entering 425 constant 528 copying from one report to another 429 from onli
Index suppressing 201 group selection formulas creating 178 troubleshooting 178 group sorting 529 group values, sorting summarized 190 grouping 171 and performance 151 data in intervals 180 hierarchically 183 on first letter of name 182 on server 151 original sort direction 171 reports 64 specified sort order 171 with SQL expressions 153 groups calculating a percentage 196 creating conditional running totals for 207 custom 173 running totals 206 running totals using formula 210 editing 189 selecting with S
Index J join types 500 full outer 503 inner 501 left outer 501 right outer 502 L languages, SQL 514 layouts chart 282 map 300 left outer join 501 less than link 507 less than or equal to link 508 line chart 284 zooming 295 line spacing 255 lines adding 264 adding blank conditional 220 modifying 264 link relationships 490 link types 500 equal 505 greater than 505 greater than or equal to 506 less than 507 less than or equal to 508 not equal to 509 linked bitmap image object 326 SQL tables 495 subreports an
Index maps 300 centering 315 changing borders 316 geographic maps 314 layers 312 titles 311 type 311 creating on details fields with Advanced layout 304 on group fields with Group layout 307 on OLAP cube with OLAP layout 309 data mismatches 313 drilling down 304 editing with Map Expert 311 inserting on Cross-Tabs 308 panning 315 types of 301 using underlay feature 316 where to place 304 zooming in and out 314 members displaying captions or names 379 menu commands, dynamic OLE 322 Microsoft Mail, exporting
Index OLAP grid objects 368 OLAP Report Wizard 47 OLE 318 and the Picture command 322 dynamic menu commands 322 embedded objects 325 functionality 319 linked vs. embedded objects 325 OLE objects copying and pasting 320 embedded 325 inserting in reports 320 linked vs.
Index prompt groups, described 446 prompting best practices 456 considerations 439 creating a dynamic prompt 449 creating a dynamic prompt that cascades 451 creating a static prompt 446 overview 438 prompts building 356 combining with filters 357 properties conditional attribute 271 conditional on/off 271 prototypes, developing on paper 80 publishing to BusinessObjects Enterprise.
Index importing text-based objects from a file 245 page margins 257 placing multi-line, text-based objects 244 placing text-based objects 242 pre-printed forms 238 section characteristics 236 setting page orientation and paper size 258 TrueType fonts 256 report design, key strategies 133 report experts.
Index placing charts 286 data on 98 database fields on 98 maps 304 special fields on 100 SQL expression fields 98 text objects on 100 previewing for the first time 460 printing area characteristics 78 refreshing data 460 running total fields on 100 saving 59 selecting data source 94 database 49 sorting data with sort fields 168 records 105 splitting and resizing sections 217 standard view to display 91 stating purpose 74 subreport linking 471 subtotals 105 summaries 105 summarize for usability 138 totaling
Index sections 236 deleting 215 Details 88 identifying 89 inserting 214 making read-only 261 merging two related 216 moving 215 multiple in report 218 Page Header 88 Report Header 88 resizing 217 resizing to delete white space 269 splitting 217 suppressing to delete white space 270 sections, and accessibility 549 SELECT clause 514 Select Expert 157, 177 and Formula Editor 159 for group selection 177 selecting multiple objects 252 selection criteria 60 selection formulas and performance 144 parameter fields
Index ORDER BY clause 516 SELECT clause 514 WHERE clause 515 SQL, viewing 355 standard deviation option, Ranged map 302 standard group headers, creating 198 Standard Report Wizard 47 stock chart 285 stored procedures 513 and performance 144 subreports 470 adding captions to 481 and accessibility 542, 550 and SQL databases 496 caution when using 138 creating on-demand 481 different views of same data 482 for performance 138 inserting 473 linked vs.
Index titles adding a title page 108 inserting 57 maps 311 Top N selecting groups 191 selecting groups conditionally 193 selecting percentages 191 selecting percentages conditionally 193 sorting 529 Top N/Sort Group Expert 190 totaling 105 and performance 151 with SQL expressions 153 totals, running 203 training, on Business Objects products 564 troubleshooting group selection formulas 178 record selection formulas 163 TrueType fonts 256 two-pass reporting 528 U underlay feature using with charts 297 usin
Index 582 Crystal Reports User’s Guide
