You can specify a parameter instead of any expression, value, or name in a query.

First, you must define a parameter, which can then be used in several places in the query. For example:

SELECT COUNT(*) number
FROM (
TABLE_DATE_RANGE([githubarchive:day.events_],
TIMESTAMP('{dateFrom default="2015-01-01" type="datetime"}'),
TIMESTAMP('{dateTo default="2015-01-02" type="datetime"}')
))
WHERE type = '{event default='PushEvent' values='PushEvent,WatchEvent,ForkEvent' type='select'}'
LIMIT {limit_12 default="10" pattern="^[0-9]+$" type="input"}

The types of parameters:

1. input field (default type): {parameterName default="100" type="input" pattern="[0-9]*"}

  • default * — the default value
  • type — input
  • pattern — regular expression to check the value

2. date and time: {parameterName default="2015-01-02" type="datetime"}

  • default * — the default value
  • type * — datetime

3. select from the list: {parameterName default="PushEvent" type="select" values​="PushEvent,WatchEvent,ForkEvent"}

  • default * — the default value
  • type * — select
  • values * — list of values separated by commas

On subsequent calls to the parameter you only need to specify its name, for example: {date}.

* Required properties.

 

Example of using dynamic parameter as a regular expession value:

...
WHERE REGEXP_CONTAINS(user_country, r'{UserCountry default=".*" type="select" values​=".*,US,UK,Canada,Australia"}')
...

In the dropdown list of values that the regular expession can take you'll see:

  1. .* (any value)
  2. US
  3. UK
  4. Canada
  5. Australia

regexp-dynamic-param.png

Was this article helpful?
2 out of 2 found this helpful
Have more questions? Submit a request

6 Comments

  • 2
    Avatar
    Александр Малафеевский

    This is a very good opportunity, I'm using for my dynamic reports.

  • 0
    Avatar
    Nick Hughes

    Can you make filters optional?

    can you have IN parameters?

  • 3
    Avatar
    Maurits Meijer

    Is it possible to link parameters to cell/range references? 

  • 0
    Avatar
    Eugene Sorokin

    Hi Maurits,

    Currently, the add-on doesn’t support cell value reference as parameter for the request.
    But there is the feature request to make it possible. The upgrade might take a while to be released (you can check OWOX BI news and updates here).

  • 0
    Avatar
    Antonio Anderson Souza

    Is there some way to change the dynamic parameters values using AppScript?

    Edited by Antonio Anderson Souza
  • 0
    Avatar
    Samuel Finegold

    Might be worth saying at the end of the article that the parameter option will appear in the edit option on the right side of the report.

    Edited by Samuel Finegold
Please sign in to leave a comment.