Crystal Reports 2008 SP3 User's Guide Crystal Reports 2008 windows
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 Chapter 1 Introduction to Crystal Reports 2008 27 About Crystal Reports................................................................................28 About the Crystal Reports documentation.................................................29 Locate information quickly....................................................................29 Sample Reports....................................................................................
Contents Running a silent installation.......................................................................45 Setting custom banners.............................................................................51 Upgrading Crystal Reports components....................................................52 Chapter 4 Quick Start 53 Learning how to use Crystal Reports........................................................54 Sample data - Xtreme.mdb............................................................
Contents Determining printing area characteristics.............................................95 Developing a prototype on paper..............................................................97 To design a paper prototype.................................................................98 Chapter 6 Introduction to Reporting 99 Report creation options............................................................................100 Report Creation Wizards......................................................
Contents Adding summary information to the report.........................................135 Exploring reports and working with multiple reports................................136 The Report Explorer...........................................................................136 The Workbench..................................................................................138 The Dependency Checker..................................................................140 Opening and docking explorers............
Contents To delete an object from the repository..............................................159 Using Undo in the repository...................................................................159 Chapter 8 Designing Optimized Web Reports 161 Overview..................................................................................................162 Key strategies for optimizing web reports..........................................163 Scale with BusinessObjects Enterprise...............................
Contents Using SQL Expressions for groups, sorts, and totals.........................188 Using SQL Expressions for Case Logic.............................................188 Inserting summary and running total fields where possible...............189 Chapter 9 Record Selection 191 Selecting records.....................................................................................192 Options for selecting records..............................................................
Contents Group selection..................................................................................224 Grouping data in intervals..................................................................229 Grouping by the first letter of a company name.................................231 Grouping data hierarchically...............................................................233 Editing groups....................................................................................241 Summarizing grouped data.
Contents Chapter 12 Multiple Section Reports 273 About sections.........................................................................................274 Working with sections..............................................................................274 Inserting a section..............................................................................275 Deleting a section...............................................................................275 Moving a section...............................
Contents Making an object underlay a following section...................................303 Pre-printed forms................................................................................305 Multiple columns.................................................................................305 Hiding report sections.........................................................................306 Hiding report objects..........................................................................
Contents Repeating report objects on horizontal pages ...................................344 Using white space between rows ......................................................346 Working with conditional formatting.........................................................348 Conditional on or off properties..........................................................349 Conditional attribute properties..........................................................350 Changing fonts conditionally.................
Contents Working with charts.................................................................................381 Editing charts using the Chart Expert.................................................381 Editing charts using the Chart Options menu items...........................382 Editing charts using other menu items...............................................382 Using the zooming features with bar and line charts.........................384 Auto-arranging charts.......................................
Contents Hiding and showing the Map Navigator ............................................408 Formatting Maps................................................................................410 Using the underlay feature with maps................................................410 Chapter 16 OLE 413 OLE overview..........................................................................................414 OLE terminology.................................................................................
Contents Cross-Tab example..................................................................................431 Report of order data - no sorting/grouping.........................................432 Report of order data - grouped by region...........................................433 Report of order data - grouped by product.........................................434 Report of order data - grouped by region and product.......................434 Order data in a Cross-Tab object............................
Contents Embedded Summaries.......................................................................458 Chapter 18 Building Queries 461 Connecting to a universe.........................................................................462 To connect to a universe....................................................................462 Defining the data selection for a query....................................................463 To build a simple query...........................................................
Contents Creating an OLAP report.........................................................................487 To create an OLAP report...................................................................487 Specifying the data source.................................................................487 Defining the structure of the grid........................................................488 Setting sliced dimensions and specifying the number of grids...........490 Applying a predefined style................
Contents Faxing a report...................................................................................506 Exporting a report...............................................................................507 Working with Web folders...................................................................521 Working with Enterprise folders .........................................................522 Viewing reports........................................................................................
Contents Specifying formulas.................................................................................559 Working with the Formula Workshop.................................................560 Working with the Formula Editor........................................................566 Creating and modifying formulas.............................................................572 Creating a formula and inserting it into a report.................................
Contents Creating a parameter with a dynamic prompt..........................................607 To create a parameter with a dynamic prompt...................................607 Creating a parameter with a cascading list of values..............................609 To create a parameter with a cascading list of values........................610 The Parameter Panel...............................................................................612 Working with lists of values...................................
Contents Creating a saved-data record filter using parameter fields................633 To create a saved-data record filter using parameter fields...............634 To create a parameter appearing on the Parameter Panel................634 To incorporate the parameter into a saved data selection formula.....635 Adding dynamic grouping using parameter fields..............................636 Chapter 24 Subreports 639 What are subreports?......................................................................
Contents Relational database basics................................................................658 Aliases ...............................................................................................659 Locating files......................................................................................661 Indexed tables....................................................................................662 Linking tables.................................................................................
Contents For additional information........................................................................714 Chapter 26 Accessing Data Sources 715 Introduction..............................................................................................716 Six types of data.................................................................................716 Direct access database files ...................................................................717 Advantages.....................................
Contents Pre-pass 1..........................................................................................750 Pass 1................................................................................................750 Pre-pass 2..........................................................................................751 Pass 2................................................................................................751 Pass 3............................................................................
Contents Text objects and data table values.....................................................777 Other data table design considerations..............................................783 Accessibility and BusinessObjects Enterprise.........................................784 Setting accessible preferences for BusinessObjects Enterprise........785 Accessibility and customization...............................................................785 Resources......................................................
Contents 26 Crystal Reports 2008 SP3 User's Guide
Introduction to Crystal Reports 2008 1
1 Introduction to Crystal Reports 2008 About Crystal Reports About Crystal Reports Crystal Reports is designed to work with your database to help you analyze and interpret important information. Crystal Reports makes it easy to create simple reports, and, it also has the comprehensive tools you need to produce complex or specialized reports. Create any report you can imagine Crystal Reports is designed to produce the report you want from virtually any data source.
Introduction to Crystal Reports 2008 About the Crystal Reports documentation Whether it's the web master in IT, the promotion manager in marketing, the database administrator in finance or the CEO, Crystal Reports is a powerful tool designed to help everyone analyze and interpret the information that's important to them. About the Crystal Reports documentation The Crystal Reports documentation includes procedures for typical reporting tasks such as placing fields, formatting reports, and sorting records.
1 Introduction to Crystal Reports 2008 About the Crystal Reports documentation 30 Crystal Reports 2008 SP3 User's Guide
What's New in Crystal Reports 2008 SP3 2
2 What's New in Crystal Reports 2008 SP3 Introduction Introduction This section provides a high-level overview of the components, features, and benefits that are provided by the latest release of Crystal Reports. The following is an overview of the new features in Crystal Reports 2008 SP1.
What's New in Crystal Reports 2008 SP3 Introduction XML and Web Services Database Driver Web services created with SOAP 1.2 are now accessible to Crystal Reports XML and Web Services driver. This includes new architectures such as .NET 3.0 (Windows Communication Foundation). Also, the Crystal Reports XML Web Services driver recognizes a Web Service that returns an ADO.NET DataSet. Viewer Accessibility The Webform (DHTML) viewer is now accessible.
2 What's New in Crystal Reports 2008 SP3 Introduction HTML interpretation tag “” is now supported When you add a hyperlink to your report, you can now use the “ ” tag to display text instead of the hyperlink. Example: SAP.COM Note: Only the “href” linking property is supported; other HTML 4.01 linking properties are not supported.
Installing Crystal Reports 2008 SP3 3
3 Installing Crystal Reports 2008 SP3 Installation overview Installation overview The Crystal Reports Installation Wizard works with Microsoft Windows Installer to guide you through the installation process. The Installation Wizard automatically recognizes your computer's operating system and updates files as required. Note: Data-Direct ODBC driver installers are not included as part of the Crystal Reports installer. Instead, you must download and run these installers separately.
Installing Crystal Reports 2008 SP3 Installation requirements Installation requirements Minimum installation requirements Windows Vista and Vista SP1 Windows XP Professional SP2 Operating Systems Windows 2003 Server Windows 2008 server Computer/Processor 450 MHz or higher Pentium-compatible CPU Memory At least 128 megabytes (MB) of RAM. 256 MB recommended. 4 gigabytes (GB) maximum Disk space 1.0 gigabyte minimum. 1.
3 Installing Crystal Reports 2008 SP3 Installing Crystal Reports on a local machine registry entries and may update some system files that require Administrator rights. Close all currently running programs and stop as many services as possible when installing Crystal Reports. If you want to limit the features you install, see Customizing your installation. To install on a local machine 1. Unless Autoplay is enabled for your CD-ROM drive, run Setup.
Installing Crystal Reports 2008 SP3 Installing Crystal Reports on a local machine 11. Choose the type of installation that you want to perform: • Typical installs the most common application features. • Custom enables you to choose the features that you want installed, to specify where they will be installed, and to check the disk space required by each feature. For details, see Customizing your installation . 12.
3 Installing Crystal Reports 2008 SP3 Creating an installation point and installing from a network server service packs each time you open Crystal Reports; if you disable it, you cannot activate it later. 14. Click Next to begin copying files to your local drive. Creating an installation point and installing from a network server Installing Crystal Reports from a central network location involves two steps: 1.
Installing Crystal Reports 2008 SP3 Creating an installation point and installing from a network server Note: Restricting access to this folder lets you stay within your license limit. Installing Crystal Reports from a network If your network administrator has copied Crystal Reports to the network, make sure you have read privileges to that network before beginning this process.
3 Installing Crystal Reports 2008 SP3 Creating an installation point and installing from a network server Tip: You may need to contact your Administrator for the product activation keycode. 9. Click Next. 10. In the Choose Language Packs dialog box, select the languages that you want to install. 11. Click Next. The Select Installation Type dialog box appears. 12. Choose the type of installation that you want to perform: • Typical installs the most common application features.
Installing Crystal Reports 2008 SP3 Customizing your installation The default location for Crystal Reports is C:\Program Files\Business Objects\ 14. Click Next to begin copying files to your local drive. Customizing your installation Selecting the Custom installation option invokes the Select Features dialog box, which allows you to install specific features, to change the default location of various features, and to check the amount of disk space required by each feature.
3 Installing Crystal Reports 2008 SP3 Customizing your installation • A red X means that the feature or subfeature is either unavailable or will not be installed. Crystal Reports uses an "install on-demand" technology for some of its features. As a result, the very first time a particular feature is used after being installed, there may be an extra wait for the "install on-demand" to complete. This behavior will affect new installations only once and will not occur when features are restarted.
Installing Crystal Reports 2008 SP3 Running a silent installation Note: Subfeatures are listed below each feature. A subfeature can have a different type of installation than its parent feature. Running a silent installation A silent installation is one that you can run either from the command line or by use of a response file to automatically install Crystal Reports on any machine in your system, without the installation program prompting for information during the installation.
3 Installing Crystal Reports 2008 SP3 Running a silent installation • <%keycode%> Replace this variable with a valid product activation keycode. The example uses the most common parameters. You can choose any number of valid parameters, but it is good practice to keep the silent installation as simple as possible. The following table lists the most common parameters used in a command-line silent installation. To use a parameter, place it on the command line after the setup.exe command.
Installing Crystal Reports 2008 SP3 Running a silent installation Installation parameter Description Use this to specify the language version code for the install. • For English, enter EN. CLIENTLANGUAGE= • For French, enter FR. • For German, enter DE. • For Spanish, enter ES. • For Japanese enter JA. • For Simplified Chinese, enter zh_CN • For Traditional Chinese, enter zh_TW. • For Italian, enter IT. • For Korean, enter KO. • For Dutch, enter NL.
3 Installing Crystal Reports 2008 SP3 Running a silent installation 48 Installation parameter Description INSTALLDIR="filepath" Specifies the machine and directory where you want to create the Crystal Reports sub-directory, Crystal Reports and install Crystal Reports into. Replace filepath with the path where to create the installation subdirectory.
Installing Crystal Reports 2008 SP3 Running a silent installation Installation parameter Description Specifies a list of features, delimited by commas, that you want to install. For example, ADDLOCAL=ALL ADDLOCAL Note: When using any of the EXCLUDE parameters in this table, you must also use ADDLOCAL. For example, ADDLOCAL=ALL EXCLUDEJAVAFILES =1 Specifies whether the .NET developer components should be installed.
3 Installing Crystal Reports 2008 SP3 Running a silent installation installer then records the installation options you select as you install Crystal Reports, and saves the file to disk. The created INI file can be read by the installer to silently install Crystal Reports with the same options specified during the creation of the file. This is helpful for quickly producing a Silent Installation of Crystal Reports without having to figure out complicated command-line parameters.
Installing Crystal Reports 2008 SP3 Setting custom banners Installation parameter Description To be used with the -r parameter. Creates a silent install log file at the path specified by filepath. If this parameter is not set, the default location for the creation of the silent install log file is the %TEMP% folder. -l "filepath" This log captures all the placeholder DLL errors.
3 Installing Crystal Reports 2008 SP3 Upgrading Crystal Reports components Image filename Description The Crystal Reports splash screen that is shown after installation. splash.bmp This must be placed in the win32x86 folder. The images must be in Windows bitmap format. Upgrading Crystal Reports components If you have purchased an upgrade product activation keycode, you will find that the installation program does not remove your previous version of Crystal Reports.
Quick Start 4
4 Quick Start Learning how to use Crystal Reports Learning how to use Crystal Reports You can teach yourself how to use Crystal Reports by choosing from the methods available in this 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 Business Objects Technical Support web site: http://support.businessobjects.
Quick Start Report Creation Wizards alternative ways of designing a database, this design was selected to keep the tutorials and examples focused on reporting, not on data manipulation. 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.
4 Quick Start Quick start for new users Cross-Tab The Cross-Tab Report Creation Wizard guides you through the creation of a report in which your data is displayed as a cross-tab object. Two special screens (Cross-Tab and Grid Style) help you create and format the cross-tab itself. Mailing Label The Mailing Labels Report Creation Wizard lets you create a report that is formatted to print on any size mailing label.
Quick Start Quick start for new users You begin by learning the basic concepts: selecting a database, placing some fields on the report, and then selecting specific records to be included. You will then learn how to: • Insert and move database fields. • Add and format a title. • Display a report in the Preview tab so you can fine-tune your work. • Use the Select Expert to ensure the report includes only the data you need. • Move objects. • Group and sort data. • Insert pictures.
4 Quick Start Quick start for new users The New Reports area of the Start Page contains a number of wizards to guide you through the creation of specific types of reports. Since you will be learning reporting concepts here, you can skip the wizards and build your report from scratch. After you have completed this tutorial, you may want to build some reports using the wizards to decide which method of report construction you are most comfortable with. 2. In the New Reports area, click Blank Report.
Quick Start Quick start for new users • If you wish to see database and server properties after you have selected a database, right-click the database node in the Database Expert and select Properties from the shortcut menu. 2. Expand the Tables node of the Xtreme.mdb connection to see a list of tables. Because you are dealing only with customers in this tutorial, you will select the Customer table. 3. Select Customer and click the > arrow to add it to the Selected Tables list, and then click OK.
4 Quick Start Quick start for new users Note: Depending on how it appeared when you last used Crystal Reports, the Field Explorer dialog box might be docked or in floating mode. For more information about docking explorers, see Opening and docking explorers. Report sections The Design tab is divided into five sections: Report Header (RH), Page Header (PH), Details (D), Report Footer (RF), and Page Footer (PF).
Quick Start Quick start for new users This dialog box is set to remain on-screen until you close it. All the tables available for use are listed in this box. You will now start placing objects on the report by inserting the Customer Name field. 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.
4 Quick Start Quick start for new users If you move the field too far to the left, the Arrow cursor turns into a Stop cursor, indicating that you cannot drag the field that far. Keep in mind that you cannot place any objects outside the page margin.
Quick Start Quick start for new users • The font and style (Bold, Underline, and so on) used in displaying the characters or Xs indicate the font and style selected for the characters in the field. Later in this tutorial you will learn how to make changes to these properties. • The line spacing is adjusted to the point size selected for the characters in the field. Adding additional fields Next, you will insert two additional fields in the report.
4 Quick Start Quick start for new users • Position the cursor inside the object frame and click once. The handles appear, indicating the object is selected. • Move the cursor away from the object frame and click in an empty part of the window. The handles disappear. That's all it takes to select and deselect objects. Resizing fields To resize the field, follow these steps: 1. Click the Customer Name field in the Details section to select it. 2.
Quick Start Quick start for new users 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.
4 Quick Start Quick start for new users Combining database fields in a text object 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 Quick start for new users 12. Drag the field to the text object. 13. Move the cursor over the text object until the cursor becomes a Drag and Drop cursor. Move the cursor to the right of the comma and space you just typed, and release the mouse button. The field will be inserted to the right of the comma and space. 14. Click the Preview tab to look at the fields you just placed.
4 Quick Start Quick start for new users 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. Adding a title As you can see, the report looks incomplete without a title.
Quick Start Quick start for new users 5. Position the object frame in the upper left-hand corner of the Page Header (PH) section of the report and release the mouse button to place the object. 6. Click the Preview tab or Refresh to review your changes. The report title object now displays the title that you entered in the Title text box of the Document Properties dialog box. Formatting objects Now you can format the report title. This time, however, you will remain in the Preview tab to do the work.
4 Quick Start Quick start for new users Adding a field heading As you can see, the Contact Name field is the only field without a heading. In this section you will create a heading. To add a field heading 1. Click Insert Text Object on the Insert Tools toolbar. 2. Insert the text object in the Page Header section directly above the Contact Name field. 3. Click Underline on the Formatting toolbar, and then type Contact Name into the text object. 4. Click your cursor outside the text object.
Quick Start Quick start for new users 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. Click the Preview tab to view the report. The report should now look similar to the following: Congratulations! You have just created a basic listing report.
4 Quick Start Quick start for new users 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. To enter selection criteria 1. To begin, click the Design tab to return to design mode. 2. Click an empty area of the report to make sure all fields are deselected. 3. Click Select Expert on the Expert Tools toolbar.
Quick Start Quick start for new users 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.
4 Quick Start Quick start for new users 10. Save this version of the report without overwriting the original report by choosing Save As from the File menu and giving the new report the name USA.rpt. Congratulations! You have started formatting your report and have added selection criteria to it. More than that, you have learned how to manipulate your data. By now, you have a good idea of the powerful kinds of reports you can prepare.
Quick Start Quick start for new users Balancing field spacing Now that the Country field has been deleted, there is a large amount of white space between the City and Contact Name fields. You might be satisfied with the spacing as it stands, but it might be more readable if the columns were better balanced across the page. To balance field spacing 1. Return to the Design tab. Select the Contact Name field and its field heading by using the Ctrl-click combination. 2.
4 Quick Start Quick start for new users Grouping and sorting Data in reports can be grouped and sorted in a variety of ways. Sorting and grouping tools provide a great deal of flexibility for customizing reports. Grouping the report In many reports you need to break the data into groups in order to make it easier to read and to understand. Crystal Reports lets you do this easily. For this customer list, you will group the customers by region and then sort the customers alphabetically within each group.
Quick Start Quick start for new users 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). This is how the program shows that the report has been grouped. 5.
4 Quick Start Quick start for new users Note: For many reports, you will want to insert summaries, subtotals and grand totals. For example, when creating a sales report rather than a customer list, you would want to calculate the total sales amount for each region. See Sorting, Grouping, and Totaling. Understanding "live" group headers When a group is inserted, a group name field is automatically inserted in the Group Header section of the report. The group name field displays the current group's name.
Quick Start Quick start for new users 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.
4 Quick Start Quick start for new users Notice that the records within each group are in alphabetic order. Completing the report You have just one step left to complete the report. A company logo needs to be added to the first page of the report. Inserting a company logo In this section, you will place a company logo at the top of the first page of the report. 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.
Quick Start Quick start for new users 3. Position the object frame in the upper left-hand corner of the Report Header (RH) section of the report and click once to place it. Placing the logo in the Report Header (RH) section ensures that the logo is printed only on the first page of the report. Note: Although there does not appear to be enough room in the Report Header (RH) section when you place the graphic, the section will automatically expand to accommodate the picture. 4.
4 Quick Start Quick start for advanced users 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. Choosing a report type and data source 1. In Windows, click Start > Programs > BusinessObjects XI Release 3 > Crystal Reports, and then select Crystal Reports 2008. 2.
Quick Start Quick start for advanced users Working with report elements on the Design tab 1. Each of the default report areas contains a single section. To add additional sections, click Section Expert on the Expert Tools toolbar and use the Section Expert to add the desired sections. Once you have added sections to an area, you can move, merge, and delete them in the Section Expert. See Designing with guidelines . 2.
4 Quick Start Quick start for advanced users 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 Quick start for advanced users To speed processing time while building a report, you can preview the report using only a small subset of the available data. To do this, go to the File menu, point to Print and then click Preview Sample. See Preview tab. In either case, the program takes you to the Preview tab. You can fine-tune a report in the Preview tab while viewing the results as actual report data. You can also close the Preview tab and continue working on the report in the Design tab.
4 Quick Start Quick start for advanced users 3. Click New. The Formula Name dialog box appears. Enter a name for the formula and click OK. The Formula Workshop appears with the Formula Editor active. 4. Enter the formula in the Formula Editor. Enter fields, operators, and functions by selecting them from their respective lists or by typing them in. You can check the formula syntax by clicking Check. 5. When you are finished editing, click Save and close to return to the Field Explorer dialog box. 6.
Quick Start Quick start for advanced users 11. To add a map, click Insert Map on the Insert Tools toolbar. See Mapping. 12. To insert a spreadsheet, picture, or other OLE object that you can edit from within the Report Designer using the tools from the object's native application, choose OLE Object from the Insert menu. See OLE, and Working with static OLE objects. 13. To change the record sort order, click Record Sort Expert on the Expert Tools toolbar. The Record Sort Expert appears.
4 Quick Start Quick start for advanced users 88 Crystal Reports 2008 SP3 User's Guide
Report Design Concepts 5
5 Report Design Concepts Basic report design 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.
Report Design Concepts Deciding on the content of the report • The purpose of this report is to show sales activity for each item in inventory, and to suggest reorder quantities based on that activity. • The purpose of this report is to calculate bowling averages and handicaps for each member of the bowling league. Defining the purpose of the report before you start is a critical step in the overall process. Who is going to read the report? A single report is often used by many individuals.
5 Report Design Concepts Deciding on the content of the report Determining the layout of the report What is the report title going to be? Write out a working title for the report. You may decide to change it later, but at least you will have a title to use when creating the prototype report.
Report Design Concepts Deciding on the content of the report Are you familiar enough with the data to find the necessary information? When looking for a Customer Contact name, can the field be found in a database table? If not, your MIS professional, database administrator, or co-workers will have to help you become familiar with the data. What specific data should appear in the body of the report? The body should contain all the data needed to fulfill the statement of purpose you wrote for the report.
5 Report Design Concepts Deciding on the content of the report working with, before you start any calculations. For example, some functions require numeric data, while others work with only string fields. For more information on specific functions and operators, search for "Functions" and "Operators," or for the function or operator by name in the Crystal Reports Online Help.
Report Design Concepts Deciding on the content of the report bottom of the selected column. See Calculating a percentage and Selecting top or bottom N groups or percentages . What information should be flagged on the report? You may want to call attention to some data by flagging it on the report. For example, non-moving inventory items are often flagged on inventory reports so they can be given special attention.
5 Report Design Concepts Deciding on the content of the report How often do report objects print? The way objects print will determine how you design your report. This will help you decide where to place charts, Cross-Tabs, and formulas to get specific results. Report Header Objects placed in the Report Header area print once, at the beginning of the report. • Charts and Cross-Tabs placed in this area contain data for the entire report.
Report Design Concepts Developing a prototype on paper Details area Objects placed in the Details area print with each new record. • Charts or Cross-Tabs cannot be placed in this area. • Formulas placed in this area are evaluated once for each record. Group Footer Objects placed in the Group Footer area print at the end of each group. • Charts and Cross-Tabs placed in this area contain data just for the group.
5 Report Design Concepts Developing a prototype on paper learning and using the commands, rather than into trying to design and learn at the same time. To design a paper prototype 1. Get the same size paper you will be using for the finished report. 2. Position the title and other descriptive header information, using boxes or lines to represent report elements. 3. Position the footer information. 4. Review the page layout for balance. 5.
Introduction to Reporting 6
6 Introduction to Reporting Report creation options Report creation options Each time you create a new report, you have three options: • Use a Report Creation Wizard. • Use another report as a model. • Create a report from scratch. You will probably use each option at some time. Report Creation Wizards The Report Creation Wizards help create reports as quickly as possible and many new users and developers alike prefer to create the majority of their reports using them.
Introduction to Reporting Report creation options New report The Blank Report option is used to create a report from scratch. This is useful when you want the full flexibility and control of building a report from the ground up, or when a report type is different from the many report types available in the wizards. 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.
6 Introduction to Reporting Choosing data sources and database fields New report The Blank Report option is used to create a report from scratch. This is useful when you want the full flexibility and control of building a report from the ground up, or when a report type is different from the many report types available in the wizards.
Introduction to Reporting Choosing data sources and database fields Data tab Tree View The Data tab of the Database Expert shows a tree view of possible data sources you can select when creating a report. The tree—in the Available Data Sources list—is made up of folders for: • My Connections • Create New Connection The Create New Connection folder contains subfolders for many popular data sources.
6 Introduction to Reporting Choosing data sources and database fields Use this option to add a Table object that has been created using an SQL command. • Make New Connection Use this option to create a new connection. • Remove from Report Use this option to remove a table or stored procedure from your report. This option is also available by clicking the < arrow on the Database Expert. • Properties Use this option to obtain detailed information on the selected item.
Introduction to Reporting Choosing data sources and database fields Links tab Database tables are linked so records from one database match related records from another. For example, if you activate a Suppliers table and a Product table, the databases are linked so that each product (from the Product table) can be matched up with the supplier (from the Supplier table) that made the product. The majority of reports will probably require data from two or more tables, so linking will be necessary.
6 Introduction to Reporting Choosing data sources and database fields Use this option to add a field to the report. You can insert more than one field at a time by selecting multiple fields, right-clicking, and choosing Insert to Report. Alternatively, to insert a field, you can drag and drop it in the Design or Preview tabs. • Browse Use this option to browse data for a database field, formula field or SQL expression field. Note: On the shortcut menu, this option is called Browse Data.
Introduction to Reporting Choosing data sources and database fields Use this option to find occurrences of the selected item throughout all of the formulas in your report. • Set Prompt Order 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.
6 Introduction to Reporting About the report design environment About the report design environment Design tab When working with Crystal Reports, you will probably use the Design tab more than any other part of the program. 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.
Introduction to Reporting About the report design environment Design tab areas When you first begin creating a report, Crystal Reports automatically creates five areas in the Design tab. • Report Header This section is generally used for the report title and other information you want to appear at the beginning of the report. It can also be used for charts and cross-tabs that include data for the entire report.
6 Introduction to Reporting About the report design environment This section generally holds the summary value, if any, and can be used to display charts or cross-tabs. It is printed once at the end of a group. When a group, summary, or subtotal is added, the Group Header area appears directly above the Details area and the Group Footer area appears directly below the Details area.
Introduction to Reporting About the report design environment 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.
6 Introduction to Reporting About the report design environment • Add horizontal and vertical guidelines by clicking the rulers. See Designing with guidelines . • Zoom in and out on a report at any magnification from 25% to 400% of the original size. See Using the zoom feature. Preview tab To preview a report before printing it, click Print Preview on the Standard toolbar. The program gathers the data, makes the necessary calculations, and displays the report in the Preview tab.
Introduction to Reporting About the report design environment • From that point on, the program uses the saved data whenever you preview the report unless you specifically refresh it or add a field that requires the program to retrieve new data. Standard view In standard view, the report is displayed a page at a time. Using the navigation buttons in the Preview tab, you can move to the beginning or end of the report, or you can move backward and forward through the report one page at a time.
6 Introduction to Reporting About the report design environment Group Tree 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 About the report design environment forth between different groups, the smart navigation features of the Group Tree make your work extremely efficient. 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.
6 Introduction to Reporting About the report design environment Comparisons with the Design tab You have the same formatting capabilities in the Preview tab as you do in the Design tab. Menus (both menu bar and shortcut menus) and toolbars remain active, providing essentially the same functionality you have when working with a report in the Design tab. However, when you are making numerous changes, it is quicker to make the changes in the Design tab.
Introduction to Reporting About the report design environment • • The program places a sizing frame around the specific value you select. • It highlights every other value in the field. Likewise, when you select a summary value, you are actually selecting all the related summary values: • The program places a sizing frame around the specific value you select. • It highlights all the related summary values.
6 Introduction to Reporting Creating a new report 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)." Creating a new report 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 Creating a new report • Access/Excel (DAO) This option lets you connect to a supported database type (Access, dBASE, Excel, Lotus, and so on). You can create a new connection using the Make New Connection option. • 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.
6 Introduction to Reporting Creating a new report To add a table 1. Choose Database Expert from the Database menu. The Database Expert dialog box appears. 2. On the Data tab, search for the database you want to use in your report. See Selecting the data source. 3. In the Available Data Sources list, select the table you want to add to your report and click the > arrow to add it to the Selected Tables list.
Introduction to Reporting Creating a new report 4. To create links manually, drag a field from one table to a field in another table. If successful, a link line is created. If unsuccessful, a message is issued. Note: You can link tables by table name or by foreign key information. 5. If you've deleted links and you want to recreate them automatically, click Link. 6. Click OK when finished.
6 Introduction to Reporting Creating a new report satisfied with the link, you can modify it using the Links tab of the Database Expert. Related Topics • Understanding Databases • Linking options Placing data on the report Placing data on a report is a very important task. You need to know what type of data should be placed on the report and where on the report it should be placed. Database fields Much of the data placed on a report are database fields, displaying data as it is stored in the database.
Introduction to Reporting Creating a new report Formula fields To display data that is a calculated value, you need to create a formula field and place that formula field on the report. For example, if the database only stores the order dates and ship dates for orders but you need to display the number of days it takes to ship the order, you must create a formula field that will calculate the number of days between ordering and shipping. This is just one example of the use of formula fields.
6 Introduction to Reporting Creating a new report 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. 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 Creating a new report Running total fields To display a total that evaluates each record and provides a running sum of all the values in a field (or all the values in a certain set of values), a running total field needs to be created and placed in the report. If the first three values in a field were 2, 4, and 6, a running total would print 2, and then 6 (the sum of 2 + 4), and then 12 (the sum of 2 + 4 + 6). See Running Totals, for an introduction to running totals.
6 Introduction to Reporting Creating a new report Each special field is inserted into the report as an object. An object frame appears. You can now place it on the report. Note: To change the formatting of an inserted object, click the object to select it and click Format on the Expert Tools toolbar. The Format Editor appears where you can make the desired changes. See Formatting. Text objects Text objects are used in reports for a multitude of purposes.
Introduction to Reporting Creating a new report The ruler allows you to add indents and align text within the text object. Note: When you first insert the text object into the report, the object is automatically selected for editing. Picture fields When designing reports, there will be times when you want to include a picture. For example, you may wish to put a company logo in the report header. To insert a picture 1. On the Insert Tools toolbar, click Insert Picture. The Open dialog box appears. 2.
6 Introduction to Reporting Creating a new report 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. For more information, see Making a static OLE object dynamic. In general, Crystal Reports allows you to access BLOB fields containing: • Device-independent bitmaps (DIB). • JPEG, TIFF, or PNG images.
Introduction to Reporting Creating a new report To create a hyperlink field 1. Select a report object on the Design or Preview tab. 2. On the Format menu, click Hyperlink. The Format Editor dialog box appears with the Hyperlink tab active. 3. Click the type of hyperlink you want to create. The available types in the upper area are: • No Hyperlink This is the default option. There is no hyperlink associated with the selected report object.
6 Introduction to Reporting Creating a new report • A File Select this option to create a hyperlink to a file on a specific computer or networked computer. Use the formula button to create a file path based on a field value. You can also use a hyperlink of this type to run an EXE with a command-line parameter. Enter the path and application name, and follow it by the parameter for the command.
Introduction to Reporting Creating a new report Formatting data At this point in creating a report, you may want to do some basic formatting. Perhaps you would like to change the font size and style of a text object used as a title. Or, if you have a number field, such as a sales figure, you might want to place a dollar sign before the number or change the number of decimal places displayed.
6 Introduction to Reporting Creating a new report sort the records within each region in alphabetic order by Customer Name. Many of your reports will use some type of sorting. Depending on the report, you will sort the records in a list or sort in conjunction with grouping. See Sorting single and multiple fields, and Sorting records within groups. Summaries, subtotals, and grand totals Many reports use some sort of totaling.
Introduction to Reporting Creating a new report • If you have multiple summaries, you can look at the summaries behind summaries (the city summaries that make up the region summaries, for example), or at the data from the individual records that are summarized. Using the zoom feature You can easily zoom in on a report. You can choose any magnification from 25% to 400%. This feature is active in both the Design and Preview tabs.
6 Introduction to Reporting Creating a new report • 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 Creating a new report 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.
6 Introduction to Reporting Exploring reports and working with multiple reports Enter the desired information and click OK when finished to return to your report. 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.
Introduction to Reporting Exploring reports and working with multiple reports objects are listed. Any item you select in the tree view will be selected in the report (in either Design or Preview modes). You can modify report fields and objects by selecting them in the Report Explorer. When you right-click the selected item, you see a menu that contains the actions you can carry out on the item.
6 Introduction to Reporting Exploring reports and working with multiple reports • Show/Hide Grids and Subreports Use this option to show or hide cross-tabs, OLAP grids, and subreports in your report. Note: • • Items connected to a BusinessObjects Enterprise Repository are shown with the repository icon beside them. Items with hyperlinks are shown as blue text with a solid underline. Items with Report Part hyperlinks are shown as green text with a dashed underline.
Introduction to Reporting Exploring reports and working with multiple reports Note: This option is available only when there is an active report. • Add New Project When you select this option, the program adds a new folder under the root node that is called Untitled Project. You can add reports to this folder. Project folders offer a way to organize the reports in the Workbench. • Open Use this option to open the report or report package that you have selected in the project folders.
6 Introduction to Reporting Exploring reports and working with multiple reports entity.) When you choose this option, the Publish Object dialog box appears. For information about using this dialog box, search the Crystal Reports online help for "Publish Object dialog box." • Remove Use this option to remove a report, report package, or folder from the projects area. Note: • • • You cannot undo this option. When you remove a folder, you remove all of the items in it at the same time.
Introduction to Reporting Exploring reports and working with multiple reports 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. • Description of the error.
6 Introduction to Reporting Exploring reports and working with multiple reports • Sort By Use this option to sort the messages by type, number, description, or location. • Clear Use this option to remove the selected message. • Clear All Use this option to remove all of the messages. • 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.
Introduction to Reporting Beyond basic reports After installation, each explorer opens in a docked position in the Report Designer. You can dock explorers in any area of the designer by dragging them to a new location. Double-click an explorer's title bar to change between docked and free-floating mode. In free-floating mode, the explorer can be dragged to any position. When you drag the explorer, a placement frame shows you where it will be placed.
6 Introduction to Reporting Beyond basic reports 144 • Form letters (see Form letters) • Subreports (see Subreports) • Cross-tabs (see Cross-Tab Objects) • Multi-section reports (see Multiple Section Reports) Crystal Reports 2008 SP3 User's Guide
BusinessObjects Enterprise Repository 7
7 BusinessObjects Enterprise Repository What is the BusinessObjects Enterprise Repository? What is the BusinessObjects Enterprise Repository? The 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 BusinessObjects Enterprise Repository when you publish reports to BusinessObjects Enterprise.
BusinessObjects Enterprise Repository What is the BusinessObjects Enterprise Repository? The Repository Explorer displays the contents of your BusinessObjects Enterprise Repository as a tree with folders, subfolders, categories, and subcategories. Two folders and two categories are supplied through BusinessObjects Enterprise; these are called: Repository Items, Enterprise Items, Categories, and Personal Categories. You can add subfolders and subcategories.
7 BusinessObjects Enterprise Repository Accessing the BusinessObjects Enterprise Repository • Commands can be updated and added back to the repository through the Database Expert. Accessing the BusinessObjects Enterprise Repository The repository supplied by Crystal Reports is set up when you install. You don't have to do anything additional before you use the repository. To open the BusinessObjects Enterprise Repository 1. Start Crystal Reports and open a report. 2.
BusinessObjects Enterprise Repository Accessing the BusinessObjects Enterprise Repository 4. Expand nodes to see the repository contents. The Repository Explorer might appear in a docked position in the Report Designer depending on where it was when you last used Crystal Reports. For more information about docking explorers, see Opening and docking explorers. Hover your mouse's cursor over any object in the Repository Explorer to see a tooltip.
7 BusinessObjects Enterprise Repository Adding subfolders and subcategories to the repository Use this option to add a new subfolder to the repository. For information, see Adding subfolders and subcategories to the repository. Note: This option is called "Insert a new category" when you have selected a category in the repository tree. • Logoff Server Use this option to log off the BusinessObjects Enterprise server that you are connected to.
BusinessObjects Enterprise Repository Adding items to the repository Tip: The insert button changes automatically from folder to category depending on the type of object that you select. A new subfolder or subcategory is added to the repository tree. (If your repository items are not sorted, the new subfolder or subcategory is added to the bottom of the selected node. If your repository items are sorted by type, the new subfolder or subcategory is added alphabetically with the default name New Folder.) 2.
7 BusinessObjects Enterprise Repository Adding items to the repository 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. Adding a text object or bitmap image - another method You could have added the text object or bitmap image without dragging and dropping it. 1.
BusinessObjects Enterprise Repository Adding items to the repository Adding a custom function 1. In the Formula Workshop, expand the Report Custom Functions node and select the custom function you want to add to the repository. 2. Click the Add to Repository button. You'll see that your custom function has been added to the Repository Custom Functions node.
7 BusinessObjects Enterprise Repository Using repository objects in reports Using repository objects in reports Once you have an object or a collection of objects added to the repository, you can start using them in your Crystal reports. Each type of repository object is added through its own user interface. In the case of text objects and bitmap images, you simply drag them from the Repository Explorer to a report.
BusinessObjects Enterprise Repository Using repository objects in reports For information about how to log on to BusinessObjects Enterprise, see Working with Enterprise folders . 3. Expand the appropriate folder in the Repository Explorer and drag a text object or bitmap image to your report. Note: Folders in your BusinessObjects Enterprise Repository do not have to be named to represent the objects they hold; you can use a name of your choice.
7 BusinessObjects Enterprise Repository Modifying objects in the repository Tip: Another way to do this is to click the Database Expert option under the Database menu. 2. In the Database Expert, expand the Repository folder. Tip: If the BusinessObjects Enterprise Explorer doesn't open immediately, double-click Make New Connection. 3. In the BusinessObjects Enterprise Explorer, expand folders until you find the command you want to add, and then click Open.
BusinessObjects Enterprise Repository Updating connected repository objects in reports To modify a repository object Note: This procedure shows you how to modify and update a text object in the repository. Custom functions and commands are modified and updated in their respective user interfaces; in each case, you must first disconnect the custom function or command from the repository, make the modifications, and then add the custom function or command back to the repository. 1.
7 BusinessObjects Enterprise Repository Deleting items from the repository 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.
BusinessObjects Enterprise Repository Using Undo in the repository To delete an object from the repository 1. Click the Repository Explorer button on the Standard toolbar. Tip: Another way to do this is to click the Repository Explorer option under the View menu. 2. 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.
7 BusinessObjects Enterprise Repository Using Undo in the repository 160 Crystal Reports 2008 SP3 User's Guide
Designing Optimized Web Reports 8
8 Designing Optimized Web Reports Overview Overview Whether you distribute your enterprise reports over a local network, a corporate Intranet, or the Web, you can use Crystal Reports' powerful, built-in performance features to deliver web reporting speed.
Designing Optimized Web Reports Scale with BusinessObjects Enterprise Note: Understanding databases and how they work is often important when considering performance. See Databases overview for background information. Key strategies for optimizing web reports • As your company grows, Crystal Reports and BusinessObjects Enterprise can grow along with you. For details, see Scale with BusinessObjects Enterprise.
8 Designing Optimized Web Reports Scale with BusinessObjects Enterprise BusinessObjects Enterprise provides granular group, user, and data-level security to help you protect sensitive reports and deliver a more personalized end-user experience. • Scheduling BusinessObjects Enterprise incorporates a flexible time- and event-based scheduling system to allow you to process large reports during off hours and to avoid unnecessary database hits.
Designing Optimized Web Reports Scale with BusinessObjects Enterprise Evaluation times for date functions in BusinessObjects Enterprise Different date and time functions available in Crystal Reports are evaluated at different stages during report processing. Some are evaluated when a scheduled report is processed, or when a report instance is refreshed: this evaluation time is known as "WhileReadingRecords.
8 Designing Optimized Web Reports Making the right design choices Making the right design choices This section offers design options and considerations that will help improve the performance of your reports. The topics covered range from basic suggestions, such as updating reports created in older versions of Crystal Reports to the latest file format, to more involved decisions, such as whether to use live or saved data, and how to use subreports efficiently.
Designing Optimized Web Reports Making the right design choices 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 2008 report. 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.
8 Designing Optimized Web Reports Making the right design choices Saved data Reports with saved data are useful for dealing with data that isn't continually updated. When users navigate through reports with saved data, and drill down for details on columns or charts, they don't access the database server directly; instead, they access the saved data. Consequently, reports with saved data not only minimize data transfer over the network, but also lighten the database server's workload.
Designing Optimized Web Reports Making the right design choices Designing summary reports Designing and distributing summary reports is a relatively easy way to ensure that users quickly find the data they need over the Web. A summary report can include as much data as any other report. However, by hiding a summary report's Details section, you avoid overwhelming users with data they may not immediately need.
8 Designing Optimized Web Reports Making the right design choices Note: For details on minimizing data transfer with summary reports, see Performing grouping on server. 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.
Designing Optimized Web Reports Making the right design choices report, no data is retrieved for the on-demand subreport until you drill down on the hyperlink. For example, when designing a report that shows each employee's quarterly sales for each product and each product type, you might also want to track each employee's progress by including weekly sales information. This additional data, however, may not be of interest to many users viewing the report.
8 Designing Optimized Web Reports Making the right design choices database record in the main report.) As an alternative, consider using linked on-demand subreports in the Details section of your main report. Linking tables instead of linking subreports Whenever possible, coordinate your report data by linking database tables on the Links tab of the Database Expert, rather than by linking regular subreports—that is, subreports that aren't on-demand subreports—to the main report.
Designing Optimized Web Reports Making the right design choices Designing reports to maximize data sharing BusinessObjects Enterprise has data-sharing functionality that improves performance by reducing the number of database calls that are made in a system with multiple users. Data can be shared between users of a report object only if certain conditions are met.
8 Designing Optimized Web Reports Streamlining your reporting environment Streamlining your reporting environment Another step to ensuring that report users receive their information quickly is to assess your reporting environment.
Designing Optimized Web Reports Streamlining your reporting environment ways to adhere to this guideline are to assess your reporting needs and to plan your strategy before creating your report. When you know exactly what you need from your data source, Crystal Reports makes it easy to get that information. There are many other specific issues to consider when linking tables. These additional considerations, however, are largely contingent upon your reporting environment.
8 Designing Optimized Web Reports Streamlining your reporting environment Using thread-safe database drivers If you share and refresh reports over the web, open them using thread-safe database drivers whenever possible. The Crystal Report Engine supports multiple threads. Thus, when you make multiple data requests through thread-safe database drivers, the Report Engine can process all of the requests simultaneously. As the result, you'll be able to view your reports sooner.
Designing Optimized Web Reports Using enhanced record selection formulas • crdb_olap.dll (OLAP) • crdb_oracle.dll (Oracle) • crdb_p2ssby10.dll (Sybase) Note: Check the Release Notes in the Crystal Reports product distribution for updates to these lists; more database drivers may be added if they are found to be thread-safe during testing.
8 Designing Optimized Web Reports Using enhanced record selection formulas Crystal Reports analyzes your record selection formula and generates an SQL query from it. This SQL query is then processed by the database, which sends the resulting records back to Crystal Reports. Crystal Reports then evaluates the record selection formula locally for each of the records retrieved from the database, thereby calculating the set of records used to generate the report.
Designing Optimized Web Reports Using enhanced record selection formulas 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.
8 Designing Optimized Web Reports Using enhanced record selection formulas • You can only push down AND clauses (not OR). 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.
Designing Optimized Web Reports Using enhanced record selection formulas Consideration 2 Any selection formula that is of the form: DataBaseField SupportedOp erator ConstantOrParameterExpression can be pushed down. 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.
8 Designing Optimized Web Reports Using enhanced record selection formulas Consideration 4 SqlExpression SupportedOperator ConstantOrParameterExpression can be pushed down. For example, the selection formula {@ExtendedPrice} > 1000 cannot be pushed down if {@ExtendedPrice} = (Quantity * Price). However, if the formula @ExtendedPrice is replaced with the equivalent SQL Expression, then your record selection formula will be pushed down.
Designing Optimized Web Reports Using enhanced record selection formulas In general, parameter fields provide interactivity for users, who respond to the parameter prompts in order to specify the data they want to see. However, by incorporating your parameter fields right into your record selection formula, you not only provide interactivity, but you also decrease data transfer and increase performance.
8 Designing Optimized Web Reports Using enhanced record selection formulas Note: You've now created the parameter field. The rest of these procedures describe how to add the parameter field to the record selection formula with the Select Expert. 8. On the Report menu, click Select Expert. The Choose Field dialog box appears. 9. Select the Customer.Last Year's Sales field, and then click OK. The Select Expert appears. 10. On the Customer.
Designing Optimized Web Reports Using enhanced record selection formulas For more information • To create enhanced parameters, see Advanced parameter features. • For general information about parameter fields, see Parameter and prompt overview . • For general details regarding record selection formulas, see Record Selection. Using SQL expressions where appropriate SQL expressions are like formulas, but they are written in Structured Query Language (SQL).
8 Designing Optimized Web Reports Improving grouping, sorting, and totaling Finally, if your database supports Case Logic, and your report needs to summarize an If-Then-Else formula calculation, replace the formula with an SQL Expression field. In such cases, SQL Expression fields enable Crystal Reports to perform the report's grouping on the server. For more information, see Using SQL Expressions for Case Logic. For more information • For an introduction to SQL, see What is SQL? and The SQL language.
Designing Optimized Web Reports Improving grouping, sorting, and totaling Tip: When Use Indexes or Server for Speed is selected, you can quickly enable Perform Grouping On Server from the Database menu. 3. Click OK. Benefits of grouping on server—an example The following example describes a typical reporting scenario wherein grouping on server can significantly reduce the amount of unnecessary data transferred from the database server. To decrease data transfer by grouping on server 1.
8 Designing Optimized Web Reports Improving grouping, sorting, and totaling 8. Drill down on a country by double-clicking the report. Tip: When drill down is available, your cursor turns into a magnifying glass. Crystal Reports retrieves the appropriate Detail records as you need them. For instance, if you drill down on Australia, Crystal Reports quickly retrieves the seven records that make up the group.
Designing Optimized Web Reports Improving grouping, sorting, and totaling For instance, suppose that you're reporting off of an MS SQL Server 7 database, which supports Case Logic. You need to include an If-Then-Else calculation in your report, and you need to summarize that 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.
8 Designing Optimized Web Reports Improving grouping, sorting, and totaling 190 Crystal Reports 2008 SP3 User's Guide
Record Selection 9
9 Record Selection Selecting records Selecting records When a field is selected to appear on a report, field values from every record in the active table(s) are printed by default. In many cases, you may not want to include all the values, but only a subset of those values. For example, you may want to include: • Records only for a specific group of customers. • Records for a specific range of account numbers out of the total number of records in the database.
Record Selection Selecting records 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. • If the table used in a report has a State or Region field, you can specify in your request that the program use only those records in which the value in the state field is equal to California (Region is equal to CA).
9 Record Selection Selecting records The Select Expert can also be used to set up some sophisticated requests: • Customers whose names start with "A", "M", or "S." • Customers from California or Florida who ordered in July. These are all range limit requests. One or more constants define the range. The program compares the field value in each record to the constant(s) and rejects records with values outside the range. The report is limited to values within the range.
Record Selection Selecting records Tip: To base record selection on more than one field, click the New tab. Select the next field from the Choose Field dialog box. A selection formula is generated based on your specifications, limiting the report to the records you indicated. Note: To view the selection formula, click the Show Formula button. The Select Expert expands to show the formula.
9 Record Selection Selecting records Interaction of the Select Expert and the Formula Editor The Select Expert and the Record/Group Selection Formula Editor are interactive. That is, record selection criteria you enter via the Select Expert automatically generates a record selection formula which can be reviewed and modified. Likewise, record selection formulas and modifications to existing record selection formulas automatically update the selection criteria in the Select Expert.
Record Selection Selecting records 4. 5. 6. 7. Click the Hide Formula button when you are done with your review. Use the Select Expert to change your selection formula. Review the updated formula by clicking the Show Formula button again. To make changes to the formula, click the Formula Editor button in the expanded Select Expert and use the tools in the Formula Workshop to make your formula changes.
9 Record Selection Selecting records 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 Using formula templates 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.
9 Record Selection Using formula templates 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).
Record Selection Using formula templates Selects those records in which the value in the {file.FIELD} field is greater than 11111 but less than 99999 (both 11111 and 99999 are included in the range of values). Selecting records using dates The Month, Day, and Year functions can all be used in examples like the following: Year ({file.DATE}) < 1999 Selects those records in which the year found in the {file.DATE} field is earlier than 1999. Year ({file.DATE}) > 1992 and Year ({file.
9 Record Selection Pushing down record selection to the database server Selects those records in which the date found in the {file.DATE} field falls within the last full month. (If the month is May, this selects all records with an April date.) not({file.DATE} in LastFullMonth) Selects all records except those in which the date found in the {file.DATE} field falls within the last full month (if the month is May, this selects all records except those with an April date).
Record Selection Troubleshooting record selection formulas • The first stage of record selection takes place when the database server processes the SQL query and returns a set of records to Crystal Reports. • In the second stage, Crystal Reports further evaluates locally the record selection formula for the set of records returned from the database server.
9 Record Selection Troubleshooting record selection formulas 4. Make certain that all fields referenced in the record selection formula (the selectors) are on the report physically and are not hidden. For example, if one of the selectors is: {customer.POSTAL CODE} > "80000" but the {customer.POSTAL CODE} field is not used on your report (as in the case of a sales report that uses the postal code to define territories but does not include the postal code in the report data), then insert the {customer.
Record Selection Troubleshooting record selection formulas • If it does, then you know that this part of the selection formula is working. • If it does not, then troubleshoot this part of the selection formula. 7. Once the selection formula with one selector activated is working properly, add a second selector. For example, the new selection formula might look like this: {customer.POSTAL CODE} > "80000" and {customer.CONTACT LAST NAME}[1] = "C" 8.
9 Record Selection Troubleshooting record selection formulas in the {customer.CONTACT FIRST NAME} field are mixed case ("Bob", for example), the selection formula will find no matches and thus will not print any details for the report. You can solve this problem by using the UpperCase (str) or LowerCase (str) functions in your selection formula to convert field data to a consistent case before the program begins its selection. For example, if you were using this formula: {customer.
Record Selection Troubleshooting record selection formulas Check your selection formula closely, and make sure that the spaces in the selection formula match the spaces in the fields you are trying to match. Using If statements in selection formulas When creating record selection formulas that include If statements, always include the Else keyword; otherwise, no records or unexpected records may be returned.
9 Record Selection Troubleshooting record selection formulas 208 Crystal Reports 2008 SP3 User's Guide
Sorting, Grouping, and Totaling 10
10 Sorting, Grouping, and Totaling Sorting data Sorting data Sorting means placing data in some kind of order to help you find and evaluate it. When you first insert a database field into your report, the data within the fields appears in the order in which it was originally entered into the database. Locating information in this kind of report is difficult. It is much easier to review or find information when you can see it sorted in a logical format.
Sorting, Grouping, and Totaling Sorting data Field Type Sort Order blanks punctuation Single-character string fields numbers uppercase letter lowercase letters two letters three letters four letters, and so on Multiple character string field For example: • "123" comes before "124" • " " (blank) comes before "a" • "aa" comes before "aaa" Currency fields numeric order Number fields numeric order Date fields chronological order chronological order DateTime fields same-date values sorted by time
10 Sorting, Grouping, and Totaling Sorting data Field Type Sort 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. In certain cases, Unicode data is sorted by its binary value, but it can also be sorted according to a particular locale setting.
Sorting, Grouping, and Totaling Sorting data Sorting single and multiple fields In single field sorting, all the records used in the report are sorted based on the values in a single field. Sorting an inventory report by stock number or sorting a customer list by customer number are examples of single field sorts. 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.
10 Sorting, Grouping, and Totaling Sorting data 2. Highlight the field to be sorted from the Available Fields area. 3. Click the > arrow. The selected field is added to the Sort Fields list. 4. Specify the sort direction. 5. If sorting by more than one field, highlight the second field you want the data to be sorted by and add it to the Sort Fields list. 6.
Sorting, Grouping, and Totaling Sorting data 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". A "Sort Control" is particularly useful for several reasons: • • • • It allows users to sort report data for further analysis without leaving the canvas of the report. It eliminates processing demand on the database.
10 Sorting, Grouping, and Totaling Sorting data 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 Grouping data b. Select a sort field and click OK. c. Click and drag your cursor to the report location where you would like to insert the Sort Control. d. Enter a name for the new Sort Control, and click outside the text object to complete the process. 5. Repeat Step 4 for any other sort controls you want to create. Users can sort the fields you have selected by clicking the sort arrows that appear beside the text object.
10 Sorting, Grouping, and Totaling Grouping data Group and sort direction When data is grouped, four sort and group direction options are available. Direction refers to the order in which the values are displayed. • Ascending Ascending order means smallest to largest (1 to 9, A to Z, False to True). The program sorts the records in ascending order and then begins a new group whenever the value changes. • Descending Descending order means largest to smallest (9 to 1, Z to A, True to False).
Sorting, Grouping, and Totaling Grouping data 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.
10 Sorting, Grouping, and Totaling Grouping data Tip: To hide the group header name, right-click the group header, select Format Field, and click Suppress on the Common tab of the Format Editor. 6. Click OK. If your records within each group are unsorted, you need to sort the records within each group. See Sorting records within groups. Related Topics • Grouping data in intervals • Creating group headers Creating custom groups Usually data is sorted based on the values from a field in the report.
Sorting, Grouping, and Totaling Grouping data Specified order grouping provides a solution to these custom sorting and grouping challenges. It enables you to create both the customized groups that will appear on a report and the records that each group will contain. The only limitation is that a record can be assigned to only one group. To follow a tutorial where you create custom groups to rank customers by the amount of business they did in the previous year, see Grouping data in intervals.
10 Sorting, Grouping, and Totaling Grouping data For example, if you group a sales report by invoice date, you might want your users to choose whether they see that data from earliest invoice date to most recent invoice date (ascending order) or most recent invoice date to earliest invoice date (descending order).
Sorting, Grouping, and Totaling Grouping data 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. When you are prompted to select a Sort Order, select the option that you want, and click OK. Your report appears with groups for the field that you selected in the Insert Group dialog box and sorted in the order that you selected in your parameter prompt.
10 Sorting, Grouping, and Totaling Grouping data 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.
Sorting, Grouping, and Totaling Grouping data • You might want to see only the groups with the highest summary values, or the lowest. You can select the groups that appear in the report in two different ways: • By using the Select Expert. • By using selection formulas. Note: For faster results, limit records through record selection before creating groups. See Selecting records.
10 Sorting, Grouping, and Totaling Grouping data The Select Expert dialog box appears. Note: If you click the Select Expert button without first highlighting a summary field in your report, the Choose Field dialog box appears. 2. Use the drop-down list to enter your selection criteria for the indicated field. 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.
Sorting, Grouping, and Totaling Grouping data 2. Click Record to create a record selection formula. - or Click Group to create a group selection formula. The Formula Workshop appears. 3. Enter your selection formula in the Group Selection Formula Editor. Note: The resulting formula must be Boolean; that is, it must return either a True or False value. 4. Click Check to identify any errors in the formula. 5. Fix any syntax errors the Formula Checker identifies. 6.
10 Sorting, Grouping, and Totaling Grouping data Note: Xtreme.mdb is located on the Business Objects Technical Support web site http://support.businessobjects.com/samples/. For each order, the report shows the customer that placed the order, the region in which that customer is located, the order ID number, and the amount of the order. 2. Group the report by the {customer.REGION} field. 3. Insert a summary that subtotals the {orders.ORDER AMOUNT} field for each {customer.REGION} group.
Sorting, Grouping, and Totaling Grouping data 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. Grouping data in intervals You may want to group your data into intervals. Age groups, time periods, and sales categories are some of the interval groupings that can be created.
10 Sorting, Grouping, and Totaling Grouping data 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.
Sorting, Grouping, and Totaling Grouping data 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. The Define Named Group dialog box reappears. 10. Set up a second group that contains values between $10,000 and $25,000. • Type "$10,000 to $25,000" in the Group Name field.
10 Sorting, Grouping, and Totaling Grouping data customers in a group, then all the "B" customers, and so forth. To do this requires the use of a formula. Do not worry if you are unfamiliar with formulas. This text will show you what formula you need here and how to enter it. You can learn more about creating and editing formulas in Formulas overview. You are going to create a formula that will extract the first letter of each customer's name.
Sorting, Grouping, and Totaling Grouping data 7. On the Insert menu, click Group. The Insert Group dialog box appears. 8. Select the formula field as the field you want your data grouped by from the first drop-down list. 9. Select the sort direction from the second drop-down list. 10. Click OK. You return to your report with the data grouped by the formula field as specified. The data is broken into groups based on the first letter in the customer's name.
10 Sorting, Grouping, and Totaling Grouping data 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. By default, the group header of the report displays the value of the field you are grouping on. 5.
Sorting, Grouping, and Totaling Grouping data 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.
10 Sorting, Grouping, and Totaling Grouping data 11. In the Group Indent field, enter the amount you want to indent for each subgroup. 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.
Sorting, Grouping, and Totaling Grouping data The report is now grouped by employee name and further sorted to show the supervisor hierarchy. You can see that Mina, whose employee data had no supervisor data associate with it, has been sorted at the top of the list. Under Mina's name is each supervisor who reports to her; and under each of those supervisors is a list of employees who reports to him or her.
10 Sorting, Grouping, and Totaling Grouping data 2. Click the Conditional Formula button next to the X position value field. 3. In the Formula Workshop, enter your conditional X position formula text. For example, enter formula text such as this: numbervar hLevel := HierarchyLevel (1); numbervar deltaX := 0; if (hLevel > 1) then deltaX := (hLevel - 1) * 0.4; deltaX := deltaX * 1440; Note: • • There are several ways that you can create this formula; this code is an example of one way.
Sorting, Grouping, and Totaling Grouping data • There cannot be any circular logic in the data (that is, A cannot be related to B, while B is related to C, and C is related back to A). For example, if you want to show the hierarchical relationship of the people who work in a department, you could group data by employee name (the child field) and specify the hierarchy by using the field that lists whom the employee reports to (the parent field). Your database tables might look like the following example.
10 Sorting, Grouping, and Totaling Grouping data 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 Summarizing grouped data • The report contains hierarchical group footers that include the records that are lower in the hierarchy of each group. You can summarize data across the hierarchies. • Use the conditional-X-position feature to ensure that the indenting you set to show hierarchical relationships does not affect other fields in same section of your report.
10 Sorting, Grouping, and Totaling Summarizing grouped data • Determine the maximum, minimum, average, or Nth largest value. • Calculate up to two kinds of standard deviations and variances. For example: • Customer list reports: determine the number of customers in each state. The summary would count the distinct customers in each state group. • Order reports: determine the average order placed each month. The summary would calculate the size of the average order for each month group.
Sorting, Grouping, and Totaling Summarizing grouped data For more information about percentages, see Percentages. 6. If you want to summarize across a hierarchy, select Summarize across hierarchy. For more information about hierarchies, see Grouping data hierarchically. 7. Click OK when finished. Related Topics • Subtotaling data Ordering groups by summarized values Groups can be organized in either ascending or descending order by summary value.
10 Sorting, Grouping, and Totaling Summarizing grouped data In such a case, you would select the sum or the average from this drop-down list. 5. Specify the sort direction. 6. To select a second group sort, repeat Steps 2-5. When you run the report, the program will order the groups based on the summary value(s) specified.
Sorting, Grouping, and Totaling Summarizing grouped data 2. • With top N groups, you are instructing the program to display those groups that have the highest summary values (Top N). • With bottom N groups, you are instructing the program to display those groups that have the lowest summary values (Bottom N). Click the Group Sort Expert button on Expert Tools toolbar. The Group Sort Expert appears with a tab for your group.
10 Sorting, Grouping, and Totaling Summarizing grouped data In such a case, you would select the sum or the average from this drop-down list. 5. In the Where N is text box, enter the number of groups you want to display. For example: • To report on the three fastest selling product lines, set N to be equal to three. • To report on the five least productive sales regions, set N to be equal to five. 6.
Sorting, Grouping, and Totaling Summarizing grouped data Selecting top or bottom groups or percentages conditionally If you want your users to choose their own value for a Top N, a Bottom N, or a top or bottom percentage, create a parameter to prompt for the value that Crystal Reports can then pass to a conditional selection formula. Note: • • For groups, the top or bottom N value must be between 1 and 32,766. For percentages, the top or bottom N value must be between 0 and 100.
10 Sorting, Grouping, and Totaling Subtotaling 10. Click OK to save your group sort. 11. When you are prompted to enter a number or percentage, enter the value that you want, and click OK. Your report appears with only the number of groups that match the value you entered in your parameter prompt. To enter a different value, click the Refresh button and choose "Prompt for new parameter values." The groups in your report are refreshed to show the new value that you entered.
Sorting, Grouping, and Totaling Subtotaling 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. Choose the field you want the data grouped by, specify a sort direction and then click OK when finished. 5. On the Insert Summary dialog box, select the group you just created from the Summary location list and then click OK.
10 Sorting, Grouping, and Totaling Subtotaling The Formula Name dialog box appears. 4. Enter the name you want to identify the formula, and then click OK. 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.
Sorting, Grouping, and Totaling Percentages 11. Click OK to return to the Insert Summary dialog box. 12. Select the group you just created from the Summary location list and then click OK. Your data will be sorted by date and grouped in one week intervals. Percentages 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.
10 Sorting, Grouping, and Totaling Percentages The Insert Summary dialog box appears. 2. Select the field for which you want to calculate the sum. For example, you may want to insert a field which calculates the sum of last year's sales. 3. Select Sum in the Calculate this summary list. 4. Select the summary location. Note: The summary location cannot be Grand Total (Report Footer) when you are calculating a percentage. 5. Click the Show as a percentage of check box. 6.
Sorting, Grouping, and Totaling Group headers Group headers 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.
10 Sorting, Grouping, and Totaling Group headers 2. When the object pointer appears, move the object frame into the Group Header section. 3. Enter the text you want to use for the header. 4. Click outside the frame when finished to complete the process. Now when you run the report, the same header will appear at the beginning of each group. Live headers A live header is a header that changes based on the content of the group.
Sorting, Grouping, and Totaling Group headers Group name with text A more complex type of live header combines a field value and text. A typical group header of this kind for data broken down by region would be, "Sales for California" or "Customers in Postal Code 60606". Creating these headers involves three steps: • Insert a text object in the Group Header section. • Type in the text you want to appear. • Enter the Group Name field in the text field where you want it to appear in the Group Header.
10 Sorting, Grouping, and Totaling Group headers 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. To create a live group header for a group based on a formula, simply insert the group name field in the Group Header section. When you run the report, the "A" group will have the letter "A" as a header, the "B" group will have the letter "B", and so on.
Sorting, Grouping, and Totaling Group headers Suppressing group headers You have the option to hide group headers in your report. 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.
10 Sorting, Grouping, and Totaling Group headers 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 11
11 Running Totals Understanding running totals Understanding running totals Running total fields are similar to summary fields but allow more control over how the total is calculated and when it is reset. Running total fields are specifically suited to perform the following totaling functions: • Show values of a total accumulate as it is calculated record by record. • Total a value independent of the report's grouping. • Total a value conditionally.
Running Totals Understanding running totals 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.
11 Running Totals Creating running totals Creating running totals Creating running totals in a list Running totals are totals that can be displayed on a record by record basis. They total all records (in the report, in the group, and so on) up to and including the current record. 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.
Running Totals Creating running totals 6. Select sum from the Type of summary list. 7. In the Evaluate section of the dialog box, click On change of field, and select Orders.Order ID as the On change of field. The running total will execute each time this field changes. 8. In the Reset section of the dialog box, click Never (this gives you a running total that never resets; that is, the running total continues throughout the report). 9. Click OK to save the running total field.
11 Running Totals Creating running totals Link the Customers and Orders tables and place the following fields from left to right in the Details section: {customer.CUSTOMER NAME} {orders.ORDER ID} {orders.ORDER AMOUNT} Note: Xtreme.mdb is located on the Business Objects Technical Support web site http://support.businessobjects.com/samples/. 2. On the Insert menu, click Group and group on the Customer.Customer Name field. 3. On the View menu, click Field Explorer. The Field Explorer dialog box appears. 4.
Running Totals Creating running totals 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. For example: • You have a list that contains both Canadian and U.S. customers. • You want to keep customer records sorted alphabetically based on customer name. • You do not want to break the data into groups based on the country. • You want a total of the values from just the Canadian records.
11 Running Totals Creating 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 Creating running totals 15. Now create the "CanadaTotal" running total field using the process outlined in steps 5-13. The only difference is that this time you will set the evaluation formula to: {Customer.Country} = "Canada" 16. When you are finished, place the #CanadaTotal field in the Details section of your report Note: If you only want to see a grand total of the Canadian and American sales, place the two running total fields you created in the Report Footer section of your report.
11 Running Totals Creating running totals using a formula 2. On the Insert Tools toolbar, click Insert Group and create a group based on the Customer.Customer Name field. 3. Right-click the Customer.Last Year's Sales field and select Summary from the Insert submenu. 4. Choose Group #1: Customer.Customer Name - A as the location for your summary. If you look at the subtotals for each group, you will notice they aren't accurate. This is because the Customer.
Running Totals Creating running totals using a formula When you create a running total manually, you need to create three formulas: • A summary formula. • A reset formula to set a variable to zero. • A display formula to display the variable. In the following procedure, you will create a report that performs the following functions: • Maintains a running total of customer orders. • Groups customer orders and resets the running total for each group.
11 Running Totals Creating running totals using a formula 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. In the Formula Workshop, create "AmountReset": WhilePrintingRecords; CurrencyVar Amount := 0; This formula says: Set the value in the Amount variable to 0. 10.
Running Totals Creating running totals using a formula Note: This formula prints the same value that @RunningTotal prints as the running total for the last record in each group. But since it is printing it in the Group Footer section, it acts as a group subtotal, not as a running total. On your report, each row in the running total column displays the current record value added to the previous values.
11 Running Totals Creating running totals using a formula 272 Crystal Reports 2008 SP3 User's Guide
Multiple Section Reports 12
12 Multiple Section Reports About sections About sections Crystal Reports provides five design areas to use when building your report: • Report Header • Page Header • Details • Report Footer • Page Footer Each area contains a single section when you first create a new report. You cannot delete any of these original sections but you can hide them or add to them. Once you have added sections, you can delete them, move them in relation to other similar sections, or merge related sections together.
Multiple Section Reports Working with sections 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. 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 insert a section after.
12 Multiple Section Reports Working with sections 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.
Multiple Section Reports Splitting and resizing sections Note: The program enables only those options (free form, new page before, and so on) that apply to the highlighted section. 2. Move the sections so the two sections you want to merge follow each other in the list. 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.
12 Multiple Section Reports Splitting and resizing sections Splitting a section 1. Move the pointer over the left boundary of the section you want to split. 2. When the pointer becomes a Section Splitting cursor, click the boundary and drag the pointer into the section. 3. When a horizontal line appears, drag it up or down to split the section the way you want it. Resizing a section 1. Move the pointer over the bottom boundary of the section you want to resize. 2.
Multiple Section Reports Using multiple sections in reports A section automatically expands vertically in two instances: • When you place an object and the object is bigger (vertically) than the section you place it in. • When you expand an object (vertically) so it becomes bigger than the section it resides in. Note: • • You cannot resize a section smaller than the combined height of all the objects in the section.
12 Multiple Section Reports Using multiple sections in reports length object is toggled on in the Format Editor, that object may overprint objects positioned directly below it unless you have: • Expanded the section to fit the maximum size of the object. • Spaced the objects, allowing enough space for the first object to complete printing before the second one begins.
Multiple Section Reports Using multiple sections in reports • OLAP grids Eliminating blank lines when fields are empty It is very common to have two address lines in a customer table, one for street address (Address 1), and one that can be used for a suite number or mail stop (Address 2). Address 1 usually contains a value, but Address 2 is often blank.
12 Multiple Section Reports Form letters To add blank lines conditionally 1. Use the Section Expert to create two Details sections. See Working with sections. 2. 3. 4. 5. Place the report detail data in the top section. Leave the second section empty. In the Section expert, highlight the second section. On the Common tab, select the Suppress (No Drill-Down) check box then click the conditional formatting button to its right. 6.
Multiple Section Reports Form letters 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.
12 Multiple Section Reports Form letters wrap. In the edit mode you can insert text and such non-text objects as database fields and formulas. Whenever the object is in edit mode, it contains an insertion point, a flashing vertical line that indicates the position at which typed text or inserted fields will begin. 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.
Multiple Section Reports Form letters • You can leave edit mode by clicking outside of the text frame. You can also click Ctrl+Enter. Creating a form letter using a text object The following section shows you how to create a form letter. You are going to use a text object to create a form letter. The form letter you create will be tied to a database table so that each letter will be customized with company information from a different record.
12 Multiple Section Reports Form letters 5. Drag the resizing handle on the right side of the object to the right edge of the Design tab. This will make the object about eight inches wide, the approximate width of a page. You may have to stop resizing, scroll the window, and resize some more to accomplish this. 6. Double-click inside the text object to place it in edit mode. It is now ready for you to begin your work. The insertion point appears at the extreme left, inside the object.
Multiple Section Reports Form letters 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. Note: To change the way the date is formatted in the letter, double-click the text object to select it. Then right-click the Print Date field and choose Format {Print Date} from the shortcut menu.
12 Multiple Section Reports Form letters 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 Form letters 3. In the Field Explorer dialog box, highlight the Contact Title field from the Customer table and drag it into the text object, placing it immediately after the space. 4. Insert a space. The program positions the insertion point immediately after the space. 5. In the Field Explorer dialog box, drag the Contact Last Name field into the text object and place it at the insertion point. The insertion point moves to the right of the field. 6.
12 Multiple Section Reports Form letters thank you and your staff for your support. I hope next year will be a banner year for you." 5. Press Enter twice. 6. Type "Sincerely yours" (without the quotation marks), followed by a comma, and then press Enter four times. 7. Finally, to complete the form letter, type your name. The Design tab should look similar to this: 8. Click Print Preview on the Standard toolbar to preview the form letter.
Multiple Section Reports Form letters 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. To create a conditional message 1.
12 Multiple Section Reports Form letters • Format the first section to be suppressed when the balance is less than the credit limit. • Format the second section to be suppressed when the balance is more than the credit limit. Now, when a record indicates available credit, the "buy more" letter will print. When the account is over the credit limit, the "over limit" letter will print. And when the customer is right at the credit limit, nothing will print at all.
Formatting 13
13 Formatting Formatting concepts Formatting concepts This section explains how to format a report. Formatting refers to changes you can make to the layout and design of a report, as well as the appearance of text, objects, or entire report sections. You can use formatting to do many things, including: • Dividing sections of a report. • Calling attention to certain data. • Changing the presentation of dates, numbers, Boolean values, currency values, and text strings. • Hiding unwanted sections.
Formatting Using a template 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. To apply a template in the Standard Report Creation Wizard 1. Click New on the Standard toolbar. 2.
13 Formatting Using a template To apply a template to an existing report 1. On the Report menu, click Template Expert. The Template Expert appears. 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.
Formatting Using a template Note: To remove a template, you must use this option; the Undo command is not available from the Edit menu. Reapplying the last template selected If you want to reapply the last template you selected during a session of Crystal Reports, you can simply select an option on the Template Expert. 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.
13 Formatting Using a template To add a Template Field Object to a template report 1. On the Insert menu, click Template Field Object. A placement frame is attached to your cursor. 2. Place the Template Field Object frame on your template report. A Template Field Object can be placed in any report section. 3. Right-click the object and click Format Template Field from its shortcut menu. A list of formatting options appears.
Formatting Using a template 2. In the Formula Editor, replace the Space(10) section of the argument with a database field of the type you want to see in your sample, save your change, and close the Formula Workshop. 3. Refresh your report's data.
13 Formatting Using a template The formatting and objects from an existing report used as a template can override choices you made in the Standard Report Creation Wizard. For example, if you had selected "No Chart" on the Chart screen of the wizard, but then applied a template report that includes a chart, the template report will override your selection and the new report will contain a chart.
Formatting Using a template If the Template has... If the target report has...
13 Formatting Using the Report Design Environment Using the Report Design Environment This section describes several things to keep in mind when designing reports that are distributed to different environments. Section characteristics A report consists of several sections, including the Report Header, Page Header, Group Header, Details, Group Footer, Page Footer, and Report Footer. Each report section is made up of a series of lines.
Formatting Using the Report Design Environment Making an object underlay a following section Using this example, you can make a logo underlay multiple sections. This procedure is similar to the one for inserting a company watermark to serve as a report background. To make an object underlay a following section, first place the object in the section above the section you want it to underlay.
13 Formatting Using the Report Design Environment Note: In this example, the picture is placed to the right of the fields to avoid underlaying the text. When you are working with a watermark, a subdued picture designed to be nearly invisible, place it directly above the text. 3. On the Standard toolbar, click Print Preview. The picture prints in the Page Header section of each report page. To make the picture underlay the following section 1. On the Report menu, click Section Expert.
Formatting Using the Report Design Environment • The position of the picture in the section. By modifying size and placement of an object, you can create a variety of visual effects, using the underlay feature. Pre-printed forms If you print on pre-printed forms, you will be able to: • Scan a form. • Place it in the report as a bitmap. • Use the underlay feature to line up the bitmap and report, as well as move objects anywhere you want them to appear.
13 Formatting Using the Report Design Environment Keep in mind the width of your paper when deciding your column width. For example, if you have three fields in your Details section, and they take up four inches of space, limit the width of the column to under four and a half inches so that all the field information can be seen. 5. Set the Horizontal and/or Vertical gap you want to maintain between each record in your column. 6. In the Printing Direction area, choose a direction. 7.
Formatting Using the Report Design Environment one to suppress when sales are equal to or over $X and one to suppress when sales are under $X. Suppress Blank Section The Suppress Blank Section property hides a section whenever there is nothing in it. If something is placed within the section and it produces a value in your report, then it becomes visible. Hiding report objects Crystal Reports has three formatting options in the Format Editor for hiding individual objects.
13 Formatting Using the Report Design Environment 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. 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 Using the Report Design Environment Note: This will only work if there are no other objects in the section. 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.
13 Formatting Using the Report Design Environment Note: You can click the Conditional Formula button for any of these properties and create a formula that will make the setting conditional on some event. See Working with conditional formatting. To set these properties, select the object, then click Format from the Expert Tools toolbar to open the Format Editor dialog box. When the Format Editor appears, set the properties.
Formatting Using the Report Design Environment • For text-based objects, the default width is approximately 19 average character widths wide. Text objects are different from database fields in that their width automatically expands as you enter in text and/or database fields into the object. As with all other text-based objects, the width can be resized by the user. • For different number fields (double, single, integer, long integer, and byte) the default widths are all different.
13 Formatting Using the Report Design Environment 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. There are many times when the actual text in a database field is far less than the maximum amount the field can contain. For example, a {table.LAST NAME} field is designed with a field size of 80 and the longest name in the database is 28 characters.
Formatting Using the Report Design Environment Note: The suppression of embedded field blank lines is designed to remove blank lines within a text object if the text object contains an entirely blank field alone on a line followed by a carriage return. 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.
13 Formatting Using the Report Design Environment Placing multi-line, text-based objects While text-based objects that are formatted to print on multiple lines follow the same design rules as other objects, they have one additional characteristic to be considered. If the printer driver expands or contracts the spacing of the text, word wrapping may differ, changing the number of lines necessary to print the object in order to accommodate growth or shrinkage.
Formatting Using the Report Design Environment Importing text-based objects from a file Using Crystal Reports, you can import a formatted, text-based object from an existing file onto your report. To import text-based objects from a file 1. Double-click the text-based object you want to format to put it in edit mode, then right-click it to bring up the shortcut menu. 2. On the shortcut menu, click Insert from file. 3.
13 Formatting Using the Report Design Environment Each report contains a design grid. You can select the grid on or off, as well as set it to different sizes when required. By default, the grid is not selected. See Selecting the grid. Once set, the grid remains the same size for all sections. It is measured from the upper-left corner of each section and continues down and to the right, until the end of the section.
Formatting Using the Report Design Environment Designing with guidelines Crystal Reports provides guidelines to help you align and size report objects with accuracy. Guidelines are non-printing lines that you can place anywhere on the Design and Preview tabs to aid in alignment. They have a snap property that automatically snaps objects to them. Viewing guidelines You can view guidelines on the Design and Preview tabs by selecting the view options in the Options dialog box. To view guidelines 1.
13 Formatting Using the Report Design Environment To insert, move, and remove guidelines manually 1. In the Design or Preview tab, click the ruler at the top to activate a vertical guideline; click the ruler on the left to activate a horizontal guideline. Notice that each guideline is attached to an arrowhead on its originating ruler. Note: If guidelines don't appear, ensure that the appropriate Guideline option is selected on the View menu.
Formatting Using the Report Design Environment You can snap an object's left side, right side, or vertical midline (the invisible line that bisects an object vertically) to a vertical guideline. To snap objects to a guideline 1. Insert a guideline by clicking one of the rulers. 2. Drag the report object onto the guideline, so that one of the object's edges is on the guideline. The snap property of guidelines works differently for text-based objects than for other objects, such as OLE objects.
13 Formatting Using the Report Design Environment To place several text objects of different font sizes on one line with their baselines lined up, snap each object's baseline to the same horizontal guideline. 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.
Formatting Using the Report Design Environment 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. If the objects are not the desired size, drag one or both of the guidelines until the objects are the correct size. Indenting lines Using Crystal Reports, you can control line indentation for memo fields, string fields, and text-based objects.
13 Formatting Using the Report Design Environment • 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. Overflow Field Representation Crystal Reports uses Overflow Field Representation to assist users when working with numeric or currency values in report cells.
Formatting Using the Report Design Environment 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 (######). Selecting multiple objects You can select multiple objects, including text, field, chart, map, bitmap, OLAP grid, Cross-Tab and OLE objects, to format them together.
13 Formatting Using the Report Design Environment 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.
Formatting Using the Report Design Environment 4. In the Spacing area, in the Character spacing exactly field, specify the value n that each character occupies. 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.
13 Formatting Using the Report Design Environment 5. Click OK to save your changes. Note: When setting fractional font sizes for individual database fields and text-based objects that you've already placed on your report, you must make your changes manually—that is, by following these procedures. (This is because the existing font settings of objects in your report will override your default Options.
Formatting Using the Report Design Environment Tip: Another way to do this is to click the Section Expert button on the Expert Tools toolbar. 2. In the Sections list, select the section to be modified. 3. On the Paging tab, select the desired page orientation. Note: This option is not available for the Page Header and Pager Footer. Setting page margins Margins are the write spaces on the left, right, top, and bottom of the page.
13 Formatting Using the Report Design Environment the margins to 0. If you next choose a bigger page, this (reduced) printable area is kept and the left/right, top/bottom margin ratios become 1:1. TrueType fonts Designing your report with printer-specific fonts can lead to problems when using different printers. The fonts may not be supported by the other printers, or if they are supported, the fonts may not be installed on the printers.
Formatting Using the Report Design Environment Problems such as these may arise when you have: • Two identical printers, but each one is using a different printer driver. • Two different printers using the same printer driver. • Two different printers using different printer drivers. • One printer driver that uses the TrueType font and a second printer driver that maps TrueType fonts to PostScript fonts.
13 Formatting Formatting properties reliance on a printer driver also prevents inconsistencies that can occur if your report is viewed with a different printer driver than the one you created it with. To format a report for web viewing 1. On the File menu, click Page Setup. The Page Setup dialog box appears. 2. In the Printer Options box, select the No Printer check box. 3. Select your desired page size, page orientation, and page margins.
Formatting Working with absolute formatting • Format Editor to format field values. • Section Expert to format entire sections. • Highlighting Expert to conditionally format all types of fields. Each of these dialog boxes contains a number of different formatting properties, as well as the tools for turning the properties on or off and specifying attributes.
13 Formatting Working with absolute formatting To make a report read-only 1. On the File menu, select Report Options. 2. Select Read-only. To make a section or area read-only 1. On the Report menu, select Section Expert. 2. Select the section or area that you want to make read-only. 3. On the Common tab, select Read-only. Note: When a section is read-only, you can move it, cut it, and delete it, but you cannot make changes within the section.
Formatting Working with absolute formatting To lock an object's size and position 1. Select the object whose size and position you want to lock. 2. On the Formatting toolbar, click Lock Size/Position. Changing your default field formats Crystal Reports allows you to display database fields in almost any format on your report. This section describes how to use the Options command to control the default format settings that Crystal Reports uses when you add a field to any report.
13 Formatting Working with absolute formatting Note: These default settings will affect only the objects that you subsequently add to a report. To format fields that you've already added to a report, you must right-click the field in the report and select Format Field from the shortcut menu. To set standard default formats for Date, Time, and Date and Time fields 1. On the File menu, click Options. 2. In the Options dialog box, click the Fields tab. 3.
Formatting Working with absolute formatting Note: If you chose to format "Date and Time" fields at Step 3, then you will see three tabs in the Custom Style dialog box (Date and Time, Date, and Time). The formats specified in these tabs apply only to the two elements of "Date and Time" fields, and will not affect the formats specified for independent date fields or time fields. 6. Create your preferred format by adjusting the various options in the Custom Style dialog box. 7.
13 Formatting Working with absolute formatting 2. Use the pencil cursor to draw the line where desired. Note: You cannot draw diagonal lines. 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.
Formatting Working with absolute formatting 2. Use the pencil cursor to draw the box where desired. 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. Expected behavior of line and box formatting This section lists the expected behavior of line and box formatting for some common cases.
13 Formatting Working with absolute formatting Between these sections 338 • 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 • GH/D/GF to RF • GH/D/GF to PF Start 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 Working with absolute formatting Between these sections • • • Start End GH2 to GH3 In the first instance of the GH3 section, or if In every instance of the that section is supGH2 section, or if that pressed, at the top of section is suppressed, the next visible at the top of any GH/D/GF section. If GH/D/GF section before these sections are supthe end section. pressed, at the top of the first RH/PH/RF/PF.
13 Formatting Working with absolute formatting Between these sections • • • 340 Start End GH2 to GF2 In the GF2 section that corresponds to the GH2 In every instance of the section, or if that section GH2 section, or if that is suppressed, at the top section is suppressed, of the next visible at the top of any GH/D/GF section. If GH/D/GF section before these sections are supthe end section. pressed, at the top of the first RH/PH/RF/PF.
Formatting Working with absolute formatting Between these sections • GF2 to GF1 Start End In the first instance of the GH2 section in group one, or if that section is suppressed, at the top of any GH/D/GF section before the end section. In the end 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.
13 Formatting Working with absolute formatting 6. Once the appropriate shape is created, click OK to save your changes. Scaling, cropping, and sizing objects When you create or modify a Crystal report, you can insert a variety of OLE objects. For more information about OLE objects and how to insert them in your report, see OLE. Once an OLE object is present in your report, you can scale, crop, or size it. To scale, crop, or size an object 1. Add a picture or other OLE object to your report.
Formatting Working with absolute formatting To reduce the size of the object to a half inch square, reset the Size settings to a half inch each. Using conventional accounting formats As a way of supporting the conventions used in the accounting profession, Crystal Reports lets you decide on how to display the currency symbol, negative values, and zero values on your financial reports. You can also set up your report to reverse the signs for credit and debit amounts.
13 Formatting Working with absolute formatting • In the Negatives list, how the negative values appear on your report are determined by the Windows locale settings. The negative values are represented by either a minus sign or brackets. • In the Show Zero Values as list, the dash symbol is automatically selected to represent zero values on your report. • On the Currency Symbol tab of the Custom Style dialog box, the currency symbol is positioned on the left-side of the currency and numeric values.
Formatting Working with absolute formatting 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. For a line or a box, the Repeat on Horizontal Pages option appears on the Line or Box tab. 4. Click OK to save your changes. Now, if a cross-tab or OLAP grid makes your report expand horizontally, the object you formatted is repeated on each horizontal page.
13 Formatting Working with absolute formatting 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.
Formatting Working with absolute formatting Note: White space can also be added to a section by right-clicking the shaded area to the left of the section and selecting Insert Line from the shortcut menu. The program resizes the section automatically, adding the amount of space necessary to hold a line of typical database fields. Deleting white space by resizing To delete unnecessary white space within a section, move the pointer over the lower section boundary line.
13 Formatting Working with conditional formatting To delete white space by suppressing a section 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 the section you want to suppress. 3. On the Common tab, select the Suppress (No Drill-Down) check box. 4. Click OK to return to your report. The blank section will no longer be printed.
Formatting Working with conditional formatting Crystal Reports makes it easy to apply conditional formatting in these and hundreds of other situations. With absolute formatting, you follow the "select, then apply" procedure. For conditional formatting, you follow the same general procedure, but you go a step further and set up conditions that determine whether or not the formatting will be applied. You specify these conditions using simple formulas.
13 Formatting Working with conditional formatting Conditional attribute properties A conditional attribute property tests to see which of two or more conditions is met. The program then applies the formatting appropriate to the condition. For example, assume that you want values under quota printed in red and all other values printed in black. The program tests to see whether the value is under quota or not.
Formatting Working with conditional formatting 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. You can specify a list of conditions and a property for each; you are not limited to two conditions.
13 Formatting Working with conditional formatting formula = crBlue End If Use a multi-condition If-Then-Else formula for this kind of conditional formatting. Changing fonts conditionally For memo or string fields that are based on conditions such as a parameter value, you can change the font, font style, size, and color for these fields using the Format Editor. To change fonts conditionally 1. Right-click the field you want to format to bring up the shortcut menu. 2.
Formatting Working with conditional formatting Margins based on page number The following formula checks whether a page number is even or odd and sets the margins accordingly: if the page is an even number, the margin is set to one inch; if the page is an odd number, the margin is set to two inches.
13 Formatting Working with conditional formatting Note: You cannot conditionally change the X position of line or box objects. To conditionally change the X position of an object 1. Right-click the field that you want to move conditionally, and select Size and Position. 2. Click the Conditional Formula button next to the X position value field. 3. In the Formula Workshop, enter your conditional X position formula text.
Formatting Working with conditional formatting 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. In the Sections area, click Page Footer. 4. To open the Formula Workshop, click the Formula button, located to the right of the Suppress (No Drill-Down) check box. 5.
13 Formatting Working with conditional formatting section conditionally, using the same formula that was used for suppressing the Page Footer section. 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).
Formatting Working with conditional formatting create your own conditional formatting formulas with the Formula Workshop (accessible through the Format Editor dialog box). For complete details, see Using Formulas. Conditionally formatting fields using the Highlighting Expert The Highlighting Expert is a quick alternative to the Formula Workshop; it allows you to conditionally format any of your report fields.
13 Formatting Working with conditional formatting 3. In the Item editor area, click the Value of list and select the field that you want to base your condition on. The field chosen here is the field upon which your condition is based; this field need not be the field that is being formatted. To create a condition based on the values of the field that is being formatted, select "this field" from the list. To base your condition on a different report field, select it from the list of available fields.
Formatting Working with conditional formatting 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.
13 Formatting Working with conditional formatting Note: A formula has priority over another formula when it is higher in the Items list area. 4. Click OK. 5. Click the Preview tab or refresh the report to see the highlighting changes. 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.
Formatting Using the Format Painter Note: You can only undo or redo actions in order from the most recent backward. You cannot undo an action without undoing more recent actions. Using the Format Painter Use the Format Painter to copy absolute or conditional formatting properties from one report object to one or more target objects. The Format Painter button is activated on the Standard toolbar when you select a source object in your report.
13 Formatting Using the Format Painter Source object or field Target object or field Report field in a cross-tab Report field in a cross-tab Template field Template field Text object (not in a cross-tab) Text object (not in a cross-tab) Text object in a cross-tab Text object in a cross-tab Object in an OLAP grid header Object in an OLAP grid header Line object Line object Box object Box object OLE or BLOB field OLE or BLOB field Subreport Subreport Chart, map, OLAP grid, or cross-tab C
Formatting Working with barcodes 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. When you move your mouse over your report, the cursor changes to a Stop cursor if the object or field cannot be used as a target. Note: • • You cannot apply formatting to read-only objects or fields.
13 Formatting Working with barcodes This may cause the new data to overlap existing fields if there is not enough space between fields. • Barcodes can be applied to number and string fields only. The barcode cannot be applied to Currency, DateTime, or other field types. • To undo changing a field to a barcode, you must use the Undo command right after you use Change to Barcode.
Formatting Working with barcodes • To display the barcode in a different color. Related Topics • Adding a barcode • Removing a barcode To change the appearance of a barcode 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.
13 Formatting Working with barcodes 7. Change the font and size to the desired size, and then click OK . 8. On the report, resize the field to a an appropriate length.
Charting 14
14 Charting Charting concepts Charting concepts Charting overview Crystal Reports enables you to include sophisticated, colorful charts in your reports. You can use charts any time you want to improve the usefulness of a report. For example, if you have a sales report grouped by Region with a subtotal of Last Year's Sales for each region, you can quickly create a chart that will display Sales per Region. You can chart on the following: • Summary and subtotal fields.
Charting Charting concepts 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. Advanced Use the Advanced layout when you have multiple chart values or when you do not have any group or summary fields in the report.
14 Charting Charting concepts OLAP Use the OLAP layout to chart on an OLAP grid. An OLAP chart uses the fields in the OLAP grid for its condition and summary fields. Note: Your report must include an OLAP grid before you can create an OLAP chart. 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.
Charting Charting concepts Area An area chart displays data as areas filled with color or patterns. This type of chart is best suited for showing data for a limited number of groups (for example, percentage of total sales for AZ, CA, OR, and WA). Pie A pie chart displays data as a pie, split and filled with color or patterns.
14 Charting Charting concepts pool of customer information. Viewing all of this data on an XY Scatter chart would allow you to speculate as to why certain products were selling better than others or why certain regions were purchasing more than others. Radar A radar chart positions group data, such as countries or customers, at the perimeter of the radar. The radar chart then places numeric values, increasing in value, from the center of the radar to the perimeter.
Charting Charting concepts Gantt A Gantt chart is a horizontal bar chart often used to provide a graphical illustration of a schedule. The horizontal axis shows a time span, while the vertical axis shows a series of tasks or events. Horizontal bars on the chart represent event sequences and time spans for each item on the vertical axis. You should use only date fields when creating a Gantt chart.
14 Charting Creating charts Drill-down with charts Not only is charting a means of presenting data—it is also an analysis tool. Move your cursor over a section of the group chart 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 chart. Drill-down with legends If the chart consists of one or more group fields, you can use the chart legend to drill down on individual groups.
Charting Creating charts Charting on details or formula fields (Advanced layout) The Advanced layout allows you to create a chart based on specific values. Since charts are a good way to display summarized information, they are often based on a summary field in your report. With an Advanced layout, you can create a chart without the need for a summary field by using values that appear in the Details section of your report.
14 Charting Creating charts 3. On the Type tab, in the Chart type list, select a chart type. Click the chart subtype that best illustrates your data. See Chart types. 4. Click the Data tab. 5. In the Layout area, click Advanced, if it is not already selected. 6. In the Data area, specify the database fields you want to use as conditions. You can select "On change of" from the list, then add up to two database fields in the box underneath the list.
Charting Creating charts 8. If you do not want Crystal Reports to automatically summarize the chart values for a formula field, select the Don't summarize check box. 9. 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. 11. Accept the default title information or add new titles to your chart. 12. Click OK. Note: When your chart is inserted, it may cover a portion of the report.
14 Charting Creating charts default chart, and then click Chart Expert. The Chart Expert dialog box appears. 3. On the Type tab, in the Chart type list, select a chart type. Click the chart subtype that best illustrates your data. See Chart types. 4. Click the Data tab. 5. In the Layout area, click Group, if it is not already selected. 6.
Charting Creating charts Tip: Another way to create a chart is to click the Insert Chart button on the Insert Tools toolbar. 3. Drag the frame to the desired position in the Report Header. The Chart Expert dialog box appears. Note: You may see a default chart in the Report Header section rather than the Chart Expert dialog box. To select a different chart type, right-click the default chart, and then click Chart Expert. The Chart Expert dialog box appears. 4.
14 Charting Creating charts 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. To chart on an OLAP cube 1. Select the OLAP grid on which you want to chart. 2. On the Insert menu, click Chart. An object frame appears in the Report Header area.
Charting Working with charts 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.
14 Charting Working with charts Editing charts using the Chart Options menu items 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.
Charting Working with charts The options on the Custom tab represent directory locations under \Program Files\Business Objects\Common\4.0\ChartSupport\Templates where custom chart files are stored. Note: The custom charts are available only if you selected Custom Charting when installing Crystal Reports. 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 >.
14 Charting Working with charts To choose a viewing angle for a 3D chart • Right-click your chart, then select 3D Viewing Angle from the menu. Using the zooming features with bar and line charts On the Preview tab, you can find commands for zooming bar charts and line charts within your report. You have the ability to zoom in and out on these chart types at any time, with each time being referred to as instance-specific.
Charting Working with charts Auto-arranging charts If you move or resize chart objects on the Preview tab, select the Auto-Arrange Chart feature to reset the chart. To auto-arrange a chart 1. On the Preview tab, right-click the chart to bring up the shortcut menu. 2. On the shortcut menu, click Auto-Arrange Chart. Crystal Reports resets the chart to its original size and position. Formatting charts Changing the border of a chart 1.
14 Charting Working with charts Note: • • • • The option to apply conditional formatting is not available for every chart type. An area chart must have two "On change of" values for conditional formatting to appear. If your chart type is line, the chart must have data markers before you can see conditional formatting. After you apply conditional formatting, you must select "Color by Group" on the Look tab of the Chart Options dialog box before you will be able to see your formatting.
Charting Working with charts To underlay a chart 1. Create your chart and place it in the Report Header section. See Creating charts. 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. In the Sections area, click Report Header, then select the Underlay Following Sections check box. 4. Click OK. Crystal Reports returns you to the report.
14 Charting Working with charts 388 Crystal Reports 2008 SP3 User's Guide
Mapping 15
15 Mapping Mapping concepts Mapping concepts Mapping overview With Crystal Reports, you can include geographic maps in reports. Maps help you analyze report data and identify trends more efficiently. For example, you could create a map that shows sales by region. You would then be able to: • Use one of the five map types to analyze the data. • Adjust the appearance and organization of the map (allowing you to better identify trends). • Drill down on the map regions to view underlying data.
Mapping Mapping concepts 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. Note: If there is no map associated with the data you specify, then an empty block will appear unless the section that the map is placed in has been formatted to suppress if blank.
15 Mapping Mapping concepts very distorted representation of the data. A more useful map would have ranges like 0-5000, 5000-10000, 10000-15000, 15000-20000, and over 20000. It is important to carefully define your ranges. Note: The end value for each division is repeated as the start value for the next division; the actual value is included in the group it starts. That is, the ranges in the previous example are actually: 0-4999, 5000-9999, and so on.
Mapping Mapping concepts dots. However, in some coastal states, such as South Carolina, you might be able to count the dots on the map, since their dispersal would be fairly wide. The purpose of a Dot Density map is to provide an overall impression of the distribution of the specified item. A Dot Density map is much like a nighttime satellite photo of the United States, where you can see the lights of all the cities.
15 Mapping Mapping concepts pie) of electricity because of the hydropower in that region, while Idaho would probably use a high percentage (a large slice of the pie) of natural gas. You can use this map type to compare the distribution of several items within a particular region. You can also specify that the pie charts be sized proportionately so that, as with the symbols in a Graduated map, the pie charts will appear in various sizes, depending on the underlying data values.
Mapping Creating maps 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}.
15 Mapping Creating maps a map showing last year's sales for the countries, the Last Year's Sales field would be the value. To map on a details field 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. On the Data tab, in the Layout area, click Advanced, if it is not already selected.
Mapping Creating maps 3. In the Placement area, in the Place map list, specify how often your map appears on the report, then click Header or Footer to specify where to place your map. 4. In the Data area, add the database field you want to base your map on to the Geographic field. The arrow buttons on the Map Expert dialog box allow you to move fields from one list to the other. Single arrows move only the selected field; double arrows move all fields at the same time.
15 Mapping Creating maps 9. In the Options area, apply formatting options to your map. 10. Click the Text tab. 11. In the Map title field, enter a title for your map. 12. In the Legend area, you can click one of the following options: • Full legend to show a detailed legend on your map. • Compact legend to show a condensed legend on your map. • No legend to exclude the legend from your map. 13.
Mapping Creating maps Mapping on group fields (Group layout) To map on a group, you can use the Group layout, in which you show a summary (such as Last Year's Sales) on change of a geographic field (such as Region). In order to create a map using the Group layout, you must have at least one group and at least one summary field for that group. 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.
15 Mapping Creating maps 11. 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. Your map is placed in the Header or Footer section of the report, depending on your selection in Step 3. Mapping on Cross-Tab summaries (Cross-Tab layout) With the Cross-Tab layout, you can map on a Cross-Tab summary field.
Mapping Creating maps 6. In the Map on list, click a summary field to supply the numeric data for your map. 7. Click the Type tab. 8. Click the map type that best illustrates your data. See Map types. 9. 10. 11. 12. • If you plan to map on only one field, be sure to select None from the "Subdivided by" list, then choose either the Ranged, Dot Density, or Graduated map type.
15 Mapping Creating maps 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. On the Data tab, in the Layout area, click OLAP, if it is not already selected. 3. In the Placement area, click Header or Footer to specify where to place your map. 4. In the Data area, click the On change of list and select the field on which you want to base your map. Note: This field should contain geographic information, such as Country. 5.
Mapping Working with maps 12. Click OK. Your map is placed in the Header or Footer section of the report, depending on your selection in Step 3. Working with maps Once you have created a map, you may want to add a new title, headings, and a legend, and you may want to change fonts, or even the map type. Crystal Reports provides many options for working with your existing maps.
15 Mapping Working with maps 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. For example, if you want to see how the data of a Ranged map would look if it were presented in a Dot Density style, you can rearrange the map without having to return to the Map Expert and rework the format. To change the map type 1. On the Preview tab, right-click the map to bring up the shortcut menu. 2.
Mapping Working with maps To change map layers 1. On the Preview tab, right-click the map to bring up the shortcut menu. 2. On the shortcut menu, and choose Layers. The Layer Control dialog box appears. 3. In the Layers list, click Up or Down to arrange the order of the map layers. Be aware that some map layers contain non-transparent sections that can obscure the detail of other layers.
15 Mapping Working with maps 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. Resolving data mismatches Sometimes the map you are working with uses a different geographic name from the one used in the database.
Mapping Working with maps Changing the geographic map If you prefer to have your data values presented with a different geographic map, specify your changes using the Change Map tab of the Resolve Map Mismatch dialog box. 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.
15 Mapping Working with maps Note: • • If a map occurs once for each instance of a group, any panning or zooming settings you specify are instance-specific. In other words, if you zoom in on the map in one group header, this setting is only for that group header. None of the headers have been changed. This way you can set each map to different settings. If the Save Data with Report option is selected from the File menu, your panning and zooming settings will be saved with the report.
Mapping Working with maps You have the option of hiding or showing the Map Navigator. 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. 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.
15 Mapping Working with maps Formatting Maps Changing the border of a map 1. On the Design or Preview tab, right-click the map to bring up the shortcut menu. 2. On the shortcut menu, click Format Map. 3. On the Format Editor dialog box, click the Border tab to see its options. 4. Change the line style, color, background color, and add or remove a drop shadow from the map border. 5. Click OK to save your changes. Crystal Reports returns you to the report and implements your changes.
Mapping Working with maps 4. Click OK. Crystal Reports returns you to the report. Your map will now underlay the sections below it. 5. If necessary, move or resize the map.
15 Mapping Working with maps 412 Crystal Reports 2008 SP3 User's Guide
OLE 16
16 OLE OLE overview OLE overview Object Linking and Embedding (OLE) enables you to insert objects (OLE objects) into a report from other applications (OLE server applications) and then use those applications from within Crystal Reports to edit the objects if necessary. If you were not using OLE, you would have to exit Crystal Reports, open the original application, change the object, return to Crystal Reports, delete the object originally inserted, and then insert the newly revised object.
OLE OLE overview 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 Server Application An OLE server application is an application that allows its documents to be inserted into an OLE container document as OLE objects.
16 OLE Inserting OLE objects into reports in the server document is changing and you want the object in your report to be updated when you open the report. General OLE considerations There are several points to keep in mind when utilizing OLE functionality. • 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.
OLE How OLE objects are represented in a report To copy and paste OLE objects This procedure assumes that you have Microsoft Excel or another spreadsheet program on your computer that is an OLE server application. 1. Open both Crystal Reports and Microsoft Excel. 2. Open an existing report in Crystal Reports. 3. Create a spreadsheet in Microsoft Excel by typing the numbers one through ten (1-10) in cells A1 through A10. 4.
16 OLE Editing OLE objects in reports • If the object was created from an existing file, the data from that file (or an icon) is displayed in the report. This data can be edited by double-clicking the object or its icon. • If you are creating a new object, the application for the object type that you chose will open, and you can begin designing the object. When you are finished, close or exit the application. The object (or its icon) will be displayed in the report.
OLE Working with static OLE objects • If the object is linked, the Edit menu displays commands for that type of linked object. Commands on the context menus change in a similar fashion. These dynamic commands are provided to give you more control when working with OLE objects.
16 OLE Working with static OLE objects Inserting a static OLE object 1. Open or create a report that you want to insert a static OLE object in. 2. On the Insert menu, click Picture. Tip: Another way to do this is to click the picture button on the Insert Tools toolbar. The Open dialog box appears. 3. Select a picture (.bmp, .tiff, .jpg, .png, or . wmf) from a directory that you have access to. 4. Click Open. 5. When the object frame appears, place the picture in your report. 6.
OLE Working with static OLE objects • • When you schedule a report that contains a dynamic static OLE object in BusinessObjects Enterprise, the instances that are created contain the version of the static OLE object as it existed in your Crystal report when you scheduled it. BusinessObjects Enterprise does not update the object dynamically for each instance.
16 OLE Working with embedded vs. linked objects 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. Right-click the object you have just converted. The command on the shortcut menu is now Bitmap Image Object. 4. Double-click the object. Microsoft Paint, or another graphics application installed on your machine, opens. Working with embedded vs.
OLE Working with embedded vs. linked objects 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. Right-click this picture and observe that it is also identified as an OLE Object on the shortcut menu.
16 OLE Working with embedded vs. linked objects 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 Integrating Shockwave Flash (SWF) objects Integrating Shockwave Flash (SWF) objects Flash technology is a popular way to add animation to web pages. Flash files can be viewed in a standalone Flash Player, inserted into web pages or inserted into Crystal Reports. Flash files have a .swf extension. To modify an SWF, you will need the original source file and a Flash authoring tool. You can either embed an SWF or link to an SWF in any section of a Crystal Reports report.
16 OLE Integrating Shockwave Flash (SWF) objects The only format that will display SWF objects in exported reports is PDF. However, if the object contains data binding to the report, the default image is displayed instead of the SWF. Printing Shockwave Flash (SWF) objects Crystal Reports 2008 allows you to print SWF objects in your report without the need to first export the report to PDF.
OLE Integrating Shockwave Flash (SWF) objects Note: In the "Design" view, if you choose to insert an SWF that contains an embedded movie or sound, you will hear the sound, but you will not see the movie play. This may cause an echo when you switch to the "Preview" view. Please consult with your System Administrator to find out how to resolve this issue, or check the Business Objects support site for more information. To add an Xcelsius SWF object to a report 1. On the Insert menu, click Flash.
16 OLE Integrating Shockwave Flash (SWF) objects A user interface that is like the cross-tab expert allows you to map rows, columns, and summarized fields to Xcelsius variables. • Bind to an external cross-tab. You can bind data to a cross-tab within the report. • Bind to a single value. You can bind data to one detail item in the report. Select fields from the tables to bind to existing variables in the "Flash Data Expert". Note: If a recurring field is selected, only the first instance is sent.
Cross-Tab Objects 17
17 Cross-Tab Objects What is a Cross-Tab object? What is a Cross-Tab object? A Cross-Tab object is a grid that returns values based on the criteria you specify. Data is presented in compact rows and columns. This format makes it easy to compare data and identify trends. It is made up of three elements: • Rows • 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 Cross-Tab example • 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.
17 Cross-Tab Objects Cross-Tab example The first way of looking at the data is in the most basic of all reports, a columnar report with no grouping or sorting. Report of order data - no sorting/grouping 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.
Cross-Tab Objects Cross-Tab example 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. Report of order data - grouped by region This report uses the data seen in the first report, but here the data is grouped by region. All the orders in each region are grouped together, but each regional group contains orders for different types of locks.
17 Cross-Tab Objects Cross-Tab example Report of order data - grouped by product This report groups the data by product. Each group displays all the orders for a specific product. At first it appears that this might be useful, but then it becomes clear that each product group includes orders from several different regions. The information is helpful, and it brings you closer to your goal, but you are still a long way from having the information you need.
Cross-Tab Objects Cross-Tab example regions in each product group, then it seems to make sense to combine the two. Doing that, you group first by Region and then by Product. Each group contains orders for one product for one region. But the data is all spread out and remains difficult to analyze. 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.
17 Cross-Tab Objects Cross-Tab example 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 Creating a Cross-Tab report Creating a Cross-Tab report This section provides you with the steps to create a Cross-Tab object in a new report and how to add a Cross-Tab object to an existing report. Things to keep in mind when using Cross-Tab objects: • You can drag report fields into or out of Cross-Tab objects. • You can have multiple rows, columns, and summarized fields. • You can use print-time formulas as your rows or columns.
17 Cross-Tab Objects Creating a Cross-Tab report 3. On the Insert menu, click Cross-Tab. Tip: Another way to do this is to click the Insert Cross-Tab button on the Insert Tools toolbar. An object frame appears with the Arrow cursor as you drag the Cross-Tab object into the report. 4. Place the object frame in an empty area in the Group Header #1 and click to release it. An empty Cross-Tab object appears in your report. You can drag fields from your report or from the Field Explorer into your Cross-Tab.
Cross-Tab Objects Creating a Cross-Tab report 9. Finally, drag Last Year's Sales to the Insert Summary cells of the Cross-Tab, and click to release it. Tip: Notice that all of the cells change color when you position the field over them. 10. Click the Preview tab to see your Cross-Tab. Specifying the data source 1. On the Data screen, locate the data source you want to use. This example uses the Xtreme Sample Database. For information on how to select a data source, see Selecting the data source. 2.
17 Cross-Tab Objects Creating a Cross-Tab report Modifying the links 1. In this example, check to make sure that the Customer table is linked to the Product table via the Orders and Orders Detail tables. If you do not like the linking that Crystal Reports has automatically suggested, change it by clicking Clear Links, and then select the fields that you would like to link together. 2. Click Next. The Cross-Tab screen appears. Adding a chart 1.
Cross-Tab Objects Creating a Cross-Tab report 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. In the filter values list that appears, select the value(s) you want to filter on and click Add. For this example, choose AZ, CA, and OR from the drop-down list.
17 Cross-Tab Objects Creating a Cross-Tab report 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. 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.
Cross-Tab Objects Creating a Cross-Tab report This example uses the Group By Intervals.rpt included in the Feature Examples directory. 2. Click the Design tab. 3. On the Insert menu, click Cross-Tab. Tip: Another way to do this is to click the Insert Cross-Tab button on the Insert Tools toolbar. An object frame appears with the Arrow cursor as you drag the Cross-Tab object into the report. 4. Place the object frame in an empty area in the Report Header and click to release it.
17 Cross-Tab Objects Creating a Cross-Tab report • Click Group Options. • Select in specified order from the drop-down list. • Type North America for the name of the Named Group. • Click New. • Choose is one of from the drop-down list. • Select Canada, Mexico, and USA from the drop-down list. • Click OK to close the Define Named Group dialog box. • Click OK to close the Cross-Tab Group Options dialog box. Choosing a predefined style 1. Click the Style tab. 2. Select a style.
Cross-Tab Objects Working with Cross-Tabs Finishing the Cross-Tab 1. Click OK. 2. On the Report menu, click Refresh Report Data. The updated report appears. Working with Cross-Tabs This section describes ways you can work with a Cross-Tab once you've added it to your report. Showing values as percentages 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.
17 Cross-Tab Objects Working with Cross-Tabs Abbreviating large summarized fields Because the values in a Cross-Tab's summarized fields are often very large, Crystal Reports lets you abbreviate such values. Note: To complete this procedure, the report you're working with must include the custom function called cdFormatCurrencyUsingScaling. The sample report called Custom Functions.rpt includes this function. Sample reports are located on the Business Objects Technical Support web site http://sup port.
Cross-Tab Objects Working with Cross-Tabs 6. Click Check to identify any errors in the formula. 7. Fix any syntax errors the Formula Checker identifies. 8. When the formula has the correct syntax, click Save and close on the Formula Workshop toolbar. You return to the Format Editor dialog box. 9. Click OK to return to your Cross-Tab. Your summarized field values appear abbreviated as defined.
17 Cross-Tab Objects Working with Cross-Tabs 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. Using running totals in Cross-Tabs Running Total fields can be useful as summary fields in Cross-Tab objects. Create your Cross-Tab as shown in Creating a Cross-Tab report, but choose a running total field as your Summary Field.
Cross-Tab Objects Working with Cross-Tabs 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. If you add a Group Sort (top or bottom N sort) based on a Running Total field, the sort is carried out on the running total values as shown in the Cross-Tab's Row/Column Total areas—it is not based on the summary field's totals.
17 Cross-Tab Objects Formatting Cross-Tabs 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.
Cross-Tab Objects Formatting Cross-Tabs 2. Click the Customize Style tab. 3. Click the row (in the Rows area) or column (in the Columns area), and select a color from the Background Color drop-down list. 4. Click OK to return to the Cross-Tab. The row/column is formatted as specified. 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.
17 Cross-Tab Objects Formatting Cross-Tabs • Empty rows and columns. • Row and column grand totals. • Subtotals and their labels. To suppress empty rows and columns 1. Right-click the blank top-left area of the Cross-Tab and select Cross-Tab Expert from the shortcut menu. The Cross-Tab Expert appears. 2. Click the Customize Style tab. 3. 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.
Cross-Tab Objects Advanced Cross-Tab features 2. Click the Customize Style tab. 3. Click the field whose subtotal you want to suppress. The Suppress Subtotal and the Suppress Label check boxes become active. 4. In the Group Options area, select the Suppress Subtotal check box. 5. Click the Suppress Label check box to suppress the label associated with subtotal. 6. Click OK.
17 Cross-Tab Objects Advanced Cross-Tab features Calculated Members You can add rows or columns to your Cross-Tab by inserting a Calculated Member. These rows or columns can contain custom calculations (for example, the difference in sales between two regions) or can be used entirely for formatting purposes (for example, inserting a blank row every three lines to increase readability).
Cross-Tab Objects Advanced Cross-Tab features A row or column that displays the results of the selected calculation appears in your Cross-Tab . To add a blank row or column to your Cross-Tab 1. In your Cross-Tab, right-click the header immediately preceding the desired location of your blank row or column. 2. On the shortcut menu, point to Calculated Member, and depending on whether you have clicked a row or a column, click either Insert Row or Insert Column.
17 Cross-Tab Objects Advanced Cross-Tab features The pre-defined formulas are as follows: • Sum of • Difference of • Product of • Quotient of Crystal Reports is equipped with a variety of functions to help you design your own formulas. To edit a Calculation Formula 1. In the Calculated Member you would like to edit, right-click any cell except for the header. 2. On the shortcut menu, point to Calculated Member, and click Edit Calculation Formula. The Formula Workshop appears. 3.
Cross-Tab Objects Advanced Cross-Tab features Value Formulas If you want to reference a Calculated Member in a formula, you must assign a value to it. Value Formulas assign values to Calculated Members for this purpose. To edit a Group Value 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 depending on whether you have clicked a row or a column, click Edit Row Value Formula or Edit Column Value Formula.
17 Cross-Tab Objects Advanced Cross-Tab features 3. In the Formula Workshop, enter your desired formula. For example, the following formula inserts a Calculated Member after the country field for Canada: GetColumnGroupIndexOf(CurrentRowIndex) = 1 and GridRowColumnValue("Customer.Country") = "Canada" 4. Click Save and close to return to your report. The Calculated Member appears in the locations specified by the Insertion Formula.
Cross-Tab Objects Advanced Cross-Tab features 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.
17 Cross-Tab Objects Advanced Cross-Tab features 460 Crystal Reports 2008 SP3 User's Guide
Building Queries 18
18 Building Queries Connecting to a universe Connecting to a universe You access the Business Objects Query Panel in Crystal Reports through the Database Expert. Once you have selected Universes from the Create New Connection node, you are prompted to log onto BusinessObjects Enterprise, after which you can select a universe and design your query. Crystal Reports can connect to multilingual universes and report off of them, but it cannot take advantage of the multilingual attributes.
Building Queries Defining the data selection for a query 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.
18 Building Queries Defining the data selection for a query Objects that appear in the Result Objects pane become fields that you report on in your Crystal report. 3. Repeat the previous step for each object that you want to include in the query. 4. Select an object in the left-hand pane, or in the Result Objects pane, and drag it into the Query Filters pane. Add filters to the Query Filters pane to restrict the data your query returns based on the object that you selected.
Building Queries Defining the data selection for a query Quick reference to objects An object is a named component that maps to data or a derivation of data in the database. For example, an object may represent a column in a database table, or it may be the summary of the values in a column. You use objects in a query to retrieve data for your reports. For example, some of the objects in a human resources universe might be Names, Addresses, and Salaries.
18 Building Queries Editing an existing query Object Examples Description Detail This object provides descriptive data about a dimension. A detail is always attached to the dimension for which it provides additional information. For example, Age is a detail object that is associated with the Customer dimension. Address provides additional information on customers. Measure This object retrieves numeric data that is the result of calculations on data in the database.
Building Queries Viewing the SQL behind a query 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 BusinessObjects Enterprise if you have not done so already. For information about how to log on to BusinessObjects Enterprise, see Working with Enterprise folders . 3. In the Query Panel, edit your query as necessary. 4. When you have finished editing your query, click OK. 5.
18 Building Queries Query filters and prompts 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.
Building Queries Query filters and prompts 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. As a user of the Query Panel, you cannot view the component parts of predefined filters, neither can you edit them.
18 Building Queries Query filters and prompts Tip: Prompts allow multiple users view a single report but specify a different sub-set of the database information. Prompts also reduce the time it takes for the data to be retrieved from the database. 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.
Building Queries Query filters and prompts Combining query filters and prompts You can apply multiple filters and prompts on a single query. To combine filters and/or prompts 1. Create each query filter and/or prompt. 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.
18 Building Queries Query filters and prompts Retrieve this data Example Select Customers who ordered supplies in Q1 and in Q2 (the data you reData true for both filters. trieve will include: cus- And tomers who placed orders in both Q1 and Q2). Customers who ordered supplies in: Q1 or Q2 (the data you retrieve will include: customers Data true for any one of who placed orders in Or the filters. Q1only; customers who placed orders only in Q2, and; customers who placed orders in both Q1 and Q2).
Building Queries Query filters and prompts Values to retrieve Example Select Filter created Values different Retrieve data for from a value you all quarters exDifferent from specify. cept Q4. Different from Q4 Values greater Retrieve data for than a value you customers aged Greater than specify. over 60. Greater than 60 Retrieve data for Values greater revenue starting Greater than or than or equal to a from $1.5M up- equal to value you specify. ward.
18 Building Queries Query filters and prompts Values to retrieve 474 Example Select Filter created Retrieve all the weeks of the Values outside year, except for the range of two weeks 25 through Not between values you speci36 (week 25 and fy. week 36 are not included). Not Between 25 and 36 Retrieve data for Values that are only the following the same as sevcountries: the US, In list eral values you Japan, and the specify. UK.
Building Queries Query filters and prompts Values to retrieve Example Retrieve cusValues that intomers whose cludes a specific date of birth is string. 1972. Select Filter created Matches pattern Matches pattern, '72' Retrieve cusValues that don't tomers whose Different from include a specific date of birth is not pattern string. 1972. Different from pattern, '72' Retrieve Telco Values that corre- customers who spond to two val- have both a fixed Both ues you specify.
18 Building Queries Filtering data using subqueries and database ranking 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. To remove a query filter • Drag the filter you want to remove and drop it onto the left-hand pane.
Building Queries Filtering data using subqueries and database ranking • They allow you to restrict the values returned by the subquery with a WHERE clause. Subqueries allow you to pose complex questions that are difficult or impossible to formulate with simple query filters.
18 Building Queries Filtering data using subqueries and database ranking 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.
Building Queries Filtering data using subqueries and database ranking 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.
18 Building Queries Filtering data using subqueries and database ranking What is a database ranking? Database rankings allow you to answer questions like "what are the top three customers, based on the revenue they generated for each year?" at the query level, without the need to return data that falls outside the ranking to Crystal Reports. You can then filter this data using a ranking. When you rank data you sort and filter it according to ranking criteria.
Building Queries Filtering data using subqueries and database ranking To create a database ranking 1. Add the objects that you want to appear in your query to the Result Objects pane of the Query Panel. 2. Select the object that you want to rank by. 3. Click Add a database ranking on the toolbar. The ranking outline appears in the Query Filters pane. The object that you selected appears as the ranking dimension, and the first measure in the query appears as the ranking measure in the "Based on" list.
18 Building Queries Filtering data using subqueries and database ranking Parameter Description Ranking order. Top/Bottom Top - ranks in descending order. Bottom - ranks in ascending order. 482 Number of records The number of records to return in the ranking. For example, the top 10. Ranking object The object used in the ranking. For example, if the object is Region and the ranking is Top 10, the ranking returns the top 10 regions. Based on The measure by which the ranking object is ranked.
Building Queries Filtering data using subqueries and database ranking Parameter Description WHERE condition (optional) Additional restriction on the values returned in the ranking that appears below the other parameters. For example, a ranking of regions with a condition that restricts Country to "USA" ranks only those regions in the USA.
18 Building Queries Filtering data using subqueries and database ranking 484 Crystal Reports 2008 SP3 User's Guide
Creating and Updating OLAP Reports 19
19 Creating and Updating OLAP Reports OLAP reporting with Crystal Reports OLAP reporting with Crystal Reports Although relational databases such as SQL servers and PC databases are the most common sources of data, Online Analytical Processing (OLAP) and Multi-Dimensional Data are rapidly becoming the popular data-storage and analysis formats. Crystal Reports provides the same access and reporting features for OLAP data sources that it provides for relational data.
Creating and Updating OLAP Reports Creating an OLAP report 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). When the grid is long, the program processes it on a page-by-page basis. When the grid is wide, the program must gather all the data before it can display a page.
19 Creating and Updating OLAP Reports Creating an OLAP report 1. Click Select Cube. 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.
Creating and Updating OLAP Reports Creating an OLAP report 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.
19 Creating and Updating OLAP Reports Creating an OLAP report 8. Select a dimension in either the Rows or the Columns areas and click Create/Edit Parameter to create a parameter for use with the dimension. The Create Parameter Field dialog box appears. 9. Select default values, prompting text, and other options, and then click OK. Note: • • Once you have created a parameter, access to the Member Selector dialog box is disabled for the dimension until you delete the parameter.
Creating and Updating OLAP Reports Creating an OLAP report To specify a slice 1. To determine the member that is going to be used as the slice, select a dimension in the Slice list and click Select Slice, or double-click a dimension in the Slice list. The Member Selector dialog box appears. 2. Select the appropriate member, expanding the structure if necessary. 3. Click OK. 4. Click Next if you do not want to add a page. The Style screen appears. To add a page 1.
19 Creating and Updating OLAP Reports Creating an OLAP report Applying a predefined style 1. Select a predefined style for the grid from the list. Tip: If you do not want to use a predefined style, click Next. A preview of the color scheme you select appears on the right. 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.
Creating and Updating OLAP Reports Updating an OLAP report 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. In the On change of list, select the dimension you want to base your chart on. 4. If necessary, in the Subdivided by list, click a secondary row or column you want to base your chart on.
19 Creating and Updating OLAP Reports Updating an OLAP report • Removing dimensions contained in the report that are not in the cube. • Removing fields referred to explicitly in the design of the report, such as a formula. Note: If a report contains subreports or grids, and the database used for them has changed name or location, you must update each subreport or grid. To update a cube location in an OLAP grid object 1. Select the grid by clicking on the border. 2.
Creating and Updating OLAP Reports Formatting data in an OLAP grid that your data remains synchronized. The program can change the location automatically, or you can change it manually in the Set Datasource Location dialog box. Updating the database location 1. In the Set Datasource Location dialog box, expand the Create New Connection folder in the "Replace with" area. 2. Expand the OLAP folder and search for the new cube location.
19 Creating and Updating OLAP Reports Formatting data in an OLAP grid • Use the Formula Workshop to write your own conditional formatting formulas with Crystal or Basic syntax. The Formula Workshop gives you maximum control over the formatting of the OLAP grid. To access the Formula Workshop, open the Format Editor and click the appropriate Formula button. For more details, see Working with conditional formatting. • Use the OLAP Expert to reformat the entire OLAP grid.
Creating and Updating OLAP Reports Formatting data in an OLAP grid 2. In the Group Options area, select the color from the Background Color list. 3. Click OK if you have finished customizing your grid. Creating an alias for a dimension You can create aliases to shorten long dimension names. This is useful when you plan on referring to a dimension in a conditional formatting formula (by using the GridRowColumnValue function). 1. On the Customize Style tab of the OLAP Expert, select the dimension. 2.
19 Creating and Updating OLAP Reports Changing the view of OLAP data Formatting grid lines In the Format Grid Lines dialog box, you can define whether the lines show or not, as well as the color, style, and width. 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.
Creating and Updating OLAP Reports Changing the view of OLAP data Search the online help for "Using the OLAP Worksheet" to learn more about the functionality available on the Cube View tab. To show or hide dimension members 1. Right-click the dimension whose members you want to show or hide. 2. On the shortcut menu, click Expand Member or Collapse Member. The dimension expands to show its members, or collapses to hide them.
19 Creating and Updating OLAP Reports Changing the view of OLAP data To change the display format for member names 1. Right-click the dimension name. 2. On the shortcut menu, point to Display Members Using, and then click one of the display options: • Caption • Name • Caption : Name • Unique Name For information about names and captions, search the online help for "Changing member captions." To alter the data displayed in the OLAP grid 1.
Creating and Updating OLAP Reports Sorting and filtering OLAP grid data 2. Right-click the dimension and, on the shortcut menu, click Reorder Displayed Members. Note: You cannot reorder members for dimensions that you have created row or column parameters for. 3. In the Reorder Displayed Members dialog box, select fields and click the Up and Down arrows to rearrange the members that are displayed in the grid. 4. Click OK to effect your changes and return to the OLAP grid.
19 Creating and Updating OLAP Reports Sorting and filtering OLAP grid data 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.) In the next example, the Budget column remains sorted in ascending order; in this case, however, the Break Hierarchies option is selected.
Creating and Updating OLAP Reports Sorting and filtering OLAP grid data Tip: To locate a sorted row or column, move the mouse pointer over the OLAP grid. When you reach a sorted member, the pointer turns into a double-arrow. 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.
19 Creating and Updating OLAP Reports Adding calculations to OLAP grids You can filter data by "Actual values," or by selecting "Top/bottom n" or "Top/bottom n%." 4. Use the Filter Definition options to specify which rows or columns you want to exclude or display. 5. Click OK to add the filter and return to the OLAP grid. 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.
Printing, Exporting, and Viewing Reports 20
20 Printing, Exporting, and Viewing Reports Distributing reports Distributing reports Crystal Reports enables you to distribute your report using a variety of methods. 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 Distributing reports 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.
20 Printing, Exporting, and Viewing Reports Distributing reports between these attributes and other objects. Depending on the format you choose, it may not be possible for the program to preserve all layout and formatting perfectly, but page-based formats, in general, preserve these properties as closely as possible. With record-based formats, the emphasis is on data rather than the layout and formatting.
Printing, Exporting, and Viewing Reports Distributing reports MHTML MHTML export format can be used to send HTML files in email messages. It can also be used to save a web page with all its pictures, applets, and so on in one single file in MHTML format. Microsoft Excel (97-2003) Microsoft Excel format is a page-based format. This format converts your report contents into Excel cells on a page-by-page basis. Contents from multiple pages are exported to the same Excel worksheet.
20 Printing, Exporting, and Viewing Reports Distributing reports format is intended for use in applications such as fill-out forms where the space for entering text is reserved as empty text objects. Almost all of the formatting is retained in this export format. Text objects cannot, however, be placed outside the left edge of the page. Therefore, if you have text objects in your report that are placed before the left edge of the printable area, they will be pushed right.
Printing, Exporting, and Viewing Reports Distributing reports Report Definition The Report Definition format exports your report to a text file that contains a brief description of the report's design view. This format is maintained only for compatibility with Crystal Reports 5.0. Rich Text Format (RTF) The Rich Text Format (RTF) format is similar to the Microsoft Word (97-2003) format. Separated Values (CSV) The Separated Values format is a record-based, data-exchange format.
20 Printing, Exporting, and Viewing Reports Distributing reports report's layout. Text format assumes that a font of a constant dimension is used throughout the export. The Characters Per Inch (CPI) option specifies the number of characters that can be fit in a linear inch of horizontal space, and thus determines the dimension of the font. 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.
Printing, Exporting, and Viewing Reports Distributing reports • • • When exporting in ODBC format, Application and Disk file are the same. You will need to specify a file path if you are exporting in HTML format. The file name of the report and the file name of the temporary file cannot be the same. The sections below provide instructions on how to export a report to the Microsoft Excel (XLS) format for each of the different destination types. To export to an application 1.
20 Printing, Exporting, and Viewing Reports Distributing reports The program exports the report and opens it in the appropriate application. In this example, Microsoft Excel opens with the exported data. Exporting to a disk file If you export to a disk file, the program saves the report to the disk or diskette you have specified. To export to a disk file 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 Distributing reports 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. Open the report you would like to export. 2.
20 Printing, Exporting, and Viewing Reports Distributing reports 10. When the Select a Folder dialog box appears, select the folder in the profile in which you want the report to appear, and click OK. The report is exported to the Microsoft Exchange folder you selected. The exported report can now be accessed through the Microsoft Exchange client. Exporting to Lotus Domino You must have Version 3.0 or later of the Lotus Domino client. You will also require, at a minimum, depositor access.
Printing, Exporting, and Viewing Reports Distributing reports The Select Database dialog box appears. 8. Double-click the Lotus Domino server you would like to export your report to. The file name defaults. 9. Select the database you would like to export the report to. 10. Click OK. The Comments dialog box appears. 11. Type in any comments that are to appear when another user selects your report from the Lotus Domino Desktop. 12. Click OK. The export process begins.
20 Printing, Exporting, and Viewing Reports Distributing reports The Number and Date Format Settings dialog box appears. 6. Select the check boxes as required, then click OK. 7. Enter password information as required, then click OK. The Send Mail window appears. 8. Complete the fields as required, then click Send. Exporting to MAPI (Microsoft Mail) Note: This option works only if you have a mail client installed (Microsoft Outlook, Microsoft Mail, or Exchange).
Printing, Exporting, and Viewing Reports Distributing reports Tip: Click Cancel Exporting to cancel the export process. Exporting to HTML By exporting reports in HTML format, Crystal Reports provides you with a new option for rapid, convenient distribution of important company data. Once exported, your reports become accessible with many of the most popular Web browsers, including Netscape and Microsoft Internet Explorer. To export to HTML 1.
20 Printing, Exporting, and Viewing Reports Distributing reports sections and set the top and bottom page margins to zero in the Page Setup dialog box. 5. Select an existing directory, or create a new directory for your report. 6. To navigate through separate HTML files, click the Page navigator option. 7. Click OK. The program exports the report to HTML format. Exporting to an ODBC data source Crystal Reports allows you to export reports to any ODBC data source.
Printing, Exporting, and Viewing Reports Distributing reports To export to an ODBC data source 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 ODBC. Note: Destination is ignored when you are exporting a report to an ODBC data source.
20 Printing, Exporting, and Viewing Reports Distributing reports • Are running Windows 2000 (or higher) or have Office 2000 (or higher) installed. • Have access to a web server that is configured to support Web Folders. • Add a Web Folder from this server into your Web Folders folder. 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. To save your report 1.
Printing, Exporting, and Viewing Reports Distributing reports Workbench. For more information about publishing through the Workbench, see The Workbench. When you choose the Enterprise option in the Open dialog box, you can select any published report to make changes to it.
20 Printing, Exporting, and Viewing Reports Distributing reports 5. Click the Authentication list to select the appropriate authentication type. Enterprise authentication requires a user name and password that is recognized by BusinessObjects Enterprise. LDAP authentication requires a user name and password that is recognized by an LDAP directory server. Windows Active Directory (AD) authentication requires a user name and password that is recognized by Windows AD.
Printing, Exporting, and Viewing Reports Viewing reports Enterprise folder is the same as publishing the report to BusinessObjects Enterprise. To save a report to an Enterprise folder 1. On the File menu, click Save As. 2. In the Save As dialog box, click Enterprise. 3. If you have not already logged onto BusinessObjects Enterprise, do so now. For information about how to log onto BusinessObjects Enterprise, see Opening a report in an Enterprise folder. 4.
20 Printing, Exporting, and Viewing Reports Viewing reports What are Report Parts? Report objects displayed by themselves in a viewer—without the rest of the report page—are referred to as Report Parts. More precisely, however, Report Parts are objects that use hyperlinks to point from a home report object to a destination object. Report Parts work with the DHTML viewer subset of the Crystal Report Viewers to expand the navigation possibilities within and between reports.
Printing, Exporting, and Viewing Reports Viewing reports Report Parts use this navigation functionality when linking between Report Part objects. The key difference between Report Part navigation and regular (page) navigation is that, when navigating with Report Parts (using the Report Part Viewer), you see only the objects identified as Report Parts. In regular navigation (using the page viewers or the Advanced DHTML Viewer), you go to the identified object(s), but you see the entire page.
20 Printing, Exporting, and Viewing Reports Viewing reports See The Report Part Drilldown option for information about how to use these options. 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 Viewing reports You cannot select the following report object types as destinations: • Objects inside the Page Header or Page Footer. • Lines or boxes. • Subreports or any object inside a subreport. • Entire sections (you must select the objects inside the section individually). You can add specific information in one of two ways: • • You can type the object name(s) as they appear in the Report Explorer.
20 Printing, Exporting, and Viewing Reports Viewing reports The Report Part Drilldown option The Report Part Drilldown option lets you define a hyperlink so that the Report Part Viewer can emulate the drill-down functionality of Crystal Reports. The Report Part Viewer displays only destination objects; therefore, to make drill down work, you need to define a navigation path from a home object to one or more destination objects.
Printing, Exporting, and Viewing Reports Viewing reports Include another object from the same section of the report by entering a semi-colon (;) and typing the name of the object. 5. Click OK. To create a Report Part Drilldown 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.
20 Printing, Exporting, and Viewing Reports 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 Viewing reports Tip: Use the Report Explorer to quickly identify the default names assigned to each of your report objects. To open the Report Explorer, click its button on the Standard toolbar. You can add all the objects in a section by selecting the section node. If you expand the section node, you can select one or more of the objects that it contains. 5. Use the arrow buttons to move the selected section or object(s) to the Fields to Display area.
20 Printing, Exporting, and Viewing Reports Viewing reports Enterprise, or it must be part of a stand-alone Report Application Server environment. The Report Part Viewer displays only the objects you specify. Like the Report Part Drilldown option, all destination objects must be from the same report section.
Printing, Exporting, and Viewing Reports Viewing reports Your source report is the report you copy an object from; the target report is the report you add the hyperlink information to. When you view your target report in the Report Part Viewer, you first see its home object. When you drill down on that object, you go to the destination object you selected in the source report. 2. In your source report, right-click the intended destination object and select Copy from its shortcut menu. 3.
20 Printing, Exporting, and Viewing Reports Viewing reports The Paste Link button includes a list of options that you can use when pasting a link to the destination object. Depending on the type of link you are creating (for example, a link to a specific data context rather than a link to a generic data context), you may see these options: • Context Report Part This is the default option; it is selected when you click Paste Link without viewing the other list options.
Printing, Exporting, and Viewing Reports Viewing reports Use this option to paste a link that includes a generic data context (that is, a data context that does not point to a specific record). Use this option when you want a broad data context, or when data is not yet present in your source report. • Preview Report Part Use this option to paste a link that includes a specific data context (that is, a data context that points to a specific record).
20 Printing, Exporting, and Viewing Reports Viewing reports • For more information, see Data context formats. 8. Click OK. You have established a hyperlink from your home object to a destination object or objects. In the Report Part Viewer, you see the home object first, and when you click it for drill down, you see the destination object(s).
Printing, Exporting, and Viewing Reports Viewing reports data context is /Country[Australia]/ChildIndex[5], the Context Report Part data context is "/"+{Customer.Country}. Cross-tabs If the object you copy in your source report is part of a cross-tab, the Context Report Part data context is based on the selected object embedded in the cross-tab. Crystal Reports creates the data context by tracking if the selected object in the cross-tab is a row, column, or cell.
20 Printing, Exporting, and Viewing Reports Viewing reports • 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"). Hyperlinks displayed in the viewers This section summarizes information about the hyperlink options and how they work in the page viewers and the Report Part Viewer.
Printing, Exporting, and Viewing Reports Viewing reports Another Report Object Report Part Drilldown The object can be in a different report that is managed in BusinessObjects The object must be in the same reEnterprise or is part of a stand-alone port. Report Application Server (RAS).
20 Printing, Exporting, and Viewing Reports Viewing reports Creating an Enterprise Hyperlink You can create hyperlinks from a report to enterprise documents such as Crystal Reports, Webi and other documents. Note: In order for this feature to work BusinessObjects Enterprise needs to be running. To create an Enterprise Hyperlink 1. Select a report object on the Design or Preview tab. 2. On the Format menu, click Hyperlink. The "Format Editor" dialog box appears with the Hyperlink tab active. 3.
Printing, Exporting, and Viewing Reports Using smart tags Using smart tags Crystal Reports lets you take advantage of smart tags in Office XP. When you paste a chart, a text object, or a field object into an Office XP application, you can view data from the host report after selecting a smart tag option. This table summarizes the options available to you.
20 Printing, Exporting, and Viewing Reports Using smart tags 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. On the File menu, click Options and ensure that the options in the Smart Tag Web Server Options area on the Smart Tag & HTML Preview tab have been configured: • Your web server must be named. • A virtual directory must be named (a default is provided).
Report Alerts 21
21 Report Alerts About Report Alerts About Report Alerts Report Alerts are custom messages created in Crystal Reports that appear when certain conditions are met by data in a report. Report Alerts may indicate action to be taken by the user or information about report data. Report Alerts are created from formulas that evaluate conditions you specify. If the condition is true, the alert is triggered and its message is displayed. Messages can be text strings or formulas that combine text and report fields.
Report Alerts Working with Report Alerts 2. Click New. The Create Alert dialog box appears. 3. Enter a name for your new alert in the Name box. 4. Enter your alert message in the Message box. The Message box lets you enter a message to be used as a default. If you want the same message to appear every time your alert is triggered, enter it in the Message box. If, however, you want to use a formula so the message is customized with data elements, see the next step. 5.
21 Report Alerts Working with Report Alerts The Formula Workshop appears. 9. Enter your alert condition formula. 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.
Report Alerts Working with Report Alerts Only enabled and disabled alerts appear in the Create Alerts dialog box. If an alert is triggered, it is seen in the Report Alerts dialog box. Editing Report Alerts 1. On the Report menu, point to Alerts and then click Create or Modify Alerts. 2. In the Create Alerts dialog box, select the alert you want to 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.
21 Report Alerts Working with Report Alerts Deleting Report Alerts 1. On the Report menu, point to Alerts and then click Create or Modify Alerts. 2. In the Create Alerts dialog box, select the alert you want to delete and click Delete. The selected alert is removed from the Create Alerts dialog box. Note: If the alert has already been triggered, deleting it removes it from the Report Alerts dialog box as well.
Report Alerts Working with 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.
21 Report Alerts Working with Report Alerts These functions behave the same as alerts created in the Create Alerts dialog box: • IsAlertTriggered("AlertName") is true only for the records on which the alert is triggered • AlertMessage("AlertName") displays the message for a record when the alert is true. Because alerts are not field objects (you cannot drop them into the report), alerts are represented differently from report fields in the Formula Workshop.
Using Formulas 22
22 Using Formulas Formulas overview Formulas overview In many cases, the data needed for a report already exists in database table fields. For example, to prepare an order list you would place the appropriate fields on the report. Sometimes, however, you need to put data on the report that does not exist in any of the data fields. In such cases, you need to create a formula.
Using Formulas Formula components and syntax 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.
22 Using Formulas Formula components and syntax Formula components Creating a formula in Crystal Reports is like creating one in any spreadsheet application. You can use any of the following components in your formula: Fields Example: {customer.CUSTOMER LAST NAME}, {customer.LAST YEAR'S SALES} 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.
Using Formulas Formula components and syntax Control Structures Example: "If" and "Select", "For" loops Group field values Example: Average (fld, condFld), Sum (fld, condFld, "condition") Group field values summarize a group. For example, you could use group field values to find the percentage of the grand total contributed by each group. Other formulas Example: {@GrossProfit}, {@QUOTA} Formula syntax Syntax rules are used to create correct formula.
22 Using Formulas User Function Libraries in formulas If you are already comfortable with Crystal syntax, you can continue to use it, and benefit from the new functions, operators and control structures inspired by Visual Basic. Note: • • • Record selection and group selection formulas cannot be written in Basic syntax. Report processing is not slowed down by using Basic syntax. Reports using Basic syntax formulas can run on any machine that Crystal Reports runs on.
Using Formulas Specifying formulas Note: After you have completed the configuration outlined in the Developer's Guide, you must return to Crystal Reports, point to the File menu, and select Options. On the Formula Editor tab, go to UFL Support and select Java UFLs Only. Specifying formulas There are several different kinds of formulas in Crystal Reports: report, formatting, selection, search, running total condition, and alert formulas.
22 Using Formulas Specifying formulas Search formulas Search formulas help you locate data in your report. Like selection formulas, you normally do not enter these formulas directly, but instead specify the search criteria using the Search Expert. Crystal Reports generates the formula. You have the option to manually edit these formulas, but you must use Crystal syntax.
Using Formulas Specifying formulas You can open the Formula Workshop by itself before you begin adding specific kinds of formulas. To access the Formula Workshop 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. The Formula Workshop appears. 2. Click New and select the kind of formula you want to create from the list that appears.
22 Using Formulas Specifying formulas Formula Workshop buttons The main toolbar in the Formula Workshop is made up of three smaller toolbars. Each of these toolbars contains a set of buttons that is relevant to specific actions: working with the Formula Workshop as a whole, working with the Workshop Tree, or working with an editor. Individual buttons are available or unavailable based on the task you are undertaking. Each toolbar can be moved and docked as you require.
Using Formulas Specifying formulas 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. The buttons for the Workshop Tree Toolbar of the Formula Workshop perform the following functions: Lets you rename the selected formula, custom function, or SQL Expression. Deletes the selected formula, custom function, or SQL Expression.
22 Using Formulas Specifying formulas Adds the selected repository custom function to the report. 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.
Using Formulas Specifying formulas Inserts a bookmark at the beginning of a selected formula line. Click the button again to remove the bookmark. Jumps to the next bookmark. Jumps to the previous bookmark. Deletes all bookmarks in the current formula. Arranges all Report Fields, Functions, and Operators trees in alphabetical order. Hides or views the Report Fields tree. Note: This button is not available for custom functions. Hides or views the Functions tree. Hides or views the Operators tree.
22 Using Formulas Specifying formulas 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. Working with the Formula Editor The Formula Editor is a component of the Formula Workshop. Use the Formula Editor to create and modify the content of formulas.
Using Formulas Specifying formulas 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.
22 Using Formulas Specifying formulas Setting the default syntax When you open the Formula Editor, Crystal syntax appears as the syntax default. If you want to change the syntax default, select Options from the File menu, then click the Reporting tab. Choose the preferred syntax from the Formula Language drop-down list and click OK. When you access the Formula Editor, the syntax you selected appears as the default.
Using Formulas Specifying formulas Formula Editor Key Controls The Formula Editor accepts the following key controls: Keyboard Combination Action Performed Alt+B Opens Browse dialog box for highlighted field. Alt+C Checks formula for errors. Alt+F Opens or closes the Shows Field tree. 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.
22 Using Formulas Specifying formulas 570 Keyboard Combination Action Performed 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. Ctrl+Shift+F2 Clears all bookmarks. Ctrl+Home Goes to the beginning of file. Ctrl+Left Arrow Goes to the beginning of the left word (+Shift key will do the selection).
Using Formulas Specifying 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. Ctrl+Alt+F2 Goes to next bookmark.
22 Using Formulas Creating and modifying formulas Creating and modifying formulas Creating a formula and inserting it into a report 1. On the View menu, click Field Explorer. 2. In the Field Explorer dialog box, select Formula Fields and click New. 3. In the Formula Name dialog box, enter the name you want to identify the formula by, and then click OK. The Formula Workshop appears with the Formula Editor active. 4. On the Expressions Editor Toolbar, choose either Crystal or Basic syntax.
Using Formulas Creating and modifying formulas Creating a formula in the Formula Expert The Formula Expert is a component of the Formula Workshop. Use the Formula Expert to create and modify formulas based on custom functions. Note: To learn about the Formula Expert's user interface, see Formula Expert in the online help.
22 Using Formulas Creating and modifying formulas 5. In the Custom Function area, choose the custom function you want to base your formula on. You can choose a Report Custom Function (a custom function that exists in the current report) or a Repository Custom Function (a custom function that is stored in the repository). Note: If you select a Repository Custom Function, that custom function is added to the current report.
Using Formulas Creating and modifying formulas 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. 6.
22 Using Formulas Creating and modifying formulas Copying formulas 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.
Using Formulas Creating and modifying formulas Windows places a copy of the selected text on the Clipboard. 6. Return to Crystal Reports, choose View from the main menu and select Field Explorer. 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.
22 Using Formulas Deleting formulas Key points for editing a copy of a formula When making changes, use the following points as a guide: • All fields, formulas, and group fields referenced in the formula copy must actually exist in the new report. This means that any database referenced in the original formula (or a database with the same structure, field names, and alias) must be active in the new report.
Using Formulas Debugging formulas Note: You cannot delete the specification without deleting all working copies of the formula. Removing the working formula from your report 1. Right-click the formula you want to delete from the report. 2. Select Delete. Note: Even after the working copies of a formula have been deleted from the report, the formula specification remains unchanged. The specification is listed in the Field Explorer dialog box.
22 Using Formulas Debugging formulas Debugging evaluation time errors When the Formula Workshop is being displayed as a result of an evaluation time error, the Workshop Tree will contain a call stack. The root of the tree provides a description of the error which occurred. The nodes in the tree provide the names of the custom functions and/or formulas which were being evaluated when the error occurred. The custom function/formula at the top of the call stack is where the error was detected.
Using Formulas Debugging formulas About this tutorial • This tutorial uses the Xtreme.mdb sample database. • This tutorial uses Crystal syntax. • The following formula is the formula you will test for errors: If ({customer.CUSTOMER NAME} [1 to 2 ToText({customer,CUSTOMER ID}) [1] = ({customer.CUSTOMER NAME} [1] = 'Ro" ToText({customer.
22 Using Formulas Debugging formulas 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. Insert the corrected formula field to the right of the two data fields in the Details section of your report. 9.
Using Formulas Debugging formulas 4. Correct the formula by replacing the comma (,) in the field name with a period (.). 5. Click Check again. The formula should now be error-free. 6. Place the formula to the right of the @Formula1 field. 7. Click Print Preview on the Standard toolbar to check the values in the report and compare the fields to see if the field values returned by @Formula2 are correct.
22 Using Formulas Debugging formulas Formula4 1. Create a new formula called Formula4. 2. Type the following in the Formula text box of the Formula Editor: If ToText({customer.CUSTOMER ID}) [1] = "5" "TRUE" Else "FALSE" 3. Click Check to test for errors. You will receive the following error message: The word 'then' is missing. 4. Correct the formula by typing in the word "Then" at the end of the first line after "5". 5. Click Check again. The formula should now be error-free. 6.
Using Formulas Debugging formulas Else "FALSE" 3. Place the formula to the right of the @Formula4 field. You should see "TRUE" next to each customer whose name begins with Bi and Id begins with 6, and "FALSE" next to all Customer IDs that do not meet this criteria. If this formula is working correctly, you can create one last formula adding the code from @Formula3 and @Formula4. FinalFormula 1. Create a new formula called FinalFormula. 2.
22 Using Formulas Debugging formulas 586 Crystal Reports 2008 SP3 User's Guide
Parameter Fields and Prompts 23
23 Parameter Fields and Prompts Parameter and prompt overview Parameter and prompt overview Parameters are Crystal Reports fields that you can use in a Crystal Reports formula. As a formula component, a parameter must have a value before the program can process the report. By using parameters in formulas, selection formulas, and in the report itself, you can create a single report that changes its behavior depending on the values entered by your users. Parameter fields can also be used in subreports.
Parameter Fields and Prompts Parameter and prompt overview 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.
23 Parameter Fields and Prompts Parameter and prompt overview Prompt considerations There are a number of things to keep in mind when working with prompts: • Prompts can be static or dynamic. As well, a dynamic prompt can have a cascading list of values. For a description of each option, see these topics: • Creating a parameter with a static prompt. • Creating a parameter with a dynamic prompt. • Creating a parameter with a cascading list of values.
Parameter Fields and Prompts Parameter and prompt overview Changes made to the value of a data parameter require a report refresh to fetch new data from the database. For example, a report will be refreshed when a data parameter is used to generate a query. Non-data parameters are used in: • Saved data selection formulas • Conditional formulas • Parameters placed on the report Changes made to the value of a non-data parameter will filter the saved data of the report and will not require a refresh.
23 Parameter Fields and Prompts Understanding dynamic prompts • If you change the parameter from optional to mandatory, the HasValue() function is not automatically removed from your formula. Handling a parameter with no value When the report engine evaluates any formula which references an optional parameter that does not have a value, it generates a runtime error.
Parameter Fields and Prompts Understanding dynamic prompts Feature Available when Crystal reports are stored outside of BusinessObjects Enterprise? Available when Crystal reports are published to BusinessObjects Enterprise? Create dynamic prompts and cascading lists of Yes values. Yes Reuse list of value definitions within a single Yes report at design time. Yes Reuse list of value definitions within multiple No reports at design time. Yes Schedule lists of values.
23 Parameter Fields and Prompts Understanding dynamic prompts Feature Available when Crystal reports are stored outside of BusinessObjects Enterprise? Available when Crystal reports are published to BusinessObjects Enterprise? Populate list of values from Business Views. No Yes Cache lists of values at report run time, and share that list among No multiple executing reports. Yes Schedule lists of values to update themselves on No a regular basis.
Parameter Fields and Prompts Understanding lists of values • ActiveX • .NET Winform • .NET Webform • Java, COM, and JSF DHTML page viewers • Java and COM Advanced DHTML Viewer • The InfoView and Central Management Console scheduling interfaces in BusinessObjects Enterprise. • The Report Designer Component (RDC). • Crystal Reports XI R2 and later. These components support the design of reports with dynamic prompts and cascading lists of values. • Crystal Reports XI R2 and later.
23 Parameter Fields and Prompts Understanding lists of values For example, a list of countries could be a dynamic prompt, while a hierarchical list of countries, regions, and cities could be a dynamic prompt with a cascading list of values. Crystal Reports supports three data sources for list-of-values objects: • Report fields (only for unmanaged reports). • Command objects (only for unmanaged reports). • Business Views (only for managed reports). A list of values can have one or more levels.
Parameter Fields and Prompts Understanding lists of values Every report that you store in BusinessObjects Enterprise uses managed list-of-values objects. All managed list-of-values objects are based on a Business View, even if the report itself does not use a Business View. Managed list-of-values objects have a number of features that are not available to unmanaged list-of-values objects.
23 Parameter Fields and Prompts Understanding lists of values Unmanaged list of values Managed list of values Feature Report fields At report-view time, prompt for additional inforNo mation needed by the list of values. Command objects Business Views Yes Yes If the command object contains parameters, the value(s) are prompted for at report-view time. If the Business View contains parameters, the value(s) are prompted for a report-view time. Yes Display different values to differ- No ent users.
Parameter Fields and Prompts Understanding lists of values Unmanaged list of values Managed list of values Feature Report fields Command objects Business Views Yes Schedule the list of values to upNo date on a recurring schedule. No Partially schedule the list of values to update only certain por- No tions of the list on a recurring schedule. No The scheduling capability comes from the Business View Manager, not the Central Management Console (CMC) where reports are scheduled.
23 Parameter Fields and Prompts Understanding lists of values Unmanaged list of values Managed list of values Description Single-level code tables. (A table that has hundreds of semistatic values in a single level.) Report fields Command objects 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.
Parameter Fields and Prompts Understanding lists of values Unmanaged list of values Managed list of values Description Report fields Command objects Business Views Well suited. Not well suited. Well suited. Fact tables. (These tables tend to be very large, dynamic tables with millions of values in multiple levels.) Provided that the filtering is done outside of Crystal Reports in a database view, and provided that there is a multilevel hierarchy to the data.
23 Parameter Fields and Prompts Creating a parameter with a static prompt Creating a parameter with a static prompt A static prompt is one that always contains the same values. For example, if your parameter prompts for a country value, you could create a static prompt because the country list represents a set of values that does not change often. Use the following steps to create a parameter with a static prompt that lets users see a list of customers that they can select a specific country from.
Parameter Fields and Prompts Creating a parameter with a static prompt 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.
23 Parameter Fields and Prompts Creating a parameter with a static prompt 9. Click OK. 10. Return to the Field Explorer dialog box, and drag the Country parameter into your report. The "Enter prompt values" dialog box appears. 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.
Parameter Fields and Prompts Creating a parameter with a static prompt 12. Click OK. 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. The Choose Field dialog box appears. 2. Select Country from the Customer table, then click OK. The Select Expert appears. 3. Choose is equal to from the drop-down list. 4.
23 Parameter Fields and Prompts Creating a parameter with a static prompt 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. 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.
Parameter Fields and Prompts Creating a parameter with a dynamic prompt Creating a parameter with a dynamic prompt The values of a dynamic prompt are retrieved from the data source and are not stored within the report. For example, if you are prompting for customer name, you could create a dynamic prompt because the names in your customer database probably change frequently. Dynamic prompts can be authored in Crystal Reports.
23 Parameter Fields and Prompts Creating a parameter with a dynamic prompt Note: If your report already contains a list of values, or if you want to pick a list of values from your repository, you can select Existing and choose an existing list of values from the tree structure. 7. Click Insert, and from the Value list, select Region. 8. In the Value Options area, enter the desired prompting text in the Prompt Text field (up to 255 alphanumeric characters).
Parameter Fields and Prompts Creating a parameter with a cascading list of values 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. In the background, however, Crystal Reports stores the information about the list of values that you created for this prompt.
23 Parameter Fields and Prompts Creating a parameter with a cascading list of values To create a parameter with a cascading list of values 1. Open the sample report called Group.rpt. Sample reports are located on the Business Objects Technical Support web site http://support.businessobjects.com/samples/. 2. On the View menu, click Field Explorer. The Field Explorer dialog box appears. 3. Select Parameter Fields and click New. The Create New Parameter dialog box appears. 4.
Parameter Fields and Prompts Creating a parameter with a cascading list of values 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. 10. 11. 12. From the Value list, select Country. Click the blank field under Country and select Region. Click the blank field under Region and select City.
23 Parameter Fields and Prompts The Parameter Panel 14. Click OK. 15. Return to the Field Explorer dialog box, and drag the Supplier City parameter into your report. Note: If you don't want to see the parameter field you dropped in your report, place it in a section you can suppress, such as a report header or footer. 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.
Parameter Fields and Prompts Working with lists of values 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. Users must refresh the report to change the parameter values. • Editable Users can view and change the parameter values on the panel.
23 Parameter Fields and Prompts Working with lists of values scheduling it in the Business View Manager. For more information, see the Business Views Administrator's Guide. You can add lists of values to 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.
Parameter Fields and Prompts Working with lists of values This example uses SupplierCity. 4. In the List of Values area, click Dynamic. 5. Enter prompting text for your prompt group in the Prompt Group Text field. This example uses "Choose the City that your supplier is located in." 6. 7. 8. 9. 10. 11. Click Insert, and from the Value list, select Country. Click the blank field under Country and select Region, and then click the blank field under Region and select City.
23 Parameter Fields and Prompts Working with lists of values Using separate value and description fields It is common in relational databases to make use of code fields that represent values. These codes are often numeric or text strings that cannot be read by your users. For such cases, you can create separate value and description fields in your list of values definition. You set the value field to the parameter; the description field appears in the prompting dialog box.
Parameter Fields and Prompts Working with lists of values Using command objects as list-of-values data sources Crystal Reports supports command objects as data sources for unmanaged lists of values. To use this feature, you first create a command object using the Database Expert to return the values that you want in your list-of-values object. For more information see "Defining an SQL Command" in the online help.
23 Parameter Fields and Prompts Working with lists of values Long lists of values Because the number of items in a list of values (LOV) can be very large, numbering in the hundreds or thousands, Crystal Reports handles these long LOVs by retrieving smaller batches of values at a time. Crystal Reports displays a single batch of LOVs, and provides two options to the user to control the list: the batch number and a simple text filter for limiting the number of items in the list.
Parameter Fields and Prompts Best practices for prompting Where {number} in the above example is the number of items in the batch. Best practices for prompting Unmanaged reports Unmanaged reports are reports that you store outside of 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 BusinessObjects Enterprise Repository.
23 Parameter Fields and Prompts Best practices for prompting To maximize performance and manageability of your managed reports, the following practices are recommended: • Define your list-of-values objects in Business View Manager, not Crystal Reports. • 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.
Parameter Fields and Prompts Deleting parameter fields • Create a new report object from within the Central Management Console portal. In all of these cases, these actions are carried out on the prompt object in your unmanaged report: • List-of-values objects that are defined in the report are converted to repository lists of values. A Business View, Business Element, Data Foundation, and Data Connection object is created.
23 Parameter Fields and Prompts Deleting parameter fields To delete a parameter that is not used in a formula 1. On the View menu, click Field Explorer. The Field Explorer appears. 2. Expand the Parameter Fields folder and click the parameter you want to delete. 3. Click Delete. If the parameter is used in your report, a Crystal Reports dialog box appears confirming whether you want to delete the parameter. 4. Click Yes. This parameter is removed from the Parameter Fields folder and from your report.
Parameter Fields and Prompts Responding to parameter field prompts To delete a parameter that is used in a formula 1. On the View menu, click Field Explorer. The Field Explorer appears. 2. In the Formula Fields folder, select the formula that contains the parameter you want to delete. 3. Click Edit and delete the parameter field from the formula. Note: If the parameter is used in more than one formula, it must be deleted from each formula. 4. Close the Formula Workshop. 5.
23 Parameter Fields and Prompts Responding to parameter field prompts 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. Select the "Prompt for new parameter values" option to enter a new parameter value. When you select this option and click OK, the Enter Prompt Values dialog box appears. 624 • Enter String values exactly as they will appear in the field.
Parameter Fields and Prompts Advanced parameter features • Enter DateTime values using the following format: Date (Year, Month, Day), Time (Hour, Minutes, Seconds AM/PM). For example, Date (1997, 5, 21), Time (4:32:12 PM). You can also enter DateTime values by using the associated drop-down arrow to access the calendar, and the up and down arrows to scroll through the time.
23 Parameter Fields and Prompts Advanced parameter features 6. Add the values from the field you selected by clicking Actions and choosing Append all database values. You can also import values, or you can create your own in the Value area under the Options button. 7. In the Value Options area, select the Allow multiple values option and set its value to True to enable your uses to enter more than one prompting value for this parameter.
Parameter Fields and Prompts Advanced parameter features 2. Create the formula and use the parameter field in place of the fixed value you would normally use. For example, to be prompted for all the customers whose last year's sales were over a certain value, and to print their names in red, select the Last Year's Sales field and click Format from the Expert Tools toolbar. The Format Editor appears. 3.
23 Parameter Fields and Prompts Advanced parameter features The Create New Parameter dialog box appears. 3. Type a name for the parameter field in the Name field. 4. Select String from the Type list. 5. To specify a default title, click Click here to add item in the Value area and type the desired text. Continue adding titles as required. 6. Click OK. 7.
Parameter Fields and Prompts Advanced parameter features a report that uses this parameter for record selection will display all records with values between 5 and 10. This also works for string parameter fields. With a start value of "A" and an end value of "E", a report that uses this parameter for record selection will display all records within an alphabetical range of A-E.
23 Parameter Fields and Prompts Advanced parameter features 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 Advanced parameter features Now the parameter field will only accept single-character values. The field will accept "C" as a value, but not "City." 4. In the Prompting Text field, enter a prompt similar to this: Type R to sort by Region or C to sort by City; other wise, data will be sorted by Country. 5. Create a formula similar to this and call it Sort: If {?SortField} = "C" Then {customer.CITY} Else If {?SortField} = "R" Then {customer.REGION} Else {customer.
23 Parameter Fields and Prompts Advanced parameter features 4. For a string parameter field, you can choose to enter an edit mask in the Edit mask field, rather than specifying a range. An edit mask can be any of a set of masking characters used to restrict the values you can enter as parameter values (the edit mask also limits the values you can enter as default prompting values).
Parameter Fields and Prompts Advanced parameter features • "\" (causes the subsequent character to be displayed as a literal). For example, the edit mask "\A" would display a parameter value of "A." If the edit mask is "00\A00," then a valid parameter value would consist of two digits, the letter "A," and then two additional digits. • "Password".
23 Parameter Fields and Prompts Advanced parameter features • • Exported reports retain all recently applied parameter changes. Sub-report parameters cannot be shown on the Parameter Panel. 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 Advanced parameter features 6. Click Actions, and select Append all database values to move all of the applicable values to the Values area. This example will enable the user to choose from any of the values. If you want to limit the selection, manually enter only the values that you would like the user to choose from. 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.
23 Parameter Fields and Prompts Advanced parameter features 4. Choose the parameter from the adjacent drop-down list and click OK. If the parameter is being referenced for the first time, the Enter Values dialog box appears. 5. Select your initial values and click OK. Crystal Reports immediately filters the report data according to your selection criteria.
Parameter Fields and Prompts Advanced parameter features Else if {?GroupBy} = 'Country' then {Customer.Country} Else if {?GroupBy} = 'Order' then ToText({Orders.Order ID}) Note: Crystal Reports formulas do not allow conditions to return different data types. Both the Customer Name and Country fields return strings, so the ToText function must be used to convert the Order ID from a number to a string as well. 8. Save your formula and close the Formula Workshop. 9.
23 Parameter Fields and Prompts Advanced parameter features 638 Crystal Reports 2008 SP3 User's Guide
Subreports 24
24 Subreports What are subreports? What are subreports? A subreport is a report within a report. The process for creating a subreport is similar to the process of creating a regular report. A subreport can have most of the characteristics of a report, including its own record selection criteria.
Subreports What are subreports? Unlinked vs. linked subreports 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.
24 Subreports What are subreports? When you are considering whether to use a subreport or linked tables, you need to understand the ramifications of each. These issues are discussed fully in Performance considerations in one-to-many links. As a general rule, if you have indexed tables, linked indexed fields, or range limiting record selection criteria based on the indexed fields, the program needs to read the same number of records whether you are linking tables in a single report or using subreports.
Subreports What are subreports? • When the subreport is finished, the program locates the second record it needs in the primary report, prints the customer data, and then passes this customer's ID number to the parameter field. • The program then runs a subreport including only those order records for the second customer. • The process continues until the report is finished. • All of this parameter field manipulation takes place behind the scenes.
24 Subreports Inserting subreports 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 Inserting subreports 7. If you chose On-demand subreport (similar to a hyperlink), click the subreport preview tab to see your subreport. This tab is labeled with the name of your subreport. Note: Using on-demand subreports will increase the performance of reports that contain subreports. For information about creating a custom caption for the Subreport Preview Tab, see Adding captions to on-demand subreports.
24 Subreports Inserting subreports To save a subreport as a primary report 1. In the Design tab, right-click the subreport and click Save Subreport As from the shortcut menu. The Save As dialog box appears. 2. Search for the appropriate directory in the Save As dialog box. 3. Type a new name for the subreport. 4. Click Save. The program saves the subreport as a primary report so that you can open it separately when necessary.
Subreports Linking a subreport to the data in the primary report To update a specific subreport when opening a main report 1. From the Format menu, click Format Subreport. 2. In the Format Editor dialog box, click the Subreport tab. 3. Click Re-import When Opening. The current subreport will be updated when the main report is opened and refreshed. Manually updating subreport data You can update your subreport data at any time. 1. On the Design tab, right-click the subreport. 2.
24 Subreports Linking a subreport to the data in the primary report To link a subreport to the data in the primary report 1. If you are creating a new subreport or importing an existing report as a subreport, from the Insert menu, click Subreport. Choose or create a report and click the Link tab. - or If you have already placed a subreport in the primary report, but did not create a link at setup, navigate to the Subreport Links dialog box by choosing Subreport Links from the Edit menu.
Subreports Linking a subreport to the data in the primary report dates, or date/time fields, you must make sure your subreport parameter field type matches the field type set up in Report Options in the main report for the field you want linked. 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.
24 Subreports Combining unrelated reports by using subreports Combining unrelated reports by using subreports At times, you may wish to combine unrelated reports into a single report. For example, you may want to create a single report that presents: • Sales grouped by sales representative. • Sales grouped by item. While both reports deal with sales data, there is no real linear relationship between the reports. Subreports can be used to combine unrelated reports into a single report like this.
Subreports Using subreports with unlinkable data For example, if you want to use three subreports, insert two new Report Footer sections so that you have a total of three Report Footer sections. 4. In Report Footer A, place the subreport you want printed immediately after the primary report. In Report Footer B, place the subreport you want printed next, and so forth. The primary report will print first and then the subreports in the order that you placed them in the report.
24 Subreports Using subreports with unlinkable data Linking to/from a formula field There are situations in which you may need to link to or from a formula (calculated) field. For example, an employee ID could be an 11 character value that consists of a two-character department code followed by the employee's nine-character Social Security Number (for example, HR555347487). The formula language makes it easy to extract the Social Security Number from this field: {employee.
Subreports Creating an on-demand subreport 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. You cannot link the tables in a single report when two tables contain related data yet neither is indexed on the field which you want to use as a link field, or when the primary table is indexed but the lookup table is not. You must use subreports if you want to coordinate the data in both tables.
24 Subreports Creating an on-demand subreport 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. 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 Showing different views of the same data in a report 4. Enter your formula in the Formula text box. Crystal syntax formula example: "More Information About " + {Customer.Customer Name} Basic syntax formula example: formula = "More Information About" + {Customer.Customer Name} Using the Xtreme.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.
24 Subreports Showing different views of the same data in a report Use the appropriate link fields to link the report and coordinate the data.
Understanding Databases 25
25 Understanding Databases Databases overview Databases overview Though there are hundreds of Database Management Systems (DBMS) available, Crystal Reports eliminates many of the differences once it connects to the actual database files. The process of working with database files, tables, fields, and records is much the same, regardless of the actual type of data being accessed. This section discusses several concepts and tasks common to working with database files.
Understanding Databases Databases overview Often, data in two different tables can be related by a common field. For example, a Customers table will have a Customer ID for each customer, and an Orders table will have the Customer ID of each customer who placed an order, demonstrating a relationship between tables. The two tables can be linked by a common field see Linking tables.
25 Understanding Databases Databases overview not be difficult, but to find every formula that uses that field could be a difficult and time consuming task. To solve this problem, the Report Designer uses aliases to refer to database tables and files. Aliases are pointers, internal devices that tell the program where it should look for a database field. Now, if you change the name or location of the database, you simply reset the pointer. See Locating files.
Understanding Databases Databases overview 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. Locating files When a database file is moved or renamed, Crystal Reports will not be able to find the data the next time the report is printed.
25 Understanding Databases Databases overview command provides a simple way to indicate the new name or location of database files. In addition, the Set Datasource Location command automatically converts your database driver to the data source you have chosen. For example, you can automatically convert a direct access data source to an ODBC data source using the Set Datasource Location command. See "Changing the data source accessed by a report" in the online help.
Understanding Databases Databases overview Order# Customer Amount 10495 SFB Inc. 7911.80 10501 La Bomba de Bicicleta 1956.20 10511 BG Mountain Inc. 1683.60 10544 Sierra Bicycle Group 19766.20 10568 Mountain Tops Inc. 29759.55 10579 Sierra Bicycle Group 12763.95 The information in this table is organized according to the Order# field. This is fine anytime you want to look up information in the table based on order numbers.
25 Understanding Databases Databases overview Customer Pointer to Order# Allez Distribution 10444 BG Mountain Inc. 10470 BG Mountain Inc. 10511 La Bomba de Bicicleta 10501 Mountain Toad 10488 Mountain Tops Inc. 10568 SFB Inc. 10495 Sierra Bicycle Group 10544 Sierra Bicycle Group 10579 Sierra Mountain 10485 In this index, information is organized by customers, not order numbers.
Understanding Databases Linking tables since information in the index is organized according to the customer names, 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.
25 Understanding Databases Linking tables One-to-one relationships 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.
Understanding Databases Linking tables When a one-to-many situation exists between two database tables and the program matches up records from the tables, there are a number of factors that determine how many records the program reads and evaluates. The tables that follow show the effects of the different factors on the number of records the program ultimately has to read. The charts are based on these assumptions: • Table A contains 26 records (one for each letter in the alphabet).
25 Understanding Databases Linking tables Does your primary report include a record selection formula that sets range limits on the key (indexed) field in Table A? • Index A Is Table A on the field you are going to use indexed to match up the records? • Index B Is Table B on the field you are going to use indexed to match up the records? • Reads A How many records does the program have to read out of Table A to find the two records it is looking for? • For each A reads in B How many records does th
Understanding Databases Linking tables Link ing/Sub report Selection For- Index A mula Index B For each Total Reads A A reads Records in B Read Linking Yes Yes Yes 2 100 (2*100) Subre port No No No 26 2600 67,600 (26*2600) Subre port No Yes No 2 2600 67,600 (26*2600) Subre port No Yes Yes 26 100 2600 (26*100) Subre port Yes No No 2 2600 5200 (2*2600) Subre port Yes No Yes 26 100 2600 (26*100) Subre port Yes Yes Yes 2 100 (2*100) 200 200 Crystal Reports 2008 S
25 Understanding Databases Linking tables SQL Data Total Records Read Linking/Sub Selection report Formula Reads A For each A reads in B Linking No 26 100 (26*100) 2600 Linking Yes 2 100 (2*100) Subreport No 26 100 (26*100) 2600 Subreport Yes 2 100 (2*100) 200 200 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.
Understanding Databases Linking tables the database DLL (for PC data) or the server (for SQL data). See Record Selection. The second half of the selection formula, however, requires processing that must be done in the Report Engine. It uses a built-in function to manipulate and evaluate a field value and it cannot be done in the database DLL or the server. The program does not pass this condition to the database DLL.
25 Understanding Databases Linking tables • The program passes this merged record (A+B) back to the Report Engine, which tests it against the entire selection formula. • The program then locates the second matching record in Table B and passes that merged record back, then the third record, and so on, until it has located, merged, and passed back all the records in Table B that match the first record in Table A.
Understanding Databases Linking tables SQL database considerations Since indexes are not critical with SQL data, the primary concern with both linked tables and subreports is whether or not there is a selection formula in the primary report that puts range limits on Table A. See Linking data files . Linked SQL tables If there are range limit conditions in the selection formula, the program passes those conditions down to the server.
25 Understanding Databases Linking tables • 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).
Understanding Databases Linking tables program passes down the first condition, retrieves the data set that satisfies the condition, and then applies the second condition only to the retrieved data. The rule for AND situations is that the program passes down whatever conditions it can. 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.
25 Understanding Databases Linking tables Consideration 3 If the fields you are using from Table A are not indexed, but there is an indexed field that you can use in your record selection request, use that field. For example, assume that you have three products (Product 1, Product 2, and Product 3) and you want to identify all sales of Product 2 in the U.S. There is no index on the Product field but there is an index on the Order Date field.
Understanding Databases Linking tables • Some DBMS applications allow you to convert the field value to another data type in the index. For instance, the field in the table can be numeric, while the index converts the field value to a string. However, if you choose to use that field to link to another table, you must link to a field of the original data type. You cannot link a string value to a numeric field that has been converted to a string in the index.
25 Understanding Databases Linking tables For example, if you have chosen the Credit, Customer, Orders, and Orders Details tables from the Xtreme sample database, the links tab shows the tables linked as follows. In this case, the links will be processed first between the Credit/Customer tables, then between the Customer/Orders tables, and finally between the Orders/Orders Details tables.
Understanding Databases Linking tables Note: When you link fields using joins, no indexed fields are required.
25 Understanding Databases Linking tables 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.
Understanding Databases Linking tables every customer who has not placed any orders. These customers appear at the end of the list with blanks in the fields that would otherwise hold order information: 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.
25 Understanding Databases Linking tables Customer Table Customer Table Orders Table Customer ID Customer Name Order Amount 55 Deely MTB Inc. 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 an SQL database directly (not through ODBC), Crystal Reports uses a syntax native to the database.
Understanding Databases Linking tables Customer Table Orders Table Orders Table Customer ID Order ID Order Amount 53 11 19164.30 53 21 1683.60 57 4 15716.40 58 20 1956.20 60 16 24580.50 62 19 7911.80 63 28 19766.20 63 32 12763.95 64 14 8233.50 25 10320.87 Note: Left Outer and Right Outer joins are handled differently in the SQL language from other join types. If the database is accessed through ODBC, Crystal Reports uses ODBC syntax in the SQL statement.
25 Understanding Databases Linking tables like in an SQL statement, refer to Microsoft ODBC documentation or to the documentation for your SQL database. Full Outer join A Full Outer join is a bidirectional outer join where you can see all records in your linked tables. The result set from a Full Outer join includes all the records in which the linked field value in both tables is an exact match.
Understanding Databases Linking tables Customer Table Orders Table Orders Table Customer ID Order ID Order Amount 62 19 7911.80 63 28 19766.20 63 32 12763.95 64 14 8233.50 25 10320.87 65 66 Not Enforced When you select this option, the link you've created is used only if it's explicitly required by the Select statement. Your users can create reports based on the selected tables without restriction (that is, without enforcement based on other tables). This is the default option.
25 Understanding Databases Linking tables still include the join to TableA because it is enforced. Conversely, selecting only from TableA with the same join condition will not cause the join to TableB to be enforced. Note: For an explanation of from and to tables, see Link from and link to. Enforced To When you select this option, if the from table for the link is used, the link is enforced.
Understanding Databases Linking tables This statement produces the following data: 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.
25 Understanding Databases Linking tables Greater Than [>] link The result set from a Greater Than link includes all records in which the linked field value from the primary table is greater than the linked field value in the lookup table. As an example, a company may want to compare the salaries made by all their sales representatives to the salaries made by all their sales managers. The company executives want to make sure no sales representative is making more money than any manager.
Understanding Databases Linking tables SalesRep Table SalesRep Table Manager Table Manager Table Last Name Salary Last Name Salary Dodsworth $48,300.00 Fuller $32,000.00 Dodsworth $48,300.00 Brid $30,000.00 Dodsworth $48,300.00 Buchanan $29,500.00 Dodsworth $48,300.00 Martin $35,000.00 Patterson $30,000.00 Buchanan $29,500.00 In this table, there is no relationship established between sales representatives and sales managers.
25 Understanding Databases Linking tables WHERE SalesRep.'Salary' >= Manager.'Salary' This statement might produce data such as this: 690 SalesRep Table SalesRep Table Manager Table Manager Table Last Name Salary Last Name Salary Davolio $35,000.00 Fuller $32,000.00 Davolio $35,000.00 Brid $30,000.00 Davolio $35,000.00 Buchanan $29,500.00 Davolio $35,000.00 Martin $35,000.00 Dodsworth $48,300.00 Hellstern $45,000.00 Dodsworth $48,300.00 Fuller $32,000.00 Dodsworth $48,300.
Understanding Databases Linking tables 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.
25 Understanding Databases Linking tables Manager Table Manager Table SalesRep Table SalesRep Table Last Name Salary Last Name Salary Buchanan $29,500.00 Dodsworth $48,300.00 Buchanan $29,500.00 Patterson $30,000.00 Martin $35,000.00 Dodsworth $48,300.00 Hellstern $45,000.00 Dodsworth $48,300.
Understanding Databases Linking tables Manager Table Manager Table SalesRep Table SalesRep Table Last Name Salary Last Name Salary Fuller $32,000.00 Davolio $35,000.00 Fuller $32,000.00 Dodsworth $48,300.00 Brid $30,000.00 Davolio $35,000.00 Brid $30,000.00 Dodsworth $48,300.00 Brid $30,000.00 Patterson $30,000.00 Buchanan $29,500.00 Davolio $35,000.00 Buchanan $29,500.00 Dodsworth $48,300.00 Buchanan $29,500.00 Patterson $30,000.00 Martin $35,000.
25 Understanding Databases Linking tables Not Equal [!=] link The result set from a Not Equal link includes all records in which the linked field value in the primary table is not equal to the linked field value in the lookup table. This type of link can be used to find possible combinations of items when a table is joined to itself (a self-join). For example, a company can have a table listing all products they sell.
Understanding Databases Linking tables Product1 Product2 Product Name Product Name Xtreme Mtn Lock Xtreme Adult Helmet 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
25 Understanding Databases Using SQL and SQL databases Using SQL and SQL databases Perhaps the most popular and most powerful database formats are DBMS applications based on the Structured Query Language (SQL).
Understanding Databases Using SQL and SQL databases An SQL query is an SQL statement designed specifically to request data from one or more SQL databases. Some SQL applications require that you type in an SQL query directly using a text editor, while others provide graphical user interfaces that lead you through the process of querying an SQL database. In the latter case, the application must create an SQL statement based on the information you provide.
25 Understanding Databases Using SQL and SQL databases network. Because of the high processing demands required by a network server, the computer used as the server is often a high-powered, fast machine that may contain multiple processors, multiple hard drives, and multiple CD-ROM drives. A network client is a single computer workstation that is used regularly by one or more company employees. A user works on the client and accesses data and applications from the server over the network.
Understanding Databases Using SQL and SQL databases Do not confuse server applications with network server computers. Both are often referred to as servers. However, a server application resides on a network server, taking advantage of the hardware and operating system capabilities of the server machine, while a network server is a physical machine to which network clients are connected by cables or some other connection device.
25 Understanding Databases Using SQL and SQL databases 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. A stored procedure can be used to define an SQL query that you can use over and over again.
Understanding Databases Using SQL and SQL databases do the sifting and gets back a much smaller set of data, thus reducing the time and resources your workstation must use in order to finish the report.
25 Understanding Databases Using SQL and SQL databases 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 WHERE The WHERE clause has two purposes: • To specify record selection criteria. • To show how two database tables are joined.
Understanding Databases Using SQL and SQL databases ORDER BY The ORDER BY clause specifies that the database records retrieved be sorted according to the values in a specific field. If you do not use the ORDER BY clause, the program retrieves records in the order in which they appear in the original database.
25 Understanding Databases Server-side processing MYTABLE.'STATE', MYTABLE.'ZIPCODE' Server-side processing Server-side processing allows you to set up a report that performs the majority of its processing on the server and pushes only relevant details to your computer. Server-side processing provides you with a number of benefits: • Less time connected to the server. • Less memory needed to process the report on your computer. • Lower transfer time from the server to the client.
Understanding Databases Server-side processing amount of processing that must take place on the client side. If the Details section is shown, server-side processing will not be possible. • In some cases, formula fields must be processed on the client side. If grouping is based on a formula field, or if a formula is used in a summary field, then all the records must be transferred to the client side before the formula can be evaluated. This will increase the amount of time required to run the report.
25 Understanding Databases Server-side processing enabled, individual aspects of server-side processing will modify the SQL statement in different ways. • If you select Use Indexes Or Server For Speed (in the Report Options dialog box), the program adds an ORDER BY clause to the SQL statement and a WHERE clause for the record selection formula, if possible. • If you group on a linkable data type in the DBMS, the program adds a GROUP BY clause to the SQL statement.
Understanding Databases Mapping database fields This check box is inactive if Use Indexes or Server for Speed is not selected. 3. Click OK. 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.
25 Understanding Databases Mapping database fields • The lower-right box displays the names of mapped database fields. When you map fields in the upper boxes, they appear in the lower boxes. For each database field that you have changed, highlight the report field and the database field in the upper sections and click Map. The field names move from the upper boxes to the lower boxes. You do not have to remap every report field.
Understanding Databases Mapping database fields You can use each of these commands for a specific function; however, any of these commands will open the Map Fields dialog box if the program detects a mismatch between the field names in the report and the field names in the database or universe. In order to detect any possible mismatches, the program checks each field name in the report against the field names in the database or universe.
25 Understanding Databases Mapping database fields • If there is a check mark beside Verify on First Refresh, the option is active (the option is active by default for new reports). • If there is no check mark beside it, the option is inactive. Using the Set Datasource Location process When you choose Set Datasource Location from the Database menu and specify a new location for the active database or universe, the program checks the database or universe for changes.
Understanding Databases Saved Data Indexes • If the program detects no changes in the active database, the message box displays this message: "The database is up to date." In this case, click OK and return to your work. • If the program detects a change(s) in the active database, the message box displays this message: "The database file ["table name"] has changed. Proceeding to fix up the report!" 2. Click OK.
25 Understanding Databases Saved Data Indexes considerable performance gains—especially in larger reports—by indexing fields that are referred to by record selection formulas. Note: The benefits of Saved Data Indexes are largely unnoticeable in reports whose record selection returns fewer than 10,000 records. Saved Data Indexes are especially useful when you schedule your Crystal reports for viewing through BusinessObjects Enterprise.
Understanding Databases Saved Data Indexes grouping, sorting, or formatting of the report doesn't change at all. The indexes merely allow Crystal Reports to locate particular records quickly, without passing through the saved data in its entirety.
25 Understanding Databases Unicode support in Crystal Reports these criteria, then you should prioritize the fields and index only some of them. • Don't index fields that contain unique values only. For instance, don't index a field such as "Last Year's Sales," whose values are likely to be distinct from one another. If you do so, a separate index is created for each and every value in the field. To index saved data 1. Open your report in the Crystal Reports. 2.
Accessing Data Sources 26
26 Accessing Data Sources Introduction 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 • 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.
26 Accessing Data Sources Direct access database files In addition, data access is simple. Direct access database files are point-and-click data sources. You need only select the required database files, and Crystal Reports will read all the stored data. Disadvantages 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.
Accessing Data Sources Direct access database files Crystal Reports can then use a native method of communication to talk to the translation files. Data translation Data is translated through a set of DLLs specific to Crystal Reports. The program uses the DLLs specific to a certain data type to understand how data is organized for that type and to present it correctly when your report is printed, previewed, or exported.
26 Accessing Data Sources Direct access database files different set of DLLs. However, some formats expand the basic three-tiered structure. The following sections cover the systems used by Crystal Reports to access data from some of the most popular database formats. Microsoft Access Microsoft Access provides several means for opening its database files. Each method has its advantages and disadvantages, and the technique that you should use may depend on how your data is set up.
Accessing Data Sources Direct access database files Paradox Files created with Paradox (.DB) are made available to other applications through the Borland Database Engine (BDE). The BDE does the actual work with the Paradox data, retrieving the requested tables and fields. Since the BDE works so closely with the actual data, it combines with the Paradox database file to create the database layer in the three layer data access model. Crystal Reports accesses the BDE through the crdb_p2bbde.
26 Accessing Data Sources Direct access database files To translate information and data to and from DAO, the Report Designer uses the DAO translation file crdb_dao.dll. Secured Microsoft Access Databases If you will be using secured Access databases, the SystemDB parameter in the Windows Registry database must be set to point at the path where the System.mdw (Access 95 and later) file is located.
Accessing Data Sources Direct access database files Pervasive DDF files Crystal Reports does not determine the definitions of Pervasive data files directly from the data files themselves. It needs a set of Pervasive Data Definition Files (.DDF) that contain file, field, and index information. Crystal Reports uses Wbtrvdef.dll and Sbtrvdef.dll to parse these DDF files. The following are the required DDFs which must all reside in the same directory: • File.ddf • Field.ddf • Index.
26 Accessing Data Sources Direct access database files • • • Distribution Lists 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 sou
Accessing Data Sources Direct access database files File name Data source crdb_p2srepl.dll Exchange Public Folder Replica crdb_p2sacl.dll Public Folders Access Control Lists (ACL) crdb_p2soutlk.dll p2soutlk.dll Physical Server DLL for Microsoft Outlook Exchange translation files work directly with the Microsoft Messaging API (MAPI). MAPI acts as a database engine for Exchange data.
26 Accessing Data Sources Direct access database files Crystal Reports lets you report on your Outlook data by providing the translation files crdb_p2soutlk.dll and p2soutlk.dll. Because Outlook data is stored in a flat file system, the translation file accesses it directly. Note: You must have Microsoft Outlook installed, or available through UNC (universal naming convention) read-access. UNC is a standard format for paths that include a local area network file server.
Accessing Data Sources Direct access database files Sybase Adaptive Server Crystal Reports opens SQL data created by Sybase Adaptive Server directly through the crdb_p2ssyb10.dll, installed with Crystal Reports. This translation file works with the Sybase database drivers to read Sybase Adaptive Server data. If your Sybase server is correctly configured, you will be able to read Sybase data as soon as Crystal Reports is installed.
26 Accessing Data Sources Direct access database files Informix client installed and configured properly before using this translation file. DB2 Server IBM's DB2 Server is an SQL compliant, client/server application that is part of IBM's popular database management suite. Crystal Reports opens SQL data created by DB2 through the crdb_p2sdb2.dll, installed with Crystal Reports. This translation file works with the DB2 database drivers to read DB2 data.
Accessing Data Sources Direct access database files File Description 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. Local file system You can use Crystal Reports to report on files located on your system or network drives including information such as file name, version number, date, and so on.
26 Accessing Data Sources ODBC data sources files communicate with the Event Logging API in Advapi32.dll, a part of the Windows NT operating system. 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. These log files keep track of different types of events that occur when using a web server and browser.
Accessing Data Sources ODBC data sources accessed by a single application. An application need only communicate with one set of files (ODBC) to be able to work with any source of data that can be accessed by ODBC. There are hundreds of Database Management Systems (DBMS) available for personal computers, and thousands of applications that access DBMS data.
26 Accessing Data Sources ODBC data sources Finally, by using SQL pass-through technology to send an SQL statement to ODBC and retrieve an initial set of data, Crystal Reports off-loads much of the data retrieval and sorting work on to the server system, freeing up local memory and resources for more important tasks. In addition, only the data specified by the SQL statement is returned to Crystal Reports, reducing network traffic and the use of network resources.
Accessing Data Sources ODBC data sources • Crystal Reports layer • ODBC translation layer • ODBC layer • DBMS translation (ODBC data source) layer • Database layer By using the Structured Query Language (SQL), all five layers can conveniently pass data from the database to your report. Crystal Reports layer When working with ODBC data, Crystal Reports generates an SQL statement that requests the appropriate data from ODBC.
26 Accessing Data Sources ODBC data sources ODBC uses the SQL language for all transactions between Crystal Reports and ODBC. Even if the database does not normally use SQL to create and work with tables, the ODBC driver provided by the database (the DBMS translation layer) must communicate with ODBC using SQL. For most users, this feature of ODBC is transparent, but more advanced users often take advantage of the features of the SQL language used by ODBC.
Accessing Data Sources ODBC data sources Note: ODBC drivers find their specific DBMS client files on the local machine mainly through key directories that the DBMS client has installed in the search path. The important thing to remember is that a workstation client on a local PC must be able to connect to its server successfully. If you are not sure how to verify this, contact your IT manager.
26 Accessing Data Sources ODBC data sources 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. An X appears next to the option and the Feature Description area includes a remark that the feature will be completely removed. 6. Click Next on this dialog box and the following one. The data access option you selected is removed. 7. Repeat steps 2 through 4. 8.
Accessing Data Sources ODBC data sources 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).
26 Accessing Data Sources ODBC data sources Reports provides several DataDirect ODBC drivers through download, allowing you to access many of the most popular SQL databases, including: • Informix • 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.
Accessing Data Sources ODBC data sources Visual FoxPro Microsoft Visual FoxPro data is accessed through ODBC, while FoxPro data from version 2.6 and earlier is accessed directly through the xBase engine. If you are using FoxPro version 2.6 or earlier, see dBASE, FoxPro, Clipper. Lotus Domino 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.
26 Accessing Data Sources JDBC data sources JDBC data sources Java Database Connectivity (JDBC) is a Java API developed by Sun Microsystems that acts as an interface between a developer's Java code and a database. JDBC provides a mechanism for the developer to use to connect to a specified database, request information about the database, and then select information from it.
Accessing Data Sources JDBC data sources Five layers The process by which Crystal Reports accesses data from a JDBC data source consists of five layers. • Crystal Reports layer • JDBC translation layer • JDBC layer • DBMS translation (JDBC data source) layer • Database layer Crystal Reports layer The Crystal Reports layer is database-driver independent, but it is used to drive all of the remaining layers. JDBC translation layer Crystal Reports uses the Dynamic Link Library crdb_jdbc.
26 Accessing Data Sources JDBC data sources this feature of JDBC is transparent, but more advanced users often take advantage of the features of the SQL language used by JDBC. DBMS translation (JDBC data source) layer This layer consists of one or more drivers provided by a DBMS that allow JDBC to communicate with the database. If you are unsure whether you can use an JDBC driver to access the data in your database, refer to the documentation for your DBMS application.
Accessing Data Sources Business Objects universes Configuring the Crystal Reports JDBC driver When you install Crystal Reports, the following Crystal Reports JDBC driver files are added to your system: • • In the \Program Files\Business Objects\common\3.5\bin directory: • crdb_jdbc.dll • crdb_jdbc_res_en.dll In the \Program Files\Business Objects\common\3.5\java directory: • • CRConfig.xml In the \Program Files\Business Objects\common\3.5\java\lib directory: • CRDBJavaServer.jar CRConfig.
26 Accessing Data Sources Business Views Note: • • • When you report off a universe in Crystal Reports, the query that is produced is based on the structure of the database the universe references as it was when you designed the report. If the universe changes, you must return to the Query Panel to propagate the changes in the report. See Editing an existing query for more information.
Accessing Data Sources Crystal SQL Designer files • Business Elements Users can access Business Views through products such as Crystal Reports and the Report Application Server. 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.
26 Accessing Data Sources Crystal Dictionary files BusinessObjects Enterprise Repository and shared between many users. For more information, search for the topic called "Defining an SQL Command" in the Crystal Reports Online Help. A Crystal SQL Designer file gathers data from ODBC data sources (such as SQL servers) by means of SQL (Structured Query Language). The SQL DBMS handles all of the actual data gathering, sorting, and grouping, according to the instructions in the SQL statement.
Accessing Data Sources Crystal Dictionary files Note: The file crdb_dictionary.dll must be installed before you can use dictionary files in your reports. Crystal Reports reads the dictionary file by using the P2ixbse.dll file. This translation file is based on the xBase engine used to access dBASE, FoxPro and Clipper databases, but it provides all the flexibility needed to read dictionaries. See dBASE, FoxPro, Clipper.
26 Accessing Data Sources Crystal Dictionary files 748 Crystal Reports 2008 SP3 User's Guide
Report Processing Model A
A Report Processing Model Overview Overview Crystal Reports uses a three-pass reporting method to generate reports. The sections below describe what happens during each step of this process. To see a visual representation, refer to the flow-chart at the end of this section. What is a "pass"? A pass is a process that Crystal Reports uses each time the data is read or manipulated. Depending on the complexity of the report Crystal Reports may make 1, 2, or 3 passes over the data.
Report Processing Model Overview Formulas that contain references to subtotals or summary information are processed in the second pass. • Application of the record selection locally. If the record selection is too complex to be pushed down to the database, it is applied by Crystal Reports in this step. • Application of the saved data record selection formulas. When records return to the report, they are further filtered through any existing saved data formulas. • Sorting, grouping, and totaling.
A Report Processing Model Overview • Group selection formulas. • Running totals. • Calculation of formulas marked "WhilePrinting Records." 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.
Crystal Reports Error Messages B
B Crystal Reports Error Messages Drive:\filename.extension Drive:\filename.extension This document could not be opened. It does not appear to be a Crystal Reports document. Context Occurs when attempting to open a Crystal report file that does not have an RPT extension. Reason This error occurs because the report has become corrupted. Resolution A backup copy of the report must be used in place of the corrupted copy. Drive:\test.rpt This document could not be opened.
Crystal Reports Error Messages Failed to load database connector 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.
B Crystal Reports Error Messages 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. Resolution • Ensure that the data source required by the report is available and properly configured on all machines where the report can be run. For example, the ODBC data source or JNDI data source exists and connectivity can be proven from a client test tool.
Crystal Reports Error Messages Failed to retrieve data from the database. Details: [Database Vendor Code: ] In the Crystal Reports designer, verify that the SQL query, as shown under the Database menu Show SQL Query option does not contain syntax errors. If the SQL query shown in Crystal Reports is valid, execute the same SQL query through a database client test tool.
B Crystal Reports Error Messages Failed to retrieve data from the database.
Creating Accessible Reports C
C Creating Accessible Reports About accessibility About accessibility When you create Crystal reports for a large audience across the organization—and around the world—you need to account for the diverse needs of that audience. Report designers often create reports for specific languages, countries, job tasks, or work groups, but it is also important to consider the accessibility requirements of users.
Creating Accessible Reports About accessibility 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.
C Creating Accessible Reports About accessibility If you build accessible features into your reports now, it will be significantly less expensive than to redesign existing reports later. About the accessibility guidelines The most comprehensive accessibility guidelines are the Web Content Accessibility Guidelines (WCAG), developed by the international World Wide Web Consortium (W3C).
Creating Accessible Reports Improving report accessibility Accessibility and Business Objects products Business Objects products allow you to design accessible reports and deliver them to your users via the Web. By observing accessibility guidelines, you can use Crystal Reports to create reports that are accessible to users with disabilities.
C Creating Accessible Reports Improving report accessibility The following sections provide tips and guidelines on how to design reports with accessibility in mind. Click the appropriate link to jump to that section: • Placing objects in reports • Text • Color • Navigation • Parameter fields Placing objects in reports There are a few general guidelines to keep in mind when you place objects on a report.
Creating Accessible Reports Improving report accessibility 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. When you publish the report to BusinessObjects Enterprise, it looks the same as it did in Crystal Reports, but the underlying HTML displays the database field headings first, followed by the title.
C Creating Accessible Reports Improving report accessibility Text The most common accessibility issue encountered by report designers is also one of the easiest to resolve: providing text-only versions of non-text objects. A non-text object is an object that conveys meaning through a picture or sound. Non-text objects include pictures, charts, graphical buttons, graphical representations of text, sounds, animations, and audio or video clips.
Creating Accessible Reports Improving report accessibility Describe the purpose of the non-text object. For example, if an image performs an action when you click it, describe the action. For a button that opens your web site, provide a text box labeled "Click to view our web site". • If a report includes audio links, provide a transcript for significant audio clips. • If a report links to a multimedia or video presentation, provide a transcript.
C Creating Accessible Reports Improving report accessibility To add a text-only alternative to a subreport 1. Create a text-only version of the report and save it. 2. Open a new report. 3. On the Insert menu, click Subreport. 4. In the Insert Subreport dialog box, select Choose an existing report and click Browse to locate the report you created in step 1. 5. Click the subreport, then choose Format Subreport from the Format menu. 6. In the Format Editor, on the Subreport tab, select On-demand Subreport.
Creating Accessible Reports Improving report accessibility 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.
C Creating Accessible Reports Improving report accessibility equivalent. Adding text descriptions for decorative objects can produce unnecessary clutter. Text versions of visual or auditory objects in reports should be used as a complement to the object—not as a replacement. You do not need to remove non-text objects. Visual objects in reports can be very helpful, especially for people with learning disabilities such as attention deficit disorder, or for people who are deaf.
Creating Accessible Reports Improving report accessibility Contrasting colors Users with limited vision may be unable to distinguish between colors. To test the color contrast in your report, print or view a black and white copy. You should be able to distinguish between values or fields displayed in different colors (in a pie chart, for example). If you cannot distinguish between colors on the report, try different colors or use gray shading.
C Creating Accessible Reports Improving report accessibility • Hyperlinks Using color as the only method for identifying hyperlinks may also cause problems for color-blind users. When you print your report in black and white, check the hyperlinks to ensure that they are still visible. • Identifying important areas of the report Do not organize a report by using color as a background or as a separator between different sections or areas.
Creating Accessible Reports Designing for flexibility 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. Parameter fields should follow these guidelines: • Provide a list of default values for the user to choose from.
C Creating Accessible Reports Designing for flexibility to choose their own accessibility options using a parameter field that prompts them to choose whether or not to display accessible formats. Using this parameter field, you can conditionally format objects, or conditionally suppress sections that address different access needs. Or you can provide different display options by using subreports.
Creating Accessible Reports Designing for flexibility 4. Click the Formula button that corresponds to the Size list. The Format Formula Editor opens a new formula named Font Size. 5. In the Formula text window, type this formula (which uses Crystal Syntax): if {?Access} = "Yes" then 20 else 10 This formula ensures that the font size for the currently selected field is increased from 10 point to 20 point when the user chooses to display accessible formatting. 6. Click Save and close. 7.
C Creating Accessible Reports Designing for flexibility To suppress an accessible section 1. Right-click the left boundary of the section you want to suppress conditionally, and click Section Expert. 2. In the Section Expert, click the Formula button that corresponds to the Suppress (No Drill-Down) setting. The Format Formula Editor opens a new formula named Suppress (No Drill-Down). 3.
Creating Accessible Reports Improving data table accessibility If you want only screen readers to be able to see the subreport, you can hide it by changing the subreport link to the same color as the background. Alternatively, you can use the ?Access parameter field to allow users to choose whether or not the subreport appears in the report. Place the subreport in its own section and conditionally suppress the section based on the ?Access parameter field.
C Creating Accessible Reports Improving data table accessibility 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. For example, if a report displays employee names and salaries, you can add a text object before the Salary database field that reads "{Last Name}'s salary is ".
Creating Accessible Reports Improving data table accessibility 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.
C Creating Accessible Reports Improving data table accessibility Labelling data tables conditionally Although adding text objects is relatively easy to implement, it does not address all accessibility concerns. Invisible text is read by screen readers, but does not help people with limited vision. You can allow the user to choose whether or not to display text descriptions in the data table by conditionally formatting or suppressing text objects.
Creating Accessible Reports Improving data table accessibility Note: The report shown also uses the ?Access parameter field to enable the Can Grow option (also on the Common tab of the Format Editor) and increase the font size for people with visual impairments. When the user chooses No for the ?Access parameter field, the conditional formula suppresses the text objects, leaving spaces in the report in place of the text objects.
C Creating Accessible Reports Improving data table accessibility This report uses the following formulas: @Employee ID If {?Access}="Yes" then "Employee ID " + ToText({Employee.Employee ID},0) + ". " else ToText({Employee.Employee ID},0) @Last Name If {?Access}="Yes" then "Employee last name is " + {Employee.Last Name} + "." else {Employee.Last Name} @Salary If {?Access}="Yes" then {Employee.Last Name} + "'s Salary is " + ToText({Employee.Salary}) + "." else ToText({Employee.
Creating Accessible Reports Improving data table accessibility Note: • • The report also uses the ?Access parameter field to enable the Can Grow option and increase the font size. In @Employee ID, ?Access parameter field has been set to "0" to enable the Can Grow option and increase the font size. When the user chooses No for the ?Access parameter field, the formula returns only the data. The report does not display blank spaces in place of the conditional text objects.
C Creating Accessible Reports Accessibility and BusinessObjects Enterprise • Ensure that headings provide enough information to clearly identify the values that they label. • To test a table's accessibility, read its headings and values in a linear fashion from left to right and from top to bottom. For example, if a report displays last and first name fields for each customer, it may read better if it displays first name followed by last name.
Creating Accessible Reports Accessibility and customization are not currently accessible to everyone, the BusinessObjects Enterprise web desktop and the DHTML viewer allow for accessible access to reports over the Web. Several enhancements have been made to BusinessObjects Enterprise to account for accessibility issues. Text descriptions are now provided in ALT tags for the toolbar buttons and other images.
C Creating Accessible Reports Accessibility and customization If you customize Crystal reports or the BusinessObjects Enterprise web desktop extensively, you may encounter other accessibility issues. For online resources that provide comprehensive accessibility guidelines, see Re sources. The following list provides some common accessibility issues that may cause problems when you customize Crystal Reports or BusinessObjects Enterprise content.
Creating Accessible Reports Resources 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. • Applets and plug-ins If a report needs an applet, plug-in, or other application on the client machine in order to interpret page content, the plug-in or applet must follow accessibility guidelines.
C Creating Accessible Reports Resources • the Government of Canada Internet Guide: http://www.cio-dpi.gc.
More Information D
D More Information Information Resource Location SAP BusinessObjects product http://www.sap.com information 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 https://cw.sdn.sap.com/cw/community/docupedia Docupedia Docupedia provides additional documentation resources, a collaborative authoring environment, and an interactive feedback channel. https://boc.sdn.sap.com/ Developer resources https://www.sdn.sap.com/irj/sdn/businessobjects-sdklibrary SAP BusinessObjects articles https://www.sdn.sap.com/irj/boc/businessobjects-articles on the SAP Community NetThese articles were formerly known as technical papers.
D More Information Information Resource Location http://www.sap.com/services/bysubject/businessobjectscon sulting Consulting 792 Consultants can accompany you from the initial analysis stage to the delivery of your deployment project. Expertise is available in topics such as relational and multidimensional databases, connectivity, database design tools, and cus tomized embedding technology.
Index 3-D Riser chart 370 3-D Surface chart 370 A absolute formatting 330 Access 720 database 720, 736 via ODBC 736 via the DAO Engine 721 accessibility 760 and BusinessObjects Enterprise 784 and Crystal Reports 760 benefits of 760 design considerations 763 guidelines 762 resources 787 accounting conventions, using 343 ACT! database 726 ActiveX Data Objects 728 adding 504 calculations to OLAP grid 504 filters to OLAP grid 503 ADO 728 ADO .NET DataSet 33 Alerts.
Index Bottom N 244 selecting groups 244 selecting groups conditionally 247 selecting percentages 244 selecting percentages conditionally 247 sorting 751 boxes 337 expected formatting behavior 337 formatting 337 inserting 336 branding, customizing 51 bubble chart 370 Business Views 744 BusinessObjects Enterprise 163 clustering 163 evaluating date functions 165 LDAP 163 load balancing 163 management 163 scaling 163 scheduling 163 security 163 versioning 163 BusinessObjects Enterprise Repository.
Index charts (continued) types (continued) histogram 370 line 370 line, zooming 384 numeric axis 370 pie 370 radar 370 stock 370 using underlay feature with 386 XY Scatter 370 zooming features 384 clauses 701 DISTINCT 701 FROM 701 GROUP BY 703 ORDER BY 703 SELECT 701 WHERE 702 client/server architecture 697 and Informix Online Server 727 server 704 server-side processing 704 Clipper 720 collapsing dimension members 499 color and accessibility 770 contrast 770 color, adding 331 combining, sorting and groupi
Index Crystal SQL Designer files, ODBC data sources 745 Crystal syntax creating formula 572 currency fields, formatting with Highlighting Expert 356 custom banners 51 custom functions adding to repository 153 using in Formula Expert 573 D DAO Engine, Microsoft Access 721 data 212 ascending sort order 212, 218 BLOB fields 127 creating custom groups 220 cross-tabs 430 descending sort order 212, 218 formatting for accessibility 777 formatting for reports 131 grouping 76, 217 hierarchically 233 in intervals 2
Index database types (continued) SQL 737 Sybase Adaptive Server 727 Visual FoxPro 739 Web/IIS log files 730 databases 658 alias 659 and performance 174 changing name and location 659 relational 658 sample 54 selecting 58 thread-safe drivers 176 DataDirect drivers ODBC 737 Date fields 334 customizing 334 formatting 333 date ranges, for record selection 201 Date/Time fields 334 customizing 334 formatting 333 dates, to select records 201 DB2 server 728 dBASE 720 DBMS, SQL 699 deleting 281 blank lines 281 sect
Index designing reports (continued) inserting (continued) sections 275 special fields 125 text objects 126 linking two or more database tables 120 manipulating data 94 merging related sections 276 modifying chart legend text 386 moving sections 276 multiple columns 305 organizing data 131 placing 373 charts 373 data 122 database fields on 122 formula fields 123 maps 394 parameter fields 124 running total fields 125 special fields 125 SQL expression fields 123 text objects 126 printing characteristics 95 re
Index equal count, on Ranged map 391 equal link 686 equal ranges option, Ranged map 391 error messages, described 754 Excel 737 database 737 via ODBC 737 Exchange with Crystal Reports 723 Exchange Folder, exporting to 515 expanding dimension members 499 experts chart 370, 381 highlighting 356 map 391 select 193 top N/sort group 243 explorers, docking 142 exporting 512 destinations 512 format types for exporting 507 reports 507 to a disk file 514 to an application 512 to an Exchange Folder 515 to an ODBC da
Index filters (continued) creating 468 editing 475 reference to operators 472 removing 475 using And or Or to combine 471 Flash objects 425 binding to an Xcelsius SWF 427 inserting in reports 426 Flash printing 32 fonts 352 changing conditionally 352 setting fractional sizes 325 footers, creating after the page 354 form letters 285 creating 285 inserting a date 287 inserting address 287 printing conditional messages 291 salutation 288 using text objects 283 Format Painter, using 361 formats, changing field
Index formulas (continued) debugging 579 evaluation time errors 580 deleting 578 editing 575 copies of formulas 578 enhanced record selection 177 for record selection 192 Formula Editor 566 Formula Expert 573 Formula Workshop 560 global search 575 inserting in reports 572 pushing down selection 178 record selection templates 199 removing 579 searching and replacing text 575 syntax 557 choosing 567 troubleshooting 203 types of 559 alerting formulas 559 conditional formatting formulas 559 report formulas 559
Index groups (continued) creating (continued) custom 220 running totals 263 running totals using formula 268 editing 241 selecting with Select Expert 225 sorting 243 on summarized values 243 records within 223 sorting conditionally 221 subtotaling 248 guidelines 317 designing with 317 inserting 317 positioning objects with 320 resizing objects with 320 snapping objects to 318 viewing 317 H headers 253 creating standard group 253 custom group, creating 256 drilling-down 257 live group headers 254 suppressi
Index JNDI, description 740 join types 678 full outer 684 inner 679 left outer 680 right outer 682 K key controls for Formula Editor 569 L languages, SQL 701 layouts 369 chart 369 map 390 left outer join 680 less than link 691 less than or equal to link 692 line chart 370 zooming 384 line spacing 324 lines 335 adding 335 adding blank conditional 281 expected formatting behavior 337 modifying 335 link relationships 665 link types 678 equal 686 greater than 688 greater than or equal to 689 less than 691 le
Index Map Expert 391 editing maps with 403 map layouts 390 Advanced 390 Cross-Tab 390 Group 390 OLAP 390 Map Navigator 408 hiding 408 showing 408 map types 390 Bar Chart 391 Dot Density 391 Graduated 391 Pie Chart 391 Ranged 391 maps 390 centering 408 changing 410 borders 410 geographic maps 407 layers 404 titles 403 type 404 creating 395 on details fields with Advanced layout 395 on group fields with Group layout 399 on OLAP cube with OLAP layout 401 data mismatches 406 drilling down 395 editing with Map
Index numeric values, using accounting conventions 343 O Object Package, adding to Workbench 139 objects 317 and guidelines 317 changing X position conditionally 353 copying and pasting OLE 417 cropping 342 dynamic OLE 420 embedded 422 formatting 69 linked bitmap image 424 linked vs.
Index P Page Footer, section 109 page footers 133 creating after first page 354 Page Header, section 109 page headers 133 page margins 327 Paradox database 721 parameter fields 588 adding dynamic grouping 636 and accessibility 773 conditional formatting with 626, 774 creating 627 report title with 627 with a cascading list of values 609 with a dynamic prompt 607 with a static prompt 602 deleting 621 filtering data 182 for performance 182 inserting 124 responding to prompts 623 setting sort order with 630 s
Index prompting (continued) creating a dynamic prompt that cascades 609 creating a static prompt 602 overview 588 prompts 469 building 469 combining with filters 471 properties conditional attribute 350 conditional on/off 349 prototypes, developing on paper 97 publishing to BusinessObjects Enterprise.
Index Report Alerts (continued) deleting 550 editing 549 referring to in formulas 551 viewing 550 Report Definition (TXT), exporting to 507 report design environment design solutions 302 importing text-based objects from a file 315 placing multi-line, text-based objects 314 placing text-based objects 310 pre-printed forms 305 section characteristics 302 setting page orientation and paper size 326 setting page orientation by section 326 TrueType fonts 328 report design, key strategies 163 report experts.
Index reports (continued) inserting (continued) sections 275 special fields 125 text objects 126 title 68 layout 92 making read-only 331 manipulating data 94 merging related sections 276 moving sections 276 optimizing performance 162 organizing data 131 overflow field representation 322 page headers and footers 133 parameter fields on 124 performance considerations 674 placing 122 charts 373 data on 122 database fields on 122 maps 394 special fields on 125 SQL expression fields 123 text objects on 126 prev
Index running totals 260 creating 262 for a group 263 using formula 268 one-to-many linking 267 S sample data 54 Save As dialog box 70 Save dialog box 70 saved data 168 Saved Data Indexes 711 choosing fields to index 713 considerations 713 saving reports 70 screen readers 760 Section 508, Rehabilitation Act 762, 787 sections 302 deleting 275 Details 109 identifying 111 inserting 275 making read-only 331 merging two related 276 moving 276 multiple in report 279 Page Header 109 Report Header 109 resizing 27
Index sorting (continued) Top N 751 with SQL expressions 188 sorts, adding to OLAP grid 501 spacing between text-based objects 315 indenting lines 321 selecting the grid 316 using the grid 315 special fields, inserting 125 specified sort order 218 SQL 696 and Crystal Reports 700 database considerations when linking 673 databases 673, 737 using 696 databases via ODBC 737 DBMS 699 join types 678 language 701 server-side grouping 705 stored procedures 177, 700 using expressions 185 SQL expression fields 123 c
Index T Tab Separated Text (TTX), exporting to 507 table indexes, for performance 174 tables 119, 777 adding 119 alias 659 deleting blank lines 281 indexed 662 link from 665 link processing order 677 link to 665 linked SQL 673 linking 665, 676 for performance 174 records 665 two 120 unindexed 653 Visual Linking Expert 676 tabs 108 Design 108 differences between Preview and Design 116 SQL Expressions 123 Template Field Object 297 templates 294 applying 295 choosing in wizard 295 considerations 299 reapplyin
Index V values 212 ascending sort order 212, 218 creating custom groups 220 currency, display 322 descending sort order 212, 218 numeric, display 322 parameter fields 588 sort direction 212 sorting single fields 213 sorting summarized group 243 variable length objects 279 vertical placement 324 Visual FoxPro database 739 Visual Linking Expert 676 W Web Accessibility Initiative 787 Web Content Accessibility Guidelines 762 Web Folders, working with 521 web reports, optimizing performance 162 Web/IIS log fil
Index 814 Crystal Reports 2008 SP3 User's Guide
