Mastering Crystal Reports 9

Chapter 1: Building Your First Report

Figure 1.1. Crystal Reports Welcome screen

Figure 1.2. Using the Report Wizard

Figure 1.3. Report Gallery

Figure 1.4. Available data sources

Figure 1.5. Connection dialog

Figure 1.6. Open dialog

Figure 1.7. Connection settings

Figure 1.8. An available data source

Figure 1.9. Database components

Figure 1.10. Data dialog

Figure 1.11. Selected table

Figure 1.12. Choosing fields

Figure 1.13. Browse Data window

Figure 1.14. Find Field window

Figure 1.15. Sequencing fields

Figure 1.16. Grouping the information

Figure 1.17. Group By Country

Figure 1.18. Summaries dialog

Figure 1.19. Summarization default

Figure 1.20. Count summarization method

Figure 1.21. Previewing the report

Figure 1.22. Preview mode

Figure 1.23. Group tree

Figure 1.24. Using the Group tree

Figure 1.25. Design mode

Figure 1.26. The five basic bands   

Figure 1.27. Design mode sections

Figure 1.28. Toolbars and menus

Figure 1.29. Indented group data

Figure 1.30. Drawing boxes

Figure 1.31. Adding a graphic

Figure 1.32. Print Date variable

Figure 1.33. Object resize handles

Figure 1.34. Last page of the report

Chapter 2: Modifying a Report

Figure 2.1. Open An Existing Report

Figure 2.2. Field Explorer

Figure 2.3. Database Fields

Figure 2.4. Field Explorer context menu

Figure 2.5. Dropping a field into position

Figure 2.6. Vertical guideline markers

Figure 2.7. Activating guidelines

Figure 2.8. New fields added

Figure 2.9. Grouping toolbar icons

Figure 2.10. Group Expert

Figure 2.11. Group alignment

Figure 2.12. Inserting a group

Figure 2.13. Insert Group dialog

Figure 2.14. Define Named Group dialog

Figure 2.15. Data that doesn’t meet the custom group criteria

Figure 2.16. Group header context menu

Figure 2.17. Sorting records

Figure 2.18. Group and summarize

Figure 2.19. Percentage summary

Figure 2.20. Groups, sorts, and summaries

Figure 2.21. Help file main menu

Figure 2.22. About Crystal Reports

Chapter 3: Formatting Fields and Objects

Figure 3.1. Report summary information

Figure 3.2. Report Title placeholder

Figure 3.3. Document Properties

Figure 3.4. Text objects

Figure 3.5. Format Editor - Common tab

Figure 3.6. Border options

Figure 3.7. Font options

Figure 3.8. Font problem

Figure 3.9. Paragaph options

Figure 3.10. Formatting Boolean data

Figure 3.11. Standard date formatting

Figure 3.12. Custom date formatting

Figure 3.13. Standard time formatting

Figure 3.14. Custom time formatting

Figure 3.15. Standard number formatting

Figure 3.16. Custom number formatting

Figure 3.17. Formatting boxes

Figure 3.18. Extending boxes

Figure 3.19. Formatting lines

Figure 3.20. Formatting pictures

Figure 3.21. Hyperlink options

Figure 3.22. Defining a report part

Figure 3.23. Linking to another report object

Figure 3.24. OLE components

Figure 3.25. Linking to Excel

Figure 3.26. Linking to a cell

Figure 3.27. Editing links

Figure 3.28. Embedding a BMP file

Chapter 4: Adding Business Logic with the Formula Workshop

Figure 4.1. The Report Explorer

Figure 4.2. Using formulas in the Format Editor

Figure 4.3. The Field Explorer

Figure 4.4. Naming a formula

Figure 4.5. The Formula Workshop and the Workshop Tree

Figure 4.6. Formatting Formula folder without all nodes displayed

Figure 4.7. Formatting Formula folder with all nodes displayed

Figure 4.8. New Formatting Formula

Figure 4.9. Section Expert New Page After option button indicating code with a different icon.

Figure 4.10. Adding a Repository function to the report

Figure 4.11. The complete Workshop Tree

Figure 4.12. The Find dialog

Figure 4.13. The Search Expert

Figure 4.14. Using conditions in the Search Expert

Figure 4.15. The Formula Editor

Figure 4.16. Finding things in the Formula Editor

Figure 4.17. Choosing the formula language in the Formula Editor

Figure 4.18. Expanding and collapsing categories in the Function Tree

Figure 4.19. Function Keyword Auto Complete with a typed value

Figure 4.20. Function Keyword Auto Complete without a typed value

Figure 4.21. The Formula Expert

