The Crystal Query Designer

Why use a query?

A query is simply a request for specific information from a database. If you are requesting that information from an SQL database (or from a database that you access via ODBC), your query must be written using the Structured Query Language (SQL). The SQL language is not difficult to learn, but mastering the fine points of creating and retrieving data using SQL can take quite a while. Since the Crystal Query Designer eliminates the need to understand SQL, it can get you building effective queries right away.

The Crystal Query Designer has been designed to meet the needs of individuals with little or no query background as well as the needs of experienced SQL professionals.

●If you are new to querying, you will enjoy the way the Query Designer helps you create queries. By answering a few questions on a set of sequential tabs, you give the program all the information to generate a query that fits your needs and it will do all the work for you.

●If you are an SQL professional, you will appreciate the facility that enables you to fine tune the queries that the Query Designer generates. If you are more comfortable writing your own SQL queries, you will find it easy to enter your queries directly or even paste them in from another source.

The Crystal Query Designer can be a powerful tool for many of your information gathering needs.

NOTE: The Query Designer can only access data stored in an ODBC data source. Any SQL or other type of database mentioned in this chapter must be accessed through an ODBC data source.

Query files provide a means of off-loading much of the data generating tasks normally performed by Seagate Crystal Reports on to an SQL or other database server. The set of data returned represents a subset of the actual data in the database - only the data you specifically need.

468	Seagate Crystal Reports User’s Guide

Using the

Query Designer

If you are an experienced SQL programmer, the Crystal Query Designer allows you to reuse your existing queries, quickly and easily. All of your existing work becomes convenient query files that can be used to design reports with Seagate Crystal Reports.

In addition, a Query file provides full ANSI SQL compatibility. Although Seagate Crystal Reports supports powerful SQL passthrough reporting, it does not support the full SQL language or allow you to edit every part of an SQL statement. By using the Query Designer to create data sets to base your reports on, you get all of the power of SQL, including complex joins, sorts, and aggregate functions.

NOTE: Many of the topics in this chapter refer to specific SQL clauses. If you are experienced with SQL, use this information to better understand how the Query Expert generates an SQL statement. If you are new to SQL, you can ignore this information and continue to use the Query Expert.

The Query Designer has two primary uses:

1.Designing and developing data sets for building reports in Seagate Crystal Reports, and

2.Retrieving and analyzing current information on an “as needed” basis to facilitate informed decision making.

SEAGATE CRYSTAL REPORTS AND QUERY DATA SETS

Seagate Crystal Reports allows you to design a report based on a query data set rather than tables and fields. Since the query contains a predefined set of data, the tables and fields necessary for the report are already included.

The set of data produced by the SQL query works just like a database table when you design your report. The name of the query, along with fields it accesses, appear in the Insert Field dialog box. Aggregate functions and SQL expressions act like fields, providing data values corresponding to each record in the SQL query.

DATA ANALYSIS AND DECISION MAKING

You may not always need to produce finished reports based on your data; sometimes you just need numbers in a hurry (to prepare for a meeting, to help you make projections, etc.). The

Queries

469