|
|
Run Report
Overview
Reports can be previewed and printed from the Report Designer under the Tools menu, however, the report designer does not provide a mechanism to filter or restrict which records are included in the report. The procedure to print a report and filter or restrict which records are included is:
Select
a Tasks
| Run
Report from the menu. An Open file dialog will appear.
Select
and open the report (.EZT) file that you want to work with. A Query
Builder dialog will appear.
If
you want to apply a filter to the report, either open an exist query or design a new one using the Query
Builder.
Click
Run Report when you are ready to run the report using the filter.
The
Report
Designer will open and display the report for previewing or printing using only the filtered records
to build the report. You can modify and preview the report as needed, however you will not be allowed
to save your changes. Saving changes to report designs is restricted to users with access to the Report
Designer under the Tools
menu.
When
you exit the Report
Designer, you will be given an opportunity to return to the Query Builder in case you want to modify
or save your query.
The Query Builder is used to apply a filter to restrict or limit the records that are used to generate a report. The dialog below appears automatically after selecting a report to work with under Tasks | Run Report. The example below has two (2) filter conditions already defined.
File Menu
The File menu of the Query Builder is used to start new queries, open existing queries, save queries and run reports. The open query method and run report method can also be invoked by command buttons on the dialog.
Open and Save Queries
Two special dialogs are used to open and save queries. The Open Query dialog can be displayed by clicking the Open Query button or using the File | Open Query menu selection. The Save Query dialog can only be displayed using the File | Save Query or Save Query As menu selection.
|
|
|
Queries are saved on the hard drive in the \DATA directory using a special naming format:
[record source] query name.qry
The record source will automatically be extracted from the report you selected. The query name (what you see above in the open and save dialogs) is the name you give the query.
The [record source] and query name, if assigned, also appear in the dialog title.
Since you can have multiple reports based on the same record source, once you save a query for the record source, that query becomes available for all reports based on that record source.
The Field list can be dropped down to reveal all fields available in the record source associated with your report. Fields are used with Operators and Values then ANDed or ORed to the query.
The Operator list displays a list of legal comparison operators for the current Field. Operators are used with Fields and Values then ANDed or ORed to the query.
The Value section is used to enter a comparison value for your query. This section will change depending on the Field type, i.e., text, numeric, date/time, logical. Values are used with the Fields and Operators then ANDed or ORed to the query.
The value section for text and numeric fields is simply an empty box where you can type a comparison value.
For logical (boolean) fields, the value section provides a drop down list as shown below from which you can select True or False as the comparison criteria.
There are two value options for date/time fields, Today and Range. The Today option is a special option where EZScale automatically substitutes todays date into the query each time you run the report. This is useful for daily reports.
If you select Range, you will then be given an opportunity to select a From and To date. The From and To dates can be the same. A pop-up calendar is available by clicking the drop-down arrow by the From and To fields.
After selecting a Field, Operator and Value, you must AND it or OR it to the query by clicking the AND or OR button. The field, operator, and value will be inserted above the currently selected row in the list. The Query Builder will automatically add quotes ("") around text fields and pound signs (#) around date and time fields.
AND means that the condition is an additional condition that must be met to include the record.
OR means that the condition is an alternative condition that can be met to include the record.
(Add) and (Remove)
You can wrap (parenthesis) around rows of filter conditions to change the logic. Rows bounded by parenthesis are evaluated as a group. For example:
The query...
FirstName = "George" AND LastName = "Jungle" OR FirstName = "Sam" AND LastName = "Walton"
won't return any records because each clause is evaluated individually. That means that the LastName would have to be Jungle AND Walton even though they are far apart in the query, and that can not happen.
However, adding parenthesis to the query...
(FirstName = "George" AND LastName = "Jungle") OR (FirstName = "Sam" AND LastName = "Walton")
will return all records where the FirstName is George and LastName is Jungle, OR the FirstName is Sam and the LastName is Walton.
Select the rows that you want included by holding down the shift key as you select the range of rows to be included then click (Add) to place parenthesis around those rows.
The Query Builder will enforce rules that ensure parenthesis are matched. If you Remove or a row or move a row Up or Down, the parenthesis may be moved or deleted to maintain the rules.
You can manually remove a parenthesis by selecting the rows bounded by parenthesis and clicking the (Remove) button.
IMPORTANT: You can not include an OR within a parenthesis. You must rearrange your query to avoid placing the OR in the query. See the example below...
Say that you want a list of tickets printed for PurchaseOrderID 123 on 11/25/2000 OR 11/30/2000. The query...
PurchaseOrderID = 123 AND (DateTime = #11/25/2000# OR DateTime #11/30/2000#)
isn't valid.
Instead, rearrange the query as follows...
(PurchaseOrderID = 123 AND DateTime = #11/25/2000#) OR (PurchaseOrderID = 123 AND DateTime #11/30/2000#)
A similar example with results are presented below under the Complex Query Example.
Move Up and Move Down
You can move a row up or down by selecting it and clicking the respective Move Up or Move Down button. The Query Builder may move and/or remove parenthesis if necessary to maintain rules for parenthesis.
Following is a somewhat complex query using AND and OR join elements and parenthesis.
The results of the above query, using a sample set of data is shown below.
|
|
