Use parameters in a custom query

Parameters let you build more responsive, customizable reports. You can pass parameters in a data source back to the underlying query. To use a parameter in your custom query, follow the relevant syntax guidelines documented in Running parameterized queries

Standard parameters

You can use the following parameters in any custom query:

Parameter

Purpose

@DS_START_DATE

Gets the beginning of the report date range.

@DS_END_DATE

Gets the end of the report date range.

@DS_USER_EMAIL

Gets the email address of the logged-in user.

To use these parameters:

  1. Below the query editor, turn on the parameters you want to use.

    You will be prompted to grant access to your email address when you turn on the @DS_USER_EMAIL parameter.
  2. In your query, be sure to use uppercase for the parameter names.

All parameter values are passed as strings. To handle them as dates, numbers, or other data types, be sure to use an appropriate conversion function, such as PARSE_DATE , PARSE_TIMESTAMP, or CAST.

Use date parameters to pass date ranges to the underlying query

If your report includes a date range control, viewers can use that to request different starting and ending dates from the data.

Use the email parameter to provide row-level access to the data

The email parameter lets you show only the data associated with the logged-in user of a report, data source, or exploration. That user must be logged in to a Google account and must consent to providing their email address to Data Studio. If the viewer doesn't consent, all charts in the report based on this data source will display an authorization error.

Custom parameters

You can use any parameters you create in the Data Studio user interface in your custom query.

Create a parameter in the connection page

  1. Below the custom query editor, click +ADD PARAMETER.
  2. Configure the parameter options.
  3. Click OK.

Hide custom parameters in the data source

By default, custom parameters can be modified in reports. To prevent report editors from changing a parameter's value, you can hide the parameter.

  1. To the right of the parameter, click More options More options..
  2. Click Hide.

Learn more about allowing data source parameters in reports.

Use a parameter in a custom query

In the body of your custom query, replace a hard-coded value with an identifier beginning with the @ character. For example: @param_name.

Parameter examples

Custom parameter example:

SELECT word FROM `TABLE` WHERE corpus = @corpus;

Use a string with contains and a number:

SELECT * FROM `bigquery-public-data.baseball.games_post_wide`

WHERE REGEXP_CONTAINS(gameId, @s)

AND attendance > @attendance LIMIT 100;

Use a multi-select string parameter. Note the use of UNNEST to flatten the list of values:

SELECT * from user.users as user WHERE display_name in UNNEST(@name);

Date parameter example:

SELECT creation_date, age, display_name from user.users as user

WHERE creation_date > PARSE_DATE('%Y%m%d', @DS_START_DATE)

AND creation_date < PARSE_DATE('%Y%m%d', @DS_END_DATE);

Email parameter example:

Select * from Sales WHERE sales-rep-email = @DS_USER_EMAIL;

Parameters

Was this helpful?
How can we improve it?
Search
Clear search
Close search
Google apps
Main menu
Search Help Center
true
102097
false