Controlling Data in Reports
Before running a report or label, you set up the data needed by your report or label layout. You provide data for reports and labels by choosing and making data sources available, controlling the records selected or filtered in those data sources, and sorting, ordering, and relating those records.
You can also manipulate data within reports by using report variables and calculated fields. Report variables and calculated fields store values that are dynamically calculated during the report run. Your report layouts can display the results of these calculations as well as the data derived directly from your data sources.
Controlling Data Sources for Reports
You can provide data for reports and labels in a variety of ways, depending on which is best for your situation.
If you always run a report with the same data sources, you can define a data environment that is stored with the report, pointing to specific tables. Saving the data environment places additional records in the report or label definition table for all open tables and index files, index order, and any relationships between tables. The steps you use to define the data environment objects for a report or labels are the same as the process you use to define the data environment for a form. For more information, see How to: Set the Data Environment.
You can also make data available for a report by specifying the data sources using code every time you run a report, which is useful when running the same report with different data sources.
The following table describes methods of making data available during a report run. Choose the method that suits the way you want to use data sources.
| To | Add |
|---|---|
Always use the same data sources. |
Tables or views to the report's data environment. -OR- A DO <query> or SQL SELECT statement to the Init event of the report's data environment. For more information, see DO Command, SELECT - SQL Command, and Init Event. |
Use separate sets of data sources. |
One or more USE <table>,USE <view>, DO <query>, or SQL SELECT statements to Click event or other code that precedes a REPORT or LABEL command. For more information, see USE Command and Click Event. |
Note
Use aliases with the fields from the selected or driving table in the report only if you do not plan to use the report with data sources other than the original table. If you use a view or query as the data source and aliases are included in the report controls, sometimes the alias with which you designed the report might not be available at runtime. An error such as "Alias 'X' not found" occurs when the report engine evaluates expressions including the alias. At other times, if the original table is actually open, the alias might be available but not selected for this report run. In this case no error occurs, but the report displays the same record repeatedly on the report page.
Controlling Data Sessions for Reports
As part of deciding when and how to open data sources for a report, you determine whether to share these data sources with the report's environment.
Data sources for a report may be manipulated by your application before you run the report. For example, you can create a form that allows the user to navigate through a customer table. When the user clicks a Print button on your form, you run a report printing a current statement for the customer selected in the form. In this scenario, you run the report in the same data session as the form.
You can also choose to manipulate data for the report just before running the report, and completely independently of the report's surrounding environment. For example, the same customer form might have a button or menu option to Print a Sales Territory Summary, requiring you to summarize data from a number of tables not used directly in this form. In this scenario, you might choose to open the report data in a private data session. When the report finishes printing, the form's data environment and the selected customer record are undisturbed.
For more information, see How to: Specify a Report's Data Session.
Controlling Record Selection
By default, when you run a report against a table, each record in the table is processed. The report evaluates expressions in the report layout for each record, displaying appropriate results.
You can control selection of the records that appear in the report through the data source, report print options, or both.
The following table describes how to control record selection in the report based on whether you use a data source or report print options.
| When using for record selection | Use |
|---|---|
View or query |
Conditions in the Filter tab of the View Designer or Query Designer. For more information, see Filter Tab, Query and View Designers. |
SQL SELECT statement |
WHERE or HAVING clause. For more information, see SELECT - SQL Command. |
Report Designer |
Setting in the Print Options dialog box. For more information, see Report and Label Print Options Dialog Box. |
REPORT FORM command |
Scope, FOR, or WHILE expressions. For more information, see REPORT FORM Command and Field and Record Manipulation. |
Table |
Filtered index. For more information, see INDEX Command. |
Controlling Record Relationships
Reports and labels process your selected records by moving the record pointer in the current work area. By setting up relationships between the table or view opened in the current work areas and other data sources, you can access other fields for use in report expressions. You can set the relationships in code, using SET RELATION and SET SKIP commands, or by using Relation objects in the DataEnvironment.
When you refer to fields not in the current work area in report expressions, you prefix the field names with the alias under which the additional tables or views are open. Refer to How to: Create and Use Table Aliases for more information.
Note
The table or view in the current work area drives the REPORT FORM or LABEL FORM and is often termed its driving alias. Tables or view in the related work areas are known as target aliases because these work areas are the targets of SET RELATION command. When you establish relationships between tables, you can change the way reports and labels move through your data, to account for multiple records in the target aliases that may be related to each record in the driving alias. For example, a report on a table of invoice summary information may be related to multiple detail lines for each invoice. See Working with Related Tables using Multiple Detail Bands in Reports for information on coordinating multiple tables and relationships in reports.
Controlling Record Order
The page layout for reports and labels does not actually sort and order data. It processes records in the same order that they exist in the data source. You must perform sorting with a view, index, or other form of data manipulation outside the layout.
For example, if the data source is a table, you can sort and order the data by setting an appropriate index for the table, using an ordered view in the data environment, or using a query as the data source to display the records in groups. You can create a table index using code, for example, using the INDEX command, or as part of the report's data environment. If the data source is a query, view, or SQL SELECT statement, you can use the ORDER BY clause.
If you do not use the report's data sources to control the record order, the only way to control record order through the report is through the Order property of a cursor in the data environment.