Report Data Source 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 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.

• 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.

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.

A different data presentation option is provided through the Crystal Report method. In this case a Crystal Report is developed outside of Versago. The report is then registered in Versago and used as the presentation method.

1. Select the Crystal Report object type.
2. A dialog box is presented to select the desired Crystal Report.

1. Click [Choose File] to open the standard Windows Explorer tool.
2. Locate and “open” the desired Crystal Reports file (.RPT).
   1. The file name is displayed in the Versago dialog.
3. Click [Select] to associate the Crystal Report file with the Versago report.
   1. Once the file has been selected you will see file path information displayed in the Report Data Source box.

The Crystal Report source file is copied from the original location to the folder “C:\Program Files (x86)\TWBS\Versago\Websites\Versago Administration Web Site\Reports.” A number is added in front of the file name to ensure it always unique. If the Crystal Report needs to be modified, you can either modify this file or modify it elsewhere and re-register the new version in Versago.

The B1 Saved Query option is only available for sites using the SAP Business One business management system. This option provides a method to use queries developed in SAP Business One as a Versago data source.

The database connection (“Database” as described earlier in this document) must include the credentials required to connect to SAP Business One for this option to work. This configuration is described in the Versago – Database Connections Configuration document.

1. Select the “B1 Saved Query” option.
2. Click the “Report Data Source” drop-down to see a list of available saved queries from SAP Business One.
3. Select the desired query.

While this option is available, the preferred approach is to copy the query from Query Manager in SAP Business One and paste it into Versago as a SQL Select statement.

In some rare instances it may be necessary to use a SQL Stored Procedure as the data source. This approach is not recommended if any other option is available. However, it is available.

1. Select the “Stored Procedure” option.
2. Click the “Report Data Source” drop-down to see a list of available stored procedures in the designated database.
3. Select the desired procedure.

Keep in mind that if the procedure is changed so that different/additional column names are returned, you must step through the report configuration process again to be sure these changes are accounted for in the “Display” step.

The “View” option has been deprecated since a SQL View can be referenced in the SQL Select option.