SAP Crystal Reports 2011 Users Guide ■ SAP Crystal Reports 2011 Support Package 2 2011-05-16
Copyright © 2011 SAP AG. All rights reserved.SAP, R/3, SAP NetWeaver, Duet, PartnerEdge, ByDesign, SAP Business ByDesign, and other SAP products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of SAP AG in Germany and other countries.
Contents 3 Chapter 1 Introduction to SAP Crystal Reports 2011............................................................................21 1.1 1.2 1.2.1 1.2.2 About Crystal Reports...........................................................................................................21 Chapter 2 Quick Start............................................................................................................................23 2.1 2.1.1 2.2 2.2.1 2.2.2 2.2.3 2.2.4 2.3 2.3.1 2.3.2 2.3.3 2.3.
Contents 4 3.2.5 3.3 Determining printing area characteristics................................................................................55 Chapter 4 Introduction to Reporting......................................................................................................59 4.1 4.2 4.2.1 4.2.2 4.3 4.3.1 4.3.2 4.3.3 4.4 4.4.1 4.4.2 4.4.3 4.4.4 4.4.5 4.4.6 4.4.7 4.4.8 4.4.9 4.4.10 4.4.11 4.4.12 4.4.13 4.5 4.5.1 4.5.2 4.5.3 4.5.4 4.6 Report creation options.....................................
Contents 5 5.4.1 5.5 5.5.1 5.5.2 5.5.3 5.5.4 5.6 5.6.1 5.6.2 5.6.3 5.6.4 5.7 5.7.1 5.8 5.8.1 5.9 5.9.1 5.10 To add a new subfolder or subcategory..................................................................................99 Chapter 6 Designing Optimized Web Reports.....................................................................................107 6.1 6.1.1 6.2 6.2.1 6.3 6.3.1 6.3.2 6.3.3 6.3.4 6.3.5 6.3.6 6.4 6.4.1 6.4.2 6.4.3 6.4.4 6.4.5 6.5 6.5.1 6.5.2 6.5.3 Overview.....................
Contents 6 6.5.4 6.5.5 6.6 6.6.1 6.6.2 6.6.3 6.6.4 6.6.5 Incorporating parameter fields into record selection formulas...............................................124 Chapter 7 Record Selection.................................................................................................................129 7.1 7.1.1 7.1.2 7.1.3 7.1.4 7.1.5 7.1.6 7.2 7.2.1 7.3 7.4 7.4.1 7.4.2 Selecting records..........................................................................................................
Contents 7 8.3.1 8.3.2 8.3.3 8.3.4 8.4 8.4.1 8.4.2 8.5 8.5.1 8.6 8.6.1 8.6.2 8.6.3 To summarize grouped data.................................................................................................166 Chapter 9 Running Totals....................................................................................................................181 9.1 9.1.1 9.2 9.2.1 9.2.2 9.2.3 9.2.4 9.3 9.3.1 Understanding running totals............................................................................
Contents 8 10.5.1 10.5.2 10.5.3 Working with text objects.....................................................................................................199 Chapter 11 Formatting...........................................................................................................................207 11.1 11.2 11.2.1 11.2.2 11.2.3 11.2.4 11.2.5 11.3 11.3.1 11.3.2 11.3.3 11.3.4 11.3.5 11.3.6 11.3.7 11.3.8 11.3.9 11.3.10 11.3.12 11.3.13 11.3.14 11.3.15 11.3.16 11.3.17 11.3.18 11.3.19 11.3.20 11.
Contents 9 11.5.8 11.5.9 11.5.10 11.5.11 11.5.12 11.6 11.6.1 11.6.2 11.6.3 11.6.4 11.6.5 11.6.6 11.6.7 11.7 11.7.1 11.8 11.8.1 11.8.2 11.8.3 Adding shapes to a report....................................................................................................247 Chapter 12 Charting..............................................................................................................................269 12.1 12.1.1 12.1.2 12.1.3 12.1.4 12.1.5 12.1.6 12.2 12.2.1 12.2.2 12.2.3 12.2.4 12.
Contents 10 Chapter 13 Mapping..............................................................................................................................285 13.1 13.1.1 13.1.2 13.1.3 13.1.4 13.1.5 13.2 13.2.1 13.2.2 13.2.3 13.3 13.3.1 13.3.2 13.3.3 13.3.4 13.3.5 13.3.6 13.3.7 13.3.8 13.3.9 13.3.10 13.3.11 Mapping concepts................................................................................................................285 Chapter 14 OLE.................................................
Contents 11 14.5.3 14.6 14.6.1 14.6.2 14.7 14.7.1 14.7.2 14.7.3 Converting a static OLE object to a bitmap image object......................................................308 Chapter 15 Cross-Tab Objects..............................................................................................................315 15.1 15.2 15.2.1 15.2.2 15.2.3 15.2.4 15.2.5 15.3 15.3.1 15.3.2 15.3.3 15.3.4 15.3.5 15.3.6 15.3.7 15.3.8 15.3.9 15.4 15.4.1 15.4.2 15.4.3 15.4.4 15.4.5 15.5 15.5.1 15.5.2 15.5.3 15.5.
Contents 12 15.6.1 15.6.2 Calculated Members............................................................................................................336 Chapter 16 Building Queries.................................................................................................................343 16.1 16.1.1 16.2 16.2.1 16.2.2 16.2.3 16.3 16.3.1 16.4 16.4.1 16.4.2 16.5 16.5.1 16.5.2 16.5.3 16.5.4 16.5.5 16.5.6 16.6 16.6.1 16.6.2 16.6.3 16.6.4 Connecting to a universe................................
Contents 13 17.4 17.4.1 17.4.2 17.4.3 17.4.4 17.5 17.5.1 17.5.2 17.5.3 17.5.4 17.5.5 17.5.6 17.6 17.6.1 17.6.2 17.7 Formatting data in an OLAP grid...........................................................................................369 Chapter 18 Printing, Exporting, and Viewing Reports............................................................................379 18.1 18.1.1 18.1.2 18.1.3 18.1.4 18.1.5 18.2 18.2.1 18.2.2 18.3 18.3.1 Distributing reports.........................................
Contents 14 Chapter 20 Using Formulas...................................................................................................................417 20.1 20.1.1 20.2 20.2.1 20.2.2 20.3 20.4 20.4.1 20.4.2 20.5 20.5.1 20.5.2 20.5.3 20.5.4 20.5.5 20.6 20.6.1 20.6.2 20.7 20.7.1 20.7.2 20.8 Formulas overview...............................................................................................................417 Chapter 21 Parameter Fields and Prompts........................................
Contents 15 21.5 21.5.1 21.6 21.6.1 21.7 21.8 21.8.1 21.8.2 21.8.3 21.8.4 21.8.5 21.9 21.9.1 21.9.2 21.9.3 21.9.4 21.10 21.10.1 21.10.2 21.10.3 21.11 21.11.1 21.11.2 21.12 21.12.1 21.12.2 21.12.3 21.12.4 21.12.5 21.12.6 21.12.7 21.12.8 21.12.9 21.12.10 21.12.11 Creating a parameter with a dynamic prompt.......................................................................466 Chapter 22 Subreports...........................................................................................................
Contents 16 22.2.1 22.2.2 22.2.3 22.3 22.3.1 22.4 22.4.1 22.5 22.5.1 22.5.2 22.6 22.6.1 22.6.2 22.7 22.7.1 22.8 22.8.1 22.9 Previewing subreports..........................................................................................................495 Chapter 23 Understanding Databases...................................................................................................505 23.1 23.1.1 23.1.2 23.2 23.2.1 23.2.2 23.2.3 23.2.4 23.2.5 23.3 23.3.1 23.4 23.5 23.5.1 23.5.2 23.5.3 23.5.4 23.5.
Contents 17 23.5.9 23.5.10 23.6 23.6.1 23.7 23.7.1 23.7.2 23.7.3 23.8 23.8.1 23.8.2 23.8.3 23.9 23.10 Link processing order...........................................................................................................531 Chapter 24 Integrating Business Applications with Crystal Reports.....................................................555 24.1 24.1.1 24.1.2 24.1.3 24.2 24.2.1 24.2.2 24.3 24.3.1 24.3.2 24.3.3 24.3.4 24.3.5 24.3.6 24.3.7 24.3.8 24.3.9 24.3.10 24.3.11 24.4 24.4.1 24.4.
Contents 18 Chapter 25 Accessing Data Sources.....................................................................................................633 25.1 25.1.1 25.2 25.2.1 25.2.2 25.2.3 25.2.4 25.3 25.3.1 25.3.2 25.3.3 25.3.4 25.3.5 25.3.6 25.4 25.4.1 25.4.2 25.4.3 25.4.4 25.5 25.6 25.7 25.8 Introduction..........................................................................................................................633 Chapter 26 Report Processing Model......................................
Contents A.5 Failed to retrieve data from the database. Details: [Database Vendor Code: ].............................................................................................................................667 Chapter 27 Creating Accessible Reports...............................................................................................669 27.1 27.1.1 27.1.2 27.1.3 27.2 27.2.1 27.2.2 27.2.3 27.2.4 27.2.5 27.3 27.3.1 27.3.2 27.3.3 27.4 27.4.1 27.4.2 27.5 27.5.1 27.6 27.
Contents 20 2011-05-16
Introduction to SAP Crystal Reports 2011 Introduction to SAP Crystal Reports 2011 1.1 About Crystal Reports SAP Crystal Reports 2011 (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.
Introduction to SAP Crystal Reports 2011 as a reference for your basic reporting needs as well as an introduction to new concepts in report creation. 1.2.1 Locate information quickly • Access online help from the Crystal Reports help menu. Use the Contents tab to view all major sections in the online help and drill down to specific headings within each section, the Index tab to view topics in alphabetical order, and the Search tab to enter a keyword to view all the sections that relate to the keyword.
Quick Start Quick Start 2.1 Learning how to use Crystal Reports You can teach yourself how to use Crystal Reports by choosing from the methods available in this section: • You can study the sample reports and sample database included with Crystal Reports. You will find the sample reports and sample database for Crystal Reports at the SAP Business Objects Support web site. • You can use the detailed descriptions and instructions in the Quick start for new users.
Quick Start 2.2 Report Creation Wizards The tutorials in this section show you how to build a report from scratch. As a complement or an alternative, however, you may want to use the Report Creation Wizards available on the Start Page. There are four Report Creation Wizards: • Standard • Cross-Tab • Mailing Label • OLAP 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.
Quick Start 2.2.3 Mailing Label The Mailing Labels Report Creation Wizard lets you create a report that is formatted to print on any size mailing label. You can use the Label screen to select a commercial label type, or you can define your own layout of rows and columns for any multi-column style report. 2.2.4 OLAP The OLAP Report Creation Wizard lets you create a report in which your OLAP data is displayed as a grid object.
Quick Start 2.3.1 Before you begin This tutorial assumes you are familiar with Microsoft Windows and uses conventional terms and procedures common to the Windows environment. If you are not familiar with Windows, please refer to the documentation that came with Microsoft Windows for further explanation. The default font for all report sections in the program is set to Arial, 10 point.
Quick Start 2.3.2.1 Selecting a database to use The next step in creating a report is to select a database. Use the Xtreme.mdb sample database for this tutorial. The Xtreme.mdb file is located on the SAP Business Objects Support web site. Copy Xtreme.mdb to a location on your machine that you can access for the tutorial. 2.3.2.1.1 To select a database 1. In the Database Expert dialog box, expand the Create New Connection folder and then expand the Database Files folder; then search for the Xtreme.
Quick Start 3. Select Customer and click the > arrow to add it to the Selected Tables list, and then click OK. The Design tab of the Report Designer appears. 4. Click Field Explorer on the Standard toolbar. The Field Explorer dialog box appears. 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. 2.3.2.
Quick Start 2.3.2.3.1 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 2.3.2.4 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. • If you haven't selected the Show Field Names check box on the Layout tab of the Options dialog box, the object frame contains Xs to indicate that the database field contains a text string.
Quick Start As the cursor is moved over the report, an object frame appears along with the Arrow cursor. 3. Place the fields to the right of the Customer Name field. Both fields appear in the Details section of the report in the same order in which they are listed in the Field Explorer dialog box. 2.3.2.6 Selecting fields When a field is selected, the object frame appears with a handle (box) on its right, left, top, and bottom edge. These handles indicate that the field is selected, and therefore active.
Quick Start 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. You can then switch between designing and previewing the report by clicking the corresponding tab. You have the beginnings of a customer list report, but you still have several fields to add. 2. When you are finished reviewing the report, return to the Design tab by clicking it. 2.3.2.
Quick Start Instead of adding the Contact First Name and Contact Last Name fields as separate objects, you can insert both fields in a text object. This allows you to control the formatting of both fields by making changes to only one object. When you insert fields in a text object, the fields are automatically trimmed (they do not have any extra white space on either side).
Quick Start 2.3.2.11 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. 2.3.2.11.1 To add summary information 1. On the File menu, click Summary Info. The Document Properties dialog box appears with the Summary tab active.
Quick Start 2. Enter information about your report in the text boxes provided. Be sure to enter the title "Customer List" in the Title text box. This information will be used in the next section of the tutorial. 3. Click OK when finished. 2.3.2.12 Adding a title As you can see, the report looks incomplete without a title. Although you can add a title using a text object, you can also tell the program to take the title information directly from the Title text box in the Document Properties dialog box. 2.
Quick Start 3. With the report title object still selected, click Align Center on the Formatting toolbar. The title is centered within the object. 4. Right-click the object and choose Format Field from the shortcut menu. 5. When the Format Editor appears, click the Font tab. 6. Set the report title to a larger, bolder version of the active font by selecting Bold from the Style list and 16 (or a point size suitable to the font you are using) from the Size list. 7.
Quick Start 2.3.2.15 Saving the report 1. Click Save on the Standard toolbar to save your work. Since this is the first time you are saving the report, the Save As dialog box appears displaying the default directory where the file will be saved. Notice that a default file name, based on the report title you defined earlier, is also displayed. 2. Type Custlist.rpt in the File name box and click Save. Your report is saved to the default directory or another directory you chose. 3.
Quick Start For example, it may be useful to have a customer list that only lists customers from the USA. The sample data contains records from the United States and International customers. It is easy to restrict lists like this using the Select Expert. 2.3.3.1 Entering the selection criteria When you scroll through the report, you will see that it contains information for customers from many different countries. In this step, you will limit the number of countries displayed to the USA. 2.3.3.1.
Quick Start Imagine that you are completing the following sentence: Select all records where a customer's country is You complete the sentence with the condition you want the program to use when selecting records for your report. Right now the condition is any value, which implies there is no restriction on the record selection. 5. Click the arrow on the operators box to see what other options you have. Since you want only those records where the Country is USA, select the is equal to condition.
Quick Start Now that the report contains only records from the USA, displaying the Country field in the body of the report is not necessary. You can delete this field before continuing. 2.3.3.2.1 To delete a field 1. Select the Country field and the Country column heading using the Ctrl-click combination. 2. Press Delete on your keyboard. That is all it takes to delete fields from the report. The report should now look similar to this: 2.3.3.
Quick Start The spacing between the fields is much better, but it looks as if the report title is off-center. 4. Click the report title object to select it. 5. Position the cursor on the right handle of the object until the cursor turns into a Resizing cursor. Drag the right edge of the object frame until it is even with the right edge of the data in the Contact Name field object. The report title automatically centers itself based on the size of the object. 2.3.
Quick Start 2. Select Region from the Customer table in the first drop-down list. The program takes all records with the same value in the region field and places them together in a group on the report. 3. Select in ascending order from the second drop-down list. The region grouping will be displayed on the report in alphabetic ascending order. 4. Click OK. Notice that two new sections now appear in the Design tab: GH1 (Group Header #1) and GF1 (Group Footer #1).
Quick Start 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.
Quick Start 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. Highlight the Customer Name field and click the > arrow button to add it to the Sort Fields list. 3.
Quick Start 2.3.5 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. 2.3.5.1 Inserting a company logo In this section, you will place a company logo at the top of the first page of the report. 2.3.5.1.1 To insert a company logo 1. 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.
Quick Start 5. Save the report by clicking Save on the Standard toolbar. You have just completed your first report. 2.4 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. 2.4.1 Choosing a report type and data source 1.
Quick Start 5. After you have added all the tables you want to work with, click OK on the Database Expert. Note: If you select more than one table in the Database Expert dialog box, the Links tab appears. For more information on linking, see Linking multiple tables. The Design tab appears with Report Header, Page Header, Details, Page Footer, and Report Footer areas. A report is created by inserting and formatting items in each of these areas.
Quick Start When you place multiple fields, they appear in the same order that they appear in the Field Explorer dialog box. The program marks the position of each field with a rectangular frame. The characters in the frame indicate whether the field is text (XXX...), number (555...), currency ($555...), date (12/31/99), time (00:00:00), DateTime (12/31/99 00:00:00), or Boolean (T/F).
Quick Start • 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. • Right-click the field to format or to summarize it. A shortcut menu appears listing commands for formatting and summarizing the field. Tip: Many font and formatting options are available on the formatting toolbar.
Quick Start Parameter fields can be used in reports (as title or label prompts), in selection formulas (as selection criteria prompts), and in formulas (for a variety of purposes including specifying sort fields). See Parameter Fields and Prompts. 10. 11. To add a chart, click Insert Chart on the Insert Tools toolbar. See Charting. To add a map, click Insert Map on the Insert Tools toolbar. See Mapping. 12.
Report Design Concepts Report Design Concepts 3.1 Basic report design The purpose of this section 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. 3.
Report Design Concepts in a haphazard manner, the report may slow the decision-making process or may even encourage incorrect decisions. A good starting place in the development of a report is to write out the purpose of the report in a sentence or two. The purpose statement helps you focus on your primary needs, and it gives the report both a starting point and a goal. Here are some examples of purpose statements.
Report Design Concepts What identifying information is needed in the header and footer? You may wish to include the print date, information on who prepared the report, a block of text to describe the purpose of the report, the range of data covered, or something similar. If you are going to include such information, write it down so you can use it in preparing your prototype. The information can come from a variety of sources, depending on the kind of information you plan to use.
Report Design Concepts Does the data exist or does it need to be calculated? Some report information can be drawn directly from data fields (sales information, for example); other information will have to be calculated based on data field values (for example, sales commission, based on the relationship of sales to quota). In your planning, it can be helpful to segregate or flag data that needs to be calculated from that which can be used directly. See Specifying formulas.
Report Design Concepts want to flag each item that 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 • Formulas placed in this area are evaluated once, at the beginning 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.
Report Design Concepts 3.3 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. 3.3.1 To design a paper prototype 1. Get the same size paper you will be using for the finished report. 2.
Report Design Concepts 58 2011-05-16
Introduction to Reporting Introduction to Reporting 4.1 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 4.2 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. Each of these dialog boxes uses the familiar Windows tree structure to allow you to navigate through the possible choices. 4.2.1 The Database Expert The Database Expert provides an integrated tree view of all data sources you can use with Crystal Reports.
Introduction to Reporting • Database Files • ODBC (RDO) • OLAP • OLE DB (ADO) Note: The data source options available in the Create New Connection 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.
Introduction to Reporting Use this option to rename an existing SQL command in the SAP 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. 4.2.1.2 Links tab Database tables are linked so records from one database match related records from another.
Introduction to Reporting 4.2.2.2 Toolbar and Shortcut Menu The Field Explorer's toolbar provides buttons with tool tips and hot-key combinations. You can right-click any item in the tree view to bring up a shortcut menu. 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.
Introduction to Reporting Use this option to open the Set Prompt Order dialog box, in which you can change the prompt order of your parameter fields. • Show Field Type Use this option to see the field type (string, number, and so on) when you're looking at a list of database fields. The length of string fields is included in brackets at the end of their names. • Sort Fields Alphabetically Use this option to display table fields in alphabetical order (instead of the order they appear in the table).
Introduction to Reporting The Design tab is the place you do most of the initial work when creating a report. It designates and labels the various sections of the report. You can do the initial formatting, place objects in the sections where you want them to appear, specify sorting, grouping, and totaling needs, and so forth. The Design tab provides a very efficient environment for designing a report because you work in the tab with data representations, not with data itself.
Introduction to Reporting This section is used for the body of the report, and is printed once per record. The bulk of the report data generally appears in this section. • Report Footer This section is used for information you want to appear only once at the end of the report (such as grand totals) and for charts and cross-tabs that include data for the entire report. • Page Footer This section usually contains the page number and any other information you want to appear on the bottom of each page.
Introduction to Reporting Like the original areas, each of these newly added areas can contain one or more sections. By default, they each contain a single section. 4.3.1.2 Identifying and working with areas and sections By default, each area contains only a single section. The name for that section appears directly to the left of the section. If you have multiple sections in an area, the sections are designated as a, b, c, and so forth.
Introduction to Reporting 4.3.1.3 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. • Split a section (create two sections from one) by clicking its left boundary. See Splitting a section. • Add horizontal and vertical guidelines by clicking the rulers. See Designing with guidelines .
Introduction to Reporting 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. For shorter reports or reports in which you're primarily interested in seeing the "bottom line" totals, the standard view provides all of the functionality you need.
Introduction to Reporting The Group Tree can be shown or hidden using the Toggle Preview Panel button on the Standard toolbar. The Group Tree 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 • Dynamic parameters that use cascading prompts. On the panel, data parameters are differentiated from non-data parameters with a database icon. When a user modifies the value of a data parameter, the report refreshes and accesses the database to make the corresponding changes. When a user modifies the value of a non-data parameter, the report is formatted or filtered based on saved data within the report. It does not access the database. 4.3.2.2.
Introduction to Reporting In the Preview tab, however, you are working with the actual data. Instead of a field frame representing many field values, the values themselves appear. Some additional things to consider are: • • When you highlight a field or formula field value, you are actually selecting every value in the field: • The program places a sizing frame around the specific value you select. • It highlights every other value in the field.
Introduction to Reporting For information about how to configure the HTML Preview Options, search the Crystal Reports online help for “Smart Tag & HTML Preview tab (Options dialog box)”. 4.4 Creating a new report 4.4.1 Selecting the data source After deciding which option you want to use for creating your report (see Report creation options), the next step is to select a data source to use. Most data sources can be chosen through the Database Expert dialog box.
Introduction to Reporting • Database Files This option shows a list of standard PC databases that reside locally. You can use Find Database File to browse for a PC database using the Open dialog box. • ODBC (RDO) This option shows a list of ODBC data sources you have already configured for use. • OLAP This option opens the OLAP Connection Browser so you can choose an OLAP cube as a data source. • OLE DB (ADO) This option shows a list of OLE DB providers you have already configured for use.
Introduction to Reporting 4.4.3 Linking multiple tables If the report contains data from two or more database tables, they need to be linked at this point when creating reports. Note: It isn't necessary to link tables in reports created from a query or command because any links required by the data have already been processed. 4.4.3.1 To add and link multiple tables 1. Choose Database Expert from the Database menu. The Database Expert dialog box appears. 2.
Introduction to Reporting 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. Note: When manually creating links, the field you are linking "to" must be of the same data type as the field you are linking from.
Introduction to Reporting 4.4.4.1.1 To insert a database field 1. On the Standard toolbar, click Field Explorer. The Field Explorer dialog box appears. To speed the report building process, this dialog box remains on-screen until you close it. You can move this dialog box wherever you wish. 2. Expand the Database Fields folder to see all the tables chosen from the database(s). 3. Expand the individual tables to see all the fields they contain. 4. Select the field you want to appear in the report. 5.
Introduction to Reporting 4. Type the expression in the SQL Expression Editor. Note: For an overview of the formula language, see Formula components and syntax. 5. Click Save. 4.4.4.4 Parameter fields To prompt the user of a report to enter information, create a parameter field. Think of a parameter as a question that the user needs to answer before the report is generated. The information users enter, or the way they respond, determines what appears in the report.
Introduction to Reporting sum of 2 + 4), and then 12 (the sum of 2 + 4 + 6). See Running Totals, for an introduction to running totals. 4.4.4.6 Special fields To display information such as Page Numbers, Print Date, and Report Comments use the commands in the Special Fields tree view of the Field Explorer dialog box. 4.4.4.6.
Introduction to Reporting Text objects are used in reports for a multitude of purposes. They are a powerful way of inserting titles; labeling summaries and other data on your report; and of easily combining database fields. 4.4.4.7.1 To insert a text object 1. On the Insert Tools toolbar, click Insert Text Object. A cursor appears. 2. Position the cursor where you want the text object to appear in the report. Click once on the border of the text object to select it for resizing and moving.
Introduction to Reporting 3. Position the picture object where you want it to appear in the report and left-click once. 4.4.4.9 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.
Introduction to Reporting Note: You can also use this option to connect to SAP Business Objects, Web Intelligence, and OLAP Intelligence documents (this is also known as report linking). Click Create enterprise Hyperlink to create these hyperlinks in Crystal Reports. Tip: Alternatively, you can use the openDocument function in the "Formula Workshop" to create hyperlinks. • Current Website Field Value Select this option if you want the program to create a hyperlink out of the field you selected.
Introduction to Reporting 4.4.5 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. Inserting a BLOB field into your report allows you to access these binary objects as you would other data types. Note: Crystal Reports can also access BLOB objects by reference (that is, dynamically through a file path), so that you do not need to store the BLOB objects in your database.
Introduction to Reporting 4.4.7 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, and so on. For example, a sales report may be designed to only include sales from one product line for the last calendar month. 4.4.
Introduction to Reporting You can drill down on your data, to show the data behind individual groups, using the Drill-down cursor. See Sorting, Grouping, and Totaling. 4.4.9.1 Drill-down cursor Crystal Reports allows you to drill down on group or summary information in the Preview tab in both the Standard and the Group Tree. When you position the cursor over any summary value that you can drill down on, the program displays a Drill-down cursor.
Introduction to Reporting • Information to appear only on the first page of the report goes in the Report Header (RH). • Information to appear only on the last page of the report goes in the Report Footer (RF). • Information to appear at the top of every page goes in the Page Header (PH). • 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.
Introduction to Reporting 4. Move the object frame to the Report Header section and click once to place the frame. 5. With the report title selected, click Section Expert on the Expert Tools toolbar. The Section Expert appears. 6. With the Report Header section highlighted, select the New Page After check box. Now the title will appear on the first page and the report will begin on the second page. 4.4.
Introduction to Reporting Enter the desired information and click OK when finished to return to your report. 4.5 Exploring reports and working with multiple reports Crystal Reports lets you work with your existing reports through the Report Explorer, the Workbench, and the Dependency Checker. This section describes these tools and provides some general information about opening and docking each of the explorers that are available in Crystal Reports. For more information, see Opening and docking explorers.
Introduction to Reporting and so on. Likewise, if you right-click a section node, the menu will contain the options to hide, suppress, format and so on. Note: You cannot add additional fields or report objects when using the Report Explorer, but you can delete them. You can select multiple fields for formatting by using Shift-click or Ctrl-click. 4.5.1.1 Toolbar The Report Explorer's toolbar provides buttons with tool tips.
Introduction to Reporting 4.5.2 The Workbench In the Workbench, you can create projects that contain one or more reports. Use the options on the toolbar to add, remove, or rename folders, reports, and object packages. You can reorganize the files in a folder or folders by dragging and dropping them where you want them to appear. You can also drag report files from Windows Explorer and drop them into the folder of your choice in the Workbench. 4.5.2.
Introduction to Reporting 4.5.2.2 Shortcut Menus You can right-click any item in the projects area of the Workbench to see a shortcut menu. Many of the options that are available on the shortcut menu are also available on the toolbar. This section describes only the additional options that are not available on the toolbar. • Add Object Package When you select this option, the program prompts you to log on to your SAP BusinessObjects Enterprise system.
Introduction to Reporting 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. The Dependency Checker has found an error that you should fix so that the report will run correctly.
Introduction to Reporting • Copy Use this option to copy the message to the clipboard so that you can paste it into another application. • 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)." 4.5.
Introduction to Reporting 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. 4.
SAP BusinessObjects Enterprise Repository SAP BusinessObjects Enterprise Repository 5.1 What is the SAP BusinessObjects Enterprise Repository? The SAP BusinessObjects Enterprise Repository is a database in which you manage shared report objects. You can refresh a report's repository objects with the latest version from your SAP BusinessObjects Enterprise Repository when you publish reports to SAP BusinessObjects Enterprise.
SAP BusinessObjects Enterprise Repository The "Repository Explorer "displays the contents of your SAP BusinessObjects Enterprise Repository as a tree with folders, subfolders, categories, and subcategories. The following folders and categories are supplied through SAP BusinessObjects Enterprise: • • • • Repository Items Enterprise Items Categories Personal Categories You can add subfolders and subcategories.
SAP BusinessObjects Enterprise Repository 5.2.1 To open the SAP BusinessObjects Enterprise Repository 1. Start Crystal Reports and open a report. 2. 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. 3. Click Logon to connect to BusinessObjects Enterprise. For information about how to log on to SAP BusinessObjects Enterprise, see Working with Enterprise folders. The Repository Explorer appears. 4.
SAP BusinessObjects Enterprise Repository The Repository Explorer's toolbar provides buttons with tool tips. These buttons let you add new folders or categories, 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.
SAP BusinessObjects Enterprise Repository • Corporate categories are created by the administrator, or other users who have been granted access to these categories. If you have the appropriate rights, you can create corporate categories. • Personal categories can be created by each user to organize his or her own personal documents. 5.4.1 To add a new subfolder or subcategory 1.
SAP BusinessObjects Enterprise Repository 2. In the Name field of the Object Information dialog box, create a name for your object or image. Note: You cannot use these characters in your object's name: # " { } ; / 3. Enter an author and description if you want, and then click OK. Tip: Author and description information appears in tooltips and can be searched for using the Advanced filtering option. You'll see that your text object or bitmap image has been added to the appropriate folder. 5.5.
SAP BusinessObjects Enterprise Repository 2. Click the Add to Repository button. You'll see that your custom function has been added to the Repository Custom Functions node. Tip: • • You can also add a custom function to the repository by dragging it from the Report Custom Functions node—in the Workshop Tree—and dropping it on a Repository Custom Functions node. The custom function icon in the Report Custom Functions node changes to indicate that the custom function exists in the repository.
SAP BusinessObjects Enterprise Repository Note: • • To reconnect a report object that has been disconnected from its repository, re-add the object or update the repository copy. Objects that remain disconnected from the repository cannot be automatically updated when the report is next opened. You can see that a report object is connected to the repository by opening the Report Explorer and looking for it; if the object has an icon in front of it, it is connected to the repository.
SAP BusinessObjects Enterprise Repository You'll see your custom function has been added to the Report Custom Functions node of the Formula Workshop. Note: You can also add custom functions to a report while creating formulas in the Formula Workshop. For more information about this method, see Creating a formula in the Formula Expert. 5.6.3 Adding a command to a report 1. Click the Database Expert button on the Expert Tools toolbar.
SAP BusinessObjects Enterprise Repository You can modify repository objects by disconnecting them, changing them in a report, and adding them back to the repository. As well, you can rename objects and move them to different folders directly in the repository.
SAP BusinessObjects Enterprise Repository 5.8.1 To set the update option 1. On the File menu, click Options. The Options dialog box appears. 2. Click the Reporting tab. 3. Select the Update Connected Repository Objects on Open check box. 4. Click OK. If you don't want to use this global option, you can also update a report's repository objects by selecting the Update Repository Objects option on the Open dialog box for an individual report.
SAP BusinessObjects Enterprise Repository Tip: Another way to do this is to click the Repository Explorer option under the View menu. 2. In the appropriate folder, select the object you want to remove and press the Delete key. You are asked to confirm the deletion. 3. Click the Yes button. The object is removed from the repository. You can also delete a repository object by right-clicking it and selecting Delete from the shortcut menu. 5.
Designing Optimized Web Reports Designing Optimized Web Reports 6.1 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 6.1.1 Key strategies for optimizing web reports • As your company grows, Crystal Reports and SAP BusinessObjects Enterprise can grow along with you. For details, see Scale with SAP BusinessObjects Enterprise. • For tips and considerations on how to create fast, interactive reports, see Making the right design choices. • For information about getting the most out of your existing database, see Streamlining your reporting environment.
Designing Optimized Web Reports • 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 SAP BusinessObjects Enterprise incorporates proven clustering and load-balancing technology to help you deliver a highly available, reliable information delivery system.
Designing Optimized Web Reports • If you use date and/or time functions in a report's selection formula, the report's data is dependent upon the return values of those formulas. Consequently, when you view a report instance at a later date, the function may cause the report data to be updated from the database. • To ensure that date and time functions return the values that you expect, you can force the evaluation time through the use of the WhileReadingRecords and WhilePrintingRecords functions.
Designing Optimized Web Reports Note: If for some reason you need to keep a report in its original file format, use the Save As command (instead of Save) and enter a new name for the version 2011 report. 6.3.2 Choosing between live and saved data When reporting over the Web, the choice to use live or saved data is one of the most important decisions you'll make.
Designing Optimized Web Reports You can schedule these reports within SAP BusinessObjects Enterprise, so they automatically refresh from the database on a predetermined basis. For example, if your sales database is only updated once a day, or once a week, then you can run the report on a similar schedule and save it with data. Sales representatives then always have access to current sales data, but they aren't hitting the database every time they open a report.
Designing Optimized Web Reports 6.3.3.1 To hide details in a summary report 1. Open your report in Crystal Reports. If you haven't yet created a grouped and summarized report, open Group.rpt from the Feature Examples samples folder. 2. On the Report menu, click Section Expert to open the Section Expert. 3. In the Sections list, click Details. 4. On the Section Expert's Common tab, select the Hide (Drill-Down OK) check box. 5. Click OK. You'll notice the details are now hidden.
Designing Optimized Web Reports Many report objects—such as large cross-tabs, OLAP grids, advanced charts, and maps—are ideal candidates to include in on-demand subreports, so that the object is not processed until you drill down. To insert an on-demand subreport, see Inserting subreports and Creating an on-demand subreport. Tip: You could also place such report objects in a hidden Details section of a report that uses the Perform Grouping On Server option.
Designing Optimized Web Reports 6.3.5 Using other design elements effectively Maps Map rendering is a single-threaded operation that does not scale well. Although maps are supported, you need to be careful in considering the overall effect a map in your report will have on performance. Report Templates If you will be applying the same template to multiple reports, it is best to open the report once and cache it, because the template only needs to be read-only.
Designing Optimized Web Reports • Users view a report using the same parameters. • Users view a report using the same locale settings. • Users do not modify the report. Reports viewed using the Advanced DHTML viewer (or your own custom application that permits report viewing and modification) are processed using the Report Application Server. 6.
Designing Optimized Web Reports When you add multiple database tables to your report, you link the tables on a common field so that records from one table match related records from another. (Table linking works best if your database tables are indexed.) Linking your database tables in this way is usually much faster than incorporating linked subreports into your main report.
Designing Optimized Web Reports ODBC connections to the following database types assume that the ODBC database driver is also thread-safe and, therefore, operate in a multi-threaded manner: • Microsoft SQL Server • Oracle • DB2 • Teradata • Sybase Note: It is also assumed that the database drivers used with JDBC and OLE DB are always thread-safe and, therefore, always operate in a multi-threaded manner. The following native database drivers are thread-safe: • crdb_ado_plus.dll (ADO.
Designing Optimized Web Reports • To select a stored procedure as a data source, see "SQL stored procedures" in the online help. 6.5 Using enhanced record selection formulas The most important thing you can do to speed up report processing is to limit the amount of data that is returned from the database. Your primary tool for doing this is the record selection formula. Crystal Reports analyzes your record selection formula and generates an SQL query from it.
Designing Optimized Web Reports formula is evaluated by Crystal Reports, no further records need to be eliminated. Click Show SQL Query on the Database menu and notice that the resulting SQL query has a WHERE clause. As this example shows, your report's processing speed improves when you enhance your record selection formula. In this case, both formulas generate the same report, but the second takes advantages of the power and optimizations that the database server can use when handling its own data.
Designing Optimized Web Reports 6.5.2.3 SQL Databases • You can push down record selection on indexed or non-indexed fields. • Your SQL server will respond faster if you use indexed fields. • You can push down AND and OR clauses. • Record selection formulas containing some types of embedded formulas can be pushed down. • You should incorporate SQL Expression fields to push down formula calculations necessary for the record selection.
Designing Optimized Web Reports Of course DataBaseField is just a database field. SupportedOperator is any of =, <>, <, <=, >, >=, StartsWith, Like or In. ConstantOrParameterExpression is any expression that involves constant values, operators, functions, and parameter fields. It cannot involve variables, control structures, or fields other than parameter fields. By their very definition, constant and parameter expressions can be evaluated without accessing the database.
Designing Optimized Web Reports 6.5.3.1 To add a parameter field to your record selection formula 1. Open the sample report Group By Intervals.rpt. Take a moment to navigate the Group tree and see how the data is organized; notice also, in the bottom right of the Crystal Reports window, that 269 records were returned for this report. Note: You'll see this number only if you have the Status Bar option selected on the View menu. Now click the Design tab. 2. On the View menu, click Field Explorer. 3.
Designing Optimized Web Reports a numeric value representing last year's sales quota. The resulting report will display only those customers whose Last Year's Sales exceeds the numeric value you specify. For instance, if you respond to the parameter by entering 40000, then the report will display only those customers whose sales exceeded $40,000 last year.
Designing Optimized Web Reports enhance and hone your formulas in ways that are either difficult or impossible with SQL. In certain circumstances, however, creating and using SQL Expression fields can speed up the processing of your reports. 6.5.5.1 Key uses of SQL Expression fields To maintain optimum report processing speeds, avoid using formulas (whether Crystal or Basic syntax) within record selection formulas.
Designing Optimized Web Reports Note: Server-side processing works only for sorted and grouped reports that are based on SQL data sources. 6.6.1.1 To enable server-side processing 1. On the File menu, click Report Options. 2. Select Perform Grouping on Server on the Report Options dialog box. This check box is inactive if Use Indexes or Server for Speed is not selected. Tip: When Use Indexes or Server for Speed is selected, you can quickly enable Perform Grouping On Server from the Database menu. 3.
Designing Optimized Web Reports Notice that 269 records are still returned for the report, even though the Detail records are hidden from view. 7. On the Database menu, click Perform Grouping on Server. Now notice that only 71 records are returned for the report. The grouping (by Country) has been performed on the database server, and fewer records have been transferred to the report. 8. Drill down on a country by double-clicking the report.
Designing Optimized Web Reports calculation for each group in the report. By performing the calculation with an SQL Expression field of the following form, you use your database's ability to process Case Logic: CASE DatabaseTable. "DatabaseField " WHEN 'SpecifiedValue' THEN Calculation1 ELSE Calculation2 END If a record's DatabaseField value is equal to SpecifiedValue, then Calculation1 is performed; for all other records, Calculation2 is performed.
Record Selection Record Selection 7.1 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 When you select records, you are basing your report only on those records that meet some conditions that you have set. You base those conditions on the kind of information you want in the finished report. Assume, for example, that you want a report that only shows data from California. The challenge is to find the best way to identify those records that come from California.
Record Selection report is limited to values within the range. You can set up all of these types of record selection requests without any previous knowledge of the formula language. Note: The Select Expert can be used to set up both record selection and group selection requests. When a group name or summary field is selected, the program knows that the selection criteria set up is intended for group selection. In all other cases, the program knows that you are setting up record selection. 7.1.3.
Record Selection 7.1.4 Using formulas 7.1.4.1 To set up record selection using a formula 1. On the Report menu, point to Selection Formulas and click Record. 2. In the Record Selection Formula Editor, enter the formula by typing in the components or selecting them from the component trees. Note: The resulting formula must be Boolean; that is, it must return either a True or False value. 3. Click Check to identify any errors in the formula. 4. Fix any syntax errors the Formula Checker identifies. 5.
Record Selection 7.1.5.1 To view the Select Expert formula 1. Right-click the field on which you want to view record selection. 2. Click Select Expert. 3. Click the Show Formula button. The Select Expert expands so you can review the formula the program generated based on your selection criteria. 4. Click the Hide Formula button when you are done with your review. 5. Use the Select Expert to change your selection formula. 6. Review the updated formula by clicking the Show Formula button again. 7.
Record Selection 7.1.6 Saved data selection formulas Saved data selection formulas filter report data after the records have been stored in the report. Like record selection formulas, they can be created using both the Select Expert and formulas. Unlike record selection formulas, any change to a saved data selection formula does not cause a refresh to the database. Instead, the report's saved data is used for all subsequent filtering.
Record Selection Selects those records in which the value in the {file.FIELD} field does not begin with the character "C" (includes values like Bob's Bikes Ltd. and Feel Great Bikes, Inc.; excludes values like CyclePath, Corp. and Cyclist's Trail Co.). "999" in {file.FIELD}[3 to 5] Selects those records in which the 3rd through 5th digits of the {file.FIELD} field is equal to "999" (includes values like 10999, 70999, and 00999; excludes values like 99901 and 19990). "Cycle" in {file.
Record Selection Selects those records in which the year found in the {file.DATE} field falls between 1992 and 1996 (1992 and 1996 not included). Year({file.DATE}) >= 1992 and Year({file.DATE}) <= 1996 Selects those records in which the year found in the {file.DATE} field falls between 1992 and 1996 (1992 and 1996 are included). Month({file.DATE}) in 1 to 4 Selects those records in which the month found in the {file.
Record Selection These templates can be used as they are (with your own data), or they can be combined to create complex formulas. 7.3 Pushing down record selection to the database server The drivers provided with Crystal Reports for SQL data sources allow "pushing down" record selection to the database server. When you specify a record selection formula in a report based on an SQL data source, Crystal Reports analyzes it, generates a SQL query from it and passes the SQL query to the database server.
Record Selection 1. Write down the record selection formula on paper. You will use this written copy to help you reconstruct the selection formula one step at a time. 2. Remove the record selection formula from your report by deleting the formula from the Record/Group Selection Formula Editor in the Formula Workshop. 3. Click Close when finished in the Formula Workshop. 4.
Record Selection Does the {customer.CONTACT LAST NAME} field show only text strings beginning with the letter "C"? • If it does, then this part of the selection formula is working. • If it does not, then troubleshoot this part of the selection formula. 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. 7.4.
Record Selection Another formula which performs much the same functions is the following: "BOB" in UpperCase({customer.CONTACT FIRST NAME}) 7.4.2.2 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.TITLE} will not find any matches with the form of address "Mr.
Sorting, Grouping, and Totaling Sorting, Grouping, and Totaling 8.1 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 8.1.1.2 Sort field A sort field is the field that determines the order in which data appears on your report. Almost any field can be used as a sort field, including formula fields. A field's data type determines the method in which the data from that field is sorted. Note: You cannot sort on memo or BLOB fields.
Sorting, Grouping, and Totaling Field Type Sort Order Time fields chronological order False values (0) Boolean comparison fields 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. The order applied depends on the rules in place for your data source.
Sorting, Grouping, and Totaling 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 Tip: The order of the fields listed in the Sort fields box is the order by which data will be sorted. 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. 8.1.3 Sort controls You may want to design your report so that users can modify a sort field or sort direction without refreshing information from the database. You can do this using a "Sort Control".
Sorting, Grouping, and Totaling Note: Sort fields that begin with "Group" specify that the sort was done automatically when the data was grouped. 2. Highlight the fields that you want to sort by and click the > arrow to add them to the Sort Fields list. The order of the fields in the Sort Fields list is the initial order by which data is sorted. Note: To enable a Sort Control on a field, that field must be included in the Sort Fields list. 3. Click OK when finished. 4.
Sorting, Grouping, and Totaling Note: Grouping hierarchy is not affected by "Sort Control". 8.1.3.2 To remove a sort control 1. Right-click the text object that contains the existing Sort Control. 2. Click Bind Sort Control. The "Sort Control" dialog box appears. 3. Click < Not Interactive >. 4. Click OK to return to your report. The "Sort Control" is removed from the report. 8.2 Grouping data Grouped data is data that is sorted and broken up into meaningful groups.
Sorting, Grouping, and Totaling 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. 4. If you want to show a different value in the group header, click the Options tab. Note: By default, the group header of the report will display the value of the field you are grouping on. 5.
Sorting, Grouping, and Totaling 8.2.2 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. For example, your report may contain a City field and a State field, but no Country field, although you need to group by country.
Sorting, Grouping, and Totaling 7. Click the tab to add more selection criteria to your specified group, if necessary. 8. Click OK. 9. Click New to create more custom groups as necessary. 10. Click the Others tab to specify how you want to organize the data that is not part of the group(s) you defined. 11. Click OK. 8.2.
Sorting, Grouping, and Totaling 7. Select Use a Formula as Group Sort Order, and click the Conditional Formula button next to it. Note: This option is not available if you choose "in specified order" as the sort order for your group. 8. In the Formula Workshop, enter your conditional formula text. For this example, enter this text: If {?Sort Order} = "Ascending" then crAscendingOrder else crDescendingOrder 9. Click Save and close to return to your report. 10. Click OK to save your group. 11.
Sorting, Grouping, and Totaling 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. Note: 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. 8.2.
Sorting, Grouping, and Totaling 8.2.5.1 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. • If you have grouped your data but have not summarized it, you can only set up group selection based on the group name field.
Sorting, Grouping, and Totaling 3. To base the group selection on more than one field, click the New tab and choose the next field from the Choose Field dialog box. 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.
Sorting, Grouping, and Totaling 8.2.5.3 Troubleshooting group selection formulas In some cases, no values will print when using a group selection formula, even though there are values that match the selection criteria. Typically, in these cases: • The group selection formula references another formula. • The referenced formula is one that calculates the value of each group as a percentage of the total value of all groups (in other words a subtotal as a percentage of a grand total). 8.2.5.3.
Sorting, Grouping, and Totaling 8. Instead of using the formula name (in this case @Percent) in the group selection formula, enter the formula itself (the formula named @Percent). Thus, instead of using the group selection formula: {@Percent} < 5 use the group selection formula: Sum({orders.ORDER AMOUNT}, {customer.REGION}) % Sum({orders.ORDER AMOUNT}) < 5 Now when you print, only the regions that contributed less than 5% will print. 8.2.
Sorting, Grouping, and Totaling 5. Click the New button. The Define Named Group dialog box appears. 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: is less than 10000 8. Click OK to return to the Specified Order Tab. 9. Click New.
Sorting, Grouping, and Totaling • Specify a range of values: • Type "10000" in the top field. • Type "25000" in the bottom field. 11. Click OK to return to the Specified Order tab. 12. Click New. The Define Named Group dialog box reappears. 13. Set up the final group that contains all values over $25,000. • Type "Over $25,000" in the Group Name field. • Set the first field so the condition reads: is greater than. • Type "25000". 14. Click OK to return to the Specified Order Tab. 15. Click OK.
Sorting, Grouping, and Totaling {customer.POSTAL CODE} {customer.COUNTRY} Note: Xtreme.mdb is located on the SAP Business Objects Support web site. 2. On the View menu, click Field Explorer. The Field Explorer dialog box appears. 3. Select Formula Fields and click New. 4. In the Formula Name dialog box, enter the name you want to identify the formula, for example "First Letter," and then click OK. The Formula Workshop appears with the Formula Editor active. 5.
Sorting, Grouping, and Totaling Characteristics of the data needed for hierarchical grouping You can group data in a report to show hierarchical relationships. When you group data hierarchically, Crystal Reports sorts information based on the relationship between two fields. A hierarchical relationship must be inherent in the data that you use for the report: • Parent and child fields must be of the same data type for the program to recognize a relationship between them.
Sorting, Grouping, and Totaling Employee (child) Supervisor (parent) 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 8.2.8.1 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.
Sorting, Grouping, and Totaling 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. For example, for an employee hierarchical report, you might select the data field listing the supervisor to whom the employee reports.
Sorting, Grouping, and Totaling 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. To indent only the hierarchy records and not the other objects, leave this value as 0 (zero) and use the conditional-X-position feature. 12. Click OK.
Sorting, Grouping, and Totaling Note: The level at which a name appears in the hierarchy of this report is determined by the number of employees who report to him or her. Those employees with no one reporting to them are at the lowest level in the hierarchy. If you want to, you can now calculate summary fields across your new hierarchical grouping. When inserting a subtotal, grand total, or summary in the usual manner, select the "Summarize across hierarchy" option.
Sorting, Grouping, and Totaling 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. 8.3 Summarizing grouped data One of the primary purposes for breaking data into groups is to run calculations on each group of records instead of on all the records in the report.
Sorting, Grouping, and Totaling 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 your report by clicking the Insert Group button.
Sorting, Grouping, and Totaling 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.
Sorting, Grouping, and Totaling • 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). 2. Click the Group Sort Expert button on Expert Tools toolbar. The Group Sort Expert appears with a tab for your group. Note: If you have multiple groups, the program will display a tab for each of the groups. 3.
Sorting, Grouping, and Totaling • Order 1 = 100 • Order 2 = 90 • Order 3 = 80 • Order 4 = 80 If you set your top N to be three, but you do not select "Include ties," your report will show Order 1, Order 2, and Order 3. If, in the same scenario, you do select "Include ties," your report will show Order 1, Order 2, Order 3, and Order 4 even though N is set as three. In this way, the program accommodates the equal values of orders 3 and 4. 8. Click OK when finished.
Sorting, Grouping, and Totaling 5. On the Report menu, click Group Sort Expert. 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 Note: Xtreme.mdb is located on the SAP Business Objects Support web site. 2. Right-click the Last Year's Sales field, point to Insert and choose Summary from the shortcut menu. The Insert Summary dialog box appears with the chosen field listed as the field to be summarized. 3. Click Insert Group. The Insert Group dialog box appears so you can specify the group you want to add to your report. 4.
Sorting, Grouping, and Totaling The Formula Workshop appears with the Formula Editor active. 5. Enter the following formula into the Formula text box: {Orders_Detail.Quantity} * {Orders_Detail.Unit Price} 6. Click Save and close to return to the Field Explorer dialog box. Your formula is listed in the Formula Fields node. 7. Drag your formula field to the right of the Unit Price field in the Details section of the report. 8.
Sorting, Grouping, and Totaling 8.5.1 Calculating a percentage You can calculate the percentage of one group within a broader grouping. For example, you can show the percentage of sales in each city based on the total sales for each country. Or, you can see what percentage of the grand total of sales each country contributes. 8.5.1.1 To calculate a percentage 1. On the Insert menu, click Summary. The Insert Summary dialog box appears. 2. Select the field for which you want to calculate the sum.
Sorting, Grouping, and Totaling 7. Click OK. The summary percentage field is added to your report. 8.6 Group headers 8.6.1 Creating group headers Whenever you create a group, a subtotal, or a summary, the program creates both a Group Footer (GF) section (where it places any subtotal or summary value), and a Group Header (GH) section (where it automatically places the group name/header). Group Headers are useful, even necessary, if you want your report data to be clear and easily understood.
Sorting, Grouping, and Totaling 8.6.1.1 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. While the header is somewhat descriptive ("Region Sales Figures" means it is a regional group), you never know what region is in the group without first looking at the details in the group. 8.6.1.1.1 To create a standard header 1.
Sorting, Grouping, and Totaling Group name only The easiest live header to create is one based on identifying the value of the group field. 8.6.1.2.1 To create a live header by group name only 1. 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.
Sorting, Grouping, and Totaling 8.6.1.2.3 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 8.6.2 Suppressing group headers You have the option to hide group headers in your report. 8.6.2.1 To suppress group headers 1. 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. 8.6.
Sorting, Grouping, and Totaling 1. Right-click the gray Details section to the left of the report. 2. Select Hide (Drill-Down OK). 3. Click Refresh. When you place the cursor over a group header, the cursor becomes a magnifying glass. 4. Double-click the group header to drill-down to the detail information. A drill-down tab appears in the Report Designer. Click the Design or Preview tab to return to that view.
Running Totals Running Totals 9.1 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 the Report Footer returns the desired value. The Running Total field is properly calculated in both cases, but it is displayed too soon in the first case. The following list summarizes the records that are included in the calculation when a running total is placed in various report sections. This list assumes the running total is not reset.
Running Totals The most basic form of a running total is a running total maintained throughout a list. In this tutorial, you will create this kind of report by setting up a running total for a list of order amounts. Note: Running total fields are prefixed by the # sign. 9.2.1.1 To create a running total in a list 1. To get started, create a report using the sample database, Xtreme.mdb.
Running Totals 9.2.2 Creating running totals for a group Another common use for running totals is tallying items in a group. The running total starts with the first item in the group and ends with the last. Then it starts all over again for the next group, then the next, and so on. In this tutorial, you will create a report that: • Maintains a running total of customer orders. • Groups customer orders and resets the running total for each group. • Displays the subtotal for each customer group. 9.2.2.
Running Totals 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. Note: If you want to view a grand total of each group, place the running total field in the Group Footer section of your report. 9.2.3 Creating conditional running totals There may be times when you have a list of values, and you only want to subtotal some of the values in the list.
Running Totals Tip: Another way to do this is to click the Record Sort Expert button on the Expert Tools toolbar. 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 9.2.4 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.
Running Totals 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. You will see the running total is accurate while the subtotal is not. 9.
Running Totals 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. This formula prints the running total of the values in the Order Amount field. 8. On the Insert menu, click Group and group the report on the Customer.Customer Name field. 9.
Running Totals On your report, each row in the running total column displays the current record value added to the previous values. The running total starts fresh with each new group, and the final running total for each group becomes the subtotal for that group.
Multiple Section Reports Multiple Section Reports 10.1 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.
Multiple Section Reports Many of the procedures in this section show you how to work with sections in the Section Expert. Sections can also be inserted, deleted, and so on by right-clicking the shaded area to the left of the section in the Design or Preview tabs and choosing the appropriate option from the shortcut menu. 10.2.1 Inserting 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.
Multiple Section Reports 10.2.3 Moving 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. When there are more than one of any kind of section, the sections are lettered A, B, C, and so on. Note: The program enables only those options (free form, new page before, and so on) that apply to the highlighted section. 2. Highlight the section you want to move. 3. Click the Up or Down arrow to move the section.
Multiple Section Reports 3. Highlight the top section. 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. 10.3 Splitting and resizing sections A section can be split into two or more sections and/or resized easily in the Design tab. 10.3.1 Splitting a section 1. Move the pointer over the left boundary of the section you want to split. 2.
Multiple Section Reports 10.3.2 Resizing a section 1. Move the pointer over the bottom boundary of the section you want to resize. 2. When the pointer becomes a Resizing cursor, drag the boundary to make the section bigger or smaller as you wish. 10.3.2.
Multiple Section Reports 10.4 Using multiple sections in reports Certain reporting tasks are performed most efficiently by creating multiple sections within an area. Once you understand the power of multiple sections, you will discover even more ways to produce the effects you want. Related Topics • Working with sections 10.4.
Multiple Section Reports Note: Many report objects can use the Can Grow option and can, therefore, cause overprinting: • Text fields • Formula fields • Memo fields • BLOB fields • Subreports • Cross-Tabs • OLAP grids 10.4.2 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).
Multiple Section Reports 10.4.3 Adding blank lines conditionally Use multiple sections to print a blank line on your report under specific conditions. For example, you may want to insert a blank line after every fifth record in the report. 10.4.3.1 To add blank lines conditionally 1. 2. 3. 4. 5. Use the Section Expert to create two Details sections. See Working with sections. Place the report detail data in the top section. Leave the second section empty.
Multiple Section Reports 10.5.1 Working with text objects You will use many of the capabilities of text objects when creating form letters. A brief discussion of text objects should make it easier for you to create the form letter in the next section. Some things to consider are: • A text object can contain both text and fields; you will use both in this example. • You can resize text objects; you will be resizing the text object so that it prints as a letter. 10.5.1.
Multiple Section Reports The insertion point moves as you type, automatically staying to the right of the last character. It also moves when you insert a field, automatically staying to the right of the field. It moves one character position at a time when you press the space bar. It moves down one line, to the inside left edge of the text object, when you press Enter (this action inserts a carriage return). It moves to the cursor position when you click anywhere within the existing text.
Multiple Section Reports 10.5.2.1 To create a form letter The letter will consist of a date, an inside address, a salutation, a one paragraph letter body, and a closing section. 1. Create a blank report. Use the Customer table of Xtreme.mdb. The Design tab appears. 2. Since you do not want field titles to appear above the fields that you insert into the letter, clear the Insert Detail Field Headings check box on the Layout tab of the Options dialog box. 3.
Multiple Section Reports 6. Double-click inside the text object to place it in edit mode. It is now ready for you to begin your work. The insertion point appears at the extreme left, inside the object. 10.5.2.2 Inserting a date 1. To insert a date into the letter, expand Special Fields in the Field Explorer dialog box and scroll until you find Print Date. 2. Click Print Date and drag the placement frame into the text object and place it at the insertion point.
Multiple Section Reports 2. Drag in the Address 1 field and place it at the insertion point, and press Enter. The insertion point moves down to the line below. 3. Drag in the City field and place it at the insertion point. 4. Type a comma, followed by a space. 5. Drag in the Region field and place it at the insertion point. 6. Type in two spaces. 7. Finally, drag in the Postal Code field, place it at the insertion point, and press Enter. The insertion point moves down to the line below. 8.
Multiple Section Reports 10.5.2.5 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. I want to thank you and your staff for your support.
Multiple Section Reports 8. Click Print Preview on the Standard toolbar to preview the form letter. It should look similar to this: 10.5.3 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.
Multiple Section Reports 10.5.3.1 To create a conditional message 1. Use the Section Expert to insert a second Details section in the report. See Working with sections. 2. Create two form letters. Place a letter that encourages customers to buy more in the Details A section of the report; place a letter that encourages customers to bring their balance down in the Details B section of the report. See Creating a form letter using a text object. 3.
Formatting Formatting 11.1 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 11.2.1 Applying a template When you create a new report in the Standard Report Creation Wizard, you can apply a template as an optional step. You can also apply a template later by using the Template Expert. You can choose from a number of predefined templates, or you can use an existing Crystal report as a template. 11.2.1.1 To apply a template in the Standard Report Creation Wizard 1. Click New on the Standard toolbar. 2.
Formatting Tip: Another way to do this is to click the Template Expert button on the Expert Tools toolbar. As was the case with the Template screen, you can choose from a number of predefined templates, or you can click the Browse button to search for an existing report to use as a template. 2. Choose a template and click OK. Note: Any drill-down tabs, alerts, or analyzer views that are open will be closed before the template is applied. 11.2.
Formatting 11.2.3.1 To reapply the last template selected 1. On the Report menu, click Template Expert. Tip: Another way to do this is to click the Template Expert button on the Expert Tools toolbar. 2. Choose Re-apply the last template and click OK. 11.2.4 Using Template Field Objects You can use Template Field Objects to create more flexible report templates. These report objects do not refer to existing database fields; you simply put them in your template report and format them as you require.
Formatting 4. Specify the formatting for your Template Field Object as you require. For information about how to apply formatting, see Working with absolute formatting. For each Template Field Object you create, a special formula field is created. You can see this formula field in the Formula Workshop. If you want to use sample data in your report to see how your formatting will look, you can reference database fields in these formulas.
Formatting If an existing report is used as a template, these report objects will not be applied to the new report: • Detail charts • Subreports • OLAP grids • Cross-tabs • Maps • Embedded OLE objects • BLOB fields • Specified order grouping • Advanced summaries (such as Top Ns, Percentages, and Running Totals) The formatting and objects from an existing report used as a template can override choices you made in the Standard Report Creation Wizard.
Formatting If the Template has... If the target report has...
Formatting 11.3.1 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. When a text-based object is placed in a section, it is placed on a line in such a way that the text is aligned to the baseline. The line's height is then adjusted by the printer driver so that it is high enough to accommodate the object.
Formatting The Xtreme.mdb file is located on the SAP Business Objects Support web site. 2. Place {customer.CUSTOMER NAME} and {customer.LAST YEAR'S SALES} side-by-side in the Details section of the report. 3. To remove unnecessary objects from this example, delete the field titles that the program places in the Page Header section of each field. 4. On the Insert menu, click Group to break the data into regional groups. 5. On the Common tab of the Insert Group dialog box, select {customer.REGION}. 6.
Formatting The picture now prints in both the first Group Header and the following few Details sections, next to (instead of above) the text in the body of the report. Note: Using the technique of placing a picture to the right of the body of the report, you can set up a chart or an employee picture to print beside the details pertaining to that chart or employee. 4. Once you are finished previewing the report, return to the Design tab. 5.
Formatting 11.3.4.1 To create a multiple-column report 1. 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. In the Section Expert, highlight Details, and then select Format with Multiple Columns. A Layout tab is added to the Section Expert. 4. Click the Layout tab and set the Width you want your column to be.
Formatting 11.3.5.2 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 Note: • • This option does not work for text fields that contain embedded fields. This option compares record values, not formatted field values. The program ignores the option in the first Detail section of a formatted page. 11.3.6.2 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.
Formatting Note: This will only work if there are no other objects in the section. 11.3.6.3 Suppress (Common tab) The Suppress property hides an object when you run the report. For example, it is common to apply this property to formulas that are needed to do some report calculations, but that you do not want to print when you run the report. When you select this property, the selected object does not print.
Formatting 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. • For database fields that are not memo fields, the width is initially determined by the width of the field as defined in the database, and by the average character width as provided by the selected font and font size.
Formatting 11.3.7.2 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. 11.3.7.2.1 To prevent the breaks in non-spacing text inside an object 1. Select the object you want to format. 2. Expand the object frame to make it wider than the widest block of text inside the frame.
Formatting 11.3.7.3.1 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. Tip: To ensure that you have clicked a text object, look for the word Text in the status bar at the bottom left corner of the screen. 2. Right-click the text object and, on the shortcut menu, click Format Text. 3.
Formatting Unlike single-line text-based objects, expanding the object frame of a multi-line text-based object to accommodate growth is not a viable option. When you do this, the line width increases according to the expanded boundaries. 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. 11.3.
Formatting The object is imported from the file into the text object on your report. 11.3.10 Spacing between text-based objects Use the grid and guidelines options to help evenly align text-based objects. You can select the Snap To Grid option, set the grid to a maximum of one inch, and make the grid visible or invisible on the Design tab, Preview tab, or both. For more information on working with grids, see Using the grid.
Formatting The Design and Preview tabs have an underlying grid structure that you can activate on the Layout tab in the Options dialog box. 11.3.10.2.1 To select the grid 1. On the File menu, click Options. The Options dialog box appears. 2. On the Layout tab, in the Grid options area, activate the snap to grid feature, or specify the grid size. 3. To show the underlying grid structure on the Design or Preview tab, select Grid in the Design View or Preview areas. 4. Click OK to save your changes.
Formatting 11.3.10.5 Inserting guidelines Although you can and should insert manual guidelines whenever necessary, Crystal Reports will automatically insert guidelines in certain situations: • Whenever you add a field or formula field to a report, the program creates a guideline at the left edge of the field frame and snaps the field and field title to it. • When a field is summarized, the program snaps the summary to the same guideline to ensure proper alignment.
Formatting To snap an object to a guideline, drag the report object onto the guideline until the object's edge sits atop the guideline. Snapping enables you not only to align report objects accurately, but also to re-position and re-size multiple objects together. Once several objects are snapped to a guideline, you can move all the objects by moving the guideline. You can snap either the top or the bottom of an object to a horizontal guideline.
Formatting 11.3.10.7 Positioning objects using guidelines Once you've snapped one or more objects to a guideline, you can move all the objects at once by moving the guideline. To move the guideline, drag its arrowhead along the ruler. Note: When a guideline is moved, any object that is snapped to it is moved as well. But, if you move an object that is snapped to a guideline, the guideline does not move. 11.3.10.8 To resize objects using guidelines 1. Create a guideline. 2.
Formatting 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. For each additional object you want to snap to both guidelines, repeat Steps 2 through 5. 7.
Formatting Note: • • Only indentation values within the range of the field or object width are accepted. When you select the "Right to Left" reading order, indents are measured from the opposite side of the object. That is, a left indentation is measured from the right side of the object. 5. Click OK to save your changes. 11.3.11 To allow for overflow field representation 1. Right-click the currency field or number field you want to format to bring up the shortcut menu. 2.
Formatting Note: You also have the option to click the Conditional Formatting button to enter a formula in the Format Editor. In the Format Formula Workshop, you can specify that field clipping will be disabled only when certain conditions are met. 5. Click OK to save your changes. To view the results, refresh the report. If you disabled field clipping, any numeric/currency field values that are larger than the field objects containing them will be represented by number signs (######). 11.3.
Formatting 11.3.13 Vertical placement On the Common tab of the Format Editor, you can use the text rotation options to vertically align the fields and text-based objects on your report. When you select a text rotation of 90 degrees, the text shifts 90 degrees in a counter-clockwise direction. When you select a text rotation of 270 degrees, the text shifts 270 degrees in a counter-clockwise direction. Note: • • • If text rotation is left at 0 degrees, your report is horizontally formatted, left to right.
Formatting The value n is defined as the distance in number of points measured from the start of one character to the start of the next. When you change the character spacing, you change only the spacing between adjacent characters, not the font size of the characters. For example, if you specify a 14-point font with a character spacing of 14 points, each character will remain as a 14-point font size, occupying a space that is 14 points wide. 5. Click the Paragraph tab.
Formatting 11.3.16 Setting page size and page orientation You can view and print your reports using either portrait or landscape orientation, and in a variety of page sizes. You may also set different page orientation for each section in your report. This is useful for formatting certain sections to display charts or other graphics. Specify these options using the Page Setup command from the File menu. 11.3.16.1 To set page size and page orientation 1. On the File menu, click Page Setup.
Formatting 11.3.17 Setting page margins Margins are the write spaces on the left, right, top, and bottom of the page. Crystal Reports gives you the option of setting margins to meet your specifications. 11.3.17.1 To set specific margins 1. 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. Page margins can also be controlled by use of conditional formulas. See Changing margins conditionally.
Formatting 11.3.19 Printer drivers 11.3.19.1 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.
Formatting • Using a second printer driver that measures fonts narrower could result in the same block of text requiring less than six full lines. • Using a third printer driver that measures fonts wider could require more than six full lines. 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.
Formatting You can set formatting properties using the Format Editor for objects and the Section Expert for report sections. In most cases, you can set one of two types of properties: • Absolute (always apply the property). • Conditional (apply the property only if certain criteria are met). 11.5 Working with absolute formatting Absolute formatting is formatting that applies under any condition. This type of formatting property always follows a "select, then apply" procedure.
Formatting 11.5.2 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. The formatting options that are usually available on the toolbars or shortcut menus are also suppressed for the report or object. Note: This feature is for your convenience in protecting report formatting; it is not intended to be used as report security. 11.5.2.
Formatting Note: • • You can also do this by clicking the Lock Format button on the Formatting toolbar. When a report object is read-only, you can move it, copy it, cut it, delete it, browse its data, and select it, but you cannot format it. 11.5.3 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. 11.5.3.
Formatting 2. In the Options dialog box, click the Fields tab. 3. Click the button appropriate to the type of field you want to format (String, Number, Currency, Date, Time, Date and Time, or Boolean). The Format Editor appears. 4. Use the Format Editor's tabs to specify the formats you want. 5. Click OK. 11.5.4.
Formatting 11.5.4.2.2 To customize 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. Open the Format Editor by clicking the button appropriate to the field you want to format (Date, Time, or Date and Time). 4. In the Format Editor dialog box, click the Date and Time tab. 5. Click Customize to open the Custom Style dialog box.
Formatting 2. Use the pencil cursor to draw the line where desired. Note: You cannot draw diagonal lines. 11.5.5.2 To edit lines on a report 1. Right-click the line you want to format to bring up the shortcut menu. 2. On the shortcut menu, click Format Line. The Format Editor dialog box appears. 3. On the Line tab, make the desired changes to the line. 4. Click OK to save your changes. 11.5.
Formatting 1. On the Insert Tools toolbar, click Insert Box. 2. Use the pencil cursor to draw the box where desired. 11.5.6.2 To edit boxes on a report 1. 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. 11.5.
Formatting Between these sections • RH to PH • RH to RF • RH to PF • PH to RF • PH to PF • RH to PH • RH to GH/D/GF • PH to GH/D/GF End In the top section, or if that section is suppressed, at the top of the next visible section before the bottom section (including the bottom section). In the bottom section, or if that section is suppressed, at the top of the next visible section.
Formatting Between these sections Start End • GH2 to GF3 In every instance of the GH2 section, or if that section is suppressed, at the top of any GH/D/GF section before the end section. In the first instance of the GF3 section, or if that section is suppressed, at the top of the next visible GH/D/GF section. If these sections are suppressed, at the top of the first RH/PH/RF/PF.
Formatting When designing report formats in Crystal Reports, you can insert a variety of shapes such as circles, ellipses, and boxes with rounded corners, as part of your report. This is especially useful for formatting reports in languages that require these shapes to effectively communicate. 11.5.8.1 To add shapes to a report 1. Add a box to your report. See Adding and editing boxes. 2. Right-click the box to bring up the shortcut menu. 3. On the shortcut menu, click Format Box. 4.
Formatting 2. Right-click the object and select Format Graphic from the shortcut menu. 3. In the Format Editor, click the Picture tab. 4. To crop the object, specify the size of the piece that you want to cut from its top, bottom, left, and/or right side. Note: Cropping begins at the outer edge of the object. Positive numbers cut into the object, while negative numbers add the specified amount of white space between the outer edge of the object and the frame. 5.
Formatting 11.5.10.2 To customize the accounting conventions for a report 1. Right-click the currency field or number field you want to format to bring up the shortcut menu. 2. On the shortcut menu, click Format Field. The Format Editor dialog box appears with the Number tab open. 3. Click the Customize button. The Custom Style dialog box appears with the Number tab open. 4. Select the Use Accounting Format check box.
Formatting 11.5.11.1 To repeat objects on horizontal pages 1. Right-click the report object you want to repeat. 2. On the shortcut menu, click Format Field, or the appropriate format option. Note: Formatting options are not available if the object you select is connected to the BusinessObjects Enterprise Repository. For information about modifying an object in the repository, see Modifying objects in the repository. 3. On the Common tab of the Format Editor, select the Repeat on Horizontal Pages check box.
Formatting 11.5.12 Using white space between rows The height of a section in relation to the objects within it affects the amount of white space that appears between rows on the report. The free-form Design tab lets you add and delete white space in two ways: • Using the Resizing cursor to resize the area on the Design tab. • Changing the option in the Section Expert. Note: You can also right-click the shaded area to the left of the section and select Fit Section from the shortcut menu.
Formatting 11.5.12.2 Deleting white space by resizing To delete unnecessary white space within a section, move the pointer over the lower section boundary line. The pointer changes to a Resizing cursor. When the Resizing cursor appears, drag the section boundary upward to remove extra white space. 11.5.12.
Formatting 4. Click OK to return to your report. The blank section will no longer be printed. 11.5.12.3.2 To delete white space by clamping the Page Footer 1. On the Report menu, click Section Expert. The Section Expert appears. Tip: Another way to do this is to click the Section Expert button on the Expert Tools toolbar. 2. In the Sections area, click Page Footer. 3. Click Clamp Page Footer. 4. Click OK to return to your report.
Formatting 11.6.1 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.
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. Crystal syntax example If Condition A Then crRed Else DefaultAttribute Basic syntax example If Condition A Then formula = crRed Else formula = DefaultAttribute End If You can take this kind of property one step further.
Formatting 3. Click the Font tab. 4. To change any of the font options, click the appropriate Formula button, located on the right side of the dialog box. 5. In the Formula Workshop, you can specify that conditional fonts will change only when certain conditions are met. 6. Click Save and close. Note: • • 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.
Formatting 3. In the Formula Workshop, enter your conditional margin formula text. 4. Click Save and close to return to your report. 5. Click OK to apply your new settings. 11.6.4 Changing X position conditionally You can change the X position (that is, the horizontal position from the left margin) for an object based on a condition.
Formatting You may choose to print a page footer on all pages except the first page. You can do this by formatting the Page Footer section conditionally, using an on or off property. 11.6.5.1 To create footers after the first page 1. Place the field you want displayed as a page footer in the Page Footer section of the report. 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. The Section Expert dialog box appears. 3.
Formatting 11.6.6 Using the Highlighting Expert The Highlighting Expert enables you to apply conditional formatting to all types of report fields (Number, Currency, String, Boolean, Date, Time, and Date and Time fields). With the expert, you format the selected field either by specifying a condition based on that field's value, or by specifying a condition based on the value of a different report field.
Formatting The dialog box is divided into two areas: the Item list area displays the formula; the Item editor area allows you to specify the formula. The Item editor area includes a Sample field that displays the formatting specifications being applied. 11.6.6.1.1 To conditionally format fields using the Highlighting Expert 1. To open the Highlighting Expert, right-click the field you want to format and select Highlighting Expert from the shortcut menu.
Formatting 5. Complete the condition by entering the desired value in the box. Note: If the field selected in the "Value of" list is not numeric, the text box turns into a list of available values, from which you must select one. 6. In the Font style, Font color, Background, and Border lists, specify the formatting changes that you want to apply to the selected field when your condition is met. 7. Repeat steps 3 and 4 if you want to apply multiple highlighting conditions to the selected field.
Formatting 11.6.7 Undo/Redo activities Crystal Reports includes multiple levels of undo. With multiple levels of undo, you can undo any number of changes to an object, in reverse order, until you have your report in the condition you want it. The program also has a redo feature that reverses an undo. If you move an object, for example, and do not like its new position, you can click Undo to move it back to its original position. If you then change your mind, you can click Redo to restore the latest change.
Formatting • • • The Format Painter does not copy formatting from text/template objects to database fields. The Format Painter can use any object (including Repository objects, read-only objects, and objects contained in read-only sections) as a source of formatting (however, formatting cannot be applied to these objects).
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: • • The Format Painter button is not available until you select an object or field. Click the button a second time, or press ESC, to exit the Format Painter. 2. Click the target object or field you want to apply formatting to.
Formatting • To undo changing a field to a barcode, you must use the Undo command right after you use Change to Barcode. If you perform any other actions after you change the field to a barcode, you must either delete the field item or use the Format Field option to reformat the appearance. Related Topics • Changing the appearance of a barcode • Removing a barcode 11.8.1.1 To add a barcode 1. In the report, right-click the field to use for the barcode.
Formatting 1. Right-click the barcode field that you would like to reformat. 2. From the shortcut menu, select Format Field. The Format Editor appears. 3. Set your desired border, font size and color, or other options. 4. Click OK. 11.8.3 Removing a barcode If you applied the barcode to the wrong field, or you decide that you do not want to have a barcode, you can use Format Field to change the field back to a regular font. Related Topics • Adding a barcode • Changing the appearance of a barcode 11.8.3.
Formatting 268 2011-05-16
Charting Charting 12.1 Charting concepts 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. 12.1.1 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.
Charting • OLAP data. You will typically chart on summary and subtotal information at the group level. However, depending on the type of data you are working with, you can create an Advanced, Cross-Tab, or OLAP grid chart for your report. 12.1.2 Chart layouts The Chart Expert provides four layouts that correspond to certain sets of data. You can create charts with any of the following layouts, and depending on the data you are using, you can change the chart from one layout to another.
Charting 12.1.3 Chart types Different sets of data are particularly suited to a certain chart type. The following is an overview of the main chart types and their most common uses. Bar Most bar charts (also known as a column chart) display or compare several sets of data. Two useful bar charts are the Side-by-Side bar chart and the Stacked bar chart. • Side-by-Side bar chart A Side-by-Side bar chart displays data as a series of vertical bars.
Charting 3-D Surface 3-D Surface charts present a topographic view of multiple sets of data. If, for example, you need a chart to show the number of sales by customer by country, in a visually dynamic and relational format, you might consider using the 3-D Surface chart. 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.
Charting Funnel Funnel charts are often used to represent stages in a sales process. For example, the amount of potential revenue shown for each stage. This type of chart can also be useful in identifying potential problem areas in an organization's sales processes. A funnel chart is similar to a stacked bar chart in that it represents 100% of the summary values for the groups included in the chart. Histogram A histogram is a type of bar chart used to depict how measurements vary from the mean value.
Charting 12.2 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. For more information on where to place a chart in the report, see Where to place a chart . • A chart automatically inserted in the Report Header.
Charting 12.2.2 Charting on summary or subtotal fields (Group layout) Many of the charts you create are based on summary or subtotals within your report. In order to create these charts, you must have a summary or subtotal already inserted into your report in a group header or footer. For more information on inserting summaries and subtotals, see Summarizing grouped data and Subtotaling. 12.2.2.1 To chart on a summary or subtotal field 1. On the Insert menu, click Chart.
Charting 12.2.3 Charting on Cross-Tab summaries (Cross-Tab layout) Crystal Reports allows you to include a chart based on summary values in your Cross-Tab report. For example, with a Cross-Tab that shows the amount of a certain product sold in each region of the United States, you may want to include a chart showing the percentage of total sales provided by each region for that product. To create a Cross-Tab chart, you must first have a Cross-Tab in your report. For more information, see Cross-Tab Objects.
Charting 12. Accept the default title information or add new titles to your chart. 13. Click OK. Note: When your chart is inserted, it may cover a portion of the report. Move and resize the chart so that it fits properly within the report. 12.2.4 Charting on an OLAP cube (OLAP layout) The OLAP layout lets you chart on an OLAP grid. In order to create an OLAP chart, you must first have an OLAP grid in your report. For more information, see Creating an OLAP report. 12.2.4.1 To chart on an OLAP cube 1.
Charting 9. 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. 10. Click the Text tab. Accept the default title information or add new titles to your chart. 11. Click OK. Note: When your chart is inserted, it may cover a portion of the report. Move and resize the chart so that it fits properly within the report. 12.
Charting Some of the editing options available in the Chart Expert are also available directly from the Chart Options menu. This menu, which is available when you right-click a chart, also contains many advanced formatting options. The following procedures show you how to access the various options that are the Chart Options. For more information about how to use these features, click Help in the various dialog boxes to open the Chart Help. The Chart Help (Chart.
Charting 2. The Custom templates dialog box appears. The options on the Custom tab represent directory locations under \Program Files\SAP BusinessObjects\Crystal Reports 14.0\Templates where custom chart files are stored. Note: The custom charts are available only if you selected Custom Charting when installing Crystal Reports. 12.3.3.2 To format a selected chart object 1. Select a line, area, or text object in your chart. 2. Right-click the specified object, and then click Format < object >.
Charting 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. 12.3.4.1 To zoom in and out on a bar or line chart 1. On the Preview tab, right-click the bar or line chart to bring up the shortcut menu. 2.
Charting 12.3.6 Formatting charts 12.3.6.1 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. 12.3.6.2 Conditionally formatting a chart 1.
Charting 12.3.6.3 Changing the chart's legend text 1. On the Preview tab, click the text in your chart's legend to select it. Tip: Be sure to select the text and not the entire legend. 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. 12.3.
Charting 284 2011-05-16
Mapping Mapping 13.1 Mapping concepts 13.1.1 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. 13.1.
Mapping Note: 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. Cross-Tab Use the Cross-Tab layout when mapping on a Cross-Tab object. A Cross-Tab map does not require groups or summary fields. OLAP Use the OLAP layout when mapping on an OLAP grid. An OLAP map does not require groups or summary fields.
Mapping • Equal count This option assigns intervals in such a way that the same number of regions (or as close to the same number of regions as possible) appear in each interval. In other words, this option would assign intervals so that each color on the map is assigned to the same number of regions. The numeric quantity of the summary values in each interval may or may not be equal, depending on the individual regions and their summary values.
Mapping 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. Each slice of the pie represents an individual data item and shows that item's percentage in the whole. For example, you could create a Pie Chart map showing heating fuel types by region.
Mapping 13.1.5 Drill-down with maps Not only is mapping a means of presenting data—it is also an analysis tool. Move your cursor over a section of the map on the Preview tab, so that the pointer becomes a Drill-down cursor, then double-click to view the underlying details for that section of the map. Note: If you drill down on a region that has no data associated with it, you will get a message saying, "There are no detail records for that {Region Name}." 13.
Mapping 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. 13.2.1.1 To map on a group 1. 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.
Mapping 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. For more information about Cross-Tabs, see Cross-Tab Objects. 13.2.2.1 To map on a Cross-Tab summary 1.
Mapping 14. Click OK. Your map is placed in the Header or Footer section of the report, depending on your selection in Step 3. 13.2.3 Mapping on an OLAP cube (OLAP layout) Using the OLAP layout, you can map on an OLAP grid. In order to create an OLAP map, you must first have an OLAP grid in your report. For more information about OLAP grids, see Creating an OLAP report. 13.2.3.1 To map on an OLAP cube 1. On the Insert menu, click Map.
Mapping 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. Note: 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. 12. Click OK.
Mapping 13.3.2 Changing the map title 1. On the Preview tab, right-click the map to bring up the shortcut menu. 2. On the shortcut menu, choose Title. 3. In the Change Map Title dialog box, enter a new title. 4. Click OK to save your changes. Crystal Reports returns you to the Preview tab and implements your changes. 13.3.3 Changing the map type You can change the map type and set the properties for that map directly from the menu that appears when you right-click a map on the Preview tab.
Mapping 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. This layer sits on top of the basic world map to provide further information. You can specify which of the provided map layers you want to see, and you can rearrange the order of the layers that you have selected. 13.3.4.
Mapping Use this dialog box to set the default display mode and zoom range (the minimum and maximum possible magnification) for the layer in question, then click OK to return to the Layer Control dialog box. 6. Click OK to save your changes. Crystal Reports returns you to the Preview tab and implements your changes. 13.3.5 Resolving data mismatches Sometimes the map you are working with uses a different geographic name from the one used in the database.
Mapping 13.3.6.1 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; the Change Map tab displays the name of the current map you are using, and a list of replacement maps that you can choose from. 3. In the Available maps list, click the new map you want to use. 4. Click OK to save your changes.
Mapping 13.3.8 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. 13.3.9 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. 13.3.10 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.
Mapping You have the option of hiding or showing the Map Navigator. 13.3.10.1 To hide the Map Navigator 1. On the Preview tab, right-click the map to bring up the shortcut menu. 2. On the shortcut menu, click Map Navigator. The Map Navigator is removed from the Preview tab. 13.3.10.2 To show the Map Navigator 1. On the Preview tab, right-click the map to bring up the shortcut menu. 2. On the shortcut menu, click Map Navigator. The Map Navigator is displayed on the Preview tab.
Mapping 13.3.11 Formatting Maps 13.3.11.1 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. 13.3.
OLE OLE 14.1 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 Container Application An OLE container application is one that can contain and process OLE objects created in other applications (such as Paint or Excel). Crystal Reports is a container application. • Container Document A container document is a document that was created using the container application and that contains one or more OLE objects.
OLE • When you double-click an embedded OLE object, the object is activated for editing and the Report Designer merges its menus and toolbars with those of the object's server application. If the OLE server application does not support this behavior, the object appears in a separate window. When you are finished editing, click outside the object and the Report Designer toolbars reappear.
OLE 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 in-place. 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. 14.
OLE 14.4.2 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 Static objects cannot be edited. To edit a static object, it has to be converted into an editable type of object using the Convert command on the Edit menu. 14.5 Working with static OLE objects You can insert a number of different types of static OLE objects into your report.
OLE 14.5.2 Making a static OLE object dynamic A dynamic static OLE object is a picture or metafile that you access in Crystal Reports by use of a file path or URL. Typically, you use this option when you have the path to your pictures or metafiles stored as a string field in a database (that is, you do not have the actual objects stored in the database). You can also use this option when you have pictures or metafiles stored on a network share, and you know these objects change occasionally.
OLE 6. Click Refresh to update the reference link to your static OLE object. 14.5.3 Converting a static OLE object to a bitmap image object 1. Right-click the static OLE object that you inserted, and choose Convert Picture Object from the shortcut menu. The Convert dialog box appears. 2. Select Paintbrush Picture in the Object Type list, and click OK. You have just converted a static OLE object to a modifiable OLE object. Note: This option does not appear if the original file was a metafile. 3.
OLE 14.6.1.1 To insert an embedded OLE object 1. Click the Design tab. 2. On the Insert menu, click OLE Object. The Insert Object dialog box appears. 3. Select Create From File. The dialog box changes, allowing you to either type in an object name or browse. 4. Click Browse and choose a bitmap file (BMP). 5. Click Open to return to the Insert Object dialog box. 6. Click OK to return to the report. An object frame appears. 7. Place the object in your report. 8.
OLE When you have a linked object and you break the link using the Links dialog box, all connections to the original data in the server document are broken. A linked object in a container application is merely a representation of that object and a link between the object and the server document. When you break the link you're left with only the representation, with no relationship to the original data or to the editing capabilities of the server application.
OLE When you embed the object, you can bind it to the report, but you may be able to bind to only a single variable. You can bind to multiple values in a cross-tab, but you cannot automatically extract the values. To bind to multiple values, you should use an SWF object that has been created with Xcelsius 2008. When you link to an object, you will see a static representation of the data. If the SWF object that you link to is updated, you must close and reopen your report to view the changes.
OLE 1. Select the page with the SWF object that you want to print. 2. Adjust the SWF to your desired settings. 3. From the "File" menu, select Print. The "Print Setup" dialog box opens. 4. Ensure that Print Flash Objects for Current Page is selected. 5. Click Print. 14.7.2 Adding an Xcelsius SWF object to a report Shockwave Flash (SWF) files cannot be created within Crystal Reports, but they can be displayed.
OLE 14.7.3 Using data binding to bind an Xcelsius SWF object to a report Data can be sent from a report to an SWF, but not from an SWF to a report. The parameters in the SWF are extracted to set the name of the parameter and the format of the data, such as cell, row, or table. There are three ways to bind data to Flash variables within the SWF object: • Bind to a cross-tab in the SWF.
OLE 14.7.3.3 To bind data to a single value 1. Right-click the SWF object and select Flash Data Expert. The Flash Data Binding Expert appears. 2. For each variable that is listed, select a field or type a value. 3. When finished, click OK.
Cross-Tab Objects Cross-Tab Objects 15.1 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 • Columns • Summary fields • 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 • At the end of each row is a total for that row. In the example above, this total represents a single product sold in all countries. At the end of the "Gloves" row is the value 8, the total number of gloves sold in all countries. Note: The total column can appear at the beginning of each row. • At the bottom of each column is a total for that column. In the example above, this total represents all products sold in a single country.
Cross-Tab Objects 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. 15.2.
Cross-Tab Objects Each region group contains orders for different kinds of locks. 15.2.3 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.
Cross-Tab Objects Each product group contains orders for many regions. 15.2.4 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.
Cross-Tab Objects Each group contains orders for one product for one region. But the data is all spread out and remains difficult to analyze. This information is useful, and with a little work you can use a report like this to get the comparison information you need. However, a Cross-Tab offers a better solution. 15.2.5 Order data in a Cross-Tab object With Cross-Tabs, all the information you need is provided in a compact format.
Cross-Tab Objects In this Cross-Tab: • Product names make up the row headings. • Regions make up the column headings. • The value at each row/column intersection is the sum of all the orders for a particular product for a particular region; for example, the total number of Guardian Mini Locks purchased in British Columbia.
Cross-Tab Objects • You can use Running Total fields as your summarized field. • You can include a group sort (top or bottom N) on the rows in your Cross-Tab. Note: Group sorting cannot be applied to columns. • You can insert as many Cross-Tab objects in a report as you need. • You can insert the Cross-Tab into either the Report Header or Footer, or the Group Header or Footer. • You can place Cross-Tab objects in subreports. This is useful when you want to refer to the results from another report.
Cross-Tab Objects Once you have clicked to release the field, it appears to the right of the column that you selected. 7. In the Field Explorer, select Country and drag it to the Cross-Tab. This field will be your row value. 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.
Cross-Tab Objects Tip: Use the Ctrl-click combination to pick a non-continuous range of fields and the Shift-click combination to pick a continuous list of fields. This example uses Customer, Orders, Orders Detail and Product. 3. Click the > arrow to add the selected tables. 4. Click Next. The Link screen appears. 15.3.4 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.
Cross-Tab Objects 15.3.6 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. For this example choose Region. 2. Click the > arrow. The field is added to the Filter Fields area and the filter types list appears below it. 3. From the filter types list choose a filter method. For this example, choose "is one of" as the filter method. 4.
Cross-Tab Objects 4. Select the summary operation you want to perform on Order Amount from the list beneath the Summary Fields area. 5. Click Next. The Chart screen appears. 15.3.8 Applying a predefined style and finishing the report 1. Select a style. For this example choose Original. 2. Click Finish. The Cross-Tab report appears in the Preview tab. 3. If you do not want the Cross-Tab to appear in the Report Header, click the Design tab, and move the Cross-Tab to the Report Footer.
Cross-Tab Objects 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 Cross-Tab object into the report. 4. Place the object frame in an empty area in the Report Header and click to release it. An empty Cross-Tab object appears in your report. 5. Right-click the Cross-Tab and select Cross-Tab Expert from the shortcut menu. The Cross-Tab Expert appears.
Cross-Tab Objects • Click OK to close the Define Named Group dialog box. • Click OK to close the Cross-Tab Group Options dialog box. 15.3.9.3 Choosing a predefined style 1. Click the Style tab. 2. Select a style. 15.3.9.4 Applying a custom style 1. Click the Customize Style tab. 2. Customize the style as desired. 15.3.9.5 Finishing the Cross-Tab 1. Click OK. 2. On the Report menu, click Refresh Report Data. The updated report appears.
Cross-Tab Objects 15.4 Working with Cross-Tabs This section describes ways you can work with a Cross-Tab once you've added it to your report. 15.4.1 Showing values as percentages 1. Right-click the blank top-left area of a Cross-Tab and select Cross-Tab Expert from the shortcut menu. The Cross-Tab Expert appears. 2. On the Cross-Tab tab, select a summarized field; then click Change Summary. 3.
Cross-Tab Objects 2. Right-click the summarized field you want to abbreviate and choose Format Field from the shortcut menu. The Format Editor appears. 3. On the Common tab, click the conditional formatting button adjacent to Display String. 4. In the Functions tree of the Format Formula Editor, select cdFormatCurrencyUsingScaling from the Custom Functions folder. 5. Complete the custom function's arguments as follows: cdFormatCurrencyUsingScaling (CurrentFieldValue,1,"K","M") 6.
Cross-Tab Objects For more information about how to do this, see Working with conditional formatting. 4. Click Check to identify any errors in the formula. 5. Fix any syntax errors the Formula Checker identifies. 6. When the formula has the correct syntax, click Save and close on the Formula Workshop toolbar. You return to the Format Editor dialog box. 7. Click OK to return to your Cross-Tab. Your customized row and column names appear when the conditions you set are matched. 15.4.
Cross-Tab Objects 15.4.4.2 To total across rows 1. Repeat steps 1 to 3 of the previous procedure. 2. Click Record Sort Expert and sort your Cross-Tab by the field you designated as a Row in the Cross-Tab Expert. 3. Create a second sort on the field you designated as a Column in the Cross-Tab Expert.
Cross-Tab Objects 15.5 Formatting Cross-Tabs Crystal Reports has powerful formatting capabilities that can be applied to Cross-Tabs. The following sections describe some key procedures. By applying such formatting as background color, borders, and fonts, you can emphasize important data and create professional-looking, easy-to-understand Cross-Tabs. For more information, see Formatting properties. You can also use the Highlighting Expert to apply conditional formatting to Cross-Tab cells.
Cross-Tab Objects 15.5.3 Formatting fields individually 1. Right-click the field you want to format and choose Format Field from the shortcut menu. The Format Editor appears. 2. In the Format Editor, make font, background, borders, numbering, currency symbols, and printing characteristics choices. 3. Click OK to return to the Cross-Tab. The field is formatted as specified. 15.5.4 Formatting several fields at one time 1. Use the Shift-click method to highlight the desired fields. 2.
Cross-Tab Objects 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. Select either the Suppress Empty Rows or Suppress Empty Columns check box. 4. Click OK. Now, when you print the report, empty rows and/or columns will not appear. 15.5.5.2 To suppress row and column grand totals 1.
Cross-Tab Objects 15.5.6 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. In the Summarized Fields area, select Horizontal. 4.
Cross-Tab Objects Related Topics • Calculation Formulas • Header Formulas • Insertion Formulas • Value Formulas 15.6.1.1 To add a Calculated Member to your Cross-Tab 1. In your Cross-Tab, right-click the row or column header you would like to use as the first value of your Calculated Member. For example, if you wanted to create a Calculated Member for the difference between Sales and Sales Returns, the desired formula would be Sales - Sales Returns. In this example, you would right-click the Sales header.
Cross-Tab Objects 1. Right-click the top-left corner of your Cross-Tab. 2. On the shortcut menu, point to Advanced Calculations, and click Calculated Member. The Cross-Tab Calculated Members Expert appears. 3. In the Calculated Members box, use the arrows to change the processing order of the Calculated Members. 4. Click OK to save your changes and return to your report. The Cross-Tab recalculates the values of the Calculated Members.
Cross-Tab Objects 15.6.1.5.1 To edit a Header Formula 1. Right-click the header of the Calculated Member you would like to edit. 2. On the shortcut menu, point to Calculated Member, and click Edit Header Formula. The Formula Workshop appears. 3. In the Formula Workshop, enter your desired header name in quotations. Note: You can also control your header formulaically. A Header Formula must return a string. 4. Click Save and close to return to your report.
Cross-Tab Objects Insertion formulas determine where a Calculated Member appears in a Cross-Tab. In most cases, a Calculated Member appears only once. However, you can set it to appear in multiple locations or in a repeating pattern. 15.6.1.7.1 To edit an Insertion Formula 1. Right-click the header of the Calculated Member you would like to edit. 2. On the shortcut menu, point to Calculated Member, and click Edit Insertion Formula. The Formula Workshop appears. 3.
Cross-Tab Objects 15.6.2.2 To change the processing order of Embedded Summaries If you have multiple Embedded Summaries in your Cross-Tab, the order in which they are calculated can affect your results. You can change the processing order of Embedded Summaries in the Embedded Summaries dialog box. 1. Right-click the top-left corner of your Cross-Tab. 2. On the shortcut menu, point to Advanced Calculations, and click Embedded Summary. The Embedded Summaries dialog box appears. 3.
Cross-Tab Objects 342 2011-05-16
Building Queries Building Queries 16.1 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. Crystal Reports can connect to multilingual universes and report off of them, but it cannot take advantage of the multilingual attributes.
Building Queries 16.2 Defining the data selection for a query You build queries in the Query Panel by using objects in a Business Objects universe. The objects in the universe are a graphical representation of the information available in a database. The objects in the universe are mapped to the table columns and rows in the database. You can only base new documents and queries on universes for which you have been granted the custom right, Create / Edit Query.
Building Queries Add filters to the Query Filters pane to restrict the data your query returns based on the object that you selected. Filtering limits the data that a user can see and reduces the runtime of queries. For information about adding filters, see Creating query filters. 5. Click OK. 6. If your query contains prompts, select your prompt value(s) in the Enter Parameter Values dialog box.
Building Queries Note: • • Object properties are defined in the BusinessObjects Designer, but are not taken into account by the Query Panel in Crystal Reports. 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.
Building Queries You can return to the Query Panel to change an existing query, provided you have been granted the custom right, Create / Edit Query. You can add or remove an object, or you can add, edit, or remove a filter. 16.3.1 To edit an existing query 1. In Crystal Reports, open a report created with a universe as a data source, go to the Database menu, and click Query Panel. 2. Log on to SAP BusinessObjects Enterprise if you have not done so already.
Building Queries 16.4.2 To view the SQL after you have created a query • In Crystal Reports, open a report created with a universe as a data source, go to the Database menu, and click Show SQL Query. You can see the SQL in the Show SQL Query dialog box. Or In Crystal Reports, open a report created with a universe as a data source, go to the Database menu, and click Database Expert. Once you are in the Database Expert, right-click your query in the Selected Tables list and select View Command.
Building Queries 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. 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. Note: Predefined filters are created and edited by your administrator.
Building Queries 16.5.2.1 To create a prompt 1. 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.
Building Queries For step-by-step information on how to create filters and prompts, see Creating query filters or Building 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. 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 The following table will help you select the operator you need to define a query filter. 352 Values to retrieve Example Select Filter created Values equal to a value you specify. Retrieve data for the US only. Equal to Equal to US Values different from a value you specify. Retrieve data for all quarters except Q4. Different from Different from Q4 Values greater than a value you specify. Retrieve data for customers aged over 60.
Building Queries 353 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). Is null Is null Values for which a value was entered in the database.
Building Queries 16.5.6 Editing and removing query filters You can edit and remove filters on queries. 16.5.6.1 To edit a query filter 1. Double-click the filter you want to edit. The Filter Editor appears. 2. Change the definition of the filter in the Filter Editor. For information on how to define filters, see Creating query filters. 3. Click OK to confirm your changes. The modified query appears in the Query Filters pane. 16.5.6.
Building Queries 16.6.1 What is a subquery? A subquery is a more flexible kind of query filter that allows you to restrict values in more sophisticated ways than with an ordinary query filter. Subqueries are more powerful than ordinary query filters for the following reasons: • They allow you to compare the values of the object whose values are used to restrict the query with values from other objects. • They allow you to restrict the values returned by the subquery with a WHERE clause.
Building Queries Note: For more information on query filter operators and values, see Query filters and prompts. 5. Click Add a subquery to add an additional subquery to the query filter. By default the two subqueries are linked in an AND relationship. Click the AND operator to toggle between AND and OR. In addition to linking subqueries in AND or OR relationships, you can nest them (create subqueries within subqueries) by dragging an existing subquery to the white area of the subquery outline.
Building Queries Parameter Description The operator that specifies the relationship between the Filter object and the Filter By object. Operator Because of database restrictions you cannot use certain combinations of operators and Filter By objects together. For example, if you use the Equal To operator with a Filter By object that returns multiple values, the database rejects the SQL because this type of subquery requires the Filter By object to return one value only.
Building Queries A database ranking allows you to specify a ranking at the query and database level so that the data returned to Crystal Reports by the query is already ranked. This has the following advantages: • Ranking data can be processing-intensive. By ranking at the database level you allow the server, which is typically far more powerful than the client machine, to perform this processing. • Pre-ranking data reduces the amount of data retrieved across the network and stored in Crystal Reports.
Building Queries Note: You can specify a prompt instead of a constant by clicking the arrow next to the number. When you select a prompt, the user must enter the ranking number when they run the query. For more information on prompts, see Building prompts. 6. Drag the object that provides the calculation context for the measure to the For each box. This dimension is optional. Note: To display the "For each" box, click the arrow to the right of the "Based on" measure. 7.
Building Queries 360 Parameter Description For Each (optional) The object that specifies additional calculation context for the ranking. For example, if the ranking object is Region, the measure is Revenue and the For Each dimension is Country, Crystal Reports ranks regions by revenue within each country. WHERE condition (optional) Additional restriction on the values returned in the ranking that appears below the other parameters.
Creating and Updating OLAP Reports Creating and Updating OLAP Reports 17.1 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 OLAP grid objects provide true multi-dimensional reporting. Add dimensions to either axis to analyze three, four, or more dimensions in a single OLAP grid. Or instead of viewing multiple dimensions within one OLAP grid, create multiple OLAP grids within the same report. Note: When Crystal Reports displays an OLAP grid, it can do so quickly if the grid is long (down many pages) instead of wide (across many pages).
Creating and Updating OLAP Reports The OLAP Connection Browser appears. 2. Browse your OLAP server for the cube you want to connect to. If your server isn't in the list, click Add. In the Connection Properties dialog box, provide the server information; then click OK. 3. Select the desired cube and click Open. The OLAP Data screen reappears with the supplied data source information. 4. Click Next. Note: You can also click "Select CAR File" on the OLAP Data screen.
Creating and Updating OLAP Reports 4. Click the Up and Down arrow buttons associated with the Rows and Columns areas to arrange the order of the dimensions. Note: If you accidentally add a dimension to either the Rows or Columns area, click the < arrow to return it to the Dimensions list. 5. Select a dimension in either the Rows or the Columns areas and click Select Row Members or Select Column Members to specify the members to be included in your report. The Member Selector dialog box appears. 6.
Creating and Updating OLAP Reports Note: • • Once you have created a parameter, access to the Member Selector dialog box is disabled for the dimension until you delete the parameter. If you create a row/column parameter, the View Cube command and the Cube View tab are not available. 10. If you want to delete a parameter, select the appropriate dimension and click Delete. When you click OK in the Delete Link to Parameter Field dialog box, the program removes the parameter description from the dimension.
Creating and Updating OLAP Reports 17.2.4.2 To add a page 1. Use the > arrow to add dimension(s) to the Page list. The Member Selector dialog box appears. 2. Expand the dimension's structure and select the appropriate members. 3. Click OK. 17.2.4.3 To add a parameter You can create a parameter field to link to any dimension. When you add a parameter to a dimension in your OLAP grid, users are prompted to select a value when refreshing report data. 1. Click the Create/Edit button. 2.
Creating and Updating OLAP Reports 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. 17.2.6 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.
Creating and Updating OLAP Reports Note: Be sure that the chart type selected in Step 1 supports a secondary charting field. 5. Click Other Dimensions to define field values for any dimensions you've used in your OLAP grid but not in your chart. For more information refer to Charting on an OLAP cube (OLAP layout) and Creating charts. 6. To finalize your report, click Finish. 17.3 Updating an OLAP report The location of the OLAP data accessed by your report may change.
Creating and Updating OLAP Reports 4. Click Select. The OLAP Connection Browser appears. 5. Browse your OLAP server for the cube you want to connect to. If your server isn't in the list, click Add. In the Connection Properties dialog box, provide the server information; then click OK. 6. Select the desired cube and click Open. The Set OLAP Cube Location dialog box reappears. 7. Click OK.
Creating and Updating OLAP Reports To access the Highlighting Expert, right-click the field you want to format and, on the shortcut menu, click Highlighting Expert. For more details, see Using the Highlighting Expert. • Use the Format Editor to apply absolute formatting to fields in the grid. Absolute formatting is applied under all conditions, irrespective of the data values in the field.
Creating and Updating OLAP Reports 2. In the Group Options area, select the color from the Background Color list. 3. Click OK if you have finished customizing your grid. 17.4.2 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.
Creating and Updating OLAP Reports 1. On the Customize Style tab of the OLAP Expert, click Format Grid Lines. The Format Grid Lines dialog box appears. 2. From the list, choose the description of the area where you would like the lines to appear, or click the appropriate area in the Format Grid Line diagram. 3. Select the Draw check box to have your lines appear in the report. 4. Select the color, style, and width. 5. Click OK. 6. Click OK if you have finished customizing your grid. 17.4.
Creating and Updating OLAP Reports The dimension expands to show its members, or collapses to hide them. Tip: You can also double-click parent dimensions to drill down into the hierarchical data displayed in the OLAP grid. 17.5.2 To create asymmetry in an OLAP grid Using asymmetry, you can display different inner dimension members for any number of outer dimensions. Note: You can create asymmetry only on stacked dimensions. 1. Right-click the member that you want to remove. 2.
Creating and Updating OLAP Reports • Unique Name For information about names and captions, search the online help for "Changing member captions." 17.5.5 To alter the data displayed in the OLAP grid 1. Drag and drop rows and columns to rearrange the data in the OLAP grid. 2. Drag and drop dimensions to and from the OLAP Labels area to add or remove data from the grid. On the Preview tab, the OLAP Labels area is at the upper-left corner of the OLAP grid. 17.5.
Creating and Updating OLAP Reports shortcut menu. You can sort data in ascending or descending order, and you can specify whether or not you want to break grid hierarchies. In the following grid, for instance, an ascending sort has been added to the Budget column. In this case, the OLAP grid respects the parent/child relationships between grid members and sorts the data values accordingly. (Frozen Goods precedes Bakery, but Pastry follows Frozen Goods.
Creating and Updating OLAP Reports 17.6.1.1 To sort data in the OLAP grid 1. Right-click the row or column member that you want to sort by. 2. On the shortcut menu, point to Add First Sort. 3. From the submenu, select from the available sort options: • Ascending • Descending • Ascending, Break Hierarchies • Descending, Break Hierarchies Note: An ascending sort on a grid row orders data values from lowest to highest, left to right.
Creating and Updating OLAP Reports 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. If you filter all the cells in your OLAP grid, right-click the empty grid and select Remove All Filters from the shortcut menu. Search the online help for "Filtering data" in the "Using the OLAP Worksheet" section to learn more about filtering. 17.
Creating and Updating OLAP Reports 378 2011-05-16
Printing, Exporting, and Viewing Reports Printing, Exporting, and Viewing Reports 18.1 Distributing reports Crystal Reports enables you to distribute your report using a variety of methods. 18.1.1 Printing 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. Choose the appropriate settings, and then click OK. The Printing Report dialog box appears showing the progress of your print job.
Printing, Exporting, and Viewing Reports 18.1.2.1 To fax a report 1. On the File menu, click Print. The Print dialog box appears. 2. In the Print dialog box, click Find Printer. The Find Printers dialog box appears. Use this dialog box to select your fax driver. 3. Click OK. You are returned to the Print dialog box. 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. 18.1.
Printing, Exporting, and Viewing Reports Page-based formats tend to produce a more exact output. The emphasis of these formats is layout representation and formatting. Formatting refers to attributes such as font style, text color, text alignment, background color, and so on. Layout refers to object position, object size, and the relationship between these attributes and other objects.
Printing, Exporting, and Viewing Reports kinds of summaries in Crystal Reports as Excel functions. The summaries that are supported are SUM, AVERAGE, COUNT, MIN, and MAX. 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 SAP Business Objects Support site. Microsoft Excel Workbook Data-only Microsoft Excel Workbook Data-only (.
Printing, Exporting, and Viewing Reports 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:". Record Style - Columns with spaces and Record Style - Columns without spaces 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).
Printing, Exporting, and Viewing Reports This format also provides an option for pagination. If you select this option, a page break is inserted in the output after every specified number of lines. Page breaks in this format may not correspond to the pagination of your report. XML Legacy XML is used primarily for data exchange. It is a record-based format that uses the Crystal XML Schema. 18.1.3.1.
Printing, Exporting, and Viewing Reports Supported SAP BusinessObjects BI Platform Features The following SAP BusinessObjects BI platform features are supported with an RPTR formatted report: • • • • • Add RPTR file using BI LaunchPad View RPTR file using Java DHTML Viewer Export during scheduling Publication destination format (include limitations) Publishing wizard Non-Supported Platforms (Viewers, Designers, and BI Platform Servers) The following list details the viewers, designers, and BI platform se
Printing, Exporting, and Viewing Reports To export to HTML 1. With the report you want exported active, go to 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. 2. From the Format drop-down list, select HTML 3.2 or HTML 4.0. 3. Select a destination from the Destination drop-down list.
Printing, Exporting, and Viewing Reports Exporting to an ODBC data source allows you to: • Change data from a centralized database format into a format compatible with a local DBMS application. • Change data from a local database format into a format compatible with a centralized database. • Create a new database table that can be used as a separate data set in future reporting. • Create a mini data-warehouse.
Printing, Exporting, and Viewing Reports The destination determines the export location of your report. Crystal Report enables you to choose one of six destinations. They include: • Application • Disk file • Exchange Folder • Lotus Domino • Lotus Domino Mail • MAPI 18.1.3.2.1 Exporting to an application If you export to an application, the program exports the report to a temporary file in the specified format and then opens the file in the appropriate application.
Printing, Exporting, and Viewing Reports • Selecting "Whole Report" does not necessarily emulate the layout of your Crystal report. Rather, the Excel cell width is based on objects found in any section of your report. Likewise, for example, selecting "Details" creates cell widths based on the objects found only in the report's Details section. 6. Change the formatting options as needed. 7. Click OK. The Exporting Records dialog box appears. Tip: Click Cancel Exporting to cancel the export process.
Printing, Exporting, and Viewing Reports The program exports the report and saves it in the designated directory. 18.1.3.2.3 Exporting to a Microsoft Exchange folder Crystal Reports enables you to export a report file to a Microsoft Exchange folder. You select the folder, and the report is stored there in the format that you specify. A Microsoft Exchange folder can contain standard notes (mail), files, and instances of Microsoft Exchange forms. To export to a Microsoft Exchange folder 1.
Printing, Exporting, and Viewing Reports 18.1.3.2.4 Exporting to MAPI (Microsoft Mail) Note: This option works only if you have a mail client installed (Microsoft Outlook, Microsoft Mail, or Exchange). Because the exported report is attached to an email message, you must also have an email account configured properly. 1. Open the report you would like to export. 2. On the File menu, point to Export, and then click Export Report.
Printing, Exporting, and Viewing Reports 18.1.4.1 To open your report 1. On the File menu, click Open. The Open dialog box appears. 2. Click Web Folders. 3. Open the folder that contains the report. 4. Double-click to open the report. 18.1.4.2 To save your report 1. On the File menu, click Save As. The Save As dialog box appears. 2. Click Web Folders. 3. Locate the folder you would like to save the report to. 4. Enter the file name. 5. Click Save.
Printing, Exporting, and Viewing Reports to its original location, or it lets you save a new report to an Enterprise folder, which, in essence, is the same as publishing the report to SAP BusinessObjects Enterprise. 18.1.5.1 Opening a report in an Enterprise folder You can open reports from SAP BusinessObjects Enterprise folders in Crystal Reports. Crystal Reports displays the appropriate SAP BusinessObjects Enterprise folders in the Open dialog box. 18.1.5.1.1 To open a report in an Enterprise folder 1.
Printing, Exporting, and Viewing Reports Windows NT authentication requires a user name and password that is recognized by Windows NT. 6. Click OK. The Enterprise folders appear and you can select a report to open in Crystal Reports. Note: • • • To find a specific report, highlight a folder and select Find from its shortcut menu. You can then use the Find dialog box to enter the report's name or a part of its name.
Printing, Exporting, and Viewing Reports 7. Click Save to publish your report to SAP BusinessObjects Enterprise. 18.2 Viewing reports You can view Crystal reports using a number of report viewers available through the stand-alone Report Application Server (RAS) or SAP BusinessObjects Enterprise and the SAP BusinessObjects Enterprise Software Development Kit (SDK).
Printing, Exporting, and Viewing Reports 18.2.1.2 Navigating Report Parts and report objects The navigation functionality in Crystal Reports lets you move to other report object(s) in the same report, or to object(s) in another report—with a specified data context. In this last case, the other report must be managed in SAP BusinessObjects Enterprise, or must be part of a stand-alone Report Application Server environment.
Printing, Exporting, and Viewing Reports When you select the Another Report Object option, the "Hyperlink information" area contains these options: • Paste the Report Part link Use the Past Link button to add detailed information about a report object that you previously selected and copied. The pasted information depends, in part, on the option you select from the list associated with the button.
Printing, Exporting, and Viewing Reports • Data Context The program normally completes the Data Context field when you paste information for the destination object(s). You can also customize this option by using the Conditional Formula button. For more information, see Data context formats. See The Another Report Object option for information about how to use these options. Note: Report objects with hyperlinks defined are shown in the Report Explorer as blue text with a solid underline.
Printing, Exporting, and Viewing Reports 3. On the File menu, click Report Options. 4. In the Initial Report Part Settings area of the Report Options dialog box, click Paste Link. The program pastes the name and data context of the report object you selected as your home object into the appropriate fields. Tip: For more information about data contexts, see Data context formats. Include another object from the same section of the report by entering a semi-colon (;) and typing the name of the object. 5.
Printing, Exporting, and Viewing Reports 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). Note: The Available Fields area does not show suppressed report objects. 4.
Printing, Exporting, and Viewing Reports 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: • • The position an object has in the Fields to Display area determines how it appears in the Report Part Viewer; top to bottom in the Fields to Display area equates to left to right in the viewer. Use the arrows above the Fields to Display area to change the order of objects.
Printing, Exporting, and Viewing Reports The following procedure shows you how to do all of these steps. 18.2.1.4.1 To create an Another Report Object hyperlink Note: Before you begin this procedure, be sure to read Setting up navigation to acquaint yourself with the limitations of creating this type of hyperlink. 1. Open your source and target reports. Your source report is the report you copy an object from; the target report is the report you add the hyperlink information to.
Printing, Exporting, and 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.
Printing, Exporting, and 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 Otherwise, the program uses the source report's Design view data context as the Context Report Part data context in the target report. Scenario 2 If your source and target reports are the same report, and the object you choose as your source is from the Preview view, when the target object you choose to link to is in a higher level than the source object, the data context is left empty.
Printing, Exporting, and Viewing Reports Note: 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.Country") + "/" + GridRowColumnValue ("Product.Product Class"). 18.2.1.
Printing, Exporting, and Viewing Reports Differences between how the viewers display hyperlinks Page viewers Report Part Viewer Another Report Object: • Navigates to destination objects and shows contents of the entire page. Another Report Object: • Navigates to destination objects and shows only the destination objects. Report Part Drilldown: • Navigates (drills down) to the group and shows the complete contents of the group.
Printing, Exporting, and Viewing Reports The "Open" dialog box appears. 6. Click My Connections to add a new connection to the Enterprise. If you already have a connection, click Enterprise. 7. Navigate to the enterprise document that you wish to open and click Open. The "Document ID" and "Document Name" fields are filled out with the CUID and name of the selected document respectively. 8. On the "Hyperlink Settings" section change the different options to create the hyperlink with the specified settings.
Printing, Exporting, and Viewing Reports Note: • • The reports that you want to use with smart tags must exist on the web server named in the Options dialog box in a directory that mirrors their real location, or the web server must be configured to accept a UNC path. The Smart Tag option is only available at design time. 18.3.1 To use smart tags with a Crystal Reports object 1. Open the Crystal report that contains the object you want to copy to an Office XP application. 2.
Printing, Exporting, and Viewing Reports 410 2011-05-16
Report Alerts Report Alerts 19.1 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 19.2.1.1 To create a Report Alert 1. On the Report menu, point to Alerts and then click Create or Modify Alerts. The Create Alerts dialog box appears. 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.
Report Alerts Alert formulas can be based on recurring records or on summary fields, but cannot be based on print-time fields, such as running totals or print time formulas. Alert formulas cannot have shared variables. 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.
Report Alerts 2. In the Create Alerts dialog box, select the alert you want to edit and click Edit. Tip: Double-clicking an alert also lets you edit it. 3. Make the changes you want in the Edit Alert dialog box. 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. 19.2.3 Deleting Report Alerts 1. On the Report menu, point to Alerts and then click Create or Modify Alerts. 2.
Report Alerts 2. Select the alert whose records you want to see. 3. Click View Records. 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.
Report Alerts 416 2011-05-16
Using Formulas Using Formulas 20.1 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 Pulling out a portion, or portions, of a text string To extract the first letter of the customer name: Crystal syntax example: {Customer.Customer Name} [1] Basic syntax example: formula = {Customer.Customer Name} (1) Extracting parts of a date To determine what month an order was placed: Crystal syntax example: Month ({Orders.Order Date}) Basic syntax example: formula = Month ({Orders.Order Date}) Using a custom function To convert $500 from U.S.
Using Formulas Numbers Example: 1, 2, 3.1416 Text Example: "Quantity", ":", "your text" Operators Example: + (add), / (divide), -x (negate) Operators are actions you can use in your formulas. Functions Example: Round (x), Trim (x) Functions perform calculations such as average, sum, and count. All functions available are listed with their arguments and are arranged by their use. Custom functions Example: cdFirstDayofMonth, cdStatutoryHolidays Custom functions provide a way to share and reuse formula logic.
Using Formulas 20.2.2.1 Crystal and Basic syntax When creating formulas, you have the option of using either Crystal or Basic syntax. Almost any formula written with one syntax can be written with the other. Reports can contain formulas that use Basic syntax as well as formulas that use Crystal syntax. Crystal syntax is the formula language included in all versions of Crystal Reports. If you are familiar with Microsoft Visual Basic or other versions of Basic, then Basic syntax may be more familiar to you.
Using Formulas Note: Java UFLs are not supported in the Report Application Server (RAS) and the Crystal Page Server, so if a report has a formula in it, and that formula uses a Java UFL, the report may not run in SAP BusinessObjects Enterprise because the formula will fail to compile. For details about developing Java UFLs and configuring Crystal Reports to use a UFL, see the Java Reporting Component Developer's Guide.
Using Formulas Note: If you already know Basic syntax, you need to know only a small amount of Crystal syntax to modify most selection and search formulas. Running Total condition formulas Running Total condition formulas let you define the condition upon which your running total will be evaluated or reset. See Creating conditional running totals. Alerting formulas Alerting formulas help you define conditions and messages for report alerts. See About Report Alerts. 20.4.
Using Formulas The appropriate editor or dialog box appears. 20.4.1.2 Workshop Tree The Workshop Tree contains folders for each type of formula you can create in Crystal Reports. It also contains folders for custom functions and SQL Expressions. If the workshop appears as the result of using a specific command (for example, you've selected the Record command on the Selection Formulas submenu), the appropriate folder in the tree is selected, and the appropriate version of the Formula Editor appears.
Using Formulas Shows or hides the Workshop Tree. Toggles display between Custom Function Editor and Custom Function Properties dialog box. Custom functions and formulas (where possible) are displayed in the selected mode until this button is clicked again. Opens the Formula Expert. Use the Formula Expert to help you create a formula based on a custom function. Note: This button is not available when creating a custom function. Opens online help for the Formula Workshop dialog box.
Using Formulas The buttons for the Expressions Editor Toolbar of the Formula Workshop perform the following functions: Tests the syntax of the formula or custom function and identifies syntax errors if they are found. Undoes the last action performed. Redoes the last action. When you select a field from the Report Fields window and click the Browse Data button, a dialog box appears with a list of the values for the selected field.
Using Formulas Hides or views the Functions tree. Hides or views the Operators tree. Hides or views the result set of a global formula search. Allows you to select Crystal syntax or Basic syntax as your formula syntax. Allows you to select Exceptions For Null or Default Values For Nulls as a method for dealing with null values in your data. Comments out the highlighted selection of a formula. Commented lines are not evaluated as part of the formula. 20.4.
Using Formulas Window Description of contents Functions Functions are prebuilt procedures that return values. They perform calculations such as average, sum, count, sin, trim, and uppercase. Custom functions are also listed in this window. Operators Operators are the "action verbs" you use in formulas. They describe an operation or an action to take place between two or more values. Examples of operators: add, subtract, less than, and greater than.
Using Formulas The Report Fields, Functions, and Operators tree at the top of the Formula Editor contain the primary formula components. Double-click any component from these trees to add this component to your formula.
Using Formulas 429 Keyboard Combination Action Performed Alt+M Comments out or removes comments from current line Alt+O Sorts contents of trees. Alt+P Opens or closes the Shows Operator tree. Alt+S Saves formula without closing Formula Editor. Alt+U Opens or closes the Shows Function tree. Ctrl+A Selects all. Ctrl+C Copies. Ctrl+End Goes to the end of the last line of the formula. Ctrl+F Opens the Find dialog box (same as clicking the Find or Replace button). Ctrl+F2 Sets bookmark.
Using Formulas Keyboard Combination Action Performed Ctrl+Shift+Tab Changes focus to next control box (reverse order of Ctrl-Tab). Ctrl+Tab Changes focus to next control box. Ctrl+V Pastes. Ctrl+X Cuts. Ctrl+Z Undoes an action. Ctrl+Shift+Z Repeats an action. Ctrl+Space Keyword Auto Complete—shows a list of the functions available. End Goes to end of line. Enter Copies a selected object from a list to the formula text box. F3 Finds next item as defined in the Find dialog box.
Using Formulas 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. If you are unsure which syntax to choose see Formula syntax. 5.
Using Formulas 1. On the Report menu, click Formula Workshop. 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.
Using Formulas You can now use this formula in your report just as you would use a formula you created in the Formula Editor. 20.5.3 Editing formulas 1. On the View menu, click Field Explorer. The Field Explorer dialog box appears. 2. Right-click the formula you want to edit and choose Edit. The Formula Workshop appears with the Formula Editor active. 3. In the Formula Editor, edit the formula. 4. Click Check to identify any errors in the formula. 5. Fix any syntax errors the Formula Checker identifies.
Using Formulas 20.5.5 Copying formulas 20.5.5.1 To copy an existing formula Crystal Reports lets you copy an existing formula and then modify it to create a new formula. 1. Choose View from the main menu and select Field Explorer. The Field Explorer dialog box appears. 2. Select an existing formula in the Formula Fields list, right-click it, and select Duplicate from the shortcut menu. Crystal Reports creates a copy of the formula by appending a number to the end of the formula's name. 3.
Using Formulas The Field Explorer dialog box appears. 7. Select Formula Fields and click the New button. The Formula Name dialog box appears. 8. Enter the name you want to identify the formula by, and then click OK. The Formula Workshop appears with the Formula Editor active. 9. Place the insertion point where you want the text to appear in the Formula text box of the Formula Editor and press Ctrl+V to paste the text from the Clipboard. 10.
Using Formulas • If the formula contains conditional elements, make certain that the conditions apply to the data in the new report. For example, if the formula in your old report performed an action when the quantity was greater than 100, make sure that the greater than 100 condition makes sense in the new formula. When modifying a formula, you may find that greater than 10 or greater than 2000 makes more sense with your new data.
Using Formulas 1. Choose View from the main menu and select Field Explorer. The Field Explorer dialog box appears. 2. Right-click the formula you want to delete and choose Delete. Note: A dialog box appears if this formula is currently in use in a report. If you delete this formula, you will delete all references of it in reports. Click Yes to delete. 20.7 Debugging formulas For help on debugging formulas that occur when you save your formula, see the Debugging tutorial.
Using Formulas Insert this formula into the details section of the report and preview it. You'll get a division by zero error and the formula editor will be invoked with the call stack on the left hand side. 20.7.2 Debugging tutorial Follow the example below to learn the necessary steps for debugging a formula. After completing this exercise, use the same principles to debug your own formulas. 20.7.2.1 About this tutorial • This tutorial uses the Xtreme.mdb sample database.
Using Formulas 2. Create a new formula called Formula1. 3. Type the following in the Formula text box of the Formula Editor: If {customer.CUSTOMER NAME} [1 to 2 = "Bi" Then "TRUE" Else "FALSE" 4. Click Check to test for errors. You will receive the following error message: The ] is missing. 5. Correct the formula by inserting the missing " ] " after the 2. 6. Click Check again. You will receive the following message: No errors found. 7. Click Save and close on the Formula Workshop toolbar. 8.
Using Formulas You should see "TRUE" next to all customer numbers that begin with 6 and "FALSE" next to all customer numbers that do not begin with 6. 20.7.2.4 Formula3 1. Create a new formula called Formula3. 2. Type the following in the Formula text box of the Formula Editor: If {customer.CUSTOMER NAME} [1 to 2] = 'Ro" Then "TRUE" Else "FALSE" 3. Click Check to test for errors. You will receive the following error message: The matching ' for this string is missing. 4.
Using Formulas 7. Click Print Preview on the Standard toolbar to see the values in the report and compare the fields to check if the field values returned by @Formula4 are correct. You should see "TRUE" next to all Customer IDs that begin with 5 and "FALSE" next to all Customer IDs that do not begin with 5. Now that the formulas are error-free and the field values returned are correct, you will create a formula that links the separate components together.
Using Formulas 20.8 Error Messages and Formula Compiler Warnings A ) is missing. Parentheses must be used in pairs; each opening parenthesis must be matched with a closing parenthesis. One of your opening parentheses is not matched by a closing parenthesis. Insert the missing parenthesis and recheck. A ] is missing. Brackets must be used in pairs; each opening bracket must be matched with a closing bracket. One of your opening brackets is not matched by a closing bracket.
Using Formulas A subscript must be between 1 and the length of the string. You have entered a subscript number that specifies a character that does not exist. If you enter a subscript that references the 6th or the 8th character in a five character string, for example, you will get this warning. Change the subscript to a value that exists and recheck. Note: A subscript can be a negative number if that number is a negative between -1 and the negative length of the string.
Using Formulas Cannot allocate memory. This message typically indicates that there is not enough memory available. Close any reports that are not needed, and exit any programs that are not essential. Then try again. Cannot reallocate memory. This message typically indicates that there is not enough memory available. Close any reports that are not needed, and exit any programs that are not essential. Then try again. Custom function, a return value must be specified by assigning a value to the function name.
Using Formulas File not found. The file name you specified cannot be found. Either the filename or the path is incorrect. Enter the correct filename/path and try again. In some instances the file WBTRVDEF.DLL is missing from your directory. This file is required for reading Data Dictionary files along with WBTRCALL.DLL. File permission error. You have requested a file for which you do not have permission. You must gain the necessary permission before you can activate the file.
Using Formulas You cannot begin to utilize the program unless you have a default printer selected. Trying to start the program without a default printer results in this error message. To select a default printer. Click the Printers icon in the Windows Control Panel; the Printers dialog box appears with all installed printers listed in the Installed Printers box. If you have not yet installed the printer, install it first, and then double-click its listing.
Using Formulas Report file already exists. Overwrite sample.rpt? You are attempting to save a report under the same name as an existing report. This will overwrite the existing report and make it no longer available. Click Yes to overwrite the report, No to stop the saving process to give you a chance to select a different name. Report has changed. Save changes to sample.
Using Formulas The number of copies of the string is too large or not an integer. Using the ReplicateString function, you have requested too many copies or you are requesting a non-integer number of copies. Lower the number of copies requested or specify an integer number of copies and try again. The number of days is too large or not an integer.
Using Formulas The string is non-numeric. The argument to the ToNumber function must be a number stored as a string (for example, a customer number, an ID number, and so on). The string may be preceded by a minus sign and may contain leading and trailing blanks. You have used an argument that is non-numeric and therefore cannot be converted to a number. Change the argument to numeric and recheck. The summary field could not be created.
Using Formulas This array must be subscripted. For example: Array [i]. You have entered an array without enclosing it in brackets. Enclose the array in brackets and recheck. This field cannot be summarized. You have entered a summary field that does not already exist in your report. Any summary field you enter in a formula must duplicate a summary field already in your report.
Using Formulas This function cannot be used within a custom function. You have tried to use a function or formula that will not work in a custom function, that is it is not "stateless". This group section cannot be printed because its condition field is non-existent or invalid. Your report contains a group section that is based on a condition field that is either no longer in the report or changed so it is invalid for the group section.
Using Formulas 452 • A date is required here. • A date range is required here. • A number array is required here. • A number array or currency array is required here. • A number, currency amount, Boolean value, or string is expected here. • A number, currency amount, Boolean, date, or string is required here. • A number, currency amount, date, or string is required here. • A number, currency amount, or date is required here. • A number field or currency amount field is required here.
Parameter Fields and Prompts Parameter Fields and Prompts 21.1 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 • Currency: Requires a dollar amount. Example: Display customers with sales over XXXXX. • Date: Requires an answer in a date format. Example: Enter the start and end dates of the quarter. • DateTime: Requires both date and time. Example: Display statistics for 07/04/1999 between 1:00pm-2:00pm. • Number: Requires a numeric value. Example: Enter the customer identification number. • String: Requires a text answer. Example: Enter the region.
Parameter Fields and Prompts • Creating a parameter with a static prompt. • Creating a parameter with a dynamic prompt. • Creating a parameter with a cascading list of values. • Dynamic prompts contain lists of values that you can use for a particular report or for many reports. You share a list of values by adding it to the BusinessObjects Enterprise Repository. • A list of values can be scheduled for automatic updates through the Business View Manager.
Parameter Fields and Prompts 21.1.4 Optional parameters Crystal Reports supports optional parameters. An optional parameter is a prompted value that does not have to be supplied by the user. Report designers should provide some guidance to the end user that a parameter is optional by adding this information to the prompt text. Optional parameters are treated by Crystal Reports as optional anywhere within the report that they are used.
Parameter Fields and Prompts Note: In the table, and elsewhere in this section, the term managed report is used to describe reports that are stored in a SAP BusinessObjects Enterprise environment, while unmanaged report is used to describe reports that are stored outside such an environment.
Parameter Fields and Prompts Feature Available when Crystal reports are stored outside of SAP BusinessObjects Enterprise? Available when Crystal reports are published to SAP BusinessObjects Enterprise? Schedule lists of values to update themselves on a regular basis. No Yes Schedule portions of lists of values to update themselves on a regular basis, while the remaining portions retrieve their values from the database on demand.
Parameter Fields and Prompts Note: While the dynamic capability is not available with SAP BusinessObjects BI Interactive Analysis or with full-client products, Crystal reports that are based on universes can use the dynamic prompting capabilities described throughout this section. 21.3 Understanding lists of values List-of-values objects describe how to return a set of values from a data source: • They provide the values for prompts in the prompting dialog box.
Parameter Fields and Prompts • Unmanaged lists of values are stored within each report file. If you do not have SAP BusinessObjects Enterprise, or if you never publish your reports to SAP BusinessObjects Enterprise, you use an unmanaged list-of-values object. Unmanaged list-of-values objects can use report fields or command objects as a data source. • Managed lists of values are stored within SAP BusinessObjects Enterprise.
Parameter Fields and Prompts Unmanaged list of values Managed list of values Report fields Business Views Feature Command objects Yes Display different values to different users. No No Because managed lists of values are based on Business Views, they inherit the view-time security capabilities of Business Views. Yes Schedule the list of values to update on a recurring schedule. No No Partially schedule the list of values to update only certain portions of the list on a recurring schedule.
Parameter Fields and Prompts Unmanaged list of values Managed list of values Report fields Command objects Business Views Not well suited. Well suited. Because report fields cannot be filtered, any filtering of the list that you require must be done outside of Crystal Reports in a database view. You can define filtering within a command object. (That command objects return the data for all levels in the list is not a problem for single-level lists.) Description Single-level code tables.
Parameter Fields and Prompts 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 as separate objects so that you can share the same list of values with different presentations. For example, you can have a Shipping City prompt, and a Customer City prompt.
Parameter Fields and Prompts 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. 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. This example will enable the user to choose from any of the countries.
Parameter Fields and Prompts 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. 11. Select the country to base the report on. This example uses Brazil. 12. Click OK. 21.4.2 To incorporate the parameter into the record selection filter 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.
Parameter Fields and Prompts 5. Click OK. 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. 21.4.3 To incorporate the parameter into a saved data selection formula 1. On the Report menu, point to Select Expert and then click Saved Data. The Choose Field dialog box appears. 2. Highlight the field on which you want to base record selection and click OK. The Select Expert appears. 3.
Parameter Fields and Prompts dynamic prompts. When Crystal Reports is used with Crystal Reports Server or SAP BusinessObjects Enterprise, additional features are available. For more information, see Managed reports. Use the following steps to create a parameter that uses a dynamic prompt. As part of the procedure, you will create a list of values. 21.5.1 To create a parameter with a dynamic prompt 1. Open the sample report called Group.rpt.
Parameter Fields and Prompts 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. When you look at your dynamic prompt within Crystal Reports, it does not seem to be much different from a static prompt.
Parameter Fields and Prompts and region that city comes from, you could create a dynamic and cascading prompt. In this case, you first prompt for a country, and when that value has been selected, the program prompts for a region by showing only the regions that apply to the selected country. Finally, when a region value has been selected, the program prompts for a city by showing only the cities that apply to the selected region.
Parameter Fields and Prompts The program automatically expands the Value list. You use this area to define the fields that make up your cascading list of values. This example uses a cascade of Country, Region, and City. 9. From the Value list, select Country. 10. Click the blank field under Country and select Region. 11. Click the blank field under Region and select City. 12. In the Parameters area, click City to bind the field that contains the City value to the parameter.
Parameter Fields and Prompts 21.7 The Parameter Panel The Parameter Panel lets users interactively format and filter report data by changing parameter values. It is located within the Preview Panel. In the Create New Parameter and Edit Parameter dialog boxes, you can specify parameters to display on the Parameter Panel via the Show on (Viewer) Panel option. You can choose one of several settings: • Do not show The parameter is not visible on the panel.
Parameter Fields and Prompts You can add lists of values to SAP BusinessObjects Enterprise or Crystal Reports Server in several ways: • You can create a list of values when you design a report, and then you can save the report to an Enterprise folder. For more information, see Saving a report to an Enterprise folder. • You can create a list of values in the Business View Manager. For more information, see the Business Views Administrator's Guide.
Parameter Fields and Prompts 7. Click the blank field under Country and select Region, and then click the blank field under Region and select City. 8. In the Parameter Binding area, ensure that only the City value is bound. 9. Click OK. 10. Return to the Field Explorer dialog box, select Parameter Fields, and click New. 11. Enter a name for the second parameter in the Name field. This example uses CustomerCity. 12. In the List of Values area, click Dynamic. 13.
Parameter Fields and Prompts 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 Customer Name. 4. In the List of Values area, click Dynamic. 5. Click Insert and, in the Value field, select Customer ID. 6. In the Description field, select Customer Name. 7. In the Value Options area, set the Prompt with Description Only option to True. 8. Click OK.
Parameter Fields and Prompts Null values in the data returned by the list of values are displayed as "null" in the drop-down list for that prompt. Your users can select these values, and any parameters associated with that prompt receive the null value. You can test for this functionality in the Crystal Reports formula language with the IsNull function. For more information, see "IsNull (fld)" in the online help. 21.8.
Parameter Fields and Prompts 21.9 Best practices for prompting 21.9.1 Unmanaged reports Unmanaged reports are reports that you store outside of SAP BusinessObjects Enterprise. These reports can use lists of values that you define within the report, or lists of values that you have stored in the SAP BusinessObjects Enterprise Repository. (You create report lists of values in Crystal Reports when you use the "Create New Parameter" dialog box.
Parameter Fields and Prompts • Build a separate Business View to provide lists of values for all of your reports. The only fields that you need in this Business View are those that you use for prompting. See the Business Views Administrator's Guide for information about how to create a Business View. • Create managed list-of-values objects for each dynamic prompt that you intend to use in your report. These objects are visible to Crystal Reports users when they design parameters and prompts.
Parameter Fields and Prompts 21.9.4 Deploying managed reports with dynamic prompts Repository-based lists of values and prompt groups are repository objects like any other. When managed reports are migrated from one repository to another using the Import Wizard, the list-of-values and prompt-group objects are imported as well. This functionality makes it easy to migrate reports from system to system.
Parameter Fields and Prompts Tip: Another way to do this is to click the Select Expert button on the Expert Tools toolbar. The Select Expert dialog box appears. 2. Choose the tab whose selection criteria uses the parameter you want to delete. 3. Click Delete. 4. Click OK to close the Select Expert. 5. On the View menu, click Field Explorer. 6. Expand the Parameter Fields folder and click the parameter you want to delete. 7. Click Delete. 21.10.3 To delete a parameter that is used in a formula 1.
Parameter Fields and Prompts • If you did not specify a default value, the program will not refresh the data until you supply a new value. Note: If the parameter is a string value type, without a default value, and the "Allow discrete values" field is True, an empty string appears. 21.11.2 Refreshing report data When you refresh data from the Preview tab, the Refresh Report Data dialog box appears. Select the "Use current parameter values" option to use the current parameter value.
Parameter Fields and Prompts Note: The drop-down arrow to access the calendar for dates and the up and down arrows to scroll for times will only be available if you have the correct version (4.70 or later) of comctl32.dll. To use the current value displayed, click OK, or, to use a different value from the one displayed, type a new value in the text box, and click OK. • If the parameter field is range limited, then you can only enter values within a certain range.
Parameter Fields and Prompts 8. Click OK. 21.12.2 Applying conditional formatting using parameter fields Parameter fields can be used to create conditional formatting formulas. You can customize these formulas whenever you refresh the report data. A conditional formatting formula could be used for color-flagging data that meets certain conditions. For example: • Sales representatives who sell more than 10% over quota. • Customers who have not ordered in the last quarter.
Parameter Fields and Prompts 21.12.3 Creating a report title using parameter fields Crystal Reports allows you to use parameter fields to create a report title that can be changed each time the report is refreshed. 21.12.3.1 To create a report title using parameter fields 1. On the View menu, click Field Explorer. The Field Explorer appears. 2. Select Parameter Fields and click New. The Create New Parameter dialog box appears. 3. Type a name for the parameter field in the Name field. 4.
Parameter Fields and Prompts 21.12.4.1 To specify single or range values 1. Select the Allow discrete values option or the Allow range values option to specify whether the parameter field will accept a range of values. • If you select "Allow discrete values," the parameter field will accept single values (rather than ranges of values). • If you select "Allow range values," then when you are prompted for parameter values, you can enter a start value and an end value.
Parameter Fields and Prompts 6. Create a formula using the parameter field as you would any constant value. For example, rather than creating a formula that hard-codes the country name: {customer.COUNTRY} = "USA" Use a parameter field instead of "USA". {customer.COUNTRY} = {?Country} To do this double-click the database field, press =, then double-click the parameter. Tip: Identify parameter fields easily by looking for (?). 7. Click Save and Close on the Formula Workshop.
Parameter Fields and Prompts This formula prompts for a value for the parameter field {?SortField}. If you enter "C", the formula will sort by the City field. If you enter "R" it will sort by the Region field. If you enter anything else, or do not enter anything at all, the formula will sort by the Country field. For more information see "If statements" in the online help. 6.
Parameter Fields and Prompts • "&" (allows any character or space, and requires the entry of a character in the parameter value). • "C" (allows any character or space, and does not require the entry of a character in the parameter value). • ". , : ; - /" (separator characters). Inserting separator characters into an edit mask is something like hard coding the formatting for the parameter field.
Parameter Fields and Prompts • • • To prevent report refreshing to the database, only non-data parameters should be added to the Parameter Panel. Exported reports retain all recently applied parameter changes. Sub-report parameters cannot be shown on the Parameter Panel. 21.12.8.1 To create a saved-data record filter using parameter fields • Combine the tasks below to create an interactive saved-data record filter. a. Create a parameter appearing on the Parameter Panel b.
Parameter Fields and Prompts 7. Select Editable from the Show on (Viewer) Panel list. This example will enable the user to edit the parameter value on the Parameter Panel. If you want to show the parameter on the panel, but do not want to allow the user to edit it, select Read only for this option. 8. Click OK. 9. Return to the Field Explorer dialog box, and drag the parameter onto your report. The Enter prompt values dialog box appears.
Parameter Fields and Prompts 21.12.11.1 To add dynamic grouping using parameter fields 1. Create a report using the sample data, Xtreme.mdb, and place the following fields from left to right in the Details section: {Customer.Customer Name} {Customer.Country} {Orders.Order ID} {Orders.Order Date} {Orders.Order Amount} 2. Create a new parameter field and call it GroupBy. 3. Select String from the Type list. 4. Add the following values to your parameter: • Customer • Country • Order 5.
Subreports Subreports 22.1 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. The only differences between a subreport and a primary report are that a subreport: • Is inserted as an object into a primary report; it cannot stand on its own (although a subreport can be saved as a primary report).
Subreports 22.1.1.1 Unlinked Unlinked subreports are free-standing; their data is not in any way coordinated with the data of the primary report. In unlinked subreports, there is no attempt to match up the records in one report with records in the other. An unlinked subreport does not have to use the same data as the primary report; it can use the same data source or a different data source entirely. In addition, the subreport is not limited to reporting on a single table.
Subreports 22.1.2 How subreport linking works When you link a subreport to a primary report, the program creates the link by using a parameter field. When a subreport link field is selected, the program creates: • A parameter field in the subreport which is then used to retrieve values passed to it by the primary report. • A record selection formula for the subreport using the parameter field.
Subreports 22.2 Inserting subreports 1. On the Insert menu, click Subreport. Tip: Another way to do this is to click the Insert Subreport button on the Insert Tools Toolbar. The Insert Subreport dialog box appears. 2. To choose an existing subreport, click Choose an existing report and type the name. If you do not know the name, click the Browse button and locate it in the dialog box that appears.
Subreports 6. Click the Preview tab to see your report. 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. 22.2.
Subreports 22.2.2 Saving a subreport as a primary report You may find it advantageous to save a subreport as a primary report for the sake of distributing the information to a variety of audiences. For example, the primary report containing the subreport may be relevant for a stockholders meeting at the end of the fiscal year; however, the data contained in the subreport may be relevant for everyday use by your managers. In such cases, it is easy to save a subreport as a primary report. 22.2.2.
Subreports 22.2.3.1 To manually update the subreport data You can update your subreport data at any time. 1. On the Design tab, right-click the subreport. 2. Click Re-import subreport from the shortcut menu. 3. Click Yes to update the subreport data. 22.2.3.2 To globally update subreports when opening a main report 1. On the File menu, click Options. 2. In the Options dialog box, click the Reporting tab. 3. Click Re-import Subreport on Open. 4. Click OK.
Subreports 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. 22.3.1 To link a subreport to the data in the primary report 1.
Subreports 22.4 Linking a subreport to the main report without modifying the selection formula Crystal Reports uses a parameter field mechanism for linking subreports to main reports. When linking a main report field that is not a parameter field to a subreport field, the program: • Automatically creates a parameter field to complete the link. • Modifies the subreport record selection formula to select those records in which the subreport field is equal to the parameter field value.
Subreports 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. While the reports could be based on the same data set, they do not have to be. They could each be based on entirely different data sets. Each of these reports is free-standing; the data in any of the reports is not linked in any way to data in another report.
Subreports Tables can be linked in a report as long as the following criteria are met: • The link fields are both database fields. • The link fields contain similar data. • The link fields are the same length. • The link field in the link to (lookup) table is indexed (PC databases only). Linking tables is rarely a problem. However, there are some circumstances in which you cannot coordinate data from different tables because the data does not meet the linking criteria.
Subreports 4. Link the subreport to the primary report by linking the Social Security Number field in the primary report ({file.SSN}) to the formula that extracts the number in the subreport ({@EXTRACT}). See Linking a subreport to the main report without modifying the selection formula. 22.6.2 Linking unindexed tables When using PC (not SQL or ODBC) databases, the link field in the lookup database needs to be indexed in order to create a valid link.
Subreports 22.7.1 To create an on-demand subreport 1. Place an ordinary subreport in your primary report. 2. Click the Format button on the Expert Tools toolbar. The Format Editor dialog box appears. 3. Click the Subreport tab and select the On-demand subreport check box. 4. Click OK. 22.8 Adding captions to on-demand subreports To further organize a report, captions can be created for the Subreport Preview tab and for the placeholder frame of an on-demand subreport.
Subreports Basic syntax formula example: formula = "More Information About" + {Customer.Customer Name} Using the Xtreme.mdb sample database, these formulas would give you a caption like "More Information About Pathfinders" or "More Information About Rockshocks for Jocks." 5. Click Check to check the formula for errors. If the program finds an error, it will prompt you with a message box detailing the nature of the error. 6. After fixing any errors, click Save and close. 7.
Understanding Databases Understanding Databases 23.1 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 section discusses several concepts and tasks common to working with database files.
Understanding Databases 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. The following diagram displays how two tables can have a relationship: 23.1.
Understanding Databases Creating indexes for database tables can increase the speed of data access and reduce the time it takes for the program to evaluate data. Some DBMS applications automatically index your database tables, while others require that you create an index yourself. For the best report generation performance, make sure each of your database tables has a corresponding index. Note: Some DBMS applications do not support indexed tables.
Understanding Databases request. If not, it goes to the second order number, and checks that customer name. When an order number is reached that contains the correct customer name, the database engine retrieves the information, then continues to the next order number. Using this technique, both the Order# field and the Customer field must be read for every single record in the table.
Understanding Databases the database engine does not need to continue searching through the index or the table as soon as it finds an index entry that does not match the requested customer. The advantage of this highly organized search through a database table according to an index is speed. Using indexes speeds up data retrieval and report generation, important factors when reporting on large database files. 23.
Understanding 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.
Understanding Databases the finished results are sent back to the client. This provides more efficient time management for users because the local workstation has less processing time and more "up" time available to the user. 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 the statement, performs the requested operation, and returns any data requested to the client software. If the server returns any data, the client software displays the data to the user. 23.2.2.1 Stored procedures In addition to the common relational database attributes (tables, fields, records, and so on) many SQL DBMS systems support stored procedures. A stored procedure is a compiled SQL program consisting of one or more SQL statements.
Understanding Databases This SQL query is a representation of the SQL statement that Crystal Reports sends to the SQL server. By interpreting as much as possible from the report design into an SQL query, Crystal Reports can off-load much of the report processing onto the server machine.
Understanding Databases 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.'STATE' FROM 'TABLEA' TABLEA 23.2.4.
Understanding Databases clause, the program sorts the records according to the values in the first field specified, then, within that sort, the program sorts the records by the values in the second field specified, and so on. The following SQL statement uses the ORDER BY clause: SELECT MYTABLE.'COMPANY', MYTABLE.'CITY', MYTABLE.'STATE' FROM 'MYTABLE' MYTABLE ORDER BY MYTABLE.'STATE' ASC, MYTABLE.
Understanding Databases When you use a virtual table that was created as a Command, or when you use a Command from the SAP BusinessObjects Enterprise Repository, Crystal Reports does not alter the syntax of the SQL submitted to the server (that is, it does not automatically add quoting or escape characters). This behavior also applies to parameters used in Commands. Therefore, you must add the quoting and escape characters that are necessary for your database driver.
Understanding Databases Note: The use of double or single quotes (and other SQL syntax) is determined by the database driver used by your report. You must, however, manually add the quotes and other elements of the syntax as you create the command. 5. Optionally, you can create a parameter for your command by clicking Create and entering information in the Command Parameter dialog box. For more information about creating parameters, see To create a parameter for a command object. 6. Click OK.
Understanding Databases Enter the text you want to appear when the program prompts you. • Value Type Select the data type of the parameter field. • Default Value Enter the value you want the program to use if you do not supply a new value. This is an optional step. 3. Click OK. Your parameter is added to the Parameter List. You can modify or delete it by returning to the Modify Command dialog box. 23.2.5.4 To add a parameter to a command object 1.
Understanding Databases 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. If the databases are not found in the specified location, the program notifies you of the discrepancies. Use the Set Datasource Location command on the Database menu to change the alias pointers stored by Crystal Reports.
Understanding Databases This button is available only when like items are selected (that is, if you select a table in the upper list, you must select a table in the lower list). Once the Update button is clicked, the new data source information is updated in the Current Data Source list. 5. Repeat steps 2 to 4 if necessary. 23.4 Working with aliases For a variety of reasons, database names and locations get changed.
Understanding Databases the file name extension. For example, if you are using the dBASE database file Company.dbf, the program will assign a default alias name of Company to the file. You can accept the default alias or assign a new one to the database table. You can change an alias at any time using the Database Expert. However, if you have already created formulas in your report using the original alias name, you will need to edit the formulas to use the new alias. 23.
Understanding Databases In a one-to-one relationship between records in two linked tables, for every record in the primary table there is only one matching record in the lookup table (based on the linked fields). For example, in the Xtreme.mdb database, the Employee table can be linked to the Employee Addresses table based on the Employee ID field in each table. The Employee table contains information about employees at the company, the positions they hold, their salaries, hiring information, and so on.
Understanding Databases In a worst case scenario the program would have to read about 67,600 records to accomplish the same task. Note: The performance considerations for data files are different from the considerations for SQL databases. A data file is any non-SQL database that is accessed directly from Crystal Reports.
Understanding Databases PC Data Link ing/Subre port Selection Formula Index A Index B Reads A For each A reads in B Total Records Read Linking No Yes or No Yes 26 100 (26*100) 2600 Linking Yes No Yes 26 100 (26*100) 2600 Linking Yes Yes Yes 2 100 (2*100) 200 Subreport No No No 26 2600 (26*2600) 67,600 Subreport No Yes No 2 2600 (26*2600) 67,600 Subreport No Yes Yes 26 100 (26*100) 2600 Subreport Yes No No 2 2600 (2*2600) 5200 Subreport Yes No Yes
Understanding Databases Linking/Subre port Selection Formula Reads A For each A reads in B Total Records Read Subreport No 26 100 (26*100) 2600 Subreport Yes 2 100 (2*100) 200 23.5.4 Data file considerations When working with data files, one-to-many links can occur when you link tables in a single report or when you add a subreport to your report. 23.5.4.
Understanding Databases • The program passes this merged record (A+B) back to the Report Designer, which tests the record against the entire selection formula. • The program then reads the second matching record and passes the merged record on, and then reads the third matching record, and so on, until it has read all of the matching records. • The program then returns to Table A and reads the next record.
Understanding Databases • The number of records read for each subreport is determined by the index situation on Table B: • If you have an index on Table B, the program will read only the matching records (100) when it runs a subreport. • If you do not have an index on Table B, the program will always read every record in Table B (2600) when it runs a subreport. 23.5.
Understanding Databases • • If there is no selection formula, or if the selection formula does not pass down range limits on Table A, the program runs a subreport for every record in Table A (26). The number of records read by each subreport remains the same regardless of whether there was range limit selection on Table A. Each subreport will read only those records in Table B that match each record read in Table A (100). 23.5.6 Performance considerations for all reports 23.5.6.
Understanding Databases Note: If all of the conditions in an AND situation can be satisfied on the server or in the database DLL, the program passes them all down. • OR situations {customer.REGION} = "CA" or {customer.CUSTOMER ID}[3 to 5] = "777") In this situation, the program also sees that it can pass down the condition before the Or operator but not the condition after.
Understanding Databases 23.5.7 The Database Expert Links tab The Database Expert Links tab lets you easily link two or more tables. Choose the Database Expert from the Database menu to display all current tables; then choose the Links tab to display all current links. The easiest way to link database tables is to select Auto Link in the Database Expert Links tab.
Understanding Databases 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. Delete the link made by the Smart Linking feature and manually link the tables. See Linking multiple tables for more information. Note: Not all DBMS applications support indexed tables.
Understanding Databases 23.5.10 Linking options Crystal Reports enables you to specify the type of join and type of link you want to use when linking tables. You can also enforce the use of tables in your joins. Joins and links indicates how linked fields in two tables are compared when records are read. Join, enforce, and link options can be specified in the Link Options dialog box.
Understanding Databases Customer Table Customer Table Orders Table Customer ID Customer Name Order Amount 52 Allez Distribution 25141.50 53 BG Mountain Inc. 19164.30 53 BG Mountain Inc. 1683.60 57 Hansen MTB Inc. 15716.40 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 23.5.10.
Understanding Databases Customer Table Customer Table Orders Table Customer ID Customer Name Order Amount 52 Allez Distribution 25141.50 53 BG Mountain Inc. 19164.30 53 BG Mountain Inc. 1683.60 57 Hansen MTB Inc. 15716.40 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.
Understanding Databases 23.5.10.3 Right Outer join The result set from a Right Outer join includes all the records in which the linked field value in both tables is an exact match. It also includes a row for every record in the lookup (right) table for which the linked field value has no match in the primary table. If you link the Customer table to the Orders table, you get one row in the table for each order a customer has placed.
Understanding Databases 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. If you are connecting to a SQL database directly (not through ODBC), Crystal Reports uses a syntax native to the database.
Understanding Databases Customer Table Orders Table Orders Table Customer ID Order ID Order Amount 63 32 12763.95 64 14 8233.50 25 10320.87 65 66 23.5.10.5 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). This is the default option. 23.5.10.
Understanding Databases 23.5.10.7 Enforced To When you select this option, if the from table for the link is used, the link is enforced. For example, if you create a link from TableA to TableB using Enforce To and select only a field from TableA, the join to TableB will be enforced, and the Select statement that is generated will include both tables. Note: For an explanation of from and to tables, see Link from and link to. 23.5.10.
Understanding Databases Customer Table Customer Table Orders Table Customer ID Customer Name Order Amount 53 BG Mountain Inc. 19164.30 53 BG Mountain Inc. 1683.60 57 Hansen MTB Inc. 15716.40 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 23.5.10.
Understanding Databases 'Manager' Manager WHERE SalesRep.'Salary' > Manager.'Salary' This SQL statement might produce data similar to 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 Dodsworth $48,300.00 Hellstern $45,000.00 Dodsworth $48,300.00 Fuller $32,000.00 Dodsworth $48,300.00 Brid $30,000.
Understanding Databases The result set from a Greater Than Or Equal link includes all records in which the linked field value in the primary table is greater than or equal to the linked field value in the lookup table. The example here is identical to the example for the Greater Than join, except that it uses the Greater Than Or Equal link: SELECT SalesRep.'Last Name', SalesRep.'Salary', Manager.'Last Name', Manager.'Salary' FROM 'SalesRep' SalesRep, 'Manager' Manager WHERE SalesRep.'Salary' >= Manager.
Understanding Databases 23.5.10.12 Less Than [<] link The result set from a Less Than link includes all records in which the linked field value in the primary table is less than the linked field value in the lookup table. Using the Less Than link, you can compare sales representative and manager salaries in a different direction. Once again, the Salary field in each table is used as the link field.
Understanding Databases 23.5.10.13 Less Than Or Equal [<=] link The result set from a Less Than Or Equal link includes all records in which the linked field value in the primary table is less than or equal to the linked field value in the lookup table. The example here is identical to the example for the Less Than link, except that it uses the Less Than Or Equal link: SELECT Manager.'Last Name', Manager.'Salary', SalesRep.'Last Name', SalesRep.
Understanding Databases Manager Table Manager Table SalesRep Table SalesRep Table Last Name Salary Last Name Salary Hellstern $45,000.00 Dodsworth $48,300.00 23.5.10.14 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).
Understanding Databases Product1 Product2 Product Name Product Name Xtreme Mtn Lock InFlux Lycra Glove Xtreme Mtn Lock Roadster Micro Mtn Saddle InFlux Lycra Glove Xtreme Adult Helmet InFlux Lycra Glove Xtreme Mtn Lock InFlux Lycra Glove Roadster Micro Mtn Saddle Roadster Micro Mtn Saddle Xtreme Adult Helmet Roadster Micro Mtn Saddle Xtreme Mtn Lock Roadster Micro Mtn Saddle InFlux Lycra Glove Note: The symbol != is used to represent a Not Equal link, if the ODBC data source driver for
Understanding Databases the data retrieval and sorting work onto the server system, thus freeing up local memory and resources for more important tasks. That is why server-side processing works only for reports that have been sorted and grouped; if a report has not been sorted and grouped (for example, if it is a simple list report), then there is no processing to push to the server. You should also note that server-side processing works only for reports based on SQL data sources.
Understanding Databases 23.6.1 How server-side grouping affects the SQL query When a report pushes most of its processing to the server, this by necessity alters the SQL query. Thus, when the Perform Grouping on Server option is enabled, individual aspects of server-side processing will modify the SQL statement in different ways.
Understanding Databases Note: You can also enable or disable this option quickly by selecting or deselecting Perform Grouping on Server on the Database menu as needed. If Use Indexes or Server for Speed is not selected in the Report Options dialog box, this command is inactive. 23.
Understanding Databases On some occasions, you might want to unmap or unlink report and database fields that you have already mapped. For example, you might mistakenly map the wrong fields or want to map a report field to a database field that you have already mapped. In these cases, you can select a report field in the lower boxes (the program then automatically selects the database field) and click Unmap. The field names will move from the lower boxes to the upper boxes.
Understanding Databases • The name of a field that is used in the report has changed. • The database or universe has been upsized from a PC data source to an SQL data source. For a tutorial on the Map Fields dialog box, see Remapping altered database fields. Crystal Reports automatically adapts the report (and does not display the Map Fields dialog box) if it detects any of these changes: • Fields have been added to the database or universe.
Understanding Databases 23.7.3 Remapping altered database fields Use the Map Fields dialog box to remap existing report fields in the active database or universe if they have been altered. 23.7.3.1 To remap an altered database field 1. With the report active in the Design tab, choose Verify Database from the Database menu. The Verify Database message box appears. • If the program detects no changes in the active database, the message box displays this message: "The database is up to date.
Understanding Databases 23.8 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.
Understanding Databases 23.8.2 Considerations for using Saved Data Indexes There are some things that you should consider before deciding to use Saved Data Indexes: • Indexes work best in scenarios where a report contains a large set of data, but only small subsets of the data are viewed at one time. • Large, complex reports can overload the Crystal Reports report engine.
Understanding Databases 1. 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. Click OK to return to Crystal Reports. 5. If you want to create the index immediately, refresh and save the report. 23.
Integrating Business Applications with Crystal Reports Integrating Business Applications with Crystal Reports Crystal Reports is the program that you use to create and format Crystal reports, and SAP BusinessObjects Enterprise is the multi-tier system that allows you to distribute these reports over the Web in a secure and managed environment. The SAP BusinessObjects Enterprise XI 4.
Integrating Business Applications with Crystal Reports This section's focus is to provide a high level overview of the key new features and enhancements that are specific to SAP BusinessObjects Enterprise XI Integration for SAP Solutions for Crystal Reports. SAP BusinessObjects Enterprise XI Integration for SAP Solutions provides you with powerful report-design tools and a framework for managing, scheduling, and distributing your reports over the Web.
Integrating Business Applications with Crystal Reports Note: Crystal Reports includes several integrated tools that allow you to report off your data. Using the Log On Server command 1. Start Crystal Reports. 2. On the File menu (or Database menu, if a report is already open), click Log On or Off Server. 3. In the Data Explorer, expand Create New Connection. Then, expand the folder that corresponds to the driver that you want to use: • To use the Info Set driver, expand "SAP Info sets.
Integrating Business Applications with Crystal Reports 2. Click the Database tab. 3. In the Data Explorer area, select the check boxes for the types of data that you want to make available for the report. 4. Use the Table name LIKE and/or the Owner LIKE fields to select only a subset of the available data types. Use any of the following techniques when filtering: • Type full or partial names of tables.
Integrating Business Applications with Crystal Reports 24.1.2 Add-Ons The Add-Ons distribution contains the setup program and additional documentation for the components that integrate with your SAP system. The setup program determines which SAP BusinessObjects Enterprise products are already installed, and then installs the add-on components as required. The add-on components are: • Data Access This component provides database drivers that are designed specifically for reporting off SAP data.
Integrating Business Applications with Crystal Reports • The InfoSet driver provides Crystal Reports with another means of accessing SAP data sources: this driver can access R/3 InfoSets (previously known as Functional Areas) and ABAP Queries. You access this driver through the Data Explorer in Crystal Reports . • The Operational Data Store (ODS) driver allows you to use existing ODS objects from your BW Data Warehouse as a data source in Crystal Reports.
Integrating Business Applications with Crystal Reports Formatted reporting overview With the BW MDX Query driver, you can design formatted Crystal reports that are based on the data that is stored in SAP Business Information Warehouse (BW). You base these reports either on the queries that you create with SAP's Business Explorer (BEx) Query Designer or directly on cubes.
Integrating Business Applications with Crystal Reports 6. Click Query Properties. 7. Click the Extended tab and ensure that Allow External Access to this Query is selected. This option allows other programs, like Crystal Reports, to access this query. 8. Click Save Query. 9. In the "Save Query" dialog box, click Roles and select the role where you want to save the query. 10. Type a Description and a Technical Name for the query, then click Save.
Integrating Business Applications with Crystal Reports The next" SAP logon" dialog box prompts you for user credentials 4. Type your usual SAP user credentials in the Client, Username, and Password fields, then click Next. Tip: To set the logon language, you must specify your Preferred Viewing Local in Crystal Reports under View > Preferred Viewing Local. The next "SAP logon" dialog box asks you whether you would like to generate RFC trace files. 5.
Integrating Business Applications with Crystal Reports To adjust the field name and description settings 1. On the File menu, click Options. The "Options" dialog box appears, with the Layout tab active. 2. Click the Database tab. 3. In the “Tables and Fields” area, select Show Name, Show Description, or Show Both. 4. Click OK. Tip: For more information about formatted reporting, see the SAP Library > Business Information Warehouse > Business Explorer > Formatted Reporting: Crystal Reports Integration.
Integrating Business Applications with Crystal Reports Selecting cubes for reports If you or someone else in your organization has defined one or more cubes for reporting purposes within BW, you can select them as data sources for new Crystal Reports. To select an existing query for a report 1. On the File menu in Crystal Reports, click New and then click Blank Report. The "Database Expert" appears. 2. Under “Available Data Sources”, expand Create New Connection and then expand SAP BW MDX Query.
Integrating Business Applications with Crystal Reports "Crystal Reports" generates a blank report that uses your cube as its data source. You can now add objects to the report. Multiple Structures and Crystal Reports The MDX Query driver recognizes Multiple Structures and allows you to use them in your Crystal reports. Multiple Structures show in Crystal Reports as a single dimension. That is, they show with no attributes. The structure consists only of the members that are in the query.
Integrating Business Applications with Crystal Reports If you select this option now, you can modify your default settings later by clicking Settings on the SAP menu. 5. Click OK. Building a report off a BW query This tutorial provides an introduction to reporting off a query using the BW MDX Query driver.
Integrating Business Applications with Crystal Reports • Invoiced quantity • Sales Volume 7. Click Query Properties. 8. Click the Extended tab and ensure that Allow External Access to this Query is selected. This option allows other programs, like Crystal Reports, to access this query. To save the query in BW 1. Click Save Query. 2. In the "Save Query" dialog box, click Roles and select the role where you want to save the query. 3. Type a Description, such as Sample Material query 4.
Integrating Business Applications with Crystal Reports 3. Click the Refresh button to view the result set. Only one value is returned on the report. This value represents the aggregated value of this Key Figure across all Characteristics in the cube. To display more detailed results, you must group the data on one or more dimensions. To group and sort data Note: If you group on an attribute value, report performance is degraded. This occurs because the MDX query is slow to return data.
Integrating Business Applications with Crystal Reports 2. The Insert Summary dialog box appears. 3. In the Choose the field to summarize list, select Invoiced Quantity. 4. In the Calculate this summary list, select Sum. 5. In the Summary Location list, select Group #1:[Field Name]. In this case, [Field Name] indicates the name of the field that the group is based on. 6. Click the Refresh button to view the result set. When you view the report, there is one summary in Group Footer 1.
Integrating Business Applications with Crystal Reports To create a BW query with a hierarchy 1. Go to Start > Programs > Business Explorer > Query Designer. 2. Click Open Query. 3. Select the query you created in the last tutorial. If you have not created a query, see Building a report off a BW query. 4. In the Rows area, right-click Material and click Properties. The "Characteristic Properties" dialog box appears. 5. Next to the Hierarchy Name field, click the Values button.
Integrating Business Applications with Crystal Reports To build a hierarchy report off the BW hierarchy • On the View menu, click Field Explorer. The "Field Explorer" appears. Expand Database Fields and Simple Hierarchy for MDX to see the list of fields available for your report. Tip: You may want to set the field name and description display options to make it easier to select fields.
Integrating Business Applications with Crystal Reports How to summarize data This section guides you through the process of summarizing data based on the query you created in the last section, How to build a report off the BW hierarchy. To summarize the data 1. On the Insert menu click Summary. 2. The Insert Summary dialog box appears. 3. In the Choose the field to summarize list, select Invoiced Quantity. 4. In the Calculate this summary list, select Sum. 5.
Integrating Business Applications with Crystal Reports 6. In the Formula Workshop, enter the formula: HierarchyLevel (GroupingLevel({[Hierarchy Name] Node ID})) * 250 Note: • • • The field {[Hierarchy Name] Node ID} appears with its technical name in the formula. Positions are measured in twips; there are 1440 twips in an inch. The report now displays the summary data in its original position and the group headers hierarchically.
Integrating Business Applications with Crystal Reports 5. Ensure that the Selection field is set to Single Values, and that the Hierarchy field is set to the hierarchy you created for this dimension. In this example, the hierarchy is Material class. 6. Click the Variable tab. 7. Right-click in the white space and click New Variable. The SAP BW Variables Wizard opens. 8. Click Next on the Introduction window. 9. Ensure that the Type of Variable field says Hierarchy Node. 10.
Integrating Business Applications with Crystal Reports You now have a query with a simple hierarchy on Material class and a hierarchy node variable based on the Material class hierarchy. How to build a report off the query with a hierarchy node variable This section guides you through the process of creating a list of default values for the parameter that appears in "Crystal Reports". 1.
Integrating Business Applications with Crystal Reports should be based on the same hierarchy as the query. Therefore, you will change the hierarchy node variable to reflect the new hierarchy variable in this query. To create a hierarchy variable 1. Go to Start > Programs > Business Explorer > Query Designer. 2. Click Open Query. 3. Select the query you created in the last tutorial. This query already contains a hierarchy and a hierarchy node variable.
Integrating Business Applications with Crystal Reports Now that the display hierarchy is set to a variable rather than the Material class hierarchy, you must reset the hierarchy node variable to the new hierarchy variable. To set the hierarchy node variable 1. In the Rows area, click the Test Hierarchy Node Variable, and click Restrict. The "Selection for Material" dialog box appears. 2. In the Selection area, right-click the Test Hierarchy Node Variable. 3. Click Select Variable Hierarchy. 4.
Integrating Business Applications with Crystal Reports Note: You do not need to do this when viewing the report in SAP BusinessObjects Enterprise. In SAP BusinessObjects Enterprise the pick list is dynamic. Creating default values list for hierarchy node variable To create a list of default values for the hierarchy node variable Note: This procedure is based on the query you created in Building a report off a BW query with a hierarchy variable and a hierarchy node variable. 1.
Integrating Business Applications with Crystal Reports When a query contains a hierarchy variable, the MDX Query driver creates an extra field called Hierarchies in Crystal Reports that allows you to create a list of values for the variable. 1. In the Field Explorer, expand Parameter Fields, and right-click [HV01]. The parameter, [HV01], in CR represents the hierarchy variable you created for this query. 2. Click Edit. The Edit Parameter dialog box opens. 3.
Integrating Business Applications with Crystal Reports 2. Connect to SAP as outlined in Using the Log On Server command. Select SAP Operational Data Store in the Data Explorer. 3. Click Options.
Integrating Business Applications with Crystal Reports The "Options" dialog box appears with only the Database tab displayed. Tip: If you chose to use the report wizard, right-click SAP Operational Data Stores and click Options. 4. In the Data Explorer area, select the options for the types of data that you want to make available for the report. In this case, ensure that Stored Procedures is selected. 5. Use the Table name LIKE and Owner LIKE fields to select only a subset of the available data types.
Integrating Business Applications with Crystal Reports Note: The % and _ wildcards correspond respectively to the asterisk (*) and question mark (?) wildcards used in Windows. The % and _ wildcards correspond respectively to the asterisk (*) and plus sign (+) wildcards in SAP. • Clear both fields and click OK to proceed without filtering. 6. In the Tables and Fields area, click Show Both. 7. When you have specified the ODS that you want to see, click OK.
Integrating Business Applications with Crystal Reports 10. In the Available Data Sources area, double-click the ODS that you want to use as a data source for the report. The ODS moves to the Selected Tables area. 11. Click OK. You can now add fields and continue to design your report. 24.1.3.1.3 Reporting off InfoSets and SAP Queries This section describes the combined InfoSet/ABAP Query driver and shows how to select an InfoSet or an SAP query as a data source for a Crystal report.
Integrating Business Applications with Crystal Reports Reports that are based on SAP queries and InfoSets behave similarly to reports that are based on stored procedures in mainstream databases. You can insert subreports that are based on different queries or InfoSets into the main report. Note: • • If you want to join two or more InfoSets or SAP queries, you should consider creating a brand new InfoSet or SAP query instead.
Integrating Business Applications with Crystal Reports 4. In the Data Explorer area, select the check boxes for the types of data that you want to make available for the report. 5. Use the Table name LIKE field to select only a subset of the available data types. Use any of the following techniques when filtering: • Type full or partial names of tables.
Integrating Business Applications with Crystal Reports The Data Explorer displays the selected queries or InfoSets, along with the descriptions defined by the SAP administrator. 8. Click Close. 9. Open a new, blank report. The Database Expert appears. Tip: You can also add InfoSets or SAP queries to existing reports. To do so, open the desired report and, on the Database menu, select Database Expert. 10.
Integrating Business Applications with Crystal Reports Tip: For additional information about parameters and general report design topics, see the Crystal Reports Online Help. 24.1.3.2 Reporting off other SAP data sources In addition to the previously mentioned drivers, Crystal Reports provides the Open SQL driver, which allows you to report off additional SAP data sources from within Crystal Reports. 24.1.3.2.
Integrating Business Applications with Crystal Reports Objects supported by the Open SQL driver The Open SQL driver provides access to several low-level objects within SAP: transparent tables, pool and cluster tables, views, and ABAP data clusters and functions. Transparent tables This type of database object is similar to the traditional database table. Transparent tables contain the majority of the SAP application data from which you can derive Business Intelligence.
Integrating Business Applications with Crystal Reports 4. In the Create New Connection folder, expand SAP Table, Cluster, or Function. The "SAP System Logon" dialog box appears. 5. Log on to an SAP system, provide your user logon credentials, and specify extended logon parameters as prompted. Then, click Finish. You return to the "Database Expert" dialog box. 6. Right-click the connection you created and select Options. The "Options" dialog box appears, with only the Database tab displayed. 7.
Integrating Business Applications with Crystal Reports Linking tables and views Reports that are based on SAP tables (transparent, pool, and cluster tables, as well as views) behave similarly to reports that are based on mainstream databases. If you include more than one table or view in a report, you must describe the relationship between the tables by using the Links tab in the Database Expert in Crystal Reports.
Integrating Business Applications with Crystal Reports SQL driver provides the ability to call ABAP functions that have defined return types and to display their results. ABAP functions are also useful in situations where complex pre-processing of the data must occur before viewing, or in circumstances where you may want to further optimize the performance of a query. How it works Using the steps listed in Selecting tables, views, functions, and clusters, select a function.
Integrating Business Applications with Crystal Reports Field Type Name String Department String Years_of_service Integer Age Integer DateField_In Date DateField_Out Date When working with the Name, Department, and DateField_In fields, you must either link them to another table, or specify them in a record selection formula. For example, if table "T" contains departments that are over budget, you can link {T.Department} to {F.Department} and have a record selection formula that specifies {F.
Integrating Business Applications with Crystal Reports Field Type ContactInfo.Phone String ContactInfo.email String As before, you must either link the street and zip code fields to or from another table, or define them in a record selection formula.
Integrating Business Applications with Crystal Reports Name Gender Positions.Title Positions.
Integrating Business Applications with Crystal Reports Name Gender Positions.Ti tle Positions. Options. Options. StartDate GrantDate Number Tom M VP, Mail 7/12/98 5/6/97 15300 Reporting off ABAP data clusters Reporting off ABAP data clusters is an advanced topic that requires an understanding of the ABAP programming language.
Integrating Business Applications with Crystal Reports In order to overcome these issues, the individual components of data clusters must be mapped as tables. You can do this by creating a dictionary of the clusters on a particular SAP system. Mapping ABAP data clusters The Cluster Definition tool (transaction ZCDD) enables you to create a dictionary of the data clusters on your SAP system.
Integrating Business Applications with Crystal Reports Locating the cluster area's key fields This section guides you through the process of locating the cluster area's key fields based on the new cluster entry you created in the last section, Creating a new cluster entry in the dictionary. There are several ways to find this information within SAP. This example uses the Object Navigator to locate the key information for the B2 cluster area. To locate the cluster area's key fields 1.
Integrating Business Applications with Crystal Reports You now need to add the key fields to your new dictionary entry in the Cluster Definition tool. To add these key fields to the dictionary entry, go to next section, Adding the key fields to the dictionary entry. Adding the key fields to the dictionary entry This section guides you through the process of adding the key fields to the dictionary entry based on the key fields that you noted in the last section, Locating the cluster area's key fields.
Integrating Business Applications with Crystal Reports field's data type. In this example, the key field is defined by reference, so you can ignore the ABAP type and Length fields. 9. Click Save; then click Back to return to the View Cluster Record screen. 10. Repeat steps 4 to 9 for each field in the key. In the Key Number field, be sure to increment the sequence number by one every time you define a new key field for this cluster area.
Integrating Business Applications with Crystal Reports 5. In the ABAP code, look for the heading that denotes the beginning of the table, and note the structure that is included in the following line. For this example, look under the heading DATA: BEGIN OF SALDO OCCURS 50 for the line INCLUDE STRUCTURE PC2B5 (highlighted in the image above). This shows that the PC2B5 structure defines the fields of the SALDO table.
Integrating Business Applications with Crystal Reports Note: In this example, you need not complete the remaining fields (Reference Field, ABAP Type, and Length). Use these fields to add specific information if the structure of the table is not defined in the data dictionary. 9. Click Save; then click Back to return to the "View Cluster Record" screen. Now that you have entered all of the definitions, you have finished creating your new dictionary entry in the Cluster Definition tool.
Integrating Business Applications with Crystal Reports Migrating your existing security model To continue to use the data access restrictions that you defined using a previous version of SAP BusinessObjects XI Integration (using the ZRLS transaction), you can import these restrictions into client-dependent form using /CRYSTAL/RLS. Import these data restrictions before using /CRYSTAL/RLS to create or modify additional restrictions.
Integrating Business Applications with Crystal Reports 2. You now have two options: • If you want to enable the Global Lock, select the Allow access only to the tables specified above option. • If you want to disable the Global Lock, ensure that the Allow access only to the tables specified above option is not selected.
Integrating Business Applications with Crystal Reports Customizing authorizations for specific tables Whether you have enabled or disabled the Global Lock feature, you may need to customize the data access authorizations for one or more SAP tables. If the Global Lock is enabled, you will need to allow all or some users to access particular tables. (Otherwise, users will be unable to design or refresh reports against the SAP system.
Integrating Business Applications with Crystal Reports You will now associate your new authorization object with an SAP table. 3. In the Table name field, type the name of the table whose security definition you will customize. For this example, type VBAK. 4. Click Create.
Integrating Business Applications with Crystal Reports 5. Click Create. The "Authorization Object entry" dialog box appears. 6. Click Reference to an authorization object. The "Authorization Object entry" screen appears.
Integrating Business Applications with Crystal Reports 7. In the Authorization object name field, type the name of the authorization object that you created in step 1. For this example, type ZTABCHK. 8. Click Create. The "Authorization field values" screen appears.
Integrating Business Applications with Crystal Reports 9. In the Field value list, type the name of the table whose security definition you want to customize. For this example, type VBAK. 10. Click Save. You have now associated your customized authorization object with a specific SAP table. 11. Exit the Security Definition Editor. 12. Following your usual procedure, incorporate the new authorization object into your configuration of user profiles or roles.
Integrating Business Applications with Crystal Reports Note: Regardless of the method you use, ensure that your new authorization holds the name of the correct database table in its TABLE field value. In this example, the VBAK table must be specified. Customizing authorizations for specific functions When the Global Lock feature is disabled, report designers essentially have the freedom to call any function on the SAP system. To restrict the functions available to report off, enable the Global Lock.
Integrating Business Applications with Crystal Reports Essentially, by associating an authorization object with a field within an SAP table, you define that table as an exception to the Global Lock settings that you have made within the Security Definition Editor. In other words, once you've associated an authorization object with a field, the parent table is secured from all users—regardless of your Global Lock settings.
Integrating Business Applications with Crystal Reports The "Authorization field values" screen appears. 10. In the Field value list, type the equals sign (=) and the name of the field that you want to secure. For this example, you would type =BUKRS. Tip: To view a list of available fields, right-click the Field value list and, on the shortcut menu, click Possible entries. Double-click the desired field (in this case, BUKRS) to insert it along with the required equals sign (=). 11. Click Save. 12.
Integrating Business Applications with Crystal Reports Note: You can now use wildcards to specify the table name. This is an efficient way to apply an authorization to multiple tables with similar names simultaneously. Use * to specify zero or more characters and + to specify one character. 3. Click Create. The "Authorization entries" screen appears. This screen lists the authorization entries (if any) that are currently applied to the table. 4. Click Custom exit.
Integrating Business Applications with Crystal Reports 24.1.3.3 Reporting with SAP BW 24.1.3.3.1 Designing and Translating Reports This section introduces you to the Report Wizards in Crystal Reports and provides reporting details that are relevant within SAP environments. The section also shows how to prepare reports for translation and how to conditionally modify the report's design based on the user's logon language.
Integrating Business Applications with Crystal Reports 3. Ensure that Prepare reports for translation on server is selected. This option changes your default settings, so reports are always prepared for translation on the server. If you prefer to select this option for individual reports, clear the Suppress save dialog option. You are then prompted with a Prepare this report for translation option every time you save a report to BW. 4. Click OK. 5. On the SAP menu, click Save Report and proceed as usual.
Integrating Business Applications with Crystal Reports The "Format Editor" dialog box appears. 4. Click the Font tab. 5. Click the Formula button that corresponds to the Size field. The Formula Workshop opens a new formula and names it Font Size. 6.
Integrating Business Applications with Crystal Reports Before importing your content, consider the following: • BW treats Crystal reports (.rpt files) as native objects. If the Crystal reports are stored in the repository of your development BW system, you can transport the BW content and then batch publish the reports to SAP BusinessObjects Enterprise. (For details about transporting content between BW systems, see the SAP Library.
Integrating Business Applications with Crystal Reports 4. Provide the information in the following table. Field Action Host Type the name of the server that hosts your Oracle EBS system. Port Type the port number that the server uses for the SML listener Environ ment Type the name of the environment that you want to access in the system. User Type your user name. Password Type your password. Role Type the name of the role that you want to access in the system. 5. Click Finish.
Integrating Business Applications with Crystal Reports 24.3.1.1 Creating a connection in SAP Crystal Reports Once you've installed the Integration product on a machine running the SAP Crystal Reports designer, you must create a connection to Siebel for your report. There are two ways to create a connection in SAP Crystal Reports: use one of the Report Wizards, or select Log On Server from the File or Database menu. Both methods are described below. 24.3.1.1.1 Using the Report Wizards 1.
Integrating Business Applications with Crystal Reports Tip: Once you have configured the Siebel connection, you can store the connection in the Favorites folder. 24.3.1.1.2 Using the Log On Server command If you prefer not to use the Report Wizards, connect to Siebel using the Log On Server command. 1. Launch SAP Crystal Reports. 2. On the File menu (or Database menu, if a report is already open), click Log On or Off Server. 3. In the Data Explorer, expand Create New Connection. 4.
Integrating Business Applications with Crystal Reports • Local • Local (Active Dedicated Session) 24.3.2.1 Connection names When you open a connection to a data source, Crystal Reports automatically assigns a name to the connection in the Connection Name field of the Siebel eBusiness Applications Connection Information dialog box. By default, the first connection is named SIEBEL1, and additional connections opened in the same session are named SIEBEL2, SIEBEL3, and so on.
Integrating Business Applications with Crystal Reports 24.3.2.3 Local The Local connection type connects to the Siebel Mobile/Dedicated Web Client and accesses data from any of the available data sources, such as Local database, Server database, and Sample database. To use this connection type, the complete Siebel Mobile/Dedicated Web Client must be installed on the same machine as Crystal Reports.
Integrating Business Applications with Crystal Reports 2. From the shortcut menu, click Properties. The Siebel Properties dialog box appears. On the Shortcut tab, the Target field contains the filename and path to the configuration file. Local (Active Dedicated Session) The Local (Active Dedicated Session) connection type is a refinement of the Local connection type, and requires the complete Siebel Mobile/Dedicated Web Client.
Integrating Business Applications with Crystal Reports 24.3.2.4.1 Apply Current Query Siebel allows a user to filter information displayed in any view by applying a query. If Apply Current Query is selected when using the Server connection type, the query applied in Siebel is applied to the Crystal report when the report is published to SAP BusinessObjects Enterprise.
Integrating Business Applications with Crystal Reports 24.3.3 Selecting business components Once a connection is completed, Crystal Reports displays the list of business objects and business components available. SAP BusinessObjects Enterprise and business components are displayed in a tree structure, with business components shown under the business object that they are a part of. This is formatted as: business object.
Integrating Business Applications with Crystal Reports 24.3.6 Security Crystal Reports supports Siebel's security to ensure that sensitive information can be managed. Every time users run reports against Siebel data, they must log on with valid Siebel user credentials. This ensures that users are able to access only the information that they are permitted to by their Siebel credentials.
Integrating Business Applications with Crystal Reports Siebel provides a mechanism that formats information for the user based on preferences defined within the Siebel system. For example, currency fields are formatted to show the correct currency symbol, numbers and dates are adjusted to the display style of the user's nation, and textual information may also be modified. Although much of Siebel's formatting can be replicated inside Crystal Reports, doing so can be time consuming.
Integrating Business Applications with Crystal Reports 24.3.10 Multi-value columns There are certain columns within the tables that are displayed through the Siebel driver that represent multiple values. Crystal Reports will normally display the primary value for these fields. If no primary ID field is defined, Crystal Reports will display one of the values, but this value is not predictable. By default, Crystal Reports tries to improve performance by having any sort performed on the server.
Integrating Business Applications with Crystal Reports Siebel Type Crystal Reports Type DTYPE_DATETIME Date-time DTYPE_ID String DTYPE_PHONE String DTYPE_TEXT String DTYPE_NOTE Memo DTYPE_UTCDATETIME Date-time Note: DTYPE_PHONE in Siebel is a number. However, for the value to be meaningful, it is necessary to apply Siebel formatting to the field.
Integrating Business Applications with Crystal Reports 24.4.2 To access data in SAP Crystal Reports 1. Start SAP Crystal Reports. 2. On the Start Page, click Blank Report. 3. In the Database Expert, expand Create New Connection. 4. Double-click the data source type that corresponds to your installation: • If you installed the integration product on a PeopleSoft Enterprise (PeopleTools 8.46-8.
Integrating Business Applications with Crystal Reports • Field Action Password Type your password. If you installed the integration on a PeopleTools 8.50 environment, click PeopleSoft Query, and provide the following information: Field Action Server Type the appropriate URL to connect to the Query Access Services through the PeopleSoft Integration Gateway. (For example, type http://webserver/PSIGW/PeopleSoftSer viceListeningConnector.) User Type your user name. Password Type your password. 5.
Integrating Business Applications with Crystal Reports 24.5.1 Overview If you installed the Data Connectivity feature of the SAP BusinessObjects Enterpise XI, you can work with JD Edwards EnterpriseOne data in SAP Crystal Reports. 24.5.2 To access data in SAP Crystal Reports 1. Start SAP Crystal Reports. 2. On the Start Page, click Blank Report. 3. In the Database Expert, expand Create New Connection. 4. Provide the information in the following table.
Accessing Data Sources Accessing Data Sources 25.1 Introduction Crystal Reports can access data stored in almost any common database format, as well as many uncommon formats. This section discusses the many different types of data that Crystal Reports can access, and explains the data access layers involved in connecting to the data. If you are not sure what Database Management System (DBMS) your company uses, contact your IT manager or your network administrator.
Accessing Data Sources • Direct access database files • ODBC data sources • OLE DB • Business Views • Crystal SQL Designer files • Crystal Dictionary files Each type of data must be accessed using a specific set of Dynamic Link Libraries (DLLs) and other data access-related files. Once you understand the process the program uses to access each type of data, you will have a better understanding of the report creation process and the elements used to create powerful reports with your data.
Accessing Data Sources When you access a database directly through Crystal Reports, only that database type can be used by the report. You cannot switch to a different type of database or table without creating a new report. For example, if you design a report based on Pervasive data, you cannot change the tables accessed by the report to Access data. Crystal Reports communicates with Pervasive data using Pervasive-specific syntax, a syntax that is not compatible with Access data.
Accessing Data Sources 25.2.3.3 Database The database file consists of one or more tables. Different DBMS applications store database information differently. For example, dBASE stores each database table as a separate file. Access, on the other hand, can store several tables, along with queries, macros, and other database elements, all in a single file. When Crystal Reports accesses a database file directly, it automatically retrieves information about all of the tables and fields in that file.
Accessing Data Sources 25.2.4.2 dBASE, FoxPro, Clipper Crystal Reports has been designed to open dBASE data simply and directly through the xBase engine (inside crdb_p2bxbse.dll). FoxPro and Clipper are dBASE compatible database formats, and Crystal Reports uses the same DLL to access files created by any of these three DBMS applications. Note: The crdb_p2bxbse.dll translation layer supports FoxPro files up through version 2.6. See Visual FoxPro for versions after 2.6. The file crdb_p2bxbse.
Accessing Data Sources DAO uses Microsoft's Object Linking and Embedding (OLE) technology to provide access to Access data through an object-oriented approach. In addition to Access database tables, Crystal Reports can open and use Access queries through DAO. If you are not familiar with Access queries, refer to your Access documentation. Search also for the topic called "Opening Access queries through DAO" in the Crystal Reports Online Help.
Accessing Data Sources • When you open a Pervasive database, Crystal Reports opens the entire database and loads information about all the tables in the database. To do this, Crystal Reports must reserve a large section of your computer's memory, called a buffer, in advance. Computer memory restrictions limit this buffer to 65,536 bytes (64K). This restricts the size of your Pervasive database to about 80 tables, depending on the number of fields in each table.
Accessing Data Sources • • Exchange Folder Contents • Mail messages • Exchange Form applications • Properties of OLE documents Exchange Administrator • Properties of Exchange mailboxes on the Exchange Server • Properties of public folders on the Exchange Server • Replica list of public folders • ACL (Access Control List) of public folders You can use each Exchange data source like a database table and you can link each Exchange data source to other data sources.
Accessing Data Sources How Crystal Reports treats Exchange data Crystal Reports treats Exchange Administrator data (Public Folder Admin, Public Folder ACL, Public Folder Replica, and Mailbox Admin server types) as a physical database server. To access this data, you must log on using a SQL/ODBC server and select the appropriate tables from the Choose SQL Table dialog box. When you log on to any of the Exchange Administrator servers, you must select a profile (or profiles).
Accessing Data Sources 25.2.4.9 Oracle Crystal Reports supports direct access to Oracle SQL databases. This accessibility is provided by the crdb_oracle.dll translation file. This file communicate with the Oracle database driver, which works directly with Oracle databases and clients, retrieving the data you need for your report.
Accessing Data Sources Note: The Lotus Domino executable and data directories are added to the PATH. 25.2.4.12 Informix Online Server The Informix Online Server provides client/server capabilities along with SQL compliance. Crystal Reports opens SQL data created by Informix through the crdb_p2sifmx.dll, installed with Crystal Reports. This translation file works with the Informix database drivers to read Informix data.
Accessing Data Sources File Description crdb_fielddef.dll For Microsoft ActiveX Data Objects crdb_ado.dll For Microsoft ActiveX Data Objects crdb_cdo.dll For Crystal Data Object cdo32.dll Crystal Data Object COM DLL crdb_dao.dll For Microsoft Data Access Objects crdb_odbc.dll For ODBC These translation files works with a different set of drivers for each data source. 25.2.4.
Accessing Data Sources Crystal Reports provides the translation files crdb_p2sevta.dll (archived log) and crdb_p2sevtc (current log) for working with NT Event Log data. These files communicate with the Event Logging API in Advapi32.dll, a part of the Windows NT operating system. 25.2.4.17 Microsoft IIS/Proxy log file If you use Microsoft Internet Information Server (MS IIS), or Microsoft Proxy, you can use Crystal Reports to report on the log files.
Accessing Data Sources There are hundreds of Database Management Systems (DBMS) available for personal computers, and thousands of applications that access DBMS data. Normally, a company that designs an application that accesses data, such as Crystal Reports, must develop a means for the application to communicate with each type of data that a customer might want to use. Crystal Reports does this with the databases that it can access directly.
Accessing Data Sources translation (ODBC data source) layer. The data source must analyze the request and translate it again into a format that can be understood by the DBMS. This complex process can fail at any of several possible levels. In addition, ODBC data sources must be correctly configured and set up in the Odbc.ini and Odbcinst.ini files before they can be used.
Accessing Data Sources 25.3.3.2 ODBC translation layer Crystal Reports uses the Dynamic Link Library crdb_odbc.dll to communicate with ODBC. This file is unique to Crystal Reports environment and provides your report with access to any ODBC data source. This is the driver that actually passes data to and from ODBC. 25.3.3.3 ODBC layer ODBC is a set of several DLLs and INI files built into the Windows environment that act as a gateway through which database requests and data can pass.
Accessing Data Sources Once an ODBC driver is registered, you need to establish an ODBC data source using that driver. The ODBC data source is the object that you connect to when accessing data from Crystal Reports through ODBC. Data sources are recorded in the Odbc.ini file. The data source keeps track of the DBMS translation files (ODBC drivers) and, sometimes, the database itself. An ODBC data source can specify just a database format, such as Oracle, Sybase SQL, or MS SQL Server.
Accessing Data Sources 1. On the Start menu, select Settings, select Control Panel, then double-click Add or Remove Programs. 2. Select Crystal Reports and click Change. 3. In the Crystal Reports Setup dialog box, click Add/Remove. 4. Expand the Data Access folder and select the appropriate data access option. For example, if you have installed the Informix database client, select Informix from the Data Access folder. 5. Click the selected data access option and choose Entire feature will be unavailable.
Accessing Data Sources 25.3.6.2 Excel You can convert Microsoft Excel spreadsheets into databases that can be read by Crystal Reports through ODBC. In Excel 4.0 and earlier, use the Set Database command on the Data menu. In Excel 5.0 and later, use Define on the Name submenu of the Insert menu. Once converted, spreadsheet rows become records, and spreadsheet columns become fields. (For more information on converting your spreadsheets to database format, refer to your Excel documentation).
Accessing Data Sources • Oracle • Sybase • Microsoft SQL Server • DB2 Crystal Reports still communicates with ODBC through the crdb_odbc.dll translation file, but the DataDirect drivers provide ODBC with easy access to the actual databases. Note: If you are using an SQL database not accessed by the DataDirect drivers, refer to your DBMS documentation for more information about the required ODBC drivers. Do not confuse SQL databases with the SQL language.
Accessing Data Sources A Lotus Domino database can be read by Crystal Reports through ODBC. For Windows, the Lotus Domino DBMS translation layer consists of three files: • Nsql32.dll • Nsqlv32.dll • Nsqlc32.dll These files use the drivers installed by the Lotus Notes client to work with the Lotus Domino database. The client component of Lotus Domino must be installed on the local machine. Note: The Domino Server can be substituted for the Lotus Notes client in the database layer. 25.3.6.
Accessing Data Sources 25.4.1 JNDI The Java Naming and Directory Interface (JNDI) provides an interface that you can use to seamlessly connect to diverse naming and directory services within your company. Most importantly for reporting purposes, JNDI contains a repository for holding JDBC connection strings; when you provide these predefined connection strings through JNDI, your users will not be called on to provide connection URLs and class names when connecting to a data source.
Accessing Data Sources Crystal Reports uses the Dynamic Link Library crdb_jdbc.dll to communicate with JDBC. This file is unique to Crystal Reports environment and provides your report with access to any JDBC data source. This is the driver that actually passes data to and from JDBC. 25.4.2.3 JDBC layer JDBC acts as a gateway through which database requests and data can pass. Note: For more information on JDBC and the JDBC files, see the Sun Microsystems web site.
Accessing Data Sources crdb_jdbc.dll translation file, which has been designed to work with any fully JDBC-compliant driver. Crystal Reports officially supports third-party JDBC-compliant database drivers provided with: • IBM DB2 • Oracle • Microsoft SQL Server • BEA LiquidData for WebLogic Note: Check the Release Notes in the Crystal Reports product distribution for specific version requirements and for updates to this list; more database drivers may be added as testing progresses. 25.4.
Accessing Data Sources You can create a Crystal report from a Business Objects universe. Crystal Reports lets you select a universe and design a query from it using a tool called the Business Objects Query Panel. This query forms the basis of a SQL statement that is saved as a Crystal SQL Command object. For more information about SQL Command objects, search for the topic called "Defining an SQL Command" in the Crystal Reports Online Help.
Accessing Data Sources For detailed information about Business Views and how to create them, see the Business Views Administrator's Guide. Note: • • When you save a report that is based on a Business View, the Save As dialog box contains an option called "Disconnect view security." Selecting this option disconnects the report from the Central Management Server (CMS) so that users are not prompted for logon when they open the report.
Accessing Data Sources Note: The file crdb_query.dll must be installed before you can use query files in your reports. 25.8 Crystal Dictionary files A dictionary (.dc5) file is a structured and simplified view of organizational data that you can create for any of the individuals in your organization that are using Crystal Reports. With a dictionary, end users only see the subset of tables and fields they need. A dictionary is an optional source of data for Crystal Reports.
Accessing Data Sources 660 2011-05-16
Report Processing Model Report Processing Model 26.1 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 section. 26.1.1 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 • Record retrieval. Where possible record selection and sorting are pushed down to the database in this step. • Evaluation of recurring formulas. These formulas are those that contain database fields but do not contain references to subtotals or summary information. This evaluation time is known as "WhileReadingRecords." Formulas that contain references to subtotals or summary information are processed in the second pass. • Application of the record selection locally.
Report Processing Model These are formulas that contain references to subtotals or summary information, also known as "PrintTime" formulas. This evaluation time is known as "WhilePrinting Records." • Cross-Tabs, charts, and maps. Cross-Tabs, charts, and maps that include running totals and/or PrintTime formulas, and charts that are based on Cross-Tabs are generated in Pass 2. • OLAP grids. • Subreports. • Generation of Pages on Demand.
Report Processing Model 664 2011-05-16
Crystal Reports Error Messages Crystal Reports Error Messages A.1 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. A.2 Drive:\test.rpt This document could not be opened.
Crystal Reports Error Messages A.3 Failed to load database connector Reason The database connector is not available on the machine that is processing the report. The database client software required by the database connector is not available on the machine that is processing the report. 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.
Crystal Reports Error Messages A.5 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. For example, a field/table/procedure required by the report is missing, or the queries contain syntax errors.
Crystal Reports Error Messages 668 2011-05-16
Creating Accessible Reports Creating Accessible Reports 27.1 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 Many accessibility guidelines result in improved usability. An accessible report must provide logical and consistent navigation. Its content must be clearly written and easy to understand. • Accessible reports are more compatible with a variety of technologies, new and old. 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.
Creating Accessible Reports step further, the United States government introduced legislation in the form of Section 508 of the Rehabilitation Act, which ensures the right to accessible government web content. Common to all guidelines is a focus on providing web content that is useful for all people, regardless of disability or impairment. For reports, accessible design is focused on the same key concepts: • Content must be easy to understand and navigate.
Creating Accessible Reports To begin improving the accessibility of your Crystal reports, start with accessibility guidelines that are quick and easy to implement. A small change in your design conventions or company template may have a significant impact on accessibility. Simple navigation and clearly-written content are critical for accessibility, but they are easy to implement and useful for all report users.
Creating Accessible Reports left to right and top to bottom, screen readers and 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 People who use assistive technologies are accustomed to text-only substitutes and, therefore, will respond well to the text-only alternatives you provide. There are a number of ways you can use text to substantially improve your reports' accessibility: • Provide text equivalents for objects in reports. • Provide text alternatives for reports. • Ensure that text is written and formatted clearly. Text is a useful tool for creating accessible reports.
Creating Accessible Reports Note: If you cannot produce a complete text-only version of the report, you can still improve accessibility by providing a descriptive summary of key information or conclusions illustrated by the report. It is good practice to provide the text-only alternative on a subreport, linked from the top left corner of the main report, so the user has the opportunity to switch to the text-only version as soon as possible.
Creating Accessible Reports 27.2.2.4 Formatting text After you create text equivalents or alternatives for non-text objects, ensure that the text is clearly written and easy to read. Observe the following design guidelines: • Use a larger font. Although people with visual impairments can use the Zoom feature to increase the size of the report, they will not need to magnify the report as much if the font size is larger. For example, chart labels or legends can appear in a small font by default.
Creating Accessible Reports groups, provide a combination of audio and text. Multimedia presentations may provide audio information for people with visual impairments, as well as video information for people who are deaf or hard of hearing. Multimedia presentations are particularly effective for people with attention deficit disorder. However, people with certain mental health disabilities may be distracted by visual or audio objects.
Creating Accessible Reports For example, a text object may instruct users to "click the green button" to open a subreport. Users with limited vision cannot tell which button is green. The button should be recognizable by another defining characteristic besides its color. For example, you can change the button graphic to a shape that is not used elsewhere on the report, and instruct users to "click the green arrow button".
Creating Accessible Reports • Use navigation consistently. • Provide the opportunity to skip repetitive navigation links. 27.2.5 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.
Creating Accessible Reports 27.3.1 Accessibility and conditional formatting Using the accessibility parameter field in simple formulas, you can provide multiple formats for any object in a report. If a user chooses "Yes" when prompted by the parameter, the conditional formulas will ensure that the objects are modified with accessible formatting conventions. If a user chooses "No", then the report appears without accessible formatting, perhaps in the standard company template.
Creating Accessible Reports 27.3.2 Accessibility and suppressing sections Instead of formatting individual objects conditionally, you can create separate sections for accessible versions of the report content, then use the accessibility parameter field to conditionally suppress sections. The accessible and non-accessible sections can be suppressed or shown, based on the parameter value the user selects.
Creating Accessible Reports Accessible report design may become too cumbersome using conditionally formatted objects and suppressed sections. Two situations in particular may be problematic: • To make the report accessible, you may need to change the overall organization of the report sections, or you may need to provide different objects. • If the report contains a large number of objects or sections, it may take too much time to create conditional formulas for all of them.
Creating Accessible Reports You can make a large table easier to understand and navigate by adding text objects that provide information about each value in the table. Include whatever information is necessary to establish the meaning and context of the value displayed. When appropriate, include information that describes column headings or neighboring fields.
Creating Accessible Reports Providing extra information for each value can make a data table appear cluttered for people without vision impairments, so you may want to hide the extra text objects by changing the font color to the same color as the background. The extra text is invisible, but is still detected and read by screen readers. 27.4.1.2 Labeling data tables conditionally Although adding text objects is relatively easy to implement, it does not address all accessibility concerns.
Creating Accessible Reports You can use the parameter field to suppress the text objects conditionally. While it has the same effect as changing the font color to the background color, conditionally-suppressed text also allows you to use the parameter field to specify other formatting options such as font size and style.
Creating Accessible Reports The following report uses formulas placed in the Details section that combine the database fields and the extra text. When the user chooses Yes for the ?Access parameter field, each formula builds a string that includes the description and the value. This report uses the following formulas: @Employee ID If {?Access}="Yes" then "Employee ID " + ToText({Employee.Employee ID},0) + ". " else ToText({Employee.
Creating Accessible Reports 27.4.2 Other data table design considerations In addition to labeling data values with text objects, other report design techniques can help you create data tables that are easier to understand and navigate. • Include an introductory paragraph that summarizes the content of the table. The summary should be brief: one or two sentences if possible. • Ensure that headings provide enough information to clearly identify the values that they label.
Creating Accessible Reports To display the table summary conditionally, the report designer divided the Page Header into two sections. The first page header is suppressed when the ?Access parameter field is set to No . The second page header is suppressed if the user chooses Yes . For details, see Accessibility and suppressing sections. 27.5 Accessibility and BusinessObjects Enterprise Designing accessible reports is only part of the solution.
Creating Accessible Reports menus for report commands. Depending on your users' needs, you may also want to reduce the number of reports displayed on each page. For viewing reports, choose the DHTML viewer as the default viewer in your preferences. Note: You must have your own account on the system in order to set preferences. 27.
Creating Accessible Reports Electronic forms can present difficulties for screen readers, and must be set up carefully. When you label a component in a form, ensure the label is clearly located next to the form component. For example, for a Search box, ensure that the "Search" title appears alongside the appropriate text box.
More Information More Information Information Resource Location SAP BusinessObjects product information http://www.sap.com Navigate to http://help.sap.com/businessobjects and on the "SAP BusinessObjects Overview" side panel click All Products. SAP Help Portal You can access the most up-to-date documentation covering all SAP BusinessObjects products and their deployment at the SAP Help Portal. You can download PDF versions or installable HTML libraries.
More Information Information Resource Location SAP BusinessObjects articles on the SAP Community Network https://www.sdn.sap.com/irj/boc/businessobjects-articles These articles were formerly known as technical papers. https://service.sap.com/notes Notes These notes were formerly known as Knowledge Base articles. Forums on the SAP Community Network https://www.sdn.sap.com/irj/scn/forums http://www.sap.
Index 3-D Riser chart 271 3-D Surface chart 271 assistive technology 669 attribute properties, conditional 255 A B ABAP data clusters and the Cluster Definition tool 596 and the Open SQL driver 588 described 589, 596 mapping as tables 596, 597 reporting off 596 selecting for reports 589 ABAP functions and the Open SQL driver 588 calculations and functions 591 described 589, 591 examples 592 selecting for reports 592 ABAP queries and parameters 584 and subreports 584 reporting off 559 absolute formatting
Index charts (continued) creating 274 on an OLAP cube 277 on Cross-Tab summaries 276 on subtotal fields 275 on summary fields 275 drilling down 273 modifying 282 legend text 283 with Chart Expert 278 with Chart Options commands 278 placing 273 types 271 3-D Riser 271 3-D Surface 271 area 271 bar 271 bar, zooming 281 bubble 271 doughnut 271 funnel 271 Gantt 271 gauge 271 histogram 271 line 271 line, zooming 281 numeric axis 271 pie 271 radar 271 stock 271 using underlay feature with 283 XY Scatter 271 zoomi
Index data (continued) grouping 41, 147 hierarchically 159 in intervals 156 on first letter of name 158 linking 75 live 111 minimizing transfer 107 placing on report 76 refreshing report 480 saved 111 selecting records for reports 84 sorting records within groups of 151 subtotaling grouped 171 summarizing grouped 166 data access 556 connecting with the Data Access Components 556 Data Access Components 556 connecting to SAP 556 Data Access Objects (DAO) 637 data access restrictions 602 data age indicator 68
Index designing reports (continued) multiple columns 216 organizing data 84 placing 273 charts 273 data 76 database fields on 76 formula fields 77 maps 288 parameter fields 78 running total fields 78 special fields 79 SQL expression fields 77 text objects 80 printing characteristics 55 record selection 84 resizing 31, 252 fields 31 sections to add white space 252 sections to delete white space 253 selecting multiple objects 232 spacing between text-based objects 225 splitting and resizing sections 194 supp
Index Flash objects (continued) inserting in reports 312 fonts setting fractional sizes 234 footers, creating after the page 258 form letters inserting a date 202 inserting address 202 printing conditional messages 205 salutation 203 using text objects 199 Format Painter, using 263 formats, changing field defaults 241 formatted reporting 561 formatting 207 absolute 239 accounting conventions 249 adding 198 blank lines conditionally 198 lines 243 shapes 248 and accessibility 676 applying Siebel 626 backgrou
Index groups (continued) selecting with Select Expert 153 sorting conditionally 150 records within 151 subtotaling 171 guidelines 226 designing with 226 inserting 227 positioning objects with 229 snapping objects to 227 viewing 226 H headers 176 creating standard group 176 custom group, creating 178 drilling-down 179 live group headers 176 suppressing 179 height, modifying for cells 333 hiding group headers 179 report objects 218 hierarchies creating in BW query 570 in BW queries 570 reporting off 571 wit
Index lists of values (continued) command objects as data source 474 long lists 475 null handling 475 overview 459 separate values and descriptions 473 sharing common lists 472 types 459 working with 471 live data 111 live group headers 43 creating 176 Local (Active Dedicated Session) connection type 623 Local connection type 622 locking down tables 603 Log On Server command 557, 620 logging on to SAP 557 logical databases 584 logon dialog box 557 logos, inserting 45 long lists of values 475 Lotus Domino 6
Index OLAP (continued) creating OLAP report 362 reporting 361 updating database location 368 OLAP cube 277 charting on 277 mapping on 292 OLAP data 376 filtering 376 sorting 374 OLAP grid 377 filtering 376 formatting 369 inserting calculations 377 sorting 374 OLAP Report Wizard 25 OLE 301 and the Picture command 305 dynamic menu commands 305 functionality 302 linked vs. embedded objects 308 OLE DB 653 OLE objects 303 copying and pasting 303 dynamic 307 inserting in reports 303 linked vs.
Index publishing to SAP BusinessObjects Enterprise. See Enterprise Folders 392 Publishing Wizard 616 Q queries 344 apply current 624 building 344 creating a combined query 344 defining data selection 344 design with hierarchy 570 editing 346 filters 348 object quick reference 345 prompts 348 running 344 viewing SQL 347 query filters. See filters 348 query prompts.
Index reports (continued) formatting (continued) with a template 207 formatting for web viewing 238 formula fields on 77 Group Tree, to display 69 grouping 147 data 147 how OLE objects are represented 304 inserting 28, 81 a title page 86 database fields 28 hyperlink fields 81 OLE objects 303 sections 192 summary information 87 title 35 layout 52 making read-only 240 manipulating data 54 merging related sections 193 migrating with BW content 616 moving sections 193 optimizing performance 107 organizing data
Index sections (continued) identifying 67 inserting 192 making read-only 240 merging two related 193 moving 193 multiple in report 196 Page Header 65 Report Header 65 resizing 195 resizing to delete white space 253 splitting 194 suppressing to delete white space 253 security 626 defining 602 global lock feature 603 migrating from client-independent model 603 preserving existing restrictions 603 row-level restrictions 610 table-level restrictions 603 Security Definition Editor description 602 SELECT clause
Index T Tab Separated Text (TTX), exporting to 380 table indexes, for performance 116 table-level security 603 tables 74, 591, 605, 682 adding 74 alias 520 customizing authorizations 605 defining security by group 610 deleting blank lines 197 indexed 506 link from 521 link processing order 531 link to 521 linked SQL 527 linking 521, 530, 591, 627 for performance 117 records 521 two 75 unindexed 502 locking down 603 selecting for reports 588 Visual Linking Expert 530 tabs differences between Preview and Des
Index Z 705 ZCDD, Cluster Definition tool 597 zooming features 281 2011-05-16
Index 706 2011-05-16
