Crystal Queries

Chapter 10 - Data Sources and the Database Expert
Mastering Crystal Reports 9
by Cate McCoy and Gord Maric
Sybex 2003

The Crystal SQL Designer is a separate, and free, product that was installed with earlier versions of Crystal Reports. Its purpose is to allow you to build a SQL query, preview the data in rough form, and then save the query for use as a data source. In Crystal Reports 9, you can still use the Crystal SQL Designer, but it is not shipped with the product. You can download it as part of a set of Crystal Reports 9 Data Compatibility tools that includes the Crystal SQL Designer and a second tool we’ll talk about momentarily, the Crystal Reports Dictionary tool. To download these free tools, go to the support.crystaldecisions.com site, choose the option to download updates and samples, and search for the file named cr9_data_tools.zip.

To activate the Crystal SQL Designer, choose Start > Crystal Reports Tools > Crystal SQL Designer. The Create SQL Expert uses a tabbed query builder interface, shown in Figure 10.19, and takes a tab-by-tab approach to building a SQL query.

Figure 10.19. Crystal SQL Designer

The first step is to connect to the data on the Tables tab, which is done the same way you’ve seen with the Database Expert, but with a slightly different dialog window. Next, choose the fields to include in the query.

Note 

If multiple tables are involved in the query being built, a tab labeled Links appears directly to the right of the Tables tab. On the Links tab, you can add links manually or click the Smart Linking button to link based on primary and foreign keys, which were discussed in Chapter 9, “Working with Multiple Tables.”

Once you’ve selected the data on the Fields tab and chosen a sort order on the Sort tab, then you can click the SQL tab to display the resulting SQL query based on your criteria. An example is shown in Figure 10.20. You can modify the query using the SQL tab at this point by applying any SQL statements valid in a SELECT clause. In addition, you can go directly to the SQL tab and type a valid SQL statement without using the tabbed interface at all.

Figure 10.20. SQL query

When you click the Finish button, you have the option of processing the query immediately or saving it to a file. Running the query displays the results of the field selection along with its data and any other SQL statements. The behavior is reminiscent of working with a database interactively, running a query, and seeing the tabular results immediately. An example of this is shown in Figure 10.21. This is a great tool to use if you want to have a quick look at the data in its raw format.

Figure 10.21. Query results

Choosing File > Save As provides a way for you to save the query in a file that can then be used as a Crystal Reports data source, as shown in Figure 10.22. The file automatically gets a .qry extension, and you can save it to any location on your computer or attached network drive. When you choose this option as the data source, each time the report opens, the query will be run anew and data generated for use in the report.

Figure 10.22. Saving a .qry file

The File menu also contains the Save Data With Query option. This option prevents Crystal from running the query when a report is opened and instead uses the data that was generated with the query when it was created and saved. This means that the query itself is not rerun when the report opens. An example of how a Crystal query will be displayed in the Database Expert is shown in Figure 10.23.

Figure 10.23. Crystal queries in the Database Export

Use of content on this site is expressly subject to the restrictions set forth in the Membership Agreement

Conello © 2000-2003     Feedback