Reports Information
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
- 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.
|
- 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.
- Select currentRow.
Another list is displayed, with all of the attributes of the current row of
your query.
- Select the following columns to include in your report:
A report form is created and loaded into the cursor.
- 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.
- 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:
- Select Promote Part Feature from the query part's pop-up
menu, then select name from the attribute list.
- Type hostVar in the Promote feature name field and
click on Promote.
- 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.
- 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.
- To run the script, connect the selectedItemChanged event of the
drop-down list part to the showString script of
BusinessExpenseView.
- Pass the string to the database query in BusinessExpenseReport,
as follows:
- In the Composition Editor containing BusinessExpenseView and
select Options ==> Add part.
- 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
- 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.
- Connect the normalResult attribute of the connection to the
showString script to the hostVar attribute of the report
part.
Creating the second query
- 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'))
- Add another report line to the body of the report shell and delete the
default text field.
- 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.
- Select the NAME attribute to include the name of the
employee's manager in your report.
- Drop the report fields onto the new line in the report body.
- 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.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]