To bring up this dialog, click the Advanced button in Step 2 of the Quick Report or Label Wizards or Step 3 of the Cross-Tabulation or Chart Wizards. Some of the steps in this wizard have a text box where you can enter the desired code, a Test Syntax button you can click to test whether the code is correct, and an Expression button that displays the Expression Builder to help enter field names or other expressions. In addition, you can right-click the text box and choose Zoom from the shortcut menu to display a larger editing window with complete syntax coloring and IntelliSense on Stonefield Query commands and functions.

Note that the first step is the only one available unless you are defined as an "advanced" user in the Maintain Users and Groups dialog.

Step 1 allows you to specify some advanced settings for this report. The options available in this step are:

• Add DISTINCT to SQL statement: if you filter on a field from a table that isn't involved in the report, you may end up with what appear to be duplicate records. For example, say your report displays the Company Name and Contact Name from the Customers table. If you filter on the Product Name from the Products table being "Apricot Jam" (in other words, you want a list of customers who bought that product), you'd end up with each customer showing up once for every order they placed for that product. So, if Sam's Grocery ordered it 25 times, they'd appear on the report 25 times. That isn't typically something you'd want, so Stonefield Query can eliminate these duplicate records for you.

  The DISTINCT clause in a SQL statement tells the database to only find records that have distinct values, so adding that clause to the SQL statement means that Sam's Grocery will only show up once. The Add DISTINCT to SQL statement option allows you to control whether the DISTINCT clause is used or not. No means don't add a DISTINCT clause to the SQL statement for this report and Yes means do add it. When filtering on a table not included in the fields list means only add the DISTINCT clause if you filter on a field from a table that isn't involved in the report.

  You might think that When filtering on a table not included in the fields list should always be used. However, there may be times when this isn't the correct behavior. For example, if you want to show fields from the Orders table (Order Date, Product Name, and Quantity Ordered) but filter on Company Name is "Sam's Grocery," Stonefield Query eliminates what it thinks are duplicate records, such as two orders for the same product and same quantity on the same day. Clearly, this isn't right—you'd end up with some missing data. In that case, set this option to No.

• Select only top: this option allows you to display only the top records. If you turn this option on, you can enter a number and indicate whether that number is the number of records you want or a percentage of the total records (such as the top 10 percent). Note that "top" refers to the sort order for the report. For example, if the report is sorted alphabetically by Company Name, you'll get the specified number of records in alphabetical order. If the report is sorted in descending by Order Date, you get the specified number of most recent orders.

• Use version 1.0 report engine: the report engine in Stonefield Query is more powerful than that of earlier versions, but may have a side effect for advanced layout reports created in earlier versions of Stonefield Query: some fields, especially numeric fields, may need to be sized a little larger in order to fit the data; failure to do so causes these fields to show up as a row of asterisks (*****). To prevent this from happening, the Use version 1.0 report engine setting is automatically turned on for these reports. However, once you've edited the report to increase the size of the affected fields, you can take advantage of the features of the new report engine by turning this setting off. With this setting turned on, the Run Report and Print Preview functions in the Advanced Report Designer are turned off, so that may be another reason to turn this off. Also, with this setting turned on, the Preview window appears more quickly, especially for very long reports, but the Preview window used when this is turned on has fewer features and some advanced options, such as dynamic formatting and rotation, aren't supported.
  

Use Step 2 to customize the SQL statement for the report. This step shows the SQL statement Stonefield Query sends to the database engine. You can add additional fields to the SQL statement (although they won't appear in the report unless you add them yourself in the Advanced Report Designer because they aren't automatically added to the report layout), change join clauses, or add WHERE or HAVING clauses (note that Stonefield Query automatically adds the filter specified for the report to the SELECT statement). However, don't remove any fields or change field names or you will get an error message when Stonefield Query tries to output the fields it expects to find in the data set to the report layout.

If you want to reset the SQL statement back to its default after you've changed it, click the Reset button.

Any code you enter in the Specify OnSelect Code step is executed when the report is selected in the Reports Explorer. An example is setting the default output of a report to a certain file. Say you want a particular report sent to an Excel file called SALES.XLS by default. The following code accomplishes that:

loReport.SetOutputToFile('SALES.XLS')

Note: The code in this and the remainder of the steps must be written in Visual FoxPro syntax.

The variable loReport contains a reference to the selected report object so your code can access properties and methods of this object as necessary. See the Report Objects topic for details on this object.

If you want code executed after a report has been run, such as to notify another application or to log the report run to a file, enter the desired code in the Specify AfterRun Code step. Here's an example that adds a new entry to the end of a text file, indicating when a report was run and who ran it.

lcText = 'Report ' + loReport.cReportName + ;
  ' was run on ' + ttoc(datetime()) + ' by ' + loReport.cUserName + ;
  chr(13) + chr(10)
strtofile(lcText, 'C:\REPORTLOG.TXT', .T.)

Here's an example that changes the delimiter for CSV files from a comma to a pipe character (|) and recreates the output file:

loReport.oOutput.cDelimiter = '|'
loReport.oOutput.CreateFile()

In the Specify BeforeData Code step, you can enter code you want executed before data is retrieved from the database engine. You can use this code to pre-create some data or open a different set of tables as necessary.

Sometimes, a report needs a result set that's more complex than the one created by the SQL statement generated by Stonefield Query. For example, perhaps the report needs certain sums or averages calculated. Use the Specify AfterData Code step of the Customize Report Wizard to specify code you want executed after the data has been retrieved from the database engine but before it's output to the report layout.

A variable called ResultSet contains the name of the result set retrieved for the report. You can use this to further manipulate the result set. For example, suppose you have a summary report showing customer name (which is grouped) and sales (which is summed). Because it's a summary report, it just shows the total sales for each customer, sorted by customer name. However, you'd like it sorted in descending order by total sales so the biggest sales appear first. The following code will do that (assuming the name of the field containing the sales amounts is SALES):

select * from (ResultSet) order by Sales descending into cursor NewResults

The parentheses around ResultSet are required since the name of the result set is stored in the variable called ResultSet.

Note that the result set you create must have the exact same field names the original result set had (it can contain more fields if desired) because that's the structure the rest of the reporting process expects to work with.