How to pass Data Studio Controls to BigQuery custom query

1
votes

I have the Data Studio Report in which I have Date and Task Controls.

enter image description here

I am using custom BigQuery and have already set the date Control via date between @DS_START_DATE and @DS_END_DATE but I also need to set Task in the custom query.

enter image description here

Note: I cannot use the parameter like Task = @Task because Task is a Control while adding parameter(@Task) is a completely different thing.

Update : The task is not a first-level filter. Somehow I need it to pass it in the subquery. I can pass the date in subquery via @DS_START_DATE and @DS_END_DATE but cannot do the same with Task.

enter image description here

Thanks in advance :)

1
google-bigquerygoogle-data-studio

1 Answers

1
votes

Unless there is a specific requirement, your setup as it is should work. I'm assuming Task is a field in your data. When the report viewer picks one or more values from the Task filter control, Data Studio will send the query to BigQuery with appropriate filter clause. You can confirm this by looking at the billing project's query history in BigQuery.

Edit(since OP has a specific requirement to add the parameter):

  1. Create a task parameter in your data source. (how to) This can be text or single select. If it is single select, you will have to manually define the values. Make sure this parameter can be modified by report editors.
  2. In your query, add to the WHERE clause: WHERE task = @task
  3. Add the appropriate control in the report and link it to the task parameters.

This post has a similar example in the Creating parameterized custom queries section.