How can a user set a specific chart to use a different data source than the reports default data source?

Create reports and charts using Google Data Studio and the BigQuery connector

In this section of the tutorial, you use Google Data Studio to visualize data in BigQuery using the BigQuery connector. You create a data source, a report, and charts that visualize data in the natality sample table.

Create a data source

The first step in creating a report in Google Data Studio is to create a data source for the report. A report may contain one or more data sources. When you create a BigQuery data source, Google Data Studio uses the BigQuery connector.

You must have the appropriate permissions in order to add a BigQuery data source to a Google Data Studio report. The permissions applied to BigQuery datasets also apply to the reports, charts, and dashboards you create in Google Data Studio. When a Google Data Studio report is shared, the report components are visible only to users who have appropriate permissions.

To create a data source:

1. Open Google Data Studio.

2. On the Reports page, in the Start with a Template section, click the Blank Report template. This creates a new untitled report.

3. If prompted, complete the Marketing Preferences and the Account and Privacy settings and then click Save. You may need to click the Blank template again after saving your settings.

4. In the Add data to report window, in the search box, enter BigQuery.

5. In the Google Connectors section, hover over BigQuery and then click Select.

6. For Authorization, click Authorize. This action lets Google Data Studio access to your Google Cloud project.

7. In the Request for permission dialog, click Allow to give Google Data Studio the ability to view data in BigQuery. You may not receive this prompt if you previously used Google Data Studio.

8. For My Projects, click Public Datasets.

9. For Billing Project, click the name of the Cloud project you created previously.

10. For Public Dataset, click samples.

11. For Table, click natality.

12. Click Add. Once Google Data Studio connects to the BigQuery data source, the tables fields are displayed. You can use this page to adjust the field properties or to create new calculated fields.

13. In the natality table, the year and source_year fields are stored as integers. To use these columns as strings in Google Data Studio, you change the type for these columns to text. To the right of the year and source_year fields, in the Type column, click Number and choose Text from the drop-down list.

14. Click Add to report.

15. When prompted, click Add to report.

16. In the Request for permission dialog, click Allow to give Data Studio the ability to view and manage files in Google Drive. You may not receive this prompt if you previously used Google Data Studio.

Create a bar chart using a calculated field

Once you have added the natality data source to the report, the next step is to create a visualization. Begin by creating a bar chart. The bar chart displays the total number of births for each year. To display the births by year, you create a calculated field.

To create a bar chart that displays total births by year:

1. (Optional) At the top of the page, click Untitled Report to change the report name. For example, type BigQuery tutorial.

2. After the report editor loads, click Insert > Bar chart.

3. Using the handle, place the chart on your report.

4. On the Data tab, notice the value for Data Source (natality) and the default values for Dimension and Metric.

5. Because you are charting the number of births by year, the dimension should be set to source_year. If source_year is not the default dimension, drag source_year from Available Fields onto the default dimension to replace it.

6. In the Metric section, click Add metric.

7. In the metric picker, click Create field.

8. To display a count of the number of children born each year (by gender), you create a calculated field. For this tutorial, you count the entries in the is_male field. The value in is_male is true if the child is male and false if the child is female. In the new metric window, for Name, type birth_count.

9. For Formula, type the following: COUNT(is_male).

10. Click Apply.

11. Click the report page close the metric picker.

12. After the metric is added, hover over the default metric and click the delete icon on the right-hand side. The Dimension should now be set to source_year and the Metric should be set to birth_count.

13. Notice the chart is sorted in Descending order by default. The years with the most births are displayed first.

14. To enhance the chart, change the bar labels. In the bar chart properties window, click the Style tab.

15. In the Bar chart section, check Show data labels.

    The total number of births is displayed at the top of each bar in the chart.

16. Sort the data by source_year instead of birth_count. In the bar chart properties window, on the Data tab, in the Sort section:

    • Click New field.
    • In the sort picker, scroll to Chart Fields and select source_year.
    • Click away from the dialog to close the sort picker.
    • Click Descending and select Ascending. The display changes to show the number of births by year in ascending order.

