Dynamic parameters

Syntax

You can specify a parameter instead of any expression, value, or name in a query. First, define a parameter that can be used in various parts of the query by specifying its name, such as {date}. After that, you only need to reference the parameter by its name in subsequent calls. 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

In the OWOX BI BigQuery Reports extension, you can use three types of Dynamic parameters. Required properties are marked with an asterisk (*) symbol.

Input field (default type)

Syntax example: {parameterName default="100" type="input" pattern="[0-9]*"}

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

Date and time

Syntax example: {parameterName default="2015-01-02" type="datetime"}

  • default * — the default value
  • type *datetime

Select from the list

Syntax example: {parameterName default="PushEvent" type="select" values​="PushEvent,WatchEvent,ForkEvent"}

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

 

Example of using the dynamic parameter as a regular expression value:


SELECT
  actor_attributes_location,
  COUNT(*) count
FROM `bigquery-public-data.samples.github_timeline`
WHERE
  TIMESTAMP(repository_created_at)
    BETWEEN TIMESTAMP('{DateFrom default="2012-04-01" type="datetime"}')
    AND TIMESTAMP('{DateTo default="2014-04-10" type="datetime"}')
  AND repository_language  LIKE '%{Language default='' values=',Java,PHP,Ruby,C++' type='select'}%'
  AND actor_attributes_location IS NOT NULL
  AND actor_attributes_location != ""
GROUP BY 
    actor_attributes_location
ORDER BY 
    count DESC
LIMIT {Limit default="10" pattern="^[0-9]+$" type="input"}

With the provided query example, you will receive four parameters, as shown on the screen below:

owox-bi-bigquery-reports-dynamic-parameters-example.gif

 

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

6 Comments

  • 1
    Avatar
    Oleksandr Malafeievskyi

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

  • 1
    Avatar
    Nick Hughes

    Can you make filters optional?

    can you have IN parameters?

  • 4
    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.