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, this statement may use inner joins, and it might be better to use outer joins for some types of reports. 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, or just after data has been retrieved. You can also indicate whether duplicate records are allowed and whether only the top number of records are 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 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.
Note that the first step is the only one available unless you are defined as an "advanced" user.
Step 1 allows you to specify some advanced settings for this report. The options available in this step are:
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, turn the Include duplicate records option on so these records appear as well.
Use Step 2 to customize the SQL statement for the report. This step shows the SQL statement that 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 Step 3 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 variables loReportEngine and loReport contain references to the report engine and selected report object so your code can access properties and methods of these objects as necessary.
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 Step 4. 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 Step 5, 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 sum or averages calculated. Use Step 6 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.
For example, say you want to show customers sales and the percentage that each customer's sales is of the total. In Step 6, you can calculate the total sales amount using the following code:
public lnTotalSales sum INVAMT to lnTotalSales
Then, in the Advanced Report Designer, you can add a new field with INVAMT/lnTotalSales * 100 as the expression to show the percentage that each sales amount is of the total.
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.