To report on a database query, you can add the database query parts directly to the new report part you have just created and generate report fields from the query result table. In this sample report, you will report on two database queries:
Creating the first query
SELECT DISTINCT STAFF.NAME, STAFF.ID, ORG.DEPTNAME, STAFF.DEPT, ORG.DEPTNUMB FROM ORG, STAFF WHERE ((STAFF.NAME = :name) AND (ORG.DEPTNUMB = STAFF.DEPT))
|
![]() | Drop the query part to the right of the report shell to make it easier to layout connections. |
A list of features of the result table is presented.
Another list is displayed, with all of the attributes of the current row of your query.
A report form is created and loaded into the cursor.
You will see the quick report containing a pair of report text fields for each data column you selected. The first report text field of each pair serves as the label for the second report text field. The object attribute of the second report text field is connected to the appropriate attribute of the current row object so that the correct data is displayed at run time.
Creating a host variable
The host variable for the query you just created, will be passed to the report from BusinessExpenseView. To set the host variable, you need to promote it in your report part:
Setting the host variable
To set the host variable for this query, you need to make a few changes to BusinessExpenseView.
showString "Show selected column as String" | string | string := (self subpartNamed: 'Drop-down List1') selectedItem colGet: 'NAME'. ^string
This script converts the name selected in the drop-down list to a string so that it can be passed into a database query as a host variable.
Note: | This step adds the report part to your visual part. If you change your report part, be sure to save the report part before attempting to view it in the visual part. Changes will not be reflected in the visual part until they have been saved in the report part. |
Creating the second query
SELECT * FROM STAFF WHERE ((STAFF.DEPT = :dept) AND (STAFF.JOB = 'Mgr'))
A list of features of the current row is presented.
Running the queries
The two queries you have defined for your report need to be run in order: the first query before the report is formatted, and the second query after the first query runs. To run the first query, connect the aboutToFormat event of the Report Shell to the executeQuery action of the Multi-row Query.
To trigger the second query, connect the executedQuerySuccessfully attribute of the first query part to the executeQuery action of the second query part.
Note: | If a quick report is done from a Multi-row Qyery, then the query will be executed when the report is run. All other queries must be connected to an event which triggers the executeQuery action. In this example the quick report was done on the current row of the result table, not on the Multi-row query, therefore the Multi-row query would not execute automatically. |
After you have defined both queries and generated quick reports for their
results, your report part should look similar to the following. In this
illustration, the connections between the report text fields and the current
row parts have been hidden so that you can see the connections between the
queries better. Some fields on the report have also been moved.