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:
-
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. -
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 Looker 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 Looker Studio user interface in your custom query.
Create a parameter in the connection page
- Below the custom query editor, click +ADD PARAMETER.
- Configure the parameter options.
- 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.
- To the right of the parameter, click More options
.
- 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 (date is in standard format):
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);
Date parameter example (date is a Unix timestamp in microseconds):
SELECT creation_date, age, display_name from user.users as user
WHERE creation_date > UNIX_MICROS(PARSE_TIMESTAMP('%Y%m%d', @DS_START_DATE))
AND creation_date < UNIX_MICROS(PARSE_TIMESTAMP('%Y%m%d', @DS_END_DATE));
Email parameter example:
Select * from Sales WHERE sales-rep-email = @DS_USER_EMAIL;