Create

This option is used to create a Reports Wand report definition from scratch. Click the Create button. This will launch a wizard to guide the user through the process.

Enter the short name for the report, as well as the report name. The Short Name and Report Name must be valid before you can proceed. This is indicated by a green tick on the left hand tab.

A PL/SQL stored procedure can be configured to run before the Report Definition SQL.

Click next.

Enter a SQL statement, and then click on the Next button to validate the SQL. If the SQL is invalid, an error message will be shown. The message will be typical Oracle SQL error message, and will depend on the type of error.

SQL Limitations

• The SQL query must not be longer than 32,000 characters. This is the limit on a single cell in Excel, where we store the SQL to create the definition.
• Lexical parameters are not supported.
• Typical conventions for aliases can be used; however, an alias within quotes cannot consist entirely of all upper-case characters, numbers, and underscores.
• There must not be a trailing semi-colon at the end of the SQL.
• You cannot end the SQL statement with a single line comment. Please use a multi-line comment if you require a comment at the end of the SQL.

If necessary, correct the SQL (the table name was invalid above) and validate it again. When the SQL is valid, you will automatically be taken to the next screen to set up the Bind Variables.

Any bind variables created in the SQL will be shown here. The user will need to select the correct type, and add a Value Set if applicable. The Prompt can also be changed to be more user friendly if required.

To make it easier to select the Value Set, it is possible to search the value sets using any string. Simply click the drop down list, and start typing in the filter field. This will reduce the list to any list that contains what has been typed.

In the screen shot above, the types have been changed, and the descriptions updated. More information on Bind Variables can be found here. Once complete, click next.

If there are any filters to add, add them here.More information on Filters can be found here. Then click next.

If the report is to be assigned to a Request Group now, select the required groups from the list and click the add button. Once all the Request Groups have been selected, click finish. This will create a Reports Wand report definition in Oracle.