- •Welcome to Seagate Crystal Reports
- •Welcome
- •Two kinds of Hands-On tutorials
- •Command, button, key, and control conventions
- •Using Seagate Crystal Reports documentation
- •Seagate Crystal Reports online Help features
- •If you need more help...
- •Installation Requirements
- •Installing Seagate Crystal Reports
- •Installing on a network workstation
- •Upgrading from a previous version
- •Quick Start
- •Subreports expand report usefulness
- •Query Designer adds ad-hoc querying capabilities
- •Parameter fields mean multi-purpose reports
- •Text objects give you text with intelligence
- •Preprinted-form reports easier than ever
- •More powerful formulas extend your capabilities
- •Web solution serves up variety of online reports
- •HTML exporting simplifies Web activities
- •New database support improves data access
- •Running totals made easy
- •Smart Navigation
- •Learning Seagate Crystal Reports
- •User’s Guide
- •Online Help
- •Books Online
- •Sample Reports
- •Glossary
- •Sample Data - CRAZE.MDB
- •Suggested learning paths
- •The application window
- •Menu bar
- •Standard toolbar
- •Supplementary toolbar
- •Format bar
- •Status bar
- •Shortcut menus
- •Cursors
- •Design Tab
- •Preview Tab
- •Other fundamentals
- •HANDS-ON (Report Design Environment)
- •How to add, delete, and move guidelines
- •How to move and position objects using guidelines
- •How to turn the grid on/off
- •How to zoom your report in and out
- •How to undo/redo activities
- •How to drill down on summarized data
- •HANDS-ON (Sections and Areas)
- •How to add, delete, move, and merge sections
- •How to split and resize sections
- •Basic report design
- •How to design a prototype
- •Concepts in reporting
- •Beyond basic reports
- •HANDS-ON (Report Creation and Design)
- •How to select data and begin creating a report
- •How to add and link multiple tables
- •How to insert database fields
- •How to insert special fields
- •How to insert a page n of N field
- •How to insert text objects
- •How to use a database field in a text object
- •How to insert a picture
- •How to select, move, and resize objects
- •How to hide parts of the report
- •HANDS-ON (Finishing Your Report)
- •How to insert page headers and footers
- •How to add a title page to your report
- •How to add summary information to your report
- •Printing considerations
- •Design solutions for printing/distributing
- •Report creation checklist for distributed reports
- •Updating printer drivers
- •Report distribution
- •HANDS-ON (Distributing Your Report)
- •How to export reports
- •How to fax a report
- •How to request reports from a web browser
- •How to specify parameter field values
- •How to log on to a database
- •How to view plain HTML reports
- •Overview
- •Getting started
- •Record Selection
- •Grouping and sorting
- •Completing the report
- •Introduction
- •Working with Arbor Essbase data
- •HANDS-ON (Reporting on OLAP data)
- •How to create a cross-tab with Essbase data
- •Using multiple sections in reports
- •HANDS-ON (Multiple Section Reports)
- •How to work with text objects
- •How to create a form letter using a text object
- •How to format objects conditionally
- •How to print conditional messages in form letters
- •How to alternate background colors for rows
- •How to eliminate blank lines
- •How to add blank lines conditionally
- •Formatting concepts
- •Absolute formatting
- •Types of formatting properties
- •Conditional formatting
- •HANDS-ON (Absolute Formatting)
- •How to add color, shading, and borders
- •How to add/edit lines and boxes
- •How to change margins
- •How to add/delete white space between rows
- •How to set page orientation and paper size
- •HANDS-ON (Conditional Formatting)
- •How to flag values that meet certain conditions
- •Record selection
- •Group selection
- •Record selection formula templates
- •HANDS-ON (Record and Group Selection)
- •How to create a record or group selection formula
- •How to use record/group selection templates
- •How to select the top or bottom N groups
- •Sorting, Grouping, and Totalling Overview
- •Creating custom groups
- •HANDS-ON (Sorting, Grouping, and Totalling)
- •How to do a single field sort
- •How to do a multiple field sort
- •How to group data
- •How to sort records within groups
- •How to summarize grouped data
- •How to subtotal grouped data
- •How to sort based on summarized group values
- •How to create multiple levels of subtotals
- •How to group data in intervals
- •How to calculate a percentage of the grand total
- •How to create group headers
- •What are formulas?
- •Other formula conventions
- •Formula syntax
- •How formulas are evaluated - Order of precedence
- •HANDS-ON (Formulas 101)
- •How to insert a formula in your report
- •How to delete formulas from your report
- •How to copy formulas from online Help
- •How to copy formulas from one report to another
- •How to create if-then-else formulas
- •How to format text with formulas
- •How to use variables in formulas
- •How to declare a variable
- •How to assign a value to a variable
- •How to conditionally assign values to variables
- •How to use an array in a formula
- •How to use a range in a formula
- •How to use semicolons in formulas
- •How to fine tune group selection formulas
- •How to fine tune record selection formulas
- •How to debug a formula
- •Introduction
- •HANDS-ON (Advanced Totalling)
- •How to maintain running totals in a list
- •How to subtotal running totals within groups
- •How to subtotal without grouping
- •How to subtotal true A to B, A to C reports
- •Parameter field objects overview
- •Multiple parameter fields
- •Parameter field considerations
- •HANDS-ON (Parameter Field Objects)
- •How to create a parameter field
- •How to use a parameter field in a formula
- •How to respond to parameter field prompts
- •How to use wildcards with parameter fields
- •How to set a report title using parameter fields
- •How to set sort order using parameter fields
- •Graphing Overview
- •Choosing a graph or chart type
- •Where to place your graph
- •Data you can graph on
- •Before you create your graph
- •HANDS-ON (Graphing)
- •How to graph on a summary or subtotal field
- •How to graph on a details field
- •How to graph on a formula field
- •How to graph on cross-tab summaries
- •How to edit graphs using PGEditor
- •How to use the underlay feature with graphs
- •OLE Objects Overview
- •Inserting OLE objects in your reports
- •Linked vs. Embedded Objects
- •The dynamic OLE menu commands
- •OLE and the Picture command
- •General OLE considerations
- •HANDS-ON (OLE Objects)
- •How OLE objects are represented in your report
- •How to use OLE - General Overview Tutorial
- •How to insert a graphic/picture as an OLE object
- •What are subreports?
- •Unlinked vs. linked subreports
- •How subreport linking works
- •HANDS-ON (Subreports)
- •How to insert a subreport
- •How to preview your subreport
- •How to combine unrelated reports using subreports
- •How to use subreports with unlinkable data
- •Cross-tab overview
- •Cross-tab components
- •HANDS-ON (Cross-Tab Objects)
- •How to create a cross-tab object
- •How to format a cross-tab
- •How to print cross-tabs that span multiple pages
- •The Crystal Query Designer
- •HANDS-ON (Queries)
- •How to create a new query
- •How to add tables to a query
- •How to link tables and specify a join type
- •How to add fields to a query
- •How to identify unique values in a query
- •How to summarize data with aggregate functions
- •How to sort records according to field values
- •How to specify records to be included in a query
- •How to select groups to be included in a query
- •How to create an SQL expression
- •How to create a query from another Crystal Query
- •How to select a query for a report
- •How to use a parameter field in a query
- •Dictionaries Overview
- •HANDS-ON (Dictionaries)
- •How to create a new dictionary
- •How to add a data file
- •How to open an SQL or ODBC data source
- •How to link multiple tables
- •How to select tables and fields for users
- •How to add/create formulas
- •How to move fields/field headings within the list
- •How to update the location of a database table
- •How to add a new field heading
- •How to add Help text
- •How to add a graphic
- •How to create sample data for users to browse
- •How to edit an existing dictionary
- •How to convert a 3.x or 4.x dictionary file
- •How to select a dictionary for a report
- •Databases Overview
- •For additional information
- •HANDS-ON (Working With Databases)
- •How to open Access queries through DAO
- •How to open Access queries through ODBC
- •How to open Access parameter queries
- •How to set up an ODBC data source
- •How to check settings for an ODBC data source
- •How to log on to an ODBC data source
- •How to add an ODBC database table to a report
- •How to log on to MS SQL Server via ODBC
- •How to log off an ODBC data source
- •How to set up an A to B, A to C link
- •How to edit an SQL query
- •How to use an ACT! database
- •How to open the NT Event Log
- •Introduction
- •Four types of data
- •Direct access database files
- •ODBC data sources
- •Crystal Query Designer files
- •Crystal Dictionary files
- •Multi-pass reporting
- •Product support
- •Web support
- •E-mail support
- •Fax support
- •Telephone support
- •Extended technical support policy
- •Product registration
- •Product return policy
- •Product replacement policy
- •Glossary
Crystal Query Designer files
A query is simply a request for a specific set of data from a database. Once the data is gathered, it can be stored as a Crystal Query Designer file (.QRY). The Crystal Query Designer file can then be used much like a database table. The data it gathers from your SQL databases becomes a new data set that can be added to reports.
A Crystal Query Designer file gathers data from ODBC data sources, such as SQL servers, by means of SQL (Structured Query Language). An SQL statement with instructions to gather and return a specific set of data is sent directly to the SQL DBMS. The DBMS handles all of the actual data gathering, sorting, and grouping according to the instructions in the SQL statement. Only the final set of data is returned to the Query Designer and stored as a query file.
NOTE: If you are not familiar with SQL, see SQL join types (ODBC data sources), Page 537.
Query file collects data and stores as new data set.
Crystal Reports |
|
|
Crystal Reports |
|
uses data stored |
Crystal |
in query file. |
|
|
query |
|
file |
|
Query |
|
data |
|
set |
|
ODBC
Data Source
(SQL Server)
First, you refine an SQL statement and create a query file that contains only the data you need. Then, you design your reports based on the query file instead of the original databases. Most of the data gathering process is done on the SQL server when you design the query. When you design your report, Seagate Crystal Reports needs only work with a small set of data, saving you time and trouble. See How to create a new query, Page 472.
Data Sources |
623 |
Advantages |
While there are many reasons for using queries, the primary |
|
reason is the ability to off-load most of the data retrieval process |
|
to a server and store the resulting data as a separate data set. |
|
Using this SQL pass-through technique, where data retrieval tasks |
|
are passed through to the server, you retrieve your data faster and |
|
more efficiently. Anytime you are working with SQL data, you |
|
should consider building a query file before designing your |
|
report. |
|
In addition, the Query Expert provides for more control over your |
|
data with the SQL language. Seagate Crystal Reports provides |
|
some SQL language features when accessing SQL or ODBC data, |
|
but the Query Expert is a powerful query tool that provides more |
|
powerful SQL data access features. The Query Expert allows |
|
aggregate functions in an SQL statement and supports all data |
|
query elements of the SQL language. If you know the SQL |
|
language, you can perform complex SQL tasks using UNION |
|
operations and sub-queries. See The SQL language, Page 551. |
Disadvantages |
Since a query represents a complete data set, any records, fields, |
|
or tables that were not included when the query was created are |
|
not available when using the query to build a report. A report can |
|
not be any more complex than the data it accesses, so by using a |
|
query, you limit your reporting options to the data in the query. |
|
In addition, a query can not be used with any other source of data. |
|
If you decide to use a query in your report, you can not select an |
|
additional source of data, such as a database table or another |
|
query file, to use in the report. |
Crystal Dictionary files
A dictionary (.DC5) file is a structured and simplified view of organizational data that you can create for some or all of the individuals in your organization that are using Seagate Crystal Reports. With a dictionary, end users only see the subset of tables and fields they need.
NOTE: The application for creating dictionaries is only available with the Professional edition of Seagate Crystal Reports. This application is designed for IS and Database Managers who need to
624 |
Seagate Crystal Reports User’s Guide |
provide alternatives to accessing data for users. Both the Standard and Professional editions of Seagate Crystal Reports can open and use dictionary files.
A dictionary is an optional source of data for Seagate Crystal Reports. It provides a convenient filter, clarifying and simplifying complex data access techniques for end users, but data can still be accessed directly from the database by the user. Dictionaries simply provide all the convenience without enforcing restrictions.
If you choose to use a dictionary to design a report, however, you can only use that dictionary as a source of data for that report. In other words, you can not open both a dictionary and an ODBC data source, a direct access database, or any other source of data from within the same report. This includes other dictionaries. The purpose of a dictionary is to provide fast, easy to understand access to a particular set of data. Connecting to other sources of data within the same report would defeat this purpose. See How to create a new dictionary, Page 493.
Dictionaries let you:
●design a single, dynamic view of all the data that is necessary to create organizational reports and queries,
●create a dictionary that contains multiple data sources, tables, and links,
●organize the data and rename tables and fields to make it easier for users to understand the content and purpose of the data, and
●create complex data-manipulation formulas that users can access without the need to understand formula concepts.
Dictionaries reduce support cost and time, increase user productivity, and reduce data misuse, loss, and damage.
Once created, the dictionary acts as a filter, providing a view of complex data that is clear and easy for any user to understand. Complicated data will not stop end users from creating powerful reports.
Data Sources |
625 |
Crystal Reports
PDIXBSE.DLL (16 bit) or
P2IXBSE (32 bit)
Dictionary
Database Access Layer
Seagate Crystal Reports reads the dictionary file using the PDIXBSE.DLL file (P2IXBSE.DLL for 32-bit). This translation file is based on the xBase engine used to access dBASE, FoxPro, Clipper, Page 588, databases, but provides all the flexibility needed to read dictionaries.
NOTE: If you upgraded from an earlier version of Seagate Crystal Reports that allowed dictionary files, the older dictionary files (.DCT) were based on the Btrieve engine. If you convert those older files to new dictionary files, the new files will use the xBase translation layer instead of Btrieve. However, the Btrieve engine installed by the earlier version of Seagate Crystal Reports must remain on your system for the new dictionary application to read the older dictionary files.
The Database Access Layer is the layer through which the dictionary file actually reads the original database data. Dictionaries must read database data through the same routes that Seagate Crystal Reports reads the data. The section on direct access database layers and ODBC data sources that are described throughout this chapter illustrate the files needed for dictionaries to open data.
The benefit of dictionaries is that this Database Access Layer is completely transparent to the user. The dictionary displays a view of data in Seagate Crystal Reports that may or may not match the
626 |
Seagate Crystal Reports User’s Guide |
actual data, but displays a view that is easily accessible and usable by users.
Finally, dictionaries provide an easy means of changing the original data accessed without changing the view of data seen by users. For example, your original database may contain Price, Quantity, and Cost fields, but you decide that Cost can be calculated from Price and Quantity, so you eliminate the Cost field. By simply updating the dictionary to calculate Cost instead of getting it directly from the database, your users never know the difference. This also helps when the entire underlying database format is changed. Once again, just update the dictionary.
Dictionary files are often created by an IS manager who controls a company database. The IS manager can work with data that is organized and classified to create simple, easy dictionaries for company employees. The employees will find only the data they need in each dictionary file, because the original data has been manipulated or customized by the IS manager for varying usage.
Data Sources |
627 |
628 |
Seagate Crystal Reports User’s Guide |
A
Report Processing Model
What you will find in this appendix...
Multi-pass reporting, Page 630
What is a “pass”?, Page 630
Pre-pass #1, Page 630
Pass #1, Page 630
Top/bottom N and group sorting, Page 631 Pass #2, Page 631
Report Processing Model |
629 |