asdfasdfsadf

Runtime Parameters

Runtime parameters can be applied to Direct query modes to apply run time paramaters into query (or datasource configuration). This is used for dynamic parameters into query either through filters via the UI or behind the scenes at a user level.

Creation

Query UI

You can also create basic runtime query parameters using the Filter functions available within the Query Builder UI.

Manually

Creating a runtime query parameter with a label:

Adding the Runtime query into a dashboard:

Add the above widget into a dashboard. The runtime query can now be run for both widget and dashboard filters and the value will be injected into the query created above.

Example:

db['pagehits'].find(
 {"Region":{"$eq":"$c9_region$(West)$"}}
)

$c9_region$ is replaced with contentFilter parameter (and defaults to West in the above example if one is not passed in).

This can be passed in either using the contentFilters URL parameter, via the JavaScript API, or can be set at user level. User level filters are always applied automatically for that user and cannot be modified by the user.

For example, we define a user contentFilters or URL contentFilters as:

[
    {"fieldName":"$c9_region$","values":["West"],"operator":"Equals"},
    {"fieldName":"$c9_territory$","values":["Bay Area"],"operator":"Equals"}
]

For filters on the cached datasets in Knowi, omit the $c9_ prefix. Example:

[
    {"fieldName":"region","values":["West"],"operator":"Equals"},
    {"fieldName":"territory","values":["Bay Area"],"operator":"Equals"}
]

For more details on using contentFilters in an embedded use case, see /docs/embedSSO.html

Query Parameters Format

Query parameters must start with $c9_.

$c9<name>$(defaultValue)

Example: $c9_borough$(Manhattan)$[Borough]$ Here the parameter name is $c9_borough, the default value is Manhattan (can be empty brackets).

$c9<name>$(defaultValue)$[label]$

Same example as above, but with a custom label display on the filter at the widget/dashboard level. The label is optional and will default to the query paramater 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.