The Expert view displays the SQL expression generated by the graphical settings established by the Search Criteria and/or the Sort Order.
For example, sorting by Province in ascending order and, within Province, sort City in descending order:
where SF_MVCity.City = [City] order by SF_MVCity.Prov DESC,SF_MVCity.City ASC
The Expert view is editable and the SQL expression can be:
The Expert view also contains the Lock table option.
This option should be used with extreme caution as it may unnecessarily curtail
data processing by other
users. Locked tables become unavailable to other applications and users until the lock is removed.
The following example was created with the Report Builder - Report Procedures (it should not to be construed as a good example as to when to use this option, it just happens to be a short Report that allows us to explain how to use it):
This example showed the soft-coded option. This will enable you to defer the decision whether or not to lock the table until run-time. The following image is the displayed window after the Run-time - Run Report, Report > Select application / menu selections have been made and CustomerNames was selected from the Select Report window:
Purpose of the Lock table option
Sometimes database table's contents must be preserved for a certain (usually short) period of time, during which the table's contents cannot be changed by any other application. An example of this can be a table that stores Customer account data. If you want to be sure that you run a precise analytical report (e.g. aging of AR), then you might consider locking the contributing tables during the running of this report.
When the need to lock tables is associated with a long-running report, you may also consider scheduling the report to start running automatically during off-hours so as to not interfere with normal business activities.
If table locking is to be used, set the Lock table switch in the Database Retrieval Inspector Expert view. The switch can be set permanently to a fixed value (YES or NO), or setting the switch can be deferred until run-time by passing it in the procedure's variable. Either way, the table becomes locked when control reaches the Database Retrieval object in the flowchart. The locked table remains locked until the procedure exits, at which point the lock is removed.
The locked table becomes unavailable to other applications until the lock is removed, for this reason table locking should be used sparingly on an as-needed basis.
Table of Contents
Global Template - Topics of Interest
Subledger Template - Topics of Interest
Transaction Template - Topics of Interest