HANDS-ON (Queries)
How to use an SQL query that was designed elsewhere
1Copy the SQL statement to the Windows Clipboard. Many SQL editors allow the SQL statement to be copied to the Clipboard using the Ctrl-C key combination.
2Click the NEW button on the toolbar in the Crystal SQL Designer. The New Query dialog box appears.
3 Click the Enter SQL statement directly button.
The Log On Server dialog box appears.
4 Select the desired SQL server for your SQL statement and click OK.
The SQL Server Login dialog box appears.
468 |
Seagate Crystal Reports User’s Guide |
5Enter your user ID and password to log on to the SQL server, and click OK.
After a message appears indicating the success of your log on (assuming you logged on correctly) the Enter SQL Statement dialog box appears.
6 Select an ODBC data source from this drop-down list.
If you are not already logged on to an SQL Server, click this button and use the Log On Server dialog box to log on.
7To log on to another server or ODBC data source, click the Log On Server button. All data sources that you are logged on to will appear in the Data Source drop-down list.
8In the Title text box, type a descriptive title for the query.
The Crystal SQL Designer |
469 |
9 Edit the SQL statement in this box.
10 Click this button to paste the SQL statement into the SQL edit box.
The SQL edit box allows you to make any changes you wish to the SQL statement. You can even enter an entirely new SQL statement if you wish.
11Make any changes you need and click the OK button when you are finished.
The old SQL statement becomes a new SQL query that can be used with
Seagate Crystal Reports just like any other SQL query file.
Alternatively, you can import an SQL statement saved in an ASCII text file. To do so, disregard Step 1 above, follow Steps 2 through 6 to open the Enter SQL Statement dialog box and log on to the ODBC data source, then click Import to import the SQL statement from the text file.
How to create a new query
1Click the NEW button in the Crystal SQL Designer. The New Query dialog box appears.
470 |
Seagate Crystal Reports User’s Guide |
2 Click the Use Crystal Query Expert button.
The Create Query Expert consists of several tabs. The tabs are numbered to lead you step-by-step through the query creation process.
NOTE: The Links Tab only appears when two or more database tables are selected on the Tables Tab.
NOTE: Once you select at least one field for the query on the Fields Tab, you can view the data retrieved by your query at any time, by clicking the Preview Query button at the bottom of the dialog box. After you are done viewing the query data, use the Edit button to return to the Query Expert.
How to add tables to a query
FROM clause
The FROM clause specifies the sources (tables) of the database fields indicated in the SELECT statement.
1 Click the Tables Tab in the Create Query Expert.
The Crystal SQL Designer |
471 |
2Click the SQL/ODBC or Dictionary button.
●If you click SQL/ODBC:
¾Select an SQL or ODBC data source in the Log On Server dialog box.
¾Choose one or more database tables in the Choose SQL Table dialog box. Click Add to add each table to your SQL query. Click Done when finished.
●If you click Dictionary:
¾Select Dictionary (.DC5) in the File Open dialog box, and click
OK.
¾All ODBC database tables accessible from the dictionary will be added to your SQL query.
NOTE: Dictionary files must be based on an ODBC data source. See
Dictionaries, Page 487, for further information.
How to link tables and specify a join type
WHERE clause
The WHERE clause specifies how two database tables are linked.
NOTE: Some links can be generated in the FROM clause.
472 |
Seagate Crystal Reports User’s Guide |
1Click the Links Tab and click the Smart Linking button. The Create Query Expert will make any links it can detect between tables. These links are represented by an arrow between the two fields. This arrow is called a link line.
Link line.
2Click one of the link lines. It becomes highlighted, along with the fields on either end of the line.
3Click the Options button to open the Link Options dialog box. This dialog box describes the link between the tables in greater detail. Search for Link Options dialog box in Crystal SQL Designer online Help.
The Crystal SQL Designer |
473 |
4 Select an SQL join type.
Related Topics
SQL join types (ODBC data sources), Page 527
WHERE, Page 541
How to add fields to a query
SELECT clause
The SELECT clause selects specific data items to retrieve from the database tables specified by the FROM clause.
1 Click the Fields Tab in the Create Query Expert.
474 |
Seagate Crystal Reports User’s Guide |
2 Select the fields you want to appear in your query from this list.
3 Click the Add button...
...the field(s) are added to this list.
4 Click Remove to remove any fields from the Query Fields list box.
Related Topics
SELECT, Page 540
How to identify unique values in a query
DISTINCT clause
DISTINCT forces the query to retrieve only unique (distinct) sets of data.
1 Click the Fields Tab of the Create Query Expert.
The Crystal SQL Designer |
475 |
2 Toggle the Select Distinct Values check box on to activate it.
Related Topics
DISTINCT, Page 540
How to summarize data with aggregate functions
GROUP BY clause
The GROUP BY clause retrieves a set of summary data.
Aggregate functions
Use aggregate functions to obtain summary information on all records or on specific groups of records. Aggregate functions are most useful when you do not need the detailed information and you only want to examine totals.
For example, you might need the total number of orders made and the average amount of each order. For this type of query, you apply the COUNT function to the Order ID field, and the AVG (average) function to the Amount field. The query calculates the summary information and provides the results you need.
1 Click the Fields Tab of the Create Query Expert.
476 |
Seagate Crystal Reports User’s Guide |
2 Highlight the field on this list you want to summarize.
3From the Total drop-down list, choose an aggregate function to apply to the selected field.
●COUNT( ) counts the number of values within a group.
●SUM( ) adds the values within a group, to provide a total.
●AVG( ) finds the average of all values within a group.
●MIN( ) finds the minimum value within a group.
●MAX( ) finds the maximum value within a group.
The query summarizes the field to which the aggregate function is applied. Any other fields that appear in the Query Fields list box are used to sort the data.
Related Topics
GROUP BY, Page 542
How to sort records according to field values
ORDER BY clause
The ORDER BY clause specifies that the database records retrieved be sorted according to the values in a specific field.
The Crystal SQL Designer |
477 |
You can group either by sorting data, so that records with like data appear grouped in the sorted list, or by summarizing data with aggregate functions, so that summary data appears in your query for each group of records. For information on using aggregate functions in your query, see
How to summarize data with aggregate functions, Page 476. This section shows you how to group data by sorting.
1 Click the Sort Tab in the Create Query Expert.
2 Select the field you |
|
|
|
...the field is added |
want sorted from this list. |
3 Click Add... |
|
to this list. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Use these arrows to move the field up/down the list.
4 Select the sort order from this drop-down list.
Related Topics
ORDER BY, Page 541
How to specify records to be included in a query
WHERE clause
The WHERE clause can specify record selection criteria.
1 Click the Select Tab of the Create Query Expert.
478 |
Seagate Crystal Reports User’s Guide |
2 Select the field that |
|
|
contains the data you |
|
|
want to base selection |
|
|
criteria on from this list. |
3 Click Add... |
...the field is added to this list. |
4 Use these controls to specify the selection criteria to be applied to the field highlighted in the Select Fields dialog box.
This tab works much like the Select Expert in Seagate Crystal Reports.
Search for Select Expert in Crystal SQL Designer online Help.
5 Repeat Steps 2 and 3 for every field in the Select Fields list box.
Related Topics
WHERE, Page 541
How to select groups to be included in a query
GROUP BY and HAVING clauses
The HAVING clause creates selection criteria for the summary information produced by the GROUP BY clause.
1 Click the Fields Tab of the Create Query Expert.
The Crystal SQL Designer |
479 |
2 Select the field you want to summarize from this list.
3 Select the aggregate function from this drop-down list you want applied to the highlighted field.
4 Click the Select Tab.
480 |
Seagate Crystal Reports User’s Guide |
5 Select the aggregate |
|
function from this list box. |
6 Click Add... |
...the field is added to this list.
7 Use these drop-down lists to specify which group summary values based on the aggregate function should appear in the result.
Related Topics
GROUP BY, Page 542
How to create an SQL expression
NOTE: You must have some familiarity with the SQL language and SQL expressions before trying to add an expression to your query file.
1 Click the Fields Tab in the Create Query Expert.
The Crystal SQL Designer |
481 |
2 Click Expression.
The SQL Expression dialog box appears.
3 Type a name for the new expression and click OK.
4 Double-click a field in this list to add it to the expression.
5 Edit the expression in this edit box.
482 |
Seagate Crystal Reports User’s Guide |
6When you have finished designing the SQL expression, click OK to return to the Fields Tab.
7 Highlight the new SQL expression. It will be indicated by a @ sign.
8 Click Add...
...the field is added to this list.
How to create a query from another Crystal Query
1Click the NEW button in the Crystal SQL Designer. The New Query dialog box appears.
2 Click Start from existing Crystal Query.
The Crystal SQL Designer |
483 |
3Use the File Open dialog box to highlight the query (*.QRY) file that you want used to base a new query on, and click OK. The program runs the existing SQL query and displays the data set.
4Click the EDIT button.
The Create Query Expert appears with the specifications for the selected query already in place.
5Use the Create Query Expert to make any changes necessary to the query. Click Preview Query when finished to view the new query results.
6Choose SAVE AS from the File Menu to save the new query under a different name. The new query is saved in a separate file; your source query remains unchanged.
How to select a query for a report
1In Seagate Crystal Reports, click the NEW button on the standard toolbar.
The Report Gallery appears.
2Click a Report Expert from the Report Gallery. The Create Report Expert appears.
3On the Tables Tab, click Query. The File Open dialog box appears.
484 |
Seagate Crystal Reports User’s Guide |
4Use the controls in this dialog box to locate and highlight the query (.QRY) file you want to use to create a new report.
5Click OK. The query file for your report is opened. Use the tools in the Create Report Expert to design a new report. Your query fields will appear in each section of the Expert just as any other database fields would. In the list boxes, the name of the query file will appear above the fields, where you would normally see the name of a database table.
NOTE: More than one query file can not be used in a report.
The Crystal SQL Designer |
485 |
24 Dictionaries
What you will find in this chapter...
Dictionaries Overview, Page 488
Why use a dictionary?, Page 489
HANDS-ON (Dictionaries), Page 489
A dictionary is a structured and simplified view of data that you can create for some or all of the individuals in your organization that are using Seagate Crystal Reports.
Unlike some systems that force users to access data through a data distribution metalayer, dictionaries are optional components. Data can still be accessed directly by the user. Dictionaries simply provide all of the convenience without the restrictions.
Dictionaries allow you to:
●design a single, dynamic view of all the data that is necessary to create organizational reports and queries,
●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.
NOTE: The Formula Editor used in Crystal Dictionaries is not as complete as the one used in Seagate Crystal Reports. Please see Crystal Dictionary online Help for a description of the Formula Editor for use in Crystal Dictionaries.
Dictionaries reduce support cost and time, increase user productivity, and reduce data misuse, loss, and damage. They are a powerful component of Seagate Crystal Reports.
When a Dictionary is used to create a report, the only data used in the report is the data accessed through the Dictionary; you can not use a Dictionary and some other data source in the same report. Because Dictionaries are often used to impose data security, it would breach that security to allow unrestricted data access in a Dictionary report.
NOTE: You can include a subreport based on a different data source in a primary report based on a Dictionary.
Finally, Dictionaries provide an easy means of changing the underlying data set without changing the view of the data seen by users. You can change field and table names in the underlying data, for example. Then you remap the Dictionary to the new field and table names without changing the aliases assigned to the data. The users create their reports using the same data interface they've been using, never knowing what has changed under the surface.
488 |
Seagate Crystal Reports User’s Guide |
