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