First, don’t be excited, it is a silly workaround, and introduce it is own problem, but if you are like me and need to deliver a nice-looking pivot table in Google Data Studio, it may be worth the hassle.
Show the spent and budget by Campaign and country, the spent is at the country level, the budget at the country level, here is a sample data set.
The Solution, First try
Probably you are saying, it is too easy, why you are writing a blog about it, GDS support pivot Table !!, let’s see the result
We have three Problems already (1 bug, 1 limitation and 1 by design)
Bug: you can return not return a null in the metric spent
By design: GDS does not understand hierarchy, country null is all good.
Limitation: The Famous Excel compact View is not supported
Here is the deal, contrary to what you may read in the internet, Pivot table is the most used viz in reporting ( ok, maybe second after table) and users will want their pivot table to look exactly like their beloved Excel, my own experience, if you show a user a map for example and he ask for a feature which is not possible, you can say, I can’t do it and people will tolerate that, but their Excel looking Pivot table, zero tolerance, if you can’t reproduce it, they will either think :
- Your BI is not good
- You don’t know the tool
The Solution, SQL!!.
Write a SQL that return a column that show the campaign and country in the same field, using union
Assuming your data is on google sheet.
- Link Google Sheet to an External table in BigQuery
2-Write the Query
Connect to that table using a custom Query
SELECT project,sum(budget) as budget,sum(spent) as spent FROM `test-187010.work.factraw`
group by 1
SELECT Concat(“\U0001f680”,country), budget, spent FROM `test-187010.work.factraw` where country IS NOT NULL
3-BI engine does not support External table.
Every time you open the report, GDS studio will issue a new query which cost 10 mb minimum !!!, even if the data is 1 kb ( it is a big data thing after all), to avoid that we extract the data
We use conditional formatting to highlight the row campaign.
needless to say !!!! you should not use it unless you have to, cross filtering will be a mess , Hopefully GDS will improve pivot table formatting in the near future.