Reporting on a database query
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:
• The first query retrieves employee information for the name selected in BusinessExpenseView.
• The second query uses the results of the first query to retrieve the name of the employee's manager.
Creating the first query
1. Add a Multi-row Query part to the free-form surface of the Composition Editor. Use this part to create a query with a host variable as follows:
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
2. Tear off the result table from this query part and select Quick Report from the result table's pop-up menu to generate report fields.
A list of features of the result table is presented.
3. Select currentRow.
Another list is displayed, with all of the attributes of the current row of your query.
4. Select the following columns to include in your report:
a. NAME
b. ID
c. DEPTNAME
d. DEPT
A report form is created and loaded into the cursor.
5. Drop the loaded quick report into the report line in the body of the report shell.
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.
6. Rearrange the fields to match the illustration at the beginning of this chapter. See Creating a simple report for instructions on changing the settings of report parts.
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:
1. Select Promote Part Feature from the query part's pop-up menu, then select name from the attribute list.
2. Type hostVar in the Promote feature name field and click on Promote.
3. Save the report part.
Setting the host variable
To set the host variable for this query, you need to make a few changes to BusinessExpenseView.
1. In the Composition Editor for BusinessExpenseView, switch to the Script Editor and add the following script:
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.
2. To run the script, connect the selectedItemChanged event of the drop-down list part to the showString script of BusinessExpenseView.
3. Pass the string to the database query in BusinessExpenseReport, as follows:
a. In the Composition Editor containing BusinessExpenseView and select Options ==> Add part.
b. In the Add Part window, set the following options:
Class name
Type
BusinessExpenseReport
Part name
Type a name to use to identify BusinessExpenseReport in the Composition Editor
Part type
Select Part
c. Drop the part onto the free-form surface.
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.
d. Connect the normalResult attribute of the connection to the showString script to the hostVar attribute of the report part.
Creating the second query
1. Switch to the Composition Editor for BusinessExpenseReport and add a Single Row Query part to the free-form surface of the Composition Editor. Use this part to create a query with a host variable as follows:
SELECT * FROM STAFF
WHERE ((STAFF.DEPT = :dept) AND (STAFF.JOB = 'Mgr'))
2. Add another report line to the body of the report shell and delete the default text field.
3. To generate report fields from the query results, select Quick Report from the query part's pop-up menu, and then select resultRow.
A list of features of the current row is presented.
4. Select the NAME attribute to include the name of the employee's manager in your report.
5. Drop the report fields onto the new line in the report body.
6. To pass the DEPT value of the first query to the second query, connect the DEPT attribute from the current row part of the first query to the dept attribute of the second query part.
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.
Last modified date: 05/18/2020