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, follow these steps:
-
Below the query editor, turn on the parameters you want to use.
-
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
use
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
;