Figure 4.22. Populating a parameter value in the Formula Expert

Figure 4.23. Additional choices

Figure 4.24. Saving a formula created with the Formula Expert

Figure 4.25. Dependencies on other custom functions

Figure 4.26. The Workshop Tree after using the Formula Expert

Figure 4.27. Viewing properties of custom functions

Figure 4.28. Control Structures for Crystal Syntax

Figure 4.29. Control Structures for Basic Syntax

Figure 4.30. Using a formula to format a background color

Figure 4.31. How a loop works

Figure 4.32. Starting a new custom function

Figure 4.33. Naming a custom function

Figure 4.34. A new function in the tree

Figure 4.35. AddSuffixToNumber function in Basic Syntax

Figure 4.36. AddSuffixToNumber function in Crystal Syntax

Figure 4.37. Custom function properties

Figure 4.38. Categorized custom functions

Figure 4.39. Calling a custom function

Figure 4.40. Color constants for formatting

Figure 4.41. The Function Tree and Operator Tree for SQL expressions

Figure 4.42. Finding performance information

Chapter 5: Working with the Crystal Repository

Figure 5.1. Sharing report objects

Figure 5.2. The Repository Explorer

Figure 5.3. The Workshop Tree

Figure 5.4. The folders in the repository

Figure 5.5. Adding text and bitmap objects

Figure 5.6. Adding custom functions

Figure 5.7. Adding a SQL command to the repository

Figure 5.8. Describing a SQL command object

Figure 5.9. A SQL command as a data source

Figure 5.10. Setting a global option to update connected repository objects

Figure 5.11. Updating connected repository objects when a report is opened

Figure 5.12. Adding a repository custom function to a report

Figure 5.13. Crystal Reports installation directory

Figure 5.14. Repository installation directory

Figure 5.15. or_objectdetails_30 table

Figure 5.16. or_objectdetails_31 table

Figure 5.17. or_objectdetails_32 table

Figure 5.18. or_objectdetails_33 table

Figure 5.19. or_objects table

Figure 5.20. or_type_schema table

Figure 5.21. ODBC data source setup

Figure 5.22. ORMAP.INI   

Chapter 6: Summarizing Information

Figure 6.1. A sorted report

Figure 6.2. Group Expert

Figure 6.3. Inserting a group

Figure 6.4. Setting group options

Figure 6.5. A report grouped on a field

Figure 6.6. Choosing a formula for a group

Figure 6.7. Manually indenting groups

Figure 6.8. A report grouped on a formula

Figure 6.9. Grouping on a date field

Figure 6.10. Moving or reordering a group

Figure 6.11. Hierarchical table

Figure 6.12. Hierarchical relationships

Figure 6.13. Hierarchical Options

Figure 6.14. Hierarchical report with group tree

Figure 6.15. Summarize Across Hierarchy

Figure 6.16. Coding a group selection formula

Figure 6.17. Group selection formula applied

Figure 6.18. Special grouping fields

Figure 6.19. Inserting a summary

Figure 6.20. Summarized number fields

Figure 6.21. Group Sort Expert

Figure 6.22. Top and Bottom N reporting

Figure 6.23. Top 1 with Others

Figure 6.24. Top 1 without Others

Figure 6.25. Creating a running total field

Figure 6.26. Running Total Expert

Figure 6.27. A running total result

Figure 6.28. Inserting a subreport

Figure 6.29. Creating a link in a subreport

Figure 6.30. An embedded subreport

Figure 6.31. Subreport menu

Figure 6.32. Subreport in Design mode

Figure 6.33. Formatting a subreport

Figure 6.34. Placing a value retrieval formula

Figure 6.35. Page size warning

Chapter 7: Selectively Refining a Report Using Data Values

Figure 7.1. The Select Expert

Figure 7.2. Comparison operators

Figure 7.3. An is one of selection

Figure 7.4. Additional conditions

Figure 7.5. Generated SQL query

Figure 7.6. Record selection formula

Figure 7.7. Coding a group selection formula

Figure 7.8. Parameter fields in the Field Explorer

Figure 7.9. A new parameter field

Figure 7.10. Setting default values for a parameter

Figure 7.11. Adding a parameter to a report query

Figure 7.12. Choosing a comparison field

Figure 7.13. Choosing a comparison operator

Figure 7.14. Choosing a comparison value

Figure 7.15. Ask the user a question or two.

Figure 7.16. Single and range value parameter

Figure 7.17. Unconditionally suppress fields

Figure 7.18. Suppress zero values

Figure 7.19. Text object with embedded fields

Figure 7.20. Suppressing embedded blank fields

Figure 7.21. Section

