Query Runner Parameters View
The Query Runner Parameters view is used to create all the parameters that can be used by each query runner step as parameters they can pass to their process.
Query Runner Parameter Features
All Query Runners, automatically have the parameters of the SQL Wrapper they are associated with. Each Query Runner Step can use the row results of that SQL Wrapper as parameters for their process.
However sometimes parameters you use to run a query don't make sense in the final result set, or aren't easily included there. Query Runner Parameters allow you to create additional parameters for running the Query Runner, that are accessible for every Query Runner Step. These values won't change for every row result of the SQL Wrapper, and are instead global parameter values for that Query Runner that can be used as parameters for every Query Runner Step.
Query Runners also provide two special parameters that are accessible to Query Runner Steps. They are the following:
| Name | Description |
|---|---|
| @runKey | The unique run key for that instance of the Query Runner execution. Each run produces a unique key that query runner steps have access to as a parameter they can use. |
| @rowResultNumber | Holds the current number of the row result from the Query Runner SQL Wrapper that is being used to execute the Query Runner Step |
Note
By default the system produces the @runKey value, but you can create your own parameter named @runKey and the system will always use that. This allows you to provide the same value every time (instead of the unique one the system does), or re-use a prior one, for debugging/testing purposes.
@runKey is useful for complex queries that are run hundreds of times by each step (as the main SQL Wrapper returns hundreds of rows). Instead your report could run a stored procedure that if the rownumber is 1, it runs the whole query and temporarily stores the data using the @runKey, then returns the results filtered for its parameter. Then when the stored procedure runs for the 2nd row number, it can see the @runKey results already exist and can just select with its parameter values. This can significantly speed up queries that take a long time to re-run for different parameters versus running once with no parameter.
Query Runner Parameter Properties
The parameters for your Query Runner have the same functionality as SQL Wrapper Parameters [see here]. Specifically for Query Runners they have the following properties:
| Name | Description |
|---|---|
| [Required] Name |
The user friendly name for this parameter |
| Description | A user friendly description for this parameter that is shown as a tooltip to the user |
| SQLType | The underlying sql type for this parameter in the function or procedure it writes for you. This should match the underlying sql type of the field itself if you are mapping it to one. i.e. if this holds an ID to a record then it should be of type uniqueidentifier. |
| IsNullable | Indicates if the field can be left blank with no input from the user |
| Max Length | If the field is a type of text field (nvarchar, etc) this indicates the max length for that field. Leaving this blank would be the equivalent of varchar(max) - presumably unnecessary for a text input parameter though. |
| Precision | If this field is a decimal this holds the precision value. i.e decimal(16,8) |
| Scale | If this field is a decimal this holds the scale value. i.e. decimale(16,8) |
| Visible | Indicates if this parameter should be visible to the user. By default some system parameters are not visible, such as those for permissions. This gives you the ability to make a field invisible and auto populate the value through formulas. |
| EntityType | If this field maps to a field in the system you can specify the entity type of that field here. Doing so will mean the system will understand automatically how to display a control for this parameter. For instance if you select Tasks as Entity Type and ID as Entity Property Name, the system will provide a popup of all available tasks for this parameter. |
| Entity Property Name | The property this field maps to within the selected EntityType. |
| Format | Applies any formatting to the display of this control. [see here for more info] |
| [Required] [Key] Param Name |
The unique parameter name. Must begin with an '@' symbol. |
| Required | Indicates if the user must enter a value for this parameter |
| Default Value | Provides a hardcoded default value for this parameter |
| Formula Value | Provides a formula that can be run at execution time to determine a default value for this parameter |
Commands
Note
A Query Runner Parameter has the standard set of commands and functions when being viewed from a grid. See our standard grid functionality.