Runtime Parameters
Runtime parameters can be used to supply query templates with tokens that are replaced during query execution.
The tokens can be values from:
- User input from dashboard filters,
- User input from filters
- Hidden user level filters based on the user configuration
- Hidden Content filters (embedding scenarios).
Tokens can be applied at the query level or at the datasource level.
Runtime parameters are only applicable for Direct queries.
In addition to queries, Runtime parameters can also be applied at the datasource level (not too common).
High Level Steps
- Define the token(s) in the Query.
- Specify an optional label if you'd like the user to input the values.
- When a widget associated to this query is added into the dashboard, the filter will include the Label associated to the token (or applied automatically if hidden).
- For User input fields, if you'd like a picklist to be shown, change the filter to get suggestions from a another dataset that has a list of values.
There are two ways to create Runtime parameters:
Query Editor
You can write Runtime Parameters into the query directly using Query Parameters Format.
Or, use the visual Token editor:
Click on the {:} Token button to create the runtime query. Click on the Insert Token button to add the token to your editor where your current cursor is.
The token modes can be one of the following modes:
Single Value
In this case, the token is a single value passed in. Define the following to insert the token:
Parameter Name: An identifier (without spaces).
Default Value (Optional): A default value to pass into the runtime parameter if the value is empty. Leave it empty if you do not wish to set a default.
Label (Optional): Enter the name you want the runtime filter to be displayed as in the filter menu at the dashboard or widget level.
List
In this case, multiple values are handled in the filter by adding Quote and Separator. Depending on your datasource and type, you will need to insert quotation marks and a separator. For example, for filter values of A and B selected by the user, if the Quote is a double quote and the Separator is a comma, the translation will occur as "A","B"
Date
Formats the date selected by the user from the filter to a date into the query, using the format required in your use case. For example, if the selected value is today, then today's date will be formatted into the query that you have specified.
Date Range
Formats a Date Range selected by the user. Use the same token label for the start and end date.
For example, the user selects a date range from the filter. To translate that date into your query, you can create the token in your where condition for the
date greater than equals to the start date (set that as the From), and date less than equals to the end date (set that as the To).
Cloud 9QL
Use the {:} Token button to write the Cloud9QL syntax. Click on the Insert Token button to add the token to your editor where your current cursor is.
URL Parameters
Use the {:} Token button to write the URL Parameters. Click on the Insert Token button to add the token to your editor where your current cursor is.
Note: Limited to REST API datasource only
Visual Builder
Use the visual builder and create the Runtime Query Parameter via Filter functions in a no-code environment.
Preview Results
You can preview the configuration for the filter by clicking on the Preview button and selecting the value (if the default is not configured).
Note: If the default value is already selected, then the same will become automatically available for you while running the preview.
Once saved, you can add the runtime Query into the dashboard.
Using Runtime Parameter as a Filter
If a label is specified, the Label will be shown in the Field to Filter dropdown for Dashboard and widget filters.
Example: Using the runtime parameter filter with label- "MEMBERS" at the dashboard and widget level:
If a label is not specified, pass in the token from the query builder
Configuring Runtime Parameters (C9 Tokens)
To optionally use a runtime parameter:
1. Set the token default value to *
2. Prepend |$ at the start of the optional section
3. Append $| at the end of the optional section
Syntax
Scenario 1: Optional runtime parameters placed at the middle or end of a WHERE statement:
WHERE <field1> = <value>
|$ and <field2> = $c9_<tokenName>$(*)$[<filterLabel>]$ $|
and <field3> = <value>
Example 1: The following query will return records where state equals AZ and all city values if a value is not otherwise specified.
SELECT *
WHERE state = AZ
|$ and city = $c9_city$(*)$[city]$ $|
Scenario 2: Optional runtime parameter(s) at the beginning of a WHERE statement:
Single optional runtime parameter
|$ WHERE <field> = $c9_<tokenName>$(*)$[<filterLabel>]$ $|
Optional runtime parameter placed at the beginning of a *WHERE statement with additional required condition(s)*
WHERE |$ <field> = $c9_<tokenName>$(*)$[<filterLabel>]$ and $|
<field2> = <value>
Example 2: The following query will pass all city values by default if a value is not otherwise specified.
SELECT *
|$ WHERE city = $c9_city$(*)$[city]$ $|
Scenario 3: Nested optional runtime parameters with multiple required conditions:
WHERE <field1> = <value>
|$ and <field2> = $c9_optional$(*)$
|$ and <field3> = $c9_optional2$(*)$ $| $|
and <field4> = $c9_required$(<value>)$
Example 3: The following query will return records where state equals AZ and set the default neighborhood to Uptown. All cities and streets will be returned by default. If only a street is passed as a filter value, it will not be returned unless a city is also specified.
SELECT *
WHERE state = AZ
|$ and city = $c9_city$(*)$[city]$
|$ and street = $c9_street$(*)$[street]$ $| $|
and neighborhood = $c9_neighborhood$(Uptown)$[neighborhood]$
c9_user_timezone Token
The $c9_user_timezone$ token allows users to dynamically display/use the user?s Knowi time zone within a query.
Note: Only direct queries can use the $c9_user_timezone$ token.
To display the timezone:
Navigate to the query editor.
Select Direct Query as the data strategy.
Write your query and include the $c9_user_timezone$ token. The token will be replaced dynamically by the user?s Knowi timezone.
Save your dataset.
For example:
Query Parameters Format
Query parameters must start with $c9_.
$c9_<name>$(defaultValue)
$c9_<name>$(defaultValue)
Example: $c9borough$(Manhattan)$[Borough]$ Here the parameter name is $c9borough, the default value is Manhattan (can be empty brackets) and a custom label display on the filter at the widget/dashboard level. The label is optional and will default to the query parameter if not specified.
More complex example, with multiple values and types:
$c9_<name>$(<default>)$[<label>]${list|<quotation>|<separator>}$
List of strings
Use case: User selects multiple values on the filter for an attribute, and they need to be mapped into a query.
$c9_<name>$(<default>)$[<label>]${list|<quotation>|<separator>}$
Each object value in list will be presented as a string.
Example
$c9_myMacro123$(Some value)$[<label>]${list|"|,}$
Example of query with macro:
db['restaurants'].find({
"borough" : { $in: [$c9_myMacro123$("Bronx")$[My label]${list|"|,}$ ] }
})
If the user selects 2 strings, say _Queens_ and _Brooklyn_, those shall be mapped as the following during runtime:
db['restaurants'].find({
"borough" : { $in: ["Queens", "Brooklyn"] }
})
Single Date object
$c9_<name>$(<default>)$[<label>]${date|<date format>}$
Value is a date and it will be formatted into a custom format string from field of a macro. This will result in Date/Calendar picker on the UI, which will be translated into the appropriate date format into the query at runtime.
Supported formats:
Show supported formats list
yyyy-MM-dd'T'HH:mm:ss.SSS'Z'
yyyy-MM-dd'T'HH:mm:ss'Z'
yyyy-MM-dd'T'HH:mm:ss.SSS zzz //D + T + Millis + ZZ (GMT, UTC, etc)
yyyy-MM-dd'T'HH:mm:ss zzz //D + T + ZZ (GMT, UTC, etc)
yyyy-MM-dd HH:mm:ss.SSS zzz //D + T + Millis + ZZ (GMT, UTC, etc)
yyyy-MM-dd HH:mm:ss zzz //D + T + ZZ (GMT, UTC, etc)
yyyy/MM/dd HH:mm:ss.SSS zzz //D + T + Millis + ZZ (GMT, UTC, etc)
yyyy/MM/dd HH:mm:ss zzz //D + T + ZZ (GMT, UTC, etc)
yyyy-MM-dd'T'HH:mm:ss.SSSZZZ //D + T + Millis + ZZ (XXXX)
yyyy-MM-dd'T'HH:mm:ssZZZ //D + T + ZZ (XXXX)
yyyy-MM-dd HH:mm:ss.SSSZZZ //D + T + Millis + ZZ (XXXX)
yyyy-MM-dd HH:mm:ssZZZ //D + T + ZZ (XXXX)
yyyy/MM/dd HH:mm:ss.SSSZZZ //D + T + Millis + ZZ (XXXX)
yyyy/MM/dd HH:mm:ssZZZ //D + T + ZZ (XXXX)
yyyy-MM-dd'T'HH:mm:ss.SSSSSSXXX //D + T + Millis + ZZ (XX:XX)
yyyy-MM-dd'T'HH:mm:ssXXX //D + T + ZZ (XX:XX)
yyyy-MM-dd'T'HH:mm:ssX //D + T + ZZ (XX)
yyyy-MM-dd HH:mm:ss.SSSXXX //D + T + Millis + ZZ (XX:XX)
yyyy-MM-dd HH:mm:ssXXX //D + T + ZZ (XX:XX)
yyyy-MM-dd HH:mm:ssX //D + T + ZZ (XX)
yyyy/MM/dd HH:mm:ss.SSSXXX //D + T + Millis + ZZ (XX:XX)
yyyy/MM/dd HH:mm:ssXXX //D + T + ZZ (XX:XX)
yyyy/MM/dd HH:mm:ssX //D + T + ZZ (XX)
yyyy-MM-dd'T'HH:mm:ss.SSS //D + T + Millis
yyyy-MM-dd'T'HH:mm:ss //D + T
yyyy-MM-dd HH:mm:ss.SSS //D + T + Millis
yyyy-MM-dd HH:mm:ss //D + T
yyyy-MM-dd //D
yyyy/MM/dd HH:mm:ss.SSS //D + T + Millis
yyyy/MM/dd HH:mm:ss //D + T
yyyy/MM/dd hh:mm a
yyyy/MM/dd HH:mm //D + T (up to minutes)
yyyy/MM/dd //D
// Formats starting with MM and yyyy
MM-dd-yyyy"
MM/dd/yyyy HH:mm:ss zzz
MM/dd/yyyy hh:mm a
MM/dd/yyyy HH:mm
MM/dd/yyyy
// Formats starting with MM and yy
MM/dd/yy HH:mm:ss.SSS
MM/dd/yy HH:mm:ss zzz
MM/dd/yy HH:mm:ss
MM/dd/yy hh:mm a
MM/dd/yy HH:mm
MM/dd/yy
Example
Macro:
$c9_myMacro123$(2014-01-01T01:01:01Z)$[My date]${date|yyyy-MM-dd'T'HH:mm:ss'Z'}$
Example of query with macro:
db['pagehits'].find({
"lastAccessTime" : { $gt: { $date : "$c9_myMacro123$(2014-01-01T01:01:01Z)$[My date]${date|yyyy-MM-dd'T'HH:mm:ss'Z'}$" } }
})
The filter value for field "My date" is date 2015-05-05.
The result query with applied macro and filter value:
db['pagehits'].find({
"lastAccessTime" : { $gt: { $date : "2015-05-05T00:00:00Z" } }
})
The optional default value "2014-04-08T01:01:01Z" of macro will be used in case there is no provided filter with "My date" set. It will be used as string as-is without changing format.
Date Range
$c9_<name>$(<default>)$[<label>]${date|<date format>|<from|to>}$
Value is a date range (the start date and end date) and each date will be formatted as string using custom format from field of a macro. The macro with "from" keyword will get the start range value, and macro with "to" keyword will get the end range value.
Supported formats:
Show supported formats list
yyyy-MM-dd'T'HH:mm:ss.SSS'Z'
yyyy-MM-dd'T'HH:mm:ss'Z'
yyyy-MM-dd'T'HH:mm:ss.SSS zzz //D + T + Millis + ZZ (GMT, UTC, etc)
yyyy-MM-dd'T'HH:mm:ss zzz //D + T + ZZ (GMT, UTC, etc)
yyyy-MM-dd HH:mm:ss.SSS zzz //D + T + Millis + ZZ (GMT, UTC, etc)
yyyy-MM-dd HH:mm:ss zzz //D + T + ZZ (GMT, UTC, etc)
yyyy/MM/dd HH:mm:ss.SSS zzz //D + T + Millis + ZZ (GMT, UTC, etc)
yyyy/MM/dd HH:mm:ss zzz //D + T + ZZ (GMT, UTC, etc)
yyyy-MM-dd'T'HH:mm:ss.SSSZZZ //D + T + Millis + ZZ (XXXX)
yyyy-MM-dd'T'HH:mm:ssZZZ //D + T + ZZ (XXXX)
yyyy-MM-dd HH:mm:ss.SSSZZZ //D + T + Millis + ZZ (XXXX)
yyyy-MM-dd HH:mm:ssZZZ //D + T + ZZ (XXXX)
yyyy/MM/dd HH:mm:ss.SSSZZZ //D + T + Millis + ZZ (XXXX)
yyyy/MM/dd HH:mm:ssZZZ //D + T + ZZ (XXXX)
yyyy-MM-dd'T'HH:mm:ss.SSSSSSXXX //D + T + Millis + ZZ (XX:XX)
yyyy-MM-dd'T'HH:mm:ssXXX //D + T + ZZ (XX:XX)
yyyy-MM-dd'T'HH:mm:ssX //D + T + ZZ (XX)
yyyy-MM-dd HH:mm:ss.SSSXXX //D + T + Millis + ZZ (XX:XX)
yyyy-MM-dd HH:mm:ssXXX //D + T + ZZ (XX:XX)
yyyy-MM-dd HH:mm:ssX //D + T + ZZ (XX)
yyyy/MM/dd HH:mm:ss.SSSXXX //D + T + Millis + ZZ (XX:XX)
yyyy/MM/dd HH:mm:ssXXX //D + T + ZZ (XX:XX)
yyyy/MM/dd HH:mm:ssX //D + T + ZZ (XX)
yyyy-MM-dd'T'HH:mm:ss.SSS //D + T + Millis
yyyy-MM-dd'T'HH:mm:ss //D + T
yyyy-MM-dd HH:mm:ss.SSS //D + T + Millis
yyyy-MM-dd HH:mm:ss //D + T
yyyy-MM-dd //D
yyyy/MM/dd HH:mm:ss.SSS //D + T + Millis
yyyy/MM/dd HH:mm:ss //D + T
yyyy/MM/dd hh:mm a
yyyy/MM/dd HH:mm //D + T (up to minutes)
yyyy/MM/dd //D
// Formats starting with MM and yyyy
MM-dd-yyyy"
MM/dd/yyyy HH:mm:ss zzz
MM/dd/yyyy hh:mm a
MM/dd/yyyy HH:mm
MM/dd/yyyy
// Formats starting with MM and yy
MM/dd/yy HH:mm:ss.SSS
MM/dd/yy HH:mm:ss zzz
MM/dd/yy HH:mm:ss
MM/dd/yy hh:mm a
MM/dd/yy HH:mm
MM/dd/yy
Example
Macro:
$c9_myMacro123$(2014-01-01T01:01:01Z)$[My date]${date|yyyy-MM-dd'T'HH:mm:ss'Z'|from}$
$c9_myMacro123$(2014-03-03T01:01:01Z)$[My date]${date|yyyy-MM-dd'T'HH:mm:ss'Z'|to}$
Example of query with macro:
db['pagehits'].find({
"lastAccessTime" : {
$gt: { $date : "$c9_myMacro123$(2014-01-01T01:01:01Z)$[My date]${date|yyyy-MM-dd'T'HH:mm:ss'Z'|from}$" },
$lt: { $date : "$c9_myMacro123$(2014-03-03T01:01:01Z)$[My date]${date|yyyy-MM-dd'T'HH:mm:ss'Z'|to}$" }
}
})
The filter value for field "My date" is date range from 2015-06-06 and to 2015-08-08.
The result query with applied macro and filter value:
db['pagehits'].find({
"lastAccessTime" : {
$gt: { $date : "2015-06-06T00:00:00Z" },
$lt: { $date : "2015-08-08T00:00:00Z" }
}
})
The optional default value "2014-01-01T01:01:01Z" of macro will be used in case there is no provided filter with "My date" set. It will be used as string as-is without changing format, in "from" macro. The optional default value "2014-03-03T01:01:01Z" will be used same way but in "to" macro.
User Level Runtime Parameters
Runtime parameters are not just applicable for query to dashboard flow, but can also be used provide row level security at a user level. For example, if a user is only allowed access to a specific id that is mapped into a query, you can set that at the user level.
User level filters are always applied automatically for that user and cannot be modified by the user. Mode details here (docs/user-content-filters.html).
This also applied to embedded use case cases, for both SSO and secure URL based approaches, where the tokens can be passed in to the underlying queries securely. For more details on using contentFilters in an embedded use case, see Embed SSO
Datasource Level Runtime Parameters
Runtime parameters can also be applied at a datasource level. Typically used in cases where datasource configuration may be dependent on a per user/customer basis (not common).
Datasources
Knowledge Center
Video Tutorials
Security
About Knowi