Crystal Reports  

Defining Virtual Tables Based on a Command/SQL Query

If the database you are using supports a query language such as SQL, you can write your own command which will be represented in Crystal Reports as a Table object. This allows experienced database users complete control of the data processing that gets pushed down to the database server. An experienced user can write a highly optimized command that can considerably reduce the size of the set of data returned from the server.

You can write your own command by using the Add Command node in the Database Expert to create a virtual table which will represent the results of processing the command.

When you use a virtual table that was created as a Command, Crystal Reports does not alter the syntax of the SQL submitted to the server (that is, it does not automatically add quoting or escape characters). This behavior also applies to parameters used in Commands. Therefore, your must add the quoting and escape characters that are necessary for your database driver.

To create your own command object

  1. Right-click Database Fields in the Field Explorer and click Database Expert.
  2. In the Database Expert dialog box, browse the folders to locate your data source.
  3. Under your data source, double-click the Add Command node.
  4. In the Add Command to Report dialog box, enter an appropriate query/command for the data source you have opened.

    For example:

    SELECT
        Customer.`Customer ID`,
        Customer.`Customer Name`,
        Customer.`Last Year's Sales`,
        Customer.`Region`,
        Customer.`Country`,
        Orders.`Order Amount`,
        Orders.`Customer ID`,
        Orders.`Order Date`
    FROM
        Customer Customer INNER JOIN Orders Orders ON
            Customer.`Customer ID` = Orders.`Customer ID`
    WHERE
        (Customer.`Country` = 'USA' OR
        Customer.`Country` = 'Canada') AND
        Customer.`Last Year's Sales` < 10000.
    ORDER BY
        Customer.`Country` ASC,
        Customer.`Region` ASC
  5. Click OK.

    You return to the Crystal Reports ActiveX Designer. In the Field Explorer, under Database Fields, a Command table appears listing the database fields you specified.

    Note   By default, your command is called Command. You can change its alias by pressing F2.

To edit a command object

  1. In the Selected Tables area of the Database Expert, select the command you want to edit.
  2. Right-click the command and select Edit Command from the shortcut menu.
  3. Make the changes you want in the Modify Command dialog box and click OK when you're done.

You can create a parameter field while working in the Add Command to Report dialog box or the Modify Command dialog box.

To create a parameter for a command object

  1. In the Parameter List area of the Add Command to Report dialog box or the Modify Command dialog box, click Create.
  2. In the Command Parameter dialog box, enter the following information in the fields provided:
    • Parameter Name

      Enter the name you want to identify your parameter by.

    • Prompting Text

      Enter the text you want to appear when the program prompts you.

    • Value Type

      Enter the data type of the parameter field.

    • Default Value

      Enter the value you want the program to use if you do not supply a new value.

  3. Click OK.

    Your parameter is added to the Parameter List. You can modify or delete it by returning to the Modify Command dialog box.

To add a parameter to a command object

  1. Place the cursor in the query text.
  2. Double-click the parameter's name in the Parameter List.

    The parameter is added where your cursor is sitting. It should look like this:

    WHERE
        Customer.`Country` = '{?CountryParam}'

    If you want to change the parameter field you have created, go to the Field Explorer and select it from the Parameter Fields node.

See Also

Inserting Database Fields