User Defined Scripts in Reports

Within the Millennium Reporter, there is an advanced reporting option, set by choosing User Defined as the Worktable Format, that allows you to specify that a script that you have defined be launched automatically by the Reporter, for the purpose of creating a new, customized worktable. We would anticipate that this function would most commonly be used to create a new worktable based on the ID's from the Generic worktable that the system created when the report was run. In any case, the new export worktable will consist of the data and the worktable layout that you define in the custom script.

Important! This functionality is considered a very advanced option and is NOT part of any of the standard Millennium training courses. You MUST be comfortable and adept with SQL or Oracle scripting before you attempt to use this functionality. There could be significant hazard to the integrity of your database if this function were used to launch an injurious script. Problems resulting from the use of Custom Scripts (via this option or any other means) are not covered by our standard license and/or customer support agreements. Other than this document which provides a list of rules or assumptions that the system requires in order to execute your script, we cannot provide assistance in the creation of your scripts. If you choose to use this functionality, your institution assumes all responsibility for the outcome.

This process is carried out on the Queue machine on which the report will be executed. Therefore on the Options, Report form, when you specify User-Defined in the Workfile Format box and are given access to the Script Path/File Name text box, the path name you enter must be identified as it will be recognized by the Queue machine. The full UNC may be used, or if the script is located on the Queue machine, the directory name may be used. (If you entered C:\scriptname.txt, the system would look on the root of the C directory on the Queue machine.)

Considerations When Constructing a User-Defined Script

The system has certain expectations and requirements regarding variables or commands within the script.

@STARTTABLE

The system will expect to find this exact text ("@STARTTABLE" minus quotes) in the script, and will automatically replace this text with the system-assigned name of the generic worktable that was created by the run of the Millennium Reporter.

@ENDTABLE

The system will expect to find this exact text ("@ENDTABLE" minus quotes) in the script, and will automatically replace this text with either the system assigned name (if you did not choose to save the export worktable) or the user-entered name (if you did choose to save the export worktable). This text is required if you will be using the export worktable with a Crystal format. If you are simply creating an export worktable, this text is not a requirement.

@PARAMETER#

If the system finds this exact text ("@PARAMETER#" minus quotes and substituting a number for the # symbol) then it will substitute the corresponding parameter from the report criteria into the script.

GO

For SQL scripts, this text ("GO" minus quotes) signals the end of a single SQL command to be executed by the system. Each GO command must be placed on a line of its own.

; (semi-colon)

For Oracle scripts, this text (";" minus quotes) signals the end of a single Oracle command to be executed by the system. Each ";" command must be placed on a line of its own.

ODBC connection

For SQL sites, the ODBC connection on the Queue machine will be to the Millennium database.

Table Names

The system assumes that the script will only reference table views that are available to the user for whom the report is being run. If the script references a data table (gifts), or a table view (gifts_full) to which the user does not have access, no data from that table will be delivered to the new export worktable. Therefore, the script must be written in accordance with the security views of the user for whom the report is run. In general, use table views instead of table names.

Other Considerations

If the script is to be used with a Crystal format that was created using a worktable OTHER THAN the standard Millennium GENERIC, and if, at the conclusion of creating the format, the worktable is not pointed back to GENERIC, then the name of the actual worktable must be communicated to the Millennium Reporter. This is accomplished by taking note of the name of the worktable you use when creating the Crystal format, and then defining a parameter in the report criteria according to the following specifications, substituting the name of your worktable for 'Custom_table' as the Parameter Value:

Parameter Description = Custom Workfile Name
Parameter Value = Custom_table

The custom worktable will be owned by the user who runs the report, and it will be deleted after the hand-off to Crystal.

Sample Script

The following is a sample SQL script that has been successfully used to create a new export worktable, based on the generic worktable that was produced by the run of a report.

Testscript.txt