Change Query by free typing in dashboard

[ulyssisss]

Hi all,

I want to change query by typing in dashboard level.

Like There's some filter window in dashboard.

And let's assume LookML is written like this (Of course it doesn't actually work. It's just example to help you understand.)

derived_table : { SQL : SELECT * FROM Table Where { %parameter_which_get_values_on_dashboard} }

So, If I type(not a choose) like ' ID = 3 AND Brand = 'Looker' Like this in dashboard filter, SQL will work 'SELECT * FROM Table Where ID=3 AND Brand= 'Looker'

I checked that the value can be changed through the parameter. However, from what I tested, it only show specific value which already declared by 'allowed_value' . I'd like to put the value freely as above, not this declared value.

Is it possible to do that?

Thanks 🙂

[a_shah · Accepted solution]

Hi @ulyssisss - It is possible to have a parameter that takes typed in values instead of values from database or allow list but it will not take spaces within the text ( like you are asking ->"  ID = 3 AND Brand = 'Looker'  "). And, for it to work, we need to define a parameter as below and then define the derived table.

parameter: test_param {type=string}

And then define the derived table with sql like .... FROM Table Where ID={% parameter test_param%}

Hope this helps

~Ashish

[ulyssisss]

Thanks @a_shah, It was very helpful.

It's such a shame to can't do that, But Based on what you said about parameter - type string, I think I can do many things.
Thanks! 🙂

[a_shah]

@ulyssisss - Looker is one of a kind tool currently in the market that is designed to take full benefits of the database capabilities with the help of liquid parameters. No other tools allows the kind of flexibilities in writing SQL on the fly, that can be achieved by Liquid. However, because of its nature of being programmatic, one needs to be SME of database and business use case to articulate the best use of it. 

~Ashish

[ulyssisss]

Thank you for the detailed additional explanation @a_shah 🙂

Yes, I agree with you. Of course, I haven't used all the tools, but at least from experience, there's nothing that supports SQL modeling as free as Looker. When I looked at the actual Liquid support product(Link), they didn't support as many products as I thought (which in BI or data intelligence product. Of course, Liquid is a great template).

 I'm so sorry but please understand that my knowledge is short. Can you briefly explain what you said 'However, because of its nature of being programmatic, one needs to be SME of database and business use case to articulate the best use of it. '? Do you mean that there should be a database that can supports cool features about LookML, and a predefined Use Case that help to make right LookML modeling?

Thanks!

[a_shah]

Sure @ulyssisss , What I mean is that the Looker features like writing code for views and models in json like scripting language where we can leverage database specific features/functions and apply variety of logic to dynamically change SQL, needs a good understanding of different database features. And then creating explores, looks and looker studio reports on top of it which are more business goals oriented and it needs a good understanding of business use case.

So, both of these are required to connect the analytical requirements with data in an innovative way and create intuitive and engaging experiences for users.

~Ashish  

[ulyssisss]

Thank you very much @a_shah ! 🙂

 I totally agree with you.

 In fact, All of the results in the Looker are SQL, which is run by database. As you said, depending on how well I understand the database that I am using, I can make LookML code more optimized for the database.

And also, In the end, our goal is to make better decisions through Looker and to establish the better decision of our company or client. This requires an understanding of the business and a clear understanding of what the actual users want(to make them reaching their goals). As you said, if I always remember and specify these two points, it seems that I can get best insights through Looker.

 Starting with a simple question about parameter, I was able to understand what kind of base I should use the Looker. I think it will be very helpful to use it in the future.
Thank you from the bottom of my heart.

Have a nice Day!

ulyssisss.