This is an old revision of the document!
Table of Contents
Report Object Types
Report Object Types are the mechanisms that can be used to obtain desired data from a database. Only one method (object type) can be selected for a report.
The image above shows the list of methods displayed as of Versago version 2.2. However, some of these methods are slated for removal in upcoming versions. Use only the options described below.
SQL Select
The SQL Select method is the most commonly used option. When it is selected, a text box is presented where standard SQL script can be entered. The script can be entered manually or pasted from another source such as SQL Server Management Studio.
Note that only SELECT statements may be used.
- Enter the SQL SELECT statement in the “Query Statement” area.
- Once the SQL statement has been entered, click [Execute] to validate the statement.
- Standard SQL error messages will be displayed if errors are encountered.
- If the statement is correct, the first 50 records of the dataset are displayed in the “Output Results” area.
- Date values are shown in a rather strange format as show in the image above for versions earlier than v2.3. This is normal. The dates will be displayed correctly in the report presentation.
- Click [Save] to confirm that the results are what you wanted and to save the SQL statement. The SQL Select dialog area will close automatically.
- Click [Close] to close the SQL Select dialog box without saving any changes that may have been made.
When working with an existing report the first part of the current SQL Select statement is displayed. Click on the […] button to open the editing window. You can also “grab” the lower-right corner of the window and drag to make it larger.
The SQL statement can be changed at any time. However, remember that changes may affect other areas of the report defined in subsequent steps. Be sure to complete the [Execute] and [Save] steps if you do make changes. Otherwise your new selection station will not be saved. Then be sure you continue the wizard through the “Display” step to ensure any data changes are reflected in the formatted report output.
SQL Statement Restrictions
Certain SQL statements are not allowed in the SQL Select function.
- An Order By clause cannot be used except in cases where the statement contains a TOP operator. The ordering (sorting) is handled by the report output process. See Supplemental Field Controls for additional information on sorting.
- A Select Distinct statement cannot be used directly. This is due to some internal constraints used in the Versago SQL processing. If “Select Distinct” is needed it will need to be done using a sub-query. An example is found » here.
- Computed values in the SQL statement must be assigned an alias. For example, the statement “ColA + ColB” will not work. Instead, you would need to use something like “ColA + ColB as [CalcValue]” where [CalcValue] is the alias. “CalcValue” is then seen as a valid column in the subsequent report definition steps.