Figure 7.22. The Highlighting Expert

Figure 7.23. Coding a highlight

Figure 7.24. A highlighted field

Figure 7.25. Creating an alert

Figure 7.26. Editing an alert

Figure 7.27. Coding an alert

Figure 7.28. A triggered alert

Figure 7.29. Alert results

Figure 7.30. Alert function tree

Chapter 8: Customizing Sections

Figure 8.1. Band layout

Figure 8.2. Bands in Design mode

Figure 8.3. Designer settings

Figure 8.4. Section submenu

Figure 8.5. Drill-down sections

Figure 8.6. Drill-down tabs

Figure 8.7. Section Expert

Figure 8.8. Inserting sections with the Section Expert

Figure 8.9. New sections in Design mode

Figure 8.10. Formatting multiple columns

Figure 8.11. Data displayed in multiple columns

Figure 8.12. Object Size And Position

Figure 8.13. Setting a section background color

Figure 8.14. Color spectrum

Figure 8.15. Section spacing

Figure 8.16. Resizing a section

Figure 8.17. Select All Section Objects

Figure 8.18. Multiple selection of objects

Chapter 9: Working with Multiple Tables

Figure 9.1. Database components

Figure 9.2. Pulling data

Figure 9.3. Database Expert - Data tab

Figure 9.4. Database Expert - Links dialog

Figure 9.5. Browse Field

Figure 9.6. Sample data

Figure 9.7. Creating a manual link

Figure 9.8. Automatic links

Figure 9.9. Link types

Figure 9.10. Resorts

Figure 9.11. Finance companies

Figure 9.12. Regions

Figure 9.13. Region resorts

Figure 9.14. Joining data

Figure 9.15. Join types

Figure 9.16. Inner join

Figure 9.17. Left outer join

Figure 9.18. Left join data

Figure 9.19. Right outer join

Figure 9.20. Full outer join

Figure 9.21. More than two tables

Chapter 10: Data Sources and the Database Expert

Figure 10.1. The Database Expert

Figure 10.2. Data access architecture

Figure 10.3. ODBC layers

Figure 10.4. ODBC Data Source Administrator

Figure 10.5. Setting up a DSN

Figure 10.6. Database Expert ODBC connections

Figure 10.7. Data Source Selection

Figure 10.8. Connection Information

Figure 10.9. VistaNations ODBC connection

Figure 10.10. OLE DB

Figure 10.11. OLE DB Provider

Figure 10.12. OLE DB connection

Figure 10.13. SQL Server OLE DB

Figure 10.14. Connecting to OLAP data

Figure 10.15. Adding an OLAP server

Figure 10.16. OLAP cubes

Figure 10.17. Native drivers

Figure 10.18. Native drivers in Database Expert

Figure 10.19. Crystal SQL Designer

Figure 10.20. SQL query

Figure 10.21. Query results

Figure 10.22. Saving a .qry file

Figure 10.23. Crystal queries in the Database Export

Figure 10.24. Crystal Dictionary data source

Figure 10.25. Setting an alias

Figure 10.26. Determining the view

Figure 10.27. Database Files connectivity

Figure 10.28. DAO in the Database Expert

Figure 10.29. An XML file structure

Figure 10.30. Installing the XML ODBC driver

Figure 10.31. Configuring an XML location

Figure 10.32. Advanced options for XML configuration

Figure 10.33. The Database Expert with an XML data source

Chapter 11: Using SQL in Crystal Reports

Figure 11.1. Standalone PC database

Figure 11.2. Workgroup PC database

Figure 11.3. Database Files

Figure 11.4. Client/server databases

Figure 11.5. SQL dialects

Figure 11.6. Selecting cities

Figure 11.7. Selecting distinct cities

Figure 11.8. Using a WHERE clause

Figure 11.9. Grouping and sorting

Figure 11.10. SQL Expression Fields

Figure 11.11. SQL Expression Editor

Figure 11.12. Adding a SQL command

Figure 11.13. Modify Command dialog

Figure 11.14. Selected Tables

Figure 11.15. Field Explorer

Figure 11.16. Report based on a SQL command

Figure 11.17. Query parameter

Figure 11.18. Stored procedures as a data source

Figure 11.19. Stored procedure results

Figure 11.20. SQL database utilities

Figure 11.21. Changing a database

Chapter 12: Analyzing Data Visually

Figure 12.1. First page of report

Figure 12.2. A bar chart in a report

Figure 12.3. Chart Expert

Figure 12.4. Data options

Figure 12.5. Advanced layout option

Figure 12.6. X-axis and Y-axis

Figure 12.7. Chart Sort Order

Figure 12.8. Edit Summary

