In this tutorial, we’ll look at BigQuery parameters and how you can use them to make your Data Studio reports more flexible and maintainable. We’ll connect a BigQuery data source to a Data Studio report using a custom query, and use a parameter so report editors can modify the query from a list of predefined options.
Hello. Today we’re going to talk about how to filter data from BigQuery inside of a Data Studio report using a custom parameter. It’s an easy and powerful way to customize your reports. So, let’s look at the data we have, so let’s flip over to BigQuery. So, we have a table with information on episodes of the TV show, The Office. So you can see we have title, air date, IMDb rating and some descriptions. And we’re going to build a report that only wants to report on certain characters, so we’re going to be doing some filtering in this description column. So, let’s flip over to Data Studio and we’ll click on BigQuery and then we’ll go down to custom query and we’ll paste this in, and let’s look at what we have in the select statement here.
So, in the where clause we have this red text, contains, and then we’re looking at this specific description column, and then we have a parameter here. So, this parameter starts with an at symbol, and then we’re calling it character. So it can have any name, but it’s going to show up in the edit screen in Data Studio so we want it to be something that’s pretty obvious. So down here, it shows up in the parameter, Data Studio knows that we’re adding a parameter to the query. So, data type is going to be text, now for input type we have some decisions to make, so we could leave it as a text input which is just going to be a regular text box, or we can put some guardrails. We can make it a single select which is a dropdown so let’s add some options.
And then we want to make sure to check a lot of character to be modified in reports and we want that to be shared. And let’s go ahead and add that to the report. All right. So, let’s scroll down here and you can see under parameters it’s been added, and if we go ahead and change that you can see that the episodes are going to change. One thing to keep in mind is that if you go and change the format like if we were to make it multi-select the query, the custom query, would have to change because the parameters would be an array instead of a string. So, it’s something that we’ll be talking about in future videos. So, thanks for watching. And if you’re interested, please subscribe for more information about Google Analytics, Tag Manager and Data Studio.