Advanced users may need to customize how the report executes. For example, Stonefield Query automatically generates a SQL statement that's sent to the database engine to retrieve records for the report. However, you may need to use a subquery to get the exact results you need. You can change the SQL statement Stonefield Query sends to the database using the Customize Report Wizard dialog. You can also specify code to execute when someone selects the report in the Reports Explorer, when the report run has completed, just before data is retrieved from the database, just after data has been retrieved, or before the report is output. You can also change the relationship between tables, indicate whether the DISTINCT clause is added to the SQL statement, and specify whether only the top number of records is retrieved.

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, Chart, or Gauge 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 two steps are the only ones available unless you are defined as an "advanced" user in the Maintain Users and Groups dialog. Also, the second step is only available if the report includes fields from more than one table.

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.

  Also note that for a cross-tab report, "top" refers to the first row field. For example, if you use top 10 with Territory and Customer Name as the row fields, the report will show the top 10 territories, not the top 10 customers.

• Use "Other" for non-top records: if you turn this option on, records outside the top values are combined into a single "Other" value. For example, if you turn on Select only top, choose 10 for the number of records, and turn on Use "Other" for non-top records, rather than displaying only the top 10 records, the report shows the top 10 plus an Other record with all other records combined. This option isn't available for quick reports unless Summary report is turned on.

• 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. Note that this option doesn't appear for gauge or chart reports.

• Use older cross-tab engine: this option, which only appears for cross-tab reports, specifies whether or not to use the older cross-tab report engine.

Step 2 allows you to change the relationship between tables. This step is only available if the report includes fields from more than one table. Select a table from the list and then select how it's related to the first table. Note that the first table used in the report isn't listed, because the relationships start with the second table. For example, if the first field in the report is from Order Details, that table isn't included in the list.

The sample image shows an example of how the selected relationship will work. The sample is based on one table having records A, B, C and the other table having records A, C, and D. This means there's a match for records A and C but none for B or D.

The types of relationships you can specify are (in these relationships, Table2 refers to the selected table and Table1 refers to the first table in the report):

• Display only records from Table2 that have matches in Table1. For example, if Table1 has records A, B, and C and Table2 has records A, C, and D, the report will only include records A and C. In the SQL language, this is called an "inner join."

• Display records from Table1 whether there are matches in Table2 or not. For example, if Table1 has records A, B, and C and Table2 has records A, C, and D, the report will include records A, B, and C but show blanks for fields from Table2 for record B since there is no record B in Table2. In the SQL language, this is called a "left outer join" ("left" because Table1 is to the left of Table2 in the SQL statement).

• Display records from Table2 whether there are matches in Table1 or not. For example, if Table1 has records A, B, and C and Table2 has records A, C, and D, the report will include records A, C, and D but show blanks for fields from Table1 for record D since there is no record D in Table1. In the SQL language, this is called a "right outer join" ("right" because Table2 is to the right of Table1 in the SQL statement).

• Display records from both Table1 and Table2 whether there are matches between them or not. For example, if Table1 has records A, B, and C and Table2 has records A, C, and D, the report will include records A, B, C, and D but show blanks for fields from Table1 for record D since there is no record D in Table1 and blanks for fields from Table2 for record B since there is no record B in Table2. In the SQL language, this is called a "full join."

Use Step 3 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), add a subquery, or add WHERE or HAVING clauses (note that Stonefield Query automatically adds the filter specified for the report to the SQL statement). Although you can also edit the relationships between tables in this step, it's better to use Step 2 to do so. 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. Also note that if the custom SQL statement has an ORDER BY clause, be sure that the list of field names in the statement are in the same order as they appear in step 2 or the data may not be sorted correctly.

You can enter a formatted SQL statement; for example, carriage returns and tabs are permitted. This makes it easier to take a SQL statement from another application and paste it into a report's SQL statement.

As mentioned, Stonefield Query automatically adds the filter specified for the report to the SQL statement. It does this by finding the WHERE clause in the statement and ANDing the filter condition with the existing conditions. If there is no WHERE clause, one is added. While this works for many types of SQL statements, complex statements with subqueries may cause a problem. For example, consider this SQL statement:

