Looker Studio - How to specify a WHERE clause database?

[randomizedcoder]

G'day,

Some months ago we spent time evaluating different "business analytics" tools, mostly because our existing tool QuickSight is garbage ( slow & ugly ).  After evaluating a bunch of tools, we agreed that Looker Studio seemed like a good option.

However, we have discovered a major issue that we have been unable to solve, and which ironically QuickSight can do.

The thing we need to know how to do is to pass a variable into the WHERE clause, so we can filter.  The primary use case if having a filter for the customer/tenant ID.

We are not alone.  There are many people asking about this, but nobody from Google has replied since this was opened back in 2020.  ( Is Looker Studio another abandoned Google project? )

https://support.google.com/looker-studio/thread/79585050/how-can-i-use-parameters-in-a-mysql-connect...

If we used BigQuery then maybe it is supported, but apparently:

- not Mysql ( https://cloud.google.com/sql/docs/mysql )

- and not Postgres ( https://cloud.google.com/sql/docs/postgres )

- and not AlloyDB ( https://cloud.google.com/alloydb/docs )

https://support.google.com/looker-studio/answer/9002005?hl=en#zippy=%2Cin-this-article

And there are other people talking about filtering with nice videos

https://visionlabs.com/blog/fieldids/

And some suggestions about literally hiding fields

https://support.google.com/looker-studio/thread/75023445/how-to-hide-a-report-level-filter-control-i...

... If we can't solve this, I guess we're going to be looking for a better tool.

Thanks in advance,

[ArkadyZagdan]

@randomizedcoder 
If you manage to find me I may be able to point you into a wrokable solution but writing hhere hhow to solve is too time consuming, sorry!

[Francesco_V]

I think you can try to use parameters for this and custom queries. Parameters can be pushed and used as WHERE conditions.

Also START AND END date can be pushed to the WHERE condition.

[randomizedcoder]

@Francesco_V 

Thanks for the reply

Here's what the team tried:

"

Update on injecting parameters into a Looker Studio dashboard:
Unfortunately, I think we're still blocked as there's no way to directly use the injected parameter value to filter the fetched records.
My progress so far:

• Define a parameter for a specific data source within a report  [tick]
• Enable that parameter to be set/overridden by dashboard URL query parameter [tick]
• Inject a specific parameter value into the report using URL query parameter (URL-encoded JSON object) [tick]
• Use injected parameter value within a Looker Studio component filter  -- not supported
• Use injected parameter value to parameterize custom SQL query against the database -- not supported by MySQL connector (apparently only supported for BigQuery)

What Francesco from Google said is true, for Google's BigQuery.

Perhaps there's a hack where I can create a custom boolean field based on the injected SP ID with the semantics "filter me/don't filter me".

That would keep all the tenant filtering within Looker Studio, no need to go back to the database to get that filtering done (which anyway isn't supported for MySQL connector +

Okay, the "tenant filter" custom field strategy seems workable.

The steps are:

• Inject tenant SP ID into dashboard using a query parameter
• Create a custom field that compares record's SP ID to the tenant ID (true if a match)
• Filter based on the custom field = true

The challenge is that we may need one parameter/filter combination per database table (as each database table must be represented in Looker Studio with a unique data source).

If you're wondering, QuickSight has a clean solution for this that's working today: inject parameter text string and run filters directly against that string. No need to jump through the additional hoops.

"

-> We are using Clickhouse which has MySQL and Postgres interfaces, but it seems like Parameters to WHERE clause only works with BigQuery/BigTable