Figure 12.9. Setting chart options

Figure 12.10. Chart menu options

Figure 12.11. Chart templates

Figure 12.12. Custom chart templates

Figure 12.13. General chart commands

Figure 12.14. Customizing chart titles

Figure 12.15. Grid customizations

Figure 12.16. Changing chart item fonts

Figure 12.17. Changing a chart’s viewing angle

Figure 12.18. Z coordinate series settings

Figure 12.19. Map files

Figure 12.20. Map layer files

Figure 12.21. Geographic map

Figure 12.22. Map Expert

Figure 12.23. Advanced map layout

Figure 12.24. Maps based on cross-tab grids

Figure 12.25. Map types

Figure 12.26. Map menu

Figure 12.27. Layer Control

Figure 12.28. Resolving a mapping mismatch

Figure 12.29. Cross-tab example

Figure 12.30. Cross-Tab Expert

Figure 12.31. Cross-Tab Styles

Figure 12.32. Customizing a style

Figure 12.33. Formatting grid lines

Figure 12.34. Cross-tab options

Figure 12.35. Cross-tab after interaction

Figure 12.36. An example of an OLAP grid

Figure 12.37. Starting the OLAP process

Figure 12.38. Choosing an OLAP data source

Figure 12.39. Adding an OLAP server

Figure 12.40. The OLAP Expert

Figure 12.41. Adding rows and columns

Figure 12.42. Adding slices

Figure 12.43. OLAP grid styles

Figure 12.44. Customized OLAP styles

Figure 12.45. OLAP menu options

Figure 12.46. OLAP grid after interaction

Figure 12.47. Analyzer’s Cube View

Figure 12.48. Row menus

Figure 12.49. Highlighting threshold values

Figure 12.50. Time - first dimension

Figure 12.51. Measures - second dimension

Figure 12.52. Product - third dimension

Figure 12.53. Yearly Income - fourth dimension

Figure 12.54. Dimensions rearranged

Chapter 13: Report Templates

Figure 13.1. Template files

Figure 13.2. Storage location

Figure 13.3. Document Properties

Figure 13.4. Template field placement

Figure 13.5. Template menu

Figure 13.6. Format Editor

Figure 13.7. Formula Workshop

Figure 13.8. Template Expert

Figure 13.9. Standard Report Creation Wizard—Optionally Applying A Template

Figure 13.10. Removing a template

Chapter 14: The Report Engine Processing Model

Figure 14.1. Three-pass processing

Figure 14.2. Page count indicator

Chapter 15: Configuring Your Design Environment

Figure 15.1. Global database options

Figure 15.2. Global data source settings

Figure 15.3. XML Expert

Figure 15.4. Global reporting options

Figure 15.5. Global layout options

Figure 15.6. Global field formatting

Figure 15.7. Global font formatting

Figure 15.8. Global formula editor settings

Chapter 16: Deploying Your Report

Figure 16.1. Creating Report Bursting Indexes

Figure 16.2. Switching databases

Figure 16.3. Link Options

Figure 16.4. Mapping fields

Figure 16.5. Database verified

Figure 16.6. Database verification options

Figure 16.7. Updating repository objects

Figure 16.8. Export options

Figure 16.9. Export HTML version 3.2

Figure 16.10. Export HTML version 4

Figure 16.11. Export HTML Version 4

Figure 16.12. Accessing a report on a web server

Figure 16.13. New items added to the Report menu

Figure 16.14. Compile Report dialog

Figure 16.15. Report Distribution Expert - Options

Figure 16.16. Report Distribution Expert - File List

Figure 16.17. Report Distribution Expert - Third Party DLLs

Figure 16.18. Report Distribution Expert - Distribution

Figure 16.19. End-user report viewer

Figure 16.20. Changing the selection criteria

Figure 16.21. Report viewer

Figure 16.22. Report viewer Window Style Options screen

Chapter 17: The Crystal Programming Forest

Figure 17.1. A Windows application displaying a Crystal report

Figure 17.2. A web application displaying a Crystal report

Figure 17.3. A web service

Chapter 18: The Object-Oriented Primer

Figure 18.1. Compiling the program

Figure 18.2. Reusing program code

Figure 18.3. Compiling the program

Figure 18.4. Using a DLL

Figure 18.5. Compiling the program

Figure 18.6. Loan object model

Figure 18.7. The Crystal Report object model

Figure 18.8. Methods, properties, and events in a Crystal object

Chapter 19: Building Windows Applications with the Report Designer Component

Figure 19.1. Installing Crystal developer components

Figure 19.2. Enabling the Crystal Report Designer

Figure 19.3. The RDC components

