The dark side of the ‘report filter’ is a pathway to many abilities, some considered as ‘unnatural’
In this post we are looking at how to create one dashboard that is shared among many users, but where a part of the data is only visible to a selected few.
The problem: Restricting data to some users
I have this use-case: we need to create a dashboard for water services in 63 local communities. They have data for water consumption, quality, measurements, hydrants, pipe length, maintenance, water meters, and a bunch of other things. Each local Mayor should be able to view the data only in their local community.
Unlike some other BI tools, Google Data Studio cannot restrict access to specific data. But there is a work-around for this. We can create 63 different dashboards and filter to different communities. Then we can grant the mayors and their assistants view access.
The solution: filters and multiple dashboards
My first idea for the technical design was the following.
First, I would create the main dashboard without any filters, so we would view all data of the communities.
Then, I would copy the dashboard and filter with a WHERE clause in my SQL of BigQuery connection. Very easy, right? So I did so. I copied the dashboard and updated my SQL WHERE community = “Nashville”. AND the result was perfect. My second dashboard was containing only the data of Nashville.
Now it was time to create the second dashboard.
I returned back to my main dashboard. OMG! I was now seeing only the data for Nashville!
But I didn’t change anything there, I only edited the copy of the main dashboard.
What was happening?
Let me explain.
As I updated the custom SQL, the whole data source was changed and the main dashboard was affected too. So what could I do next?
- Creating a new data source and re-connect to each element? No this would take too much time.
- Duplicating the data source and updating the SQL with a where clause, then I would replace the old data source with the new one while copying the dashboard. This means too many data source pollution but it would still work well.. But now the customer wants to see some KPIs for all of 63 communities, without the community filter. I would be lost in the forest of data sources.
There is however a great way to handle this: report filters.
Just go to the dashboard settings and select report filter.
Create a new one community is equal to “Nashville”. For the elements which shouldn’t have this filter, just click on the element and scroll down to “inherit filter” and uncheck it.
That simple actually!
Irem helps companies transform their way of working with Google Workspace and Google Data Studio in Germany, Sweden, Norway and Poland.