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
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
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
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
Enter the name you want to identify your parameter by.
Enter the text you want to appear when the program prompts you.
Enter the data type of the parameter field.
Enter the value you want the program to use if you do not supply a new value.
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
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.