Variables in Views/Scripts and Publishing to Intelligence Central
Notes:
- This feature is available in the Toad Data Studio Professional edition only and requires access to a Toad Intelligence Central server.
- This feature is supported in Intelligence Central 3.3 or later.
Toad allows you to publish Views and Automation scripts containing variables and then specify a default value for each variable during the publishing process. After publishing, you can modify the default value. For scripts, you can also provide a value at run time. The variables can be bind parameters or other types of variables, such as those supported in Automation scripts.
This feature allows data providers to publish views and Automation scripts with variables so that data consumers can change the variable values when they run the view or execute the script.
Object owners and users with Manage privileges can modify the default value (views and scripts). Any user with access to a script can enter a variable value at run time when manually executing. Scheduled scripts use the default value.
This feature applies to the following objects:
- View. SQL published as a view in which the SQL contains one or more variables (bind parameters or other variables)
-
Automation Script (Set Variable). An Automation script containing a Set Variable activity. To enable this feature, the Prompt during run option must be selected in the activity. See Set Variable activity in Use System Automation Activities for more information.
-
Automation Script (Send Email). An Automation script containing a Send Email activity. To enable this feature, the Prompt option must be selected in the activity. See Send Email activity in Use System Automation Activities for more information.
A Note About Datasets: Toad allows you to publish data as a dataset from SQL containing a variable. However, the cached value for the variable is used and you cannot edit this value after publishing. A cached value must exist before publishing. Execute the SQL to create a cached value.
Specify Default Value During Publishing
To specify a default value when publishing a view or script
- When publishing one of the objects listed above, in the publishing dialog click Default values in the Variables field. See Publish to Toad Intelligence Central.
- If you previously specified variable values, either through the Bind Variables dialog or through the script, Toad finds and enters those values.
- If cached values are not found, you must specify values before publishing.
Note: This option is enabled only for certain objects (listed above).
- On the Bind Variables and Parameters page, enter a value for each variable listed.
Edit Default Value After Publishing
To edit the default value after publishing
- Connect to the Intelligence Central server.
- In the Object Explorer, right-click the published view or Automation script and select View Details.
- Select the Variables tab. The default value is displayed.
- Click
to enter a new default value.
- For a view, the new value will be used when the result set is returned.
- For a script, the new value is used when the scheduled script is executed.
Tip: In the Object Explorer, select a view or script and click
Prompt for Value at Run Time (Scripts)
When manually executing an enabled Automation script in Intelligence Central, Toad prompts the user for a variable value. Enabled scripts were created with the Prompt or Prompt during run option selected in the applicable Automation activity. See list of applicable objects above. Scheduled script execution uses the default value.
To enter variable values at run time
- Connect to the Intelligence Central server.
- In the Object Explorer, select a script and click
to run it. Toad prompts you for a variable value. Enter the value. This value is used for this execution only. The default value remains unchanged.
Considerations and Limitations
| Consideration/Limitation | Description |
|---|---|
| ODBC connections: Publishing view with variable is not supported |
You cannot publish SQL containing a variable from an ODBC connection to Intelligence Central as a view. Workaround: You can publish SQL containing a variable from an ODBC connection to Intelligence Central as a dataset. The cached value for the variable is used and you cannot edit this value after publishing. |
| When inner view is located in another TIC instance | When publishing a view in which the source data is a view from another Intelligence Central instance, the variables in the inner (source) view are not included in the list of editable variables. For the inner view variables, the current default value is used. |
| Using published view in cross-connection query | When using a view (containing a variable) from Intelligence Central in a cross-connection query, you are not prompted for a variable value. The current default value is used. |
Tip: The Web Console in Toad Intelligence Central 3.3 (or later) allows the user to enter a variable value at run time. In Toad Intelligence Central 4.3 (or later) the user can also modify a variable default value. See the Toad Intelligence Central Quick Start Guide for more information.