Figure 19.4. Crystal Reports ActiveX Designer Runtime Library

Figure 19.5. Crystal Reports ActiveX Designer Design and Runtime Library

Figure 19.6. Crystal Report Gallery with VB

Figure 19.7. Project data using the Report Expert

Figure 19.8. Crystal Report Expert

Figure 19.9. Visual Basic design environment

Figure 19.10. RDC Object model hierarchy diagram

Figure 19.11. Methods for the Application object

Figure 19.12. Field object properties

Figure 19.13. Report section events

Figure 19.14. Resort VB application

Figure 19.15. Methods, properties, and events of the Crystal Report Viewer with VB’s Object Browser

Figure 19.16. User choosing export format and destination

Figure 19.17. Graph properties

Figure 19.18. Connection information to SQL Server

Figure 19.19. Report Designer Component

Figure 19.20. Previewing a report from the Report Designer

Figure 19.21. The customized Crystal Report Viewer

Figure 19.22. Report Viewer events

Figure 19.23. Package and Deployment Wizard

Figure 19.24. RDC Automation Server DLL

Chapter 20: ASP Web Applications

Figure 20.1. Report Viewer

Figure 20.2. Filtered report

Figure 20.3. Report comment

Figure 20.4. Form with four input elements

Figure 20.5. The Java viewer

Figure 20.6. The HTML viewer

Chapter 21: .NET Applications

Figure 21.1. Solution Explorer

Figure 21.2. Choose A ReportDocument dialog

Figure 21.3. Property Inspector

Figure 21.4. Report Viewer

Figure 21.5. Creating a Setup and Deployment project

Figure 21.6. Adding merge modules

Figure 21.7. Setup project output

Figure 21.8. Adding a Crystal Viewer to a web page

Figure 21.9. Binding a report to the viewer

Figure 21.10. Viewing the report data

Chapter 22: XML Web Services

Figure 22.1. Creating a web service project in Visual Studio .NET

Figure 22.2. Creating the AddMe web method

Figure 22.3. Web service client interface

Figure 22.4. Adding a web reference

Figure 22.5. Web service architecture

Figure 22.6. WSDL document

Figure 22.7. SOAP request and response using Visual Studio’s test client

Figure 22.8. SOAP response from the AddMe web

Figure 22.9. Publishing a Crystal report as a web service

Figure 22.10. Crystal web service methods available to the Crystal Viewer

Figure 22.11. Displaying the web service report

Chapter 23: Crystal Enterprise

Figure 23.1. Crystal Enterprise architecture

Figure 23.2. ePortfolio main page

Figure 23.3. Report Options menu

Figure 23.4. Viewing a report from Crystal Enterprise

Figure 23.5. Crystal Management Console main menu

Figure 23.6. Crystal Enterprise Configuration Manager

Figure 23.7. Crystal Import Wizard

Figure 23.8. Adding a Crystal report to a Crystal Enterprise folder

Figure 23.9. Crystal Offline Viewer

Figure 23.10. Installing Crystal Enterprise

Figure 23.11. Crystal Enterprise Launchpad

Figure 23.12. Installing a web connector only

Figure 23.13. Copying the APS database

Figure 23.14. Adding Crystal Enterprise services to another server

Figure 23.15. Three computers dedicated to Crystal Enterprise and separate database and web servers

Figure 23.16. Hardware and software redundancy

Figure 23.17. Hardware and software redundancy and fault tolerance

Figure 23.18. Downloading a Crystal Report

Chapter 24: Report Application Server

Figure 24.1. Crystal Enterprise Report Application Server architecture

Figure 24.2. Selecting the Report Application Server installation type

Figure 24.3. Report Application Server Configuration Manager

Figure 24.4. Report Application Server Launchpad

Figure 24.5. ePortfolio Lite

Figure 24.6. ePortfolio Lite Preferences

Figure 24.7. ePortfolio Lite without the disabled features

Figure 24.8. Product Catalog report

Figure 24.9. Product Catalog Report using the HTML Page Viewer

Figure 24.10. Product Catalog Report using the Report Parts Viewer

Figure 24.11. Product Catalog Report using the Report Parts Viewer Detail view

Figure 24.12. Interactive Report Viewer toolbar

Figure 24.13. Export The Report window

Figure 24.14. Print The Report window

Figure 24.15. Advanced Search Wizard

Figure 24.16. Report Page Viewer toolbar

Figure 24.17. New Project dialog

Figure 24.18. Add Item dialog

Figure 24.19. Project Explorer with ePortfolio Lite files imported

Appendix B: Developer Reference Documentation

Figure B.1. Crystal Reports 9 documentation