Crystal Queries
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.