Filtering Data Studio by viewer
As we’ve worked with our districts to help build out their report library in Google Data Studio, we’ve seen a common request come up. Both in our cohort and in the broader Data Studio community, analysts have asked if it is possible to create a report that filters the report based on viewer.
Imagine having a student profile dashboard that is given to students, they all access the same report, but the student only receives their data. On the teacher side, imagine an assessment dashboard that shows a teacher only their students.
While Google does not provide this on the report building side for Data Studio, they do offer this functionality via Community Connectors.
Community Connectors are custom data connectors built with Google Apps Script. This means they can connect any internet enabled data source to Data Studio introducing a logic layer to handle filtering based on report viewer.
The above diagram shows Cloud SQL because districts in DataViz receive a SQL database in their Google Cloud where data from their key data systems is pushed to nightly. You can imagine this easily being subbed out for Google Sheets.
Analysts leverage the native Cloud SQL connector if they need to connect data and build a dashboard in Data Studio. Community Connectors come into play when the analyst wants to capture the report viewer’s email address so that it may be used in the WHERE statement of a SQL query. Earlier in this article we gave the example of a student facing dashboard. This dashboard could contain gradebook data and attendance. The connector would query the database for that data only returning the records for the logged in student.
Building the connector
We have created some sample code that shows what a community connector looks like when it uses the report viewer’s email address to filter a SQL query. If you plan to build a Community Connector we encourage you to follow this codelab created by Google. This codelab will take you step by step through the process and include additional resources for you to reference.
We hope you found this article useful and can use it to grow your Data Studio skills. Sign up for our newsletter and follow us on Facebook to hear the latest updates as the Data Viz Starter Pack grows!