Filter the chart

Currently, the bar chart displays the total number of male and female children born each year. Add a filter to display only the female children born each year.

1. In the bar chart properties window, click the Data tab.

2. In the Filter section, click Add a filter.

3. In the Create filter window:

   • For Name, type Female birth count.
   • Click Include and change it to Exclude.
   • Click Select a field and choose is_male.
   • Click Select a condition and choose True.
   • Click Save.

Notice the chart is updated to display only female children born each year. Also notice that the legend does not change. The legend name still reflects the name of the metric birth_count.

Create a chart using a custom query

Creating a chart using the Custom Query option lets you leverage BigQuery's full query capabilities such as joins, unions, and analytical functions.

Alternatively, you can leverage BigQuery's full query capabilities by creating a view. A view is a virtual table defined by a SQL query. You can query data in a view by adding the dataset containing the view as a data source. For more information on views, see Using views.

When you specify a SQL query as your BigQuery data source, the results of the query are in table format, which becomes the field definition (schema) for your data source. When you use a custom query as a data source, Google Data Studio uses your SQL as an inner select statement for each generated query to BigQuery. For more information on custom queries in Google Data Studio, consult the online help.

Add a bar chart using a custom query

To add a bar chart to your report that uses a custom query data source:

1. From the menu options, choose Page > New page.

2. Click Insert > Bar chart.

3. Using the handle, place the chart on the report.

4. In the Bar chart properties window, on the Data tab, notice the Data Source is set to natality. Click natality to open the Select Data Source window.

5. Click Create new data source.

6. In the Google Connectors section, hover over BigQuery and the click Select.

7. For My Projects, click Custom query.

8. For Project, select your Google Cloud project.

9. Enter the following standard SQL query in the Enter custom query window:

   SELECT source_year, sum(case when is_male then 1 else 0 end) as male_births, sum(case when is_male then 0 else 1 end) as female_births FROM `bigquery-public-data.samples.natality` GROUP BY source_year ORDER BY source_year DESC

   This query uses a CASE statement to evaluate the is_male boolean field to determine how many births were female and how many were male for a given year.

10. Under Query Options, verify Use Legacy SQL is cleared. This action lets you use BigQuery standard SQL syntax.

11. At the top of the window, click Untitled data source, change the data source name to Male female case query.

12. In the upper right corner of the window, click Connect. After Google Data Studio connects to the BigQuery data source, the results of the query are used to determine the table schema.

13. When the schema is displayed, notice the type and aggregation for each field. By default, source_year is formatted as a Number and the Aggregation is set to None. To the right of the source_year field, in the Type column, click Number and select Text. Verify Aggregation is set to None.

14. Click Add to report.

15. When prompted, click Add to report.

16. Click the back arrow to close the Select Data Source window.

17. Google Data Studio may be unable to correctly determine the appropriate Metric for the chart. Because you are charting the number of male and female births by year, the values for Metric should be female_births and male_births. In the bar chart properties window, on the Data tab, in the Metric section, click Record Count.

18. In the metric picker, select female_births.

19. In the Metric section, click Add metric.

20. In the metric picker, select male_births. Your chart now displays the number of male and female children born each year using separate bars.

21. The chart should be sorted by female_births in descending order. Change this to source_year as you did in the previous chart. In the Sort section, click female_births.

22. In the sort picker, choose source_year. The chart now shows female and male births each year in descending order.

23. For readability, change the chart styles. In the Bar chart properties, click the Style tab.

24. Notice each bar has a default color based on the order the metrics were added to the chart. Click the second square in the color palette and change it from red to blue.

25. Click the first square in the color palette and change it from blue to red.

    The chart should look like the following:

View your query history

You can view queries submitted through the BigQuery Connector by examining your query history in the BigQuery web interface. Using the query history, you can estimate query costs, and you can save queries for use in other scenarios.

To examine your query history: