I have blogged already how to drill down to another page using Parameter, today, I find another approach, it is extremely easy, and a bit embarrassed why I have not thought about before !!!
I will be using administrative division of Algeria as an example, see the report here, please notice the pattern is not only for maps, personally I use drill down to another page extensively with finance reports, for example drilling down by Project, vendor, user etc
1- Create a map at the region Level
2- Add a new empty tab
3- Make the Map at the report level
4-make sure apply filter is on
5- in the second tab , hide the map either using send to back or add a big rectangle
6- add a new map at the city level
7- add a new button to navigate between page 1 and 2
basically when you create a report level map, the selection from page 1 get synchronized to page 2 , then the hidden map in page 2 will filter the map at the city level.
in 2017, at my previous job, we were using PowerBI Desktop as our reporting solution, but there was a big limitation, we couldn’t use the service, so sharing the reports was either in Excel or pdf.
I remember I did try different solutions (Rstudio, Qlik, SSRS), they were great Products, but you need some kind of server to share the reports. At that time all I wanted is a simple web app where people can click on a slicer and get a fancy charts.
At that time Google made their reporting solution free, I was really excited about Data Studio, a free product, extremely easy to share but unfortunately a bit slow and lacked some basic functionality, I still managed to build something but it was not really good
It is all history now, moved to another job, we have PowerBI service ( and Tableau), but still for some reason, I felt like a missed opportunity, what if Data Studio became a good enough to be used as a free report tool.
If I remember correctly 2017 and 2018, there was no major progress but then they released custom viz, which basically means you can port any javacript library relatively easily , I managed to build a custom viz see example here
and in sept 2019, BI Engine showed up !!
It was really a big Deal, BI Engine is an analytics in-memory Database , and it is fast and they gave away 1 GB for free, it means you can connect your data from BigQuery and pay nothing ( with a fair limit of course), this made this report possible
In May 2020, they finally released Google Map Integration , although with a limit of 10K points, it was not useful for my use cases ( Solar farm needs a lot of point around 40k to 60K)
That was great and all, but still I couldn’t write complex measures easily ( or maybe did not know how), but something changed in August 2020
At last we have Proper support for parameter, that changed everything, now you can write any complex business logic using SQL in BigQuery and visualize the results using Google Data Studio, and you can do a lot of fancy stuff see those examples
Still there was still a major bug, Pivot table in Data Studio show 0 for null values needless to say, it is extremely annoying although you can build workaround, it was a hack and not sustainable.
That was fixed last week
So yes, it took me three years to finish this report, BI Engine + Parameter + Custom Viz and a bug fix in the Pivot Table to make this report possible
I added a workflow explanation in the report, but basically create a reporting dataset as large flat fact table and show the results in BigQuery with further control by SQL Parameter, if the native visual are not satisfying, you can show pretty much anything using Vega-lite custom viz.
One aspect was impossible to do without Parameter is the dynamic grouping of dates, in the time series, the weekend update dynamically based on the cut off selected.
Please don’t get me wrong, there is still a lot of work to be done, but the foundation of the product is already there, I can see clearly the vision of the product team, hopefully they keep investing but faster this time ( Parameter Action, support for BigQuery geography field, analytics functions, Tiles for Custom Viz ……)
– If you need near real time reports
– You want a reporting solution and don’t have a decent budget.
– Used Data Studio in 2017 and dismiss it.
I have a good news for you, BigQuery/Data Studio is a viable option now, and you get 1 TB free for BigQuery and 1 GB compressed in -memory for BI Engine, that’s a lot of free resources, and there is no catch, you can share securely with anyone, again totally free.
Although I am a PowerBI developer and I love it, I think it is very healthy for the industry to have more choices, 2021 will be exciting !!!
saw this on twitter (Thanks Pablo), and I had an idea of using Regex_Match as currently comparing dimension does not work !!!
How RLS Works in Data Studio
Data Studio support RLS using google account ( can work with any email address not only gmail), so when you login using your account, Data Studio get your email adress which is unique to you, then you can use that email to filter rows .
in Some cases, maybe you want to implement security using username/Password, it turn out using parameter, it is trivial to implement, notice we are using Google sheet as a data Source, if you need more control, using SQL Parameter is even more Powerful
Warning : Storing password as plain text is dangerous
now for a very simple scenario, you have data and you want people to have access only to some rows
1- Create two parameters
usernameParameter and PasswordParameter
let’s start with usernameParameter, it has to be any value, and default value null or none etc
do the same for PasswordParameter
2- Create calculated field RLSfilter
basically, if both the values typed in the parameter control match then return 1 else 0
3- Create a Filter using the calculated field RLSfilter
4- Apply the filter to Visual
go to file, report setting, and add the filter, so it will automatically apply to all the visual in your report, or you can apply to every visual where it make sense only ( you may have multiple dataset where it does not apply)
you can add an image on the filter control to hide the password when the user type it.
Edit : 28 November 2020, there is an easy way without using parameter
Drill down to another page is a well known technique in BI software, you have a main page with aggregate data, and you can select one category then drill down to another page with more details and keeping the filter selection.
Data Studio does not support this functionality natively but we can simulated using parameter URL
in this example, we will drill down from Country to cities
1- Create a country parameter
2- add a new Page
the first report will show data at the country level, like this
make sure interaction is one
3- Go to Resource, Manage Report URL Parameter
4- Allow to be modified in report URL
tick the option on, you can edit the parameter to remove ds1.drillcountry to drillcountry
5- Build the URL
that’s the main part of the post, create a new calculated field using this forumula