select SomeField, 
  (select SomeOtherField from SomeOtherTable where SomeCondition) as SomeOtherField
  from SomeTable

Although the main SQL statement doesn't have a WHERE clause, the subquery does. This can confuse Stonefield Query and as a result, it may add the filter for the report to the WHERE clause of the subquery, which is incorrect. To precisely specify where the filter should be added, use a text merge expression (one surrounded with "{" and "}") with "UserFilter" as a placeholder for the filter condition. For example:

select SomeField, 
  (select SomeOtherField from SomeOtherTable where SomeCondition) as SomeOtherField
  from SomeTable {'where ' + UserFilter}

In addition to allowing you to specify where the filter condition is added, it gives you flexibility in how it's added. For example, consider this SQL statement:

select SomeField, 
  (select SomeOtherField from SomeOtherTable
    where SomeOtherTable.SomeField = A.SomeField) as SomeOtherField
  from SomeTable A

In this case, SomeTable is aliased as A. The problem is that when Stonefield Query adds the filter with a condition involving a field from SomeTable, it doesn't know the table needs to be aliased, resulting in:

select SomeField, 
  (select SomeOtherField from SomeOtherTable
    where SomeOtherTable.SomeField = A.SomeField) as SomeOtherField
  from SomeTable A where SomeTable.SomeField = SomeValue

This fails because the filter condition should be A.SomeField = SomeValue. In this case, use a text merge expression that converts any instance of the table name to the aliased name:

select SomeField, 
  (select SomeOtherField from SomeOtherTable
    where SomeOtherTable.SomeField = A.SomeField) as SomeOtherField
  from SomeTable A
  {iif(empty(UserFilter), '', 'where ' + 
    strtran(UserFilter, 'SomeTable', 'A', -1, -1, 1))}

The test for EMPTY(UserFilter) is there so no WHERE clause is added if there is no filter (for example, if all of the conditions are ask-at-runtime but the user turned on the Ignore this condition option for all of them in the ask-at-runtime dialog). The STRTRAN() function changes the specified expression (in this case, UserFilter, which contains the filter) so that "SomeTable" is converted to "A" (the two "-1" parameters indicate that all instances should be changed and the final "1" parameter means case-insensitive, so "SOMETABLE" and "sometable" are also converted).

Instead of a SQL statement, you can enter procedural code, although this is rarely used. To do so, prefix the code with "*-". Alternatively, if you want no SQL statement at all because you will create a result set manually in the AfterData script for the report, enter "*:" for the SQL statement in this step.

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

To visually see the relationships for the tables in the SQL statement, click the View button. Here's an example:

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.xlsx by default. The following code accomplishes that:

loReport.SetOutputToFile('C:\MyFolder\Sales.xlsx')

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 Object Members 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:\MyFolder\ReportLog.txt', .T.)

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 from the Customers table (which is grouped) and Total Sale Amount from the Orders table (which is summed). Because it's a summary report, it just shows the total sales for each customer. However, you only want those customers whose total sales are more than $10,000. The problem is that you can't filter on Total Sale Amount is greater than $10,000 because that only finds records where each individual sale is more than $10,000, not the total of all sales for a customer. The following code prompts the user for the value they want to filter on, then pulls from the original result set only those customers with a total more than that value:

lnValue = GetValueForField('Orders.Total', 'greater than')
if not isnull(lnValue)
  select * from (ResultSet) ;
    where Total >= lnValue into cursor NewResults
endif

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.

If the report includes a chart, another variable named ChartResultSet contains the name of the result set used for the chart.

If this code returns False, the report execution is halted.

The AfterPreview script allows you to code what happens when the Preview window is closed for the report. For example, suppose you want to output the report's result set to a CSV file after previewing it. The following code does that:

copy to C:\MyFolder\MyDataFile.csv type delimited

You can use code in the BeforeOutput script to change output settings before a report is output to a file or email. For example, to use a pipe character (|) rather than a comma as the delimiter for a comma-separated values (CSV) file, use this code:

loReport.oOutput.cDelimiter = '|'